数据库索引-2

前言

写数据库，我第一时间就想到了MySQL、Oracle、索引、存储过程、查询优化等等。

不知道大家是不是跟我想得一样，我最想写的是索引，为啥呢？

以下这个面试场景，不知道大家熟悉不熟悉：

面试官：数据库有几千万的数据，查询又很慢我们怎么办？

面试者：加索引。

面试官：那索引有哪些数据类型？索引是怎么样的一种结构？哪些字段又适合索引呢？B+的优点？聚合索引和非聚合索引的区别？为什么说索引会降低插入、删除、修改等维护任务的速度？……..

正文

索引有哪些数据结构

Hash，BTree

哈希表，完全平衡二叉树，B树，B+树都可以查询优化，为何mysql喜欢B+树？

首先哈希表可能出现哈希冲突。

那么对于这样一个索引结构，现在来执行下面的sql语句：

select * from sanguo where name = '鸡蛋'

可以直接对"鸡蛋"按哈希算法算出来一个数组下标，然后可以直接从数据中取出数据并拿到所对应那一行数据的地址，进而查询那一行数据。那么如果现在执行下面的sql语句：

select * from sanguo where name > '鸡蛋'

则无能为力了，因为哈希表的特点是可以快速的精确查询，但是不支持范围查询。

那hash表在哪些场景比较合适？

等值查询的场景，就只有KV的情况，例如redis，memcached等

你说的是无序的Hash表，那有没有有序的数据结构？

有序数组，它在等值查询以及范围查询的时候都很好

那有缺点吗？

有序的适合静态数组，因为如果我们增删改数据的时候就会改变他的结构，比如新增，那在你新增的位置后面，所有的节点都会后移，成本很高

那他也不优秀呀，也没地方放。

可以用来做静态存储引擎，用来保存静态数据，例如2019年支付宝账单等，不会变动的历史数据

二叉树呢？

二叉树是有序的，所以支持范围查询，但是他的时间复杂度是O(log(n))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(n))，那就得保持这棵树是完全平衡的二叉树了。另外二叉树的数据多了之后，树会变的很高，查询的成本就会随着树高的增加而增加。

B树呢

同样的元素，B树的表示要比完全平衡二叉树要矮，原因在于B树中的一个节点可以存储多个元素。B树其实就已经是一个不错的数据结构了，用来做索引效果还是不错的。

那为什么使用B+树？

同样的元素，B+树的要比B树胖，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

B+树有什么优势

而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

提高了的原因也无非是会有指针指向下一个节点的叶子节点。并且B+树里的元素也是有序的。

那么一个B+树的节点中到底存多少个元素最合适？为什么？

B+树中一个节点为一页或者页的倍数最为合适

因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费。

如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。

所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。

页，能简单说一下吗？

首先mysql的基本存储结构就是页：

• 各个数据页可以组成一个双向链表
• 每个数据页中的记录又可以组成一个单向链表
• 每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后在遍历该槽对应分组中的记录
• 以其他列（非主键）作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录

什么是回表

回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：

select * from table where name = '丙丙'

执行的流程是先查询到name索引上的“丙丙”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。

回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

什么是覆盖索引

这个其实比较好理解，刚才我们是 select * ，查询所有的，我们如果只查询ID那，其实在Name字段的索引上就已经有了，那就不需要回表了。

覆盖索引可以减少树的搜索次数，提升性能，他也是我们在实际开发过程中经常用来优化查询效率的手段。

很多联合索引的建立，就是为了支持覆盖索引，特定的业务能极大的提升效率。

索引的最左匹配原则知道吗?

最左匹配原则：

• 索引可以简单如一个列（a）,也可以复杂如多个列（a,b,c,d），即联合索引。
• 如果是联合索引，索引只能用于查找key是否相等，遇到范围查询（>,<,between,like左匹配）等就不能进一步匹配了
• 因此，列的排列顺序决定了可命中索引的列数

总结

• 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。
• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
• 索引列不能参与计算，尽量保持列“干净”
• 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

接下来，我们说一下数据库调优