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

大体来说，MySQL可以分为Server层和存储引擎层两部分。
Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。建表时可以选择不同的存储引擎或者构建方式，比如在create table语句中使用engine=memory, 来指定使用内存引擎创建表。

连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的，指定ip+端口号+用户名，然后输入密码：

mysql -h$ip -P$port -u$user -p

注：在连接建立后，后面执行操作的时候都会依赖建立连接时获得的权限，因此在连接后就算管理员修改了用户权限，也只能在重新连接后生效。

由于建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。解决方案有两种：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

建立连接后第二步可以进行查询缓存了。当查询某条语句时，首先会查询缓存是否有对应结果，之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。

但是大多数情况下不建议使用查询缓存。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。所以针对那些静态表，不经常更新的可以使用查询缓存。

MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

分析器

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
分析器会在该阶段判断语句中某些列、某些表是否存在，不存在则会报错。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。
也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。首先会判断权限，判断该用户是否有相应表的操作权限。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

mysql> select * from T where ID=10;

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

针对有索引的表，所调用的接口是不一样的，本质原理都一样。

在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

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

表的更新语句执行过程和查询语句相似，都会经历连接器、分析器、优化器和执行器的步骤。下面是一个更新操作的例子：

mysql> create table T(ID int primary key, c int);

mysql> update T set c=c+1 where ID=2;

该语句将ID为2的这一行c的值+1。在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空，所以并不建议使用查询缓存操作。

传言MySQL可以恢复到半个月内任意一秒的状态，这是怎样做到的呢？与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

重要的日志模块：redo log

举一个酒店老板的例子，当有人来赊账的时候，老板可以选择在账本上直接划掉那个人的信息，也可以选择先在黑板上写好，然后等打烊再来写到账本上，当业务非常繁忙的时候，根本来不及每次都翻账本，所以老板会把每次信息记录到黑板上等打烊再来写到账本上。

由于MYSQL每次进行更新的时候都需要写入磁盘，然后找到对应的记录信息进行更新，整个过程IO成本和查找成本都很高，所以MYSQL采用了酒店老板的方式，先写入redo log中，等空闲后在一起写入磁盘。这就是MYSQL中的WAL（Write-Ahead Logging）技术，先写日志，在写磁盘，redo log是InnoDB引擎特有的日志。

具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。这四个块从头开始写，写到末尾就又回到开头循环写。有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

重要的日志模块：binlog

MYSQL主要分为两个层面分别是Server层和引擎层，其中redo log是InnoDB引擎特有的，同样Server层也有对应的binlog称为归档日志。

redo log和binlog两者的区别

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
redo log是循环写的，空间固定会用完；binlog是可以追加写入的，“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log用于保证crash-safe能力，进行异常恢复，binlog主要用于归档和复制。

更新执行的操作

根据redo log和binlog的要求，MYSQL进行更新操作如下：

mysql> update T set c=c+1 where ID=2;

首先Server层调用引擎层接口找到ID=2的那一行，此时要判断ID=2这一行所在的数据页是否在内存中，不在内存中则需要从磁盘读入内存，然后返回。
执行器拿到这行数据，将值+1，然后调用引擎接口将这一行新数据写入到引擎中。
引擎将内存中的数据进行更新，然后将此更新写入redo log中，此时redo log处于prepare状态，告知执行器执行结束了，随时可以提交。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的事务提交接口，引擎把刚准备好的数据变为（commit）状态，表示更新完成。

图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

上述流程中，redo log一开始是prepare状态，提交后改为commit状态，这也是经典的“两段式”提交。

两段式提交

首先回答开头提出的那个问题：为什么能恢复半个月中任意一秒的状态？

binlog采用的“追加写”的方式，会将这半个月所有的操作都记录下来，然后整个数据库还需要做一个整库备份（可以是一天一备，也可以是一周一备），当某个时间点出现错误操作后，首先找到那个时间点之前最近的一次备份，然后读取binlog中备份后到出错时间点之前的所有操作进行重放，最后将整个表发送到线上库中。

由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写binlog，或者采用反过来的顺序,这样会导致有可能MYSQL重启后redo log执行了操作，binlog中没有记录，或者binlog中有记录，而存储引擎并没有做对应的操作。

简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

总结：

redo log记录了在这一页做了什么改动（物理层面），binlog记录有两种模式，statement格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
使用两段式提交保证了数据库的一致性，和事务一样保持原子性。
redo log用于保证crash-safe能力，进行异常恢复（循环写入），binlog主要用于归档和复制（追加写）。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数建议设置成1，这样可以保证MySQL异常重启之后数据不丢失。sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数也建议设置成1，这样可以保证MySQL异常重启之后binlog不丢失。保证事务成功，日志必须落盘，这样，数据库crash后，就不会丢失某个事务的数据了。

习题：

redo log的概念是什么? 为什么会存在.
什么是WAL(write-ahead log)机制, 好处是什么.
redo log 为什么可以保证crash safe机制.
binlog的概念是什么, 起到什么作用, 可以做crash safe吗?
binlog和redolog的不同点有哪些?
物理一致性和逻辑一直性各应该怎么理解?
执行器和innoDB在执行update语句时候的流程是什么样的?
如果数据库误操作, 如何执行数据恢复?
什么是两阶段提交, 为什么需要两阶段提交, 两阶段提交怎么保证数据库中两份日志间的逻辑一致性(什么叫逻辑一致性)?
如果不是两阶段提交, 先写redo log和先写bin log两种情况各会遇到什么问题?

03讲事务隔离：为什么你改了我还看不见

事务的ACID特性：原子性、隔离性、一致性和持久性。

隔离性与隔离级别

当多个事务同时执行的时候，就会出现脏读、不可重复读、幻读的问题，为了解决这些问题就有了隔离级别的概念。

首先要了解在隔离级别中，隔离的越严实则效率会越低，所以需要在两者之间找到一个平衡点，SQL有四种隔离级别分别是：读已提交、读未提交、可重复读和串行化。读未提交：就是当前事务还没提交，其他事务就能读到对应数据了。读已提交：就是当前事务只有在提交后，其他事务才能读到数据。可重复读：就是当前事务中看到的数据总是和事务刚开始看到的数据是一样的。（同时也是读已提交的）可串行化：表示对于同一行记录，读会加“读锁”，写会加“写锁”，当读写锁冲突时，会等先前那个事务执行完毕。

根据不同的隔离级别，事务A读取到的v1、v2、v3也会有所区别，对应的值分别为：读未提交：222；读已提交122；可重复读：112（表示事务在执行期间读取的数据都是一样的）；串行化：112（当A事务在操作的时候，B事务来了会锁住，等A事务提交后再执行B事务）。

原理解析：

实际上，对于不同的隔离级别，它们构建的视图是不一样的，访问的时候以构建视图的逻辑结果为准。在可重复读的隔离级别中，数据库视图是在创建事务的时候创建的，在事务执行期间进行的访问也是根据此视图得到结果的；在读已提交的隔离级别中，数据库是在SQL语句开始执行的时候创建的；读未提交直接返回记录上的新值，没有视图的概念；可串行化靠加锁来实现，避免并行访问。

Oracle默认级别是“读已提交”，当Oracle迁移MySQL的时候要切记设置隔离级别为“读已提交”。

事务隔离的实现

在MySQL中，每条记录都要一个相应的回滚操作，记录上的最新值通过回滚操作能够得到前一个状态的值，例如一个值由1变成2、3、4，会有以下类似的记录：

在A、B、C三个视图中，因为不同时刻启动的事务会有不同的read-view，同一条记录有不同的值，相当于对于同一记录数据库存储了其不同的版本，这就是数据库的多版本并发控制（MVCC），对于保留的这些数据通过回滚就可以得到。

