从一条慢sql优化，深入探讨mysql的优化器优化机制

在某环境发现一个前端请求报错，经查为一条复杂的sql耗时约70s，最终导致前端响应超时。下面叙述下本次问题排查及根因分析过程，供其他同学参考。

本文中使用到的数据库是mariadb，对于mysql也是基本适用。

一，连接算法

使用explain该问题语句，结果如下图所示：

图一

明显存在一个BNL算法的描述，相对于INLJ算法，BNL算法效率比较低。

下面对mysql相关连接算法做个简单介绍：

**1.**Simple Nested-Loops Join（SNLJ，简单嵌套循环联接）

这是最简单的一种算法，即假设t1，t2两个表关联字段都没有索引，那么查询的过程就是遍历匹配两个表，用文字描述如下：

遍历表t1,对于表中每一行数据row1:

遍历表t2,对于表中每一行数据row2:

判断row1，row2是否满足关联条件：

如果满足，返回关联结果集 <r1,r2>。

上述过程是一个取笛卡尔积的过程，即对于t1中的每一行记录，t2都要全表遍历一次。如果表t1有10000行，t2有20000行，则上述过程一共需要扫码1w+1w*2w条数据，超过1亿条。

其代价之大可想而知，所以mysql并没有直接采用这种算法，而是使用了相对改进的BNL算法。

2.Block Nested-Loops Join（BNL，基于块的嵌套循环联接）

这种算法其实就是将SNLJ中t1表放进内存中(Join Buffer)，再从t2表中取到每一行与内存中t1进行匹配。如果join buffer不能一次性放下整个t1表，则会分批次放入。

这种算法使得t2表中的每条记录可以一次性的匹配t1表在内存中的所有记录，如果t1表全都可以放进join buffer，那么t2表只需要做一次全盘扫码。即使t1需要分N次放入join buffer中，t2表也只需要全盘扫码N次，从而减少了扫码t2表的io代价。

此处join buffer size 的配置，就成了影响t2表全盘扫码次数的关键参数，如果该值相对于t1表数据过小，则该算法相对于SNLJ算法优化效果就很有限，从而依然存在性能问题。

Join buffer的大小可以通过show variables like "%join_buffer%"查看，默认为256k。

但是仍然需要对t2表进行多次的全盘扫码，如果t2表特别大，依然会有性能问题。以上是两个表连接字段都不存在索引的情况，如果其中一个表的连接字段存在索引，则优化器会将该索引字段对应的表作为被驱动表，进而使用相对高效的INLJ算法。

3.Index Nested-Loops Join（INLJ，基于索引的****嵌套循环联接）

相对于SNLJ算法，INLJ算法的改进点在于被驱动表(内表t2)查找时使用到了索引，这样就完全避免了t2表的多次全盘扫码的代价，进而大大提高了执行效率。

备注：

对于BNL算法加入Hash算法优化，从而形成BNLH算法；以及为了减少INLJ算法中的连接字段为非主键索引时带来的随机IO问题，从而改进的BKA算法；在BKA算法中使用hash算法进而形成BKAH算法，与本文关系不大，不在此处探讨。

连接算法小结

通过上述分析，我们可以得到以下结论：

1.在两表关联时，被驱动表关联字段尽量带有索引，进而使用INLJ算法。

2.被驱动表的关联索引最好是主键索引，这样可以减少关联时的回表操作。

3.无论是INLJ算法还是BNL算法，小表驱动大表都是都可以减少io次数。

4，左连接驱动表是左表，右连接驱动表是右表，内连接驱动表由优化器自主选择。

案例分析

回过头来，看图一，发现BNL算法记录所对应的table属性为，说明这是一个派生表，而派生表是不存在索引的，所以作为被驱动表时使用了BNL算法。

怎么解决呢？

按照上面的思路，就是将这个不存在索引的派生表作为驱动表，而非被驱动表，这样就可以使用到被驱动表关联字段的索引，进而使用INLJ算法。

解决方案

对于这条内连接语句，需要用STRAIGHT_JOIN 指定驱动表即可。即由t1 JOIN t2改为t1 STRAIGHT_JOIN t2，即指定了t1作为驱动表，而不是由优化器自主选择。

通过STRAIGHT_JOIN指定内连接的驱动表，在问题现场，成功将该问题语句的耗时从70s降低到0.02s。

然而故事并没有结束

还没有完

上述做法确实解决了现场的问题，但是留下了一个疑问：为什么优化器没有选择关联字段带有索引的表作为被驱动表，而选择了一个没有索引的派生表作为被驱动表呢？

也就是优化器执行的逻辑是什么呢？

基于代价的索引选择算法

事实上，优化器是通过一种基于代价的计算方式来选择索引，以及两表之间的关联顺序的。

代价模型包括CPU代价和IO代价，不同操作的代价如下：

*操作*	*说明*	*代价值*
disk_temptable_create_cost	创建磁盘临时表代价	40
disk_temptable_row_cost	磁盘临时表每行代价	1
memory_temptable_create_cost	创建内存临时表代价	2
memory_temptable_row_cost	内存临时表每行代价	0.2
key_compare_cost	比较关键字代价	0.1
row_evaluate_cost	行访问代价	0.2
io_block_read_cost	读取磁盘块代价	1
memory_block_read_cost	读取内存块代价	1

