时间戳

首页 > 解决方案 > LIKE 'xxx%' 是否等同于范围查询？LIKE 'xxx%' 如何使用索引？

mysql - LIKE 'xxx%' 是否等同于范围查询？LIKE 'xxx%' 如何使用索引？

问题描述

有人说，（1）LIKE '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)

如果title "LIKE 'Senior%'" 等价于"title >=Senior and title < Senios"，为什么key_len是59，也就是说主键的所有列都被使用了？

EXPLAIN 格式=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)

标签： mysql

解决方案

索引中列的排序对于特定WHERE子句非常重要。在您当前的主键中，顺序为emp_no-> title-> from_date。在各个列之间使用AND条件时，MySQL 将继续使用复合索引中等于一个常量值的列，直到遇到 Range 条件。

现在，请注意这LIKE 'Senior%基本上是一个范围条件，因为作为前缀title有多种可能性。Senior因为，MySQL在索引的第二列遇到了Range条件，它没有使用索引的第三列，即(from_date `from_date='1986-06-26')。

Extra您可以从结果中的列中确认这一点EXPLAIN。那里说Using Where。这基本上意味着在进行索引查找（使用前两列）之后，它进入数据树，并from_date根据您的WHERE条件（`from_date='1986-06-26'）获取要过滤掉的值。

如果要使用索引中的所有列，则需要将主键中列的顺序更改为以下，或定义新索引：

(emp_no, from_date, title)

根据您的 MySQL 版本，如果您运行EXPLAIN format=JSON. 特别是，查看used columns解释中的 JSON 结果中的部分。另一种检查方法是，如果您定义了新索引（如上所述），然后重新运行查询，您会注意到现在Using Where已经与Explain结果不同了。

编辑

感谢您添加EXPLAIN format=JSON结果。您可以清楚地看到used_key_parts其中的价值：

"used_key_parts": [
        "emp_no",
        "title"
      ]

它清楚地表明只使用了主键中的前两列。

推荐阅读