什么是索引
索引是帮助MySQL高效获取数据的排好序的数据结构
索引的存储
InnoDB, 表结构的定义存储在[表名.frm]中,索引和数据存储在[表名…ibd]文件中
索引的优点
- 大大减少了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机IO变成顺序IO
索引的分类
- 数据结构角度
- B+Tree
- Hash
- 物理存储角度
- 聚簇索引(主键索引)
- 非聚簇索引(二级索引)
- 逻辑角度
- 主键索引
- 唯一索引
- 单列索引
- 联合索引
- 全文索引
索引结构
数据结构角度
B+Tree
问:为什么MySQL用B+Tree树作为索引的数据结构?
答: 因为数据库需要支持范围查找,同时要尽量控制每次查询过程中磁盘I/O的次数,即需要控制树高。
二叉树、红黑树
每层存储的元素较少,同样数据量的情况下,相比与B树和B+树,树高太高,导致每次查询过程中的I/O次数不可控可,大部分情况下I/O次数过多。
B树:
- 非叶子节点也存储了数据,MySQL以页为单位存储和读取数据,每页大小16KB,由于非叶子节点也存储了数据,导致在非叶子节点每页可以存储的数据行会少很多(通常索引相比于整行数据的大小小很多),这样整体树的高度相对就较高,最终如果查询的数据是落在了叶子节点,那么会导致I/O次数过多,不稳定。
- B树叶子节点没有用指针关联,若范围查找,找到第一个数据所在页后,若还需要查找其他数据,需要重新从根节点开始进行查找,效率不高。
B+树
- 非叶子节点只存储索引(冗余索引)
- 叶子节点存储索引和数据
- 叶子节点使用双向指针相互连接形成一个双向链表方便范围查找
总结
由于B+树非叶子节点只存储了索引,这样大大提高了每个非叶子节点可以存储的数据行数量,保证了树的高度可控。同时叶子节点使用双向指针连接形成链表,在执行范围查询,排序等操作时避免了重复从根节点开始查找数据。所以InnoDB最终选择了B+树作为索引的数据结构。
扩展阅读
一个用自增整数(Int)作为主键的树高为3的B+树可以存储多少行数据。
Int需要4字节存储空间, InnoDB索引对应的指针大小为6字节,假设每行数据大小为1KB(在大多数场景中,MySQL数据行不会超过1KB大小),参考上面的B+树图
第一层:根节点 - (16 * 1024) / (4 + 6) = 1638.4
第二层:非叶子节点 - (16 * 1024) / (4 + 6) = 1638.4
第三层:叶子节点 - (16 * 1024) / 1024 = 16
总共可以存储的数量是: 1638 * 1638 * 16 = 42928704
一共可以存储4000多万数据,所以大家明白InnoDB最终为什么选择使用B+树作为索引的数据结构了吧。
Hash
特点
- 只能满足 “=”、“IN” 查询,大多数时候执行此类查询时比B+树高效
- 无序,不支持范围查询
- 存在hash冲突问题,底层使用数组+链表(红黑树)来解决
Hash索引的限制
- 哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引中的值来避免读取行
- 哈希索引数据并不是按照索引值顺序存储的,所以无法进行排序
- 哈希索引不支持部分列匹配查找,哈希索引是使用索引列的全部内容来计算哈希值
- 哈希索引支持等值比较查询,也不支持任何范围查询
- 访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行
- 哈希冲突比较多的话,维护的代价也会很高
物理存储角度
聚簇索引(主键索引)
-
每张InnoDB引擎表只有一个聚簇索引,即主键索引
-
聚簇索引的叶子节点存储了整行的完整数据
-
建议主动给每张表建一个主键,并且最好使用自增的整数,原因如下
- 减少MySQL的负担,不要让MySQL来计算应该用哪列做主键或自己维护一个隐藏的主键列。
- 考虑查询时的比较效率、大小、存储空间、页分裂树旋转等因素建议使用自增整数
- 由于普通索引的叶子节点存储的是主键值,自增整型的占用的存储空间较小,所以普通索引占用的空间也就越小
思考:什么时候不适用用自增整型做主键?
业务场景:
1. 只有一个索引 2. 该索引必须是唯一索引 即KV场景,读远大于写
分析
1. 由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小的问题 2. 尽量使用主键查询,避免回表 3. 所以此时最好的选择是直接将该索引设为主键
限制:
- 聚簇数据最大限度地提高了IO密集型应用的性能,如果数据全部在内存,那么聚簇索引就没有什么优势
- 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式
- 更新聚簇索引列的代价很高,因为会强制将每个被更新的行移动到新的位置
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题
- 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候
非聚簇索引(二级索引)
- 相比于聚簇索引,非聚簇索引每张表可以建多个
- 非聚簇索引的叶子节点存储的是索引值和主键值
联合索引
思考:如何选择联合索引的顺序
- 最左前缀原则
- 区分度
- 范围查询
- 排序条件
- 尽量通过联合索引完成覆盖索引查询操作
扩展阅读
回表
先搜索二级索引树,得到主键的值,再到主键索引树搜索一次。这个过程称为回表。
页(page)的内部定位行数据
内部有个有序数组,二分法查找
系列文章
上一篇:【MySQL优化(四)】InnoDB数据类型解析及建表规约
下一篇:【MySQL优化(六)】MySQL Explain详解