阿里巴巴Java开发手册第五章MySQL数据库-索引规约篇

在我们使用数据库书写sql的时候，对于sql优化，使用索引是必不可少的。MySQL中常用的索引:

• 普通索引: 最基本的索引，它没有任何限制。
• 唯一索引: 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 主键索引: 一种特殊的唯一索引，不允许有空值。
• 全文索引: 仅可用于MyISAM，针对较大的数据，生成全文索引很耗时耗空间。
• 组合索引: 为了更多的提高MySQL效率可建立组合索引，遵循”最左前缀”原则。创建复合索引时应该将最常用(频率)作为限制条件的列放在最左边，依次递减。

关于索引还会在mysql分类中再写一篇相对详尽的文章，敬请期待。

孤尽详述了关于业务、字段设计、MySQL用法等方面的索引规约，接下来让我们来逐一分析一下。

1.[强制] 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

说明: 不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的;另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

老四附言:

来了解一下墨菲定律的定义，引自维基百科:

墨菲定律(英语:Murphy’s Law)，又译为摩菲定理，具体内容是”凡是可能出错的事必定会出错”，指的是任何一个事件，只要具有大于零的机率，就可确定它必会发生。

在科学和算法方面，它与英文所谓的“worst-case scenario(最恶劣的情况)”同义，数学上用大O符号来表示。例如，对插入排序来说，最恶劣的情形即是要排序的阵列完全倒置，必须进行 n*(n-1) 次的置换才能完成排序。在实验上，证明了最恶劣的情况不会发生，并不代表比它轻微的情形就不可能，除非能够很有信心的推论事件的概率分布是线型的。

在文化方面，它就代表着一种近似反讽的幽默，当作对日常生活中不满的排解。

创建唯一索引的sql示例:

2.[强制] 超过三个表禁止join。需要join的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。

说明: 即使双表join也要注意表索引、SQL性能。

老四附言:

看看孤尽自己是怎么解释这条的:

任何多次join运算，可以理解成两两join，任何join运算，可以理解成通过某个条件在应用进行遍历获取数据。三张表及以上的join运算有太大的血泪史证明有性能问题。

其实关于这一条的规定，网上的探讨还是比较多的。老四个人来看，不谈业务场景的情况下，人家解释的非常合理，表的join应该控制在3个以下。但是对于具体的项目以及具体的表设计，老四个人觉得该项应该根据具体情况来决定的，比如说你查一个订单，订单里面存了省份编码、城市编码以及区县编码，这就完全可以使用三个join查询出来他们对应的中文名称，这里禁止三个表join我觉得那显然是不合适的。其实这里可以优化一下说法，就是根据具体的业务场景，如果遇到数据量大或者显然多表join会带来性能等问题的时候我们要考虑表的两两join，但是涉及到一些基本的简单查询在牺牲一点点性能的基础上使用多表join也是允许的。

附上关于join(表的连接，关联)的基本形式:

• 左连接(即以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。)

• 右连接

• 内连接(查询左右表都有的数据，不要左/右中NULL的那一部分)

• 左右连接是可以相互转化的，可以把右连接转换为左连接来使用(并推荐左连接来代替右连接，兼容性会好一些)

• 外连接用union实现

3.[强制] 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

说明: 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名，索引长度))/count(*)的区分度来确定。

老四附言:

孤尽其实说的就是MySQL中的前缀索引。有很多时候我们需要索引很长的字符字段列，如果我们将其加索引会导致索引很大，得不偿失。所以如孤尽所说，20个字符算是实战经验。但是这样的索引方式带来的问题就是索引的选择性，即说明中的区分度的概念，即不重复的索引值，一般情况下前缀的区分度是足够高的，除非你的那一列真的都是以同样的文字开头。

前缀索引的优缺点:

• 优点: 前缀索引是一种能使索引更小，更快的有效办法
• 缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

4.[强制] 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明: 索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

老四附言:

B树:之于二叉树，B树这种数据结构经常用于实现数据库的索引。因为在涉及磁盘的I/O方面，它的性能比二叉树好很多。磁盘IO的次数由树的高度来决定。

从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree就在这样伟大的背景下取代了常用的二叉树。

一个m阶B-Tree满足以下条件:

• 每个节点最多拥有m个子树
• 根节点至少有2个子树
• 分支节点至少拥有m/2颗子树(除根节点和叶子节点外都是分支节点)
• 所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

另外因为在B树种数据的比对是在内存中完成中，不涉及到磁盘IO，耗时可以忽略不计。B树中一个节点中可以存放很多的key(个数由树阶决定)。相同数量的key在B树中生成的节点要远远少于二叉树中的节点，相差的节点数量就等同于磁盘IO的次数。这样到达一定数量后，性能的差异就显现出来了。

即使我们在网页搜索中禁止使用左模糊或者全模糊，但我们在使用like的时候也要注意优化:

• like ‘%xxx’ –建立反序索引
• like ‘xxx%’ –直接建立索引
• like ‘%xxx%’ –建立起索引作用的表，查询该表性能高或者使用检索

