前言

借用王国维人生的三重境界：

"昨夜西风凋碧树。独上高楼，望尽天涯路。"此第一境也。

"衣带渐宽终不悔，为伊消得人憔悴。"此第二境也。

"众里寻他千百度，蓦然回首，那人却在灯火阑珊处。"此第三境也。

对于一项技术，一门语言，一个交付项目也都能类比出三重境界，或者三个学习层次。

借鉴这个思维框架，本篇闲聊主要分三个部分介绍Mysql索引。

学习-索引的原理

昨夜西风凋碧树。独上高楼，望尽天涯路。

说起索引，这并不是一个多么新奇的玩意，在计算机科学诞生之前就已经存在。比如日常用到的词典，图书馆里面检索图书，本质上都是对单词/图书在建立索引，方面快速查找。看来不光艺术来源于生活，其实计算机里的很多技术也是来源于生活的。

索引数据结构的两大门派

哈希数据结构

其实常见的NoSQL数据库，基本上或多或少都是采用的该数据结构。优点缺点都是非常明显的。哈希算法的实现层出不穷，从图中不难看出哈希数据结构的特点：

• 数据是无序存储的，通过维护了一个哈希桶来进行定位。
• 查询过程必须知道hash key然后才能检索数据的位置。
• 可能发生哈希碰撞，这时候碰撞的数据用链表或者树来存储。

主要适用的场景：

• 等值查询，如查询某一个用户ID，或者查询某个身份证号对应的信息等。

不适用的场景：

• 由于数据存储是无序的，所以范围查询需要扫描整个数据集，因此不推荐。
• 聚合也是不划算的。

比如Redis，memcached这些数据库不是说一定不能范围查询，一定不能做聚合。关键在于合不合适。架构师的职责也是找到合适的技术选型，将合适的技术用在合适的地方，才是技术的核心要义。

树状结构

不管是二叉树也好，BTree也好，B+Tree也好，或者其他XXTree。一般情况下仔细分析不难发现该结构数据存储的特点：

• 为了数据检索方便，数据一定是有序的(决定了可以范围查询，因为只考虑等值查询的话，有更好的选择)。
• 既然选择了树状结构存储，一般情况下，都要平衡数据读取和数据写入的效率。(因为只考虑读取的话，也有更好的选择)。

基于这些特点也不难得出树状结构的使用场景：

• 有范围查询的场景。
• 读写频繁的场景。

其实除了这两大门派，还有其他比较精妙的数据结构，用来解决特定领域的问题，您还知道哪些数据结构？欢迎讨论。

InnoDB的数据结构

上一节我们聊了数据库索引的两大门派，接下来我们分析下，MySQL作为一个定位是关系型数据库的产品，他有什么特点：

• 频繁的数据读写(所谓CRUD资深专家是也)
• 既然是关系型数据库范围查询，关联总是少不了的。

因此我们不难推导出，MySQL索引适合选择树状结构。

以InnoDB引擎为例，最终我们优秀且理性的MySQL设计师也确实选择了B+Tree作为索引的数据结构。聊到这里，接下来好学多问喜欢打破砂锅问道底的朋友们，一定会有另外一个疑问了：为什么是B+Tree? 二叉树，B树不要面子的吗？接下来就回答这个疑问：

为什么是B+Tree?

我们知道读写磁盘是非常耗时的一个操作，MySQL作为一个磁盘存储的数据库，如何减低磁盘的访问次数，永远永远是第一考虑的。

• 如果用二叉树的话，对于1000条数据，就要访问10次磁盘，这个速度确实有点太要命了。
• 同样为了减少磁盘的访问次数，我们优秀的MySQL工程师，一般都会将树的根节点进行缓存，甚至是第二层也有可能被缓存，这时候，有限的空间里缓存更多的数据，就是最优的选择。而BTree非叶子节点也存储了数据，会减少缓存的索引数据量。因此BTree也不适合。

实际上，B+Tree还有一个更大的优势，在很多资料中没有提到，这个点进一步的拉开了和BTree的差距，使我们的B+Tree同学在这次选型中遥遥领先。为了说明这个优势，需要一个图来更清晰的表达出来。

