mysql多表查询优化实例_记一次mysql多表查询(left jion)优化案例

一次mysql多表查询(left jion)优化案例

在新上线的供需模块中,发现某一个查询按钮点击后,出不来结果,找到该按钮对应sql手动执行,发现需要20-30秒才能出结果,所以服务端程序判断超时,故先不显示结果

以下是对这条查询的优化记录

1 数据库配置

数据库配置:4C8G

主表数据:3W+

2 sql语句

提取sql语句,简化如下

SELECT

taba.id,

taba.title,

taba.type,

taba.end_time,

tabb.username,

tabc.orgname

FROM

taba

LEFT JOIN tabd ON tabd.info_id = taba.id

LEFT JOIN tabe ON tabe.sdo_id = taba.id

LEFT JOIN tabb ON tabb.id = taba.creator

LEFT JOIN tabc ON tabc.id = taba.organization_id

WHERE

taba.`STATUS` = 'PUBLISH'

AND tabd.type = 'INDEX'

AND tabd.`VALUE` = '1'

AND taba.info_type = 'SUPPLY'

GROUP BY

taba.id

ORDER BY

taba.create_time DESC

LIMIT 100

3 优化记录

3.1 数据库索引

首先第一反应,查sql是否走了索引

EXPLAIN

select ......

从索引检查结果发现

tabe只有主键索引,没有sdo_id的索引

经过确定sdo_id是通过uuid制作,重复数很少,可以增加上索引

其他条件虽然也没走索引,但是属于枚举值,重复性高,没有加索引的条件

经过添加索引,数据查询时间降低到3秒以内,所以正确的索引才是王道

3.2 返回数据限制

经过与开发人员沟通,确定可以每次只取10条数据,所以要求他们更改limit语句限制为limit 10

经过修改limit语句,数据库直插时间已经变味1.7秒

3.3 spring框架错误的conut *

经过前两部优化,按理2秒左右app就能显示数据,但是时间上却需要4秒钟

通过sql慢查询日志,发现在这条sql执行前,spring框架自动执行了一个select count(0) from (......)的操作来做分页,但是所以导致查询时间是理论上的2倍

再次与开发确定,不用框架的自动分页功能,改为代码层手动分页

结果修改框架的分页,app查询时间达到2秒内,已基本得到解决

3.4 极限优化limit

查询优化到2秒,已基本可以接受,但是先到数据才3万多行,感觉还是不能接受,继续找原因,发现如下:

3万+数据,但是sql执行结果却显示扫描了100多万行数据

观察sql语句,可以发现是先做了多次left join后,对结果取limit,那能不能先取limit 10再进行查询呢,于是把sql优化如下

SELECT

taba.id,

taba.title,

taba.type,

taba.end_time,

tabb.username,

tabc.orgname

FROM

taba

LEFT JOIN tabd ON tabd.info_id = taba.id

LEFT JOIN tabe ON tabe.sdo_id = taba.id

LEFT JOIN tabb ON tabb.id = taba.creator

LEFT JOIN tabc ON tabc.id = taba.organization_id

WHERE

tabd.type = 'INDEX'

AND tabd.`VALUE` = '1'

AND taba.id IN (

SELECT * FROM

( SELECT id FROM taba WHERE`STATUS` = 'PUBLISH'

AND info_type = 'SUPPLY'

ORDER BY taba.create_time DESCLIMIT 10 ) AS tmp

)

优化方法:

将limit语句通过子查询放入where条件中

sql将先执行子查询获取10条id数据

让后将10条id拿去前面做join

优化结果

sql执行时间达到0.117秒,再一次质的飞跃

基本做到秒加载,点击按钮,一秒内出结果

3.4.1

第二天一觉醒来,觉得这个sql还有值得优化的地方,于是分别提取出各语句执行后,发现耗时最长的是limit条件,耗时0.09s

优化方法

查找发现原因是order by条件create_time列未加索引,导致做了一次全表扫描

于是增加上create_time索引

优化结果

sql执行时间变为0.068s

再次说明正确的索引才是王道

3.5 优化后记

其实sql中还有几个可以优化的地方,比如:

4个left join中的3个可以改成inner join

原语句的group by,经测试改掉可优化0.3秒(1.7秒处)

limit语句可以放到from处先处理等

但是:

sql优化是长期的过程

优先解决影响业务的慢查询

优先解决占时间比例大的慢查询

咱是运维,不是DBA,还有高可用等着我玩

因此已经达到要求,甚至超额完成,就不用再管芝麻了