MySQL 关联查询执行计划问题

我来答

嘿嘿嘿

2024-03-22

MySQL 关联查询执行计划问题

同一个查询，条件不一样执行计划有差异

res表数据量40W，p表数据量17W，

res表表结构：

PRIMARY KEY (`id`),
UNIQUE KEY `idx_telnum` (`telNum`) USING BTREE,
KEY `idx_updatetime` (`updateTime`) USING BTREE,
KEY `idx_appid` (`appId`) USING BTREE,
KEY `idx_cm_group_id` (`cm_group_id`) USING BTREE,
KEY `channel_accid` (`channel_accid`),
KEY `idx_area_code` (`area_code`) USING BTREE

p表表结构

PRIMARY KEY (`id`),
UNIQUE KEY `idx_number` (`number`) USING BTREE,
KEY `idx_create_time` (`create_time`),
KEY `idx_area_code` (`area_code`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE

添加isVideo=0之后执行很慢，几乎查不出来，执行计划如图：

不添加isVideo这个条件执行很快几秒左右，执行计划如图

我来答

添加附件

问题补充

4条回答

默认

最新

徐孝亮

原因：因为你添加isVideo=0之后，走的执行计划都变了。两张表的关联顺序变了，走的索引也变了。

分析：

两张表的关联方法是嵌套循环链接。嵌套循环链接的原理：驱动表返回多少行，被驱动表就需要被扫描多少次。

不加isVideo=0，p表是驱动表，走的idx_status索引，虽然该索引选择性差，但是就扫描一次。res是被驱动表，走了唯一性较好的telNum字段的索引。

加isVideo=0之后，res表扫描一次，返回8930行，p表此时作为被驱动表，就要被扫描8930次，因为走的还是idx_status索引，这个唯一性不好的索引造成的性能瓶颈就被放大了。

优化建议：

1. 添加hint force index (idx_number) 强制p表走idx_number字段的索引。 --如果还走不上，我怀疑你两张表的数据类型不一致，建议修改下数据类型，或者通过函数转一下。

2. 使用straight_join强制让p表先走，res表做被驱动表。

--不知道有没有说清楚，有任何问题可以再提问我。。

有用 1

张sir

你可以试试加个组合索引：（area_code，isVideo，tagcode），

有用 2

嘿嘿嘿

题主

2024-03-22

强制走telnum这个索引速度即可，我的疑问是加不加isVideo为何执行计划有如此差异

张sir

答主

2024-03-25

你可以看下这篇文章，如果你这个数据库版本是5.7的话，基本可以断定是由于filtered不准导致的差异。由于加了isvideo这是一个没有索引的字段，且没有直方图，filtered收到了影响，可以参考这篇文章：https://www.jianshu.com/p/e70de1f7cdcf

zhou

telnum 和number 类型贴一下最好是完整的ddl，看着一个像是varchar 一个是整型，统计信息不准，40万17万的两表统计信息只看出8万和16万

有用 1

嘿嘿嘿

题主

2024-03-25

telnum varchar20，number varchar16

chengang

MySQL总是会选小表当驱动表不加isVideo 条件 p表当然是小表。加了isVideo条件，MySQL认为过滤性很好，从执行计划的filtered = 0.05 可以推算出 MySQL评估认为最终只有 8930 *0.05 = 446.50行参与最终的JOin 题主可以用condition_fanout_filter参数验证，这里贴了我的一篇文章可以看看https://www.modb.pro/db/1752165858076086272

有用 2

zhou

2024-03-23

假设随机读:10ms,顺序读:0.1ms 一个page 40条记录执行计划1： filtered = 0.05 可以推算出 MySQL评估认为最终只有 8930 *0.05/100 = 5 5*10 + 5*82979/40*0.1=11S 执行计划2： 82979/40*0.1 +82979*10*0.05=41S 粗糙IO COST比较执行计划1<执行计划2, 但实际运行完全不同，代表执行计划高估了res filter，实际filter没有0.05%,应该高于0.4%才会导致比执行计划2慢组合索引才是王道，估算出来的(a,b,c)filter= a filter * b filter * c filter 经常高估了filter isvideo filter 1%？ select count(distinct isvideo) from res res filter= tagcode filter * isvideo filter 可能tagcode 与area_code 有关联性，isvideo 与 area_code也有关联性？