添加到所有复杂性能关键表的查询中。最近又遇到了慢SQL。简单看了一下，也是因为MySQL优化器本身对查询计划的估计不准确。SQL如下：select*fromt_pay_recordWHERE((user_id='user_id1'ANDis_del=0))ORDERBYidDESCLIMIT20执行这条SQL需要20分钟才能得到结果。但是如果我们改变user_id，执行会非常快。从线上业务表现来看，大部分用户表现正常。我们也用一个和这个用户数据分布相似的用户来查，还是比较快的。我们先EXPLAIN原来的SQL，结果是：+----+------------+------------+-----------+--------+-----------------------------------------------------------------------------------------+--------+--------+------+--------+-----------+------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+-------------+------------+------------+------+---------------------------------------------------------------------------------------+--------+---------+------+--------+--------+------------+|1|简单|t_pay_record|空|索引|idx_user_id，idx_user_status_pay，idx_user_id_trade_code_status_amount_create_time_is_del|初级|8|空|22593|0.01|使用where|+----+------------+--------------+------------+--------+----------------------------------------------------------------------------------------+--------+---------+--------+--------+--------+------------+然后我们改对于一些分布相似但响应时间正常的用户，一些EXPLAIN结果为：+----+------------+------------+----------+--------+--------------------------------------------------------------------------------------+----------------------------------------------------+--------+-----+--------+----------+------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+------------+------------+------+--------------------------------------------------------------------------------+-------------------------------------------------------+---------+------+--------+--------+------------+|1|简单|t_pay_record|空|索引|身份证_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del|idx_user_id_trade_code_status_amount_create_time_is_del|195|空|107561|10.00|使用where|+----+------------+------------+------------+--------+--------------------------------------------------------------------------------------+-----------------------------------------------------+--------+------+--------+--------+------------+有的是：+----+------------+------------+-----------+--------+--------------------------------------------------------------------------------------+------------+--------+-----+--------+---------+------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+------------+------------+------+------------------------------------------------------------------------------------+------------+--------+------+--------+--------+------------+|1|简单|t_pay_record|空|索引|idx_user_id,idx_user_status_pay,idx_user_id_trade_code_status_amount_create_time_is_del|idx_user_id|195|其中|+----+------------+------------+------------+------+--------------------------------------------------------------------------------------+------------+--------+------+--------+--------+------------+其实根据这个表现，可以推断是错误的索引。为什么使用错误的索引？这是由很多原因造成的。本文将围绕这个SQL来分析这个多方面的原因，并给出最终的解决方案。上一篇文章对MySQL慢SQL的分析，我提到SQL调优一般使用以下三种工具：EXPLAIN：这是一个比较简单的分析，并没有真正执行SQL，分析可能不够准确和详细。但是可以发现一些关键问题。PROFILING：打开setprofiling=1的SQL执行采样。可以分析SQL执行分为哪些阶段，每个阶段需要多长时间。SQL需要执行并执行成功，分析的阶段不够详细。一般只能通过是否存在某些阶段以及如何避免这些阶段的发生（比如避免内存排序等）来进行优化。OPTIMIZERTRACE：详细显示优化器的每一步，SQL需要执行和执行成功。MySQL的优化器考虑的因素太多，迭代太多，配置也相当复杂。大多数情况下默认配置没问题，但在某些特殊情况下会出现问题，需要人为干预。这里再次说明，在不同的MySQL版本中，EXPLAIN和OPTIMIZERTRACE的结果可能不同。这是MySQL本身设计欠缺造成的。EXPLAIN更接近最终的执行结果。OPTIMIZERTRACE相当于在每一步都收集点数。在迭代开发的过程中，难免会有遗漏。对于上面的SQL，其实我们可以通过EXPLAIN知道是索引错误。但是无法直观的看出为什么取错了索引，需要借助OPTIMIZERTRACE进一步定位。但在进一步讨论之前，我想谈谈MySQL的InnoDB查询优化器数据配置。MySQLInnoDBQueryOptimizerDataConfiguration(MySQLInnoDBOptimizerStatistics)官网文档地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html为了优化用户的SQL查询,MySQL对所有SQL查询进行SQL解析、重写和查询计划优化。对于InnoDB引擎，在制定查询计划时，分析：全表扫描的消耗是多少？可以使用哪些索引？会考虑where条件和顺序条件，通过里面的条件就可以找到有这些条件的索引。每个索引的查询消耗有多大选择消耗最少的查询计划执行每个索引查询的消耗，这需要通过InnoDB查询优化器数据。这个数据是通过收集表和索引数据得到的，不是全量收集的，而是抽样收集的。它与以下配置有关：innodb_stats_persistent全局变量控制全局默认数据是否持久化。默认为ON，表示持久化。一般我们不会接受保存在内存中。这样，如果数据库重启，表会重新分析，会减慢StartTime。控制单个表的配置是STATS_PERSISTENT（在CREATETABLE和ALTERTABLE中使用）。innodb_stats_auto_recalc全局变量默认自动更新。默认为ON，即表中超过10%的行更新后，后台异步更新采集的数据。控制单个表的配置是STATS_AUTO_RECALC（用于CREATETABLE和ALTERTABLE）。innodb_stats_persistent_sample_pages全局变量控制全局默认收集页数，默认为20。即每次更新随机从表和表中的每个索引中收集20页数据，用于预估每次查询消耗index和整表扫描消耗多少，控制单表的配置是STATS_SAMPLE_PAGES（在CREATETABLE和ALTERTABLE中用到）。定位SQL执行时间最慢的原因通过前面的EXPLAIN结果，我们知道最终查询使用的索引是PRIMARY主键索引。在本例中，整个SQL执行过程就是：通过主键倒序遍历表中的每条数据，直到过滤掉20条。通过耗时的执行我们知道，这样遍历了很多数据才凑成20条，效率极低。为什么会这样？从SQL语句我们知道，在上面提到的第二步中，考虑的索引包括where条件中的user_id和is_del相关索引（通过EXPLAIN知道有这几个索引：idx_user_id、idx_user_status_pay、idx_user_id_trade_code_status_amount_create_time_is_del），以及id索引在orderbycondition中，也就是主键索引。假设这个随机集合中采集到的页面数据是这样的：图中蓝色的代表被采样的页面，同表中的每个索引默认会采样20个页面。假设本次采集结果如图，其他指标采集比较均衡。以其他指标来看，用户要扫描几万行。但是主键集合的最后一页恰好最后全是这个用户的记录。由于语句末尾有20个限制，如果末尾恰好有20条记录（并且都满足where条件），那么就会认为根据主键向后找20条记录就够了.这会导致优化器认为主键扫描的消耗最少。但实际上并不是这样的，因为这是抽样，可能有很多很多记录不是这个用户，特别是大表。如果我们去掉限制，EXPLAIN会发现索引在正确的轨道上。因为没有限制，主键索引必须扫描一次，消耗不能低于user_id相关索引。正常执行时间的SQL之所以和user_id不一样，也会去到不同的索引。道理是一样的，因为所有索引的优化器数据都是随机采样的。随着表的不断扩大和索引的不断扩大，有可能加入更复杂的索引，会加剧使用不同参数（这里是使用不同的user_id）的索引消耗差异。这也导致了一个大家可能会遇到的新问题。我在原有索引的基础上加了一个复合索引（比如原来只加了idx_user_id，后来又加了idx_user_status_pay），所以原来的索引只是根据user_id来查数据的SQL，有的可能用idx_user_id，有些可能会使用idx_user_status_pay。使用idx_user_status_pay比使用idx_user_id更慢。因此，增加一个新的复合索引可能会拖慢其他非复合索引优化的业务SQL。所以这个设计需要慎重添加。当数据量不断增加，表越来越复杂时，会带来哪些问题？由于统计数据不是实时更新的，只有当更新的行数超过一定比例时才会开始更新。而且统计不是全量统计，而是抽样统计。所以当表的数据量很大的时候，这个统计数据很难做到非常准确。由于统计数据不够准确，如果表设计比较复杂，存储的数据类型很多，字段也很多，最重要的是还有各种复合索引，而且索引越来越多并且更复杂，这将进一步加剧统计。数据不准确。顺便说一句：MySQL表的数据量不要太大，需要水平拆分。同时字段不能太多，需要垂直拆分。而且索引不能随便加。想加多少就加多少。还有上面提到的两个原因。这会加剧统计数据的不准确性，导致使用错误的指标。ManualAnalyzeTable会在表上加读锁，会阻塞表上的更新和事务。所以不能用在这种网上业务键表上。可以认为在业务低迷时，调度的Analyzebusiness-criticalTable依赖表本身自动刷新数据的机制，参数很难调整（主要是参数STATS_SAMPLE_PAGES，我们一般不改STATS_PERSISTENT，并且我们不会接受savinginmemory，这样如果重启数据库，会重新分析表，会减慢启动时间，而且我们不会关闭STATS_AUTO_RECALC，这样会导致优化器去分析越来越不准确），并且很难预测调整什么值最合适。而且，业务的增长和用户行为导致的数据倾斜也难以预测。通过AlterTable修改表的STATS_SAMPLE_PAGES时，与AnalyzeTable的效果是一样的，都会给表加一个读锁，阻塞表上的更新和事务。所以不能用在这种网上业务键表上。所以最好从一开始就估计大表的震级，但这很难。结论和建议总结一下，我建议对于线上数据量大的表，最好通过分库分表的方式提前控制好每张表的数据量，但是业务增长和产品需求是不断的迭代和变化。复杂的。很难保证不会出现复杂索引的大表。这种情况下，我们需要在适当增加STATS_SAMPLE_PAGES的前提下，针对部分用户触发的关键查询SQL，使用强制索引将其引导到正确的索引，这样就不会出现本文提到的MySQL优化器表集合。不准确的数据导致某些用户ID查询转到错误的索引。