如图所示：

• B+树作为一个N叉树，每个Page可存储的数量是可以大于2个的，这大大降低了树的层数。
• 非叶子节点不存储数据，所以相同内存可缓存的节点数量增加。
• 还有个优势就是，叶子节点间的数据通过指针相连，在做范围查询过滤的时候，可以极大的减少磁盘读取次数。

如：读取id>=1 and id<=17的数据，只需要定位到id=1和id=17所在的叶子节点，不需要走层序遍历的算法(不必访问叶子节点了)，直接以链表的方式就可以将数据读取完毕。

了解了B+树的特点，其实不难理解为什么主键最好是自增的，因为自增的ID在数据增加的过程中，可以减少树重建的次数和范围。感兴趣的话，可以推导下这个结论得出的过程。

聚簇索引非聚簇索引

聚簇索引

聚簇索引，根据名字大概能明白个大概，就是一个数据表索引和数据存储在同一个文件，InnoDB引擎采用了聚簇索引，索引过程如下图：

从图中基本上可以看出InnoDB聚簇索引的特点：

• 如果是主键索引的话，直接通过主键索引树定位到对应的数据。
• 如果是普通索引，则先通过普通索引树定位到数据对应的ID，然后根据ID在主键索引树种定位数据。这个过程有个专业名词，叫做回表。回表是会影响性能的，所以如果一个SQL能够减少回表次数，那么就可以提升性能。

其实基于聚簇索引的特点，结合之前B+Tree里的结论，我们还能得出一个结论：

基于InnoDB引擎的表，必须要指定主键，且最好是自增的。(如果没有主键，引擎内部会指定一个类似于rowid的默认主键)

非聚簇索引

非聚簇索引，索引文件和数据文件是分开存储的，MyISAM采用了非聚集索引，其索引过程如下图：

仔细看图，基本上也可以看出MyISAM非聚簇索引的特点：

• 索引文件和数据文件是分成两个文件存储的。(感兴趣的话，可以创建一个MyISAM的表，然后看下文件目录，应该有两个文件:*.MYI[索引],*.MYD[数据]，其实还有个文件用来描述表结构的)
• 无论是主键索引还是普通索引，都是先定位到数据的地址，然后通过地址，在数据文件中，查找到实际的数据。

优劣分析

看完聚簇索引和非聚簇索引之后，喜欢打破砂锅问到底的你，一定也有一个疑问，为啥要一个MySQL要有两个实现呢，直接复用一个索引方式不香吗？好吧，我也有这个疑问，接下来我们比较下两个索引的优劣。

聚簇索引：

• 其实看图不难发现，聚簇索引的物理数据也是顺序存储的。索引相邻的数据，物理数据也是相邻的。范围读取的时候更加高效。
• 由于索引和数据都是有序的，排序的场景性能也会更高。
• 典型如利用主键索引的情况下， 不需要回表就可以读取数据。

非聚簇索引：

• 非聚簇索引叶子节点存储的是数据的指针。所以内存中只需要缓存数据的地址即可。单条读取，速度更快。
• 相应的，由于非聚簇索引存储的是指针，在数据更新等场景下，数据地址可能发生变化。还需要更新叶子节点的指针位置。(我的理解：这也解释了，为什么MyISAM更新的时候，要做表锁，因为不锁表很难保证数据一致性)。

事实上，MySQL InnoDB也是用的最为广泛的数据库引擎，为了避免歧义，后面所有的讨论都会基于InnoDB来进行。

一些常见的Mysql索引知识点

本节选取了一些MySQL索引的非常实用一些知识点，他可以在我们日常创建索引的时候，提供理论支撑。

索引覆盖

让我们回顾下InnoDB引擎普通索引的数据查询过程，我们知道每次查询都要进行一次回表，而回表是会降低查询效率的。那有没有办法不回表呢？当然是有的。

索引覆盖就是解决回表问题的。一句话总结，就是如果普通索引里面已经有我们所需要的数据了 ，那么此时就不需要再回表了。以上面聚集索引图为例：

