数据库篇

1. MySQL 事务隔离级别

未提交读 - 读到其它事务未提交的数据（最新的版本）
- 错误现象：有脏读、不可重复读、幻读现象
提交读（RC） - 读到其它事务已提交的数据（最新已提交的版本）
- 错误现象：有不可重复读现象
- 使用场景：希望看到最新的有效值
可重复读（RR） - 在事务范围内，多次读能够保证一致性（快照建立时最新已提交版本）
- 错误现象：有幻读现象，可以用加锁避免
- 使用场景：事务内要求更强的一致性，但看到的未必是最新的有效值
串行读 - 在事务范围内，仅有读读可以并发，读写或写写会阻塞其它事务，
- 这种办法保证更强的一致性
- 错误现象：无

脏读现象

脏读：read uncommitted（未提交读）

tx2 开启事务，修改 balance 的值为 2000，此时并没有提交事务，tx1 去读取，发现是 2000，当 tx2 发生回滚的时候，tx1 读取到的 2000 就是无效的

不可重复读

不可重复读：read committed（提交读）

tx1 一次事务内，多次查询的数据不一致

幻读

幻读：repeatable read（可重复读）

tx1 实现的是可重复读，会在某一时刻创建快照，当 tx2 进行数据插入的时候，tx1 查询的时候并不能发现，此时插入同一主键数据就会报错
for update 进行加锁

使用 for update 避免幻读现象

串行读

2. 快照读与当前读

当前读，即读取最新提交的数据
- select … for update
- insert、update、delete，都会按最新提交的数据进行操作
快照读，读取某一个快照建立时（可以理解为某一时间点）的数据
快照读主要体现在 select 时，不同隔离级别下，select 的行为不同
- 在 Serializable 隔离级别下 - 普通 select 也变成当前读
- 在 RC 隔离级别下 - 每次 select 都会建立新的快照
- 在 RR 隔离级别下
  - ① 事务启动后，首次 select 会建立快照
  - ② 如果事务启动选择了 with consistent snapshot，事务启动时就建立快照
  - ③ 基于旧数据的修改操作，会重新建立快照

RR 下，快照建立时机 – 第一次 select 时

RR 下，快照建立时机 – 事务启动时

RR 下，快照建立时机 – 修改数据时

快照读与当前读 – 小结

当前读，即读取最新提交的数据，查询时需要加锁
快照读，读取某一个快照建立时的数据，无需加锁，读取的是历史数据（原理是回滚段）

3. MySQL 存储引擎

InnoDB

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

MyISAM

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

不同存储引擎索引区别

InnoDB
- 聚簇索引：主键值作为索引数据，叶子节点还包含了所有字段数据
- 二级索引：被索引的字段值作为索引数据，叶子节点还包含了主键值

MyISAM
- 被索引字段值作为索引数据，叶子节点还包含了该记录数据页地址

4. 为什么 MySQL 采用 B+ 树索引

