MySQL学习笔记（9）：索引
本文更新于2019-07-27，使用MySQL 5.7，操作系统为Deepin 15.4。
在创建一个n列的复合索引时，实际是创建了n个索引。可利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。
InnoDB表中的记录会按一定顺序存储。如果有主键，则按主键顺序；如果没有主键但有唯一索引，则按唯一索引顺序；如果既没有主键也没有唯一索引，则会生成内部列，按内部列顺序。InnoDB的普通索引都会保存主键的值。
索引是在存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引不一定相同，也不是所有的存储引擎都支持所有的索引类型。
索引按存储数据结构可分为：
- BTREE索引：适用于全关键字、关键字范围、关键字前缀查询。最左前缀匹配原则是BTREE索引使用的首要原则。大部分存储引擎都支持BTREE索引，MyISAM和InnoDB默认使用BTREE索引。
- HASH索引：适用于全关键字查询，不适用于范围查询。只有MEMORY存储引擎支持HASH索引，默认使用HASH索引，也支持BTREE索引。
- RTREE索引：即空间（SPATIAL）索引，主要用于地理空间数据类型。只有MyISAM存储引擎支持RTREE索引。
- FULLTEXT索引：即全文索引。只有MyISAM存储引擎支持FULLTEXT索引，只限于
CHAR、VARCHAR、TEXT列，索引总是对整个列进行的，不支持前缀索引。
索引也可以具有以下作用：
- 主键（PRIMARY）索引
- 唯一（UNIQUE）索引
- 前缀索引：对列的前面一部分进行索引。
ORDER BY和GROUP BY无法使用前缀索引。
注意，索引的长度限制以字节为单位，DDL语句中的长度表示字符数，在使用多字节字符集时，字段长度不能超过索引的最大字节长度限制。
能够使用索引的典型场景
- 匹配全值：对索引中的所有列都指定具体的值。如对索引
a, b, c，执行WHERE a=1 AND b=2 AND c=3。 - 匹配值的范围查询：对索引的值能够进行范围查找。如对索引
a，执行WHERE a>1。 - 匹配最左前缀：仅仅使用索引最左边的列进行查找。如对索引
a, b, c，执行WHERE a=1。 - 仅仅对索引进行查询，效率更高。如对索引
a, b, c，执行SELECT c FROM tbl WHERE a=1。 - 匹配列前缀：仅仅使用索引中的第一列，并且只包含索引第一列开头一部分进行查找。如对索引
a, b, c，执行WHERE a like 'xxx%'。 - 能够实现索引部分精确匹配而其他部分进行范围匹配。如对索引
a, b, c，执行WHERE a=1 AND b>1。 - 如果列名是索引，使用
IS NULL就会使用索引（区别于Oracle）。如对索引a，执行WHERE a IS NULL。 - 使用ICP（Index Condition Pushdown）特性，可将某些情况下的条件过滤操作下放到存储引擎层完成，降低不必要的IO访问。
存在索引但不能使用索引的典型场景
- 以
%开头的LIKE查询不能利用BTREE索引。一般推荐使用全文索引。或利用InnoDB都是聚簇表的特点，采取一种轻量级的解决方式：索引通常比表小，InnoDB表上的二级索引除存储字段值外，还有主键值。通过扫描二级索引获取满足条件的主键列表后，根据主键回表检索记录，可避开全表扫描。 - 数据类型出现隐式转换时也不会使用索引。
- 复合索引的情况下，如果查询条件不包含索引列最左边的部分，即不满足最左前缀，则不会使用复合索引。
- 如果MySQL估计使用索引比全表扫描更慢，则不使用索引。
- 用
OR分隔的条件，如前面的列有索引，后面的列没有索引，那么所有索引都不会被使用。因为后面的条件没有索引，肯定需要全表扫描，没必要增加索引的IO访问。
查看索引使用情况
可以通过SHOW STATUS查看索引使用情况：
- Handler_read_key：一个行被索引值读的次数。高表示索引被经常使用。
- Handler_read_rnd_next：在数据文件中读下一个行的次数。高表示索引不经常使用，进行大量的表扫描。