针对数据库中的回滚日志并不是一直保留的，当数据库中不需要该回滚日志（当系统里没有比这个回滚日志更早的视图时），回滚日志就会被删除。因此，针对数据库中的长事务（表示执行时间长，长时间未提交的事务，参考：长事务详解），由于长事务存在很多古老的事务视图，这些事务可能随时访问数据库的任何数据，所以在事务提交之前，所对应的回滚日志都会被保留，这样会导致占用很多的存储空间。而且长事务还会占用很多锁资源...

问题：回滚日志内存远大于数据量，该如何解决呢，如何重建库呢

事务的启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。设置set autocommit=1后，每执行一次SQL语句都会持久化到磁盘，能够有效防止长事务的产生。
当我们需要频繁使用交互业务时，可以选择commit work and chain去提交，该语句能够提交事务并开启下一个事务，能够省去“begin”语句的开销。
start transaction语句表示将当前的提交模式挂起，发出该语句后依次执行每条语句，执行完成后commit会提交事务持久化到磁盘，然后返回刚才的提交模式；使用rollbak后会撤回到发出start transaction语句被执行之前的状态。

长事务查询方法

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

习题

读已提交, 可重复读是怎么通过视图构建实现的?
事务隔离是怎么通过read-view(读视图)实现的?
并发版本控制(MCVV)的概念是什么, 是怎么实现的?
使用长事务的弊病? 为什么使用常事务可能拖垮整个库?
如何避免长事务的出现?答：

确认是否设置了set autocommit=1，这个确认工作可以在测试环境中开展，把MySQL的 general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。
确认是否有不必要的只读事务。只读业务不需要放在commit中，否则会浪费时间。
业务连接数据库的时候，根据业务本身的预估，通过SETMAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。
监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill。
Percona的pt-kill工具，可以kill掉MySQL满足某些特征的query语句，详见。 一个经典的例子

04深入浅出索引(上)

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本500页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

索引常见的模型

哈希表：先进行哈希得到哈希值，在放入对应的位置中，一般里面是链表存起来的，由于是无序的，所以缺点是进行区间查询的时候会很慢。所以，哈希表这种结构适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
有序数组：由于是有序存储的，例如可以根据身份证号递增地去存储，进行等值查询和区间查询都很快，用了二分的思想，但是缺点很明显在进行更新的时候，需要把后面的全部挪动。所以，有序数组索引只适用于静态存储引擎，针对那些不会修改的数据会很合适。
搜索树：利用了二叉搜索树的思想，但是这里的叉值不一定是二叉的，也有可能是多叉树，其中二叉树的效率是最快的，但是由于数据并不是全部存在内存中，有的存放在磁盘中，例如有1亿条数据的话，利用二叉树需要读取30次磁盘，每次读取磁盘块耗时假设为10ms，这样下来耗时太多了。所以InnoDb使用的是多叉树，大概N是1200，第一层或者第二层存放在内存中，其他的存放在磁盘，这样就大大减少了读取磁盘块的耗时。N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
跳表、LSM树、倒排索引等，根据不同的应用场景可以选择不同的存储模型。

InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。

这个表的建表语句是：

mysql> create table T( 
id int primary key, 
k int not null, 
name varchar(16), 
index (k))engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。

从图中可以看出，索引的类型分为主键索引和非主键索引。

主键索引：存储的叶子结点是整行数据，在InnoDB里，主键索引也成为聚簇索引。

非主键索引：存储的叶子结点是主键的值，在InnoDB里，非主键索引也成为二级索引。

基于主键索引和普通索引的查询有什么区别？

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

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

索引维护

在插入新值时会发生页分裂和页合并的现象，页分裂发生时由于将要插入的数据页满了，就需要分裂出一个新的数据页存放数据，在这种情况下，效率和空间利用率都会有所下降。当无法保证有序插入的时候，这样写数据的成本会比较高。

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。引入自增主键后，每次增加一条数据都是追加操作，不涉及到数据的移动效率会很高。除性能外，还可以从存储空间的角度来看，以存储身份证号为例，假如主键是身份证号码，则二级结点存储的每个叶子结点占用约20直接，如果存整形做主键只需要4个字节。

是否可以将所有的索引全设置自增主键呢？

有一种业务需求是这样的：索引只有一个且该索引必须是唯一索引。这就是我们的KV数据库，KV关系不能使用自增主键，只能使用唯一的索引。

总结：

为什么InnoDB要用b+树进行存储呢？使用b+树存储能够更好地配合磁盘的读写特性，将部分索引放入内存中，减少单次查询磁盘的读取次数。
N叉树的树高假设为K，为什么每进行一次访问是访问K次磁盘呢？树中每个结点就相当于一个数据块，这个数据块里面存放两个数据，一个是指向下一个结点的地址，一个是存放的主键或者数据，数据块之间是链式连接的，而且每次访问数据块还会触发随机磁盘读，耗时就会比较长。

思考

前两个语句是重建索引k，后两个语句是重建主键索引key，为何要进行重建索引操作呢？索引有可能有删除、或者页分裂等原因，导致数据页有空洞，重建索引能够创建一个新的索引，把数据按顺序进行插入，提高页面利用率，索引更加紧凑，更节省空间。
关于题目中的k索引重建是合理的，能够提高页面利用率，但是主键索引重建是不合理的，对应主键索引重建而言，会导致整个库中的所有表进行重建，所有可以用alter table T engine=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，执行过程为：在k索引树中查询k==3的，然后进行回表，获取k索引树中下一个结点k==5并进行回表，获取k索引树下一个结点k==6，不符合条件退出。共获取了索引树中的3个结点记录以及2次回表操作。

这个过程k索引树中存储的是主键值，也就是回表的内容，那么这块能不能不回表直接记录结果呢？

覆盖索引（一种简单的索引优化方式）

当执行的语句为select ID from T where k between 3 and 5时，此时需要的内容已经在k索引树上了，已经做到了覆盖，所以就不需要回表了。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

什么时候需要用覆盖索引进行优化，什么时候不能用呢？

举个栗子，当我们需要根据某个用户的身份证号查询该用户信息的时候，此时是否需要建立（用户名，身份证号）的联合索引？显然是不需要的，因为需要查询的是所有用户信息，而不只是用户名这一个属性；当我们业务需要频繁根据身份证号获得用户名时，此时建立联合索引构成覆盖索引，能够去除回表操作，有效减少读行次数。

最左前缀原则

当我们按照上述要求构建（用户名，身份证号）的联合索引后，假如现在又来了一个新需求，需要根据身份证号去查询地址信息，那么这样构建三个联合索引太耗时了，能否用刚才创建的联合索引去获得地址信息呢？

根据最左前缀原则，在搜索的过程中会对字符串的前N个字符进行比对，当利用（a，b）构建了联合索引后，可以根据a的信息回表到全部信息（减少了一个索引树），也可以进行相应的模糊匹配（例如：like “张%”），所以在能够减少索引树的前提下，良好地设置联合索引的顺序能够减少所占的索引空间。

只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推

在MySQL5.6后加入索引下推的判断，表示的是在联合检索过程中会根据保存的其他信息去判断该记录是否需要进行回表，直接过滤掉不满足条件的记录，减少回表次数。

使用索引下推和不使用进行的访问次数如下图所示：

附：当搜索到叶子结点的数据页时，如何获得所需的那一行数据呢？

当访问到对应数据页时，会采用二分的方法选到要求的那一行，然后进行输出。访问下一个结点由于b+树是按顺序进行存储的，直接在磁盘上移动即可。

习题

