在某环境发现一个前端请求报错，经查为一条复杂的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连接查询时的优化机制，包括基于代价的查询优化机制，优化器优化过程，统计字段的采样机制，自动采样的触发机制，以及统计字段自动采样机制存在的问题，并给出了解决方案。希望对遇到相关问题的同学有所帮助。
