验证Mysql中联合索引的最左匹配原则详情

当我们在 Mysql 数据库中创建联合索引时，会发现联合索引的查询效率远高于单属性索引，尤其在多条件筛选的情况下。联合索引的查询方式遵循最左匹配原则，即第一列在查询条件中的值确定，才会再考虑第二列的值。

验证 Mysql 中联合索引的最左匹配原则，可以按照以下步骤：

1. 创建测试数据表

我们可以通过以下语句来创建一个测试数据表：

CREATE TABLE test_table (
  id INT UNSIGNED NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age TINYINT UNSIGNED NOT NULL,
  gender ENUM('M','F') NOT NULL,
  INDEX idx_demo (name, age, gender)
);

在这个测试数据表中，我们创建了一个名为 idx_demo 的联合索引，包含三个列 name、age、gender。

1. 插入测试数据

为了验证联合索引的最左匹配原则，我们需要插入一些测试数据。我们可以按照以下语句插入数据：

INSERT INTO test_table (id, name, age, gender) VALUES
  (1, 'Tom', 18, 'M'),
  (2, 'Jerry', 20, 'M'),
  (3, 'Lucy', 22, 'F'),
  (4, 'Lily', 18, 'F'),
  (5, 'Tom', 22, 'F'),
  (6, 'Jerry', 18, 'F');

1. 查询演示

接下来，我们可以通过以下语句对数据表进行查询，验证联合索引的最左匹配原则。

3.1. 验证最左匹配原则

EXPLAIN SELECT * FROM test_table WHERE name = 'Tom' AND age = 18;

运行以上语句后，我们可以看到查询结果如下：

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | idx_demo      | idx_demo | 152     | const | 2    |   100.00 | Using index condition; Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+

我们可以看到查询结果中 type 字段为 ref，表示使用了索引，key 字段为 idx_demo，表示使用了 idx_demo 联合索引，key_len 字段为 152，表示查询时使用的为前两列 name 和 age 的字符长度之和，ref 字段为 const，表示实际查询的元素为常量，rows 字段为 2，表示查询结果的数量。

3.2. 验证多列查询

EXPLAIN SELECT * FROM test_table WHERE age = 18 AND gender = 'F';

运行以上语句后，我们可以看到查询结果如下：

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_table | NULL       | ref  | idx_demo      | idx_demo | 153     | const | 1    |   100.00 | Using index condition |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------------+

我们可以看到查询结果中的 type 字段为 ref，表示使用了索引，key 字段为 idx_demo，表示使用了 idx_demo 联合索引，key_len 字段为 153，表示查询时使用的为前三列 name、age 和 gender 的字符长度之和，ref 字段为 const，表示实际查询的元素为常量，rows 字段为 1，表示查询结果的数量。

通过以上两个示例，我们可以验证 Mysql 中联合索引的最左匹配原则，同时了解联合索引在多条件筛选时的高效率。