mysql B 树时间复杂度_二、MySql和B+树

一、前置问题

什么是索引？

为什么数据库要建索引？不建索引可以吗？索引是不是越多越好？

Mysql是通过索引从硬盘里找到对应的行记录吗？

有序树，平衡树，红黑树，B树和B+树的关系？

mysql为什么使用B+树实现索引结构？HashMap为什么使用红黑树？

mysql存储引擎索引的分裂方式是传统的中间分裂吗？

聚集索引和非聚集索引的区别？聚集索引的叶子节点一定包含了完整数据吗？

聚集索引是严格按照物理顺利存储的吗？

主键，联合主键的概念？主键可以有多个吗？主键和索引的关系？

联合索引和联合主键的关系？

为什么主键建议使用整形自增？

如果删除了所有索引，还能进行查询和删除操作吗，速度怎么样？

覆盖索引是什么？它解决了什么问题？

联合索引为什么遵循最左前缀原则？查询条件中的两个联合索引字段可以颠倒吗？

如何分析SQL语句是否使用了索引？什么是执行计划？

二、数据库管理海量数据带来的挑战

数据库作为一个管理数据的一个软件，管理海量数据是必须满足的一个特性。海量的数据必然是持久化到主机磁盘上，数据库需要通过IO操作访问磁盘来实现数据的读写功能。此时面临的挑战是如何高性能的读取数据，其中的一个关键指标是减少IO次数。

为了减少IO和提高数据访问效率，需要依赖某种数据结构来对海量的数据建立一个地址表，这种数据结构被称为索引(index)。

目前主要有两个方案：1)Hash索引；2)B+树索引。

本文讨论B+树索引。

三、海量的数据迫使数据库使用B+树索引

数据库为了应对海量数据的索引需要解决几个痛点：

1)通过索引快速查找数据；

2)数据更新时易于索引维护；

3)海量数据时IO次数较少；

4)支持Range操作；

树的类型

解决痛点

缺陷

二叉查找树

1)数据量小时可快速查找

极端情况下可能成为一条链，导致查询的时间复杂度为O(n)

平衡二叉树

1)数据量小时可快速查找

2)完全平衡的特性使其查询的时间复杂度可以稳定到O(logn)

维护平衡的开销太大

红黑树

1)数据量小时可快速查找；

2)非完全平衡的特性使查询时间复杂度接近O(log2n)；

3)维护平衡的开销较小

海量数据时，二叉树的高度太高，会增加IO次数

B树(平衡多叉树，B指balance)

1)海量数据时可快速查找；

2)平衡特性使其查询的时间复杂度为O(logxn)；

3)维护平衡的开销较小；

4)高扇出使其树的高度不高，一般在2-4层。

非叶子节点包含完整数据，导致索引过大，内存每次IO加载的索引数量减少，从而引发多次IO

B+树

几乎解决了数据库的所有痛点，且支持range操作

从上图可知HashMap使用红黑树的原因主要有三点：

1)其存储的数据量较小；

2)需要符合快速查找；

3)减小维护平衡的开销；

4)不需要range操作；

延伸一点：

索引并不是越多越好，应该有个平衡，过多的索引必然会增大维护索引的开销，同时会导致磁盘IO的增加。

为了增加IO的效率，索引并不是定位到磁盘里的具体某一行，而是读取该行所在的页(页是mysql管理磁盘的最小单位)到内存，然后在内存里通过二分查找定位具体的记录(虽然记录间通过双向链表连接，但是PageDirectory里的Slot是按顺序排)。

四、聚集索引和非聚集索引

聚集或非聚集指的是索引和数据是否聚集

索引

特性

优点

缺点

聚集索引

1)和数据聚集在一起的索引，其叶子节点保存的是完整数据。

2)所以叶子节点的顺序是按照聚集索引来组织和排序的，但物理存储时不是严格按照物理顺序存储的(页之间，页内记录之间通过双向链表来连接)；

1)查询效率比非聚集索引高；

1)重建索引开销较大；

非聚集索引

1)索引的叶子节点不包含完整数据，代替的聚集索引的id；

2)数据的物理存储与非聚集索引不相关；

3)是对非聚集索引的补充，以应对其他查询需求；

1)非聚集索引占用空间较小；

2)重建索引开销较小；

1)不考虑覆盖索引的情况时，查询效率较低(因为非聚集索引查询后，需再去查询聚集索引，即查找两棵树)

五、主键和索引的关系

主键：指的是一个列或多列的组合，其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。其中多列组合的主键称为联合主键；

简单来说，主键是数据库的表来标识每行记录的唯一性和完整性的一个或多个列的组合；每张表最多只有一个主键。

1)主键和索引在概念是没有任何关系

由以上概念得知主键是数据库管理和组织表数据的一种逻辑上的概念；而索引是查找表数据的一种数据结构概念；两者在概念上没有关系；

2)InnoDB的聚集索引的三种来源

a. 主键存在

由于主键具有唯一性，所以InnoDb会在主键存在时优先使用主键创建索引，即主键索引，此时的主键索引也就是聚集索引。

b. 主键不存在，但存在非空的唯一索引

c. 生成隐藏主键(整形递增)

数据库如果没有指定主键和非空唯一索引，那么会生成一个隐藏列来作为索引列；所以即便没有声明主键和索引也可以进行查询和DML操作；

六、自增主键是一种InnoDb索引规范

DBA们千万次叮咛我们使用整型自增主键主要基于以下事实：

1)整型的比较比字符串要高效，提高查找索引树的速度；

2)InnoDb的索引页的分裂会判断插入是否是随机的，如果是随机的会通过中间分裂；如果是有序的则为了保证页的满载(否则数据页中的数据始终为半页的负载)，则会以插入记录作为分裂点；

3)自增是使新增的记录都放到末尾，且以他作为分裂点，使得每页处于满载态；否则会频繁出现分裂，必然引起磁盘碎片和增大维护索引的开销；

七、联合索引和聚集索引关系

联合索引是指该索引由多个字段组合而成，重点在多个字段组成索引；

聚集索引指的是索引和数据聚合在一起，重点在数据和索引聚合；

两个概念是不同维度的，相互正交；所以有些情况下他们是有重叠的。

例如以下情况时联合索引就是聚集索引：

1)联合主键存在时，此时生成联合索引，也是聚集索引；

2)主键不存在时，若有联合索引满足非空的条件，有可能被选择为聚集索引；

八、索引覆盖

查找的数据就是索引字段，在非叶子节点就包含了要查询的列。

例如有联合索引idx_name_age，那么SQL语句select name from persion where name就是一次索引覆盖的查询。

九、联合索引的最左前缀原则

该原则基于一个事实是：联合索引是根据构成索引的列的先后顺序来排序的。

例如：联合索引idx_name_age_salary，可类比为一个三位数字的排序：百位，十位，个位。

十、执行计划

通过增加explain前缀可以查看mysql的执行计划，其中包含以下关键信息：

1)本次SQL的位置，是子查询还是主查询；

2)复合查询的执行顺序；

3)每次查询的访问类型或访问范围，全表，索引等；

4)每个查询是是否使用了索引，使用的索引名称，及其判断条件；

5)是否使用了fileSort；

参考资料：