《MySQL慢查詢優化》之SQL語句及索引優化

馬非白即黑發表於2020-12-06
MySql優化索引

1、慢查詢優化方式

  • 伺服器硬體升級優化

  • Mysql伺服器軟體優化

  • 資料庫表結構優化

  • SQL語句及索引優化

本文重點關注於SQL語句及索引優化，關於其他優化方式以及索引原理等，請關注本人《MySQL慢查詢優化》系列博文。優化我個人遵循的原則：積小勝為大勝，以空間換時間。-《論持久戰》

 

2、資料來源

　　工欲善其事必先利其器，為了測試與驗證的方便，資料庫可以直接採用MySQL官方提供的測試資料庫employees，該資料庫關係複雜度適中以及資料量較大，適合做SQL語句及索引優化分析，引用官方instruction：

The database contains about 300,000 employee records with 2.8 million salary entries. 
The export data is 167 MB, which is not huge, but heavy enough to be non-trivial for testing.

 

3、分析工具

　　採用explain指令直接模擬Mysql優化器執行SQL語句，檢視SQL語句的執行計劃。

示例：

 

explain命令執行結果包括若干引數：id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra；重點關注type、possible_keys、key、key_len、extra 這五個引數。

  • possible_keys：此次查詢中可能會被選用的索引，注意這些索引不一定被查詢使用到。
  • key：此次查詢中真正使用到的索引。當為複合索引時，不確定是否被充分使用。
  • type：訪問型別，表示MySQL在表中查詢所需行的方式。常用的型別有： ALL, index, range, ref, eq_ref, const, system, NULL（效能從左到右逐步提升），其中：
ALL Full Table Scan， MySQL將遍歷全表以找到匹配的行；
index Full Index Scan，index與ALL區別為index型別只遍歷索引樹；
range 只檢索給定範圍的行，使用一個索引來選擇行；
ref 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值；
eq_ref 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件；

const

system

 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量,system是const型別的特例，當查詢的表只有一行的情況下，使用system；
NULL MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查詢完成。
  • key_len：表示索引中使用的位元組數，用來計算索引是否被充分使用，不損失精確性的情況下，長度越短越好 ；
key_len=字元長度*位元組數+型別+是否允許為空
索引是否充分使用：複合索引每個列都需要計算，所有索引列都生效了才是充分利用。

計算規則：

。位元組數相關：長度、字元編碼、型別（int+0，char+0，varchar+2）、是否允許為空（空+1，非空+0）；

。int型別位元組數為4；

。char和varchar的長度是指字元數，一個字元在編碼gbk為2個位元組、utf-8為3個位元組，需要：字元數*位元組。

示例：

name varchar(50) 表示是varchar型別，長度為50，允許為空，假設是utf8編碼 key_len=50*3+2+1=153
emp_no int(255) 表示int型別，位元組數為4，允許為空，跟長度和編碼無關 key_len=4+0=4
  •  extra：
Using where說明：SQL使用了where條件過濾資料；
Using index說明：表示已經使用了覆蓋索引。SQL所需要返回的所有列資料均在一棵索引樹上，而無需訪問實際的行記錄。（聚簇型索引，innodb的主鍵索引）

 

4、索引策略

　　索引策略是指建立使用索引所要遵循的規則，換句話說，違背了這些規則會導致索引失效或者查詢效率降低。

策略1：儘量考慮覆蓋索引
策略2：遵循最左字首匹配
策略3：範圍查詢欄位放最後
策略4：不對索引欄位進行邏輯操作
策略5：儘量全值匹配
策略6：Like查詢，左側儘量不要加%
策略7：注意null/not null 可能對索引有影響
策略8：儘量減少使用不等於
策略9：字元型別務必加上引號
策略10：OR關鍵字左右儘量都為索引列

測試資料表：

 show index from employees;

 

策略1：儘量考慮覆蓋索引

覆蓋索引：SQL只需要通過遍歷索引樹就可以返回所需要查詢的資料，而不必通過輔助索引查到主鍵值之後再去查詢資料（回表操作）。回表操作的詳細介紹可以參考本人《MySQL慢查詢優化》系列博文之索引。

EG：

EXPLAIN SELECT emp_no,birth_date,gender FROM employees WHERE  gender  ='M' ;

 Using index：表示已經使用了覆蓋索引。

 

 策略2：遵循最左字首匹配

聯合索引命中必須遵循“最左字首法則”。即SQL查詢Where條件欄位必須從索引的最左前列開始匹配，不能跳過索引中的列。聯合索引又稱複合索引，類似於書籍的目錄，多級的目錄結構中子目錄依賴於父級目錄存在，也是遵循“最左字首法則”。

聯合索引結構分析，示例：

聯合索引

INDEX idx_empno_birthdate_gender(emp_no,birth_date,gender)
等價建立的索引

實際上聯合索引idx_empno_birthdate_gender等價建立了三個索引：

  • index_1(emp_no)
  • index_2(emp_no,birth_date)
  • index_3(emp_no,birth_date,gender)
聯合索引命中的where條件欄位列表  
  • index_1命中：
    • emp_no 或 emp_no,gender
  • index_2命中：
    • emp_no,birth_date
  • index_3命中：
    • emp_no,birth_date,gender

