Mysql面试篇(二)InnoDB vs MyISAM & 索引

最新推荐文章于 2024-05-28 13:50:02 发布

luelueking

最新推荐文章于 2024-05-28 13:50:02 发布

阅读量618

点赞数 1

分类专栏： Mysql 文章标签：面试 mysql 数据库

本文链接：https://blog.csdn.net/m0_61206225/article/details/124511158

版权

Mysql 专栏收录该内容

6 篇文章

订阅专栏

本文详细比较了InnoDB和MyISAM两种数据库引擎在索引结构、事务支持、锁定机制和查询效率等方面的差异，重点讲解了聚簇索引和二级索引的工作原理，以及索引选择、最左前缀原则和索引下推的应用。

摘要由CSDN通过智能技术生成

3. InnoDB vs MyISAM

😀InnoDB

索引分为聚簇索引与二级索引
- 聚簇索引：主键值作为索引数据，叶子节点还包含了所有字段数据，索引和数据是存储在一起的
- 二级索引：除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据，叶子节点还包含了主键值
支持事务
- 通过 undo log 支持事务回滚、当前读（多版本查询）
- 通过 redo log 实现持久性
- 通过两阶段提交实现一致性
- 通过当前读、锁实现隔离性
支持行锁、间隙锁
支持外键

😀MyISAM

索引只有一种
- 被索引字段值作为索引数据，叶子节点还包含了该记录数据页地址，数据和索引是分开存储的
不支持事务，没有 undo log 和 redo log
仅支持表锁
不支持外键
会保存表的总行数

InnoDB 索引特点

聚簇索引：主键值作为索引数据，叶子节点还包含了所有字段数据，索引和数据是存储在一起的

◉主键即 7369、7499、7521 等

二级索引：除主键外的其它字段建立的索引称为二级索引。被索引的字段值作为索引数据，叶子节点还包含了主键值

上图中 800、950、1100 这些是工资字段的值，根据它们建立了二级索引

上图中，如果执行查询 select empno, ename, sal from emp where sal = 800，这时候可以利用二级索引定位到 800 这个工资，同时还能知道主键值 7369
但 select 字句中还出现了 ename 字段，在二级索引中不存在，因此需要根据主键值 7369 查询聚簇索引来获取 ename 的信息，这个过程俗称回表

MyISAM 索引特点

被索引字段值作为索引数据，叶子节点还包含了该记录数据页地址，数据和索引是分开存储的

4. 索引

索引基础

常见索引

哈希索引
- 理想时间复杂度为 $O(1)$
- 适用场景：适用于等值查询的场景，内存数据的索引
- 典型实现：Redis，MySQL 的 memory 引擎
平衡二叉树索引
- 查询和更新的时间复杂度都是 $O(log_2(n))$
- 适用场景：适用于等值查询以及范围查询；适合内存数据的索引，但不适合磁盘数据的索引，可以认为树的高度决定了磁盘 I/O 的次数，百万数据树高约为 20
BTree 索引
- BTree 其实就是 n 叉树，分叉多意味着节点中的孩子（key）多，树高自然就降低了
- 分叉数由页大小和行（包括 key 与 value）大小决定
  - 假设页大小为 16k，每行 40 个字节，那么分叉数就为 16k / 40 ≈ 410
  - 而分叉为 410，则百万数据树高约为3，仅 3 次 I/O 就能找到所需数据
- 局部性原理：每次 I/O 按页为单位读取数据，把多个 key 相邻的行放在同一页中（每页就是树上一个节点），能进一步减少 I/O
B+ 树索引
- 在 BTree 的基础上做了改进，索引上只存储 key，这样能进一步增加分叉数，假设 key 占 13 个字节，那么一页数据分叉数可以到 1260，树高可以进一步下降为 2

树高计算公式

$log{10}(N) / log{10}(M)$ 其中 N 为数据行数，M 为分叉数

BTree vs B+Tree

无论 BTree 还是 B+Tree，每个叶子节点到根节点距离都相同
BTree key 及 value 在每个节点上，无论叶子还是非叶子节点
B+Tree 普通节点只存 key，叶子节点才存储 key 和 value，因此分叉数可以更多
- 不过也请注意，普通节点上的 key 有的会与叶子节点的 key 重复
B+Tree 必须到达叶子节点才能找到 value
B+Tree 叶子节点用链表连接，可以方便范围查询及全表遍历

注：这两张图都是仅画了 key，未画 value

