相关文章：

  • 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)