MySQL高级十四：索引的基本使用

索引的基本使用

一、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等

• 按照功能逻辑上：分为普通索引、唯一索引、主键索引、全文索引。
• 按照物理实现方式：分为聚簇索引和非聚簇索引
• 按照作用字段个数：分为单列索引和联合索引

1. 普通索引

   在创建普通索引是，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，就可以通过索引进行查询。

   例如：在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2. 唯一性索引

   使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。

3. 主键索引

   是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，即 NOT NULL + UNIQUE。一张表中最多只有一个主键索引。

4. 单列索引

   在表中的单个字段上创建索引。单列索引只根据该字段进行索引。

   一个表中可以有多个单列索引，单列索引可以是普通索引，也可以是唯一性索引，也可以是全文索引。

5. 多列索引

   是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。即：使用组合索引时，要遵循最左前缀集合。

   例如：表中的字段id、name和gender上建立了一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。

6. 全文索引

   也称为全文检索，是目前搜索引擎使用的一种关键技术。它能够利用“分词技术”等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能筛选出想要的结果。全文索引非常适合大型数据集，对于小的数据集，它的用处较小。

   使用关键字FULLTEXT，可以在类型为CHAR、VARCHAR或TEXT及其系列类型的字段上设置索引为全文索引。在定义索引的列上支持值的全文查找，且允许在这些索引列上插入重复值或者空值

   典型分类：自然语言的全文索引 和 布尔全文索引

   注：

   随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被solr、ElasticSearch等专门的搜索引擎所替代。

7. 空间索引

   使用关键字SPATIAL可以设置索引为空间索引，且只能建立在空间数据类型GEOMETRY、POINT、LINESTRING、PLOYGON上。（注：只有MyISAM支持空间索引，且字段值不能为空）

8. 存储引擎支持索引类型对应表

   	存储引擎	支持索引类型	不支持的索引类型
   1	InnoDB	B-Tree、Full - text	Hash
   2	MyISAM	B-Tree、Full - text	Hash
   3	Memory	B-Tree、Hash	Full - text
   4	NDB	Hash	B-Tree、Full - text
   5	Archive		B-Tree、Full - text、Hash

二、创建索引

1. 在创建表时指定索引列

   支持多种方法在单个或多个列上创建索引

   CREATE TABLE table_name [col_name data_type]
   [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC];
   

   分为五部分

   UNIQUE、FULLTEXT和SPATIAL 可选，分别标识唯一索引、全文索引和空间索引，都不选表示为普通索引

   INDEX 和 KEY 是同义词，都用来指定创建索引

   index_name：指索引的名称，如果指定，默认和col_name为索引名

   col_name：为需要创建索引的字段列 length：对于字符串类型的字段可以指定索引长度，其它类型无需指定

   ASC | DESC：指定索引在存储时是被升序存储还是降序存储

2. 在已存在的表上创建索引

   两个方式

   # 方式一
   ALTER TABLE table_name 
   ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX [index_name](col_name1, clo_name2);
   # 方式二
   CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX [index_name] ON table_name(col_name);
   

3. 隐式创建索引

   在声明有主键约束、唯一性约束、外键约束的字段上，会自动添加相关的索引。

4. 修改索引

   ALTER TABLE table_name 
   ALTER INDEX index_name [visible | invisible]
   

5. 删除索引

   使用场景：当需要进行大量增删改操作时，可以先将索引删除掉，等增删改完成后，再将这些字段上的索引重新加上，这样可以避免其影响增删改时的性能。

   # 方式一
   ALTER TABLE table_name DROP INDEX index_name;
   # 方式二
   DROP INDEX index_name ON table_name;
   

   注：

   ① 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成 索引的所有列

   ​ 都被删除，则整个索引将被删除。

   ② 且带AUTO_INCREMENT的PRIMARY KEY不能被删除。

6. 隐藏索引

   隐藏索引解决的问题：

   MySQL5.7之前，只能通过显示的方式删除索引。此时，如果发现删除索引后出现错误，只能通过显示创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作会消耗系统过多的资源，错做成本高。

   简介：

   MySQL8.0后，开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引。确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索 引，再删除索引的方式就是软删除 。

   即：想验证某个索引删除之后的查询性能影响，就可以暂时先隐藏该索引。

   使用：

   # 1.创建表时创建隐藏索引
   CREATE TABLE tablename(
   	propname1 type1[CONSTRAINT1],
       propname2 type2[CONSTRAINT2],
       ……
       propnamen typen,
       INDEX [indexname](propname1 [(length)]) INVISIBLE
   );
   # 2.在已存在的表上创建
   CREATE INDEX indexname
   ON tablename(propname[(length)]) INVISIBLE;
   # 或
   ALTER TABLE tablename
   ADD INDEX indexname (propname [(length)]) INVISIBLE;
   
   # 3.切换索引可见状态
   ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; 
   ALTER TABLE tablename ALTER INDEX index_name VISIBLE; 
   

   了解内容：使隐藏索引对查询优化器可见

   # 只针对单个会话有效
   SELECT @@optimizer_switch \G;
   SET SESSION optimizer_switch = "use_invisible_indexes=on";
   

参考资料

1. 在navicat中查看数据表的索引

   选中数据表，右键，点击“设计表”