MySQL SQL优化之‘%’

发布时间:2020-08-02 03:13:00 作者:橡皮高
来源:网络 阅读:4434

设计索引的主要目的就是帮助我们快速获取查询结果,而以%开头的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: <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执行时间缩短至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效率会有明显提升。
本实验中每条记录的长度还很小(只有100多字节),如果每条记录的长度进一步加大,改写后sql的执行效率会有数量级的提升,大家可以自行验证~

推荐阅读:
  1. mysql8.0怎么进行降序索引
  2. Mysql高级知识讲义

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sql 优化 mysql sq

上一篇:Xcode4.6 sdk6.1 在编译cocos2d-x 2.1.1程序 发布后闪退的问题。

下一篇:Cnyunwei-Cacti+Nagios最新版本 V11发布

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》