类似于'xxx%‘等同于范围查询吗？喜欢'xxx%‘使用索引吗？

Question

有人说，(1)像'xxx%‘一样等同于范围查询；(2) range列后面的列不能使用索引。但是，我发现这两种说法在下面的一个例子中是相互矛盾的。所以，我想知道当我们使用'xxx%‘这样的时候，查询索引树的确切过程是什么。

我使用了employees.titles表 of MySQL文档。

这是桌子的结构。

+-----------+-------------+------+-----+---------+-------+
| 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 >=Senior and title < Senios"，为什么key_len是59，这意味着使用主键的所有列？

解释format=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)

Answer 1

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

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

您可以通过Extra列在EXPLAIN结果中确认这一点。上面写着Using Where。它基本上意味着在完成索引查找(使用前两列)之后，它进入数据树，并根据您的from_date条件(‘`from_date=’1986-0626‘)获取筛选出的WHERE值。

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

(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"
      ]

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