Mysql 索引使用规则和设计优化

大部分情况下，尤其是记录数量较少的情况下Mysql总是能正常运转的很好，但不可避免的，随着数据库记录数的增长以及SQL语句越来越复杂，总会有一些实际效果与数据库或SQL设计人员理解相违背的情况，这就需要开发者对Mysql的原理和存在的问题有一个基本的认识。本文主要探讨了Mysql索引的使用和相关知识，这些知识并不复杂，不需要专业的数据库学习经验就能搞明白，理解了这些可以帮助开发人员更好的进行数据库索引设计和SQL查询语句的编写。

1. Mysql 是如何使用索引的

• MyISAM不支持事务，而Innodb支持。
• MyISAM索引和数据的存储是分开的（不同的文件），索引中最终检索到的是数据的物理地址偏移量。而InnoDB中，索引段和数据段在同一个文件中的不同段，查到索引后可以直接取出数据。
• MyISAM是非聚集索引，而Innodb则是聚集索引。

所谓聚集索引是指索引和数据的逻辑排列顺序与实际物理存储顺序一致，新华字典就是典型的聚集索引，字（叶子索引）和释意（数据）靠在一起，且按一定顺序排列的。而“非聚集索引”则相反，索引单独放在一块区域，并且叶子节点存放的是数据的地址偏移量。

1.1 MyISAM存储引擎

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

select * from table_name where id = 3

其中id为主键，那么首先检索的是索引，索引中经过2层查找，找到了索引为3的节点，值为0xABAB，代表了从.myd文件中偏移量为0xABAB的地方开始读取一行的数据。辅助索引对应普通索引，存在相同的键值。

1.2 Innodb存储引擎 

 select * from table_name where id = 3

其中id为主键，那么首先检索的是索引段，索引段中查找下个索引范围是通过地址偏移随机访问来实现的（这个步骤还是高效的），查找到对应叶子索引节点后，需要顺序的遍历和检索该叶子节点找到对应的索引值对应的节点（这个步骤是遍历，比较耗时），就可以立马读出数据了。

Innodb的表存储结构由段、簇（区）、页组成，一个段由若干簇组成，一个簇默认有64页，每页16KB。

1.3 Mysql索引选取规则

1. 寻找可能的（可选）索引：根据用户的WHERE条件，查看每个字段是否匹配某个索引，如果匹配，就把这个索引加入待选列表中。所谓字段匹配索引有两种情况：1）某个查询字段上建立了单列索引；2）某个查询字段按照最左匹配原则（下文有详细描述）匹配了某个组合索引,即为该组合索引的第一列。3）某几个查询字段按照最左匹配原则匹配了某个组合索引。可选索引列表可使用EXPLAIN查看（possible keys）[3]。待选列表如果为空，GOTO 3。
2. 索引择优算法：Mysql的索引择优算法很复杂，一般来说有这几个影响因素：1）索引对应的扫描行数，在没有Order by的情况下，一般扫描行数少的索引会被选择；2）查询语句中有Order By或者group by时，如果不使用Order By后的字段做索引的话，filesort（对应索引排序，索引排序很快，而filesort则需要对结果集进行实时排序，所以很慢）会被使用，这时候Mysql给filesort的负权重很高，很容易导致Mysql放弃最优索引（哪怕该索引估计扫描行数比实际使用的索引对应的扫描行数小很多），转而使用Order By之后的索引字段。我们在生产中遇到过这样的情况，部分文献也有记载[6]；3）Limit 限定也会影响索引的使用，甚至Limit后的值也会影响索引的使用（有时候确实会令人费解）；4）话不能说满，官网文档写的实在含糊，所以这里不敢打包票没有其他影响因素了。如果优选的扫描索引不为空，GOTO 4。
3. 如果没找到可用索引的话，再考察查询字段，也就是Select之后Where之前的那些字段。寻找查询字段所匹配的索引，并得到一个可用索引结果集。根据最小估算扫描行数优先原则，可以得到最优的索引。如果可用索引结果集仍然为空，那么就会使用全表扫描(Full table scan)。
4. 如果根据最终选择的索引估算出的扫描行数占据了表的很大一部分比例，那么Mysql优化器可能会放弃使用该索引，而退化为使用全表扫描。这是因为使用索引在有些情况下并不高效，比如索引出来的数据量很大，需要频繁的改变文件读取指针去获取数据块，可能效果还不如从头到位把整个表都扫描一边，也省去了去查找索引和频繁重定向读取指针（尤其在磁盘存储器[4]上）带来的开销。

