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

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

1. Mysql 是如何使用索引的

索引可以帮助我们快速的找到包含指定列值的行。假如没有索引的话，Mysql必须从第一行开始查找整个表，才能找到我们想要的那些行。如果没有索引，表越大，花费的时间也就越大。如果我们在查询条件中指定了某几个列的值，并且这个表恰好有一个建立在这些列上的索引，那么Mysql就可以从数据文件中快速的定位到数据所在的位置，而不用查找整个数据文件。这比不断的一行行读取数据快多了[1]。大部分Mysql索引（Primary Key、Unique index和FullText）都通过B树来存储和实现。也有一些例外：空间数据类型使用的索引是基于R-树的；内存表还支持哈希索引；InnoDB为Fulltext索引使用了逆转链表[1]。 本文不打算去赘述B树的原理和创建过程，有兴趣的可以点击B树了解。假设现在索引已经创建完毕了，那么Mysql是如何查找到我们需要的数据的呢？下面我们就MyISAM和Innodb两种不同的存储引擎做讨论。关于MyISAM和Innodb我们需要知道的有：

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

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

下面4张图分别为MyISAM和Innodb的主索引和辅助索引逻辑图：

1.1 MyISAM存储引擎

 

 

 

 

 在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

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

假设有以下语句

select * from table_name where id = 3

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

1.2 Innodb存储引擎

 

 

 

 

  在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分（只需要开启innodb_file_per_table),切分后存放在xxx.ibd中，默认不切分，存放在xxx.ibdata中。 假设有以下语句

 select * from table_name where id = 3

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

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

1.3 Mysql索引选取规则

严格意义上讲，如果我们在某一张表上建立了多个索引，那么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 何时会全表扫描

Mysql搜索优化器最终决定使用全表扫描一般有以下几个场景[5]：

  • 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。

第4个场景和第3个有点类似，Mysql优化器都是觉得用索引带来的扫描次数和全表扫描没啥区别，用索引还要承担额外频繁切换文件读取指针带来的开销，所以还不如使用全表扫描。第4个场景可能有点难懂，所以举一个例子来说明。比如一张表Animal有以下数据：

id     |  category  |  name    |  other fields
1            1         asaf          ...                                                    
2            2        ikashef        ...
3            3         lksd          ...
...         ...        ...           ...
9999         3         asdf          ...
10000        2         adsfa         ...

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

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

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

1 -->  1,...k
2 -->  2,...p...10000
3 -->  3,...q...9999

假如使用了Category索引，那么记录检索器第一步会匹配出满足的索引值，即2,3（>1)，而2,3对应的所有数据集都会被比较。待扫描的记录行数可能占了整个表的大部分，Mysql搜索优化器最终选择放弃该索引，退化为全表扫描。

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

Mysql支持创建组合索引（其实就是同时在多个列上创建索引）。一个索引最大可以包含16个列。对于某些数据类型的列来说，你还可以对其前缀进行索引（[前缀索引(https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-prefix)]）[2]。当查询条件匹配了索引中的所有列、第一列、前二列、前三列等时，Mysql就会使用这个多列索引，如果我们在定义索引的时候就安排好列的顺序，一个单独的组合索引总是可以加快好几种查询。换句话说，定义好一个组合索引，只要某个查询用到了里面的某些字段，很可能会命中这个索引[2]。

2.1 组合索引的数据构成

单列索引的数据构成很简单，以B树实现为例，每一个索引值对应了一个树的节点，通过树的节点可以快速找到最终对应了哪些记录。那么组合索引的索引值构成又是啥样的呢，我们可以把多列索引（组合索引、混合索引)的索引数据当作一个排序好的数组， 索引数据的每一行就是由这些索引列对应的值组合起来的字符串[2]。比如对于index (a, b, c)来说， 数据库中有数据：

1..."1","2","3"...
2..."6","4","5"...
3..."3","2","9"...
4..."1","2","3"

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

[1] [2] [3]  --> 1,4
[3] [2] [9] --> 3
[6] [4] [5] -->2

当然了，这里是从上至下的排序，真正Mysql使用的索引数据结构一般是是树状的，但本质也是排序好的。所以我们可以想象的到，如果第一个的组合索引字段差异化很小（基数很小）的话，查询效率不会很高，因为很多索引节点的第一个值都是一样的，使用索引筛选出的集合还是很大，Mysql搜索优化器就会估算出了一个很大的待扫描集合，然后退化为全表扫描。

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

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

官方事实上，除了使用多列索引外，我们还有一个选择，那就是在表中增加一列哈希列，哈希列的值计算自其他的某几列。如果哈系列很短，唯一性好，并且加了索引，那么它将比直接使用组合索引快得多。在Mysql中，使用这个哈希列很简单，比如[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)
);

name索引是一个建立在last_name和first_name上的索引。 这个索引会被那些为last_name和first_name字段指定了已知范围的查询所使用，当然了，他也会被只指定了last_name的查询所使用，因为只指定last_name的情况恰好符合了**最左索引匹配原则**。我们举些例子来说明下，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个字段等。其他情况视为未匹配。

而如果某个查询不满足最左原则，那么Mysql将不会使用该索引。下面我们来举几个例子，如下所示的4个查询语句：

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;

假设我们在 (col1, col2, col3)上创建了索引，那么只有1和2语句命中了该索引。后面两条语句则不会命中，因为他们不满足**最左匹配原则**。 然而打脸的是实际情况中我们在Mysql中做实验的时候，会发现3,4也命中了该索引。 这是因为触发了默认索引选择算法选取索引。当Mysql没找到适合的索引，准备退化到全表扫描前，会使用一个默认索引选择算法。Mysql认为只要能找到这样一个索引，总会比全表扫描好一点。

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

比较巧的是，*代表了所有字段，除去了主键外，其他3个字段刚好在上述索引中，所以上述索引被命中。这时候只需要表中再多一个没有被索引的字段col4,索引就不会被命中。

还是刚刚的例子，以下查询语句将不会命中name索引：

SELECT * FROM test WHERE first_name='Michael';

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

又一次打脸的是，如果你真的建了这么一个表来做实验，使用Explain命令进行执行分析时，上述语句的分析结果可能提示使用了该索引。还是因为触发了默认索引选择算法。

3. 索引设计建议

针对以上讨论的Mysql索引的选取规则和命中规则，可以总结出以下开发过程中需要注意的地方：

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

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

    index(col2, col3, col1)

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

  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搜索优化器使用某个索引
posted @ 2020-07-11 09:34  一个人的孤独自白  阅读(1741)  评论(0)  编辑  收藏  举报