mysql sql优化器_MySQL 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: 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;’

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

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执行时间缩短至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的执行效率会有数量级的提升，大家可以自行验证~