以上where子句查詢條件聯合索引idx_empno_birthdate_gender都會命中，只是使用的程度不一樣（走的子索引不一樣），因此，聯合索引有“是否充分使用”衡量指標（key_len），當然使用最充分的條件是：所有欄位都命中，即使用了index_3。

 
EXPLAIN SELECT * FROM employees WHERE  birth_date  = '1963-06-01' AND gender ='F';

 注：表存在多個索引時，即使Where條件滿足最左字首規則，SQL執行時也未必一定會命中聯合索引，根據效能可能直接使用了主鍵索引。

 EG：

EXPLAIN SELECT * FROM employees WHERE emp_no  = 10010 AND  birth_date  = '1963-06-01'  AND gender ='F';

PRIMARY KEY (`emp_no`)

 

策略3：範圍查詢欄位放最後

　　聯合索引定義時，儘量將範圍查詢欄位放在最後（放在最後聯合索引使用最充分，放在中間聯合索引使用不充分）。使用聯合索引時範圍列（當前範圍列索引生效）後面的索引列無法生效，同時索引最多用於一個範圍列，如果查詢條件中有多個範圍列，也只能用到一個範圍列索引。

EG1：

EXPLAIN SELECT emp_no,birth_date,gender FROM employees WHERE  emp_no  > 10015  AND gender ='F';

只是使用到了主鍵索引PRIMARY(emp_no),聯合索引未生效idx_empno_birthdate_gender(emp_no,birth_date,gender)；

刪除idx_empno_birthdate_gender索引，新建聯合索引idx_gender_birthdate_empno(gender,birth_date,emp_no)；

 EG2：

EXPLAIN SELECT emp_no,birth_date,gender FROM employees WHERE  emp_no  > 10015 AND birth_date = 1953-09-02 AND gender ='F';

 

策略4：不對索引欄位進行邏輯操作

 在索引欄位上進行計算、函式、型別轉換（自動\手動）都會導致索引失效。

EG：

CREATE INDEX idx_first_name ON employees(first_name);

EXPLAIN SELECT * FROM employees WHERE LEFT(first_name,3) ='Geo';

 

 策略5：儘量全值匹配

 全值匹配也就是精確匹配不使用like查詢（模糊匹配），使用like會使查詢效率降低。

 

 策略6：Like查詢，左側儘量不要加%

 like 以%開頭，當前列索引無效(當為聯合索引時，當前列和後續列索引不生效，可能導致索引使用不充分)；當like字首沒有%，字尾有%時，索引有效。

 EG1：

EXPLAIN SELECT * FROM employees WHERE first_name like'Geo%';

EG2：

EXPLAIN SELECT * FROM employees WHERE first_name like'%Geo%';

 

 

 策略7：注意NULL/NOT NULL可能對索引有影響

在索引列上使用 IS NULL 或 IS NOT NULL條件，可能對索引有所影響。

  • 欄位定義預設為NULL時，NULL索引生效，NOT NULL索引不生效；
  • 欄位定義明確為NOT NULL ，不允許為空時，NULL/NOT NULL索引列，索引均失效；

 EG1：

EXPLAIN SELECT * FROM employees WHERE first_name IS  NULL;

 

 EG2：

EXPLAIN SELECT * FROM employees WHERE first_name IS NOT NULL;

 

 EG3：

EXPLAIN SELECT * FROM employees WHERE first_name IS NOT NULL;

 

 

 策略8：儘量減少使用不等於

不等於操作符是不會使用索引的。不等於操作符包括：not，<>，!=。

優化方法：數值型 key<>0 改為 key>0 or key<0。

 EG：

EXPLAIN SELECT * FROM employees WHERE first_name != 'Georgi';

 

 

 策略9：字元型別務必加上引號

若varchar型別欄位值不加單引號，可能會發生資料型別隱式轉化，自動轉換為int型，使索引無效。

EG：

EXPLAIN SELECT * FROM employees WHERE first_name = 1;

 

策略10：OR關鍵字前後儘量都為索引列

當OR左右查詢欄位只有一個是索引，會使該索引失效，只有當OR左右查詢欄位均為索引列時，這些索引才會生效。OR改UNION效率高。

EG1：

EXPLAIN SELECT * FROM employees WHERE first_name = 'Georgi' OR emp_no = 20001;

 EG2：

EXPLAIN SELECT * FROM employees WHERE first_name = 'Georgi' OR last_name = 'Facello';

 

後續：

  • 索引的建立需要參照具體的SQL實現。
  • 當全表掃描速度比索引速度快時，MySQL會使用全表掃描，此時索引失效。
  • 表中存在多個索引時，即使where條件滿足某個索引策略，MySQL查詢優化器也不一定會使用該索引，可能使用其他索引，取決於效能。另外，當某個索引沒有命中也不一定會走全表掃描，可能走其他索引。
  • 理論上索引對順序是敏感的，也就是說where子句的欄位列表需要講究順序，但是由於MySQL的查詢優化器會自動調整where子句的條件順序以匹配適合的索引，因此，允許我們不去刻意關注where子句的條件順序。

 

 

 

相關文章