5. 如果到这步Mysql搜索优化器仍然决定用某个索引，那么就会在实际查询时使用该索引了。这个索引也是EXPLAIN分析语句结果集中key的值。

关于使用索引随机读取大量记录和顺序读取大量记录之间的取舍问题，本文并没有去研究Mysql在优化的时候是否考虑到了存储器的类型，比如是磁盘还是SSD，对于SSD这种高效随机存储器来说，频繁重定向读取指针几乎不耗时。如果没有考虑到，而只是给这种频繁读取操作预设了一个成本常量（消耗的时间）参与估算的话，可能优化结果并不恰当。

1.4 何时会全表扫描

• 1.目标数据表太小了，再去查找索引（key lookup）太麻烦了（有点杀鸡焉用牛刀的即视感）。这通常发生在10行都不到的数据表，并且每行很短的情况。（注：10这个数字不可靠，这里只是感性的说了个数字，可能小几十行的数据仍然会触发全表扫描。）
• 2.查询条件中的字段（WHERE后）没有匹配到索引的情况。（也不是说匹配不到就一定会全表扫描，见下文默认索引选择算法）
• 3.查询条件中的字段与某个常量比较时（就比如where age > 8)，并且使用这个常量值与对应索引筛选出的记录数占了总数的大部分。优化器认为扫这么大的数据还不如扫全表了，所以选择了扫描全表。大部分怎么定义恐怕只有Mysql开发者才知道，官网也并没有给出具体数值。
• 4.查询语句匹配到的索引对应的基数太小（对应SHOW INDEX FROM table_name结果中的Cardinality )，并且此时又有其他列上的查询条件（比如:select * from user where user_status > 0 and username =’tommy’）。所谓基数就是表中某列所有值的取值种数，比如一张表有5行，某一列对应的值分别为：1,2,3,3,2。那么该列基数就是3，因为一共有三种取值：1,2,3。基数小，意味着该索引中每个索引值对应的目标记录数很大，在这个索引值对应的记录数中再去一个个的检查其他列上的条件是否满足，整个过程总体的查找速度还未必有全表扫描来得快。

经测试，第3点当且仅当比较符号为非等于时才生效。如果使用了等于号，那么只要该列有匹配的索引，一定会命中，哪怕基数为1。所以当查询条件中有基数小的列时，某个索引值的条件只是从等于号改成小于号，就可能从使用索引退化到扫描全表。查看某个SQL语句是否使用某个索引靠谱的做法只有一个——使用Explain语句分析SQL。

我们在category上创建了一个索引，然后我们使用以下SQl语句进行查找:

Select * from Animal where category > 1 and name = "asaf"

这时候category对应的索引数据会如下所示：

2. 多列索引(组合索引)

2.1 组合索引的数据构成

那么索引数据就会像一样：

注意：这是官方文档上打的一个比方。实际查询条件可能是”>、<、<=”这样的范围比较符号，需要对每一列作比较，所以不会真正的连接成一个字符串，这里只是一个形象的比喻，告知大家组合索引和普通索引在数据构成上其实没啥区别。

2.2 组合索引的一种替代方案

SELECT * FROM tbl_name  WHERE hash_col=MD5(CONCAT(val1,val2))  AND col1=val1 AND col2=val2;

很显然，这种方法只适用于精确匹配的情况，如果用到了范围比较符号（>,<,>=等），那就无法使用了。

2.3 组合索引的命中规则——最左匹配原则

下面我们再来看看哪些查询会使用到我们定义的组合索引。假设一张表的定义如下：

