按照這些優化技巧來寫 SQL，連公司 DBA 也鼓掌稱贊！

按照這些優化技巧來寫 SQL，連公司 DBA 也鼓掌稱贊！

剛畢業的我們，都以為使用 MySQL 是非常的簡單的，無非都是照着 【select from where group by order by】 這個格式套來套去；從來不會關注 SQL 的耗費時長，更不會關注查詢的性能。

但是當使用者量上來了，表資料不斷暴增，導緻我們以前寫的 SQL 的查詢時間越來越長，最後還被 DBA 和上司瘋狂吐槽一波。那麼，此時我們是不是應該學習一下如何去優化我們的爛 SQL 呢？

下面，我将從多方面去深入講解如何優化 SQL 。

一、索引優化

索引的資料結構是 B+Tree，而 B+Tree 的查詢性能是比較高的，是以建立索引能提升 SQL 的查詢性能。

1、建立普通索引

對經常出現在 where 關鍵字後面的表字段建立對應的索引。

2、建立複合索引

如果 where 關鍵字後面常出現的有幾個字段，可以建立對應的 複合索引。要注意可以優化的一點是：将單獨出現最多的字段放在前面。

例如現在我們有兩個字段 a 和 b 經常會同時出現在 where 關鍵字後面：

select from t where a = 1 and b = 2; * Q1 \

也有很多 SQL 會單獨使用字段 a 作為查詢條件：

select from t where a = 2; * Q2 \

此時，我們可以建立複合索引 index(a,b)。因為不但 Q1 可以利用複合索引，Q2 也可以利用複合索引。

3、最左字首比對原則

如果我們使用的是複合索引，應該盡量遵循 最左字首比對原則。MySQL 會一直向右比對直到遇到範圍查詢(>、<、between、like)就停止比對。

假如此時我們有一條 SQL ：

select * from t where a = 1 and b = 2 and c > 3 and d = 4;

那麼我們應該建立的複合索引是：index(a,b,d,c) 而不是 index(a,b,c,d)。因為字段 c 是範圍查詢，當 MySQL 遇到範圍查詢就停止索引的比對了。

大家也注意到了，其實 a,b,d 在 SQL 的位置是可以任意調整的，優化器會找到對應的複合索引。

還要注意一點的是：最左字首比對原則不但是複合索引的最左 N 個字段；也可以是單列（字元串類型）索引的最左 M 個字元。

例如我們常說的 like 關鍵字，盡量不要使用全模糊查詢，因為這樣用不到索引；

是以建議是使用右模糊查詢：select * from t where name like '李%'（查詢所有姓李的同學的資訊）。

4、索引下推

很多時候，我們還可以複合索引的 索引下推 來優化 SQL 。

例如此時我們有一個複合索引：index(name,age) ，然後有一條 SQL 如下：

select * from user where name like '張%' and age = 10 and sex = 'm';

根據複合索引的最左字首比對原則，MySQL 比對到複合索引 index(name,age) 的 name 時，就停止比對了；然後接下來的流程就是根據主鍵回表，判斷 age 和 sex 的條件是否同時滿足，滿足則傳回給用戶端。

但是由于有索引下推的優化，比對到 name 時，不會立刻回表；而是先判斷複合索引 index(name,age) 中的 age 是否符合條件；符合條件才進行回表接着判斷 sex 是否滿足，否則會被過濾掉。

那麼借着 MySQL 5.6 引入的索引下推優化 ，可以做到減少回表的次數。

5、覆寫索引

很多時候，我們還可以 覆寫索引 來優化 SQL 。

情況一：SQL 隻查詢主鍵作為傳回值。

主鍵索引（聚簇索引）的葉子節點是整行資料，而普通索引（二級索引）的葉子節點是主鍵的值。

是以當我們的 SQL 隻查詢主鍵值，可以直接擷取對應葉子節點的内容，而避免回表。

