为什么使用索引
- 有序、加速查找过程
- 减少服务器扫描的数据量
- 随机IO变为顺序IO 减少IO次数
引入索引的负担
- 降低更新表速度,因为更新表时同时要更新索引 所以在频繁更新的情况下慎建索引。
- 索引也占空间、所以建立索引的应该考虑索引的大小,尽量能小则小
有哪些索引
- 主键索引
- 唯一性索引
- 非唯一性索引
- 组合索引(组合索引涉及到最左匹配原则及索引失效、后续说明,大部分失效可以以省市区模型进行理解)
有哪些索引结构
- 哈希表
- 有序二叉树
- 平衡有序二叉树
- 红黑树
- B+树
Mysql默认使用Innodb存储引擎,该引擎使用B+树作为索引的结构
B+树是B树的延申,树我们知道,在构建树的时候,是会进行一个排序并进行平衡处理的,所以,我们得到索引就是排好序的数据结构,对于有序的数据结构,必定是二分查找,这种数据结构与算法大幅加快了数据库的查找速度
平衡二叉有序树 索引的查找过程
假设我们某张表的每行记录只有两个字段(col1-id col2-age)
-
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址。为了加速查找,可以维护一个右边所示的二叉查找树,该二叉树节点存储col2的值,并进行排序,每个节点分别包含col的值作为键,值为行记录的物理地址
-
查找的时间复杂度由O(N)变成 O(logN)
为什么是B+ Tree
-
哈希表: 哈希表查询是O(1)的,查询速度无人匹敌,但哈希表的特点决定了需要将整个索引结构加载到内存中、太极端;在需要利用索引排序、分组、范围查找的情况下、哈希索引不能很好的支持;等值查询更好的支持
-
普通的有序二叉树或平衡树或红黑树:只有双叉,IO频繁,每次能从内存中取的页数太小,性能大多浪费在IO上
红黑树相比平衡二叉树:利用变色优化调了一点旋转的代价 、常量级别 -
B树
每个磁盘块(于内存页中)存储两个索引(k、v)三个指针,P1表示小于第一个数据项的,P2表示在第一个数据项和第二个数据项之间的,P3表示大于第二个数据项的
查找过程
以查找29为例
- 磁盘块1读入内存,此时一次IO
- 二分查找锁定磁盘块1的P2指针
- 把P2指向的磁盘块3读入内存,此时二次IO
- 二分查找锁定磁盘块3的P2指针
- 把磁盘块3的P2指向的磁盘块3读入内存,此时三次IO
- 找到29的行记录,结束查找,共3次IO
B+ Tree
来看看B+树是如何存储主键索引的
可以明显看见,每个磁盘块不止两个键值对和三个指针了,而是进行了扩展,这样极大降低了树的深度,大幅降低IO次数,典型的空间换时间
-
该B+ Tree存储主键的索引,表中有主键时自动创建该索引
-
非叶子节点存储主键(在非叶子节点时,主键会在下一层同样存在,直到搜索到叶子节点层),叶子节点存储主键和值(记录或记录的物理地址)
-
叶子节点两两指针互连,也就提高了顺序查询的性能更高(在最下面一层顺序查找),并且叶子节点层从头到尾都是有序的,在聚集索引模式下,索引和行记录不仅存储在一起,并且还是有序的
-
实际情况中,3~4层足以支撑千万到亿级别的记录了
实例:
聚簇索引
聚簇索引是innodb数据引擎支持的索引,而Myisam并不支持聚簇索引,关于innodb和Myisam的区别后面也会单独说明
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式
如下图,左侧的索引就是聚簇索引,key在磁盘的排列和索引排序保持一致,且记录直接与键值保存在一起
聚簇的好处:
- 查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,节省IO次数
聚簇的限制
- 受数据物理存储排序方式只能有一种的限制,每个表都只有一个聚簇索引,不可能有其他聚簇索引的存在
- 为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种
非聚簇索引
索引节点存储的是指针、键与指向记录的指针,而非直接是记录
索引创建时机
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段应该建立索引
- 组合索引性价比更高
- 排序的字段:若已排序的字段通过索引去访问,速度大增,因为索引天然有序
- 查询中统计或分组的字段,可以先对索引进行分组或统计、再获取记录,比直接对全表行记录操作来的快
以下情况不适合做索引
- 表记录太少
InnoDB的回表机制会对非主索引的查找走两次B+树,反而更慢
select * from table where name = `zz` ;
select id from table where name = `zz` ; `
- 经常更新的字段,因为不关要对原数据更新、还要对索引更新、代价更高