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進行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進行覆蓋掃描取出符合條件的id(Using where; Using index) step 2：對子step 1衍生出來的結(jié)果集table: <derived2>進行全表掃，獲取id（本案例中只有一個id符合條件） step 3：最后根據(jù)step 2中的id使用主鍵回表獲取數(shù)據(jù)(type: eq_ref,key: PRIMARY )