情況二：SQL 的查詢字段就在索引裡。

複合索引：假如此時我們有一個複合索引 index(name,age) ，有一條 SQL 如下：

select name,age from t where name like '張%';

由于是字段 name 是右模糊查詢是以可以走複合索引，然後比對到 name 時，不需要回表，因為 SQL 隻是查詢字段 name 和 age，是以直接傳回索引值就 ok 了。

6、普通索引

盡量 使用普通索引 而不是唯一索引。

首先，普通索引和唯一索引的查詢性能其實不會相差很多；當然了，前提是要查詢的記錄都在同一個資料頁中，否則普通索引的性能會慢很多。

但是，普通索引的更新操作性能比唯一索引更好；其實很簡單，因為普通索引能利用 change buffer 來做更新操作；而唯一索引因為要判斷更新的值是否是唯一的，是以每次都需要将磁盤中的資料讀取到 buffer pool 中。

7、字首索引

我們要學會巧妙的使用 字首索引，避免索引值過大。

例如有一個字段是 addr varchar(255)，但是如果一整個建立索引 [ index(addr) ]，會很浪費磁盤空間，是以會選擇建立字首索引 [ index(addr(64)) ]。

建立字首索引，一定要關注字段的區分度。例如像身份證号碼這種字段的區分度很低，隻要出生地一樣，前面好多個字元都是一樣的；這樣的話，最不理想時，可能會掃描全表。

字首索引避免不了回表，即無法使用覆寫索引這個優化點，因為索引值隻是字段的前 n 個字元，需要回表才能判斷查詢值是否和字段值是一緻的。

怎麼解決？

倒序存儲：像身份證這種，後面的幾位區分度就非常的高了；我們可以這麼查詢：

select field_list from t where id_card = reverse('input_id_card_string');

增加 hash 字段并為 hash 字段添加索引。

8、幹淨的索引列

索引列不能參與計算，要保持索引列“幹淨”。

假設我們給表 student 的字段 birthday 建立了普通索引。

下面的 SQL 語句不能利用到索引來提升執行效率：

select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';

我們應該改成下面這樣：

select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');

9、擴充索引

我們應該盡量 擴充索引，而不是新增索引，一個表最好不要超過 5 個索引；一個表的索引越多，會導緻更新操作更加耗費性能。

二、SQL 優化

1、Order By 優化

order by 後面的字段盡量是帶索引的，這樣能避免使用 sort_buffer 進行排序。

假如有一條 SQL，根據生日查詢所有學生的資訊：select * from student order by birthday desc;

那麼為了提升 SQL 的查詢性能，我們可以為 birthday 字段建立索引：

CREATE INDEX index_birthday ON student(birthday);

select 後面不要帶上不必要的字段，因為如果單行長度太長導緻查詢資料太多，MySQL 會利用 rowid 排序來代替全字段排序，這樣會導緻多了回表的操作。

如果我們隻是查詢學生的姓名、年齡和生日，千萬不要寫 select *;

而是隻查詢需要的字段：select name, age, birthday from student order by birthday desc;

2、Join 優化

在使用 join 的時候，應該讓小表做驅動表。小表：總資料量最小的表

使用 join 語句，最好保證能利用被驅動表的索引，不然隻能使用 BNL（Block Nested-Loop Join）算法，還不如不用。

啟用 BKA（Batched Key Access） 算法，使得 NLJ 算法也能利用上 join_buffer，被驅動表可以批量查詢到符合條件的值，然後可以利用 MMR（Multi-Range Read） 的順序讀盤特性來提升回表效率。

如果一定要用 join，而且被驅動表沒有索引可以使用，那麼我們可以利用臨時表（create temporary table xx(...)engine=innodb;）來讓 BNL 算法轉為 BKA 算法，進而提升查詢性能。

join_buffer 是一個無序數組，是以每次判斷都需要周遊整個 join_buffer。我們可以在業務端實作 hash join 來提升 SQL 的執行速度。

