文章详细介绍了MySQL的体系架构，包括C/S模型、查询执行流程，以及从缓存到优化器的各个阶段。在索引部分，讨论了B+树的优势、聚簇与非聚簇索引的区别，以及覆盖索引和回表的概念。事务部分讲解了ACID特性、事务隔离级别和InnoDB的实现机制。还涵盖了锁的类型和作用，以及MySQL的binlog、redolog和undolog的功能。最后提到了数据库优化策略，如慢查询分析和主从同步原理。
| 修订记录 | 时间 |
|---|---|
| 首次发布 | 2023.06 |
一、体系架构
1.1 讲一讲MySQL的体系结构 / 查询语句是怎么执行的？
- 首先，MySQL是一个C/S的架构，需要从client到server端进行连接，MySQL本身是一个单进程多线程的架构。
- 连接完成后，会先去查询缓存，查询缓存在MySQL5.5版本之前是有的，之后默认关闭，在8.0版本已经没有了。
- 缓存之后，会进入解析器模块，做了词法解析和语法解析两件事情。MySQL需要识别一条语句的语法，判断它是否正确，也就是识别字符串分别是什么，代表什么。解析之后会有一颗解析树，解析树会经过预处理器，它会把SQL中的所有数据查一遍，判定有没有错误，如果没有错误会进入到新解析树。
- 进入新解析树之后会进入优化器，优化器会生成执行计划，然后小型优化SQL。比如在表里面有多个索引的时候，优化器决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；或者是去除SQL语句中的废话，例如WHERE 1=1。
- 最终确认执行计划之后，会查询执行引擎，调用对应的执行引擎查询数据，并把结果返回给客户端。
二、索引、事务和锁
2.1 索引
- 索引帮助提高MySQL的查询效率，减少I/O，不需要全表扫描。
- 通过索引对数据进行排序，降低了数据排序的开销。
1. B树和B+树的区别？为什么索引使用B+树？
B树和B+树的区别？
- B树中所有节点都会存储数据；B+树只在叶子节点存储数据，所以B+树的查询效率更稳定。
- 因此更适合区间查询和外部存储。
为什么索引使用B+树？
InnoDB默认索引使用B+树。
- B+树的阶数更多，是多叉树。路径更短。（相比红黑树）
- B+树磁盘读写代价更低，因为它的非叶子节点至存储指针，数据都在叶子节点。（相比B树）
- B+树可以很好地支持区间查询，因为它的叶子节点是双向链表。
2 什么是聚簇索引？什么是非聚簇索引（二级索引）？
- 聚簇索引是数据与索引放在一起，B+树的叶子节点中保存了整行的数据，有且只有一个。
- 非聚簇索引（二级索引）是数据与索引分开存储，B+树的叶子节点只保存了对应的主键，可以有多个，一般我们额外加的就是非聚簇索引。
聚簇索引一般是主键索引，无主键则是一个唯一索引，两个都没有的话InnoDB会自动生成一个rowid作为聚簇索引。
3. 什么是覆盖索引？什么是回表？
覆盖索引是查询时使用了索引，并且这个索引可以满足所有需要返回的列，是不需要回表的情况。
回表是通过非聚簇索引查询到对应的主键值，用主键值在聚簇索引中查询整行数据的过程。
4. 什么时候索引会失效？
- 索引列上使用函数时会失效，比如COUNT、SUM、AVG。
- 扫描全表比使用索引快时，则不使用索引。
- 使用LIKE查询以%开头时索引失效。
- 如果列类型是字符串，则查询时需要加引号，否则可能发生隐式类型转换。
- 联合索引非最左字段，不会使用索引。
- 范围查询条件右边的条件索引会失效。
5. 什么是哈希索引？
在InnoDB中不支持哈希索引，在MyISAM的只读场景下使用哈希索引。
哈希索引比B+树索引快，但无法范围查询。
6. 怎么选用字段建立索引？
先回答实际工作中用到了什么索引（主键索引、唯一索引、根据业务创建的复合索引等）。
- 数据量较大且查询较频繁的表建立索引（单表超过10w条）。
- 选择常被WHERE、ORDER BY、GROUP BY操作的字段。
- 选择区分度大，及离散度接近于1的字段建立索引。离散度是COUNT(DISTINCT a) / COUNT(a)。
- 多使用联合索引，可以覆盖索引，避免回表。
- 控制索引数量，维护索引有代价。
7. 什么是最左匹配？
对于联合索引来说，以最左边的索引优先，以最左字段为起点的连续索引都可以匹配上。
2.2 事务
1. 什么是事务？事务有什么特性？
事务：事务是一组操作的集合。
特性：ACID（举例：转账）
- 原子性 Atomicity：事务是不可分割的最小单元，要么全部成功，要么全部失败。
- 一致性 Consistency：事物完成时所有的数据状态都保持一致。
- 隔离性 Isolation：一个事务尽可能不受其他事务影响，多个事务互不干涉。
- 持久性 Durablity：事务一旦提交或回滚，对数据库中的改变是永久的。
2. 事务的特性是怎么实现的？
以InnoDB为例。
- 原子性：利用的是InnoDB的undo log。它记录了回滚所需要的信息，例如执行了insert操作，回滚时会依据主键执行delete操作。
- 一致性：可以从数据库层面和应用层面两个方面来保证。数据库层面通过原子性、隔离性和持久性来保持一致性，ACID特性中一致性是目的。应用层面可以通过代码判断获得的数据是否合理，从而决定回滚或提交。
- 持久性：利用的是InnoDB的redo log。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候会讲redo log日志进行刷盘，当数据库宕机重启时，会将redo log中的内容恢复到数据库中。
- 隔离性：使用锁和MVCC多版本并发控制来保证隔离性。一个事务的写操作对另一个事务的写操作的影响由锁机制来保证隔离性；一个事务的写操作对另一个事务的读操作的影响由MVCC来保证隔离性。
3. MySQL的bin log、redo log和undo log分别有什么作用？
- bin log：bin log是MySQL Server层的日志，是二进制的日志文件，记录了所有的DDL和DML语句，可以用来做数据恢复和主从复制。
数据恢复区别于redolog的崩溃恢复，bin log可以恢复删库跑路，redo log可以恢复断电重启等情况。 - redo log：重做日志，记录事务提交时数据页的修改，在刷新脏页到磁盘错误时恢复数据，用来实现事务的持久性。redo log日志采用的是预写日志，是追加写（append），速度很快。redo log是物理日志，记录具体数据。
- undo log：回滚日志，用于记录数据被修改前的信息，用于回滚和MVCC多版本并发控制，实现了事务的原子性和一致性。redo log是逻辑日志，记录对应的操作。例如当delete一行数据时，undo log中会记录一条insert。
4. 什么是两阶段提交？如何保证宕机时数据一致性？
TODO
5. 事务的隔离级别和解决的问题
默认隔离级别是可重复读。设置不同的事务隔离级别可以解决。事务隔离级别越高，数据越安全，但性能越低。
四种隔离级别怎么实现的：隔离级别的实现
6. 什么是脏读？
在同一个事务A中，由于其他事务B修改了数据且没有提交，导致了这个事务A两次读取的数据不一致，叫做脏读。即读到了其他事务未提交的数据。
7. 什么是不可重复读？
在同一个事务A中，由于其他事务B修改了数据且提交，导致这个事务A两次读取的数据不一致，叫做不可重复读。
8. 什么是幻读？
事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。
MySQL中的可重复读，在INSERT\DELETE\UPDATE…FOR UPDATE… IN SHARE MODE这几个会获取到排他锁的情况下，加的是next-key lock，是当前行锁加上间隙锁，所以不会插入或删除行，不会出现幻读。
9. 如何保证事务的隔离性？
由锁和MVCC共同来保证。一个事务的写操作对另一个事务的写操作的影响由锁机制来保证隔离性；一个事务的写操作对另一个事务的读操作的影响由MVCC来保证隔离性。
- 锁：insert、update和delete操作会自动获取排他锁。
如果一个事务获取了某一行数据的排他锁，那么其他事物无法获取这一行的其他锁。 - MVCC 多版本并发控制
10. MVCC如何实现多版本并发控制？如何解决读写冲突的？
MVCC Multi-Version Concurrency Control，维护一个数据的多个版本，使读写没有冲突。
- 隐藏字段：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
- undo log版本链：对同一条记录的多次修改，会有一个记录版本的链表，头部是最新的旧记录，尾部是最老的旧记录。
- 读视图 readview：读视图分为当前读和快照读，读已提交和可重复读这两个隔离级别都是通过快照读来实现的。
读已提交是在事务每一次执行快照读时生成ReadView。
可重复读是在事务第一次执行快照读是生成ReadView。
2.3 讲一讲MySQL的锁
分类
乐观锁 & 悲观锁
TODO
全局锁、表锁、页锁、行锁
1. 全局锁
数据备份
2. 表级锁
- 表锁
- 元数据锁：维护表结构等元数据的数据一致性，避免DML和DDL冲突。
- 意向锁
- 意向共享锁：select … lock in share mode
- 意象排他锁：insert、update、delete、select … for update
3. 行级锁
InnoDB中行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁，没有索引则升级为表锁。InnoDB默认的是可重复读隔离级别，使用临键锁进行搜索和索引扫描，防止幻读。
- 行锁 Record Lock：锁定单个行记录的锁，防止其他事务进行update和delete。读已提交、可重复读的隔离级别下都支持。
- 共享锁：共享锁和共享锁兼容，共享锁和排他锁互斥。
- 排他锁
- 间隙锁 Gap Lock：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。可重复读隔离级别支持。
- 临键锁 Next-Key Lock：行锁和间隙锁的组合，锁住数据和数据前的间隙。可重复读隔离级别支持。
共享锁（S）&排他锁（X）和意向锁
TODO
三、MySQL优化
3.1 慢查询
1. 如何定位慢查询？
- 使用监控平台查看接口响应时间等数据，或者测试时发现某个接口非常慢。
- 在MySQL中开启慢查询日志，配置超过2s（可自定义）的查询为慢查询，可以在localhost-slow.log中查看慢查询的情况。
2. 如何分析慢SQL？
一般出现在聚合查询、多表查询、表数据量大和深度分页查询的情况下。
可以使用EXPLAIN或DESC获取MySQL怎么执行SELECT的。
EXPLAIN SELECT * FROM table WHERE 'xx' = "abc"
- 先查看key（实际命中的索引）和key_len（索引命中多少行）检查查询是否使用了对应的索引，索引有没有失效。
- 再查看type字段看是否有优化空间，是否存在全索引扫描或全盘扫描。
- 根据extra建议看是否有回表，可以添加索引或修改返回字段来优化。
3. MySQL超大分页怎么处理？
超大分页是指在数据量很大的情况下需要分页返回排序后的结果（使用limit）。
可以通过覆盖索引+子查询的方法来优化。
SELECT * FROM table t,
(SELECT id FROM table ORDER BY id LIMIT 900000, 10) sub
WHERE t.id = sub.id;
4. MySQL优化经验
- 表的设计
- 数据类型的优化 – varchar长度，数字选用int、bigint等
- 例：邮政编码、手机号等字符串不用过长
- 索引优化
- 建立合适的索引
- 避免索引失效
- SQL语句的优化
- 避免使用SELECT *
- 避免WHERE子句中的计算
- 尽量使用INNER JOIN而不是LEFT JOIN
- 主从复制、读写分离架构
- 不让写操作影响读操作
- 分库分表
3.2 数据库集群
1. 主从同步原理
MySQL的主从同步的核心是bin log，它记录了DDL和DML。
- 主库提交事务时，会将变更记录在bin log中。
- 从库的IO Thread读取主库的bin log，写到从库的中继日志relay log中。
- 从库的SQL Thread读取relay log，执行变更的操作，达到了主从一致。
2. 分库分表
问：分库分表怎么分？每个表分多少数据？最大容纳多少数据？
计算得出。以三层B+树为例，假设key为12字节。
16k / 12byte = 1300
最后一层按实际每一行存储的数据大小来计算，假设为1600字节。
16k / 1600byte = 10
则可以存1300 * 1300 * 10 = 16900000条。
四、数据库
4.1 数据库范式
三大范式：
- 第一范式 - 无重复列：强调列的原子性，每个字段不可再拆分。
- 第二范式 - 完全依赖：一是表必须有一个主键，而是没有包涵在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。（主键依赖）
- 第三范式 - 传递依赖：非主键列必须直接依赖主键，不能存在传递依赖。（外键约束）比如学生表中要增加系主任的字段，会有大量冗余，可增加一个系编号的字段，和系信息表做关联。**
1995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
