有人说,(1(喜欢'xxx%'等同于范围查询;(2(范围列后的列不能使用索引。但是,我发现这两个陈述在下面将提供的示例中相互矛盾。所以,我想知道当我们使用 LIKE 'xxx%' 时查询索引树的确切过程是什么。
我使用了MySQL文档的employees.titles表。
下面是表结构。
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| title | varchar(50) | NO | PRI | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
这是索引结构。
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| titles | 0 | PRIMARY | 1 | emp_no | A | 300698 | NULL | NULL | | BTREE | | |
| titles | 0 | PRIMARY | 2 | title | A | 441654 | NULL | NULL | | BTREE | | |
| titles | 0 | PRIMARY | 3 | from_date | A | 441654 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
下面是查询语句。
mysql> EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title LIKE 'Senior%'
AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 59 | NULL | 1 | 10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
如果标题"LIKE 'Senior%'"等同于"title>=Senior and title <Senios",为什么key_len是59,这意味着使用主键的所有列?>
解释格式的结果 = JSON
mysql> EXPLAIN format=json SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%' AND from_date='1986-06-26'G;
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.29"
},
"table": {
"table_name": "titles",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"emp_no",
"title"
],
"key_length": "59",
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "10.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.01",
"prefix_cost": "1.29",
"data_read_per_join": "3"
},
"used_columns": [
"emp_no",
"title",
"from_date",
"to_date"
],
"attached_condition": "((`employees`.`titles`.`from_date` = '1986-06-26') and (`employees`.`titles`.`emp_no` = '10001') and (`employees`.`titles`.`title` like 'Senior%'))"
}
}
}
1 row in set, 1 warning (0.00 sec)
索引中列的排序对于特定的WHERE
子句非常重要。在当前主键中,顺序为emp_no
->title
->from_date
。在各个列之间使用AND
条件时,MySQL将继续使用复合索引中等同于常量值的列,直到遇到范围条件。
现在,请注意,LIKE 'Senior%
基本上是一个范围条件,因为title
中存在各种可能性,Senior
作为前缀。因为MySQL在索引的第二列遇到了范围条件,所以它不使用索引中的第三列,即from_date
('from_date='1986-06-26'(。
您可以从EXPLAIN
结果的Extra
列中确认这一点。它说Using Where
在那里。这基本上意味着在执行索引查找(使用前两列(后,它会进入数据树,并获取要根据您的WHERE
条件过滤掉的from_date
值('from_date='1986-06-26'(。
如果要使用索引中的所有列,则需要将主键中列的顺序更改为以下内容,或定义新索引:
(emp_no, from_date, title)
根据您的MySQL版本,如果您运行EXPLAIN format=JSON
,则可以获得更多详细信息。特别是,请查看解释中 JSON 结果中的used columns
部分。另一种检查方法是,如果您定义了新索引(如上所述(,然后重新运行查询,您会注意到Using Where
现在已从Explain
结果中消失。
编辑
感谢您添加EXPLAIN format=JSON
结果。您可以清楚地看到它的价值used_key_parts
:
"used_key_parts": [
"emp_no",
"title"
]
它清楚地指示仅使用主键中的前两列。