|
MySQL性能优化主要包含3个方面，从微观到宏观依次为： SQL优化：当然其中必然包含索引的优化，表设计优化，SQL拆分，查询缓存优化等问题； 服务器参数优化：比如各种全局缓存的微调（连接数配置, InnoDB缓冲池相关配置等），每个连接需要的各种缓存微调，IO参数调整（比如日志缓冲刷盘方式），并发配置等； 服务器架构优化：比如主从复制，垂直拆分，水平拆分，甚至业务上增加缓存层等都能直接减轻数据库压力，从而降低响应时间。 当然还有服务器硬件方面地调整，也会有显著效果。本文主要阐述SQL优化中的索引部分。 在谈到索引的时候，我们需要明确的是，适当的索引在多读场景中能够显著的提升数据库的查询效率，如果业务场景是写多读少，就要谨慎地使用索引了，虽然他能够在更新的时候（比如你的索引字段只出现在WHERE子句中）提升少许性能，但是带来的负面影响可能更大，比如写入数据时需要额外的索引维护，以及索引更新时导致的锁问题。 MySQL架构图大家都很熟悉，就不贴了，此处和索引相关的主要是服务器端和存储引擎之间的交互部分。 服务器端会根据成本模型生成最优的查询执行计划，这份查询执行计划会对SQL语句进行优化，例如关联表顺序的调整，使用等价变化规则来简化表达式，IN子句优化，提前终止查询等。 查询执行计划决定了服务器端以哪种方式从存储引擎获取数据，比如通过主键获取(rnd_*接口[1])或者通过二级索引获取(index_*接口[2])。查询计划被发送给存储引擎，存储引擎通过主键或者二级索引检索数据，然后把结果返回到服务器端，服务器端根据WHERE条件中的其他子句再进行过滤，最后才返回给客户端。 需要注意的是存储引擎端只会根据主键或者二级索引的值来过滤数据行（当出现覆盖索引的时候，则不需要再回表查询数据行），不会做其他非索引字段的过滤，如果有其他非索引字段的过滤，则需要返回到服务器端再做过滤（查询执行计划中，extra列出现using where）。和客户端从服务器端获取数据的方式类似，服务器端从存储引擎端获取数据也是通过游标的方式逐行获取的。 索引的定义是存储引擎用于快速找到记录的一种数据结构。所以索引一定是针对存储引擎而言的，不同的存储引擎各自的索引实现各有不同。 InnoDB存储引擎采用B+Tree的数据结构来存储索引和数据，所以对顺序查找，最左前缀匹配，前缀列匹配，范围匹配的查询支持非常好。InnoDB存储引擎不支持用户自定义的哈希索引，自身实现的时候会依赖自适应性哈希索引来提高根据二级索引查找主索引的效率。 InnoDB存储引擎采用聚簇索引（clustered index）的数据存储方式来做物理存储。聚簇索引同时保存了索引和数据页，而且二者相邻存储，即，主索引值相邻的数据页相邻存储。聚簇索引就是按照每张表的主键引构成一个B+树，并且叶子节点中存放着整张表的行记录数据，因此也让聚簇索引的叶节点成为数据页，每个数据页都通过一个双向链表来进行链接。而对于其他非聚簇索引而言，叶子节点则指的是主索引，所以在通过非聚簇索引来作查询的时候，需要两次索引查找，但是InnoDB存储引擎会利用自适应性哈希索引来优化这部分重复工作。 根据聚簇索引的原理不难发现，如果用户的查询是使用二级索引进行范围检索的时候（实际情况中很大部分查询是这种情况），如果结果没有命中查询缓存的话，势必会造成大量的磁盘随机IO访问。因为对二级索引进行范围检索得出的一级索引本身是无序的，再根据一级索引去检索数据行就会出现对磁盘的大量随机IO操作。 InnoDB会使用一种叫Multi-Range Read的优化方式，来降低随机IO的读取。其原理就是先将二级索引范围检索得到的一级索引放入缓存进行排序，在回表的时候就用有序的一级索引进行查找，从而将大量的随机IO转换成顺序IO。这个缓存的大小受限于系统变量read_rnd_buffer_size的大小，这个系统变量是客户端级别的，对其调整需要谨慎。 在创建索引的时候，我们需要遵循一定的原则： 主键的选择：因为InnoDB存储引擎底层是通过聚簇索引来组织数据存储的，其中的一级索引就是当前表的主键，如果没有定义主键，则会选择表的非空的唯一性索引作为一级索引，如果连这都没有，那系统会自动生成一个隐藏的一级索引。大多数情况下，这个一级索引就是指的主键。 定义主键的时候需要遵循两个原则：选择占用空间小的类型，有序（单调递增）。 为主键选择占用空间小的类型是因为二级索引的叶子节点存放的是一级索引，那一级索引如果占用很大空间就意味着所有的二级索引需要更大的空间来存储。主键有序是因为聚簇索引决定了在物理存储的时候需要按照顺序存储一级索引和数据行，如果主键无序，就会出现大量的页分裂操作，产生大量的内存碎片，降低写入效率。 列的选择性：当为某列添加索引的时候，需要考虑这个列是不是频繁出现在查询条件中，这个列的选择性是否足够高。选择性不高的列，不但不会提高查询性能，反而会导致额外的存储和索引维护工作。诸如性别，状态这样的字段，需要谨慎评估是否需要添加索引。 创建组合索引：在选择索引的时候，一个常见的错误是给每个需要添加索引的列都加上独立的索引，但是一般情况下，我们的查询条件由表中的多列组成，我们完全可以利用多列索引，来覆盖单列或者多列的查询。需要使用多列索引的典型信号就是当我们在分析SQL执行计划的时候，extra列中出现using union，using sort_union, using intersection提示信息。在添加索引的时候，根据业务场景，结合最左前缀原则，适当的添加多列索引能够显著提升查询效率。 需要注意的是多列索引的顺序问题，一个是各个列之间的顺序问题，这个需要结合业务场景，根据最左前缀原则，来定义列的顺序，如果不考虑排序和分组，把选择性最高的列放在列首通常是好的，如果出现了ORDER BY子句，那索引列的顺序应该参考WHERE子句中索引列和ORDER BY子句索引列的顺序； 然后是各个列数据的升序倒序问题，查询的结果需要和索引定义的各个列的数据的升降序方向保持一致（这里的保持一致指多列多列索引所定义的数据的升降序和查询结果中的数据升降序完全一致或者完全相反），才能使用到该索引的有序的优势，不然还是需要额外的内存排序。举个例子，针对某一查询有ORDRE BY C1 DESC, C2 ASC, 创建索引INDEX(C1 DESC, C2 ASC) 和INDEX(C1 ASC, C2 DESC)都可以使用到索引的有序性而不用额外的内存排序。这里是调整多列索引自身的顺序来满足查询数据所需顺序，从而使查询的时候利用索引排序。 这里还需要注意的一点是如果有ORDER BY子句，那只有ORDER BY子句中的所有列都来自同一张表才能保证查询的时候使用索引来排序，否则还是需要额外的内存排序。 最左前缀原则：索引中经常提及的最左前缀原则，是指在多列索引中，查询语句的条件子句和分组排序子句会根据多列索引最左边的列开始匹配，如果查询条件中没有出现最左边的列，则匹配失败，如果出现了，而且是等值匹配，则依次往右进行匹配。如果查询语句中不包含分组排序子句，则此处等值的定义为等于子句或者IN子句或者OR+等于子句；如果包含分组排序子句，那此处等值只能是等于子句。当然最左前缀原则也适用于针对字符串的LIKE查询，如果是右匹配，还是能使用到索引的，但是做匹配或者全匹配就没有办法使用列上的索引了。 覆盖索引：覆盖索引是指某个查询中WHERE子句和SELECT子句中涉及到的列都来自一个索引。这样在查询的时候，存储引擎可以在检索二级索引的时候获取到所有需要查询的列，从而避免再去检索主键索引和数据行。典型的应用场景就是优化分页实现。可以先在子查询中利用覆盖索引查询出主键的区间值，再和外层查询级联，这样就只有当前分页大小的数据需要回表查询。 针对分页还有一种优化方式是利用已知的区间值（比如主键）来过滤，然后每次查询都通过该区间值来进行过滤，从而减少不必要的数据检索，比如WHERE id > 100 and a=? limit 20这种方式，但是这和传统的分页设计不符，需要改造分页查询的设计，这也是需要权衡的地方。 索引扩展：索引扩展（Index Extension）[3]是InnoDB存储引擎针对二级索引的一个优化，存储引擎内部会对这个索引进行扩展，将一级索引对应的列自动添加到二级索引后面，从而生成更高效的查询执行计划，提高性能。 举个例子，表t的主键是KEY(C1,C2)，其中定义了一个二级索引是INDEX（C3），通过索引扩展过后，该二级索引就被优化扩展为 INDEX（C3,C1,C2）的效果。比如有查询SELECT C4, C5 FROM t WHERE C3=1 and C1=2 就能利用到C3和主键两个索引。索引扩展只适用于ref, range, index_merge方式的数据扫描查询。 索引下推：索引下推（Index Condition Pushdown）[4]是针对二级索引的另一个优化，能够减少存储引擎回表的次数，降低服务器端和存储引擎端传输的数据量，默认开启。其原理就是在存储引擎根据某个索引回表查询数据行之前，先根据where子句中该索引的其他非命中的索引列进行过滤，最后再回表查询数据行，最终返回数据到服务器端。而未使用索引下推的情况下，对该索引的非命中索引列的过滤是在服务器端进行的，无法在存储引擎端过滤，更不用说在存储引擎回表之前过滤。该优化适用于range, ref, eq_ref, ref_or_null方式的数据扫描查询。 为了验证我们的索引是否能和预期一致，需要分析查询执行计划。EXPLAIN输出的每一列的意思不用多说，这里主要针对部分需要注意的值进行特殊说明。 1. type列值中的index指的是MySQL扫描表时按照索引的次序进行，而不是行，本质上还是全表扫描，只是避免了额外的内存排序，但是如果同时extra列的值是using index，那说明正在使用覆盖索引扫描，不会回表查询具体数据行，效率很高。 2. key列 + key_len列能够定位组合索引中哪些列没有被使用到。key列指MySQL决定使用哪个索引来优化对该表的访问，ken_len列指使用到该索引的字节数。 可以通过ken_len的长度来反推使用到该索引的哪些列，这里需要注意的是索引每项的长度计算方式，若列是非NULL定长类型，则该列索引长度为该定长类型的长度，例入INT类型为4 byte；如果该列可为NULL，则还需要额外的1byte来保存该信息；变长类型除开该变长本身类型的长度以外，需要额外的2 byte来保存变长相关信息；如果是字符类型，则需要考虑字符集因素，例如CHAR（10）NOT NULL UTF8则key_len是10 × 3 + 2 = 32 byte，因为UTF8字符集下每个字符需要3 byte来存储。 3. extra列有很多的提示信息，充分理解其含义可以引导优化SQL语句。using index指使用了覆盖索引方式扫描数据，using index condition 使用了索引下推优化，using sort_union，using union，using intersect 使用了索引合并优化，需要考虑是否应该合并多个单列索引为组合索引；using where指服务器端从存储引擎获取了数据过后还进行了额外的过滤操作，需要考虑是否可以将非索引字段添加为索引字段（根据实际情况而定）； 根据EXPLAIN的输出，我们很快可以定位到SQL语句中的问题所在，以下罗列了部分容易被忽视的索引失败案例： 1. 查询优化器的成本模型认为全表扫描会比使用索引进行检索更快，这个和很多因素有关，比如表数据量大小，索引列的选择性是否足够高，以及查询优化器成本模型本身的一些缺陷。我们可以使用FORCE INDEX或者USE INDEX去模拟其他因素导致的某些索引无法命中情况。 2. 索引列不独立，即索引列不能是表达式的一部分，比如使用了函数，运算符等。比如CONCAT(“Kobe”，C1) = “Kobe Bryant”，即使C1列上有索引，由于列不完整，也会导致索引无法使用。 3. 索引列类型不匹配。这是在做SQL优化中经常会遇到的情况，要么是传入的参数和索引列类型不匹配，要么是在做连接操作的时候两个连接的列类型不同，导致索引无法命中。 4. ORDER BY或者GROUP BY子句中的列来自不同的表，会导致ORDER BY 或者GROUP BY子句的列无法使用索引，从而导致需要额外的内存排序。 5. 前面提到的组合索引的顺序问题，如果不满足的话，也是无法命中索引的。需要注意不同情况下最左前缀原则的等值处理 |
|
|