观察选择过程

我们可以通过information_schema.optimizer_trace中的信息观察优化器执行的具体流程。

set optimizer_trace=‘enabled=on’;

select * from … 执行所需分析的的操作。

select * from information_schema.optimizer_trace limit 1;

set optimizer_trace=‘enabled=off’;

可以看到该条语句具体的执行流程，及各种选择方案的代价计算值。

如：

…

{

​ “rows_estimation”: [

​ {

​ “table”: “a”,

​ “table_scan”: {

​ “rows”: 18325,

​ “cost”: 467

​ }

​ },

​ {

​ “table”: “b”,

​ “table_scan”: {

​ “rows”: 16241,

​ “cost”: 402

​ }

​ }

​ ]

​ },

​ {

​ “considered_execution_plans”: [

​ {

​ “plan_prefix”: [],

​ “table”: “b”,

​ “best_access_path”: {

​ “considered_access_paths”: [

​ {

​ “access_type”: “scan”,

​ “resulting_rows”: 16241,

​ “cost”: 402,

​ “chosen”: true

​ }

​ ],

​ “chosen_access_method”: {

​ “type”: “scan”,

​ “records”: 16241,

​ “cost”: 402,

​ “uses_join_buffering”: false

​ }

​ },

​ “rows_for_plan”: 16241,

​ “cost_for_plan”: 3650.2

…

通过上面的内容，可以看到各种连接方式的花费。

当然，优化器并没有穷尽所有可能的组合，因为这样可能使评估动作变得太过耗时，所以优化器只会选择一部分可能的组合进行尝试，尤其实在关联表的数量很多的时候。

关于优化器遍历深度，可由optimizer_prune_level， optimizer_search_depth等参数进行控制。

所以在连接表的数量特别多的时候，更有可能无法得到最佳的执行策略。

行数从何而来

除了关联表特别的多的问题外，还有个问题是造成评估错误的常见原因：评估的数据来源不准确。

上面需要评估的数据行数(rows_estimation.table_scan.rows)是从哪来的呢？

答案是从information_schema.INNODB_SYS_TABLESTATS表里（mysql.innodb_table_stats，mysql.innodb_index_stats）也有相近且更详细的信息。

经过对比实际数据发现，这个数据跟实际数据并不一致。

那么这个数据是从哪来的呢，又是为什么不一致呢？

统计字段的采样机制

Innodb引擎对每张表并没有存一个字段来精确统计每张表的数据行数，这应该是基于性能上的考虑。否则就要对增删操作时，同时更新这个统计字段，且在并发操作时对该字段进行加锁，这将导致严重的并发度下降的问题。

*Innodb统计字段的方案*

既然不维护这样一个物理字段，Innodb引擎取而代之的是使用采样的方式，来得到一个近似索引数据行数（对于主键索引来说就是表行数）。采样方式是根据采样页数（参数名为innodb_stats_persistent_sample_pages ，默认值是20页。增加该参数可以使采样更准确，但会消耗更多资源），统计这些页数的数据量，再根据总页数，估算出一个行数的近似值。

上文中的优化器使用到的行数，就是这样一个采样得出的近似值。

这个采样行数什么时候会更新呢？每次有增删数据都需要重新采样吗？

Innodb采样统计自动触发机制。

上图为mariadb源码，从源码中可以看到，在开启统计持久化（参数为innodb_stats_persistent，默认开启）且开启自动统计（参数为innodb_stats_auto_recalc，默认开启）时，相关数据变动超过10%，才会触发重新采样。

既然mariadb有自动采样机制，为什么统计数据还会不准呢？

自动采样机制的弊端

*弊端一*

第一个问题很明显，那就是前文提到的变动数据要大于10%才会触发重新采样，那么对于大表来说，这个触发阈值将会是数十万甚至上百万行的变动，导致大表在长时间段内不能得到重新采样统计，进而该统计数据将会长时间与实际值产生较大差异，进而可能导致优化器的数据来源不准确，做出不好的优化策略。

*弊端二*

除此之外还有个隐含的问题，就是information_schema.INNODB_SYS_TABLESTATS中的MODIFIED_COUNTER是保存在内存中的，当mariadb重启后，该数据就归零了，进而导致需要MODIFIED_COUNTER重新计数。这将延后触发10%变动阈值的时间，对大表的影响尤为严重。

解决自动采样统计弊端的方案

有什么方法可以解决上述问题？

*我们可以主动触发该采样统计。*

即使用：analyze table 表名称，来手动触发对该表的采样统计。

也可以使用mariadb自带的脚本工具mysqlcheck 对整个库进行优化。

如在mysql/bin目录下执行：

./mysqlcheck --user=root --password --host=localhost --port=3306 --analyze --databases 数据库名

运维建议

对于大表，尤其是数据重启后，可根据需要，一定时间自动执行一下上述更新索引语句。

此操作一般都很快，不过仍建议在业务低峰时期作此操作，因为这个操作会导致表上加读锁。

总结

本文通过一次现场sql慢查询的问题，层层分析，挖掘其发生的根本原因。同时分享了mysql连接查询时的优化机制，包括基于代价的查询优化机制，优化器优化过程，统计字段的采样机制，自动采样的触发机制，以及统计字段自动采样机制存在的问题，并给出了解决方案。希望对遇到相关问题的同学有所帮助。