ps:如果%出现在字段前面，则该字段即使添加了索引也会失效

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

说明:

• 正例: where a =? and b =? order by c; 索引: a_b_c
• 反例: 索引中有范围查找，那么索引有序性无法利用，如: where a>10 order by b; 索引a_b无法排序。

老四附言:

在MySQL中的ORDER BY有两种排序实现方式:

• 利用有序索引获取有序数据
• 文件排序

在使用explain分析查询的时候，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。

除了孤尽所说的，如果不是组合索引，我们查询的数据和order by的字段数据如果不是同一字段就很容易发生文件排序，这样会影响性能，类似说明中的反例。
在总结几个知识点供您参考:

• MySQL在查询时最多只能使用一个索引。因此，如果WHERE条件已经占用了索引，那么在排序中就不使用索引了。
• 只有当ORDER BY中所有的列必须包含在相同的索引，并且索引的顺序和order by子句中的顺序完全一致，并且所有列的排序方向（升序或者降序）一样才有效果。
• where 语句与ORDER BY语句组合满足最左前缀:如果查询联接了多个表，只有在order by子句的所有列引用的是第一个表的列才可以。

6.[推荐] 利用覆盖索引来进行查询操作，避免回表。

说明: 如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗?目录浏览一下就好，这个目录就是起到覆盖索引的作用。
正例: 能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现: using index 。

老四附言:

覆盖索引的概念:

如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作

判断标准:

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

注意:

• 覆盖索引也并不适用于任意的索引类型，索引必须存储列的值
• Hash和全文本索引不存储值，因此MySQL只能使用B-TREE
• 不同的存储引擎实现覆盖索引都是不同的
• 并不是所有的存储引擎都支持它们
• 如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做

关于InnoDB引擎中覆盖索引的一些知识点:

• 覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的聚集索引列
• InnoDB的索引结构是B+树索引存储，即数据行即索引，索引即数据
• 对于InnoDB的辅助索引，由于叶子节点存储的是索引值和指向主键索引的位置，查询的时候需要通过主键在查询表的字段值，所以辅助索引存储了主键的值
• 覆盖索引也可以用上InnoDB默认的聚集索引
• InnoDB引擎的所有储存了主键ID、事务ID、回滚指针、非主键ID，他的查询会使非主键ID也可覆盖来取得主键ID

覆盖索引是一种非常强大的工具，能大大提高查询性能，因为只需要读取索引而不用读取数据，所以在来总结一下覆盖索引的一些优点:

• 索引项通常比记录要小，所以MySQL访问更少的数据
• 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
• 大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引
• 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

7.[推荐] 利用延迟关联或者子查询优化超多分页场景。

说明: MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例:先快速定位需要获取的id段，然后再关联:

老四附言:

什么是延迟关联:
使用索引查询出来数据，之后把查询结果和同一张表中数据进行连接查询，进而提高查询速度!

用过分页的我们都应该体会过，随着查询页数变大，查询时间会越来越长!延迟关联就是用来避免这种情况的。

简单说一下原理:其实这种关联利用的是索引先找到数据的位置，然后在进行分页操作，从而提高查询效率，不信的话explain试一下便知。

8.[推荐] SQL性能优化的目标: 至少要达到range级别，要求是ref级别，如果可以是consts最好。

说明:

• consts单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
• ref指的是使用普通的索引(normal index)。
• range对索引进行范围检索。

反例: explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

老四附言:

关于MySQL中explain命令以及explain命令的结果资源含义请参考老四的另外一篇文章:《mysql中执行explain命令后显示的各个字段都是什么含义?》

9.[推荐] 建组合索引的时候，区分度最高的在最左边。

正例: 如果 where a =? and b =?，a列几乎接近于唯一的值，那么只需要单建idx_a索引即可。
说明: 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a >? and b =? 那么即使a的区分度更高，也必须把b放在索引的最前列。

老四附言:

非等号代表着范围性，所以在多条件下，尽量把前列索引建立在等号上面

10.[推荐] 防止因字段类型不同造成的隐式转换，导致索引失效。

老四附言:

表设计的时候保证各个表相同字段的类型一定要一致，比如订单表里面的商品编号和商品表里面的商品编号如果是varchar类型，那么两张表都要是varchar类型。

11.[参考] 创建索引时避免有如下极端误解:

• 宁滥勿缺。认为一个查询就需要建一个索引。
• 宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
• 抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

老四附言:

总结起来就是，具体情况具体分析，不好办也得办，数形结合找答案。此篇完毕。

更博不易，如果觉得文章对你有帮助并且有能力的老铁烦请赞助盒烟钱，点我去赞助。或者扫描文章下面的微信/支付宝二维码打赏任意金额，老四这里抱拳了。赞助时请备注姓名或者昵称，因为您的署名会出现在赞赏列表页面，您的赞赏钱财也会被用于小站的服务器运维上面，再次抱拳。