数据库管理——表索引

表索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有数据类型都可以被索引。

索引概述

• 索引类型

  • ​ 索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引

• 索引存储

  • ​ 数据库底层索引实现主要有两种存储类型，B树($BTREE$)和哈希($HASH$)索引，$InnoDB$和$MyISAM$ 使用BTREE索引；而MEMORY 存储引擎可以使用BTREE 和 HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是InnoDB。

• 索引优点

  • ​ 可以提高检索数据的速度。

• 索引缺点

  • ​ 创建和维护索引需要耗费时间、空间；增删改数据时，要动态地维护索引，造成数据的维护速度降低

• 使用建议

  • ​ 先删除表中的索引，然后插入数据，插入完成后再创建索引。因为向有索引的表中插入记录，数据库会按照索引进行排序，降低插入记录的速度

创建和查看索引

​ 创建索引是指在某个表的一列或多列上建立一个索引，提高对表的访问速度。三种创建方式：创建表的时候创建索引、在已经存在的表上创建索引、使用$ALTERTABLE$语句来创建索引

普通索引

创建表时定义索引

在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型索引可以创建在任何数据类型的字段上。

CREATE TABLE tablename(
	propname1 type1,
    propname2 type2,
    ......
    propnamen typen,
    
    INDEX|KEY
    [indexname](propnamen[(length)][ASC|DESC]));
)
INDEX和KEY用来指定字段为索引的，二者选择其中之一就可以。indexname是索引名字、propnamen是索引对应的字段的名称
length是可选参数，指索引的长度，必须是字符串类型才可以使用
ASC和DESC都是可选参数，ASC是升序、DESC是降序，默认升序

• create database school;
  use school;
  create table class(id int,name varchar(128) UNIQUE,teacher varchar(64),INDEX index_no(id DESC));
  show create table class;#查看表结构
  insert into class values(1,'一班','MArtin');
  insert into class values(1,'二班','Rock');
  select * from class where id > 0; #根据id查询记录，结果将降序排列
  

已存在的表上创建索引

• 执行 create 语句

  • CREATE INDEX indexname #此处不能为 KEY
    	ON tablename(propname[(length)][ASC|DESC]);
    

  • create database school;
    use school;
    create table class(id int,name varchar(128) UNIQUE, teacher varchar(64));
    create index index_id on class(id ASC);
    show create table class;#查看表定义
    insert into class values(1,'一班','Martin');
    insert into class values(1,'二班','Rock');
    select * from class where id > 0;#升序
    

• 执行 ALTER TABLE 语句

  • ALTER TABLE tablename ADD INDEX|KEY indexname
    	(propname [(length)][ASC|DESC]);
    

查看索引执行情况

EXPLAIN 查询语句
explain select * from class where id > 0;

• $key$： 实际使用的索引。如果为NULL，则没有使用索引

• $possiblekeys$：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

• $$keylen：$$ 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

唯一索引 UNIQUE INDEX

​ 所谓唯一索引，就是创建索引时，限制索引的字段值必须时唯一的，通过该类型的索引可以比普通索引更快速地查询某条记录

创建表时定义索引

CREATE TABLE tablename(
	propname1 type1,
    ......
    propnamen  typen,
    UNIQUE INDEX | KEY [indexname](propnamen[(length)][ASC|DESC])); 
)
#就是比普通索引多一个 UNIQUE

在已存在地表上创建索引

• CREATE UNIQUE INDEX indexname
  	ON tablename(propname[(length)][ASC|DESC]);
  

• ALTER TABLE tablename ADD UNIQUE INDEX|KEY indexname (propname [(length)][ASC|DESC]);
  

全文索引 FULL INDEX

​ 全文索引主要对字符串类型建立基于分词的索引，主要基于CHAR、VARCHAR 和 TEXT的字段上，以便能更加快速地查询数据量较大地字符串类型的字段

创建表时定义索引

CREATE TABLE tablename(
	propname1 type1,
    propname2 type2,
    ......
    propnamen typen,
    FULLTEXT INDEX|KEY
    [indexname](propname [(length)])
);

create database school;
use school;
create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_comm(comment));#建立comment字段为全文索引
insert into class values(1,'一班','Martin','我是一个兵，来自老百姓！');
insert into class values(2,'二班','Rock','我是一个兵，来自老百姓！');
insert into class values(3,'三班','Janny','I"m Miss Zhang.');
select * from class where match(comment) AGAINST ('我是一个兵');#利用全文检索索引快速查询记录

