数据库为什么要设计索引
用于提升数据库的查找速度
索引结构默认使用B+Tree,而不是Hash,二叉树,红黑树
- B+tree是一种多路平衡查询树,节点是天然有序的,非叶子节点包含多个元素,不保存数据,只用来索引,叶子节点包含完整数据和带有指向下一个节点的指针,形成一个有序链表,有助于范围和顺序查找。因为非叶子节点不保存数据,所以同样大小的磁盘页可以容纳更多的元素,同样能数据量的情况下,B+tree相比B-tree高度更低,因此查询时IO会更少。
- B-tree不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;
- Hash索引底层是基于哈希表,就是以key-value存储数据的结构,多个数据在存储关系上是没有任何顺序关系的。只适合等值查询,不适合范围查询,而且也无法利用索引完成排序,不支持联合索引的最左匹配原则,如果有大量重复键值的情况下,哈希索引效率会很低,因为存在哈希碰撞。
- 二叉树:树的高度不均匀,不能自平衡,查找效率跟数据有关(树的高度),并且IO代价高。
- 红黑树:树的高度随着数据量增加而增加,IO代价高。
hash比tree更快,索引结构为什么要设计成树型
-
加速查找速度的数据结构,常见的有两类:
(1)哈希,例如HashMap,查询/插入/修改/删除的平均时间复杂度都是O(1);
(2)树,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(lg(n));
可以看到,不管是读请求,还是写请求,哈希类型的索引,都要比树型的索引更快一些,那为什么,索引结构要设计成树型呢? -
索引设计成树形,和SQL的需求相关。
对于这样一个单行查询的SQL需求:select * from t where name=”shenjian”;确实是哈希索引更快,因为每次都只查询一条记录。
但是对于排序查询的SQL需求:分组:group by 、排序:order by、比较:<、>…
哈希型的索引,时间复杂度会退化为O(n),而树型的“有序”特性,依然能够保持O(log(n)) 的高效率。
InnoDB并不支持哈希索引。
数据库索引为什么使用B+树
为了保持知识体系的完整性,简单介绍下几种树。
二叉搜索树
- 当数据量大的时候,树的高度会比较高,数据量大的时候,查询会比较慢;
- 每个节点只存储一个记录,可能导致一次查询有很多次磁盘IO;
B树
- 不再是二叉搜索,而是m叉搜索;
- 叶子节点,非叶子节点,都存储数据;
- 中序遍历,可以获得所有节点;
B树被作为实现索引的数据结构被创造出来,是因为它能够完美的利用“局部性原理”。
什么是局部性原理?
(1)内存读写块,磁盘读写慢,而且慢很多;
(2)磁盘预读:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,如果未来要读取的数据就在这一页中,可以避免未来的磁盘IO,提高效率;
通常,一页数据是4K
(3)局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO;
B树为何适合做索引
(1)由于是m分叉的,高度能够大大降低;
(2)每个节点可以存储j个记录,如果将节点大小设置为页大小,例如4K,能够充分的利用预读的特性,极大减少磁盘IO;
B+树
仍是m叉搜索树,在B树的基础上,做了一些改进:
(1)非叶子节点不再存储数据,数据只存储在同一层的叶子节点上。根到每一个节点的路径长度一样,而B树不是这样。
(2)叶子之间,增加了链表,获取所有节点,不再需要中序遍历;
这些改进让B+树比B树有更优的特性:
(1)范围查找,定位min与max之后,中间叶子节点,就是结果集,不用中序回溯;范围查询在SQL中用得很多,这是B+树比B树最大的优势。
(2)叶子节点存储实际记录行,记录行相对比较紧密的存储,适合大数据量磁盘存储;非叶子节点存储记录的PK,用于查询加速,适合内存存储;
(3)非叶子节点,不存储实际记录,而只存储记录的KEY的话,那么在相同内存的情况下,B+树能够存储更多索引;
为什么m叉的B+树比二叉搜索树的高度大大大大降低
(1)局部性原理,将一个节点的大小设为一页,一页4K,假设一个KEY有8字节,一个节点可以存储500个KEY,即j=500
(2)m叉树,大概m/2<= j <=m,即可以差不多是1000叉树
(3)那么:
一层树:1个节点,1500个KEY,大小4K
二层树:1000个节点,1000500=50W个KEY,大小10004K=4M
三层树:10001000个节点,10001000500=5亿个KEY,大小100010004K=4G
可以看到,存储大量的数据(5亿),并不需要太高树的深度(高度3),索引也不是太占内存(4G)。
总结
数据库索引用于加速查询
虽然哈希索引是O(1),树索引是O(log(n)),但SQL有很多“有序”需求,故数据库使用树型索引
InnoDB不支持哈希索引
数据预读的思路是:磁盘读写并不是按需读取,而是按页预读,一次会读一页的数据,每次加载更多的数据,以便未来减少磁盘IO
局部性原理:软件设计要尽量遵循“数据读取集中”与“使用到一个数据,大概率会使用其附近的数据”,这样磁盘预读能充分提高磁盘IO
数据库的索引最常用B+树:
(1)很适合磁盘存储,能够充分利用局部性原理,磁盘预读;
(2)很低的树高度,能够存储大量数据;
(3)索引本身占用的内存很小;
(4)能够很好的支持单点查询,范围查询,有序性查询;
索引优化可以遵循以下几个原则:
- 联合索引最左前缀匹配原则
- 尽量把字段长度小的列放在联合索引的最左侧(因为字段越小,一页存储的数据量越大,IO性能也就越好)
- order by 有多个列排序的,应该建立联合索引
- 对于频繁的查询优先考虑使用覆盖索引
- 前导模糊查询不会使用索引,比如说Like '%aaa%'这种
- 负向条件不会使用索引,如!=,<>,not like,not in,not exists
- 索引应该建立在区分度比较高的字段上 一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*)
- 对于where子句中经常使用的列,最好设置索引
- 用exists代替in
- 尽量避免在where字句中对字段进行函数操作
使用explain工具分析SQL
type:找到数据的方式,根据效率从高到低排序有如下几种
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
如果type为index或all,表示本次扫描为全表扫描,说明这个语句是需要优化的。