CREATE TABLE `geek` ( 
`a` int(11) NOT NULL, 
`b` int(11) NOT NULL, 
`c` int(11) NOT NULL, 
`d` int(11) NOT NULL, 
PRIMARY KEY (`a`,`b`), 
KEY `c` (`c`), 
KEY `ca` (`c`,`a`), 
KEY `cb` (`c`,`b`) ) 
ENGINE=InnoDB;

其中a、b为联合主键，针对以下两条查询语句，是否需要构建ca和cb两个联合索引呢？

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

首先针对C列重复很少的时候，这样where出来的内容很少，在进行相应的排序即可，不需要构建联合索引。
当C列重复很多的时候，就需要构建联合索引消除排序所需时间，在数据量很大的时候，排序操作耗时比较多，很有可能会借助磁盘临时表来进行排序。而且针对limit 1，如果没有cb这个联合索引，相当于返回1行进行回表，没有起到限制扫描行数的作用。ca联合索引是不需要构建的，因为已经存在了c索引，且ab为联合主键，那么c是固定值，对于相同的c其中ab也是顺序存储的，根据最左匹配原则可以推断得出limit 1的结果进行回表。
所以有时候如果在where条件建立索引的效率差的情况下，在order by limit这一列建索引也是很好的方案，排好序在回表，只要过滤出满足条件的limit行，就能及时停止扫描。

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

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

全局锁

当进行全库逻辑备份的时候，当需要把全库内容select出来做成文本的时候，就需要对数据库进行加锁，让数据库无法进行更新、添加操作，MySQL提供了一种全局加锁的方法命令是Flush Tables With Read Lock（FTWRL），添加后数据库为只读状态。

为何要进行加只读锁呢？如果在主库上加锁则无法进行数据更新，业务就要停滞，如果在从库上加锁则不能同步主库过来的binlog，导致主从延迟。

由于在备份的过程中，有可能备份某个表后，其他表发生了相应的变化（如购买书籍的时候，账户余额表和用户背包表的关系），这时同意更新后则会造成数据不一致的问题，说到这里，我们回想一下前面所说的视图不一致问题，针对视图不一致InnoDB提出了可重复读的隔离政策，这不正是我们所需要的吗，为什么还要提出FTWRL呢？

可重复读视图一致很好，但是也要保证引擎能够支持这个隔离级别，比如MyISAM就不支持事务的引擎，就必须用FTWRL了，否则在备份的过程中有更新的化会影响备份的一致性。

所以当mysqldump使用single-transaction参数只适用于所有的表使用事务引擎的库，如果有的表不支持事务引擎，则必须使用FTWRL了。可重复读隔离模式由于有MVCC的支持，在备份过程中还是可以支持更新的。

附：关于全库只读上，为什么不使用set global readonly=true的方式呢？他确实也是将数据库变为只读状态，但是和FTWRL相比他有两个问题：

有些系统中readonly会被用作其他逻辑，用来判断一个库是主库还是备库，因此修改global影响极大，不建议使用。
在异常处理中有差异。如果执行FTWRL后由于客户端原因发生断开，则数据库会自动释放这个锁，数据库恢复可用状态，而readonly会保持这个状态，导致整个库长期处于不可写的状态，风险较高。

表级锁

MySQL中表锁主要分为表锁和元数据锁。

表锁的语法是lock tables … read/write，和FTWRL相似，在客户端断开连接后会自动释放锁，该锁除了限制非本线程的其他进程无法访问以外，也限制了本线程后面的访问。但对于InnoDB这种支持行锁的引擎来讲，一般不用表锁，毕竟整个表都锁住的影响比较大。

另一种表锁是MDL（metadata lock）MDL不需要显式使用，在访问一个表的时候会被自动加上。用来保证读写的正确性。MDL锁在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

场景题

每次访问和写入的时候都会使用MDL锁，而MDL锁只有在事务提交的时候才会释放，在频繁读取后如果想要对表添加一个字段自然会被锁住，导致后面的读取操作也无法进行，如何安全地增加字段信息呢？

首先要解决长事务的问题，事务不提交就会占有MDL锁，导致无法释放资源，在MySQL的information_schema 库的 innodb_trx 表中可以查看到当前执行的事务，kill或者暂停掉长事务。
但是当读取很频繁，每次关掉一个事务后会立马重新启动一个新的事务，这时应该如何处理呢？正确的方法是在alter table后面设定等待时间，若等待时间内能拿到写锁则进行修改，拿不到也不能妨碍后面进行的操作，由操作者继续重试这个命令。

总结

全局锁主要用在数据库的逻辑备份过程中，如果使用InnoDB引擎则使用single-transaction更好，否则使用FTWRL对数据库进行加锁。
表锁一般在不支持行锁的引擎中才会使用，当数据库代码中存在lock table时，要留心观察是否引擎需要升级或者引擎升级完成后代码没有进行升级。lock table ... write/read表示本进程可以进行相应的读写操作，其他进程：write（无法读写），read（无法写）。
MDL是并发情况下维护数据的一致性,在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的，DDL增加写锁，会出现等待情况，DML增加读锁，读锁之间不冲突，当DML和DDL交互的时候会容易导致事务爆满，事务也是要占内存的，当事务提交的时候会释放MDL锁。注意：表锁和MDL锁是两个不同的表机构，执行DML语句时使用的是表的写锁和DML的读锁，读锁之间不互斥，所以第二个线程也使用了表的写锁和MDL的读锁，但是写锁是互斥的，所以看上去相当于第二个线程在等第一个线程执行完毕。
MySQL5.6后的online DDL操作实际上分为5步：获取MDL写锁、降级为MDL读锁、进行DDL、升级成MDL写锁、释放MDL锁，由于第三步占了绝大多数的时间，这个期间是可以正常读写数据的，所以称为online。

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

MySQL的行锁实在引擎层由各个引擎自己实现的，但并不是所有引擎都支持行锁，InnoDB是支持行锁的，这也是MyISAM被替代的重要原因之一。

从两段锁说起

针对行锁的使用，在对某行进行修改的时候，会持有改行记录的行锁，在commit的时候才会释放。也就是说，行锁是在需要的时候才加上的，但并不是不需要了就立即释放，而是等事务结束时才释放，这就是两段锁协议。

针对行锁的使用上，假如某个事务中需要锁多个行，要把最可能造成锁冲突、最有可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，设计的线程都在等待别的线程释放资源时，就会导致这几个线程进入无限等待的状态，称为死锁。出现死锁的策略有两种解决方式：

一种策略是直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
另一种策略是发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务能够继续执行。这种策略每次进来一个新的线程被等待后，都要判断是否是自己导致死锁的发生，因此要消耗大量的CPU资源。

一般情况下我们不考虑第一种策略，因为过多或者过少的时间都不是很合适的，采用第二种策略的话会导致CPU占用爆炸的问题，如何解决热点行更新导致的性能问题呢？

第一种方式是放弃死锁检测。如果能够保证不会发生死锁的情况，可以考虑临时关闭死锁，但是该方法不推荐使用。
第二种方式是控制并发度。比如假如某一行只有10个线程在更新，那么死锁的检测成本很低，就不会出现这个问题。因此可以在数据库服务端做这个并发控制，可以采用中间件的思想，对应相同行的更新，在进入引擎之前排队，这样InnoDB内部就不会有大量的死锁检测工作了。
如何从业务上实现呢？可以采用多行的思想，可以把频繁修改的某行复制为多行，每次随机选择一行进行修改，求和时采用多行的总和，这样减少了冲突的概率，降低了CPU的消耗。

总结

针对行锁会导致后续事务等待的现象，要尽量把有可能造成锁冲突、最有可能影响并发度的锁的申请时机往后放。
针对死锁的方法最好使用死锁检测，并且控制并发量来降低死锁检测的数量。

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

