索引是在存储引擎层实现的, 且在 MySQL 不同存储引擎中的实现也不同, 本篇文章介绍的是 MySQL 的 InnoDB 的索引.
下文将以这张表为例开展.
- # 创建一个主键为 id 的表, 表中有字段 k, 并且在 k 上有索引.
- create table T(
- `id` int(11) AUTO_INCREMENT,
- `k` int(11) NOT NULL,
- `name` varchar(16),
- `age` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY (`k`),
- KEY `name_age` (`name`,`age`)
- ) ENGINE=InnoDB;
- # 插入多条数据
- insert into T values (100, 'Bob'),(200, 'Peter'),(300,'Mary');
一, InnoDB 索引模型
在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, 也就是数据放在主键索引上, 其他索引上保存的是主键 id, 这种存储方式的表称为索引组织表.
InnoDB 使用了 B + 树 索引模型, 所以数据都是存储在 B + 树 中的. 每一个索引在 InnoDB 里面对应一棵 B + 树.
二, 索引的类型
2.1 主键约束: 主键索引和二级索引
主键索引的叶子节点存的是整行数据. 在 InnoDB 里, 主键索引也被称为聚簇索引.
非主键索引的叶子节点内容是主键的值. 在 InnoDB 里, 非主键索引也被称为二级索引, 辅助索引.
2.1.1 主键索引和非主键索引的区别
主键查询方式: 只需要搜索 ID 这棵 B + 树;
普通索引查询方式: 先搜索普通索引的 B + 树, 得到主键索引 ID 的值, 再到 ID 索引树上搜索, 这个过程称为回表.
2.1.2 主键索引的选取规则
从空间的角度出发: 主键列长度尽可能短, 每个二级索引的叶子节点是主键, 主键过长会导致二级索引占用空间更大.
从性能的角度出发: 推荐使用自增索引, 非自增主键在插入和删除的操作中, 会导致页分裂和页合并.
2.1.3 非主键索引的优化: 覆盖索引
先看下面这个 sql:
select * from T where k = 100;
这个 sql 语句会在 k 索引树上找到 k=100 的记录, 取得 ID=15;
再到 ID 索引树查到 ID=15 对应的记录, 发生了回表, 如果将 sql 语句改为
select id from T where k = 100;
因为 ID 的值已经在 k 索引树上了, 因此可以直接提供查询结果, 不需要回表.
如果一个索引包含 (或覆盖) 所有需要查询的字段的值, 称为覆盖索引, 即只需扫描索引而无须回表.
2.1.4 业务字段做主键的条件
如果不使用自增 ID 做主键, 用业务字段直接做主键, 则需要满足: 只有一个索引, 且该索引为唯一索引.
由于没有其他索引, 所以不用考虑其他索引的叶子节点大小的问题, 把这个索引设置为主键, 避免每次查询需要搜索两棵树.
2.1.5 索引的重建
主键索引的重建
- # 正确做法
- alter table T engine=InnoDB
- # 错误做法
- alter table T drop primary key;
- alter table T add primary key(id);
直接删掉主键索引会使得所有的二级索引都失效, 并且会用 ROWID 来作主键索引.
非主键索引的重建
- alter table T drop index k;
- alter table T add index(k);
索引可能因为删除, 或者页分裂等原因, 导致数据页有空洞, 重建索引的过程会创建一个新的索引, 把数据按顺序插入, 这样页面的利用率最高, 达到省空间的目的.
2.2 索引字段数量: 联合索引和单列索引
在上面的建表语句中, 可以看到有两个索引, 一个是 k 索引, 一个是 name_age 索引, 不难看出, 前者是单列索引, 而后者就是联合索引了.
为什么会有联合索引呢? 当查询条件为 2 个及以上时, 比如当经常要用 name 和 age 去查询数据时:
select * from T where name = 'Job' and age = 28;
创建一个 (name,age) 的联合索引, 相当于创建了 name 和 name,age 这两个组合的索引, 可以加速检索.
2.2.1 最左前缀原则
顾名思义是最左优先, 以最左边的为起点任何连续的索引都能匹配上.
联合索引的示例图如下:
索引项是按照索引定义里的字段顺序来排序的, 因此在创建联合索引时, 要根据业务需求, where 子句中使用最频繁的一列放在最左边.
当已经有了 (a,b) 这个联合索引后, 一般就不需要单独在 a 上建立索引了. 因此, 第一原则是, 如果通过调整顺序, 可以少维护一个索引, 那么这个顺序往往就是需要优先考虑采用的.
当创建 (a,b,c) 联合索引时, 相当于创建了 (a) 单列索引,(a,b) 联合索引以及 (a,b,c) 联合索引.
想要索引生效的话, 只能使用 a 和 a,b 和 a,b,c 三种组合; a,c 组合也可以, 但实际上只用到了 a 的索引, 并没有用到 c.
2.2.2 索引下推
MySQL 5.6 引入索引下推优化(index condition pushdown), 可以在索引遍历过程中, 对索引中包含的字段先做判断, 直接过滤掉不满足条件的记录, 减少回表次数.
比如根据 (name,age) 联合索引查询所有满足名称以 "张" 开头的索引, 然后直接再筛选出年龄小于等于 10 的索引, 之后再回表查询全行数据.
注意: innodb 引擎的表, 索引下推只能用于二级索引.
2.3 唯一约束: 唯一索引和普通索引
普通索引允许被索引的数据列包含重复的值, 创建唯一索引的目的一般不是为了提高访问速度, 而只是为了避免数据重复.
2.3.1 change buffer 机制
当需要更新一个数据页时, 如果数据页在内存中就直接更新, 而如果这个数据页还没有在内存中的话, 在不影响数据一致性的前提下, InnoDB 会将这些更新操作缓存在 change buffer 中, 这样就不需要从磁盘中读入这个数据页了. 在下次查询需要访问这个数据页的时候, 将数据页读入内存, 然后执行 change buffer 中与这个页有关的操作. 通过这种方式就能保证这个数据逻辑的正确性.
2.3.2 唯一索引和普通索引的选择
不推荐使用唯一索引, 这是因为:
从查询的角度出发:
如果查询结果全在内存上: 唯一索引在数据页中查找满足查询条件的第一条记录即可返回; 普通索引需要再获取下一条记录, 由于索引项是有序的且内存操作, 多一次判断的时间损耗可忽略不计;
如果查询结果不在内存上: 先把数据页加载到内存中, 再按照查询结果全在内存的流程处理.
从更新的角度出发:
如果需要更新的记录全在内存上, 直接更新内存记录并返回;
如果需要更新的记录不在内存上以及部分在内存上: 唯一索引需要先将需要更新的记录从磁盘中加载到内存, 更新内存记录并写 redolog; 普通索引将更新操作写入 change buffer, 通知执行器更新完成; 在下次读相关记录的时候, 先把原记录读取到内存, 再将 change buffer 上的操作在内存记录上回放, 并写 redolog;
普通索引在更新时, 节省了更新时从磁盘读取记录的时间, 而唯一索引在更新时, 若记录不在内存, 需要从磁盘读取记录到内存.
结论: change buffer 只适用于普通索引, 而不适用于唯一索引.
后记
关于索引的知识点比较多, 但每看一遍专栏都会有新的收获. fighting!
来源: https://www.cnblogs.com/sunshineliulu/p/12723129.html