曾经，我以为我很懂 MySQL 索引...

我的新课《C2C 电商系统微服务架构120天实战训练营》在公众号儒猿技术窝上线了，感兴趣的同学，可以长按扫描下方二维码了解课程详情：

课程大纲请参见文末

腾讯云数据库负责人林晓斌说过：“我们面试 MySQL 同事时只考察两点，索引和锁”。

言简意赅，MySQL 索引的重要性不言而喻。MySQL 索引历经了多个版本的迭代，从语法到底层数据结构都有很多改变。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询：

• 第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘 I/O 操作。
  

• 第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID（相当于页码）快速找到表中对应的记录。

MySQL 5.5 以后 InnoDB 储引擎使用的索引数据结构主要用：B+Tree；本篇文章带大家以 B+Tree 前世今生为主线来聊一聊。

Mark：B+Tree 可以对 ，>=，BETWEEN，IN，以及不以通配符开始的 LIKE 使用索引。（MySQL 5.5 后）

这些事实或许会颠覆你的一些认知，比如在你读过的其他文章或书中。以上这些都属于“范围查询”，都是不走索引的！

没错，早在 5.5 以前，优化器是不会选择通过索引搜索的，优化器认为这样取出的行多与全表扫描的行，因为还要回表查一次嘛，可能会涉及 I/O 的行数更多，被优化器放弃。

经过算法（B+Tree）优化后，支持对部分范围类型的扫描（得利与 B+Tree 数据结构的有序性）。

该做法同时也违反了最左前缀原则，导致范围查询后的条件无法用到联合索引，我们在后面详细说明。

索引的优点如下：

• 索引大大减小了服务器需要扫描的数据量。

• 索引可以帮助服务器避免排序和临时表。

• 索引可以将随机 I/O 变成顺序 I/O。

索引的缺点如下：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存索引文件。

• 建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重，但如果你在一个大表上创建了多种组合索引，且伴随大量数据量插入，索引文件大小也会快速膨胀。

• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

• 对于非常小的表，大部分情况下简单的全表扫描更高效。

其实最终选用 B+树是经历了漫长的演化：

二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）

理解 B+树之前，简单说一下二叉排序树，对于一个节点，它的左子树的孩子节点值都要小于它本身，它的右子树的孩子节点值都要大于它本身。

• 9 比 10 小，去它的左子树（节点 3）查找。

• 9 比 3 大，去节点 3 的右子树（节点 4）查找。

• 9 比 4 大，去节点 4 的右子树（节点 9）查找。

• 节点 9 与 9 相等，查找成功。

• 9 比 4 大，去它的右子树查找。

• 9 比 10 小，去它的左子树查找。

• 节点 9 与 9 相等，查找成功。

一共比较了 3 次，同样的数据量比二叉排序树少了一次，为什么呢？因为 AVL 树高度要比二叉排序树小，高度越高意味着比较的次数越多；不要小看优化的这一次，假如是 200w 条数据，比较次数会明显地不同。

③B 树（Balanced Tree）多路平衡查找树，多叉的

B 树示意图如下：

• 所有键值分布在整个树中。

• 任何关键字出现且只出现在一个节点中。

• 搜索有可能在非叶子节点结束。

• 在关键字全集内做一次查找，性能逼近二分查找算法。

为了提升效率，要尽量减少磁盘 I/O 的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。

• 由于磁盘顺序读取的效率很高（不需要寻址时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。预读的长度一般为页（page）的整倍数。

• MySQL（默认使用 InnoDB 引擎），将记录按照页的方式进行管理，每页大小默认为 16K（可以修改）。

B-Tree 借助计算机磁盘预读机制：每次新建节点的时候，都是申请一个页的空间，所以每查找一个节点只需要一次 I/O；因为实际应用当中，节点深度会很少，所以查找效率很高。

④B+Tree (B+树是 B 树的变体，也是一种多路搜索树)

• 所有关键字存储在叶子节点，非叶子节点不存储真正的 data，从而可以快速定位到叶子结点。

• 为所有叶子节点增加了一个链指针，意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。

• B+树的磁盘读写代价更低。

• B+树的查询效率更加稳定。

要知道的是，你每次创建表，系统会为你自动创建一个基于 ID 的聚集索引（上述 B+树），存储全部数据。

你每次增加索引，数据库就会为你创建一个附加索引（上述 B+树），索引选取的字段个数就是每个节点存储数据索引的个数，注意该索引并不存储全部数据。

原因有如下两点：

• B+树更适合外部存储（一般指磁盘存储），由于内节点（非叶子节点）不存储 data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用 B+树单次磁盘 I/O 的信息量相比较 B 树更大，I/O 效率更高。

• MySQL 是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以 B+树对索引列上的区间范围查询很友好。而 B 树每个节点的 key 和 data 在一起，无法进行区间查找。

①回表查询

比如你创建了 name， age 索引 name_age_index，查询数据时使用了：

select * from


    
 table where name ='陈哈哈' and age = 26;

②索引覆盖

结合回表会更好理解，比如上述 name_age_index 索引，有查询：

select name， age from table where name ='陈哈哈' and age = 26;

③最左前缀原则

比如索引 abc_index：（a，b，c）是 a，b，c 三个字段的联合索引，下列 sql 执行时都无法命中索引 abc_index 的。

select * from table where c = '1';
select * from 


    
table where b ='1' and c ='2';

以下三种情况却会走索引：

select * from table where a = '1';
select * from table where a = '1' and b = '2';
select * from table where a = '1' and b = '2'  and c='3';

另外还有一个特殊情况说明下，下面这种类型的也只会有 a 与 b 走索引，c 不会走。

select * from table where a = '1' and b > '2'  and c='3';

④索引下推优化

还是索引 name_age_index，有如下 sql：

select * from table where name like '陈%' and age > 26；

• 命中 name_age_index 联合索引，查询所有满足 name 以"陈"开头的数据， 然后回表查询所有满足的行。

• 命中 name_age_index 联合索引，查询所有满足 name 以"陈"开头的数据，然后顺便筛出 age>20 的索引，再回表查询全行数据。

①索引不会包含有 null 值的列

②使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。

例如，如果有一个 char（255）的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。

③索引列排序

④like 语句操作

⑤不要在列上进行运算

这将导致索引失效而进行全表扫描，例如：

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

⑥不使用 not in 和 <> 操作

曾经，我一度以为我很懂 MySQL。

刚入职那年，我还是个孩子，记得第一个需求是做个统计接口，查询近两小时每隔 5 分钟为一时间段的网站访问量，JSONArray 中一共返回 24 个值，当时菜啊，写了个接口循环二十四遍，发送 24 条 SQL 去查（捂脸）。

然后经理通过调用一个 dateTime 函数分组查询处理一下，就 OK 了，效率是我的几十倍吧。

END