歡迎光臨
每天分享高質量文章

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

來自:程式員的成長之路(ID:cxydczzl)

閱讀本文大概需要 6 分鐘。

前言

在網上刷到一篇資料庫優化的文章,自己也來研究一波。

場景

資料庫版本:5.7.25 ,運行在虛擬機中。

課程表

1#課程表
2create table Course(
3c_id int PRIMARY KEY,
4name varchar(10)
5)


增加 100 條資料

 

1#增加課程表100條資料
2DROP PROCEDURE IF EXISTS insert_Course;
3DELIMITER $
4CREATE PROCEDURE insert_Course()
5BEGIN
6    DECLARE i INT DEFAULT 1;

7        WHILE i<=100 DO
8        INSERT INTO Course(`c_id`,`name`) VALUES(i, CONCAT(‘語文’,i+));
9        SET i = i+1;
10    END WHILE;
11END $
12CALL insert_Course();


運行耗時

1CALL insert_Course();
2> OK
3> 時間: 0.152s


課程資料

 

學生表

1#學生表
2create table Student(
3s_id int PRIMARY KEY,
4name varchar(10)
5)


增加 7W 條資料

1#學生表增加70000條資料
2DROP PROCEDURE IF EXISTS insert_Student;
3DELIMITER $
4CREATE PROCEDURE insert_Student()
5BEGIN
6    DECLARE i INT DEFAULT 1;

7        WHILE i<=70000 DO
8        INSERT INTO Student(`s_id`,`name`) VALUES(i, CONCAT(‘張三’,i+));
9        SET i = i+1;
10    END WHILE;
11END $
12CALL insert_Student();


 

運行結果

1CALL insert_Student();
2> OK
3> 時間: 175.838s


學生資料

成績表

1#成績表
2CREATE table Result(
3r_id int PRIMARY KEY,
4s_id int,
5c_id int,
6score int
7)


增加 70W 條資料

1#成績表增加70W條資料
2DROP PROCEDURE IF EXISTS insert_Result;
3DELIMITER $
4CREATE PROCEDURE insert_Result()
5BEGIN
6    DECLARE i INT DEFAULT 1;

