MySQL SQL优化之‘%’

桀昊j

　　设计索引的主要目的就是帮助我们快速获取查询结果，而以%开头的like查询则不能够使用B-Tree索引。
　　考虑到innodb的表都是聚簇表（类似于oracle中的索引组织表），且二级索引叶节点中记录的结构为（索引字段->主键字段），我们可以通过改写sql（mysql优化器比较笨，需要给它足够的提示）采取一种轻量级的方式代替全表扫：
　　使用索引全扫描找到主键，再根据主键回表获取数据的方法。
　　这种方式的速度优势在单行记录长度较大、表中记录较多的情况下体现的尤为明显，因为此时索引全扫描带来的IO开销相对于全表扫会小得多。
　　纸上得来终觉浅，绝知此事要躬行：
　　创建测试表test，表上有自增主键primary(id)和二级索引idx_name1(name1)，表中有500万条数据。
　　

mysql> desc test;　　
+--------+-------------+------+-----+---------+----------------+
　　
| Field  | Type        | Null | Key | Default | Extra          |
　　
+--------+-------------+------+-----+---------+----------------+

　　
|>　　
| 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:>　　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:>　　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: 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>　　提示优化器在子查询中使用二级索引idx_name1获取id：
　　

mysql> select a.* from test a,(select>
*************************** 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>　　
*************************** 1. row ***************************
　　id: 1
　　select_type: PRIMARY
　　table:
　　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:>　　key_len: 63
　　ref: NULL
　　rows: 4829778
　　Extra: Using where; Using index
　　
3 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: 进行全表扫，获取id（本案例中只有一个id符合条件）
　　step 3：最后根据step 2中的id使用主键回表获取数据(type: eq_ref,key: PRIMARY )
　　总结：
　　在表中每条记录的长度较大时，通过这种方法改写后的sql效率会有明显提升。
　　本实验中每条记录的长度还很小（只有100多字节），如果每条记录的长度进一步加大，改写后sql的执行效率会有数量级的提升，大家可以自行验证~