一次非常有趣的 SQL 優化經歷

孤獨鍵客發表於2019-03-26
SQL優化
閱讀本文大概需要 6 分鐘。
前言
在網上刷到一篇資料庫優化的文章，自己也來研究一波。
場景
資料庫版本：5.7.25 ，執行在虛擬機器中。
課程表
#課程表
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)複製程式碼
增加 100 條資料
#增加課程表100條資料
DROP PROCEDURE IF EXISTS insert_Course;
DELIMITER $
CREATE PROCEDURE insert_Course()
BEGIN
    DECLARE i INT DEFAULT 1;
        WHILE i<=100 DO
        INSERT INTO Course(`c_id`,`name`) VALUES(i, CONCAT('語文',i+''));
        SET i = i+1;
    END WHILE;
END $
CALL insert_Course();複製程式碼
執行耗時
CALL insert_Course();
> OK
> 時間: 0.152s複製程式碼
課程資料
一次非常有趣的 SQL 優化經歷
學生表
#學生表
create table Student(
s_id int PRIMARY KEY,
name varchar(10)
)複製程式碼
增加 7W 條資料
#學生表增加70000條資料
DROP PROCEDURE IF EXISTS insert_Student;
DELIMITER $
CREATE PROCEDURE insert_Student()
BEGIN
    DECLARE i INT DEFAULT 1;
        WHILE i<=70000 DO
        INSERT INTO Student(`s_id`,`name`) VALUES(i, CONCAT('張三',i+''));
        SET i = i+1;
    END WHILE;
