MySQL高级之索引三

一、索引的分类

1. 按照物理实现方式分类

1.1 聚簇索引

• 看之前的文章，已经详细说过，不再赘述。

1.2 非聚簇索引

• 看之前的文章，已经详细说过，不再赘述。

2. 按照字段个数划分

2.1 单列索引

• 在一个字段上建立的索引。

2.2 多列索引（联合索引）

• 在多个字段上建立的索引。

3 按照功能逻辑分类

3.1 普通索引

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

3.1 唯一性索引

• 使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。唯一索引和唯一性约束进行绑定，创建唯一性约束，会自动生成唯一性索引。同理，删除唯一性约束，也会自动删除唯一性索引。

3.1 主键索引

• 主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。主键索引和主键约束绑定，一旦声明了主键约束，会自动生成主键索引，且主键索引只能通过定义主键约束的方式进行定义。通过删除主键约束，就可以删除主键索引。

3.1 全文索引

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

3.1 空间索引

• 使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、 LINESTRING 和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

二、索引的创建

三、索引的使用情况

1. 什么时候适合创建索引

• 字段的数值有唯一性的限制
• 频繁作为WHERE条件的字段
• 经常GROUP BY和ORDER BY的列
• DISTINCT字段需要创建索引
• 多表连接时， 对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致 。

2. 什么时候不适合创建索引

• 在where中使用不到的字段，不要设置索引
• 数据量小的表最好不要使用索引
• 有大量重复数据的列上不要建立索引
• 避免对经常更新的表创建过多的索引
• 不建议用无序的值作为索引

四、索引的失效情况

1. 全值匹配我最爱

• 如果where后面的查询语句，用到了索引所覆盖的全部列，且条件都为常值，那么索引一定不会失效。

2. 未遵守最左前缀

• 对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足才可以，一旦跳过某个字段，索引后面的字段都无法被使用。
• 原因：多列索引的底层B+树的结构，实际上就是先以通过索引的第一个字段进行查询，当第一个字段相同时，再采用第二个字段进行查询，如果跳过索引的某个中间字段，或者根本没用到索引的第一个字段，那么MySQL对于没有给定的字段就无法使用索引进行定位，后续的条件也自然就无法使用到索引了。

3. 索引列进行计算

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效。
• 原因：MySQL并不知道字段计算之后的结果是什么样的，所以只能先把所有记录该字段的值一个一个都取出来，进行运算后，和我们的条件进行比较。

4. 索引列范围查询

• 范围条件右边的列索引失效
• 原因：一旦使用了范围查询，对于该字段之后的字段，就不知道该怎么进行精确定位了，只能对范围内的数据逐一对比，故范围字段后索引失效。

5. Like百分写在开头

• like以通配符开头（’%abc…’），MySQL索引失效
• 原因：MySQL无法精确定位，没办法去B+树进行寻找，只能一个一个去匹配，直接开启了全表扫描，索引失效。

6. 使用了不等和非空

• 条件中使用了不等于和is not null，则索引失效
• 原因：不等于，就是所有的值都要看一遍，看看哪个不等于就符合，直接开启了全表扫描，索引失效。

7. OR的前后存在非索引列

• 只要OR的两侧条件中有一个是非索引列，索引就会失效。
• 原因：非索引列一定会进行全表扫描，索引自然失效。