在数据库的事务隔离中，如果是可重复读隔离级别，事务T启动的时候会创建一个视图read-view，之后事务T执行期间，即使有其他事务修改了数据，事务T看到的仍然跟在启动时看到的一样。那么当进行更新的时候会是什么样的操作呢？可重复读实现的原理又是什么呢？

注：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。

结合MVCC的实现逻辑，对于每行数据的多个版本，实现一致性读后可以根据“快照”获得对应的数据，例如上图A事务读出的结果是1，B事务得出的结果是3，这是怎么实现的呢？

“快照”的实现逻辑

事务在启动的时候，就针对整个库记下了“快照”，在可重复读的隔离模式中，根据事先获得的“快照”得出结果，但是这个“快照”不可能针对整个库进行一个复制的操作，事实上数据库中进行的每一个事务都有对应的事务ID，该ID是严格递增的，对应表中的每一行都记录了事务ID的修改历史，可以通过事务ID获得前面的数据。

其中U1、U2、U3表示undo log进行回滚，数据库并不需要保存每个历史数据，只需要记录最新数据及回滚操作就可以了。

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果这个事务自己更新的数据，还是会认的。

在实现上，每个事务在启动的时候都会记录当前“活跃”的事务ID（已启动但未提交的事务ID），在读取数据的时候：

如果该数据的ID是已提交的事务，则其可见。
如果是未开始的事务，则不可见。
如果是未提交的事务ID，查看其是否在“活跃”数组中，如果在则可见，否则不可见。因此，InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

前面所述的A、B事务读取结果如图所示。

更新逻辑

在上面的图片中，可以很明显的观察到，如果按一致性视图的思想来看，B事务修改的应该是（1，1）为什么是（1，2）呢？这就涉及到了更新的规则：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。如果不能够记录其他事务的修改则会丢失掉他们的修改！

因此在更新的时候，针对的并不是历史版本的数据，而是“当前读”的数据，将“当前读”的结果进行修改，并加上事务本身进行的修改，得出最新的数据结果进行记录。

进一步扩展，假设事务C不是马上提交的，而是变成了下面的事务C，会怎么样呢？

这里C事务进行修改完成后并没有立马提交，接着B事务又对其进行了修改，这不就正好是我们所说的两段锁协议吗！，因此事务B的修改操作会被wait要等C事务的写锁进行释放，事务C提交后才能进行修改，然后就是修改“当前读”数据，加上事务本身的修改得到k=3。A事务因为是进行读，所以C事务提交后（其结果对A事务不可见），A事务输出读取结果k=1。

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

通过上述介绍，将可重复读分为两个部分：

针对数据读取上，采用一致性读的思想，根据不同的事务ID读取对应行最合适的可见数据，如果有修改操作的话会加上修改，最后进行输出。
针对数据更新上，采用当前读的思想，读取当前对应行的最新数据，然后进行修改，如果对应行记录的行锁被其他事务占用则需要进行等待。

读已提交的隔离级别

该级别的视图是在每个操作语句开始的时候构建的（因此可重复读中一开始构建的视图就没有作用了），根据已提交的数据可见，未提交的数据不可见进行输出即可。

总结

InnoDB的行数据有多个版本，每个数据版本有自己的row trx_id（对应的修改事务ID），每个事务或者语句有自己的一致性视图。普通查询语句是一致性读，一致性读会根据row trx_id和一致性视图确定数据版本的可见性。

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；而当前读，总是读取已经提交完成的最新版本。

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

唯一索引：在构建索引时，确保该字段是唯一的则可以构建索引，在进行查询时唯一索引命中后直接返回结果，普通索引命中后会在页内往后继续读，直到读取内容不符合要求则返回结果。

当我们设计数据库时，如何去选择唯一索引和普通索引呢？

查询过程

在数据库根据索引进行检索时，首先通过b+树搜索到目标的叶子节点上，然后在页内使用二分法确定好目标结果：

当使用普通索引时，读取完结果后向后继续读一位判断是否符合要求，不符合则跳出。
当使用唯一索引时，读取完结果后根据索引的唯一性，直接返回结果。两者在查询过程差别大吗？显然不大，普通索引多读一位的判断可以忽略不记，并且如果读到页尾重新加载一页继续判断的概率也很小可以忽略不记。

更新过程

当需要更新一个数据页时，数据库并不是立即将对应的页加载到内存中，而是采用了buffer pool的机制，如果每次改动的数据页在内存中则直接进行改动，如果不在则将改动存到change buffer中，下次加载新数据页的时候判断change buffer中是否有相应的改动，如果有新改动则merge起来。

虽然change buffer是在内存中的，但是它在磁盘上也进行了持久化，每隔一段时间系统后台线程会自动将change buffer中的内容进行purge。在数据库正常关闭后，系统也会自动进行purge。

如果能够将更新操作记录在change buffer中，减少读磁盘，语句的执行速度会得到明显的提升。而且数据读入内存是需要占用buffer pool的，这种方式还能够避免占用内存，提高内存利用率。

change buffer有一部分在内存中，有一部分在ibdata中，作purge操作就是会把change buffer里相应的数据持久化到ibdata中，redo log会记录数据页的修改以及change buffer新写入的信息。

什么条件下能够使用change buffer呢？

对于唯一索引来说，每次更新需要判断是否违反了唯一性约束，如果数据页在内存中则直接判断，否则需要先加载到内存中进行判断，因此唯一索引并不能使用change buffer机制。
对于普通索引来说，每次更新只需要写入change buffer中，下次读取数据页的时候进行merge就行了，尤其针对多改少读的业务优势更大（例如账单类、日志类系统），针对少改多读的意义不大，因为有可能IO次数并没有减少，反而增加了change buffer的维护代价。因此使用change buffer要考虑实际业务情况使用。

索引的选择和实践

唯一索引和普通索引在查询上是没有太大差别的，主要在更新操作上，普通索引配合change buffer性能会更佳。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。

而且如果是针对机械硬盘读取的话可以考虑加大change buffer的大小，以此减少读磁盘的次数。

change buffer和redo log

在第二讲说提到的redo log（checkpoint到limit之间的循环写入）在性能方面的提升主要是减少了随机读写，而change buffer也是减少了随机读写，那么这两个是如何进行区分的呢？

首先关于redo log它的作用是在两段式提交中通过先写日志，后写磁盘的机制保证数据完整性，性能上减少随机写磁盘的消耗；change buffer表示当修改数据时并没有立即将对应的数据页读入磁盘中进行修改，而是存入change buffer中等下次读入数据页后进行merge。。

举个例子：假设存在两个数据页k1和k2，k1在内存中，k2在磁盘中，对k1进行修改时由于其在内存中则直接进行修改，然后将修改内容存入redo log中，对k2进行修改时，由于其不在内存中，则将修改写入change buffer，并且将此操作写入redo log中；当进行读数据时，由于k1在内存中，则直接得到结果（因此证明在WAL中，每次读取数据并不一定要读取磁盘进行更新然后返回结果，如果在内存中直接就能得到答案），在k2中由于不在磁盘中，将其装入内存后根据change buffer进行maerge，然后得到正确结果。

注意在此过程中，change buffer的作用是针对修改并不是每次都需要装入内存然后修改==减少随机读操作；redo log作用的并不是每次修改都要立马落盘，而是在特定时间段才进行磁盘上的数据修改操作。redo log中存放的是change buffer的数据修改，change buffer里面存的是数据的修改，通过系统其他线程定期purge将更新数据刷到磁盘中。

总结

当业务满足索引不唯一后，根据业务的特性，推荐使用普通索引去实现，多写少读开启change buffer，多读少些关闭change buffer。
当以下情况时可以采用唯一索引：当业务无法保证索引不唯一时，必须用数据库加以限制可以使用唯一索引。

习题

