开发反馈sql执行速度慢,单条查询耗时13秒,写在程序里面不合适。sql如下
SELECT
ter.terminal_id,
ter.terminal_name
FROM
dyys_machine_dealer t,
dyys_machine m,
dyys_machine_terminal a,
dyys_terminal ter
WHERE
t.machine_id = m.machine_id
AND m.machine_id = a.machine_id
AND a.terminal_id = ter.terminal_id
AND t.dealer_id = '5ebfb83b25af7e335'
AND t.deleted = 0
AND m.deleted = 0
AND m.is_smart = 2
AND m.`status` = 2
AND a.del_flag = 0
查看执行计划发现dyys_terminal表不走索引,而是全表扫描。执行计划如下:
查看show index from dyys_terminal的索引情况
查看show create table dyys_terminal\G的索引情况,如下:
发现两个表的字符集的问题了,小的往大的转,导致索引失效了
a.terminal_id = ter.terminal_id,实际会做一个转换a.terminal_id = 转换函数(ter.terminal_id)
更新dyys_terminal表的字符集,重新查询。
重新跑sql,发现耗时1.4秒,yyds
查看执行计划:
当时想过改写sql,但是介于业务要求:(仅供SELECT
m.machine_id,
m.machine_code,
m.machine_type_name,
m.bind_phone,
m.machine_status,
m.sales_volume,
m.residue,
m.machine_type_code
FROM
( SELECT machine_id FROM dyys_machine_dealer WHERE dealer_id = 'afec1da705bb3' AND deleted = 0 ) t
INNER JOIN (
SELECT
*
FROM
dyys_machine
WHERE
deleted = 0
AND is_smart = 2
AND `status` = 2
AND EXISTS ( SELECT machine_id FROM dyys_machine_terminal WHERE del_flag = 0 AND EXISTS ( SELECT terminal_id FROM dyys_terminal ) )) m ON m.machine_id = t.machine_id查看)