MySql实战45讲笔记 --- 基础篇

基础篇 01-08

Q：一条sql语句如何执行的？

A：mysql分为Server层和存储引擎层（默认InnoDB）。

连接器 mysql -h $ip -P $port -u $user -p。建立连接，在tcp握手之后，连接器开始认证身份，用户输入用户名和密码认证。一般使用长连接。
MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了，如何解决呢？
1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
查询缓存。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。

大多数情况下不用查询缓存，原因是什么呢？

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。MySQL 8.0版本直接将查询缓存的整块功能删掉了。
分析器。词法分析，语法分析。
优化器。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
执行器。执行语句。

Q：更新语句呢，如何进行的？

A：与查询最大的不同在于，更新流程涉及两个重要模块：redo log（重做日志）和 binlog（归档日志）。redo log是InnoDB引擎特有的日志，而Server层自己的日志称为binlog。

为什么会有两份日志呢

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新，InnoDB引擎会先把记录写到redo log里并更新内存。同时，InnoDB引擎会在适当时，将操作记录更新到磁盘。

与此类似，InnoDB的redo log是固定大小的，比如配置为一组4个文件，每个文件的大小是1GB，那么内存中总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。

redo log有两个步骤，prepare和commit，也就是两阶段提交，这是为了让两份日志之间的逻辑保持一致。why？

由于redo log和binlog是两个独立的逻辑，如果不用我们看看会有什么问题。仍然用update语句来做例。假设当前ID=2的行，字段c的值是0，再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

先写redo log后写binlog。假设在redo log写完，binlog还没有写完的时候，MySQL进程异常重启。由于我们前面说过的，redo log写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行c的值是1。但是由于binlog没写完就crash了，这时候binlog里面就没有记录这个语句。因此，之后备份日志的时候，存起来的binlog里面就没有这条语句。然后你会发现，如果需要用这个binlog来恢复临时库的话，由于这个语句的binlog丢失，这个临时库就会少了这一次更新，恢复出来的这一行c的值就是0，与原库的值不同。

先写binlog后写redo log。如果在binlog写完之后crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是1，与原库的值不同。

redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

Q：事务隔离

A：当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

为什么尽量不要使用长事务？

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，后面讲到锁时再具体展开。

如何避免长事务呢？

应用开发端：

确认是否使用了set autocommit=0。

确认是否有不必要的只读事务。

业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

数据库端：

监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；

在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；

如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

Q：索引

A：索引的出现其实就是为了提高数据查询的效率。实现方式：有序数组，哈希表，二叉搜索树，N叉树。InnoDB使用了B+树索引模型。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。基于非主键索引的查询需要多扫描一棵索引树，也就是多进行一次回表。

若主键是乱序，插入一个值时对B+树的影响可能很大，因此有时会建议主键为自增主键。自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

覆盖索引，联合索引

最左前缀原则

索引下推

重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。

Q：全局锁和表锁（有点不清楚）

A：MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。全局锁的典型使用场景是，做全库逻辑备份。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

表锁的语法是 lock tables … read/write。MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

Q：行锁，如何减少行锁对性能的影响

A：MySQL的行锁是在引擎层由各个引擎自己实现的。行锁就是针对数据表中行记录的锁。

（两阶段提交）InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。因此尽量将最可能影响并发度的锁尽量往后放，减少该锁的等待时间，提高并发度。

（死锁和死锁检测）死锁的两种解决策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。在InnoDB中，innodb_lock_wait_timeout的默认值是50s。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。（正常情况下采用该种策略居多）

但是检测是否是死锁的过程需要消耗大量的CPU资源，如何解决呢？

一：关闭死锁检测。但是死锁的风险比消耗CPU资源更为大。
二：控制并发度。消耗大量的CPU资源的情况是并发度很高，如果在客户端进行并发控制去减少并发度。但是可行性仍然不高。有的应用有上百个客户端，即使每个客户端只有5个线程，峰值并发仍然可能达到数千。

基本思路在于：对于相同行的更新，在进入引擎之前排队，这样能够减少大量的死锁检测，这就需要更改数据库；另一种思路是将一行改成逻辑上的多行，减少锁冲突。

Q：如果删除表的前10000行数据

第一种，直接执行delete from T limit 10000;

第二种，在一个连接中循环执行20次 delete from T limit 500;

第三种，在20个连接中同时执行delete from T limit 500。

哪种方案最为合适？

第二种方式相对较好。

第一种方式，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在20个连接中同时执行delete from T limit 500），会人为造成锁冲突。

Q：事务到底是隔离的还是不隔离的？

A：在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。事务有三种：已提交事务，未提交事务，未开始事务。只有已提交事务可见。InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。除了update语句外，select语句如果加锁，也是当前读。

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

读提交的逻辑和可重复读的逻辑最主要的区别：

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

“start transaction with consistent snapshot; ”的意思是从这个语句开始，创建一个持续整个事务的一致性快照。所以，在读提交隔离级别下，这个用法就没意义了，等效于普通的start transaction。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；