mysql原理

参考：
极客时间-Mysql实战45讲
小林coding
MySQL日志、事务原理
深入解读MySQL InnoDB存储引擎Update语句执行过程

一条sql执行过程

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL8.0已删除该模块；
解析SQL，通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行SQL：执行SQL共有三个阶段：
预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

innodb中行记录格式

变长字段长度列表：varchar类型的数据所占字节数
NULL值列表：用一位表示对应列值是否为空
记录头信息：包括记录是否删除，下一条记录等信息
row_id为没有指定主键时自动添加
trx_id为事务版本号
poll_ptr为上一个版本的指针，为了回滚
数据采用紧凑的方式组织，null值不存储，如果有溢出，再指向一个溢出页存放

页数据损坏怎么解决doublewrite buffer

自增id
自增主键值保存在（针对innodb）旧版在内存中，每次重启自动计算得到，新版保存在redolog
自增主键修改策略：每次插入数据如没有指定则自动指定，如果自己指定，则按指定，如果指定的小于当前的自增主键，不变，如果大于，则自增主键变为这个值加1
自增主键不连续出现的原因：
唯一值冲突导致的失败，但自增主键也增加了
可能事务回滚
可能一次申请了多个自增键

自增id与row_id不同：row_id是innodb的隐藏标识符，不可见
表的自增id达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突
的错误。
row_id达到上限后，则会归0再重新递增，如果出现相同的row_id，后写的数据会覆盖之前
的数据。

索引分类

按数据结构分类：B+树索引、哈希索引、全文索引（mysql中没有hash索引）
按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
按字段个数分类：单列索引、联合索引。

主键索引：
如果有主键，默认使用主键作为聚簇索引的索引键
如果没有主键，选择第一个不包含NULL值的唯一列作为聚簇索引的索引键
如果都没有，innodb自动生成一个隐式自增id列作为聚簇索引的索引键，即行记录格式中的row_id

主键索引与二级索引区别：
主键索引一般是聚簇索引，聚簇索引的叶子节点存放的是实际数据
二级索引存放的是主键值，而不是实际数据，需要回表，根据主键值再得到完整的数据
如果使用二级索引直接能查询到结果，称为覆盖索引，不需要回表。覆盖索引（联合索引的一种）：由于非主键索引查询后还得再查询主键索引才能得到所有数据，可以建立联合索引，查询一遍索引，就可以得到另外的字段的值。覆盖索引是指，索引上的信息足够满足查询请求，不需要再覆回到主键索引上去取数据

普通索引与唯一索引与change buffer机制
change buffer机制指在修改数据时不把数据读入内存，只是记录一下修改的操作，之后再merge(修改数据)
唯一索引在插入时需要保证唯一，所以要读入数据，判断是否能插入
普通索引在插入时不用保证唯一，只需记录插入的操作，但是如果之后要立马读取，就需要从磁盘读入数据并merge

使用联合索引时，存在最左匹配原则，即按照最左优先的方式进行索引的匹配。
在使用联合索引进行查询的时候，如果不遵循最左匹配原则，联合索引会失效，一些失效的情况：
在遇到范围查询（如 >、<）的时候，就会停止匹配，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。对于>=、<=、BETWEEN、like前缀匹配的范围查询，不会停止匹配

如果索引不能匹配
只能回表，到主键索引上找出数据行，再进行匹配过滤。
但是可以引入的索引下推，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率
select * from t_user where age > 20 and reward = 100000;
如果age与reward有联合索引
联合索引当遇到范围查询 (>、<) 就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引
如果不使用索引下推，则查询age后根据id再回表查询，之后判断reward是否=100000
如果使用索引下推，则查询age后，不回表，直接就在存储层判断索引包含的列reward是否=100000
虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作

索引优化：
注意建立联合索引时的字段顺序，要把区分度大的字段排在前面
前缀索引优化：减小索引字段大小，增加一个索引页中存储的索引值，提高索引的查询速度
覆盖索引优化：不需要再回表，减少了大量的I/O操作
主键索引最好是自增的：顺序写比随机写效率高，较少页分裂，索引数据移动

防止索引失效：
使用左或者左右模糊匹配的时，即like %xx 或者 like %xx%
在查询条件中对索引列做了计算、函数、类型转换操作（函数操作也包括隐式类型转换，编码转换等）时
联合索引没能遵循最左匹配原则
在WHERE子句中，如果在OR前的条件列是索引列，而OR后的条件列不是索引列，索引会失效
如tradeid为字符串类型，字符串与数字比较时，字符串会隐式转换为数字再比较，导致索引失效
select * from tradelog where tradeid=110717;

B+树与一些数据结构对比：

