索引
索引可以看成图书馆的目录,根据目录可以快速的找到你想要的哪一本书。如果没有索引只能一本本的找
常见的数据结构
了解索引需要先了解一下索引的结构,索引模型就是用于提高读写效率的数据结构
- 哈希表
- 有序数组
- 搜索树
哈希表
以key-value的形式存在,只要我们输入key,就可以找到对应的value
实现思路:
把值放在数组里,用一个哈希函数把 key 换算成一个确定的位置,然后把 value 放在数组的这个位置。
缺点:
因为不是有序的,所以查区间会很慢,哈希表只适用于等值查询
有序数组
如果我们要查询区间的话,有数数组比哈希表快很多
有序数组是按照数据的递增顺序保存的,用二分法可以快速的得到
衍生类型:
二叉树,N叉数
N叉数的存储形式:
父节点左子树所有结点的值小于父节点的值,右子树所有结点的值大于父节点的值
子节点越多查找对应的数据也就越快
缺点:
有序数组在中间插入一个记录挪动成本太高,只适用于静态数据的存储
InnoDB 的索引模型
B+树
B+树是InnoDB的索引模型,每一个索引在 InnoDB 里面对应一棵 B+ 树。
根据叶子节点的内容,索引可以分为主键索引和非主键索引
主键索引:存在的节点是整行数据
非主键索引:内容为主键的值
搜索方式
如果一个ID为主键的表中
当我们用主键查询时:
select * from T where ID = 250
只需要搜索ID这个B+树
如果我们用非主键索引查询时
select * from T where K = 250
需要先搜索K索引,得到ID索引再搜索一次
所以非主键索引需要多搜索一次数据表
索引维护
- 如果需要插入的值在整个索引的最后
在最后的数据页记录后面插入一个新记录
- 如果插入的值在两个数据页之间
需要逻辑上挪动后面的数据,空出位置
- 如果插入的值在已满的数据页上
这种是最麻烦的,需要新开一个数据页,然后挪动部分数据过去,性能自然会受影响
索引维护案例:
一般建表的时候都会设置主键ID为自增,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
索引问题:
如果我执行 select * from T where k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
这条 SQL 查询语句的执行流程:
- 先在索引 k 上找到 k=3 这条记录
- 再到 ID 索引数上找到 ID=300
- 然后再索引 k 上找到 k=5 这条记录
- 再到 ID 索引树上找到 ID=500
- 在索引 k 上取下一个值 k=6 ,不符合条件循环结束
这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。
那如果执行的是 select ID from T where k between 3 and 5,这个语句需要执行多少次查询呢
因为查询的值ID已经在索引k上了,索引不需要回表,只需要执行三次就可以了
最左前缀原则
B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录
SQL 语句的条件是"where name like ‘张 %’"时,是可以用上索引的,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。
在建立联合索引的时候,如何安排索引内的字段顺序
当我们有了一个联合索引(a,b),一般不需要在 a 上建立索引了,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。