change buffer是在内存中的，那么当change buffer有内容的时候突然断电会发生什么情况呢？

change buffer有一部分在内存中，一部分在ibdata中，针对ibdata中的数据在进行purge操作后会进行持久化。redo log记录了数据页的修改和change buffer的写入信息，如果断电，持久化的数据已经purge不用恢复。
主要针对未持久化的数据，未持久化的数据分为以下几种：

change buffer写入，redo log虽然做了fsync但是没有commit，binlog未fsync到磁盘，这段数据丢失。
change buffer写入，redo log写入但没有commit，binlog已fsync到磁盘，可通过binlog恢复redo log，在通过redo log恢复change buffer。
change buffer写入，redo log和binlog都已fsync到磁盘，通过redo log直接恢复change buffer。

10MySQL为什么有时候会选错索引

在MySQL中一个表是可以支持多个索引的，当写SQL语句的时候如果没有指定使用哪个索引时，默认会对索引进行对比，选择一个更优的索引，但是有时会选错索引，针对索引选择问题，MySQL 优化器有以下几个选择方式：

扫描行数越少越好，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。
根据是否使用排序、临时表的因素综合判断。 扫描行数是如何判断的呢？ MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

展示索引基数： 采用采样统计后，估算出每个索引上的“基数”，然后选择合适的索引。

优化器预估查看

若优化器此时并没有合理计算出预估扫描的行数，可采用以下策略：

执行analyze table t 命令恢复的explain结果

索引的优化选择

若在查询过程中发现一个简单的语句会消耗很长的时间，则可以选择以下几种方法：

对于由于索引统计信息不准确导致的问题，可以用analyze table来解决。
可以在应用端用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

11怎么给字符串字段加索引

前面讲的索引都是基于数字去构建的，但是针对字符串类型比如邮箱账号如何去构建呢？

前缀索引，例如可以根据邮箱账号的前5位去构建，相比于全部邮箱长度更节省空间：

mysql> alter table SUser add index index1(email); 
或 
mysql> alter table SUser add index index2(email(6));

检索过程：使用index1进行检索则根据索引树检索到叶子结点上然后查找到符合条件的结果，回表输出，继续检测下一位的数值，不符合要求则退出；使用index2进行检索后则根据前缀6位索引检索到叶子结点上，依次判断是否符合要求，很明显使用index2会扫描更多的行。因此，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是，假如多使用几位前缀索引后能够有效区分不同的数据，这样定义好长度后，就可以做到既节省空间，又不用额外增加太多的查询成本。

实际上在创建前缀索引时关注的是区分度，区分度越高越好，那么要如果判断使用多长的前缀呢？

首先可以使用下面语句计算出每个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后依次选取不同长度的索引来看这个值，指定好可以接受的损失比例后，返回满足条件最短的长度。

前缀索引对覆盖索引的影响

由于前缀索引每次都需要回表去判断截取结果是否完整符合条件，则前缀索引无法使用覆盖索引的优化。这也是是否选择前缀索引需要考虑的一个因素。

其他方式

如果对于邮箱来说，可能前缀索引的效果还不错，但是如果针对身份证这样的字段呢？

一个身份证号是18位，前6位是地址码，如果按前6位构建前缀索引的话，这个区分度就很小了，所有需要构建更长的索引，如何解决这种情况呢？

第一种方式可以使用倒序存储，如果存身份证后6位的话，这样没有重复的逻辑，效果可能会好一些。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

第二种方式可以使用hash字段，可以在表上创建一个整数字段，同时在这个字段上创建索引。 这样每次在插入新记录的时候，都需要用到crc32()这个函数得到效验码，根据效验码进行插入；在查询的时候会有效验码重复的情况，所以需要判断效验码+id是否精准相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样可以将索引长度变为4个字节，节省大量内存。

使用倒序存储和hash字段两种方法的异同点： 首先共同点是两者都不支持范围查询，由于倒序存储是按倒过来排序的，所以正向范围不支持；同样hash字段也只能支持等值查询。

区别主要在以下三个方面：

从占用的额外空间来看，倒序索引在主键上不会消耗额外的存储空间，hash字段会增加一个字段。
在CPU消耗方面，倒序读写的时候需要调用reserve函数，hash字段调用一次crc32()函数，前者消耗更少一些。
从查询效率来看，hash的查询性能更加稳定；倒序方式会增加扫描行数。

12数据库为什么会“抖”一下

在平时使用数据库的过程中，一条SQL正常情况下执行速度非常快，但是有时候突然会变得非常慢，并且很难复现这种情况，这是怎么回事呢？

回想前面所讲的redo log：在数据页需要进行修改的时候，并不是立马写入磁盘中，而是记录到redo log中，定期将redo log中的内容刷回磁盘中完成修改，所以我们平时执行就是相当于读内存、写内存的过程，抖动的时候就是将redo log中的内容刷回到磁盘的过程。

那么什么时候会将redo log中的信息刷回到磁盘中呢？

当redo log满了，记不下的时候，这时必须将现在redo log中的内容刷回到磁盘中，具体操作为：将checkpoint向前推进，然后将经过的部分写入磁盘中。
当系统内存不足的时候，这时候需要进行页面置换将内存中的脏页刷回磁盘中。
当系统空闲的时候，会进行刷页操作。
当MySQL正常关闭的时候，会把redo log中的内容全部写入磁盘。

接下来分析一下以上四种场景对性能的影响：

第三种和第四种是在MySQL空闲的时候进行操作，这时系统没什么压力，是没有影响的。
第一种情况是redo log写满了需要刷回磁盘，所以这时系统无法接受更新操作，这时更新数会跌为0。
第二种情况是经常出现的，InnoDB使用缓冲池（buffer pool）管理内存，缓冲池的内存页有三种状态分别是：还未使用的、已使用的干净页和已使用的脏页。当读入数据页时，如果所需数据页不在内存中则需要从磁盘中读取，如果系统内存已满则使用LRU置换算法进行置换。

综上所述，以下两种情况会影响性能：

一个查询要淘汰的数据页太多，导致查询响应时间边长。
日志写满，更新全部阻塞，无法继续更新操作。

所以InnoDB需要控制脏页的比例避免出现以上两种情况。

InnoDB刷脏页的控制策略

想要控制InnoDB刷脏页的比例首先要清楚主机的IO能力，这样InnoDB才能知道全力刷脏页可以刷多块。

这就需要innodb_io_capacity参数，可以将其设置为磁盘的IOPS，可通过fio工具来进行测试：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

既然已经知道了全力刷脏页的速度，但是也不能一直全力刷，毕竟磁盘不止要刷脏页还需要服务用户请求,那么要如何去设计控制刷脏页的速度呢？

如果刷的太慢会导致脏页太多，redo log写满，太快会导致查询性能下降的问题。所以InnoDB的刷盘速度主要考虑这两个因素：脏页比例和redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。具体计算方法在此不展开描述。

因此InnoDB会在后台刷脏页，而刷脏页会将内存写入磁盘，所以有可能在查询语句的时候需要在内存中淘汰一个脏页写入磁盘，占用了IO资源并可能影响更新语句，所以会感觉到MySQL“抖动”。

要尽量避免这种情况，你就要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75% 。

刷脏页的“连坐”机制

在InnoDB中，每个请求在执行过程中刷一个脏页的时候，如果数据页旁边的页也是脏页，那么会将邻居连带刷入，因此扩展直至邻居不为脏页，这样做的目的是由于以前使用的机械磁盘IOPS太低，可以减少很多随机IO，InnoDB中的innodb_flush_neighbors参数就是控制这种操作的，可以将其进行关闭。

总结

redo log是关系型数据库的核心，保证了ACID中ID，那么如果redo log设置的太小，redo log很快被写满，会出现什么情况呢？