已存在的表上创建索引

• 执行create 语句

  • CREATE FULLTEXT INDEX indexname
    	ON tablename(propname1[(length)]);
    使用 CREATE FULLTEXT INDEX 表示用来创建全文索引
    

    create database school;
    use school;
    create table class(id int, name varchar(128) UNIQUE, teacher varchar(64));
    #建立id字段索引
    create FULLTEXT index index_teacher on class(teacher);#追加全文索引
    show create table class;
    

• 执行ALTER TABLE 语句

  • ALTER TABLE tablename
    ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);
    

使用场景

• 根据全文索引字段进行全文检索数据

  • SELECT * FROM 表名 WHERE MATCH('列名')AGAINST('关键字'); # against
    

  • create database school;
    use school;
    create table class(id int, name varchar(128) UNIQUE,teacher varchar(64),info varchar(1024),FULLTEXT INDEX index_des(info));#建立info字段为全文索引
    insert into class values(1,'一班','Martin','我是一个兵，来自老百姓！');
    insert into class values(2,'二班','Rock','我是一个兵，来自老百姓！');
    insert into class values(3,'三班','Janny','I"m Miss Zhang.');
    select * from class where match(comment) AGAINST ('我是一个兵');#利用全文检索索引快速查询记录
    

mysql8 中文分词支持

配置文件 my.ini增加如下配置，然后重启MySql8.0服务

• [mysqld]
  ngram_token_size=2
  

• create database school;   #创建数据库school  
  use school;   #选择数据库school  
  create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) with parser ngram);       #创建表class, 并建立为comment 字段为全文索引  
  insert into class values(1,'1班','Martin','我是一个兵，来自老百姓!');  # 插入记录1 
  insert into class values(2,'2班','Rock','此班主任毕业自唐僧系');   # 插入记录2 
  insert into class values(3,'3班','Janny','I''m Miss Zhang.');   #插入记录3 
  select * from class where match(comment) AGAINST('百姓');#利用全文检索索引快速查询记录
  select * from class where match(comment) AGAINST('唐僧');#利用全文检索索引快速查询记录
  

多列索引

​ 多列索引，是指在创建索引时所关联的字段不是一个字段而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

​ 和普通索引基本相同，不同之处就是增加了多个索引列

创建表时定义索引

INDEX|KEY [indexname](propname1 [(length)][ASC|DESC],... propnamen [(length)][ASC|DESC]);

create database school;
use school;
create table class(id int,name varchar(128) UNIQUE,teacher varchar(64),INDEX index_mult_columns(id,teacher));
show create table class;
insert into class values(1,'一班','Martin');
insert into class values(1,'二班','Rock');
select * from class where id  > 0;#仅使用id查询记录会启用多列索引

已存在的表上创建索引

• 执行create语句

  • CREATE INDEX indexname
    	ON tablename(propname1 [(length)][ASC|DESC],... propnamen [(length)][ASC|DESC]);
    

• 执行ALTER TABLE 语句

  • ALTER TABLE tablename
    	ADD INDEX | KEY  indexname(propname1 [(length)][ASC|DESC],... propnamen [(length)][ASC|DESC]);
    

隐藏索引

​ 隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏

删除索引

所谓删除索引，就是删除表中已经创建的索引，因为有些索引会降低表的更新速度，影响数据库性能

DROP INDEX indexname ON tablename;

修改索引

先删除、再增加

索引的设计原则

• 选择唯一性索引

  • 唯一性索引的值是唯一的，可以更快地通过该索引来确定某条记录。例如 学生学号

• 为经常需要排序、分组和联合操作地字段建立索引

  • 经常使用ORDER BY，GROUP BY，DISTINCT和UNION等操作地字段，排序操作会浪费很多时间，建立索引会有效避免排序操作

• 为经常作为查询条件地字段建立索引

  • 如果某个字段经常用来做查询条件，那么该字段地查询速度会影响整个表地查询速度

• 限制索引的数目

  • 索引需要占用磁盘空间，索引越多，需要空间越大，修改时，对索引重构和更换很麻烦

• 使用数据量少的索引

  • 比如学号、手机号等等

• 尽量使用前缀来索引

  • 如果索引的值很长，最好使用值的前缀来索引。例如 TEXT 和 BLOG类型字段，全文检索浪费时间

• 删除不再使用或很少使用的索引