B+Tree 新增 key

假设阶数（m）为5

1. 若为空树，那么直接创建一个节点，插入 key 即可，此时这个叶子结点也是根结点。例如，插入 5
2.插入时，若当前结点 key 的个数小于阶数，则插入结束
3.依次插入 8、10、15，按 key 大小升序
4.插入 16，这时到达了阶数限制，所以要进行分裂
5.叶子节点分裂规则：将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前 m/2 个（2个）记录，右结点包含剩下的记录，将中间的 key 进位到父结点中。注意：中间的 key 仍会保留在叶子节点一份
6.插入 17
7.插入 18，这时当前结点的 key 个数到达 5，进行分裂
8.分裂成两个结点，左结点 2 个记录，右结点 3 个记录，key 16 进位到父结点中
9.插入 19、20、21、22、6、9
10.插入 7，当前结点的 key 个数到达 5，需要分裂
11.分裂后 key 7 进入到父结点中，这时父节点 key 个数也到达 5
12.非叶子节点分裂规则：左子结点包含前 (m-1)/2 个 key，将中间的 key 进位到父结点中（不保留），右子节点包含剩余的 key

B+Tree 查询 key

以查询 15 为例

B+Tree 删除叶子节点 key

初始状态
删完有富余。即删除后结点的key的个数 > m/2 – 1，删除操作结束，例如删除 22
删完没富余，但兄弟节点有富余。即兄弟结点 key 有富余（ > m/2 – 1 ），向兄弟结点借一个记录，同时替换父节点，例如删除 15
兄弟节点也不富余，合并兄弟叶子节点。即兄弟节点合并成一个新的叶子结点，并删除父结点中的key，将当前结点指向父结点，例如删除 7
也需要删除非叶子节点中的 7，并替换父节点保证区间仍有效
左右兄弟都不够借，合并

B+Tree 删除非叶子节点 key

接着上面的操作

1.非叶子节点 key 的个数 > m/2 – 1，则删除操作结束，否则执行 2
2.若兄弟结点有富余，父结点 key 下移，兄弟结点 key 上移，删除结束，否则执行 3
3.若兄弟节点没富余，当前结点和兄弟结点及父结点合并成一个新的结点。重复 1

命中索引

准备数据

修改 MySQL 配置文件，在 [mysqld] 下添加 secure_file_priv= 重启 MySQL 服务器，让选项生效

执行 db.sql 内的脚本，建表

执行 LOAD DATA INFILE 'D:\\big_person.txt' INTO TABLE big_person; 注意实际路径根据情况修改

测试表 big_person（此表数据量较大，如果与其它表数据一起提供不好管理，故单独提供），数据行数 100 万条，列个数 15 列。为了更快速导入数据，这里采用了 load data infile 命令配合 *.txt 格式数据

索引用于排序

/* 测试单列索引并不能在多列排序时加速 */
create index first_idx on big_person(first_name);
create index last_idx on big_person(last_name);
explain select * from big_person order by last_name, first_name limit 10; 

/* 多列排序需要用组合索引 */
alter table big_person drop index first_idx;
alter table big_person drop index last_idx;
create index last_first_idx on big_person(last_name,first_name);

/* 多列排序需要遵循最左前缀原则, 第1个查询可以利用索引，第2,3查询不能利用索引 */
explain select * from big_person order by last_name, first_name limit 10; 
explain select * from big_person order by first_name, last_name limit 10; 
explain select * from big_person order by first_name limit 10; 

/* 多列排序升降序需要一致，查询1可以利用索引，查询2不能利用索引*/
explain select * from big_person order by last_name desc, first_name desc limit 10; 
explain select * from big_person order by last_name desc, first_name asc limit 10;

最左前缀原则

若建立组合索引 (a,b,c)，则可以利用到索引的排序条件是：

order by a

order by a, b

order by a, b, c

索引用于 where 筛选

参考 MySQL :: MySQL 8.0 Reference Manual :: 8.3.6 Multiple-Column Indexes

/* 模糊查询需要遵循字符串最左前缀原则，查询2可以利用索引，查询1,3不能利用索引 */
explain SELECT * FROM big_person WHERE first_name LIKE 'dav%' LIMIT 5;
explain SELECT * FROM big_person WHERE last_name LIKE 'dav%' LIMIT 5;
explain SELECT * FROM big_person WHERE last_name LIKE '%dav' LIMIT 5;