将redo log中脏页刷入磁盘中，则checkpoint需要向前推。
由于redo log中记录了undo的变化，undo log buffer也需要持久化到undo log中。
当innodb_flush_log_at_trx_commit设置为非1（1表示会在事务提交的时候将日志写入磁盘）,还要把内存里的redo log持久化到磁盘上。
redo log还记录了change buffer的改变，所以还需要把change buffer purge到ibd中，以及merge change buffer，merge产生的数据也是脏页，也需要持久化到磁盘中。（可以说redo log太小的话change buffer的优化也没太大意义了）。以上四种情况会导致“抖动”频繁，占用IO资源，影响更新效率；而且如果刷盘速度慢的话还会导致读取耗时增加。

redo log针对脏页以及刷回到磁盘中的数据页（脏页是直接替换数据页的），是如何判断以及flush过了呢？

其实在每个数据页头部都有LSN，8个字节，每次修改都会变大，所以只需要判断当前checkpoint的LSN和数据页的LSN进行比较，如果数据页的更大则表示是干净页。

13为什么表数据删掉一般，表文件大小不变？

在MySQL的InnoDB中，一个InnoDB表包含两部分即：表结构定义和数据。在8.0版本之前，表结构存在以.frm后缀的文件中，8.0以后的版本允许表结构存放在系统数据表中，因为表结构定义的空间很小。

参数innodb_file_per_table

表数据既可以放在共享表空间里，也可以是单独的文件，有innodb_file_per_table参数进行控制：

OFF表示表的数据放在系统共享空间，也就是和数据字典放在一起。
ON表示每个InnoDB表数据放在一个.ibd的文件中（推荐ON）。当单独存放后，删除某个表系统则会直接删除那个文件，如果放在共享空间中，即使表删掉了，空间也不会回收。

数据删除的流程

InnoDB删除某条数据的时候，并不会立马将该数据删掉，而是在B+树中对应位置设为“可复用”状态，等插入数据的时候，直接放在这个“可复用”的位置即可，所以整个过程并没有改变数据页的大小，那么当删除一整个数据页上的所有记录会怎么样呢？此时数据页就是“可复用”状态了。

但是，数据页可复用和记录的可复用是不同的。

记录的复用只限于符合范围条件的数据，比如200，300，400中间删除300，此时再来一个350是可以直接插入进去的；而整个页从b+树摘掉后，可以复用到任何位置上。

因此我们在删除后会产生“空洞”的效果，而且不止删数数据会造成空洞，插入数据也会。

当插入数据发生页面分裂后，旧的一页末尾就留下了空洞，此时文件大小并没有改变，通过重建表会达到所求的目的。

重建表

为了去掉前面遗留的空洞提高页面利用率，我们可以通过临时表的思想进行重建表。

alter table A engine=InnoDB

执行流程如下图所示，根据主键ID递增的顺序，把数据一行一行地从表中读出来然后插入到tmp表中，最后将tmp表和A进行替换：显然在将数据插入tmp表的过程耗时最多，而且在这个过程中如果有新写入的表会造成数据丢失！因此在整个DDL过程中，表A不能有更新，整个DDL不是Online的。

而在MySQL5.6版本开始引入了Online DDL，在重建表的过程中允许DDL操作。重建表的流程中将A在中间中进行的操作记录在一个日志文件（row log）中，具体流程如下所示：

附：在alter语句启动的时候需要获取MDL写锁，那DDL也是要拿MDL写锁，这样能支持吗？

其实alter在开始的时候确实是拿MDL写锁，但是这个写锁在真正拷贝数据之前就已经退化为读锁，MDL读锁并不会阻塞增删改查的操作。

总结

当我们在delete表的时候，并不会真正删除里面的内容，而是将对应的记录或者数据页置为“可复用”，执行了重建表的操作后，表的大小才真正减小了。需要注意的是Online DDL是考虑在业务低峰期使用的。

14count(*)为何这么慢

select count(*) from t能够快速获取一个表的行数，但是随着记录越来越多，这个语句执行速度会越来越慢，这个语句是如何执行的呢？

count(*)的实现方式

count(*)是在sever层实现的，引擎层会把server层需要的内容发过去，然后由server层进行计数操作。在不同的引擎中，count(*)有不同的实现方式：

在MyISAM中，它把表的总行数存在了磁盘上，因此执行count(*)会直接返回这个数，效率很高（注意是没有where过滤的累计）。
在InnoDB中，需要一行一行地从引擎中读出来，然后累积计数，效果不佳。

为什么InnoDB不和MyISAM一样，也把数字存起来呢？

由于MVCC的原因（InnoDB默认可重复读隔离模式），同一时刻读取多少行是无法确定的（MyISAM不支持事务，所以就没这个问题），例如：

所以在每个时刻的行数只能通过读取计数，因为他们之间可能是不同的。

当然，count(*)也是有优化的，因为在b+树中，主键索引的非叶子结点存索引，叶子节点存数据，而普通索引的叶子节点存主键，所以每次MySQL会选择较小的树去扫描。

另外执行show table status也会显示TABLE_ROWS但是这个只是采样估算求得的，因此它也不是很准。

用缓存系统保存计数

我们是否可以用一个缓存系统来支持修改呢，比如加入Redis来进行这个表的总行数，每次插入则Redis计数加一，每次删除则减一，这种方式虽然读和更新都很快，但是一个很重要的问题是缓存系统会丢失更新。而且由于Redis不支持分布式事务，每次插入和修改操作不能放在一个事务中，这样就会导致逻辑不一致的问题。

在并发系统中，由于我们无法控制不同线程的执行时刻，就会导致以上这种计数值逻辑不一致的问题。

用数据库保存计数

如果缓存系统由于不支持分布式事务，每次修改的结果不确定，无法使用缓存系统来保存计数，那么是否可以用支持事务的InnoDB去记录呢，比如我们可以在数据库中单独放一个计数表，每次修改这个计数表能否解决？

首先解决了更新丢失问题，由于InnoDB的日志系统，保证了其数据修改的完整性，不会导致更新丢失问题。
对于计数和插入的问题，我们将其放到一个事务中，每次执行必定是完整的，以此实现逻辑的完整性。

在上面的例子中，T3时刻由于会话A还未提交，对会话B不可见，所以逻辑上是正确的。

不同的count语法

在select count(?) from t这样的查询语句里面，count(*)、count(主键id)、count(字段)和count(1)等不同用法的性能，有哪些差别呢？

count(主键id)，首先InnoDB会扫描整个表，把每行的id都拿出来返回server层，server判断每个id是否为空，按行数进行累加。
count(1)，InnoDB扫描整个行，但不取值，对于每一行放一个数字“1”进去，server层判断是否为空，按行累加。
count(字段)，首先要判断该字段定义是否为not null，如果不可以为null，则先按行读取这个字段，然后按行累加；如果可以为空，则需要判断是该行为NULL还是该字段为NULL，每行的行头有一个标记位，用来标记该行是否为空。

15问题集锦

日志相关问题

binlog和redo log是如何配合配合崩溃恢复的呢？

根据在两段式提交的不同阶段，MYSQL异常重启的现象不同：

如果在A时刻发生了crash，由于此时binlog还没写，redo log还没提交，当崩溃发生的时候这个事务会回滚，由于binlog还没写，所以也不会传到备库中。
如果在B时刻发生了crash，此时我们可以参照崩溃恢复时的判断规则来判断（此时正好符合2.2中的情况，需要进行回滚）：

如果redo log里面的事务是完整的，也就是有了commit标识，则直接提交；
如果redo log里面只有完整的prepare，则判断对应的事务binlog是否存在并完整：
- 如果是，则提交事务；
- 否则回滚事务。

