Alibaba Java开发手册索引规约学习笔记

最近一段时间再看阿里巴巴 Java开发手册索引规约，写篇帖子总结一下，索引规约内容如下

为了通用，更为了避免造数据的痛苦，文中所涉及表、数据，均来自于MySQL官网提供的示例库employees，可通过 https://launchpad.net/test-db/employees-db-1/1.0.6 自行下载。

首先把MySQL存储引擎和索引相关知识复习一下

一、存储引擎

　什么是存储引擎

　　MySQL中的数据用各种不同的技术存储在文件中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。我的理解是，数据存储使用不同的数据结构，使得它们各自支持不同的技术和功能，因此划分成不同的存储引擎。而存储引擎是对应表的，所以也被称为表类型。

　　给定一个服务器，真正使用哪几种存储引擎取决于你的Mysql版本，用show engines指令可以查看服务器支持的存储引擎。

　　

　　

1.InnoDB（MySQL默认存储引擎 从版本5.5.5开始）

支持事务，行级锁，以及外键，拥有高并发处理能力。但是在创建索引和加载数据时，比MyISAM慢。

2.MyISAM

不支持事务和行级锁。所以速度很快，性能优秀。可以对整张表加锁，支持并发插入，支持全文索引。

3.MEMORY

支持Hash索引，内存表，Memory引擎将数据存储在内存中，表结构不是存储在内存中的，查询时不需要执行磁盘I/O操作，所以要比MyISAM和InnoDB快很多倍，但是数据库断电或是重启后，表中的数据将会丢失，表结构不会丢失。

4. Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MYSQL5.1之前也不支持索引。Archive存储引擎不是一个事务型引擎，而是针对高速插入和压缩做了优化的简单引擎。

5.Blackhole引擎

Blackhole引擎没有实现任何的存储机制，它会丢弃所有的插入数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用。

6.CSV引擎

CSV引擎可以将普通的CSV文件作为MYSQL的表处理，但是这种表不支持索引。

7.Federated引擎

默认是禁用的

8.Merge引擎

Merge引擎是MyISAM引擎的一个变种。Merge表是有多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被废弃（可以参考高性能MySQL【第三版】第七章）

选择合适的引擎

InnoDB 是MySQL-5.5.5后默认的存储引擎，一般来说不是有太多人关心这个东西，但是还是值我们去研究一下，这里的文章只考虑 MyISAM 和InnoDB这两个，因为这两个是最常见的。

MyISAM or InnoDB?

下面先让我们回答一些问题：

• 你的数据库有外键吗？
• 你需要事务支持吗？
• 你需要全文索引吗？
• 你经常使用什么样的查询模式？
• 你的数据有多大？

思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。

外键：

　　如果你需要事务处理外键，那么InnoDB 可能是比较好的方式

事务：

　　如果应用需要事务支持，那么InnoDB是目前最稳定经过验证的的选择。

　　如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。

全文索引：

　　如果要用到全文索引，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文MyISAM。

备份：

　　如果可以定期关闭服务器来执行备份，那么备份因素可以忽略。反之，如果需要在线热备份，那么选择InnoDB就是基本的要求。

崩溃恢复：

大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据量较大 的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。因为，即使不需要事务支持，很多人也选择InnoDB引擎，这是一个非常重要的因素。

对于如何选择存储引擎，可以简单地归纳为一句话：“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应优先选择InnoDB引擎”。

各引擎对比

二、索引

什么是索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响越发重要。在数据量小且负载较低时，不恰当的索引对性能的影响可能还不明显，当数据量逐渐增大时，性能则会急剧下降。

索引的类型

1.B-Tree（B+Tree）索引

2.哈希索引

3.空间数据索引（R-Tree）

4.全文索引

当人们谈论索引的时候，如果没有特别指明类型，那么多半说的是B-Tree索引，大多数MySQL引擎都支持这种索引。本文将只关注于BTree索引，至于哈希索引、R-Tree和全文索引本文暂不讨论。

BTree索引的数据结构及算法原理可以参考  http://blog.codinglabs.org/articles/theory-of-mysql-index.html

对BTree节点的插入、查找、删除以及因为插入删除带来的数结构调整请参考《大话数据结构》第八章8.8节多路查找树

-----------------------------------------------------------------------华丽的分割线---------------------------------------------------------------------------

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

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

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

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

　　　　　这个我觉得不能算强制吧，数据量大可以，很多项目数据量都能小，一年都没10万数据，join 4 5张表我都见过,但是关联的字段需要有索引是必须的。

　　　　　如果join的字段数据类型不一致会导致索引不可用，查询速度变慢。

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

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

　　　　　在保证区分度的基础上，尽量减少索引文件的大小。但缺点是：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

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

　　　　　说明： 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定（查询条件没有指定索引第一列），那么无法使用此索引。

　　　　   导致索引失效原因有

　　　　　　（1）查询条件没有指定索引第一列，由于不是最左前缀，索引这样的查询显然用不到索引。如：explain  SELECT * FROM employees.titles WHERE from_date='1986-06-26';

　　　　　　（2）对索引列进行运算导致索引失效(+，‐，*，/，!等)如：explain  SELECT * FROM employees.titles WHERE emp_no - 1='10000';

　　　　　　（3）如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 如：explain SELECT * FROM employees.titles WHERE emp_no='10001' or title='Senior';

　　　　　　（4）更多的like（通配符%出现在开头）导致用到索引。如explain SELECT * FROM employees.titles WHERE  emp_no like '%10001';（emp_no为主键）

　　　　　　（5）因字段类型不同造成的隐式转换， 导致索引失效。见11点

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 无法排序。

　　　　　可以根据explain来查看是不是user filesort

6. 【推荐】利用覆盖索引来进行查询操作， 避免回表。说明： 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
　　　　   正例： 能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种效果，用 explain 的结果， extra 列会出现： using index

　　　　　实例参考：http://www.cnblogs.com/allenli263/p/7355257.html

7. 【推荐】利用延迟关联或者子查询优化超多分页场景。
　　　　   说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
　　　　   正例： 先快速定位需要获取的 id 段，然后再关联：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

　　　　   实例参考：http://www.cnblogs.com/allenli263/articles/7351107.html

8. 【推荐】SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如果可以是 consts最好。
　　　　　说明：
　　　　　　1） consts 单表中最多只有一个匹配行（主键或者唯一索引） ，在优化阶段即可读取到数据。
　　　　　　2） ref 指的是使用普通的索引（normal index） 。
　　　　　　3） range 对索引进行范围检索。
　　　　   反例： explain 表的结果， type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

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

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

　　　　　  这一点应当引起重视，也是开发中经常会犯的错误.由于表的字段XX定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给MySQL。

　　　　　  我们把departments中最后一行数据的dept_name改成123456789并添加一列dept_address

　　　　　　

　　　　　　 原因：dept_name为VARCHAR类型，SELECT * FROM employees.departments WHERE dept_name=123456789;中值123456789没有加引号，出现字段的隐式转换，导致索引失效。

11. 【参考】创建索引时避免有如下极端误解：
　　　　　  1） 宁滥勿缺。 误认为一个查询就需要建一个索引。
　　　　　  2） 宁缺勿滥。 误认为索引会消耗空间、严重拖慢更新和新增速度。
　　　　　  3） 抵制惟一索引。 误认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

参考文献

[1] Baron Scbwartz等 著，　宁海元等 译；高性能MySQL（第三版）（High Performance MySQL）；电子工业出版社，2010

[2] 简朝阳 著，MySQL性能调优与架构设计；电子工业出版社，2009