/* 组合索引需要遵循最左前缀原则，查询1,2可以利用索引，查询3,4不能利用索引 */
create index province_city_county_idx on big_person(province,city,county);
explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区';
explain SELECT * FROM big_person WHERE county='中西区' AND city='宜兰县' AND province ='上海';
explain SELECT * FROM big_person WHERE city='宜兰县' AND county='中西区';
explain SELECT * FROM big_person WHERE county='中西区';

/* 函数及计算问题，一旦在字段上应用了计算或函数，都会造成索引失效。查询2可以利用索引，查询1不能利用索引 */
create index birthday_idx on big_person(birthday);
explain SELECT * FROM big_person WHERE ADDDATE(birthday,1)='2005-02-10';
explain SELECT * FROM big_person WHERE birthday=ADDDATE('2005-02-10',-1);

/* 隐式类型转换问题
* 查询1会发生隐式类型转换等价于在phone上应用了函数，造成索引失效
* 查询2字段与值类型相同不会类型转换，可以利用索引
*/
create index phone_idx on big_person(phone);
explain SELECT * FROM big_person WHERE phone = 13000013934;
explain SELECT * FROM big_person WHERE phone = '13000013934';

最左前缀原则（leftmost prefix）

若建立组合索引 (a,b,c)，则可以利用到索引的查询条件是：

where a = ?

where a = ? and b = ? （注意与条件的先后次序无关，也可以是 where b = ? and a = ?，只要出现即可）

where a = ? and b = ? and c = ? （注意事项同上）

不能利用的例子：

where b = ?

where b = ? and c = ?

where c = ?

特殊情况：

where a = ? and c = ?（a = ? 会利用索引，但 c = ? 不能利用索引加速，会触发索引条件下推）

索引条件下推

参考 MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.6 Index Condition Pushdown Optimization

/* 查询 1,2,3,4 都能利用索引，但 4 相当于部分利用了索引，会触发索引条件下推 */
explain SELECT * FROM big_person WHERE province = '上海';
explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市';
explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市' AND county='中西区';
explain SELECT * FROM big_person WHERE province = '上海' AND county='中西区';

索引条件下推

MySQL 执行条件判断的时机有两处：

服务层（上层，不包括索引实现）

引擎层（下层，包括了索引实现，可以利用）

上面查询 4 中有 province 条件能够利用索引，在引擎层执行，但 county 条件仍然要交给服务层处理

在 5.6 之前，服务层需要判断所有记录的 county 条件，性能非常低

5.6 以后，引擎层会先根据 province 条件过滤，满足条件的记录才在服务层处理 county 条件

我们现在用的是 5.6 以上版本，所以没有体会，可以用下面的语句关闭索引下推优化，再测试一下性能

SET optimizer_switch = 'index_condition_pushdown=off';
SELECT * FROM big_person WHERE province = '上海' AND county='中西区';

二级索引覆盖

explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county= '中西区';
explain SELECT id,province,city,county FROM big_person WHERE province = '上海' ANDcity='宜兰县' AND county='中西区';

根据查询条件查询 1，2 都会先走二级索引，但是二级索引仅包含了 (province, city, county) 和 id 信息

查询 1 是 select *，因此还有一些字段二级索引中没有，需要回表（查询聚簇索引）来获取其它字段信息
查询 2 的 select 中明确指出了需要哪些字段，这些字段在二级索引都有，就避免了回表查询

其它注意事项

表连接需要在连接字段上建立索引
不要迷信网上说法，具体情况具体分析

例如：

create index first_idx on big_person(first_name);

/* 不会利用索引，因为优化器发现查询记录数太多，还不如直接全表扫描 */
explain SELECT * FROM big_person WHERE first_name > 'Jenni';

/* 会利用索引，因为优化器发现查询记录数不太多 */
explain SELECT * FROM big_person WHERE first_name > 'Willia';

/* 同一字段的不同值利用 or 连接，会利用索引 */
explain select * from big_person where id = 1 or id = 190839;

/* 不同字段利用 or 连接，会利用索引(底层分别用了两个索引) */
explain select * from big_person where first_name = 'David' or last_name = 'Thomas';

/* in 会利用索引 */
explain select * from big_person where first_name in ('Mark', 'Kevin','David'); 

/* not in 不会利用索引的情况 */
explain select * from big_person where first_name not in ('Mark', 'Kevin','David');

/* not in 会利用索引的情况 */
explain select id from big_person where first_name not in ('Mark', 'Kevin','David');