(1)MySQL是如何知道binlog是否完整？

一个事务的binlog是有完整格式的：

statement格式的binlog，最后会有COMMIT；
row格式的binlog，最后会有一个XID event。
在MySQL5.6.5版本后，还引入了binlog-checksum参数来验证binlog内容的正确性。

(2)redo log和binlog是如何关联起来的？

它们都有一个共同的字段叫XID，当发生崩溃的时候，会按顺序扫描redo log：

如果既有prepare，又有commit的redo log，就直接提交；
如果既有prepare，又没有commit的redo log，就拿上XID去binlog中查找binlog是否完整。

(3)为什么要使用两段式提交？

这时一个经典的分布式系统问题，对于InnoDB引擎来说，如果redo log提交完成了，事务就不能回滚（否则会覆盖掉别的事务更新），如果redo log直接提交，binlog写入失败了，此时就会导致数据和binlog不一致了，从库和主库数据也会不一致。

(4)使用InnoDB引擎的话，能否只用redo log不用binlog？

从崩溃恢复的角度来看是可以的，此时就没有了两段式提交，但系统依然是crash-safe的。
但是redo log的特点是循环写，binlog是追加写，如果想要回滚到以前某一时刻的话，redo log没有对日志进行保留，redo log没办法进行归档。
MySQL的高可用依赖于binlog进行主从复制，以及其他一些异构系统消费binlog，如果没有binlog就没办法工作了。

(5)redo log一般多大

redo log如果太小的话会导致频繁“抖动”，因此不得不多次强行刷盘，占据IO资源等等，WAL机制也没有优势了，因此建议4个1GB的大小。

(6)redo log的被动刷盘机制

首先要了解一下write（redo log buffer写入os buffer）、fsync（调用fsync将其刷入redo log file）、redo log block（日志记录，一个block512直接，12直接head头+496直接body块+4直接尾），那么redo log什么时候会进行刷盘呢？

当redo log buffer日志占本身大小的一半的时候，会将其刷入磁盘。
一个事务提交时，将其redo log所在的redo log block刷盘，此时刷盘又分为三种情况，通过设置innodb_flush_log_at_trx_commit进行设置：

0：延时写，延迟刷：提交事务不会写入os buffer，而是每隔一秒写入并fsync到磁盘，系统崩溃会丢失一秒钟数据，
1：实时刷，实时写：每次提交事务都将redo log写入os buffer且fsync到磁盘，这样就能保证数据不丢失，但每次都写入磁盘，性能较差（推荐，一般是用这种）
2：实时写，延时刷，每次实时写入，延时1秒fsync刷盘。

后台线程定时刷新，每隔1秒会把redo log block进行刷盘
MySQL关闭，redo log block刷盘。

两段式提交的解析：两段式提交的时候，首先redo log进入prepare状态，此时需要先write再fsync；然后binlog先write且fsync，最后commit完成后write到os buffer中，此时由于前面以及fsync，则不需要fsync了。

此时问题来了，每次提交过程中也是进行两次fsync，那么这样做的意义何在呢，不也是慢了吗？

redo log和binlog都是顺序写，这样要比随机写更节约时间性能更高。
组提交机制(获取当前最大的LSN将之前未写入的redo log一并写入)使得在并发的状态下，每次可以将多个redo log中的内容fsync到磁盘中，大幅降低磁盘的IOPS。

16“order by”语句是怎么工作的？

假如定义了某个用户信息表，需要按名字排序输出城市为“杭州”的前1000个用户信息，SQL语句可以这么写（city为普通索引）:

select city,name,age from t where city='杭州' order by name limit 1000 ;

那么这个语句的执行过程是什么样的呢？

全字段排序

针对需要排序的SQL语句，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer，具体流程如下所示：

初始化sort_buffer，确定放入name、city、age这三个字段；
从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
从索引city取下一个记录的主键id；
重复步骤3、4直到city的值不满足查询条件为止；
对sort_buffer中的数据按照字段name做快速排序；
按照排序结果取前1000行返回给客户端。

图中最后的排序过程有可能是快速排序，也可能是外部排序，这所根据的是排序所需的内存和参数sort_buffer_size，如果排序的数量小于这个参数就在内存中进行，否则使用外部排序进行。

可以用以下的方法确定一个排序语句是否用了临时文件：

/* 打开optimizer_trace，只对本线程有效 */ 
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */ 
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read'; 

/* 执行语句 */ 
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */ 
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G 

/* @b保存Innodb_rows_read的当前值 */ 
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read'; 

/* 计算Innodb_rows_read差值 */ 
select @b-@a;

通过观察OPTIMIZER_TRACE的结果来确认是否使用了临时文件：

图中的12表示MySQL将需要排序的数据分成12份，每一份单独排序后存在这12个临时文件中，然后把这12个文件合并成一个有序的大文件。

rowid排序

全字段排序在临时文件中会存放所有需要的字段信息，这样有一个问题就是当需要输出的字段很多的时候，导致内存里同时能放下的行数很少，会生成很多个临时文件，排序的性能会很差，所以如果单行很大，就尽量不要拿全部字段了，可以选择拿主键ID。

通过设置排序行数据的长度让MySQL修改其排序方式：

SET max_length_for_sort_data = 16;

此时执行流程变为以下所示：

初始化sort_buffer，确定放入两个字段，即name和id；
从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
从索引city取下一个记录的主键id；
重复步骤3、4直到不满足city='杭州’条件为止；
对sort_buffer中的数据按照字段name进行排序；
遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端（注意这里多扫描1000行，需要根据主键ID拿所需字段）。

全字段排序 VS rowid排序

在排序过程中，如果MySQL内存中无法放下全部字段才会选择rowid算法，rowid算法相比于全字段排序会增加磁盘的扫描过程，因此如果内存够，就要多利用内存，尽量减少磁盘访问。

是不是所有的order by都需要临时表进行排序呢，此时我们可以想一下struct的排序流程：当第一个字段相同，按第二个字段从小到大排序，这里也是一样的，我们可以构建联合索引（city，name），当第一个字段相同的时候，第二个字段是从小到大排序的，那么这里就不需要临时表来排序了，直接拿1000个出来就行了。

继续优化

回想前面索引部分将的覆盖索引：需要两个字段就构建两个字段的覆盖索引，所有这里可以直接构建三个字段的覆盖索引，这样就不需要拿主键id回表了，直接输出需要的字段就行了。但是这种方式构建的联合索引也很大，维护代价比较高，需要权衡决定是否使用。

思考

拥有联合索引(city,name)的城市用户表，这条语句是否需要排序，有什么方案可以避免排序？当需要输出第100页的内容如何处理？

select * from t where city in (“杭州”," 苏州 ") order by name limit 100;

虽然在单个city内部name是有序的，但是两个城市的name是没办法有序的，所以可以用两个语句进行一下结合输出100个：

执行select * from t where city=“杭州” order by name limit 100;这句话是不需要排序的，可以在客户端用一个长度为100的内存数组A存放结果。
同样的方法执行“苏州”的100条数据。
使用归并排序的思想得到前100小的值。如果要输出第100页的内容，只需要输出两个limit 10100的然后进行归并就行了。

17如何正确地显示随机消息

如果想在10000条数据中随机输出三条内容，可以采用order by rand()实现：

mysql> select word from words order by rand() limit 3;

但是这句话的操作流程还是很复杂的，设计到了内存临时表的知识。

内存临时表

执行排序操作包括全字段排序和rowid排序，对于InnoDB表来说，执行全字段排序会减少磁盘访问会被优先选择；但是对于内存表来说，回表只是简单地根据数据行的位置，直接访问内存得到数据，因此优化器会优先考虑roid排序。

