MySQL -- 10 -- MySQL联合索引最左匹配原则

原文链接：MySQL – 10 – MySQL联合索引最左匹配原则

---

相关文章：

• MySQL – 01 – Linux上源码包安装MySQL（5.7）

• MySQL – 02 – Linux上源码包安装MySQL（8.0）

• MySQL – 03 – 配置MySQL开机自启

• MySQL – 04 – 解决Access denied for user root@localhost

• MySQL – 05 – 如何设计一个关系型数据库

• MySQL – 06 – MySQL索引优化

• MySQL – 07 – MySQL聚集索引和非聚集索引的区别

• MySQL – 08 – MySQL调优之慢查询日志（8.0）

• MySQL – 09 – MySQL调优之explain工具（8.0）

• MySQL – 10 – MySQL联合索引最左匹配原则

• MySQL – 11 – MySQL存储引擎MyISAM和InnoDB之间的区别（8.0）

• MySQL – 12 – MySQL存储引擎MyISAM和InnoDB在锁方面的区别（8.0）

• MySQL – 13 – MySQL事务的四个特性以及四个隔离级别

• MySQL – 14 – MySQL（InnoDB）当前读和快照读

---

一、联合索引

• 联合索引又称复合索引，是指由多个字段组成的索引

---

二、最左匹配原则

• 最左匹配原则，MySQL 会一直向右匹配，如果遇到范围查询 (>、<、between、like) 就会停止匹配，如：a = 1 and b = 2 and c > 3 and d = 4，如果建立 (a, b, c, d) 顺序的联合索引，那么 d 就使用不了联合索引了；如果建立 (a, b, d, c) 顺序的联合索引，则都可以用的到，a、b、d 的顺序可以任意调整

• = 和 in 可以乱序，比如：a = 1 and b = 2 and c = 3，如果建立 (a, b, c) 顺序的联合索引，则可以任意顺序，MySQL 的查询优化器会帮我们将其优化成索引可识别的形式

---

二、最左匹配原则成因

• MySQL 创建联合索引的规则，是首先对联合索引最左边 (第一个) 的索引字段的数据进行排序，然后在第一个字段数据排序的基础上，再对第二个索引字段的数据进行排序，以此类推，相当于实现了类似 order by field1 filed2 ... filedN 这样的排序规则

• 因此在没有使用第一个字段的情况下，直接使用第二个或后面的字段进行条件判断是无法使用索引的，这就是 MySQL 为什么强调联合索引最左匹配原则的原因

---

三、举例说明

• 创建学生表

  DROP TABLE IF EXISTS tbl_student_left;
  CREATE TABLE tbl_student_left (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid VARCHAR(40) NOT NULL COMMENT '学生唯一标识',
    name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    age INT(3) UNSIGNED NOT NULL COMMENT '学生年龄',
    sex TINYINT(1) UNSIGNED NOT NULL COMMENT '学生性别: 1=男，0=女',
    PRIMARY KEY (id),
    KEY idx_uuid_name_age (uuid, name, age)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生信息表';
  

  • 如上所示，我们建立了 (uuid, name, age) 顺序的联合索引 idx_uuid_name_age_sex，按照最左匹配原则，实际相当于是建立了 (uuid)、(uuid, name)、(uuid, age)、(uuid, name, age) 四个索引

  • 其他类似于 (name)、(age)、(name, age) 等组合则使用不了联合索引

  • 这里我们插入 1000 条数据来作为分析说明

• 使用联合索引示例

  • 示例一 (uuid)

    mysql> explain select * from tbl_student_left where uuid = '426fcfd1-c8de-11ea-84aa-00163e0d9873';
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
    | id | select_type | table            | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ref  | idx_uuid_name_age | idx_uuid_name_age | 122     | const |    1 |   100.00 | NULL  |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

  • 示例二 (uuid, name)

    mysql> explain select * from tbl_student_left where uuid = '426fcfd1-c8de-11ea-84aa-00163e0d9873' and name = 'NrDHrYmKurDVyRvMc';
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
    | id | select_type | table            | partitions | type | possible_keys     | key               | key_len | ref         | rows | filtered | Extra |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ref  | idx_uuid_name_age | idx_uuid_name_age | 184     | const,const |    1 |   100.00 | NULL  |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

  • 示例三 (uuid, age)

    mysql> explain select * from tbl_student_left where uuid = '426fcfd1-c8de-11ea-84aa-00163e0d9873' and age = 22;
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
    | id | select_type | table            | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ref  | idx_uuid_name_age | idx_uuid_name_age | 122     | const |    1 |    10.00 | Using index condition |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
    1 row in set, 1 warning (0.00 sec)
    

  • 示例四 (uuid, name, age)

    mysql> explain select * from tbl_student_left where uuid = '426fcfd1-c8de-11ea-84aa-00163e0d9873' and name = 'NrDHrYmKurDVyRvMc' and age = 22;
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
    | id | select_type | table            | partitions | type | possible_keys     | key               | key_len | ref               | rows | filtered | Extra |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ref  | idx_uuid_name_age | idx_uuid_name_age | 188     | const,const,const |    1 |   100.00 | NULL  |
    +----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)
    

• 无法使用联合索引示例

  • 示例一 (name)

    mysql> explain select * from tbl_student_left where name = 'NrDHrYmKurDVyRvMc';
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

  • 示例二 (age)

    mysql> explain select * from tbl_student_left where age = 22;
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

  • 示例三 (name, age)

    mysql> explain select * from tbl_student_left where name = 'NrDHrYmKurDVyRvMc' and age = 22;
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | tbl_student_left | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |     1.00 | Using where |
    +----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 1 warning (0.01 sec)