MySQL实战（持续更新）-CSDN博客

01 | 基础架构：一条SQL查询语句是如何执行的？

下面我给出的是 MySQL 的基本架构示意图，从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

从图中可以看出，不同的存储引擎公用一个server层。

连接器

连接命令

mysql -h$ip -P$port -u$user -p
连接保持时间
- 连接完成之后，如果没有后续的动作，这个连接就会处于空闲状态，可以在show processlist命令中看到。
- 客户端如果太长时间没有动静，连接器会主动将其断连。这个参数是wait_timeout控制的，默认时间是8小时。
- 断开之后再发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。需要客户端重新连接，然后再发送请求。
长连接占用内存问题
- 在执行过程中，连接中使用的内存是管理在连接对象里面的。这些资源只有在断连的时候才会释放。这些长连接累积下来，可能会导致内存占用越来越大，最终被系统OOM掉。
- 解决：
  - 定期断开连接。使用一段时间以后，或者在程序里面判断执行过一个比较大的操作之后，重新连接。
  - 或者可以在执行过一个较大的操作之后，执行mysql_reset_connection来重新初始化连接资源。

查询缓存

建立连接之后，进行select语句查询，就会进入查询缓存阶段。每次的查询会把结果放入缓存，下次有相同的查询会直接在缓存中获取即可，无需进入存储引擎进行查询。

但是，查询缓存这个事情是弊大于利的，所以不会建议使用。mysql8.0之后直接将该模块删除了。

为啥有弊呢？因为每次存在update操作的情况下，就会把整个缓存都清除掉，导致缓存命中率特别低。

在8.0之前的版本，可以直接将query_cache_type设置为DEMAND，这样的话，查询语句默认不使用缓存。在需要使用查询缓存的地方可以显示调用。mysql> select SQL_CACHE * from T where ID=10；

分析器

分析器先做“词法分析”，根据语法规则，判断输入的sql语句是否满足mysql语法规则。

优化器

优化器是在表中存在多个索引的情况下，决定用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

02 | 日志系统：一条SQL更新语句是如何执行的？

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

重要的日志模块：redo log

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（按我理解这个其实还是要写文件，只不过是顺序写，效率高）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

这种机制保证了即使数据库异常宕机，之前的数据也不会丢失，这个能力成为crash-safe。

补充

作用

确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
- 这个事情我是这样想的，redolog本身是为了弥补binlog的固有的缺点才做的。binlog不会实时写磁盘，特别是在事务的情况下，不commit binlog不会写入磁盘。
- redolog是实时顺序写磁盘的（当然也不是实时，但是最迟1s写入）

内容

物理格式的日志，记录的是物理数据页面的修改的信息。对比binlog的格式是具体的sql操作语句。

什么时候产生

事务或者操作开始的时候就会产生

是么时候释放

当对应事务或操作的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

重要的日志模块：binlog

redo log是innodb引擎特有的日志，而引擎层上面的server层（如果忘了mysql的架构，记得看上一篇的图示）也有自己的日志，成为binlog（归档日志）。

为啥会有两份日志嘛？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

补充

作用

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。
用于数据库的基于时间点的还原。（在异常重启的情况下，会用到redolog）

内容

简单认为就是执行过的sql语句（查询啥的就不需要了）

什么时候产生

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

redolog和binlog的不同

作用不同：redo log是保证事务的持久性的，是事务层面的，binlog作为还原的功能，是数据库层面的（当然也可以精确到事务层面的），虽然都有还原的意思，但是其保护数据的层次是不一样的。
内容不同：redo log是物理日志，是数据页面的修改之后的物理记录，binlog是逻辑日志，可以简单认为记录的就是sql语句
另外，两者日志产生的时间，可以释放的时间，在可释放的情况下清理机制，都是完全不同的。
恢复数据时候的效率，基于物理日志的redo log恢复数据的效率要高于语句逻辑日志的binlog

update的流程

这里面涉及到了两阶段提交。 MySQL通过两阶段提交过程来完成事务的一致性的，也即redo log和binlog的一致性的，理论上是先写redo log，再写binlog，两个日志都提交成功（刷入磁盘），事务才算真正的完成。

如何让数据库恢复到半个月内的任何一秒的状态？

如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

03 | 事务隔离

事务就是要保证数据库一组操作，要么全部成功，要么全部失败。在mysql中，事务是引擎层支实现的，MyISAM不支持事务，InnoDB支持事务。这也是MyISAM被InnoDB取代的重要原因之一。

隔离性与隔离级别

