最近深入学习了MySQL,总结一下,作为备忘吧。
本篇文章主要介绍以下内容:
- 索引是什么
- 索引的类型
- 索引的原理
- 索引的好处
- 索引的代价
- 索引的使用
索引是什么
索引是存储引擎用于快速找到记录的一种数据结构。
索引的类型
B+树
索引Hash
索引
索引的原理
这里主要讲B+树
索引的原理。
我们知道InnoDB
是以页
作为管理存储空间的基本单位, 一个页的大小一般是16KB
,页的结构如下图。
一个页存储的记录数是有限的,多数情况下我们表中存放的记录是非常多的,那就需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤:
- 定位到记录所在的页。
- 从所在的页内中查找相应的记录。
如果没有索引的话,那就要遍历页
去查找记录,这个速度你可想而知。
B+ 树
聚簇索引
- 使用记录主键值的大小进行记录和页的排序
B+树
的叶子节点存储的是完整的用户记录
我们把具有这两种特性的B+树
称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB
存储引擎会自动的为我们创建聚簇索引。
二级索引
聚簇索引
只能在搜索条件是主键值时才能发挥作用,因为B+树
中的数据都是按照主键进行排序的。那二级索引是怎么做的哪?二级索引就需要再建一个B+树
,他的叶子节点存储的数据是主键
, 先上图
所以一般我们通过二级索引获取记录需要经历以下步骤:
- 通过二级索引获取
主键
值 - 通过主键值回到
聚簇索引
的B+树
中获取记录(这个过程称为回表
)
有一般,那就有不一般。不一般的情况就是 select
的字段被二级索引覆盖,就不需要回表
查询了。比如:select c1,id from s1 where c1=12
(c1 创建了二级索引)
索引的好处
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 更方便的范围查找
索引的代价
-
空间上的代价
每建立一个索引都为要它建立一棵B+树,一棵B+树的每一个节点都是一个数据页,一个页默认会占用16KB的存储空间,一棵很大的B+树由许多数据页组成,这样就会占用很大的一片存储空间 -
时间上的代价
每次对表中的数据进行增、删、改操作时,都需要去修改各个B+树索引。这样SQL的执行时间也会增加。
索引的使用
- 全值匹配
- 匹配左边的列
- 匹配范围值
- 精确匹配某一列并范围匹配另外一列
- 用于排序
- 用于分组
注意事项
- 只为用于搜索、排序或分组的列创建索引
- 为列的基数大的列创建索引
- 索引列的类型尽量小
- 可以只对字符串值的前缀建立索引
- 只有索引列在比较表达式中单独出现才可以适用索引
- 尽量让主键拥有AUTO_INCREMENT属性。
- 定位并删除表中的重复和冗余索引
- 尽量适用覆盖索引进行查询,避免回表带来的性能损耗。
B 树、红黑树
- B+ 树更方便范围查找(一般树的层数不会超过4)
- B 树查找
复杂度
不稳定,每次查找都要从根节点开始,不适合范围查找 - 红黑树(平衡二叉查找树),树太高,检索慢
B 树示意图
B+ 树示意图