-- 执行如下sql,执行时间为1.159s,最大扫描行3.01M,但是仅返回1条数据 -- SELECT count(*) AS count FROM `zh_change_apply` AS a WHERE a.status=? AND a.tid=? AND (a.uid=? OR a.id IN (SELECT b.caid FROM `zh_change_apply_audit` AS b WHERE b.aid=? AND a.times=b.times GROUP BY b.caid)) -- SELECT SUM(cast(a.total_price AS decimal(?,?))) AS total_price FROM `zh_change_apply` AS a WHERE a.tid=? AND (a.uid=? OR (a.status!=? AND a.id IN (SELECT b.caid FROM `zh_change_apply_audit` AS b WHERE b.aid=? AND a.times=b.times GROUP BY b.caid)) OR a.status=?) -- SELECT a.*,p.name AS account_name FROM `zh_change_apply` AS a LEFT JOIN `zh_project_account` AS p ON a.notice_uid=p.id WHERE a.tid=? AND (a.uid=? OR (a.status!=? AND a.id IN (SELECT b.caid FROM `zh_change_apply_audit` AS b WHERE b.aid=? AND a.times=b.times GROUP BY b.caid)) OR a.status=?) ORDER BY a.in_time DESC LIMIT ?,? -- 优化方案如下: -- 1. 所有类似sql,都去掉a.times=b.times,这个判断实际上没有任何必要,去掉后,执行时间为169ms -- 2. 所有类似的audit表,均添加如下索引,预计速度提升55倍(基于阿里云分析) ALTER TABLE `zh_change_apply_audit` ADD INDEX `idx_aid_caid` (`aid`, `caid`); ALTER TABLE `zh_change_audit` ADD INDEX `idx_uid_cid` (`uid`, `cid`); ALTER TABLE `zh_change_project_audit` ADD INDEX `idx_aid_cpid` (`aid`, `cpid`); ALTER TABLE `zh_change_plan_audit` ADD INDEX `idx_aid_cpid` (`aid`, `cpid`); ALTER TABLE `zh_financial_pay_audit` ADD INDEX `idx_aid_fpid` (`aid`, `fpid`); -- 执行如下sql,执行时间为1.55s,最大扫描行294.76K -- UPDATE `zh_change_audit_list` SET `bwmx` = CASE WHEN `mx_id` = '138dc057-0773-44c6-bad2-160686cf107b' THEN 'K37+808.4~K37+868.4右线S-Ⅳd' ELSE `bwmx` END WHERE `mx_id` IN ('138dc057-0773-44c6-bad2-160686cf107b') -- updateDataByRevisePos方法下生成执行 -- 优化方案,新增mx_id索引,预计性能提升9116倍 ALTER TABLE `calculation`.`zh_change_audit_list` ADD INDEX `idx_mxid` (`mx_id`); ALTER TABLE `calculation`.`zh_stage_rela_pos_final` ADD INDEX `idx_relatid_sid` (`rela_tid`, `sid`); ALTER TABLE `calculation`.`zh_stage_rela_bills_final` ADD INDEX `idx_relatid_sid` (`rela_tid`, `sid`);