3、Group By 優化

如果對 group by 語句的結果沒有排序要求，要在語句後面加 order by null。

盡量讓 group by 過程用上表的索引，不但不需要臨時表，還不需要額外的排序。

如果 group by 需要統計的資料量不大，盡量隻使用記憶體臨時表；也可以通過适當調大 tmp_table_size 參數，來避免用到磁盤臨時表。

如果資料量實在太大，使用 SQL_BIG_RESULT 這個提示，來告訴優化器直接使用排序算法得到 group by 的結果。

4、OR 優化

在 Innodb 引擎下 or 關鍵字無法使用組合索引。

假設現在關于訂單表有一條 SQL ：

select id，product_name from orders where mobile = '12345678900' or user_id = 6;

一般我們為了提升上面 SQL 的查詢效率，會想着為字段 mobile 和 user_id 建立一個複合索引 index(mobile,user_id)；

可是我們使用 explain 可以發現執行計劃裡面并沒有提示到使用複合索引，是以 or 關鍵字無法命中 mobile + user_id 的組合索引。

那麼我們可以分别為兩個字段建立普通索引，然後采用 union 關鍵字，如下所示：

(select id，product_name from orders where mobile = '12345678900')

union

(select id，product_name from orders where user_id = 6);

此時 mobile 和 user_id 字段都有索引，查詢才最高效。

5、IN 優化

in 關鍵字适合主表大子表小，exist 關鍵字适合主表小子表大。由于查詢優化器的不斷更新，很多場景這兩者性能差不多一樣了，可以嘗試改為 join 查詢。

假設我們現在有一條 SQL ，要查詢 VIP 使用者的所有訂單資料：

select id from orders where user_id in (select id from user where level = 'VIP');

我們可以發現不會有任何關于索引的優化，是以我們可以采用 join查詢，如下所示：

select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';

此時被驅動表應該是 user，那麼可以利用到 user 表的主鍵索引，即可以使用 BKA 算法來提升 join 查詢的性能。

6、Like 優化

like 用于模糊查詢，但是如果是全模糊查詢，将不能命中對應字段的索引。

假設現在關于學生表有一條 SQL：

SELECT name,age,birthday FROM student WHERE name like '%張%';

使用 explain 可以發現執行計劃提示查詢未命中索引。

因為本來需求就是查詢姓張的所有同學資訊，是以沒必要使用全模糊查詢，使用右模糊查詢即可。

換成下面的寫法：

SELECT name,age,birthday FROM student WHERE name like '張%';

但是産品經理一定要前後模糊比對呢？全文索引 FULLTEXT 可以嘗試一下，但是 MySQL 的全文索引不支援中文查詢的。

是以說 Elasticsearch 才是終極武器！

三、資料表設計優化

1、資料類型：應該選擇更簡單或者占用空間更小的類型。

整型選擇：可以根據長度選擇 tinyint、smallint、medium_int，而不是直接使用 int。

字元串選擇：能确定字元串長度的，盡量使用 char 類型，而不是變長的 varchar 類型。

浮點型選擇：精度要求比較高的使用 decimal 而不是 double；也可以考慮使用 BIGINT 來儲存，小數位儲存可以使用乘以整百來解決。

日期選擇：盡量使用 timestamp 而不是 datetime。

2、避免空值：

NULL 值依然會占用空間，并且會使索引更新更加複雜，更新 NULL 時容易發生索引分裂的現象。

可以使用有意義的值來代替 NULL 值，例如 “none” 字元串等等。

3、超長字元串：

一般超長字元串，varchar 難以存儲，我們一般會使用 text 類型。

但是 text 類型的字段盡量避免放在主表中，而是抽出來在子表裡，用業務主鍵關聯。

最後

到此結束，如果大家還有更好的優化點，請記得在下方評論，一起學習~

原文位址

https://blog.csdn.net/Howinfun/article/details/106019231