MySQL学习笔记(9):索引

时间:2020-07-02 09:09:00 来源:互联网 作者: 神秘的大神 字体:

本文更新于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索引,只限于CHARVARCHARTEXT列,索引总是对整个列进行的,不支持前缀索引。

索引也可以具有以下作用:

  • 主键(PRIMARY)索引
  • 唯一(UNIQUE)索引
  • 前缀索引:对列的前面一部分进行索引。ORDER BYGROUP BY无法使用前缀索引。

注意,索引的长度限制以字节为单位,DDL语句中的长度表示字符数,在使用多字节字符集时,字段长度不能超过索引的最大字节长度限制。

能够使用索引的典型场景

  1. 匹配全值:对索引中的所有列都指定具体的值。如对索引a, b, c,执行WHERE a=1 AND b=2 AND c=3
  2. 匹配值的范围查询:对索引的值能够进行范围查找。如对索引a,执行WHERE a>1
  3. 匹配最左前缀:仅仅使用索引最左边的列进行查找。如对索引a, b, c,执行WHERE a=1
  4. 仅仅对索引进行查询,效率更高。如对索引a, b, c,执行SELECT c FROM tbl WHERE a=1
  5. 匹配列前缀:仅仅使用索引中的第一列,并且只包含索引第一列开头一部分进行查找。如对索引a, b, c,执行WHERE a like 'xxx%'
  6. 能够实现索引部分精确匹配而其他部分进行范围匹配。如对索引a, b, c,执行WHERE a=1 AND b>1
  7. 如果列名是索引,使用IS NULL就会使用索引(区别于Oracle)。如对索引a,执行WHERE a IS NULL
  8. 使用ICP(Index Condition Pushdown)特性,可将某些情况下的条件过滤操作下放到存储引擎层完成,降低不必要的IO访问。

存在索引但不能使用索引的典型场景

  1. %开头的LIKE查询不能利用BTREE索引。一般推荐使用全文索引。或利用InnoDB都是聚簇表的特点,采取一种轻量级的解决方式:索引通常比表小,InnoDB表上的二级索引除存储字段值外,还有主键值。通过扫描二级索引获取满足条件的主键列表后,根据主键回表检索记录,可避开全表扫描。
  2. 数据类型出现隐式转换时也不会使用索引。
  3. 复合索引的情况下,如果查询条件不包含索引列最左边的部分,即不满足最左前缀,则不会使用复合索引。
  4. 如果MySQL估计使用索引比全表扫描更慢,则不使用索引。
  5. OR分隔的条件,如前面的列有索引,后面的列没有索引,那么所有索引都不会被使用。因为后面的条件没有索引,肯定需要全表扫描,没必要增加索引的IO访问。

查看索引使用情况

可以通过SHOW STATUS查看索引使用情况:

  • Handler_read_key:一个行被索引值读的次数。高表示索引被经常使用。
  • Handler_read_rnd_next:在数据文件中读下一个行的次数。高表示索引不经常使用,进行大量的表扫描。