Calculation/sql/index.sql

-- 执行如下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`);