重学| 面试复习（九）MySql

简述下MySql体系架构

网络连接层

• 客户端连接器

服务层

• 连接池
• 系统管理和控制工具
• SQL接口
• 解析器
• 查询优化器
• 缓存

存储引擎层

系统文件层

• 日志文件
• 配置文件
• 数据文件
• pid 文件
• socket 文件

从客户端建立连接到查询执行引擎负责执行 SQL 语句是如何执行的

1.建立连接,通过客户端/服务器通信协议与MySQL建立连接
2.查询缓存,这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。
3.解析器将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。
4.查询优化器根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）
5.查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

MySql存储引擎区别对比

事务和外键

• InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作
• MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

锁机制

• InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。
• MyISAM支持表级锁，锁定整张表。

索引结构

• InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。
• MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。

并发处理能力

• MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
• InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发

存储文件

• InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；
• MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制是256TB。

Undo Log，Redo Log和Binlog区别和作用

Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。
Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景(1.主从复制 2.数据恢复)

详情PDF/p22

事务是如何提交的？事务提交先写binlog还是redo log？如何保证这两部分的日志做到顺序一致性？

为保证binlog和InnoDB redo日志的一致性MySQL引入二阶段提交2PC

1.准备阶段（Storage Engine（InnoDB） Transaction Prepare Phase）
此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。然后调用prepare方法完成第一阶段，papare方法实际上什么也没做，将事务状态设为TRX_PREPARED，并将redo log刷磁盘。
2.提交阶段(Storage Engine（InnoDB）Commit Phase)

• 2.1 记录协调者日志，即Binlog日志。
  如果事务涉及的所有存储引擎的prepare都执行成功，则调用TC_LOG_BINLOG::log_xid方法将SQL语句写到binlog（write()将binary log内存日志数据写入文件系统缓存，fsync()将binary log文件系统缓存日志数据永久写入磁盘）。此时，事务已经铁定要提交了。否则，调用ha_rollback_trans方法回滚事务，而SQL语句实际上也不会写到binlog。
• 2.2 告诉引擎做commit。
  最后，调用引擎的commit完成事务的提交。会清除undo信息，刷redo日志，将事务设为TRX_NOT_STARTED状态。
  
  

PS：记录Binlog是在InnoDB引擎Prepare（即Redo Log写入磁盘）之后，这点至关重要。

索引类型有哪些

从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

索引的数据结构

B+Tree，hash，t-tree，r-tree

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

B-Tree结构

• 索引值和data数据分布在整棵树结构中
• 每个节点可以存放多个索引值及对应的data数据
• 树节点中的多个索引值从左到右升序排列

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有
命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束

B+Tree结构

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
• 叶子节点包含了所有的索引值和data数据
• 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进
行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高

详情PDF/p31

什么是回表，索引覆盖，索引下推

回表：通过索引查询主键值，然后再去聚簇索引查询记录信息

索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

索引下推：简称ICP，在Mysql5.6的版本上推出，用于优化查询。
在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

索引分析与优化

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化

详情PDF/p33

事务ACID特性

原子性，持久性，隔离性，一致性

详情PDF/p41

事务隔离级别

读未提交，读已提交，可重复读，串行化，他们能产生什么影响（脏读，幻读，不可重复读）

详情PDF/p47

锁的分类，以及原理

从操作的粒度可分为表级锁、行级锁和页级锁。

• 表级锁：每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB 等存储引擎中。
• 行级锁：每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB 存储引擎中。
• 页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般。应用在BDB 存储引擎中。

从操作的类型可分为读锁和写锁

• 读锁（S锁）：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（X锁）：排他锁，当前写操作没有完成前，它会阻断其他写锁和读锁

从操作的性能可分为乐观锁和悲观锁。

• 乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
• 悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴

详情PDF/p48

行锁原理

在InnoDB引擎中，我们可以使用行锁和表锁，其中行锁又分为共享锁和排他锁。InnoDB行锁是通过对
索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• RecordLock锁：锁定单个行记录的锁。（记录锁，RC、RR隔离级别都支持）
• GapLock锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别支持）

在RR隔离级别，InnoDB对于记录加锁行为都是先采用Next-Key Lock，但是当SQL操作含有唯一索引
时，Innodb会对Next-Key Lock进行优化，降级为RecordLock，仅锁住索引本身而非范围。

什么是MVCC

MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本
在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。

• 快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
• 当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发
  修改这条记录。（select… for update 或lock in share mode，insert/delete/update）

详情PDF/p46

主从复制的实现原理

主从复制整体分为以下三个步骤：

• 1.主库将数据库的变更操作记录到Binlog日志文件中
• 2.从库读取主库中的Binlog日志文件信息写入到从库的Relay Log中继日志中数据库拆分方式
• 3.从库读取中继日志信息在从库中进行Replay,更新从库数据信息

在上述三个过程中，涉及了Master的BinlogDump Thread和Slave的I/O Thread、SQL Thread，它们的作用如下：

• Master服务器对数据库更改操作记录在Binlog中，BinlogDump Thread接到写入请求后，读取Binlog信息推送给Slave的I/O Thread。
• Slave的I/O Thread将读取到的Binlog信息写入到本地Relay Log中。
• Slave的SQL Thread检测到Relay Log的变更请求，解析relay log中内容在从库上执行。

上述过程都是异步操作，俗称异步复制，存在数据延迟现象

数据库分片策略

基于范围分片
根据特定字段的范围进行拆分，比如用户ID、订单时间、产品价格等。例如：{[1 - 100] => Cluster A, [101 - 199] => Cluster B}
优点：新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。
缺点：数据热点分布不均，数据冷热不均匀，导致节点负荷不均。

哈希取模分片
整型的Key可直接对设备数量取模，其他类型的字段可以先计算Key的哈希值，然后再对设备数量取模。假设有n台设备，编号为0 ~ n-1，通过Hash(Key) % n就可以确定数据所在的设备编号。该模式也称为离散分片。
优点：实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况。
缺点：扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和
迁移。

一致性哈希分片
采用Hash取模的方式进行拆分，后期集群扩容需要迁移旧的数据。使用一致性Hash算法能够很大程度的避免这个问题，所以很多中间件的集群分片都会采用一致性Hash算法，一致性Hash在增加或者删除节点的时候，受到影响的数据是比较有限的，只会影响到Hash环相邻的节点，不会发生大规模的数据迁移

数据库扩容方案

1.停机扩容
2.平滑扩容

详情见PDF/p76

Mysql数据库如何调优?

mysq|数据库优化从两方面入手。
1.通过优化配置参数
如：合适的innodb池大小，取消反向解析，合理的连接数，合理的超时时长，合理的相关cache等。
2.通过操作的优化
如：合理的表结构，合理的索引，合理的查询语录(可通过分析慢查询日志找出可优化的，再通过explain去测试语句，找出可优化的点进行优化)。
如果都有优化了还有瓶颈、最后就是分表、分库、扩硬件、主从读写分离。