MySQLSQL優(yōu)化之‘%’

設(shè)計索引的主要目的就是幫助我們快速獲取查詢結(jié)果，而以%開頭的like查詢則不能夠使用B-Tree索引。
考慮到innodb的表都是聚簇表（類似于oracle中的索引組織表），且二級索引葉節(jié)點中記錄的結(jié)構(gòu)為（索引字段->主鍵字段），我們可以通過改寫sql（MySQL優(yōu)化器比較笨，需要給它足夠的提示）采取一種輕量級的方式代替全表掃：
使用索引全掃描找到主鍵，再根據(jù)主鍵回表獲取數(shù)據(jù)的方法。
這種方式的速度優(yōu)勢在單行記錄長度較大、表中記錄較多的情況下體現(xiàn)的尤為明顯，因為此時索引全掃描帶來的IO開銷相對于全表掃會小得多。

創(chuàng)新互聯(lián)建站-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比繁峙網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式繁峙網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋繁峙地區(qū)。費用合理售后完善，十多年實體公司更值得信賴。

紙上得來終覺淺，絕知此事要躬行：
創(chuàng)建測試表test，表上有自增主鍵primary(id)和二級索引idx_name1(name1)，表中有500萬條數(shù)據(jù)。

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進(jìn)行l(wèi)ike查詢，耗時11.13s，從執(zhí)行計劃看，sql在執(zhí)行時走的是全表掃描(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: OuPwY8BsiY
name10: O0oNGPX9IW
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4829778
        Extra: Using where
1 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;’
提示優(yōu)化器在子查詢中使用二級索引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: OuPwY8BsiY
name10: O0oNGPX9IW
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4829778
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.id
         rows: 1
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: test
         type: index
possible_keys: NULL
          key: idx_name1
      key_len: 63
          ref: NULL
         rows: 4829778
        Extra: Using where; Using index
3 rows in set (0.00 sec)

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

總結(jié)：
在表中每條記錄的長度較大時，通過這種方法改寫后的sql效率會有明顯提升。
本實驗中每條記錄的長度還很?。ㄖ挥?00多字節(jié)），如果每條記錄的長度進(jìn)一步加大，改寫后sql的執(zhí)行效率會有數(shù)量級的提升，大家可以自行驗證~

當(dāng)前名稱：MySQLSQL優(yōu)化之‘%’
URL鏈接：http://bm7419.com/article20/gihpco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計公司、微信公眾號、網(wǎng)站制作、電子商務(wù)、小程序開發(fā)、服務(wù)器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站建設(shè)網(wǎng)站維護公司