哈希索引
- 理想时间复杂度为 O(1)
- 适用场景：适用于等值查询的场景，内存数据的索引
- 典型实现：Redis，MySQL 的 memory 引擎
平衡二叉树索引
- 查询和更新的时间复杂度都是 O(log2(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
- 适用场景：不仅支持等值查询也支持范围查询

BTree vs B+Tree

BTree key 及 value 在每个节点上，无论叶子还是非叶子节点，而 B+Tree 普通节点只存 key，叶子节点才存储 key 和 value
B+Tree 叶子节点用链表连接，可以方便范围查询及全表遍历
无论 BTree 还是 B+Tree，每个叶子节点到根节点距离都相同，B+Tree 必须到达叶子节点才能找到 value

B+Tree 新增 key（以 5 阶为例）

B+Tree 查询 key

B+Tree 删除 key

5. 命中索引要注意什么？

索引基本操作

查询索引：show index from big_person;

实现索引的创建：create index first_idx on big_person(first_name);

实现索引的删除：alter table big_person drop index first_idx;

explain 用于显示 mysql 是如何优化这条语句的

索引用于排序时的例子

多列排序需要用组合索引
- select from big_person order by last_name, first_name limit 10;
- create index last_first_idx on big_person(last_name,first_name);
多列排序需要遵循最左前缀原则
- 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; （不符合）
- 创建组合索引的时候，如：create index last_first_idx on big_person(last_name,first_name)，last_name 放在前面，则查询的时候 last_name 也应该放在最前面
多列排序升降序需要一致
- 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_nameasc limit 10; （不符合）

索引用于 where 筛选例子

模糊查询需要遵循字符串最左前缀原则
- 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;（不符合）
组合索引需要遵循最左前缀原则
- 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='中西区'; （不符合）
- 如果查询条件中出现多个条件（没有全部出现），必须按照创建索引的顺序出现，并且不能断，否则会出现索引下推
函数及计算问题
- 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); （符合）
- 列上用到函数，索引会失效；函数用到值上，不会失效；
隐式类型转换问题
- create index phone_idx on big_person(phone);
- explain SELECT * FROM big_person WHERE phone = 13000013934; （不符合）
- explain SELECT * FROM big_person WHERE phone = '13000013934'; （符合）
- 假设列是字符串，而值是整型，就会涉及到隐式传参

索引条件下推

哪些条件能利用索引
- 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 条 SQL 中仅有 province 条件能够利用索引，在引擎层执行
- 但 county 条件仍然要交给服务层处理
- 在 5.6 之前，服务层需要判断所有记录的 county 条件，性能非常低
- 5.6 以后，引擎层会先根据 province 条件过滤，满足条件的记录才在服务层处理 county 条件
索引条件下推
- SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
- SET optimizer_switch = 'index_condition_pushdown=off';

6. 执行 SQL 语句 select * from user where id = 1 时发生了什么

连接器：负责建立连接、检查权限、连接超时时间由 wait_timeout 控制，默认 8 小时
查询缓存：会将 SQL 和查询结果以键值对方式进行缓存，修改操作会以表单位导致缓存失效
分析器：词法、语法分析
优化器：决定用哪个索引，决定表的连接顺序等
执行器：根据存储引擎类型，调用存储引擎接口
存储引擎：数据的读写接口，索引、表都在此层实现

7. undo log 与 redo log

undo log 的作用有两个：

回滚数据，以行为单位，记录数据每次的变更，一行记录有多个版本并存
多版本并发控制，即快照读（也称为一致性读），让查询操作可以去访问历史版本

redo log 的作用主要是实现 ACID 中的持久性，保证提交的数据不丢失

它记录了事务提交的变更操作，服务器意外宕机重启时，利用 redo log 进行回放，重新执行已提交的变更操作
事务提交时，首先将变更写入 redo log，事务就视为成功。至于数据页（表、索引）上的变更，可以放在后面慢慢做
- 数据页上的变更宕机丢失也没事，因为 redo log 里已经记录了
- 数据页在磁盘上位置随机，写入速度慢，redo log 的写入是顺序的速度快
它由两部分组成，内存中的 redo log buffer，磁盘上的 redo log file
- redo log file 由一组文件组成，当写满了会循环覆盖较旧的日志，这意味着不能无限依赖 redo log，更早的数据恢复需要 binlog
- buffer 和 file 两部分组成意味着，写入了文件才真正安全，同步策略由下面的参数控制
- innodb_flush_log_at_trx_commit
  - 每隔 1s 将日志 write and flush 到磁盘
  - 每次事务提交将日志 write and flush（默认值）
  - 每次事务提交将日志 write，每隔 1s flush 到磁盘，意味着 write 意味着写入操作系统缓存，如果 MySQL 挂了，而操作系统没挂，那么数据不会丢失

8. 你对 MySQL 的锁了解吗？

全局锁
表级锁
1. 表锁
2. MDL 元数据锁
3. IS 与 IX
行级锁
1. S 与 X
2. 记录锁、间隙锁、临键锁

全局锁

用作全量备份时，保证表与表之间的数据一致性

flush tables with read lock;

使用全局读锁锁定所有数据库的所有表。这时会阻塞其它所有 DML 以及 DDL 操作，这样可以避免备份过程中的数据不一致。接下来可以执行备份，最后用 unlock tables 来解锁

但这属于比较重的操作，可以使用 --single-transaction 参数来完成不加锁的一致性备份（仅针对
InnoDB 引擎的表）

mysqldump --single-transaction -uroot -p test > 1.sql

表级锁（InnoDB）

表锁
- 语法：加锁 lock tables 表名 read/write，解锁 unlock tables
- 缺点：粒度较粗，在 InnoDB 引擎很少使用
元数据锁，即 metadata-lock（MDL），主要是为了避免 DML（增删改）与 DDL（表修改）冲突
- 加元数据锁的几种情况
  - lock tables read/write，类型为 SHARED_READ_ONLY 和 SHARED_NO_READ_WRITE
  - alter table，类型为 EXCLUSIVE，与其它 MDL 都互斥
  - select，select … lock in share mode，类型为 SHARED_READ
  - insert，update，delete，select for update，类型为 SHARED_WRITE
- DML 的元数据锁之间不互斥
  - select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
IS（意向共享）与 IX（意向排他），主要是避免 DML 与表锁冲突
- DML 主要目的是加行锁，为了让表锁不用检查每行数据是否加锁，加意向锁（表级）来减少表锁的判断，意向锁之间不会互斥
- 由 DML 语句添加，例如 select … lock in share mode 会加 IS 锁，insert，update，delete， select … for update 会加 IX 锁
- select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;