所有执行上述语句的步骤为：

创建一个临时表，添加一个double类型字段和一个varchar类型，从words表中顺序取出所有word值，对于每个word，调用rand()函数生成一个大于0小于1的随机数，因此扫描行数是10000。
现在构建好10000行的临时表了，接下来初始化sort_buffer，一个字段是double类型，一个字段是整性。
从内存临时表中取出rand信息以及位置信息，过程中需要做全表扫描，此时扫描行数变为20000。
在sort_buffer中进行排序取出前三个，此时扫描行数变为20003。

我们可以查询slow.log来验证这一想法

# Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003 
SET timestamp=1541402277; 
select word from words order by rand() limit 3;

临时磁盘表

当内存大小超过tmp_table_size(限制内存临时表的大小)的时候，那么内存临时表会转为磁盘临时表。

针对limit 3的这种情况，由于输出的东西很少，其实InnoDB并不需要每次都排序全部的内容拿前三个，在这里InnoDB采用了优先队列的排序，只需要拿三个放进堆中，循环遍历堆就行了。在这里就并没有用到临时表来进行辅助。

当增加limit的数量，例如limit 1000的时候，由于超过了sort_buffer_size的大小，因此只能用归并排序算法(没超过的话才能选择使用优先队列)。

随机排序方法

直接在表中随机函数取值:

首先获得这个表的主键id的最大值M和最小值N;
用随机函数生成一个最大值到最小值之间的数X = (M-N)* rand() + N;
取不小于X的第一个ID的行。这个方法实际上是很高效的，每次只需要根据索引快速定位就行了，但是其问题也显而易见，例如：1、2、20000、20001这种情况的结果显然是个BUG。

根据行数去随机拿一个行出来

获得整个表的行数，记为C;
取得Y = floor(C * rand());
使用limit Y，1获得一行。这种方法最后会遍历Y + 1行，丢弃前Y行，因此总共需要遍历C + Y + 1行。

附：MySQL的执行过程都是由执行器进行调度的，在需要排序的时候，由执行器调用引擎的创建表接口实现，后面的读数据和写数据也是一样的，而排序这个操作是在server层进行的。

limit和offset

select * from table_name limit 10000,10 丢弃前10000条选出10条 
select * from table_name limit 0,10 直接选出10条

在offset变大后，limit的查询速度会很慢，这是为何呢？这个语句的执行过程是下面这样的：

首先从数据表中读取第N个数据到数据集中，需要读取10000 + 10条
根据offset丢弃前10000条
返回剩下的10条由上可知，需要读取10010条数据（全字段排序），前10000条是绝对没用的，白白浪费了很多次IO，所以需要减少IO次数，一共有两种方案：

修改max_length_for_sort_data使用rowid排序，这样每次不需要读取全部数据，减少了IO的消耗。
先找出需要数据的索引列，在通过索引列找出数据，同样也是减少IO的次数：

Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10; 
select * from table_name where( user = xxx ) limit 10000,10

18SQL语句差别解析

条件字段函数操作

举例：在一个交易系统中，假如要查询历史记录中7月份的所有数据，可以选择以下语句：

mysql> select count(*) from tradelog where month(t_modified)=7;

但是在执行过程中发现，这条语句做了全表扫描，原因是使用函数操作后改变了索引的顺序性，无法进行顺序判断（MySQL的B+树结构），图示如下：

如果执行where id = '2018-7-1'的话，会快速定位到合适的地方，该快速定位能力来源于同一兄弟结点的有序性，如果加入函数后，会破坏索引值的有序性，因此没办法利用索引。

附：例如select * from tradelog where id + 1 = 10000这种的虽然没破坏有序性，但是也没办法利用索引，要将其修改为id = 10000 - 1才可以。

隐式类型转换

关于类型不匹配的时候，例如存的是varchar(32)类型的，输入的参数却是整数(数据库比较的是整数，会将索引值转换为整数进行比较)，此时用到了类型转换函数，又和上面的那种情况一样了，所以没办法用索引了。

mysql> select * from tradelog where tradeid=110717;
相当于
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换

如果驱动表的字符集比被驱动表得字符集小，关联列就能用到索引,如果更大,需要发生隐式编码转换,则不能用到索引,latin<gbk<utf8<utf8mb4。所以要看我们对应的字符编码是否需要转换（前小后大情况比较好）。

19为什么有时候查询一行的语句也很慢

第一类查询长时间不返回的

例如查询以下语句，结果长时间不返回。

mysql> select * from t where id=1;

一般这种情况是表t被锁住了，分析原因的时候就要看表t是否被锁，可以执行一下show processlist命令查询语句的状态。

等MDL锁

有可能某个线程正在表t上请求或者持有MDL写锁，所以把查询语句堵住了。

这种情况可以查一下语句状态，把占有写锁的kill掉。

等flush

flush语法：清除或者重新加载内部缓存。如果使用flush语法必须有reload权限。一般flush语法有以下两种形式：

flush tables t with read lock; 
flush tables with read lock;

一般flush会很快，除非flush被某个线程堵住了，例如：

等行锁

和等MDL锁原理相似，当有某个线程修改对应行时事务不提交占了这个行锁，所以导致无法查询，可以通过sys.innodb_lock_waits 表查到：

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

第二类查询慢

未建立对应字段索引

经过了“锁”的限制后，还有一些情况同样会导致查询变慢：

如果某些查询字段没有建立索引的话，只能走主键顺序扫描，因此扫描时间会很长。

mysql> select * from t where c=50000 limit 1;

以上语句如果没有建立c字段的索引，则需要根据id扫描全部行。

MVCC搞的鬼

当session B执行完100万次update语句后，id=1这行在MVCC中版本太多了，要想恢复事务建立时候的版本需要回退100万次回滚日志，导致时间变长。

当使用select * from t where id=1 lock in share mode后速度就变快了，因为采用的是当前读，直接读到最后的结果。

20讲幻读是什么，幻读有什么问题

幻读是什么

设想以下3个session当只在id=5这一行加锁的话会怎么样：

在T5这一行读到了T4时刻增加的数据，这种现象被称为“幻读”，也就是说，幻读指的是一个事务在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行，但是“幻读”是有几个条件说明的：

在可重复读的隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的，因此幻读在“当前读”的情况下才会出现，
上面T3时刻的修改数据被select语句用“当前读”可以看到，不能成为幻读，幻读专指“新插入的行”。

注：当前读语句需要使用for update

幻读有什么问题？

首先是语义上的，上图中由于在T1时刻就已经声明要把d=5的行锁住，不准别的事务进行读写操作，但是后续如果将其他某行修改为符合T1时刻的语句，后修改的那一行其实是没有锁住的，因此T1时刻的语义就被破坏了。
第二个是数据一致性的问题，我们知道锁的设计是为了保证数据的一致性，这个一致性包含数据和日志逻辑的一致性，引入幻读操作后有可能修改语句写到了日志最后，在备库中执行的时候有可能会出错。

如何解决幻读？

为了解决幻读的问题，InnoDB引入了一个新的锁：间隙锁。例如插入6个数据后就会产生7个间隙锁：

因此当执行“当前读”语句的时候，不止对数据库中的记录加上了行锁，同时对7个间隙也加上了锁，但同时间隙锁之间是不冲突的，它们之间是覆盖关系：

session B并不会被堵住，因为数据中没有7，相当于A和B都是在保护(5-10]的区间，因此表示其中不允许插入值。

间隙锁和行锁合称next-key lock，每个next-key lock都是前开后闭的区间。

但是由于间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

请注意：间隙锁的使用是在可重复的隔离模式下的，当采用读提交的隔离模式就没有间隙锁这个概念的，但是还需要把binlog的格式设置为row。

MySQL实战45讲笔记