一: 索引的目的
- 索引的出现其实就是为了提高数据查询的效率, 就像书的目录一样.
二: InnoDB 索引模型
- InnoDB 采用 B + 树 的数据结构进行存储.
- 例如, 我们建立一张表, 分析他的数据建立
- MySQL> create table T(
- id int primary key,
- k int not null,
- name varchar(16),
- index (k)
- ) engine=InnoDB;
插入对应的表数据, 表中 R1~R5 的 (ID,k) 值分别为 (100,1),(200,2),(300,3),(500,5) 和 (600,6)
- 则, 对应的两颗树建立如下
-
- 从图中不难看出, 根据叶子节点的内容, 索引类型分为主键索引和非主键索引.
- 主键索引的叶子节点存的是整行数据. 在 InnoDB 里, 主键索引也被称为聚簇索引(clustered index).
- 非主键索引的叶子节点内容是主键的值. 在 InnoDB 里, 非主键索引也被称为二级索引(secondary index).
- 也就是说, 基于非主键索引的查询需要多扫描一棵索引树. 因此, 我们在应用中应该尽量使用主键查询.
三: 基于存储结构谈为什么要建立自增主键?
- B+ 树为了维护索引有序性, 在插入新值的时候需要做必要的维护.
- 以上面这个图为例, 如果插入新的行 ID 值为 700, 则只需要在 R5 的记录后面插入一个新记录.
- 如果新插入的 ID 值为 400, 就相对麻烦了, 需要逻辑上挪动后面的数据, 空出位置.
- 而更糟的情况是, 如果 R5 所在的数据页已经满了, 根据 B+ 树的算法, 这时候需要申请一个新的数据页, 然后挪动部分数据过去. 这个过程称为页分裂.
- 在这种情况下, 性能自然会受影响.
- 在这种情况下, 性能自然会受影响.
- 除了性能外, 页分裂操作还影响数据页的利用率. 原本放在一个页的数据, 现在分到两个页中, 整体空间利用率降低大约 50%.
- 自增主键
- 也就是说, 自增主键的插入数据模式, 正符合了我们前面提到的递增插入的场景.
- 每次插入一条新记录, 都是追加操作, 都不涉及到挪动其他记录, 也不会触发叶子节点的分裂.
- 显然, 主键长度越小, 普通索引的叶子节点就越小, 普通索引占用的空间也就越小.
- 所以, 从性能和存储空间方面考量, 自增主键往往是更合理的选择.
- 适合业务做主键的使用场景
- 只有一个索引;
- 该索引必须是唯一索引.
- 典型的 KV 场景.(由于没有其他索引, 所以也就不用考虑其他索引的叶子节点大小的问题.)
四: 联合索引技巧
- 覆盖索引
- 如果查询条件使用的是普通索引(或是联合索引的最左原则字段), 查询结果是联合索引的字段或是主键, 不用回表操作, 直接返回结果, 减少 IO 磁盘读写读取正行数据
- 最左前缀(前缀索引)
- 联合索引的最左 N 个字段, 也可以是字符串索引的最左 M 个字符(前缀索引)
- 联合索引
- 根据创建联合索引的顺序, 以最左原则进行 where 检索.
- 比如 (age,name) 以 age=1 或 age= 1 and name='张三'可以使用索引, 单以 name='张三' 不会使用索引.
- 考虑到存储空间的问题, 还请根据业务需求, 将查找频繁的数据进行靠左创建索引.
- 索引下推 (MySQL 底层优化)
- like 'hello%'and age>10 检索.
- MySQL5.6 版本之前, 会对匹配的数据进行回表查询.
- 5.6 版本后, 会先过滤掉 age<10 的数据, 再进行回表查询, 减少回表率, 提升检索速度
四: 索引重建
- 为什么要重索引?
- 文章里面有提到, 索引可能因为删除, 或者页分裂等原因, 导致数据页有空洞.
- 重建索引的过程会创建一个新的索引, 把数据按顺序插入, 这样页面的利用率最高, 也就是索引更紧凑, 更省空间.
- 如何重建索引
- 注意: 不论是删除主键还是创建主键, 都会将整个表重建.
重建索引请使用
- alter table T engine=InnoDB
来源: http://www.bubuko.com/infodetail-3076463.html