1.什么是索引
索引是帮助MySQL高效获取数据的数据结构(有序)。
索引的优缺点:
优点 | 缺点 |
提高数据检索的效率,降低数据库的I0成本 | 索引列也占用空间 |
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 | 索引降低了更新表的速度,如对表进行INSERT、UPDATE、DELETE时效率降低。 |
2.索引类型
MySQL的索引是在存储引擎层实现的,不同的存储引警有不同的结构,主要包含以下几种:
索引结构 | 描述 |
*B+Tree索引 | 最常见的索引类型,大部分引擎都支持 B+树索引 |
*Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MVISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES。通常使用较少 |
索引 | InnoDB | MsyISAM | Memory |
B+Tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | mysql5.6后支持 | 支持 | 不支持 |
3.B+Tree索引
3.1二叉树
一个二叉树的例子如下:
每个节点有2个子节点,左边的所有节点<自身,右边的所有节点>自身
二叉树的弊端:当出现下图这种顺序出现的数据时,检索次数将大大增加
3.2B-tree(多路平衡查找树)
B-tree的每个节点可以有多个子节点。
3.2.1基本元素
以一颗最大度数为5(5阶,即子节点个数不超过5)的b-tree为例:
图中,第一行有4个key:20、30、62、89
有5个指针(分别指向5个子节点,与度数相等),分别为<20、20-30、30-62、62-89、>89
3.2.2添加数据过程
(1)假设现有一个度数为5的B-tree,已添加了4个key如下:
如果再添加一个key1200,那么指针数量已经超过了5,这个节点就会从中间值0345向上分裂,变为下图的形式:
(2)同理,如果对下图中右下角的子节点继续添加key1000
那么结果会变为下图的形式:
(3)如果对下图中右下角的子节点继续添加key2456
那么结果会变为下图的形式:
3.3B+tree
以下图B+tree为例:
可发现B+tree相比于B-tree,有2个不同点:
(1)所有数据都在叶子节点上,根节点只做索引用
(2)叶子结点形成了单向链表
若向下图所示的5度B+tree右下角继续添加key2345
那么结果会变为下图的形式:
3.4mysql的B+tree索引
MySQL索引数据结构对在原B+Tree的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了双向循环链表,提高区间访问的性能。
上图中的页就是InnoDB存储引擎逻辑结构中的page页
*4.Hash索引
4.1什么是hash索引
Hash索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞)
可以通过链表来解决。
4.2hash索引特点
*Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<,…)
*查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引
4.3存储引擎支持
在MySQL中,支持hash索引的是Memory引擎。而innoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
5.为什么InnoDB存储引擎选择使用B+tree索引结构?
*相对于二叉树,层级更少,搜索效率高;
*B-tree无论是叶子节点还是非叶子节点,都会保存数据,这样导致以一页中存储的键值减少(每个页固定是16k大小),指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低,
*相对Hash索引,B+tree支持范围匹配及排序操作
6.mysql索引分类
6.1按索引对象分类
分类 | 含义 | 特点 | 关键字 |
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引(使用较少) | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
6.2按存储类型分类
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
聚集索引 | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
二级索引 | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
*如果存在主键,主键索引就是聚集索引。
*如果不存在主键,将使用第一个唯一索引作为聚集索引。
*如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
6.3mysql索引的工作过程
以下图为例,红色箭头指向的是主键索引,也是聚集索引;绿色箭头指向的是二级索引
可以看到,聚集索引的叶子结点带有表的行数据,而二级节点的叶子结点则不携带数据,只是带有对应的主键。
假如现在要查找name为Jack的表数据,则二级索引会根据Jack逐层找到Jack所在的叶子结点,得到它对应的主键25;再由25查找聚集索引,得到25对应的行数据