读未提交。一个事务还没提交，他做的变更就能被别的事务看到。
读已提交。一个事务提交之后，才能被其他事务看到。
可重复读。一个事务执行过程中，总是跟这个事务启动时看到的数据是一致的。
串行化。当出现读写冲突的时候，后访问的事务必须等待前一个事务执行完成之后，才能继续执行。

在实现上，数据库会创建一个视图，访问的时候以视图的逻辑结果为准。

在“可重复读”级别下，这个视图是在事务启动的时候创建的，整个事务存在期间都用这个视图。

在“读提交”级别下，这个视图是在每个SQL语句开始执行的时候创建的。

在“读未提交”级别下，直接返回记录上的最新值。

在“串行化”级别下，直接用加锁的方式来避免并行访问。

Oracle的默认级别是“读提交”。

Mysql的默认级别是“可重复读”。

查看当前数据库默认的事务隔离级别：

show variables like 'transaction_isolation';
复制代码

事务隔离的实现

每条update语句执行的同时，都会同时记录一条回滚日志。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从1按顺序改成了2/3/4，在回滚日志里会有类似下面的记录。

当前该值为4，不同时刻启动的事务会有不同的read-view。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。

当没有事务再需要用到这些回滚日志的时候，相应的回滚日志会被删除。

事务的启动方式

显式启动：begin或者start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，将线程的自动提交关掉。意味着执行一个语句，事务就启动了，而且不会自动提交。这个事务会一直存在，直到主动执行commit或者rollback。

查询长事务

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
复制代码

？如何避免长事务对业务的影响

从应用开发端和数据库端两个方面来看问题。

应用开发端
1. 确定auto_commit是否为1.
2. 避免出现不必要的事务，如都是select语句，完全没必要使用事务。
3. 设置合理的SET MAX_EXECUTION_TIME来控制每个语句的最长执行时间。

数据库端

监控information_schema.Innodb_trx表，设置长事务阈值，超过阈值就告警或kill。

mysql -N -uroot -p'密码' -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join 
information_schema.PROCESSLIST b 
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep' 
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
do 
#echo $C 
if [ "$C" -gt 5 ] 
then 
echo date "+%Y-%m-%d %H:%M:%S" 
echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H" 
fi 
done >> /tmp/longtransaction.txt
复制代码

使用pt-kill工具，kill长时间的异常事务。参考：www.cnblogs.com/bjx2020/p/9…

在业务开发测试阶段，输出所有的general_log，分析日志行为，提早发现。

mysql>set global general_log_file='/tmp/general.log';    #设置路径
mysql>set global general_log=on;    # 开启general log模式
复制代码

04 | 深入浅出索引（上）

索引就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型

哈希表
- 只适用于等值查询的场景，对于范围查询只能走全表扫描。
有序数组
- 等值查询和范围查询场景中的性能都非常好。
- 插入性能太差。所以适合静态存储引擎，如2017年城市人口信息。
二叉树
- 查询复杂度是O(log(N))
- 二叉树的搜索效率是很高的，但是大多数的数据库存储不会用二叉树。因为索引不止存在内存中，还要写到磁盘上。想象一棵100万节点的平衡二叉树，树高20，一次查询可能需要访问20个数据块。从磁盘随机读一个数据块就需要10ms左右的寻址时间，那么20个数据块的访问时间就会达到200ms。

InnoDB的索引

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

在InnoDB中，索引使用B+树的索引模式。B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。

左边是主键索引，右边是普通索引。主键索引的叶子节点存的是整行数据，普通索引的叶子节点存的是主键的值。

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。以上面这个图为例，如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。

而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

所以使用自增主键的重要性就体现在这里了。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

适合用业务字段直接做主键的场景
1. 只有一个索引
2. 该索引为唯一索引

总结

InnoDB使用B+树作为索引模型。因为B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
InnoDB是主键索引组织表
推荐使用自增主键

05 | 深入浅出索引（下）

如下所示的表：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
复制代码

如下所示的查询，需要执行几次树的操作，会扫描多少行：
```
select * from T where k between 3 and 5
复制代码
```
执行流程如下：
1. 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2. 再到 ID 索引树查到 ID=300 对应的 R3；
3. 在 k 索引树取下一个值 k=5，取得 ID=500；
4. 再回到 ID 索引树查到 ID=500 对应的 R4；
5. 在 k 索引树取下一个值 k=6，不满足条件，循环结束。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

覆盖索引

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查询ID的值，而ID的值本身已经在k索引树上，不需要回表。

