SQL優化技巧有哪些

這篇文章主要講解了“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');

2. 增加 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 優化

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

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

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

CREATE INDEX index_birthday ON student(birthday);

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

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

而是只查詢需要的字段：select name, age, birthday。

2、Join 優化

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

2. 在使用 join 的時候，應該讓小表做驅動表。小表：總數據量最小的表

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

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

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

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

3、Group By 優化

1. 鴻蒙官方戰略合作共建——HarmonyOS技術社區

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

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

4. 如果 group by 需要統計的數據量不大，盡量只使用內存臨時表;也可以通過適當調大 tmp_table_size 參數，來避免用到磁盤臨時表。

5. 如果數據量實在太大，使用 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 類型的字段盡量避免放在主表中，而是抽出來在子表里，用業務主鍵關聯。

感謝各位的閱讀，以上就是“SQL優化技巧有哪些”的內容了，經過本文的學習后，相信大家對SQL優化技巧有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！