1. B+树与B树
   B+树只在叶子节点存储数据，B树非叶子节点也要存储数据，所以B+树的单个节点的数据量更小，在相同的磁盘I/O次数下，就能查询更多的节点
   B+Tree叶子节点采用链表连接，适合基于范围的顺序查找
2. B+树与二叉树
   即使数据很大，B+树的高度依然维持在3_{4层左右，也就是说一次数据查询操作只需要做3}4次的磁盘I/O操作
   而二叉树的每个父节点的子节点个数只能是2个，比B+树高出不少
   二叉树在顺序插入时可能退化为链表
3. B+树与二叉平衡树
   无论平衡还是自平衡的二叉树（红黑树）虽然解决了二叉树可能退化为链表的问题，但依然高度很高
   且平衡操作（旋转等）复杂耗时
4. B+树与哈希表
   哈希表在做等值查询的时候效率很快，但不适合做范围查询
5. B+树与跳表
   即使数据很大，B+树的高度依然维持在3~4层左右，但跳表需要几十层，所以B+树的读性能会比跳表要好
   那为什么redis使用跳表，因为redis读写全在内存里进行操作，不涉及磁盘IO，不关心磁盘读性能，同时跳表实现简单，相比B+树少了树分裂合并的开销，写方面更优
   所以读多写少B+树，写多读少跳表

在在mysql的innodb下效率排名：
count() = count(1) > count(主键字段) > count(普通字段)
count()也是转为count(0)来计算
count(1) count(0)都是查询每条记录，看记录中1（count(0)是 0）在记录中是否为空（当然不为空，所以计数直接加一），在别的存储引擎中可能直接返回，因为其表的元数据记录有这个总的值，但innodb有mvcc机制，应该返回多少行不确定（如果非要优化，可以新建一个新表专门维护）
count(主键字段) 由于要读取字段内容，效率慢一点，如果有二级索引优先选二级索引，其次主键索引（因为二级索引小，对内存友好）
count(普通字段)没有索引，只能全表遍历

order by流程：
通过索引找到数据id
通过id找到数据，将数据需要输出的字段放入缓存
按照指定字段对缓存中所有数据排序（内排序或外排序）

id在缓存不足时使用，只将要排序的字段和id放入缓存，排序后再根据id取出要输出的所有数据

优化：建立覆盖索引，不用再排序

临时表

使用union查询，order by等可能使用临时表
临时表只能被创建它的会话session访问
用于复杂查询的优化过程
比如跨库查询，如果数据在不同实例中，如果将所有数据查到再做处理，对内存等压力大，可以在一个实例上建临时表，把查到的数据存入表中再做处理
group by优化
创建一个关联的有序索引（因为一般的group by的列是无序的，需要临时表记录统计结果），只要挨个扫描统计就行
直接排序，保存在一个磁盘数组中，再统计（数据太大，内存不够用，不适用于创建索引）

事务

当多个事务执行时可能出现脏读，不可重复读，幻读
为了解决这些问题，产生隔离级别的概念：
串行化：对同一数据事务获取锁后才能操作
可重复读：事务执行过程中看到的数据是一致的
读提交：事务提交后，它的更改才能被别的事务看到
读未提交：事务没提交时，它的更改可以被别的事务看到

InnoDB如何保证事务的这四个特性：
持久性是通过redo log来保证的；
原子性是通过undo log来保证的；
隔离性是通过MVCC和锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证

MySQL虽然支持4种隔离级别，但是标准中规定的各隔离级别允许发生的现象有些不同。MySQL在可重复读隔离级别下，可以很大程度上避免幻读现象的发生，但不是彻底避免

解决的方案有两种：
针对快照读（普通select语句），通过MVCC方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select ... for update等语句），通过next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题

但是依然会有幻读
MySQL里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。
如事务A快照读后，事务B又插入数据，事务A再当前读，就会出现幻读

这四种隔离级别具体是如何实现的呢？
读未提交隔离级别的事务，因为可以读到未提交事务修改的数据，所以直接读取最新的数据
对于串行化隔离级别的事务，通过加读写锁的方式来避免并行访问
对于读提交和可重复读隔离级别的事务来，它们是通过Read View来实现的，它们的区别在于创建Read View的时机不同，Read View可看做一个数据快照，就像相机拍照那样，定格某一时刻的风景。读提交隔离级别是在每个语句执行前都会重新生成一个Read View，而可重复读隔离级别是启动事务时生成一个Read View，然后整个事务期间都在用这个Read View

