背景:库房系统项目迁移,两个版本的结构发生了很大变化,新版本的库存系统在开发阶段由于数据量小,根据看不出查询的性能问题,还沾沾自喜的想新版本多好。但是在做同步之后(规则变更,需要插入很多不同的表),查询变得很慢。
通过查询
SHOW PROCESSLIST;
看到一推的查询正在执行查询,因为我的查询中包含了很多的试图,且存在视图套视图的情况,数据量一上来,查询完蛋了。
使用
KILL QUERY 78; -- 78 是SHOW PROCESSLIST 查询到 ID
来结束查询。
然后创建中间表来代替试图的方式,结果,查询变得很快。中间表的结果和视图一模一样。例如:
CREATE table tv_purchase_status AS
SELECT a.* FROM w_purchase_status a,
(
SELECT max(purchase_status_id) AS purchase_status_id FROM w_purchase_status
GROUP BY purchase_id, purchase_status
) b
WHERE a.purchase_status_id = b.purchase_status_id
只是,视图的查询结果会实时更新,所以本次需要建立触发器来对表进行更新
create trigger update_trigger_purchase_status
after insert on w_purchase_status
for each row
begin
INSERT INTO tv_purchase_status (purchase_id, purchase_status, opt_by, opt_time) VALUES (new.purchase_id, new.purchase_status, new.opt_by, NOW())
ON DUPLICATE KEY UPDATE opt_by = new.opt_by, opt_time = NOW();
end;
然后我原始的查询中使用到的视图换成我新建的中间表tv_purchase_status,这样我的查询立马变得很快。