select name from person where name like "小%"

此时由于普通索引就是根据name创建的，那么就不需要再去主键索引中重新检索数据了。

很多军规里面强调尽量避免select *,查询的时候，只取需要的列，其实也有这方面的考量，如果能利用索引覆盖这个特性，是可以大大提升查询效率的。

索引最左前缀匹配

结合索引的数据结构，对于一个person表(id,name,age)假设我们建立了一个(name,age)的联合索引，索引的结构图如下：

图虽简陋，但是仔细看还是容易得出联合索引的特点的：

• 多个索引值是以类似数组的形式存在节点上的。
• 第一个索引是有序的，而第二个索引只有第一个索引值相同的情况下，才是有序的，依次类推。(略微拗口，但是也容易理解)。

其实看懂了这个图，基本上也就明白了最左前缀匹配原则。

• 如果索引条件里面有name，或者name和age，那么是可以命中索引的。如果只有age，那么没法命中。

将这句话稍微抽象下就是：

如果一个表定义了一个联合索引a,b,c。那么条件里面，a,ab,abc都是可以命中索引的。如果没有a的话则是无法命中的。(你想啊，a都没影了，升下的都是乱序的，还怎么定位)

关于联合索引，还有个重要的特性，就是遇到范围查询，那么后续的索引也会失效。

例如：

select * from table_a where a>1 and b=10

a是有序的，当然会命中索引。然后a满足条件的可能是100条也可能是1000条，b命中索引的前提是a必须是确定的。这种情况下，b就无法再走索引了。

我们在建立联合索引的时候，尽量将离散的列放在前面。

其实还有个左前缀，就是字符串的左前缀匹配，一句话总结就是like 'xxx%'可以命中索引，like '%xxx%'无法走索引，这个还是比较简单的就不赘述了。

其实索引还有其他特性或者知识点：

例如索引下推是mysql 5.6开始提供的特性。核心就是对于一个联合索引，在回表之前将不符合条件的数据尽可能多的过滤掉，核心目的是为了减少回表。我个人理解算是mysql的一个bug fix或者一个优化项。感兴趣的可以搜下。

其实还有很多索引相关的知识点，有感兴趣的可以评论下方留言。

写到这里长舒了一口气，没写到吭吭哧哧写了这么多，真的是望断天涯路啊。

使用-索引的设计原则

衣带渐宽终不悔，为伊消得人憔悴。

上一讲中花了大量的时间，讲了很多八股文，这些原理和细节为什么重要？是因为只有懂得了这些原理，才能很容易明白一些索引相关的最佳实践。甚至懂得了这些原理之后，我们是可以自己来推导索引设计的最佳实践的。

其实很多的设计原则都是遵循了阿里集团mysql开发规约这里主要是结合原理，讲解下背后的逻辑。(当然，我也不可能把所有的设计规约贴出来，本章节主要是挑选一些有代表性的索引设计原则，关于规约详情，我文末放链接上)

以下几个规约节选自阿里巴巴开发规约，结合Mysql原理，加入了点自己的理解：

• 【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

我的理解:这个主要是为了防止脏数据。

• 【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

我的理解：其实结合索引的数据结构，不难理解，因为MySQL为了提升效率，会对索引进行缓存，过长的索 引影响缓存数量。

• 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

我的理解：左前缀匹配原则，模糊查询的话，不会走索引。

• 【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

我的理解：order by的条件如果设置了索引也是会走索引的，排序执行顺序在where之后，索引排序字段最好         放在联合索引的后面。

• 【推荐】利用覆盖索引来进行查询操作，来避免回表操作。

我的理解：参考覆盖索引的介绍。

• 【推荐】建组合索引的时候，区分度最高的在最左边。

我的理解：结合组合索引的数据结构就容易理解了。

• 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

我的理解：敲黑板，隐式转换会让索引失效。

有兴趣的话，可以详细看下阿里巴巴开发规约的索引部分，上面也有很多对规约的解读，相信弄懂了第一节MySQL原理部分，对于这些规约，一定会有更全新的认识和理解。