7        DECLARE sNum INT DEFAULT 1;
8        DECLARE cNum INT DEFAULT 1;
9        WHILE i<=700000 DO
10                if (sNum%70000 = 0THEN
11                    set sNum = 1;
12                elseif (cNum%100 = 0THEN
13                    set cNum = 1;
14                end if;
15        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`) VALUES(i,sNum ,cNum , (RAND()*99)+1);
16        SET i = i+1;
17                SET sNum = sNum+1;
18                SET cNum = cNum+1;
19    END WHILE;
20END $
21CALL insert_Result();


運行結果

1CALL insert_Result();
2> OK
3> 時間: 2029.5s


成績資料

測試

業務需求

 

查找 語文1 成績為 100 分的考生

 

查詢陳述句

1#查詢語文1考100分的考生
2select s.* from Student s where s.s_id in
3(select s_id from Result r where r.c_id = 1 and r.score = 100)


執行時間:0.937s

 

查詢結果:32 位滿足條件的學生

 

用了 0.9s ,來查看下查詢計劃:

1EXPLAIN
2select s.* from Student s where s.s_id in
3(select s_id from Result r where r.c_id = 1 and r.score = 100)


發現沒有用到索引,type 全是 ALL ,那麼首先想到的就是建立一個索引,建立索引的欄位當然是在 where 條件的欄位了。

 

查詢結果中 type 列:all 是全表掃描,index 是通過索引掃描。

 

先給 Result 表的 c_id 和 score 建立個索引

1CREATE index result_c_id_index on Result(c_id);
2
3CREATE index result_score_index on Result(score);


再次執行上述查詢陳述句,時間為:0.027s

 

快了 34.7 倍(四捨五入),大大縮短了查詢的時間,看來索引能極大程度的提高查詢效率,在合適的列上面建立索引很有必要,很多時候都忘記建立索引,資料量小的時候沒什麼感覺,這優化的感覺很 nice 。

 

相同的 SQL 陳述句多次執行,你會發現第一次是最久的,後面執行所需的時間會比第一次執行短些許,原因是,相同陳述句第二次查詢會直接從快取中讀取。

 

0.027s 很短了,但是還能再進行優化嗎,仔細看下執行計劃:

查看優化後的 SQL :

1SELECT
2    `example`.`s`.`s_id` AS `s_id`,
3    `example`.`s`.`name` AS `name`
4FROM
5    `example`.`Student` `s` semi
6    JOIN ( `example`.`Result` `r` )
7WHERE
8    (
9    ( `example`.`s`.`s_id` = .`s_id` )
10    AND ( `example`.`r`.`score` = 100 )
11    AND ( `example`.`r`.`c_id` = 1 )
12    )


怎麼查看優化後的陳述句呢?

 

方法如下(在命令視窗執行):

1#先執行
2EXPLAIN
3select s.* from Student s where s.s_id in
4(select s_id from Result r where r.c_id = 1 and r.score = 100);
5#在執行
6show warnings;


結果如下

 

有 type = all

 

按照之前的想法,該 SQL 執行的順序是執行子查詢

1select s_id from Result r where r.c_id = 1 and r.score = 100


 

耗時:1.402s

 

得到如下結果(部分)

然後在執行

1select s.* from Student s where s.s_id in
2(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 。

1DROP index result_c_id_index on Result;
2DROP index result_score_index on Result;


連接查詢

1select s.* from
2Student s
3INNER JOIN Result r
4on r.s_id = s.s_id
5where r.c_id = 1 and r.score = 100;


執行耗時:1.293s

 

查詢結果

用了 1.2s ,來看看執行計劃( EXPLAIN + 查詢 SQL 即可查看該 SQL 的執行計劃):

這裡有連表的情況出現,我猜想是不是要給 result 表的 s_id 建立個索引

1CREATE index result_s_id_index on Result(s_id);
2show index from Result;


 

在執行連接查詢

 

耗時:1.17s (有點奇怪,按照所看文章的時間應該會變長的)

 

看下執行計劃:

 

優化後的查詢陳述句為:

1SELECT
2    `example`.`s`.`s_id` AS `s_id`,
3    `example`.`s`.`name` AS `name`
4FROM
5    `example`.`Student` `s`
6    JOIN `example`.`Result` `r`
7WHERE
8    (
9    ( `example`.`s`.`s_id` = `example`.`r`.`s_id` )
10    AND ( `example`.`r`.`score` = 100 )
11    AND ( `example`.`r`.`c_id` = 1 )
12    )


貌似是先做的連接查詢,在進行的 where 條件過濾。

 

回到前面的執行計劃:

這裡是先做的 where 條件過濾,再做連表,執行計劃還不是固定的,那麼我們先看下標準的 sql 執行順序:

正常情況下是先 join 再進行 where 過濾,但是我們這裡的情況,如果先 join ,將會有 70W 條資料發送 join ,因此先執行 where 過濾式明智方案,現在為了排除 mysql 的查詢優化,我自己寫一條優化後的 sql 。

 

先刪除索引

1DROP index result_s_id_index on Result;


執行自己寫的優化 sql

1SELECT
2    s.*
3FROM
4    (
5        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100
6    ) t
7INNER JOIN Student s ON t.s_id = s.s_id


耗時為:0.413s

 

比之前 sql 的時間都要短。

 

查看執行計劃

先提取 result 再連表,這樣效率就高多了,現在的問題是提取 result 的時候出現了掃描表,那麼現在可以明確需要建立相關索引。

1CREATE index result_c_id_index on Result(c_id);
2CREATE index result_score_index on Result(score);


再次執行查詢

1SELECT
2    s.*
3FROM
4    (
5        SELECT * FROM Result r WHERE r.c_id = 1 AND r.score = 100
6    ) t
7INNER JOIN Student s ON t.s_id = s.s_id


耗時為:0.044s

 

這個時間相當靠譜,快了 10 倍。

 

執行計劃:

我們會看到,先提取 result ,再連表,都用到了索引。

 

那麼再來執行下 sql :

1EXPLAIN
2select s.* from
3Student s
4INNER JOIN Result r
5on r.s_id = s.s_id
6where r.c_id = 1 and r.score = 100;


執行耗時:0.050s

 

執行計劃:

這裡是 mysql 進行了查詢陳述句優化,先執行了 where 過濾,再執行連接操作,且都用到了索引。

 

擴大測試資料,調整內容為 result 表的資料增長到 300W ,學生資料更為分散。

1DROP PROCEDURE IF EXISTS insert_Result_TO300W;
2DELIMITER $
3CREATE PROCEDURE insert_Result_TO300W()
4BEGIN
5    DECLARE i INT DEFAULT 700001;

6        DECLARE sNum INT DEFAULT 1;
7        DECLARE cNum INT DEFAULT 1;
8        WHILE i<=3000000 DO
9        INSERT INTO Result(`r_id`,`s_id`,`c_id`,`score`)
10                VALUES(i,(RAND()*69999)+1 ,(RAND()*99)+1 , (RAND()*99)+1);
11        SET i = i+1;
12    END WHILE;
13END $
14CALL insert_Result_TO300W();


更換了一下資料生成的方式,全部採用隨機數格式。

 

先回顧下:

1show index from Result;


執行 sql

1select s.* from
2Student s
3INNER JOIN Result r
4on r.s_id = s.s_id
5where r.c_id = 81 and r.score = 84;


 

執行耗時:1.278s

 

執行計劃:

這裡用到了 intersect 並集操作,即兩個索引同時檢索的結果再求並集,再看欄位 score 和 c_id 的區分度,但從一個欄位看,區分度都不是很大,從 Result 表檢索,c_id  = 81 檢索的結果是 81 ,score = 84 的結果是 84 。

 

而 c_id = 81 and score = 84 的結果是 19881,即這兩個欄位聯合起來的區分度還是比較高的,因此建立聯合索引查詢效率將會更高,從另外一個角度看,該表的資料是 300W ,以後會更多,就索引儲存而言,都是不小的數目,隨著資料量的增加,索引就不能全部加載到記憶體,而是要從磁盤讀取,這樣索引的個數越多,讀磁盤的開銷就越大,因此根據具體業務情況建立多列的聯合索引是必要的,我們來試試。

1DROP index result_c_id_index on Result;
2DROP index result_score_index on Result;
3CREATE index result_c_id_score_index on Result(c_id,score);


指向上述查詢陳述句

 

消耗時間:0.025s

 

這個速度就就很快了,可以接受。

該陳述句的優化暫時告一段落。

總結

  • MySQL 嵌套子查詢效率確實比較低

  • 可以將其優化成連接查詢

  • 連接表時,可以先用 where 條件對錶進行過濾,然後做表連接(雖然 MySQL 會對連表陳述句做優化)

  • 建立合適的索引,必要時建立多列聯合索引

  • 學會分析 sql 執行計劃,mysql 會對 sql 進行優化,所有分析計劃很重要

知識擴展

索引優化

 

上面講到子查詢的優化,以及如何建立索引,而且在多個欄位索引時,分別對欄位建立了單個索引。

 

後面發現其實建立聯合索引效率會更高,尤其是在資料量較大,單個列區分度不高的情況下。

 

單列索引

 

查詢陳述句如下:

 

1select * from user_test_copy where sex = 2 and type = 2 and age = 10


 

索引:

 

1CREATE index user_test_index_sex on user_test_copy(sex);
2CREATE index user_test_index_type on user_test_copy(type);
3CREATE index user_test_index_age on user_test_copy(age);


 


分別對 sex ,type ,age 欄位做了索引,資料量為300w

 

查詢時間:0.415s

 

執行計劃:

 

發現 type = index_merge

 

這是mysql對多個單列索引的優化,對結果集採用intersect並集操作

多列索引。

 

多列索引

 

我們可以在這3個列上建立多列索引,將表copy一份以便做測試。

 

1create index user_test_index_sex_type_age on user_test(sex,type,age);


 

查詢陳述句:

 

1select * from user_test where sex = 2 and type = 2 and age = 10


 

執行時間:0.032s

 

快了10多倍,且多列索引的區分度越高,提高的速度也越多。

 

執行計劃:

 

最左前綴

 

多列索引還有最左前綴的特性:

 

都會使用到索引,即索引的第一個欄位sex要出現在where條件中。

 

執行一下陳述句:

 

1select * from user_test where sex = 2
2select * from user_test where sex = 2 and type = 2
3select * from user_test where sex = 2 and age = 10


 


索引改寫

 

就是查詢的列都建立了索引,這樣在獲取結果集的時候不用再去磁盤獲取其它列的資料,直接傳回索引資料即可

 

如:

 

1select sex,type,age from user_test where sex = 2 and type = 2 and age = 10


 

執行時間:0.003s

 

要比取所有欄位快的多

 

排序

 

1select * from user_test where sex = 2 and type = 2 ORDER BY user_name


 

時間:0.139s

 

在排序欄位上建立索引會提高排序的效率

 

1select * from user_test where sex = 2 and type = 2 ORDER BY user_name


 

最後附上一些sql調優的總結,以後有時間再深入研究

  • 列型別儘量定義成數值型別,且長度盡可能短,如主鍵和外鍵,型別欄位等等

  • 建立單列索引

  • 根據需要建立多列聯合索引

    • 當單個列過濾之後還有很多資料,那麼索引的效率將會比較低,即列的區分度較低,那麼如果在多個列上建立索引,那麼多個列的區分度就大多了,將會有顯著的效率提高。

  • 根據業務場景建立改寫索引

    • 只查詢業務需要的欄位,如果這些欄位被索引改寫,將極大的提高查詢效率

  • 多表連接的欄位上需要建立索引

    • 這樣可以極大的提高表連接的效率

  • where條件欄位上需要建立索引

  • 排序欄位上需要建立索引

  • 分組欄位上需要建立索引

  • Where條件上不要使用運算函式,以免索引失效

    赞(0)

    分享創造快樂