MySQL索引与算法

浅草没蹄——

已于 2022-07-16 14:19:06 修改

阅读量312

点赞数 2

文章标签： mysql 算法 b树

于 2021-10-23 20:47:14 首次发布

本文链接：https://blog.csdn.net/qq_42673707/article/details/120869154

版权

MySQL索引与算法

仅作为笔记，除部分装载部分，码字不易，转载请标明出处。

前言

仅作为笔记

一、InnoDB存储引擎索引概述

所支持的索引：
1）B+树索引（也就是传统意义上的索引）：根据键值(key value）查找数据，最终定位的不是具体的行，而是被查找数据所在的页，再在内存中对页进行查找。
2）全文索引
3）哈希索引（自适应哈希，不由使用者操作，系统自己根据情况判断是否使用）

二、数据结构与算法

下面两个小结是理解B+树的铺垫知识。

2.1 二分查找法

不再赘述，提一嘴别忘了时间复杂度为O(logn)

2.2 二叉查找树和平衡二叉树

二叉查找树：左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。如下图：
平衡二叉树：二叉查找树的规则使得其排列很灵活，会导致出现一些畸形的情况，比如下图这种就会导致查找效率极低，退化成普通链表。

所以需要使他没那么畸形，这就诞生了平衡二叉树（AVL），定义：满足二叉查找树的前提，必须满足任何节点的两个子树的高度最大差为1。平衡二叉树的删除，插入操作以及维护平衡二叉树的代价都不低，靠的是左旋右旋方法。

三、B+树

B+树由B树和索引顺序访问方法演化而来。
B+树相对于B树作为索引的优点：
1）B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；
2）B+树查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；
3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）
B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低

以上几点总结来自博客园的 Assassinの大神总结的，链接在此：https://www.cnblogs.com/lianzhilei/p/11250589.html，

3.1 B+树的插入操作

插入操作三大情况：

具体操作参考书籍《MySQL技术内幕：InnoDB存储引擎》第188页至189页。
注意：B+树的插入操作并不是一遇到插入就采用拆分页的手段（一个节点对应一页），而是利用兄弟节点（没有满的情况下）进行旋转操作，避免拆分页。

3.2 B+树的删除操作

删除操作三种情况：

具体操作参考书籍《MySQL技术内幕：InnoDB存储引擎》第190页至191页。

四、B+索引树

4.1 聚集索引

定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
详细解析：首先先回顾一个概念，还记得最开始我们知道，数据库表的结构是：表、段、页、行，而InnoDB能管理的最小单位是页，为什么是页呢？因为InnoDB所使用的数据结构是矮胖的B+树，B+树的节点对应的是页，这就又回到了最开始我们了解到的定义，也就是页分为数据页，索引页，这是为什么呢？因为B+树除了包含数据的节点还有作为索引的节点！回到页的概念上来，既然页是B+树的节点也就是查询到的最小单位（也就是最开始定义里说的实际上查询只能查询到页，具体位置靠遍历页的原因）。而具体的数据，是一行一行写进每一个页的（你也可以想象成写进每一个数据节点的），这一行一行的数据当然可以是数据，也可以是索引，而聚集索引呢，其所有的叶子节点装的都是数据。其叶子节点（数据页）装的数据（数据行）的物理顺序，和主键（主键通常作为聚集）的逻辑顺序是一样的，如下图：

图片来源于：https://www.cnblogs.com/s-b-b/p/8334593.html。
图上id作为主键（聚集），前面的为物理地址，这里要注意，物理地址只是与主键（聚集）的逻辑顺序是相同的（所以只能建立一个聚集索引），逻辑顺序是相同，逻辑顺序是相同，重要的事说三遍，意思就是物理地址顺序和主键一样但是可以不是主键那样连续的（逻辑连续即可）！究其原因，首先物理上保证连续的话是需要维护成本的，再者，页是通过主键排序的双向链表链接，以及每个页中的记录也是通过双向链表链接，意味着物理上连续没有了意义。
聚集索引的优缺点：
1）优点：结合上面对聚集索引的原理分析后不难得出，聚集索引很方便排序查找和范围查找。对于排序查找，比如你要找最后面或者最前面多少个，因为聚集索引将数据都排序的写进了叶子节点，且B+树索引是双向链表的，所以只需要查找最后或最前的数据页就可以了。对于范围查找，是因为每个数据页都是根据主键（聚集）排序的，所以只需要根据叶子节点（数据页）的上层节点中间节点就可以查找到某一范围内的数据了。
2）缺点：使用聚集索引的写入性能并不高，因为需要移动对应数据的物理位置。

4.2 辅助索引

辅助索引也叫非聚集索引
定义：该索引中，索引的逻辑顺序与磁盘上数据行的物理存储顺序不同，一个表中可以有多个非聚集索引。
详细解析：有了上面聚集索引的基础，辅助索引（非聚集索引）也就是其子叶节点不全是包含的数据，也可以说数据不是全部放在子叶节点里面的，还包含了索引，这个索引指向真正的数据，而这个索引就是行数据的聚集索引键。由于非聚集索引不影响数据在聚集索引中的组织，所以每张表上可以有多个辅助索引（非聚集索引）。如果要使用这个来做查找，首先会通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录，比如：如一颗高度为3的辅助索引树，那首先需要对这个课辅助索引树遍历三次找到指定主键（聚集），如果聚集索引树的高度同样是3，那么还需要对这个聚集索引进行三次遍历。总共就是6次。

4.3 B+树索引的分裂

索引分裂不完全是采取中间记录分裂的，比如如果插入都是顺序的，那么会出现空间浪费的情况，如：数据库中B+树索引的分裂并不总是从页的中间记录开始，这样可能会导致空间的浪费，例如下面的记录：
1, 2, 3, 4, 5, 6, 7, 8, 9
插入式根据自增顺序进行的，若这时插入10这条记录后需要进行页的分裂操作，那么根据B+树对半分裂的规则，会将记录5作为分裂点记录，分裂后得到下面两个页：
P1: 1, 2, 3, 4
P2: 5, 6, 7, 8, 9, 10
然而由于插入是顺序的，P1这个页中将不再会有记录被插入，从而导致空间的浪费，而P2又会再次分裂。那么如何优化？
InnoDB存储引擎的Page Header有以下几个部分用来保存插入的顺序信息：
PAGE_LAST_INSERT：最后插入记录的位置。
PAGE_DIRECTION：记录插入的方向。假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。
**PAGE_N_DIRECTION：**假设连续几次插入新记录的方向都是一致的，InnoDBhi把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

五、Cardinality值

5.1 什么是Cardinality

Cardinality是一个预估值，表示索引中不重复记录数量的预估值，用于判断条件查询中是否需要给出现的列添加索引。
什么时候加索引？：一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。也就是说所要选择的东西或者访问出来的东西很少时才有意义，比如访问性别，这个只有男和女，在用条件查询时能访问出一半，这时候使用B+树索引没意义，相反，在某个字段的取值范围很广，几乎没有重复，属于高选择性时使用B+树才合适，比如姓名查询。总之——在访问选择性属性高的字段且取出很少一部分数据时，这时候对这个字段使用B+索引树很合适。在实际操作中也就是当Cardinality/n_rows_in_table尽可能趋近1时就需要使用B+索引树。

5.2 InnoDB存储引擎的Cardinality统计

略，意义不大。

六、B+树索引的使用

6.1不同应用中B+树索引的使用

先知道一个东西：
1）OLTP（on-line transaction processing）联机事务处理，OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，记录即时的增、删、改、查，比如在银行存取一笔款，就是一个事务交易。OLTP数据量不是很大，一般只读/写数十条记录，处理简单的事务
2）OLAP（On-Line Analytical Processing）联机分析处理，OLAP即联机分析处理，是数据仓库的核心部心，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态报表系统。OLAP数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大。
OLTP，意味着一定要使用B+树索引，而OLAP则不一定，要具体情况具体分析。这就是这一节的意义。

6.2 联合索引

定义：就是对表上多个列进行索引而已，如下图：

分别是联合索引的使用，索引idx_a_b是联合索引，联合的列为（a,b），如果要查SELECT * FROM TABLE WHERE a==xxx and b=xxx，这样可以使用联合索引，单查a也可以使用，但是单查b不行，因为看图就知道b并没有按照顺序排列。
联合索引的意义或者说优点：
1）建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销，所以相对于给三个单独建立索引减小了不少的开支。
2）同样以（a,b,c）为例，如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。实现了覆盖索引，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
3） 索引字段越多，通过索引筛选出的数据越少。假如有1000W条数据的表，where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% 10% 10%=1w，然后没有回表直接进行排序、分页**。哪个更高效，一眼便知。

6.3 覆盖索引

什么是回表查询：如果使用普通索引查询其他非索引数据的话，因为普通索引无法定位行数据，所以会进行回表查询，通过聚集索引再次扫描索引树，确定行数据，然后得到想要查询的其他数据。覆盖索引可以解决回表查询问题。
覆盖索引也叫索引覆盖，如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。从辅助索引（非聚集索引，非聚簇索引）中就可以得到查询的记录，而不需要查询聚集索引中的记录，也就是覆盖索引搭配非聚集索引使用，这样的好处是非聚集索引不包含整行记录（数据行）的所有信息，故其大小要远小于聚集索引，可以减少大量的IO操作。
覆盖索引的实现：点击这里可以详细的查看索引覆盖
不是所有的索引都可以用作为覆盖索引，哈希索引，空间索引，全文索引等都不存储列值，无法作为覆盖索引进行使用。只有B-tree才能用作为覆盖索引。
注意：联合索引和覆盖索引不是一个平行的概念，覆盖索引是一种建立索引的策略，比如说可以通过联合索引达到覆盖索引的效果，覆盖索引是一种目的，而联合索引是达到这么目的的方法。

6.4 优化器选择不使用索引的情况

意思是有时候你虽然制定了索引（此索引指的是你已经弄成了索引的具体的一个字段）但是他可能不按照你指定的来，这种情况多发生于：范围查找，JOIN链接操作等情况下。比如：

查找单号大于10000小于102000的订单，此时指定的是使用orderid（非聚集索引）但是实际上执行的却是主键（聚集索引），原因是优化器认为这里用户要查询的数据是整行信息，而orderid（非聚集信息）并没有覆盖完全，要拿到数据还需要要进行二次查找，而且二次查找的书签还是无序的，这样变成了磁盘上的离散操作，在访问量大的情况下会拖慢查询速度。

6.5 索引提示

显示的告诉优化器使用哪个索引。
可能用到这个的情况：
1）数据库优化器的错误选择导致SQL很慢。
2）SQL语句可选择的索引太多，优化器执行计划时间的开销可能大于SQL执行本身。

6.6 Multi-Range Read（MRR）优化

作用：MRR优化主要作用是减少随机访问磁盘的次数，对于使用辅助索引（非聚集索引）的 explain type range 的场景。
使用MRR在数据没有预热的情况下，可以很大程度的提升范围查询的性能，我们都知道mysql的索引和数据是组合在一起的就是聚族索引，辅助索引的叶子节点就是主键ID，mysql一般都是先从辅助索引中查询主键ID，在回表查询具体的行数据，如果不适用MRR优化的话，需要一个个主键无序的查询具体的数据，会导致数据的page页重复请求，会有在同一个page中的数据，因为没有连续请求可能会导致对同一个page发送多次系统IO把数据读到内存中，内存会有LRU算法淘汰page页。开启MRR优化后，会先对辅助索引查询的主键ID进行排序，就可以减少磁盘请求，因为page页已经在内存中，这种情况在mysql没有预热的情况比较明显。

七、哈希算法

7.1 InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法对字典进行查找。
避免哈希冲突的方式是链表方式，也就是将冲突的放在一起，形成一个链表。
哈希函数采用的是除法散列方式。
还有的解决哈希冲突的方法：
1，开放定址法： 当冲突发生时，使用某种探测技术在散列表中形成一个探测序列。沿此序列逐个单元地查找，直到找到给定的关键字，或者碰到一个开放的地址（即该地址单元为空）为止（若要插入，在探查到开放的地址，则可将待插入的新结点存人该地址单元）。
2，再哈希法：
再哈希法又叫双哈希法，有多个不同的Hash函数，当发生冲突时，使用第二个，第三个，….，等哈希函数计算地址，直到无冲突。虽然不易发生聚集，但是增加了计算时间。
3，链地址法：
链地址法的基本思想是：每个哈希表节点都有一个next指针，多个哈希表节点可以用next指针构成一个单向链表。
4，建立公共溢出区：
这种方法的基本思想是：将哈希表分为基本表和溢出表两部分，凡是和基本表发生冲突的元素，一律填入溢出表
除法散列方式：H(k) = k Mod m ，k为需要哈希处理的数字，其中m表示卡槽，也就是哈希处理后可以存放数字的卡槽数量。m的取值如是描述：m不应该为2 的幂。因为这可以减小哈希冲突。

7.2 自适应哈希索引

由InnoDB存储引擎自己控制，在数据库中，哈希索引只能用来搜索等值查询，所以什么时候开始使用是自适应的，但是可以通过参数来控制。

八、全文检索

8.1 概述

在使用模糊查询如like这种的时候使用全文检索·，也就是把存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术，MySQL中的全文索引表跟普通的表生成的文件不一样，在底层文件会生成fts开头的索引文件，只要在实际生成文件包含这些，就说明表上创建了全文索引。详细的看这里：全文索引全文索引依靠倒排索引技术实现，代表的全文索引引擎是ElasticSearch，详细看这里：es搜索引擎和全文索引

8.2 倒排索引

倒排索引是全文检索的实现方式，倒排索引和B+树索引一样，也是一种索引结构。

8.3 InnoDB全文检索

对于模糊查询的情况最容易想到的就是 where … like %_… 这样。确实，在列的内容十分大的时候，like的性能就不能令人满意了，因为这个关键字并没有保证每次查询都能用上索引。因此，全文索引就派上用场。除了性能上的提高，全文索引提供了更灵活的服务
总之就是，全文索引因为用上了索引，性能更高，有词库支撑可以进行分词提供了一些语义查询的功能，有词语停用表忽略某些词语，有词语最大最小值可以设置等更灵活。