本文主要是介绍mysql like %string% 索引失效问题，cluster index, secondary index，covering index意思，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
只有在like 子句是'string%'时，建在该字段的单列索引才会被使用。但实际中LIKE '%string%'的查询需求又可能被用到，该如何做？
这时可以用覆盖索引。就是新建一个联合索引，包含了select语句中要查询的所有字段（select语句要查询的字段不能超出联合索引包含的字段哦，除了主键id），这个时候用explain看执行计划，type是index,不是all了。也就是避免了全表扫描，但是还是会进行全索引扫描，尽管如此，性能还是会好一些。
比如下边的例子
mysql> desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| employee_id | int(6) | NO | PRI | NULL | auto_increment |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | YES | | NULL | |
| email | varchar(25) | YES | | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| job_id | varchar(10) | NO | | NULL | |
| salary | double(10,2) | YES | | NULL | |
| commission_pct | double(4,2) | YES | | NULL | |
| manager_id | int(6) | YES | | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
| hiredate | datetime | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+mysql> SELECT * FROM employees limit 10;
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING1 | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
| 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | AD_VP | 17000.00 | NULL | 100 | 90 | 1992-04-03 00:00:00 |
| 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | IT_PROG | 9000.00 | NULL | 102 | 60 | 1992-04-03 00:00:00 |
| 104 | Bruce | Ernst | BERNST | 590.423.4568 | IT_PROG | 6000.00 | NULL | 103 | 60 | 1992-04-03 00:00:00 |
| 105 | David | Austin | DAUSTIN | 590.423.4569 | IT_PROG | 4800.00 | NULL | 103 | 60 | 1998-03-03 00:00:00 |
| 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | IT_PROG | 4800.00 | NULL | 103 | 60 | 1998-03-03 00:00:00 |
| 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | IT_PROG | 4200.00 | NULL | 103 | 60 | 1998-03-03 00:00:00 |
| 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | FI_MGR | 12000.00 | NULL | 101 | 100 | 1998-03-03 00:00:00 |
| 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | FI_ACCOUNT | 9000.00 | NULL | 108 | 100 | 1998-03-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
10 rows in set (0.00 sec)mysql> show index from employees;
+-----------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees | 0 | PRIMARY | 1 | employee_id | A | 108 | NULL | NULL | | BTREE | | |
| employees | 1 | index_department_id | 1 | department_id | A | 12 | NULL | NULL | YES | BTREE | | |
+-----------+------------+---------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> explain SELECT * FROM employees WHERE last_name LIKE '%st%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 108 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
因为在last_name上没有索引，所以匹配不到索引。下边创建一个联合索引，并且select的字段只包含联合索引包含的字段，顶多再多一个employee_id这个主键索引字段，explain可以看到索引终于有使用上，即使employee_id字段不在联合索引里边。这是因为mysql innodb包含两种索引，cluster index和secondary index。cluster index主要是针对唯一、非空的这种一般用于主键的索引，他底层用到的B+tree，叶子节点会包含执行磁盘具体表记录数据的指针。而普通索引(secondary index)，他的叶子节点只包含该条记录的主键id, 然后在普通索引上查到该条记录之后，如果需要差完整一条记录，需要再利用查到的主键id到主键id对应的cluster index上查一遍，这个叫回表。针对我们如下的例子，因为我们只查联合索引的字段，加上主键id三个字段，索引扫描只局限在这两个索引上就ok了，不需要对磁盘表数据做扫描，所以explain里边type是index。
注意：覆盖索引(covering index)是指select语句匹配到的索引是这样的 - select所有select字段都包含在了索引里边，顶多再加一个主键id，这时候，通过索引本身就可以查到所有数据，不需要再对表扫描了，这个时候，这个索引就叫覆盖索引，它可以是包含一个列的索引，也可以是联合索引。
mysql> create index idx_last_name on employees(last_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> explain SELECT * FROM employees WHERE last_name LIKE '%st%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 108 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)mysql> explain SELECT * FROM employees WHERE last_name LIKE 'st%';
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_last_name | idx_last_name | 53 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql> create index idx_last_name_first_name on employees(last_name,first_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> select employee_id, first_name, last_name from employees WHERE last_name LIKE '%st%';
+-------------+------------+------------+
| employee_id | first_name | last_name |
+-------------+------------+------------+
| 105 | David | Austin |
| 151 | David | Bernstein |
| 104 | Bruce | Ernst |
| 201 | Michael | Hartstein |
| 177 | Jack | Livingston |
| 138 | Stephen | Stiles |
+-------------+------------+------------+
6 rows in set (0.00 sec)mysql> explain select employee_id, first_name, last_name from employees WHERE last_name LIKE '%st%';
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | index | NULL | idx_last_name_first_name | 96 | NULL | 108 | 11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
这篇关于mysql like %string% 索引失效问题，cluster index, secondary index，covering index意思的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
