在昨天的面试中问到了 MySQL 索引怎么优化(查询很慢怎么办), 回答的很不理想, 所以今天来总结几篇关于 MySQL 索引的知识.
1. 什么是索引?
首先我们一定要明确什么是索引? 我自己的总结就是索引是一种数据结构, 可以帮助我们快速访问数据库的指定信息, 就像一本书的目录一样, 可以加快查询速度
2.MySQL 存储引擎
MySQL 中最常见的存储引擎有 InnoDB 和 MyISAM, 它们的主要区别如下:
MyISAM 不支持事务; InnoDB 是事务类型的存储引擎.
MyISAM 只支持表级锁; InnoDB 支持行级锁和表级锁, 默认为行级锁.
MyISAM 引擎不支持外键; InnoDB 支持外键.
对于 count(*)查询来说 MyISAM 更有优势, 因为其保存了行数.
InnoDB 是为处理巨大数据量时的最大性能设计的存储引擎.
MyISAM 支持全文索引(FULLTEXT);InnoDB 不支持.
总结:
最主要的区别就是 MyISAM 表不支持事务, 不支持行级锁, 不支持外键. InnoDB 表支持事务, 支持行级锁, 支持外键.
在 MySQL5.5.5 版本之后, InnoDB 已经成为了其默认的存储引擎, 也是大部分公司的不二选择, 毕竟谁家公司会不要求数据库支持事务呢? 谁家公司又可以忍受表级锁导致的读写冲突呢?
除了 InnoDB 以及 MyISAM 存储引擎外, 常见的考察存储引擎还有 Memory, 使用 Memory 作为存储引擎的表也可以叫做内存表, 将数据存储在了内存中, 所以适合做临时表来使用, 在索引结构上支持 B + 树索引和 Hash 索引.
3. 为什么选择 B + 树索引
这里推荐一个外国的学习数据结构的一个网站:(非常的 nice)
首先列举几个可以做索引的数据结构:
二叉查找树
红黑树
B 树
B + 树
1. 平衡二叉查找树(AVL 树)
看一下最基本的结构, 这里我也是插入了 7 个数据
说一下特征:
非叶子结点最多有两个子结点
非叶子结点大于左边子结点, 小于右边子结点
没有值相等重复的点
每个节点的左子树和右子树的高度差至多为 1
它的快体现在哪里呢?
比如说我们查询 3:3 是小于根节点 4 的, 从它的左子树找, 3 是大于 2 的, 所以在 2 的右子树, 这样就查询到 3 的位置了, 查询速度为 O(LogN)
缺点: 维护平衡二叉树的代价太大每次都需要左旋或者右旋来维持平衡
2. 红黑树
插入 10 后
特征:
每个节点或者是黑色, 或者是红色.
根节点是黑色.
每个叶子节点 (NIL) 是黑色. [注意: 这里叶子节点, 是指为空 (NIL 或 NULL) 的叶子节点!]
如果一个节点是红色的, 则它的子节点必须是黑色的.
从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点.[这里指到叶子节点的路径]
到这里我们要说到一些东西
磁盘 IO
计算机硬件性能在过去十年间的发展普遍遵循摩尔定律, 通用计算机的 CPU 主频早已超过 3GHz, 内存也进入了普及 DDR4 的时代. 然而传统硬盘虽然在存储容量上增长迅速, 但是在读写性能上并无明显提
升, 同时 SSD 硬盘价格高昂, 不能在短时间内完全替代传统硬盘. 传统磁盘的 I/O 读写速度成为了计算机系统性能提高的瓶颈, 制约了计算机整体性能的发展.
其实简单来说就是我们要减少磁盘 IO 的次数, 树的深度越大, 磁盘 IO 的次数就越多, 所以无论是从它的平衡代价或者磁盘 IO 次数来讲红黑树和 AVL 树都太适合.
局部性原理与磁盘预读:
由于存储介质的特性, 磁盘本身存取就比主存慢很多, 再加上机械运动耗费, 磁盘的存取速度往往是主存的几百分分之一, 因此为了提高效率, 要尽量减少磁盘 I/O. 为了达到这个目的, 磁盘往往不是严格按需读取, 而是每次都会预读, 即使只需要一个字节, 磁盘也会从这个位置开始, 顺序向后读取一定长度的数据放入内存. 这样做的理论依据是计算机科学中著名的局部性原理:
当一个数据被用到时, 其附近的数据也通常会马上被使用.
程序运行期间所需要的数据通常比较集中.
由于磁盘顺序读取的效率很高(不需要寻道时间, 只需很少的旋转时间), 因此对于具有局部性的程序来说, 预读可以提高 I/O 效率.
红黑树这种结构, h 明显要深的多. 由于逻辑上很近的节点 (父子) 物理上可能很远, 无法利用局部性, 所以红黑树的 I/O 渐进复杂度也为 O(h), 效率明显不太尽人意.
3.B 树
一个 M 阶的 b 树具有如下几个特征:
定义任意非叶子结点最多只有 M 个儿子, 且 M>2;
根结点的儿子数为[2, M];
除根结点以外的非叶子结点的儿子数为[M/2, M], 向上取整;
非叶子结点的关键字个数 = 儿子数 - 1;
所有叶子结点位于同一层;
k 个关键字把节点拆成 k+1 段, 分别指向 k+1 个儿子, 同时满足查找树的大小关系.
特性
关键字集合分布在整颗树中;
任何一个关键字出现且只出现在一个结点中;
搜索有可能在非叶子结点结束;
其搜索性能等价于在关键字全集内做一次二分查找;
4.B + 树
M 阶的 b + 树的特征:
有 n 棵子树的非叶子结点中含有 n 个关键字(b 树是 n-1 个), 这些关键字不保存数据, 只用来索引, 所有数据都保存在叶子节点(b 树是每个关键字都保存数据).
所有的叶子结点中包含了全部关键字的信息, 及指向含这些关键字记录的指针, 且叶子结点本身依关键字的大小自小而大顺序链接.
所有的非叶子结点可以看成是索引部分, 结点中仅含其子树中的最大 (或最小) 关键字.
通常在 b + 树上有两个头指针, 一个指向根结点, 一个指向关键字最小的叶子结点.
同一个数字会在不同节点中重复出现, 根节点的最大元素就是 b + 树的最大元素.
B + 树相对于 B 树的优势
b + 树的中间节点不保存数据, 所以磁盘页能容纳更多节点元素, 更 "矮胖";
b + 树查询必须查找到叶子节点, b 树只要匹配到即可不用管元素位置, 因此 b + 树查找更稳定(并不慢);
对于范围查找来说, b + 树只需遍历叶子节点链表即可, b 树却需要重复地中序遍历, 如下两图:
4. 总结
数据库引擎: InnoDB 和 MyISAM
主要区分: 事务, 外键, 行级锁(以上 InnoDB 都支持, MyISAM 只支持表级锁)
为什么选择 B + 树:
AVL 树和红黑树深度深, 磁盘 IO 次数多, 父子节点物理上远, 不满足局部性原理
B + 树, 非叶子节点不保存数据, 叶子结点之间有指针可以进行范围查找, 并且 B + 树里的元素也是有序的.
附加问题:
B + 树中一个节点到底多大合适?
B + 树中一个节点为一页 (16KB) 或页的倍数最为合适.
因为如果一个节点的大小小于 1 页, 那么读取这个节点的时候其实也会读出 1 页, 造成资源的浪费.
如果一个节点的大小大于 1 页, 比如 1.2 页, 那么读取这个节点的时候会读出 2 页, 也会造成资源的浪费.
所以为了不造成浪费, 所以最后把一个节点的大小控制在 1 页, 2 页, 3 页, 4 页等倍数页大小最为合适.
来源: https://www.cnblogs.com/dmzna/p/12930067.html