CREATE TABLE test (    id         INT NOT NULL,    last_name  CHAR(30) NOT NULL,    first_name CHAR(30) NOT NULL,    PRIMARY KEY (id),    INDEX name (last_name,first_name));

SELECT * FROM test WHERE last_name='Widenius';SELECT * FROM test  WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius'  AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test  WHERE last_name='Widenius'  AND first_name >='M' AND first_name < 'N';

如果一个表有一个组合索引，那么任何符合**最左匹配原则**的查询条件都会触发优化器使用该索引进行数据查找。比如，有一个三列的组合索引(col1, col2, col3),那么当查询：where col1=xxx 、where col1=xxx and col2=xxx、where col1=xxx and col2=xxx and col3=xxx时，都会触发该索引。

最左匹配原则——在对查询条件中的字段进行组合索引的匹配时，只考虑匹配其前N个字段，比如前一个（第一个）、前2个、前3个字段等。其他情况视为未匹配。

1. SELECT * FROM tbl_name WHERE col1=val1;2. SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;3. SELECT * FROM tbl_name WHERE col2=val2;4. SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

** 默认索引选择算法**——当查询语句的搜索条件没有命中任何索引时，Mysql索引优化器会考量查询语句中的目标字段（select后面，where前面的部分），目标字段除去主键外，如果恰好是某个索引（包括组合索引）对应列的子集，那么该索引也会被使用。如果满足的索引有多个，将会使用索引记录数最少的索引。这个算法在[3]中得到了旁证。

SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test  WHERE last_name='Widenius' OR first_name='Michael';

3. 索引设计建议

1. 索引不是定义的越多越好，对于查询条件比较多的情况，避免为每个字段创建索引，只需要创建一个联合索引即可。创建联合索引时，把可能存在单列查询的那一列放前面。比如业务需求要求以下几种查询：

   这时候，组合索引应该建成为:
   
   

   这时候上述三种查询都会使用到该索引。

2. 创建联合索引时，值差异化大的列放在前面，而不是那些取值种类很少的列。比如用户名和用户状态两列，创建索引的顺序应当是index(用户名、用户状态)，而不是index(用户状态、用户名)。如果把基数很小的用户状态放在第一个，那么如果恰好查询语句条件是“用户名=某个值 AND 用户状态>某个值”时，Mysql索引优化器很可能根据用户状态得出要扫描的行数太多，退化为全表扫描，而后面的精确匹配用户名的条件就没用上了。
3. 由于Mysql索引优化器的存在，有时候会出现很多意向不到不使用索引的情况。所以每次写无法确定使用哪个索引的Sql语句时（尤其是WHERE条件后是>,<等范围选择时），一定要多用EXPLAIN语句进行分析。
4. 可以认为Mysql在执行SQL语句时，一个表只可能使用一个索引（开启了Index Merge的情况除外）。新人在建表的时候总是会忽略这个事实，从而为很多列单独建立了索引，认为这样会更快。
5. Mysql索引优化规则是一个官网都没说清的问题，在复杂SQL的情况下不可避免的会产生一些事与愿违的情况（已知的影响因素1.1中有提到），导致Mysql很蠢的使用了不该使用的索引（这种情况的确会存在[6]，这也佐证了Mysql的执行计划是估算出来的，并不总是靠谱）。实际使用中发现索引使用错误的情况，可以使用Force Index/Use Index等引导Mysql搜索优化器使用某个索引，这里有一些可选的解决方案有兴趣的也可以看看[6]。

参考文献：

[1]. How MySQL Uses Indexes. https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

[2]. Multiple-Column Indexes, https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

[3]. EXPLAIN Output Format. https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_key

[4]. 磁盘存储器. http://www.baike.com/wiki/%E7%A3%81%E7%9B%98%E5%AD%98%E5%82%A8%E5%99%A8

[5]. Avoiding Full Table Scans. https://dev.mysql.com/doc/refman/5.7/en/table-scan-avoidance.html

[6]. 7 ways to convince MySQL to use the right index. http://code.openark.org/blog/mysql/7-ways-to-convince-mysql-to-use-the-right-index