背景

线上一条sql按正常执行计划应该是毫秒级出结果都。突然成了慢查询。dba反馈此sql执行了3000秒还未返回结果。sql做了‘脱敏’。简单描述下：m_ext_value是存放数据表，它是一张达40亿行的大表，ext_attr是定义表，只有几百行记录。通过这种方式，将原来按列存储的数据变成按行存储。

select ext.m_id as mId, attr.id as attrId, attr.code as attrCode, attr.data_type as dataType,
ext.value_datetime as attrValueDatetime, ext.value_decimal as attrValueDecimal, ext.value_int as attrValueInt,
ext.value_l_varchar as attrValueLVarchar, ext.value_s_varchar as attrValueSVarchar from m_ext_value ext
inner join  ext_attr attr
on ext.entity_ext_id = attr.id and attr.entity_type = 2 and attr.is_deleted = 0
and ext.is_deleted = 0 and attr.status = 1
where ext.m_id in ( 446351894 )
order by ext.id desc

分析

通过explain 上面语句得到等结果是

ID	读取类型	读取表	连接类型	可用索引	使用索引	索引长度	额外引用	扫描行数	额外描述
1	SIMPLE	ext	ref	idx_m_id,idx_ext_attr_id	idx_m_id	8	const	9	Using where
2	SIMPLE	attr	eq_ref	PRIMARY,uni_ext_attr	PRIMARY	8	db1.ext.ext_attr_id	1	Using where

通过这个执行计划来看，mysql走的索引是正确的，而且过滤性很好。然而线上执行缺有问题，为啥3000s还没返回？然后dba对当前这次查询进行了explain，发现它使用的执行计划和上面的不一样。

explain for connection 12345;

然而真正的执行计划却是这样

ID	读取类型	读取表	连接类型	可用索引	使用索引	索引长度	额外引用	扫描行数	额外描述
1	SIMPLE	attr	ref	PRIMARY,uni_ext_attr	uni_ext_attr	1	const	44	Using where; Using temporary; Using filesort
1	SIMPLE	ext	ref	idx_m_id,idx_ext_attr_id	idx_ext_attr_id	8	db1.ext.id	7344	Using where

DBA给的解释是：

dba给的解释是：这个时候可能在计算统计信息，导致优化器在这一刻看到的统计信息是错误的，
导致优化器认为此索引过滤性不好，就转用小表作为驱动表来join，悲剧就此发生。通过上述表格可以看到扫描行数已经去到7344，实际只有9左右。

join的本质可以粗略理解为两个循环嵌套。外循环称作驱动表。此处可以理解为，将小表作为外循环，根据attr.id与大表进行关联。
意思就是要把大表这几十亿数据轮几十遍。我的个乖乖，没挂就算幸运了。

解法

将inner join改为stiraight_join可避免此问题。让大表作为驱动表，强制其使用m_id列的索引（扫出来的结果集很小），然后对小表进行join。

mysql决策谁做驱动表由查询的数据集大小确定，谁的数据集小谁做驱动表

其他

设计

把列变成行这种做法非常不好。数据量急剧膨胀无法控制，拆库都必须依赖主表。我觉得还是建另一个表来存放好一点。开发友好，理解也简单，查问题方便。所以表设计时一定要注意。一定要对自己对业务表有一个比较准确的预估，以及扩展计划。

straight_join

straight_join其实不是什么特别join，他会干扰mysql对表的连接顺序。强制join前的表作为驱动表（外循环）

…

虽然mysql加入了bka、mrr、icp等。但其实并不能完全解决性能问题，特别是io的坑。建议不要在生产上使用join，尽量简化查询。根据简单的key来查不怕查多次，就怕慢查询。

另外，随着业务的发展后续要想拆库，需要把原来用join方式联合查询的表分到两个不同的库，那就改动大了。随着微服务的兴起，这是很常见的事。

最后修改于 2018-04-29