索引

索引是关系型数据库中用来提升查询性能最为重要的手段。关系型数据库中的索引就像一本书的目录，我们可以想象一下，如果要从一本书中找出某个知识点，但是这本书没有目录，这将是意见多么可怕的事情（我们估计得一篇一篇的翻下去，才能确定这个知识点到底在什么位置）。创建索引虽然会带来存储空间上的开销，就像一本书的目录会占用一部分的篇幅一样，但是在牺牲空间后换来的查询时间的减少也是非常显著的。

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成。

接下来我们通过一个简单的例子来说明索引的意义，比如我们要根据学生的姓名来查找学生，这个场景在实际开发中应该经常遇到，就跟通过商品名称查找商品道理是一样的。我们可以使用MySQL的explain关键字来查看SQL的执行计划。

  1. explain select * from tb_student where stuname='林震南'\G
  1. *************************** 1. row ***************************
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: tb_student
  5.    partitions: NULL
  6.          type: ALL
  7. possible_keys: NULL
  8.           key: NULL
  9.       key_len: NULL
  10.           ref: NULL
  11.          rows: 11
  12.      filtered: 10.00
  13.         Extra: Using where
  14. 1 row in set, 1 warning (0.00 sec)

在上面的SQL执行计划中，有几项值得我们关注：

  1. select_type：查询的类型。
    • SIMPLE：简单SELECT，不需要使用UNION操作或子查询。
    • PRIMARY：如果查询包含子查询，最外层的SELECT被标记为PRIMARY。
    • UNION：UNION操作中第二个或后面的SELECT语句。
    • SUBQUERY：子查询中的第一个SELECT。
    • DERIVED：派生表的SELECT子查询。
  2. table：查询对应的表。
  3. type：MySQL在表中找到满足条件的行的方式，也称为访问类型，包括：ALL（全表扫描）、index（索引全扫描，只遍历索引树）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const/system（常量级查询）、NULL（不需要访问表或索引）。在所有的访问类型中，很显然ALL是性能最差的，它代表的全表扫描是指要扫描表中的每一行才能找到匹配的行。
  4. possible_keys：MySQL可以选择的索引，但是有可能不会使用。
  5. key：MySQL真正使用的索引，如果为NULL就表示没有使用索引。
  6. key_len：使用的索引的长度，在不影响查询的情况下肯定是长度越短越好。
  7. rows：执行查询需要扫描的行数，这是一个预估值。
  8. extra：关于查询额外的信息。
    • Using filesort：MySQL无法利用索引完成排序操作。
    • Using index：只使用索引的信息而不需要进一步查表来获取更多的信息。
    • Using temporary：MySQL需要使用临时表来存储结果集，常用于分组和排序。
    • Impossible where：where子句会导致没有符合条件的行。
    • Distinct：MySQL发现第一个匹配行后，停止为当前的行组合搜索更多的行。
    • Using where：查询的列未被索引覆盖，筛选条件并不是索引的前导列。

从上面的执行计划可以看出，当我们通过学生名字查询学生时实际上是进行了全表扫描，不言而喻这个查询性能肯定是非常糟糕的，尤其是在表中的行很多的时候。如果我们需要经常通过学生姓名来查询学生，那么就应该在学生姓名对应的列上创建索引，通过索引来加速查询。

  1. create index idx_student_name on tb_student(stuname);

再次查看刚才的SQL对应的执行计划。

  1. explain select * from tb_student where stuname='林震南'\G
  1. *************************** 1. row ***************************
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: tb_student
  5.    partitions: NULL
  6.          type: ref
  7. possible_keys: idx_student_name
  8.           key: idx_student_name
  9.       key_len: 62
  10.           ref: const
  11.          rows: 1
  12.      filtered: 100.00
  13.         Extra: NULL
  14. 1 row in set, 1 warning (0.00 sec)

可以注意到，在对学生姓名创建索引后，刚才的查询已经不是全表扫描而是基于索引的查询，而且扫描的行只有唯一的一行，这显然大大的提升了查询的性能。MySQL中还允许创建前缀索引，即对索引字段的前N个字符创建索引，这样的话可以减少索引占用的空间（但节省了空间很有可能会浪费时间，时间和空间是不可调和的矛盾），如下所示。

  1. create index idx_student_name_1 on tb_student(stuname(1));

上面的索引相当于是根据学生姓名的第一个字来创建的索引，我们再看看SQL执行计划。

  1. explain select * from tb_student where stuname='林震南'\G
  1. *************************** 1. row ***************************
  2.            id: 1
  3.   select_type: SIMPLE
  4.         table: tb_student
  5.    partitions: NULL
  6.          type: ref
  7. possible_keys: idx_student_name
  8.           key: idx_student_name
  9.       key_len: 5
  10.           ref: const
  11.          rows: 2
  12.      filtered: 100.00
  13.         Extra: Using where
  14. 1 row in set, 1 warning (0.00 sec)

不知道大家是否注意到，这一次扫描的行变成了2行，因为学生表中有两个姓“林”的学生，我们只用姓名的第一个字作为索引的话，在查询时通过索引就会找到这两行。

如果要删除索引，可以使用下面的SQL。

  1. alter table tb_student drop index idx_student_name;

或者

  1. drop index idx_student_name on tb_student;

我们简单的为大家总结一下索引的设计原则：

  1. 最适合索引的列是出现在WHERE子句和连接子句中的列。
  2. 索引列的基数越大（取值多重复值少），索引的效果就越好。
  3. 使用前缀索引可以减少索引占用的空间，内存中可以缓存更多的索引。
  4. 索引不是越多越好，虽然索引加速了读操作（查询），但是写操作（增、删、改）都会变得更慢，因为数据的变化会导致索引的更新，就如同书籍章节的增删需要更新目录一样。
  5. 使用InnoDB存储引擎时，表的普通索引都会保存主键的值，所以主键要尽可能选择较短的数据类型，这样可以有效的减少索引占用的空间，利用提升索引的缓存效果。

最后，还有一点需要说明，InnoDB使用的B-tree索引，数值类型的列除了等值判断时索引会生效之外，使用>、<、>=、<=、BETWEEN…AND… 、<>时，索引仍然生效；对于字符串类型的列，如果使用不以通配符开头的模糊查询，索引也是起作用的，但是其他的情况会导致索引失效，这就意味着很有可能会做全表查询。