一次长MYSQL语句 inner join 的优化总结
事由
开发在UAT环境执行一个长SQL查询语句，超级慢！给我们发牢骚，什么破服务器~。艹，作为运维，这脸上是真挂不住啊。于是要来语句开始分析。下面是语句：
SELECT taxicity.city_name AS taxiCityName, taxicity.city_code AS taxiCity, taxiorg.full_name AS taxiOrgFullName, taxiorg.id AS taxiOrg, IFNULL( tcu.VEHICLE_TYPE, '未知' ) AS carType, tv.GET_ON_DATE AS getOnDate, tv.GET_ON_TIME AS getOnTime, tv.GET_OFF_TIME AS getOffTime, tv.WAIT_TIME AS waitTime, tv.CAR_NUMBER AS carNumber, tv.CARD_ID AS cardId, tv.HEAVY_MILEAGE AS heavyMileage, tv.EMPTY_MILEAGE AS emptyMileage, tv.TAXIMETER_PRICE AS price, tv.SCHEDULE_FEE AS scheduleFee, '' AS icBeforeBalance, '' AS icBalance, '' AS icId FROM t_driver_work_data tv INNER JOIN t_vehicle tcu ON tcu.CAR_NUMBER = tv.CAR_NUMBER INNER JOIN t_taxi_org taxiorg ON taxiorg.id = tcu.taxi_org AND taxiorg.ENABLE = 1 AND taxiorg.deleted = 0 INNER JOIN t_taxi_city taxicity ON taxicity.city_code = tcu.taxi_city AND taxicity.ENABLE = 1 AND taxicity.deleted = 0 WHERE tv.GET_ON_TIME >= '2022-01-17 00:00:00.0' AND tv.GET_ON_TIME <= '2022-01-18 23:00:00.0' ORDER BY tv.GET_ON_DATE DESC, tv.GET_ON_TIME DESC LIMIT 0,10 ;
排查步骤
先分别查了join 的四张表：最大的表t_driver_work_data有 4kw 条数据。也不大呀，况且它 limit 只要十条而已。
分析索引：join on 的所有字段都加了索引了呀
无奈只能一个表一个表join看问题出在哪里：
join 前两张表，都很快。当 join taxicity 时候，开始死啦死啦滴慢~
on 的字段都有了索引，只能查数据了。
最终发现：t_vehicle 的 taxi_city 字段竟然有 空值 ！然后加上过滤条件 tcu.taxi_city !=''
FROM t_driver_work_data tv INNER JOIN t_vehicle tcu ON tcu.CAR_NUMBER = tv.CAR_NUMBER and tcu.taxi_city !=''
最终 3s 出结果
0顶
0 踩
共 0 条评论