END $
CALL insert_Student();複製程式碼
執行結果
CALL insert_Student();
> OK
> 時間: 175.838s複製程式碼
學生資料
一次非常有趣的 SQL 優化經歷
成績表
#成績表
CREATE table Result(
r_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)複製程式碼
增加 70W 條資料
#成績表增加70W條資料
DROP PROCEDURE IF EXISTS insert_Result;
DELIMITER $
CREATE PROCEDURE insert_Result()
BEGIN
    DECLARE i INT DEFAULT 1;
        DECLARE sNum INT DEFAULT 1;
        DECLARE cNum INT DEFAULT 1;
        WHILE i<=700000 DO
                if (sNum%70000 = 0) THEN
                    set sNum = 1;
                elseif (cNum%100 = 0) THEN 
                    set cNum = 1;
                end if;
        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`) VALUES(i,sNum ,cNum , (RAND()*99)+1);
        SET i = i+1;
                SET sNum = sNum+1;
                SET cNum = cNum+1;
    END WHILE;
END $
CALL insert_Result();複製程式碼
執行結果
CALL insert_Result();
> OK
> 時間: 2029.5s複製程式碼
成績資料
一次非常有趣的 SQL 優化經歷
測試
業務需求
查詢 語文1 成績為 100 分的考生
查詢語句
#查詢語文1考100分的考生
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100)複製程式碼
執行時間：0.937s
查詢結果：32 位滿足條件的學生
用了 0.9s ，來檢視下查詢計劃：
EXPLAIN
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100)複製程式碼
一次非常有趣的 SQL 優化經歷
發現沒有用到索引，type 全是 ALL ，那麼首先想到的就是建立一個索引，建立索引的欄位當然是在 where 條件的欄位了。
查詢結果中 type 列：all 是全表掃描，index 是通過索引掃描。
先給 Result 表的 c_id 和 score 建立個索引
CREATE index result_c_id_index on Result(c_id);

CREATE index result_score_index on Result(score);複製程式碼
再次執行上述查詢語句，時間為：0.027s
快了 34.7 倍（四捨五入），大大縮短了查詢的時間，看來索引能極大程度的提高查詢效率，在合適的列上面建立索引很有必要，很多時候都忘記建立索引，資料量小的時候沒什麼感覺，這優化的感覺很 nice 。
相同的 SQL 語句多次執行，你會發現第一次是最久的，後面執行所需的時間會比第一次執行短些許，原因是，相同語句第二次查詢會直接從快取中讀取。
0.027s 很短了，但是還能再進行優化嗎，仔細看下執行計劃：
一次非常有趣的 SQL 優化經歷
檢視優化後的 SQL ：
SELECT
    `example`.`s`.`s_id` AS `s_id`,
    `example`.`s`.`name` AS `name` 
FROM
    `example`.`Student` `s` semi
    JOIN ( `example`.`Result` `r` ) 
WHERE
    (
    ( `example`.`s`.`s_id` = `<subquery2>`.`s_id` ) 
    AND ( `example`.`r`.`score` = 100 ) 
    AND ( `example`.`r`.`c_id` = 1 ) 
    )複製程式碼
怎麼檢視優化後的語句呢？
方法如下（在命令視窗執行）：
#先執行
EXPLAIN
select s.* from Student s where s.s_id in 
(select s_id from Result r where r.c_id = 1 and r.score = 100);
#在執行
show warnings;複製程式碼
結果如下
一次非常有趣的 SQL 優化經歷
有 type = all
按照之前的想法，該 SQL 執行的順序是執行子查詢
select s_id from Result r where r.c_id = 1 and r.score = 100複製程式碼
耗時：1.402s
得到如下結果（部分）
一次非常有趣的 SQL 優化經歷
然後在執行
select s.* from Student s where s.s_id in 
(12871,40987,46729,61381,3955,10687,14047,26917,28897,31174,38896,56518,10774,25030,9778,12544,24721,27295,60361,
38479,46990,66988,6790,35995,46192,47578,58171,63220,6685,67372,46279,64693)複製程式碼
耗時：0.222s
比一起執行快多了，檢視優化後的 SQL 語句，發現MySQL 竟然不是先執行裡層的查詢，而是將 SQL 優化成了 exists 字句，執行計劃中的 select_type 為 MATERIALIZED（物化子查詢）。MySQL 先執行外層查詢，在執行裡層的查詢，這樣就要迴圈學生數量*滿足條件的學生 ID 次，也就是 7W * 32 次。
物化子查詢： 優化器使用物化能夠更有效的來處理子查詢。物化通過將子查詢結果作為一個臨時表來加快查詢執行速度，正常來說是在記憶體中的。mysql 第一次需要子查詢結果是，它物化結果到一張臨時表中。在之後的任何地方需要該結果集，mysql 會再次引用臨時表。優化器也許會使用一個雜湊索引來使得查詢更快速代價更小。索引是唯一的，排除重複並使得表資料更少。
那麼改用連線查詢呢？
這裡為了重新分析連線查詢的情況，先暫時刪除索引 result_c_id_index ，result_score_index 。
DROP index result_c_id_index on Result;
DROP index result_score_index on Result;複製程式碼
連線查詢
select s.* from 
Student s 
INNER JOIN Result r 
on r.s_id = s.s_id 
where r.c_id = 1 and r.score = 100;複製程式碼
執行耗時：1.293s
查詢結果
一次非常有趣的 SQL 優化經歷
用了 1.2s ，來看看執行計劃（ EXPLAIN + 查詢 SQL 即可檢視該 SQL 的執行計劃）：
一次非常有趣的 SQL 優化經歷
這裡有連表的情況出現，我猜想是不是要給 result 表的 s_id 建立個索引
CREATE index result_s_id_index on Result(s_id);
show index from Result;複製程式碼
一次非常有趣的 SQL 優化經歷
在執行連線查詢
耗時：1.17s （有點奇怪，按照所看文章的時間應該會變長的）
看下執行計劃：
一次非常有趣的 SQL 優化經歷
優化後的查詢語句為：
SELECT
    `example`.`s`.`s_id` AS `s_id`,
    `example`.`s`.`name` AS `name` 
FROM
    `example`.`Student` `s`
    JOIN `example`.`Result` `r` 
WHERE
    (
    ( `example`.`s`.`s_id` = `example`.`r`.`s_id` ) 
    AND ( `example`.`r`.`score` = 100 ) 
    AND ( `example`.`r`.`c_id` = 1 ) 
    )複製程式碼
貌似是先做的連線查詢，在進行的 where 條件過濾。
回到前面的執行計劃：
一次非常有趣的 SQL 優化經歷
這裡是先做的 where 條件過濾，再做連表，執行計劃還不是固定的，那麼我們先看下標準的 sql 執行順序：
一次非常有趣的 SQL 優化經歷
正常情況下是先 join 再進行 where 過濾，但是我們這裡的情況，如果先 join ，將會有 70W 條資料傳送 join ，因此先執行 where 過濾式明智方案，現在為了排除 mysql 的查詢優化，我自己寫一條優化後的 sql 。
先刪除索引
DROP index result_s_id_index on Result;複製程式碼
執行自己寫的優化 sql
SELECT
    s.* 
FROM
    (
        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100 
    ) t
INNER JOIN Student s ON t.s_id = s.s_id複製程式碼
耗時為：0.413s
比之前 sql 的時間都要短。
檢視執行計劃
一次非常有趣的 SQL 優化經歷
先提取 result 再連表，這樣效率就高多了，現在的問題是提取 result 的時候出現了掃描表，那麼現在可以明確需要建立相關索引。
CREATE index result_c_id_index on Result(c_id);
CREATE index result_score_index on Result(score);複製程式碼
再次執行查詢
SELECT
    s.* 
FROM
    (
        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100 
    ) t
INNER JOIN Student s ON t.s_id = s.s_id複製程式碼
耗時為：0.044s
這個時間相當靠譜，快了 10 倍。
執行計劃：
一次非常有趣的 SQL 優化經歷
我們會看到，先提取 result ，再連表，都用到了索引。
那麼再來執行下 sql ：
EXPLAIN
select s.* from 
Student s 
INNER JOIN Result r 
on r.s_id = s.s_id 
where r.c_id = 1 and r.score = 100;複製程式碼
執行耗時：0.050s
執行計劃：
一次非常有趣的 SQL 優化經歷
這裡是 mysql 進行了查詢語句優化，先執行了 where 過濾，再執行連線操作，且都用到了索引。
擴大測試資料，調整內容為 result 表的資料增長到 300W ，學生資料更為分散。
DROP PROCEDURE IF EXISTS insert_Result_TO300W;
DELIMITER $
CREATE PROCEDURE insert_Result_TO300W()
BEGIN
    DECLARE i INT DEFAULT 700001;
        DECLARE sNum INT DEFAULT 1;
        DECLARE cNum INT DEFAULT 1;
        WHILE i<=3000000 DO
        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`) 
                VALUES(i,(RAND()*69999)+1 ,(RAND()*99)+1 , (RAND()*99)+1);
        SET i = i+1;
    END WHILE;