也就是说，在这个查询里面，索引k已经覆盖了我们的查询需求，我们称为覆盖索引。

又有一个问题：获取字段和检索字段都不是主键，怎么使用覆盖索引。

答案：建立两个字段的联合索引即可。

最左前缀原则

这边有一个疑问，如果为每一种查询都设计一个索引，索引会不会太多了。这个时候可以借助最左前缀原则来复用索引。

当已经有(a,b)索引之后，就不需要单独的a索引了。

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？

根据加锁范围，MySQL里面的锁大致可以分为全局锁、表级锁和行级锁。

全局锁

顾名思义，全局锁就是对整个数据库加锁。

加锁方式

Flush tables with read lock，使得整个数据库处于只读状态。任何更新类的语句全部阻塞。
使用场景

**做全库逻辑备份。**也就是把整库每个表都select出来存成文本。
风险
- 如果在主库备份，在备份期间不能更新，业务停摆
- 如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
具体案例
- 官方自带的逻辑备份工具mysqldump，当mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。当然，MVCC只是支持InnoDB。
为什么不使用set global readonly=true的方式？
- 在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
- 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL中表级锁有两种：表锁和元数据锁MDL（meta data lock）

表锁
- 表锁的语法是 lock tables … read/write。
  
  举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
- InnoDB支持行锁，所以一般不会用到表锁。
MDL
- MDL不需要显式的使用，在对一个表操作的时候，会自动被加上。
- 作用
  - 保证读写的正确性
  - 在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
- 注意
  - MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

07 | 行锁功过：怎么减少行锁对性能的影响？

InnoDB支持行锁，MyISAM不支持行锁，这个也是被InnoDB替代的原因之一。

作用
- 行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这个时候事务B也要更新同一行，则必须等事务A更新完成之后才能进行更新。

两阶段锁

事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

建议：

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁：

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。
解决方案： 1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。 2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。
如何解决热点行更新导致的性能问题？ 1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用 2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。 3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

08 | 事务到底是隔离的还是不隔离的？

一致性视图（MVCC）

innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)
事务在启动时会拍一个快照,这个快照是基于整个库的. 基于整个库的意思就是说一个事务内,整个库的修改对于该事务都是不可见的(对于快照读的情况) 如果在事务内select t表,另外的事务执行了DDL t表,根据发生时间,要嘛锁住要嘛报错(参考第六章)
事务是如何实现的MVCC呢?
1. 每个事务都有一个事务ID,叫做transaction id(严格递增)
2. 事务在启动时,找到已提交的最大事务ID记为up_limit_id。
3. 事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里, 并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
4. 再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对, 如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据

什么是当前读

由于当前读都是先读后写,只能读当前的值,所以为当前读.会更新事务内的up_limit_id为该事务的transaction id

为什么rr能实现可重复读而rc不能,分两种情况
- 快照读的情况下，rr不能更新事务内的up_limit_id，而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复读
- 当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读

09 | 普通索引和唯一索引，应该怎么选择？

change buffer

概念

当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。
优势

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。
merge

将change buffer中的操作应用到原数据页上，得到最新结果的过程，称为merge 访问这个数据页会触发merge，系统有后台线程定期merge，在数据库正常关闭的过程中，也会执行merge
持久化

change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上，顺序写，没有性能问题。
大小配置

change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。
使用场景
- 在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。
- 对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
- 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。

change buffer、redo log和WAL的比较

redo log是WAL机制的实现方式，同change buffer的目的相同吧，都是为了减少随机读写。

例子：通过一个场景来描述两者的区别
- mysql> insert into t(id,k) values(id1,k1),(id2,k2);其中k是索引。
  
  当前的状态是k1所在的数据页在内存（InnoDB buffer pool）中；k2所在的数据页不在内存中。
- insert过程
  - page1在内存中，直接更新内存即可。
  - page2没在内存中，就在内存的change buffer区域，记录下“我要往page2中插入一行（id2，k2）”
  - 同时，将这两个操作记录到redo log中。（图中的3和4）
  - 后台线程还会存在sync数据到磁盘的操作，不在本次的操作过程中。
  从这边可以看出，在change buffer的参与下，执行的insert语句成本很低。（当然update和delete也是一样的）
- select过程mysql> select * from t where k in (k1,k2)
  - 读page1的时候，直接从内存返回。
  - 读page2的时候，需要把page2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的结果进行返回。

选择普通索引还是唯一索引？

对于查询过程来说：
- 普通索引，查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录
- 唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索但是，两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。
对于更新过程来说：

唯一索引的更新不能使用change buffer