毫不羞耻的说，这一节写的要容易的多，毕竟有无数的大拿已经基于自己的经验，总结除了无数宝贵的经验。我们没必要重新创造，拿来就好。但是理解其中的缘由，理解并推导这些最佳实践的过程，才是最重要也是最宝贵的。

感悟-MySQL实践及理解

众里寻他千百度，蓦然回首，那人却在灯火阑珊处。

一段交付项目上的SQL分析

注明：处于数据安全性考虑，以下SQL都进行了脱敏处理。

另外以下故事纯属编造。

坦白讲，经历了原理的学习，最佳实践的熏陶，此时的我已经有点膨胀了，就如同刚刚学习了一本武林秘籍，已经迫不及待的打算参加武林大会一战成名了。于是我进入了交付项目，可接下来发生的事情，让我有点懵逼。

我从xxx项目中找了一个相对来说“短小”的慢SQL，因为其他动辄几百上千行的SQL实在也放不下：

SELECT DISTINCT

a.*,

b.bill_detail_id,

b.bill_id,

b.fromctrl_id

FROM

vou_head a,

ctrl_journal_cache c,

(

SELECT

b.vou_id,

b.bill_type,

b.bill_detail_id,

b.bill_id,

max( d.fromctrl_id ) AS fromctrl_id,

b.MOF_DIV_CODE,

b.fiscal_year

FROM

vou_bill_relation b

LEFT JOIN (

SELECT

  d.bill_id,

  bill_detail_id,

  vou_type_id,

  d.MOF_DIV_CODE,

  d.FISCAL_YEAR,

  d.ctrl_id AS fromctrl_id

FROM

  glb_ctrl_trace d,

  glb_account_cls e

WHERE

  d.is_primary = '1'

  AND e.account_cls_id = d.bgt_acct_cls_id

  AND e.mof_div_code = d.mof_div_code

  AND e.fiscal_year = d.fiscal_year

  AND ( ( e.balance_dir = 1 AND d.dr_cr = 2 ) OR ( e.balance_dir =- 1 AND d.dr_cr = 1 ) )

) d ON b.vou_type_id = d.vou_type_id

AND b.bill_detail_id = d.bill_detail_id

AND b.MOF_DIV_CODE = d.mof_div_code

AND b.FISCAL_YEAR = d.fiscal_year

AND ifnull( b.bill_id, ' ' ) = ifnull( d.bill_id, ' ' )

GROUP BY

b.vou_id,

b.bill_type,

b.bill_detail_id,

b.bill_id,

b.MOF_DIV_CODE,

b.fiscal_year

) b

WHERE

a.vou_status <> '-9' AND a.vou_id = b.vou_id AND a.mof_div_code = b.mof_div_code AND a.fiscal_year = b.fiscal_year

AND b.bill_type = c.bill_type_code AND b.bill_detail_id = c.bill_detail_id

AND ifnull( b.bill_id, ' ' ) = ifnull( c.bill_id, ' ' ) AND b.mof_div_code = c.mof_div_code

AND b.fiscal_year = c.fiscal_year AND a.mof_div_code = 'dddddd'

AND a.fiscal_year = 2022

AND ( b.bill_detail_id IN ( 'ddddd' ) )

ORDER BY

b.bill_detail_id

我擦，这个项目上相对简单的SQL怎么都这么复杂，书里的样例SQL不都是一行的么？

冷静下来分析，其实万变不离其宗：

已知情况：该SQL生产环境每次执行要10S以上。

• 首先，分析了下SQL中相关的表，发现其实每个表的数据量并不算特别大，最多的几十万条。
• 其次，SQL中涉及的表并不多，关联也不多。知识查询过滤条件多，并且有大量的聚合。

所以极有可能是由于索引设置不合理或者未命中所以导致。

接下来请出SQL优化的利器-EXPLAIN.

EXPLAIN含义

不免俗，还是要简单讲下EXPLAIN各个列的含义：

