模式比對like '%xxx%'最佳化一例

MySQL裡like 'xxx%'可以用到索引，但like '%xxx%'是不行的。

 

  
mysql> desc artist; 
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
| Field      | Type                                          | Null | Key | Default | Extra | 
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
| artist_id  | int(10) unsigned                              | NO   | PRI | NULL    |       | 
  
| type       | enum('Band','Person','Unknown','Combination') | NO   |     | NULL    |       | 
  
| name       | varchar(255)                                  | NO   | MUL | NULL    |       | 
  
| gender     | enum('Male','Female')                         | YES  |     | NULL    |       | 
  
| founded    | year(4)                                       | YES  |     | NULL    |       | 
  
| country_id | smallint(5) unsigned                          | YES  |     | NULL    |       | 
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
6 rows in set (0.58 sec) 
 

如這個例子：

 

  
mysql> explain select * from artist where name like '%Queen%';           
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       | 
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
|  1 | SIMPLE      | artist | ALL  | NULL          | NULL | NULL    | NULL | 589410 | Using where | 
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
1 row in set (0.02 sec) 
 

現在來通過覆蓋索引來進一步最佳化：

 

  
mysql> explain select artist_id from artist where name like '%Queen%'; 
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    | 
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
|  1 | SIMPLE      | artist | index | NULL          | name | 257     | NULL | 589410 | Using where; Using index | 
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
1 row in set (0.03 sec) 
 

這裡artist_id是主鍵叢集索引），葉子節點上儲存了資料InnoDB引擎），select的artist_id列從索引中就能夠取得，不必讀取資料行只要你的SELECT欄位正好就是索引，那麼就用到了覆蓋索引），通過覆蓋索引，可以減少IO，提高了效能。

覆蓋索引簡單的說就是：我要在書裡尋找一個內容，但我這個目錄寫的很詳細，我在目錄中就擷取到了，不需要再翻到該頁查看。

之前的SQL執行時間：

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1T33141I-0.jpg" />

最佳化後的SQL執行時間：

650) this.width=650;" border="0" alt="" src="http://www.bkjia.com/uploads/allimg/131229/1T3313635-1.jpg" />

 

本文出自 “賀春暘的技術專欄” 部落格，請務必保留此出處http://hcymysql.blog.51cto.com/5223301/1112687