索引优化实战七

水深无声 2022-08-28 13:46 265阅读 0赞

一 like 的前后模糊匹配

like 以通配符开头（’%abc…’），mysql 索引失效会变成全表扫描的操作。

二 实战

  1. # % 作为 like 的前缀，会失效
  2. mysql> explain select * from staffs where name like '%July%';
  3. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  4. | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  5. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  6. |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
  7. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  8. 1 row in set, 1 warning (0.00 sec)
  10. # % 作为 like 的前缀，会失效
  11. mysql> explain select * from staffs where name like '%July';
  12. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  13. | id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  14. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  15. |  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
  16. +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
  17. 1 row in set, 1 warning (0.00 sec)
  19. # 索引生效：% like 加右边
  20. mysql> explain select * from staffs where name like 'July';
  21. +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
  22. | id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
  23. +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
  24. |  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
  25. +----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
  26. 1 row in set, 1 warning (0.00 sec)

三 解决 like ‘%字符串%’时，索引不被使用的方法

1 表脚本

  1. CREATE TABLE `tbl_user`(
  2.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  3.   `name` VARCHAR(20) DEFAULT NULL,
  4.   `age`INT(11) DEFAULT NULL,
  5.   `email` VARCHAR(20) DEFAULT NULL,
  6. PRIMARY KEY(`id`)
  7. )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  9. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
  10. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
  11. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
  12. INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

2 操作

  1. # 建立索引前，全表扫描
  2. mysql> explain select name,age from tbl_user where name like '%aa%';
  3. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  4. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  5. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  6. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  7. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  8. 1 row in set, 1 warning (0.00 sec)
  10. # 建立索引前，全表扫描
  11. mysql> explain select id from tbl_user where name like '%aa%';
  12. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  13. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  14. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  15. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  16. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  17. 1 row in set, 1 warning (0.00 sec)
  19. # 建立索引前，全表扫描
  20. mysql> explain select name from tbl_user where name like '%aa%';
  21. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  22. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  23. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  24. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  25. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  26. 1 row in set, 1 warning (0.00 sec)
  28. # 建立索引前，全表扫描
  29. mysql> explain select age from tbl_user where name like '%aa%';
  30. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  31. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  32. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  33. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  34. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  35. 1 row in set, 1 warning (0.00 sec)
  37. # 建立索引前，全表扫描
  38. mysql> explain select name,age from tbl_user where name like '%aa%';
  39. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  40. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  41. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  42. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  43. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  44. 1 row in set, 1 warning (0.00 sec)
  46. # 建立索引
  47. mysql> create index idx_user_nameAge on tbl_user(Name,age);
  48. Query OK, 0 rows affected (0.05 sec)
  49. Records: 0  Duplicates: 0  Warnings: 0
  51. # 使用到覆盖索引，索引生效
  52. mysql> explain select name,age from tbl_user where name like '%aa%';
  53. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  54. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  55. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  56. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  57. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  58. 1 row in set, 1 warning (0.00 sec)
  60. # 使用覆盖索引，索引生效
  61. mysql> explain select id from tbl_user where name like '%aa%';
  62. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  63. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  64. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  65. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  66. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  67. 1 row in set, 1 warning (0.00 sec)
  69. # 使用覆盖索引，索引生效
  70. mysql> explain select name from tbl_user where name like '%aa%';
  71. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  72. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  73. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  74. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  75. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  76. 1 row in set, 1 warning (0.00 sec)
  78. # 使用覆盖索引，索引生效
  79. mysql> explain select age from tbl_user where name like '%aa%';
  80. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  81. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  82. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  83. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  84. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  85. 1 row in set, 1 warning (0.00 sec)
  87. # 使用覆盖索引，索引生效
  88. mysql> explain select id,name from tbl_user where name like '%aa%';
  89. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  90. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  91. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  92. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  93. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  94. 1 row in set, 1 warning (0.00 sec)
  96. # 使用覆盖索引，索引生效
  97. mysql> explain select id,name,age from tbl_user where name like '%aa%';
  98. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  99. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  100. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  101. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  102. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  103. 1 row in set, 1 warning (0.00 sec)
  105. # 使用覆盖索引，索引生效，主要是 email 惹的祸
  106. mysql> explain select name,age from tbl_user where name like '%aa%';
  107. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  108. | id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
  109. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  110. |  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    4 |    25.00 | Using where; Using index |
  111. +----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
  112. 1 row in set, 1 warning (0.00 sec)
  114. # 没能用到覆盖索引，索引失效
  115. mysql> explain select * from tbl_user where name like '%aa%';
  116. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  117. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  118. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  119. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  120. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  121. 1 row in set, 1 warning (0.00 sec)
  123. # 没能用到覆盖索引，索引失效，主要是 email 惹的祸
  124. mysql> explain select id,name,age,email from tbl_user where name like '%aa%';
  125. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  126. | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  127. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  128. |  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
  129. +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
  130. 1 row in set, 1 warning (0.00 sec)

3 结论

使用覆盖索引可以解决问题。

发表评论

表情：
评论列表 （有 0 条评论，265人围观）

还没有评论，来说两句吧...

相关阅读

    相关 索引优化实战二

     一  不要在索引列上做任何计算 不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），会导致索引失效而转向全表扫描。 二 实战 等号左边

    短命女短命女/ 0 赞/ 257 阅读