• id:SQL执行片段的ID，越大的越先执行。
• select_type:select子语句的查询类型
• table:执行的是哪张表，有些情况下会是<derived x>，表示是第X步执行的结果集。
• partitions:分区信息，分区表才会用到。
• type[关键]:表示访问类型，从优到差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

ALL的时候，数据量如果也很大的话，就要小心了，因为表示全表扫描。

• possible_keys:可能用到的索引，但不一定能命中。
• key[关键]：SQL语句最终确定使用的索引。
• key_len:索引的最大可能长度，一般分析的时候没怎么用到过。
• ref:表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名。好像也没怎么用到过。
• rows[关键]:预计要读取或者检查的行数，如果数据量过大，比如几万，几十万，那就要非常小心了。
• filtered：官方解释很拗口，大概就是SQL执行的时候，会分批扫描一部分数据，然后根据查询条件过滤之后，返回满足条件的数据。filtered=返回数据/扫描数据。过小的话，说明空扫了很多数据，是需要分析的。
• Extra[很关键]：额外的一些信息，对于SQL分析优化，也能提供不少参考，甚至非常重要的信息，举几个例子。

• Using temporary:用到了临时表来存储结果集，常见排序的场景。需要重点关注
• Using filesort:一般情况下排序都是在内存进行的，如果出现filesort，表示在磁盘进行排序了，可能是取的数据过多了。一般出现这个情况，一定是有性能问题了(排序都用到磁盘了，数据量不会少的，性能也快不了)
• Using join buffer:join的时候没有走索引，当然也是有性能问题的。

SQL分析改进

分析的过程就比较枯燥了，EXPLAIN之后，看是否命中索引，然后查看表索引的设置，分析SQL本身的问题。整改建议如下：

• 存在无意义关联，建议将fiscal_year字段的关联改写。该SQL查询条件中已经明确带了fiscal_year =2022的查询条件，建议将类似 a.fiscal_year = b.fiscal_year、b.fiscal_year = c.fiscal_year等关键条件改为b.fiscal_year = 2022、c.fiscal_year=2022
• 存在无意义关联，建议将mof_div_code字段的关联改写，改写方式同上。
• 5张表关联操作，建议将关联操作拆分为独立的SQL
• fiscal_year字段是varchar(4)类型，查询条件 fiscal_year =2022 传入整形值，存在隐式类型转换，建议改写为  fiscal_year =‘2022’
• 查询语句存在a.*的用法，如果该表执行DDL加列操作后，会出现业务报错异常，可能导致业务不可用，建议将 * 替换为具体的列名
• vou_head 创建vou_id,mof_div_code,fiscal_year的联合索引
• glb_ctrl_trace删除fiscal_year，mof_div_code索引，创建mof_div_code,fiscal_year索引

最终优化之后，大概执行300ms。

讲这个枯燥的案例，其实主要还是想表达几个观点和理解：

• 一个已经上线的系统，做SQL优化很痛苦。
• 慢SQL分析和优化，需要Case by case的分析。没有银弹，但是如果一开始理解mysql索引原理，遵循良好的索引规范，很多烂SQL是可以避免的，也就避免了这种痛苦。
• 优化的过程中可以借助Explain

其他常用工具

其实除了Explain命令，像show processlist，information_schema，mysqldump等都是用来定位分析慢SQL的利器。

光information_schema估计一个篇幅都讲不完，所以放到后面来说吧。

总结

唠叨了这么多，我们来回顾下，主要讲了什么，其实总结下来似乎也就几句话：

• MySQL索引的数据结构以及为什么选择B+Tree作为索引结构。
• 聚簇索引和非聚簇索引。
• 索引覆盖和联合索引的特点
• 索引的一些实践
• SQL优化及一些小工具

随着写文章的过程，自己也加深了对索引的理解，也发现很多关于知识点文章也还没来得及涉及和讨论，后续可以再开一篇关于索引的文章。

就像每个人都可以说五分钟脱口秀一样，每个人也可以写一篇技术布道文章。

--布谁的道？布技术的道吧。

--给谁布道？先给自己布道。