建表
CREATE TABLE `order` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `name` varchar(32) NOT NULL,
? `creator` varchar(24) NOT NULL,
? `price` varchar(64) NOT NULL,
? `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
? `status` tinyint(1) not null,
? PRIMARY KEY (`id`)
);
CREATE TABLE `order_item` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `name` varchar(32) NOT NULL,
? `parent` bigint(20) NOT NULL,
? `status` int not null,
? `type` varchar(12) NOT NULL DEFAULT '0',
? `quantity` int not null default 1,
? `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
? PRIMARY KEY (`id`)
);
待優(yōu)化語句①
SELECT *
FROM? `order` o
? ? ? INNER JOIN order_item i ON i.parent = o.id
ORDER? BY o.status ASC,
? ? ? ? ? i.update_time DESC
LIMIT? 0, 20;
o表大約2000數(shù)據(jù)，i表大約50W行數(shù)據(jù)
思路
o表的status只有1和0這2種值，分別令status的值=1和=0，分別取前20，再把結(jié)果集UNION ALL起來在進(jìn)行一次排序。
如果不采用這種方式，將會(huì)走全表掃描之后再進(jìn)行2次排序，掃描行數(shù)非常多，相當(dāng)耗時(shí)
官方答案
SELECT o.*,i.*
FROM? (
? ? ? ? (SELECT o.id,
? ? ? ? ? ? i.id item_id
? ? ? FROM? `order` o
? ? ? ? ? ? INNER JOIN order_item i
? ? ? ? ? ? ? ? ? ? ON i.parent =o.id
? ? ? ? ? WHERE? o.status = 0
? ? ? ? ? ORDER? BY i.update_time DESC
? ? ? ? ? LIMIT? 0, 20)
? ? ? ? ? UNION ALL
? ? ? ? ? (SELECT o.id,
? ? ? ? ? ? i.id item_id
? ? ? ? ? FROM? `order` o
? ? ? ? ? ? INNER JOIN order_item i
? ? ? ? ? ? ? ? ? ? ON i.parent =o.id
? ? ? ? ? WHERE? o.status = 1
? ? ? ? ? ORDER? BY i.update_time DESC
? ? ? ? ? LIMIT? 0, 20)
? ? ? ? ) tmp
? ? ? INNER JOIN `order` o ON tmp.id = o.id
? ? ? INNER JOIN order_item i ON tmp.item_id =i.id
ORDER? BY o.status ASC,
? ? ? ? ? i.update_time DESC
LIMIT? 0, 20;
需添加的索引
alter table order_item add index idx_1(update_time,parent);
alter table `order` add index idx_0(status);
待優(yōu)化語句②
update `order` set
create_time = now()
where id in (
? ? select parent from order_item where type = 2
);
該語句第一眼望過去涉及到2個(gè)知識(shí)點(diǎn)，第一個(gè)反應(yīng)是in改inner join，第二個(gè)是臨時(shí)表內(nèi)的去重。
按照官方說法，這個(gè)語句除了上面的2個(gè)知識(shí)點(diǎn)外，還有對(duì)數(shù)據(jù)類型的核對(duì)，type是一個(gè)varchar類型，而臨時(shí)表的查詢直接改變了類型；其次在對(duì)臨時(shí)表的去重過程中，很多人使用了distinct，按照官方說法，臨時(shí)表的distinct會(huì)造成半連接失效，所以這里不能用distinct，而是需要用group by。
需要添加的索引
alter table `order` add index idx_1(type,parent);
語句改寫
update `order` o inner join
(select type,parent from`order_item`where type='2' group by type,parent)?i?
on o.id?=?i.parent
set create_time?=now();
