一次非常有意思的 SQL 優(yōu)化經(jīng)歷！
掃描二維碼
隨時(shí)隨地手機(jī)看文章
本文來(lái)源：
https://www.cnblogs.com/tangyanbo/p/4462734.html
場(chǎng)景
索引優(yōu)化
單列索引
多列索引
索引覆蓋
排序
場(chǎng)景
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)100條
學(xué)生表:
create table Student(
id int PRIMARY KEY,
name varchar(10)
)
數(shù)據(jù)70000條
學(xué)生成績(jī)表SC
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
數(shù)據(jù)70w條
查詢(xún)目的：
查找語(yǔ)文考100分的考生
查詢(xún)語(yǔ)句：
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
執(zhí)行時(shí)間：30248.271s
暈,為什么這么慢，先來(lái)查看下查詢(xún)計(jì)劃：
EXPLAIN
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
查看優(yōu)化后的sql:
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
WHERE
< in_optimizer > (
`YSB`.`s`.`s_id` ,< EXISTS > (
SELECT
1
FROM
`YSB`.`SC` `sc`
WHERE
(
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
AND (
< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
)
)
)
)
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗時(shí)：0.001s
得到如下結(jié)果：
然后再執(zhí)行
select s.* from Student s where s.s_id in(7,29,5000)
耗時(shí)：0.001s
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
優(yōu)化后的查詢(xún)語(yǔ)句為：
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
(
(
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
)
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
)
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再執(zhí)行查詢(xún)：
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
執(zhí)行時(shí)間為：0.001s，這個(gè)時(shí)間相當(dāng)靠譜，快了50倍
執(zhí)行計(jì)劃：
我們會(huì)看到，先提取sc，再連表，都用到了索引。
那么再來(lái)執(zhí)行下sql
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
執(zhí)行時(shí)間0.001s
執(zhí)行計(jì)劃：
執(zhí)行sql
SELECT s.* from
Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=81 and sc.score=84
執(zhí)行時(shí)間：0.061s，這個(gè)時(shí)間稍微慢了點(diǎn)
執(zhí)行計(jì)劃：
alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
create index sc_c_id_score_index on SC(c_id,score);
索引優(yōu)化
單列索引
select * from user_test_copy where sex = 2 and type = 2 and age = 10
索引：
CREATE index user_test_index_sex on user_test_copy(sex);
CREATE index user_test_index_type on user_test_copy(type);
CREATE index user_test_index_age on user_test_copy(age);
分別對(duì)sex,type,age字段做了索引，數(shù)據(jù)量為300w,查詢(xún)時(shí)間：0.415s
執(zhí)行計(jì)劃：
發(fā)現(xiàn)type=index_merge
這是mysql對(duì)多個(gè)單列索引的優(yōu)化，對(duì)結(jié)果集采用intersect并集操作
多列索引
我們可以在這3個(gè)列上建立多列索引，將表copy一份以便做測(cè)試
create index user_test_index_sex_type_age on user_test(sex,type,age);
查詢(xún)語(yǔ)句：
select * from user_test where sex = 2 and type = 2 and age = 10
執(zhí)行時(shí)間：0.032s，快了10多倍，且多列索引的區(qū)分度越高，提高的速度也越多
執(zhí)行計(jì)劃：
最左前綴
多列索引還有最左前綴的特性：
執(zhí)行一下語(yǔ)句：
select * from user_test where sex = 2
select * from user_test where sex = 2 and type = 2
select * from user_test where sex = 2 and age = 10
索引覆蓋
select sex,type,age from user_test where sex = 2 and type = 2 and age = 10
執(zhí)行時(shí)間：0.003s
要比取所有字段快的多
排序
select * from user_test where sex = 2 and type = 2 ORDER BY user_name
時(shí)間：0.139s
在排序字段上建立索引會(huì)提高排序的效率
create index user_name_index on user_test(user_name)
最后附上一些sql調(diào)優(yōu)的總結(jié)，以后有時(shí)間再深入研究
列類(lèi)型盡量定義成數(shù)值類(lèi)型，且長(zhǎng)度盡可能短，如主鍵和外鍵，類(lèi)型字段等等
建立單列索引
根據(jù)需要建立多列聯(lián)合索引
當(dāng)單個(gè)列過(guò)濾之后還有很多數(shù)據(jù)，那么索引的效率將會(huì)比較低，即列的區(qū)分度較低，
那么如果在多個(gè)列上建立索引，那么多個(gè)列的區(qū)分度就大多了，將會(huì)有顯著的效率提高。
根據(jù)業(yè)務(wù)場(chǎng)景建立覆蓋索引
只查詢(xún)業(yè)務(wù)需要的字段，如果這些字段被索引覆蓋，將極大的提高查詢(xún)效率
多表連接的字段上需要建立索引
這樣可以極大的提高表連接的效率
where條件字段上需要建立索引
排序字段上需要建立索引
分組字段上需要建立索引
Where條件上不要使用運(yùn)算函數(shù)，以免索引失效
特別推薦一個(gè)分享架構(gòu)+算法的優(yōu)質(zhì)內(nèi)容，還沒(méi)關(guān)注的小伙伴，可以長(zhǎng)按關(guān)注一下：
長(zhǎng)按訂閱更多精彩▼
如有收獲，點(diǎn)個(gè)在看，誠(chéng)摯感謝
免責(zé)聲明：本文內(nèi)容由21ic獲得授權(quán)后發(fā)布，版權(quán)歸原作者所有，本平臺(tái)僅提供信息存儲(chǔ)服務(wù)。文章僅代表作者個(gè)人觀點(diǎn)，不代表本平臺(tái)立場(chǎng)，如有問(wèn)題，請(qǐng)聯(lián)系我們，謝謝！
下載文檔