MVCC如何实现：
通过版本链来控制并发事务访问同一个记录，一条数据有多个版本，版本并不是真实存在的，而是通过undolog记录，需要什么版本可以通过它回退得到其它版本
对于使用InnoDB引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：
trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务id记录在 trx_id 隐藏列里
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到undo日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。
一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：
如果记录的trx_id值小于Read View中的min_trx_id值，表示这个版本的记录是在创建Read View前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的trx_id值大于等于Read View中的max_trx_id值，表示这个版本的记录是在创建 Read View后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的trx_id值在Read View的min_trx_id和max_trx_id之间，需要判断trx_id是否在 m_ids列表中：
如果记录的trx_id在m_ids列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
如果记录的trx_id不在m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

对于可重复读隔离级别，在事务启动时会创建一个视图（由当前正在活动的事务ID组成）和系统中最高事务+1（称作高水位）
通过这个视图判断读取哪一个版本：

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。
   但是更新的时候（为了不覆盖其它事务已经发生的修改），需要加锁读取当前最新的版本的数据

锁

全局锁：用于数据库的备份，但是会阻塞其它服务（如果存储引擎支持MVCC，可以启动一个事务，得到一个一致性视图后，再做备份）

表级锁

• 表锁
• 元数据锁（MDL）：对数据库表进行操作时，会自动给这个表加上MDL，MDL是在事务提交后才会释放
• 意向锁：对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁；对某些记录加上独占锁之前，需要先在表级别加上一个意向独占锁
  意向锁的目的是为了快速判断表里是否有记录被加锁，如果没有意向锁，那么加独占表锁时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢

行级锁
Record Lock记录锁：也就是仅仅把一条记录锁上；
Gap Lock间隙锁：锁定一个范围，但是不包含记录本身；（间隙锁是可以共存的，间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁）
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

普通的select是不会加行级锁的，普通的select语句是利用MVCC实现一致性读，是无锁的。不过，select也可以对记录加共享锁和独占锁

加锁是加在索引上而不是行上，查询语句如果没有走索引，会全表扫描，把整个表锁住

日志

undo log（回滚日志）：是Innodb存储引擎生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
redo log（重做日志）：是Innodb存储引擎生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog（归档日志）：是Server层生成的日志，主要用于数据备份和主从复制

undolog是逻辑日志，和read view实现MVCC
redolog是物理日志，binlog是逻辑日志，因此redolog是崩溃安全的，而binlog不是（如果已经记录了binlog，但是操作并没有执行，数据库崩溃，之后回放binlog会造成数据不一致）
redolog采用循环写，binlog采用追加写
redolog采用二阶段提交，保证两个日志一致：在更改内存的数据后，redolog写入磁盘，为prepare状态。之后binlog写入磁盘。最后提交事务，redolog变为commit状态

binlog记录的模式：
statement:直接记录语句
优：占用内存小，如插入10万条数据，一条语句就可记录
缺：造成主备不一致，如备在执行binlog语句的时候可能选择不同的索引，对不同数据操作
row:记录具体位置修改的数据
优：主备一致，便于数据恢复
缺：一条语句可能对应多处修改，占用内存
mixed:两种混合
造成主备不一致的用row，其它的用statement

误删除恢复
删除一行数据
删除表
删除一个数据库节点

主备

主备一致：mysql使用binlog保持主备一致

主备结构
M-S：同时一主一备
M-M：互为主备库

造成主备延迟的原因：
备库硬件差
备库还提供只读查询，占用资源
执行大事务

主备切换策略：
可靠性优先：先等到备库延迟值小于一个阈值将主库设为只读，当延迟值为0，再完成切换
优：可靠
缺：造成一段时间不可用，尤其当主突然下线（不是主动切换时），这时要等备库一段时间
可用性优先：不等主备数据同步，直接切换
优：可用
缺：造成数据不一致

一主多从下基于GTID的主备切换
GTID是一个全局唯一的编号，由server_uuid+transaction_id（已经提交的事务数量）组成的

读写分离下，处理过期读方案：
强制走主库：将查询分类，对于需要最新数据的查询，强制其走主库（但不适用于只要最新的场景）
判断主备无延迟：可以等待备库延迟为0，或者对比同步位点，或者对比已经收到的GTID集合和已经执行的GTID集合是否相同来确定（但不适用于主已经返回完成，但备还没收到binlog的场景）
半同步：当备也同步完成后，主才返回完成（但不适用于一主多从场景，且存在大延迟）
等待备库也同步到主库的位点或者GTID，再进行查询（期间如果等待超时，可以放弃，也可以直接查主库）

如何判断数据库出现问题
select 1：但是当并发查询数量已经达到上限，语句还能成功执行
查表判断：通过新建一个表，定期查这个表判断，但是即使磁盘空间占用率满了，依然能成功执行
更新表判断：定期更新这个表来判断，但是即使数据库IO利用率已经满了，语句依然可能成功执行，但此时数据库很慢，可能很长时间才能检测出来
内部统计：数据库自己维护查询执行快慢的统计信息表