200万数据联表查询SQL优化4S至秒级

200万数据联表查询SQL优化4S至毫秒级

背景: 表一

表二:

原sql共两条一条分页查内容,一条查总行数

select h.* from device_info  i left join device_mqtt_history h on h.code = i.code where h.id is not null and i.dept_id in ( 17 ,2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) order by h.create_time desc LIMIT 20

select count(*) from device_info i left join device_mqtt_history h on h.code = i.code where h.id is not null and i.dept_id in ( 17 , 2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) 

以上两条SQL是来自于idea的debug日志.打出来的.该业务很慢


先说说原SQL. 有几个优点把,
第一. 联表是一般都应该是小表驱动大表,也就是小表作为主表.
第二. 他查询第一个应该也考虑想使用主键所以后面主键is not null.因为select后面查的是*
第三. 小表大表相连接的时候,索引应该建立在子表上面
后面我查了下资料, in 不会影响索引的使用,但是可以看看以上两条SQL的执行计划

SQL 1:

select h.* from device_info  i left join device_mqtt_history h on h.code = i.code where h.id is not null and i.dept_id in ( 17 ,2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) order by h.create_time desc LIMIT 20

SQL 2:

select count(*) from device_info i left join device_mqtt_history h on h.code = i.code where h.id is not null and i.dept_id in ( 17 , 2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) 

SQL2 ,没有问题. 时间0.016S

优化SQL1 一 : 把create_time 换成id(自增) 倒序, 寄希望于直接走主键索引

没有反应.于是 . . .

我把code索引删了 .删了后,确实快了很多
以为这就搞定了. . 确实SQL1优于推荐得code不走了.走primary了. 快很多了


但是: SQL2 崩了 近4S,总业务时间不变. .

于是我开始反复测试 横条.多列索引之类的全部上了.最左匹配, 带头大哥不能死, 中间兄弟不能断…

上图则展示了本人对索引还是不够深入了解. .当然更多的想着试试看吧.

以上介绍整体来说就是 code索引是否删掉问题 .
优化点1: 把create_time换成了id 查倒序
优化点2: 把code索引删掉后,SQL1(查内容)的就用到了主键很快,不删的话SQL2(统计数量的)就跑的很快

后面想到一个api.

优化SQL1 二:

Mysql的force index , 只要我保证code索引不删的时候, SQL1 查内容得走主键索引不就可以了吗?
于是 SQL 1变成了:

更改前:

select h.* from device_info  i left join device_mqtt_history h on h.code = i.code where h.id is not null and i.dept_id in ( 17 ,2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) order by h.create_time desc LIMIT 20

更改后:

select h.* from device_info  i left join device_mqtt_history h force index(primary) on h.code = i.code where h.id is not null and i.dept_id in ( 17 ,2 , 22 , 21 , 20 , 27 , 25 , 19 , 26 , 1,20 ) order by h.id desc LIMIT 20

索引保持原来得不变 code.

如果依然想根据原来得create_time倒序也可以创建create_time得排序,但不可以创建组合索引,create_time,code,因为他两都想当老大, 最左

最后看看两条SQL 最后执行得时间和结果吧

SQL1执行计划和执行时间:

SQL2执行计划和执行时间:


以上算是告一段落了. 因为本次优化,个人认为有一定得记录价值, 所以发博客记录下. 本人才疏学浅 每一条SQL得改变都是执行过后的结果, 如果有理解不到位或者更优秀的解决办法,愿意虚心学习与交流.
谢谢观看.