MySQL SQL最佳化之‘%’

標籤：sql最佳化

設計索引的主要目的就是協助我們快速擷取查詢結果，而以%開頭的like查詢則不能夠使用B-Tree索引。
考慮到innodb的表都是聚簇表（類似於oracle中的索引組織表），且二級索引分葉節點中記錄的結構為（索引欄位->主鍵欄位），我們可以通過改寫sql（mysql最佳化器比較笨，需要給它足夠的提示）採取一種輕量級的方式代替全表掃：
使用索引全掃描找到主鍵，再根據主鍵回表擷取資料的方法。
這種方式的速度優勢在單行記錄資料量較大、表中記錄較多的情況下體現的尤為明顯，因為此時索引全掃描帶來的IO開銷相對於全表掃會小得多。

紙上得來終覺淺，絕知此事要躬行：
建立測試表test，表上有自增主鍵primary(id)和二級索引idx_name1(name1)，表中有500萬條資料。

mysql> desc test;+--------+-------------+------+-----+---------+----------------+| Field  | Type        | Null | Key | Default | Extra          |+--------+-------------+------+-----+---------+----------------+| id     | int(11)     | NO   | PRI | NULL    | auto_increment || name1  | varchar(20) | YES  | MUL | NULL    |                || name2  | varchar(20) | YES  |     | NULL    |                || name3  | varchar(20) | YES  |     | NULL    |                || name4  | varchar(20) | YES  |     | NULL    |                || name5  | varchar(20) | YES  |     | NULL    |                || name6  | varchar(20) | YES  |     | NULL    |                || name7  | varchar(20) | YES  |     | NULL    |                || name8  | varchar(20) | YES  |     | NULL    |                || name9  | varchar(20) | YES  |     | NULL    |                || name10 | varchar(20) | YES  |     | NULL    |                |+--------+-------------+------+-----+---------+----------------+11 rows in set (0.01 sec)mysql> show index from test\G*************************** 1. row ***************************        Table: test   Non_unique: 0     Key_name: PRIMARY Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 4829778     Sub_part: NULL       Packed: NULL         Null:    Index_type: BTREE      Comment: Index_comment: *************************** 2. row ***************************        Table: test   Non_unique: 1     Key_name: idx_name1 Seq_in_index: 1  Column_name: name1    Collation: A  Cardinality: 2414889     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment: Index_comment: 2 rows in set (0.00 sec)mysql> select count(*) from test;+----------+| count(*) |+----------+|  5000000 |+----------+1 row in set (1.59 sec)

基於name1進行like查詢，耗時11.13s，從執行計畫看，sql在執行時走的是全表掃描(type: ALL)：

mysql>  select * from test where name1 like ‘%O4JljqZw%‘\G*************************** 1. row ***************************    id: 1167352 name1: BO4JljqZws name2: BrfLU7J69j name3: XFikCVEilI name4: lr0yz3qMsO name5: vUUDghq8dx name6: RvQvSHHg4p name7: ESiDbQuK8f name8: GugFnLtYe8 name9: OuPwY8BsiYname10: O0oNGPX9IW1 row in set (11.13 sec)mysql> explain select * from test where name1 like ‘%O4JljqZw%‘\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4829778        Extra: Using where1 row in set (0.00 sec)

將sql改寫為‘select a. from test a,(select id from test where name1 like ‘%O4JljqZw%‘) b where a.id=b.id;’
提示最佳化器在子查詢中使用二級索引idx_name1擷取id：

mysql> select a.* from test a,(select id from test where name1 like ‘%O4JljqZw%‘) b where a.id=b.id\G*************************** 1. row ***************************    id: 1167352 name1: BO4JljqZws name2: BrfLU7J69j name3: XFikCVEilI name4: lr0yz3qMsO name5: vUUDghq8dx name6: RvQvSHHg4p name7: ESiDbQuK8f name8: GugFnLtYe8 name9: OuPwY8BsiYname10: O0oNGPX9IW1 row in set (2.46 sec)mysql> explain select a.* from test a,(select id from test where name1 like ‘%O4JljqZw%‘) b where a.id=b.id\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4829778        Extra: NULL*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: a         type: eq_refpossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: b.id         rows: 1        Extra: NULL*************************** 3. row ***************************           id: 2  select_type: DERIVED        table: test         type: indexpossible_keys: NULL          key: idx_name1      key_len: 63          ref: NULL         rows: 4829778        Extra: Using where; Using index3 rows in set (0.00 sec)

改寫後的sql執行時間縮短至2.46s，效率提升了近4倍！
執行計畫分析如下:
step 1：mysql先對二級索引idx_name1進行覆蓋掃描取出合格id(Using where; Using index)
step 2：對子step 1衍生出來的結果集table: <derived2>進行全表掃，擷取id（本案例中只有一個id符合條件）
step 3：最後根據step 2中的id使用主鍵回表擷取資料(type: eq_ref,key: PRIMARY )

總結：
在表中每條記錄的資料量較大時，通過這種方法改寫後的sql效率會有明顯提升。
本實驗中每條記錄的資料量還很小，如果每條記錄的資料量進一步加大，改寫後sql的執行效率會有數量級的提升，大家可以自行驗證~

MySQL SQL最佳化之‘%’