END $
CALL insert_Result_TO300W();複製程式碼
更換了一下資料生成的方式，全部採用隨機數格式。
先回顧下：
show index from Result;複製程式碼
一次非常有趣的 SQL 優化經歷
執行 sql
select s.* from 
Student s
INNER JOIN Result r
on r.s_id = s.s_id
where r.c_id = 81 and r.score = 84;複製程式碼
執行耗時：1.278s
執行計劃：
一次非常有趣的 SQL 優化經歷
這裡用到了 intersect 並集操作，即兩個索引同時檢索的結果再求並集，再看欄位 score 和 c_id 的區分度，但從一個欄位看，區分度都不是很大，從 Result 表檢索，c_id = 81 檢索的結果是 81 ，score = 84 的結果是 84 。
而 c_id = 81 and score = 84 的結果是 19881，即這兩個欄位聯合起來的區分度還是比較高的，因此建立聯合索引查詢效率將會更高，從另外一個角度看，該表的資料是 300W ，以後會更多，就索引儲存而言，都是不小的數目，隨著資料量的增加，索引就不能全部載入到記憶體，而是要從磁碟讀取，這樣索引的個數越多，讀磁碟的開銷就越大，因此根據具體業務情況建立多列的聯合索引是必要的，我們來試試。
DROP index result_c_id_index on Result;
DROP index result_score_index on Result;
CREATE index result_c_id_score_index on Result(c_id,score);複製程式碼
指向上述查詢語句
消耗時間：0.025s
這個速度就就很快了，可以接受。
一次非常有趣的 SQL 優化經歷
該語句的優化暫時告一段落。
總結
  • MySQL 巢狀子查詢效率確實比較低
  • 可以將其優化成連線查詢
  • 連線表時，可以先用 where 條件對錶進行過濾，然後做表連線（雖然 MySQL 會對連表語句做優化）
  • 建立合適的索引，必要時建立多列聯合索引
  • 學會分析 sql 執行計劃，mysql 會對 sql 進行優化，所有分析計劃很重要
知識擴充套件

索引優化

上面講到子查詢的優化，以及如何建立索引，而且在多個欄位索引時，分別對欄位建立了單個索引。
後面發現其實建立聯合索引效率會更高，尤其是在資料量較大，單個列區分度不高的情況下。

單列索引

查詢語句如下：
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);複製程式碼
分別對 sex ，type ，age 欄位做了索引，資料量為300w
查詢時間：0.415s
執行計劃：
一次非常有趣的 SQL 優化經歷
發現 type = index_merge
這是mysql對多個單列索引的優化，對結果集採用intersect並集操作

多列索引

多列索引
我們可以在這3個列上建立多列索引，將表copy一份以便做測試。
create index user_test_index_sex_type_age on user_test(sex,type,age);複製程式碼
查詢語句：
select * from user_test where sex = 2 and type = 2 and age = 10複製程式碼
執行時間：0.032s
快了10多倍，且多列索引的區分度越高，提高的速度也越多。
執行計劃：
一次非常有趣的 SQL 優化經歷

最左字首

多列索引還有最左字首的特性：
都會使用到索引，即索引的第一個欄位sex要出現在where條件中。
執行一下語句：
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複製程式碼
執行時間：0.003s
要比取所有欄位快的多

排序

select * from user_test where sex = 2 and type = 2 ORDER BY user_name複製程式碼
時間：0.139s
在排序欄位上建立索引會提高排序的效率
select * from user_test where sex = 2 and type = 2 ORDER BY user_name複製程式碼
最後附上一些sql調優的總結，以後有時間再深入研究
  • 列型別儘量定義成數值型別，且長度儘可能短，如主鍵和外來鍵，型別欄位等等
  • 建立單列索引
  • 根據需要建立多列聯合索引
  • 當單個列過濾之後還有很多資料，那麼索引的效率將會比較低，即列的區分度較低，那麼如果在多個列上建立索引，那麼多個列的區分度就大多了，將會有顯著的效率提高。
  • 根據業務場景建立覆蓋索引
  • 只查詢業務需要的欄位，如果這些欄位被索引覆蓋，將極大的提高查詢效率
  • 多表連線的欄位上需要建立索引
  • 這樣可以極大的提高表連線的效率
  • where條件欄位上需要建立索引
  • 排序欄位上需要建立索引
  • 分組欄位上需要建立索引
  • Where條件上不要使用運算函式，以免索引失效


相關文章