MySQL 索引笔记

构建测试表 author, 建表语句如下

drop table if exists author;
create table author (
  id int auto_increment primary key,
  name varchar(128) not null,
  age int,
  gender varchar(32),
  a int,
  b int,
  c int
);

准备测试数据

insert into author(name, age, gender, a, b, c) values ('aaa', 11, 'M', 1, 1, 1);
insert into author(name, age, gender, a, b, c) values ('aab', 12, 'M', 1, 1, 2);
insert into author(name, age, gender, a, b, c) values ('abc', 10, 'F', 1, 2, 3);
insert into author(name, age, gender, a, b, c) values ('bba', 22, 'M', 2, 2, 1);
insert into author(name, age, gender, a, b, c) values ('bcb', 42, 'F', 2, 3, 2);
insert into author(name, age, gender, a, b, c) values ('bcc', 12, 'M', 2, 3, 3);
insert into author(name, age, gender, a, b, c) values ('cba', 17, 'M', 3, 2, 1);
insert into author(name, age, gender, a, b, c) values ('bcb', 32, 'F', 3, 1, 2);
insert into author(name, age, gender, a, b, c) values ('bcc', 82, 'M', 3, 1, 3);

select * from author;

添加唯一性约束和联合索引

alter table author add unique key uk_author_nag (name, age, gender);
create index ix_author_abc on author(a, b, c);

查看准备好的表结构和索引键

show create table author\G

*************************** 1. row ***************************
       Table: author
Create Table: CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_author_nag` (`name`,`age`,`gender`),
  KEY `ix_author_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

查看表结构

desc author;

查看准备好的表索引信息

show index from author;

删除唯一键和索引

alter table author drop key uk_author_nag;
alter table author drop index ix_author_abc;

修改索引名称，5.7 后面支持 rename 操作；5.7 之前需要先删除后重建索引才能修改索引 名称

alter table author rename index aa to ix_author_abc;
alter table author rename index bbb to uk_author_nag;

常见索引包括: B+ 树和哈希索引两种。哈希索引由于不支持范围查找一般使用较少，后 面主要讨论 B+ 树类型索引

1. 聚簇索引 clustered index
   • 每张表的主键构造一棵 B+ 树, 同时叶子节点中存放的就是整张表的行记录数据
   • 聚集索引的叶子节点称为数据页
   • 索引组织表中数据的同时也是索引的一部分, 每张表只能拥有一个聚簇索引, 一般 是主键索引
   • 优点
     • 数据访问更快, 因为聚簇索引将索引和数据保存在同一个 B+ 树中, 因此从聚簇 索引中获取数据比非聚簇索引更快
     • 聚簇索引对于主键的排序查找和范围查找速度非常快
   • 缺点
     • 插入速度严重依赖于插入顺序, 按照主键的顺序插入是最快的方式, 否则将会出 现 页分裂, 严重影响性能。因此, 对于 InnoDB 表, 我们一般都会定义一个自 增的 ID 列为主键
     • 更新主键的代价很高, 因为将会导致被更新的行移动。因此, 对于 InnoDB 表, 我们一般定义主键为不可更新
     • 二级索引访问需要两次索引查找, 第一次找到主键值, 第二次根据主键值找到行 数据, 即 回表 操作
2. 非聚簇索引 non-clustered index
   • 辅助索引叶子节点存储的不再是行的物理位置, 而是主键值
   • 通过辅助索引首先找到的是主键值, 再通过主键值找到数据行的数据页, 再通过数 据页中的 Page Directory 找到数据行
   • 每张表可以拥有多个非聚簇索引
   • 联合索引为非聚簇索引, 节点中除了存储索引字段, 还额外存储主键
   • 联合索引查询时, 需要考虑以下的情形
     • 最左前缀 指 WHERE 查询条件利用联合索引的从左到右的匹配, 如果匹配成功, 该查询走联合索引
     • 回表 指 WHERE 查询条件找到主键后, 然后通过主键返回 B+ 树中查找数据
     • 索引覆盖 Covering Index 只需要在一棵索引树上就能获取 SQL 所需的所有列 数据, 无需回表,速度更快
     • 索引下推 Index Condition Pushdown 在 MySQL 5.6 引入了索引下推优化, 可 以在索引遍历过程中, 对索引中包含的字段先做判断, 过滤掉不符合条件的记录, 减少回表次数

全表扫描，不走索引

explain select * from author;

主键查找，走 const 主键索引

explain select * from author where id = 4;

主键查找，走 range 主键索引

explain select * from author where id > 4;

联合索引查询优化需要考虑以下条件

1. 最左前缀
   • 指 WHERE 查询条件利用联合索引的从左到右的匹配, 如果匹配成功, 该查询走联合 索引
2. 回表
   • 指 WHERE 查询条件找到主键后, 然后通过主键返回 B+ 树中查找数据
3. 索引覆盖
   • 只需要在一棵索引树上就能获取 SQL 所需的所有列数据, 无需回表, 速度更快
4. 索引下推
   • 在 MySQL 5.6 引入了索引下推优化
   • 可以在索引遍历过程中, 对索引中包含的字段先做判断, 过滤掉不符合条件的记录, 减少回表次数

联合键查找，走 ref 范围索引

explain select * from author where a = 4;

联合键查找，走 range 范围索引

explain select * from author where a > 4;

多个联合键字段查询，符号最左前缀原则走索引 ix_author_abc

explain select * from author where a = 4 and b = 1;

同上，a 和 b 的顺序调换也走索引 ix_author_abc

explain select * from author where b = 1 and a = 4;

查询 b 和 c 情况不符合最左前缀原则，不走索引 ix_author_abc

explain select * from author where b = 1 and c = 4;

构建 a+1 为键的 B+树效率较大，无法走联合索引 ix_author_abc

explain select * from author where a + 1 > 3;

查找联合索引字段包含值，走主键索引

explain select a from author where id > 4;

查找联合索引字段包含值，并且同时过滤主键，走联合索引效率更高

explain select a from author where id > 4 and a > 4;

排序索引 ix_author_abc 包含时，直接走索引

explain select a, b from author order by a, b, c;

排序所有字段时无法走索引

explain select * from author order by a, b, c;

排序索引 ix_author_abc 包含时，走索引并且需要文件排序 filesort

explain select a, b from author order by a, b desc, c;

1. 没有符合最左前缀原值
2. 字段发送隐式类型转换
3. 走索引比全表扫描代价大