MySQL: B-Tree索引的优点和限制

先来说说优点：

最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的，所以B-Tree也就会将相关的列值都存储在一起。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。据此特性，总结下来索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

再来说说限制：

例如创建一个表：

  CREATE TABLE People (
       last_name varchar(50)    not null,
       first_name varchar(50)   not null,
       dob date                 not null,
       gender enum('m', 'f')  not null,
       key(last_name, first_name, dob)
  );

1、匹配最左前缀

如果不是按照索引的最左列开始查找，则无法使用索引，例如只查找first_name则索引不生效（即跳过了最左列last_name）。

还有一种情况是查找last_name时左边使用了模糊搜索，比如where last_name like %tom，此时索引不生效。

2、不能跳过索引中的列

比如where last_name=tom and dob = '2019-06-24'，此查询跳过了first_name，那么索引只用到last_name。

3、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name='Smith' AND frst_name LIKE 'J％' AND dob='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

以上内容摘录自《高性能MySQL》