【数据库】MySQL 数据存储

1.为什么 MySQL 存储选择了 B+ 树？

没有选择其他的平衡二叉搜索树（AVL/红黑树）？

1.B+ 树，每层的 key 比二叉树多，则 同样的 key 情况下，高度一定更低。

假设第一层 1000 ，则第二层 1000 x 1000 ≈ 100万，第三层约等于1亿；而二叉树要存取同样的数据高度肯定远大于 3，大约 2 ³⁰ ≈ 十亿。

2.MySQL 的存储一般放在硬盘上， 硬盘的 IO 速度非常慢（相对于内存）。而对于搜索的一次（查找/插入/删除）操作本质都需要查找，所以查找每经过一层高度，就要发生一次 IO 读取，又因为 IO 非常慢。为了优化，减少 IO 次数，减少树的高度，MySQL 只能选择 B+ 树。

只要类似于 MySQL 的数据存在硬盘里的应用，都不能使用二叉系列树（二叉树、红黑树等）

2.MySQL 内部（InnoDB）是如何存储数据的？

聚簇索引，即 key + 行数据 保存都保存在 B+ 树中。

解释：把主键作为 key ，保存成一颗 B+ 树，所有的主键 + 行数据，全部保存在叶子（叶子结点是链表）中。

3.MySQL中的索引（Index/key）

3.1为什么引进索引？

为了提升查询效率。

1.不命中索引（无法使用这颗B+树）的查询，时间复杂度O(n)。
2. 为了提高效率必须使用搜索专用的数据结构（哈希表、搜索树）。
3.一般指的索引都是普通索引：key:[多个主键]

注意：每次创建索引，都必须指定到那些具体的行上（指定B+树的key）；都是需要额外空间的（另外的B+树也是要存储的）。

3.2索引的优缺点

一、优点
1.提升查询效率
注意：INSERT/UPDATE/DELETE操作也是先查找，后插入/更新/删除的。

二、缺点
1.每次建立索引都需要占用硬盘空间，索引不是越多越好
2.增加维护成本（降低了修饰效率）。如果只有一个地方保存数据，修改只需要修改一处就可以了，但有了索引，修改需要修改多处，必然效率会降低。

3.3索引的命中问题

hit / miss

一个表有多个索引，问本次查询是否会用到索引（是否能用到某个B+树）？

SELECT * FROM students; 
-- miss
-- 直接遍历链表（主键叶子组成的链表）就可以了
SELECT * FROM students WHERE id=10;
-- hit主键
SELECT * FROM students BETWEEN 3 AND 13;
-- hit主键
-- 利用搜索树有序特点
SELECT * FROM students WHERE name LIKE ‘cy%’;
-- hit
-- 最左原则（左边能匹配，就可以使用索引）
SELECT * FROM students WHERE name LIKE ‘%cy’;
-- miss
-- 不知道往那边走
SELECT * FROM students WHERE id!=10;
-- miss（不一定 要看数据量）
-- 直接遍历链表 假设100行数据，一定要查出99行，直接遍历链表可能速度更快一点
SELECT * FROM students ORDER BY name;
-- 不一定
-- 数据小：把所有数据通过主键上的链表找出来，然后 name 列排序
-- 数据大：利用 idx_name 的 key 有序的特点，直接对应把所有的 id 按照 name 有序找出来

注意：没必要猜有没有命中索引，直接使用 explan 命令可以直观的看到是否使用索引。eg：

EXPLAIN SELECT * FROM students WHERE name LIKE ‘%cy’;
-- 直接显示结果

官网链接：EXPLAN

3.4索引分类和适用范围

一、分类
1.按数据结构分：哈希索引（用的较少）、B+树索引（InnoDB只支持B+树索引）
2.按用途分：主键索引（PRIMARY KEY）、唯一键索引（UNIQUE）、全文索引、普通索引
3.按照叶子中是否保存了所有数据：聚族索引（主键）、非聚族索引

二、适用范围
1.某个字段上，查询非常多
2.数据量足够大（ O(n) >> O(log(n)) ）