MySQL45讲学习日记
MySQL45讲学习日记
参考学习资料：极客时间 MySQL实战45讲（丁奇）
⚡1.基础架构：一条查询语句是如何执行的
MySQL可以分为 server层和存储引擎层。
server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
存储引擎负责数据的存储和提取。默认使用innodb，在建表时可以自行选择
连接器
连接器负责跟客户端建立连接，获取权限，管理和维持连接。
在使用命令行窗口连接mysql的时候，这个窗口就属于客户端工具，用来跟服务端建立连接，在经过tcp握手后，连接器就要开始认证身份，需要输入账号和密码
（1）如果密码/账号不正确，会提示Access denied for user ，然后客户端程序结束执行。
（2）验证正确，连接器会到权限表里查询用户拥有的权限，此处连接内所有的权限判断都基于本次查询。这也意味着，一个用户建立连接之后，即使你用管理员账户修改了该用户的权限，也是下次连接才生效。
链接建立之后，如果客户端长期没有操作，连接器会将连接断开。这个是由wait_timeout参数控制的，默认值是8h。数据库里连接建立之后，客户端一直有请求，则一直使用同一个连接。 短连接是指建立连接之后，只执行很少的几次操作就断开连接，下次操作重新建立连接。
由于建立连接的操作比较复杂，所以一般使用长连接。但是使用长连接可能会出现，mysql占用内存涨的特别快，这是由于mysql在执行过程中使用的内存是管理在连接对象里的。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了
如何解决？
- 定期断开长连接。使用一段时间或者进行一次占用内存的大查询之后，就断开连接，需要查询再重新连接
- 如果使用5.7或者更新版本，可以在大查询后执行 mysql_reset_connection ，这个操作，不需要重连，但是会重置为连接刚刚建立的状态
查询缓存
连接建立后，进入查询缓存。mysql拿到一个查询请求后会先到查询缓存看看，之前是否执行过这条语句，之前执行过的查询会以key，value的形式保存在内存中，key是查询语句，value是查询结果。如果查询语句能够在这个缓存中找到，那么这个value就会被直接返回给客户端
但是不建议用查询缓存，因为一旦某个表做了更新，那么这个表上的所有查询缓存都会被清空注：mysql8.0已经把这个功能整个删除了
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存
分析器
如果没有命中查询缓存，则开始真正执行语句，需要对SQL语句进行解析（词法分析、语法分析）
- 词法分析：分析每个单词的含义
- 语法分析：分析sql语句是否正确
优化器
经过分析器，mysql就知道要做什么，在开始执行之前还需要经过优化器的处理
优化器主要是决定使用哪个索引和表的连接顺序，即优化器是确定语句的执行方案
执行器
开始执行之前，需要先判断是否有这个表的查询权限，没有则返回报错，有权限则打开表执行。
没有利用到索引：调用innodb引擎接口取这个表的第一行，判断是否满足条件，是将这行保存在结果集中调用引擎取下一行，充足判断，一直到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端。
⚡2.日志系统：一条更新语句是如何执行的
和查询语句执行过程很像，连接器，分析器，优化器，执行器，不过和查询不同的是，更新流程还涉及到日志模块（binlog归档日志，redolog重做日志）。
redolog
场景引入：何为粉板记账？例如赊账的时候一般做法是直接翻账本，然后将赊账记录直接修改；而基于这个场景就会导致每次都要去翻找账本找到对应的人再计算随后修改结果，效率极为低下。但如果借助粉版则可将本次赊账动作记录下来，待到打烊之后再将结果写回账本。
类似地，如果MySQL的每一次更新操作都要写进磁盘，则需磁盘找到对应记录然后更新，整个IO操作、查找成本都很高，为了解决这个问题，MySQL的设计者采用类似“酒店掌柜粉板”的思路来提升更新效率。而粉版和账本配合的整个过程，则为MySQL的WAL技术（Write-Ahead Logging）：其关键在于先写日志后写磁盘
当有一条记录需要更新的时候，Innodb引擎就会先把记录写到redolog里面，并更新内存，这个时候更新就完成了，同时innodb引擎会在适当的时候（一般是在系统比较空闲的时候），将这个操作记录更新到磁盘里面。
基于上述场景，思考一个问题，如果出现“粉板满了”的情况该如何处理？例如某日的要处理的东西不多则可等空闲的时候处理，但是如果某日要处理的内容非常多，则需要先更新一部分数据到账本中，随后腾出粉板空间。基于redolog，InnoDB可以保证及时数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe（即只要记录在粉板上的数据，就算数据库异常重启，也可根据粉板记录去恢复数据）
innoDb的redolog是固定大小的，从头开始写，往复循环，写满的时候就必须要更新库里，删除部分记录才能继续写入。
binlog
redolog日志是innodb引擎特有的日志，其他引擎无法使用，server层自己的日志称为 binlog（归档日志），但是binlog并不具有cash-safe能力。（MySQL自带引擎是MyISAM，它是没有carsh-safe能力的，而binlog只能用于归档，InnoDB是另一个公司以插件形式引入到MySQL扩展应用的）
binlog & redolog的区别
- redo是innodb引擎特有的，binlog是mysql的server层实现的，所有引擎都能用
- redolog是循环写的，binlog是可以追加写入，追加写是指binlog文件写到一定大小后，会切换到下一个，并不会覆盖之前的日志
- redo是物理日志，记录的是 “某个数据越上做了什么修改”，binlog是逻辑日志，有两种格式statment格式记录sql语句，row格式记录更新前后行的内容
基于上述对redolog和binlog的概念理解，详细看下执行器在执行update语句的流程，update T set c=c+1 where ID=2;
- 执行器先找引擎取id=2这一行，引擎直接用树搜索找到这一行，如果这一行本来就在内存中，就直接返回给执行，否则需要先从磁盘读入内存，然后再返回
- 执行器拿到引擎给的行数据，把这个值加上1，比如原来是n，现在就是n+1得到新的行数据，再调用引擎接口写入这行新数据
- 引擎将这行数据更新到内存中，同时将这个更新操作记录到redolog里面，此时redolog处于prepare状态。然后告知执行器执行完成了，随时可以提交事务
- 执行器生成这个操作的binlog，并把binlog写入磁盘
- 执行器调用引擎的提交事务接口，引擎把刚刚写入redolog改成提交状态，更新完成
最后三步就是两阶段提交，其目的和事务类似就是为了让两个日志文件对应的保持一致，恢复的数据保持一致。如果有任意一个日志在生成工程中出现异常那么两个日志里关于更新这次都将不能成功生成，这次更新操作也会失败回滚。
⚡3.事务隔离：为什么你改了我还是看不到
数据库访问的问题
脏读：读到了未提交的数据
不可重复读：事务刚开始读到数据与事务之后读取到的数据不同
幻读：事务中读到数据，与开始读到数据多或者少
数据库的隔离等级
读未提交：一个事务还没提交时，他做的变更就能被别的事务看到
读提交：只能读提交了的数据。一个事务只有提交之后，他做的变更才会被其他事务看到。
可重复读：一个事务执行过程中看到的数据总是和这个事务在启动时看到的数据是一致的
串行化：能够避免任何事务问题，但是效率最低。（即对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行）
事务隔离的实现
在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。
事务的启动方式
如前面所述，长事务有这些潜在风险，建议尽量避免。其实很多时候业务开发并不是有意使用长事务，通常是由于误用所致。MySQL的事务启动方式有以下几种：
- 显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。
- set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。
有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。
因此，建议你总是使用set autocommit=1, 通过显式语句的方式来启动事务。
但是有的开发同学会纠结“多一次交互”的问题。对于一个需要频繁使用事务的业务，第二种方式每个事务在开始时都不需要主动执行一次 “begin”，减少了语句的交互次数。如果你也有这个顾虑，我建议你使用commit work and chain语法。
在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。
你可以在information_schema库的innodb_trx这个表中查询长事务，比如下面这个语句，用于查找持续时间超过60s的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
⚡4.索引深入浅出上
索引的作用
索引是为了提高数据查询的效率，就像书的目录一样。
索引的常见模型
实现索引的方式有很多种，一般把实现索引的方式称为索引模型，可以提高读写效率的数据结构很多，这里介绍下三种最常见，比较简单的数据结构：哈希表，数组和搜索树。
哈希表
哈希表是一种以键值对存储数据的结构，只要输入待查找的key，就可以找到对应的value。哈希表的思路很简单，把值放在数组中，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。不可避免的，多个key值讲过哈希函数的换算，会出现同一个值的情况，处理这种情况的一种方法是，拉出一个链表。
以身份信息表（身份证号、姓名）为例子：根据身份证号查找对应的名字，如果根据哈希索引查找，则如果出现ID_CARD_02、ID_CARD_04计算出来的值指向的都为N，则根据其后的链表继续检索，例如要查出ID_CARD_N2则通过哈希函数计算出N，随后继续按顺序遍历找到User2
需要注意的是，ID_CARD_N的值不是递增的，增加新的User速度会快，只需要往后追加即可，但是其缺点在于不是有序的，因此用哈希索引做区间查询的速度很慢，如果要在查找范围数据，就必须要全部扫描一遍。所以哈希表这种结构只适用于只有等值查询的场景，例如Memcached及其他一些NoSQL引擎。
有序数组
而有序数组在等值查询和范围查询场景中的性能非常优秀。
如果是身份信息存储，基于有序数组存储，如果身份证号没有重复，这个数组就是按照身份证号递增的顺序来保存的，这个索引结构支持范围查询，可以通过二分法快速搜索。
如果仅看查询效率，有序数组是最好的数据结构，但是数据更新的时候可能会比较麻烦，例如往中间插入一个数据就必须要挪动后面的所有记录，成本太高
所以有序数据索引只适合静态存储引擎，保存不会再修改的数据
二叉搜索树
二叉搜索树的特点是：每个节点的做儿子小于父节点，父节点又小于右儿子。查找的时间复杂度是O（logN）
为了维持O（logN）的查询复杂度，就需要保持这课树是平衡二叉树，更新的时间复杂度也是O（logN）
但是大部分的数据库存储并不使用二叉树，原因是索引不止保存在内存中，还要写到磁盘上。如果只使用平衡二叉树，树的高度可能会很高，查询的速度也会很慢。
为了让一个查询尽量的少读磁盘，就必须让查询过程访问尽量少的数据库，那么我们就不应该使用二叉树，而是要使用N叉树，这里的N取决于数据块的大小。N叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
在日常学习中要有一个概念，就是数据库底层存储的核心就是基于这些数据模型的，每碰到一个新数据库，需要先关注它的数据模型，这样才能在理论上分析出这个数据库的适用场景。
InnoDB 的索引模型
在Innodb中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，又因为Innodb使用了B+树索引模型，所以数据都是存储在B+树中的。
每个索引在Innodb里面对应一颗B+树。
主键索引的叶子节点存的是整行数据，在InnoDb中，主键索引也称为聚簇索引
非主键索引的叶子节点内容是主键的值，在InnoDb中，非主键索引也被称为二级索引
基于主键索引和普通索引的查询有什么区别
如果是主键查询方式，那么只需要搜索主键这颗B+树
如果是普通索引，查询到的是主键的值，需要到主键索引树中再搜索一次，这个过程称为回表，也就是说非主键索引的查询要多扫描一颗索引树，所以我们在应用中应当尽量使用主键查询
索引维护
为了维护索引有序性，在插入新值时需要做必要的维护，已有的数据页满了的话，插入时数据时需要申请新的数据页，然后挪动部分数据过去，这个过程称为页分裂，性能会收到影响，整体的空间利用率也会降低。
使用自增主键的优点
- 能保证有序插入，每次插入 一条新记录都是最佳操作，不涉及挪动其他记录，也不会触发叶子节点的分裂
- 自增组件的长度比较小，非主键索引的叶子节点都是主键的值，普通索引的叶子几点就越小，普通索引占的空间也比较小。
上期问题：如何避免长事务
- 确认是否使用了 set autocommit = 0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。
- 确认是否有不必要的只读事务，给查询语句加了事务
- 设置语句的最长执行时间，避免单个语句意外执行太长时间
⚡5.索引深入浅出下
问题场景：在下面的表中，执行select * fromT where k betweent 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
分析SQL的执行流程：
（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，不满足条件，循环结束
在这个过程中，回到主键索引树搜索的过程称为回表。可以看到，这个查询过程读了k，索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
覆盖索引
覆盖索引：如果执行的语句是select ID fromTwhere k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，因此使用覆盖索引是一个常用的性能优化手段
场景分析：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
身份证号是市民的唯一标识，如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？但是如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务DBA，或者称为业务数据架构师的工作。
最左前缀原则
基于上述内容，思考一个问题，如果每一种查询都设计一个索引，那索引是不是太多了？如果现在要按照市民的身份证号去查他的家庭地址呢？虽然这个查询需求在业务中出现的概率不高，但总不能让它走全表扫描吧？反过来说，单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费，应该怎么做呢？
B+树的索引结构，可以利用索引的“最左前缀”来定位记录，以（name，age）联合索引分析
索引项是按照索引定义里面出现的字段顺序排列的，当逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果
如果你要查的是所有名字第一个字是“张”的人，SQL语句的条件是"where name like‘张%’"，这时能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
基于上面对最左前缀索引的说明，来讨论一个问题：在建立联合索引的时候，如何安排索引内的字段顺序
此处评估标准为：索引的复用能力，因为可以支持最左前缀，所以当有（a，b）联合索引，一般情况不需要单独在a上建立索引，因此第一原则是：如果可以通过调整顺序，可以减少维护一个索引，那这个索引可以优先考虑采用
继续思考一个问题：如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句无法使用（a，b）联合索引，此时不得不维护另外一个索引，也就是说需要同时维护（a，b）、（b）这两个索引，这个时候考虑的原则则为空间。例如市民表的情况，name字段比age字段大，则可以考虑创建一个（name，age）的联合索引和一个（age）的单字段索引。
索引下推
基于上述最左匹配原则，最左前缀可以用于在索引中定位记录，那如果不符合最左前缀的部分会怎样？
以市民表的联合索引（name，age）为例，如果有一个场景：检索表中“名字第一个字是张，而且年龄是10岁的所有男孩”（select * from tuser where name like '张%' and age=10 and ismale=1;）
基于前缀索引规则，这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID3。当然，这还不错，总比全表扫描要好。然后继续判断其他条件是否满足。
在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值
而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
无索引下堆的执行流程：
在(name,age)索引里面特意去掉了age的值，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此需要回表4次。
有索引下堆的执行流程：
InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。因此在此处的案例中只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。
6.全局锁和表锁
Mysql的锁大致可以分为全局锁，表锁和行锁。
全局锁
全局锁就是对整个数据库实例加锁，mysql提供了一个加全局锁的方法，命令是 Flush tables with read lock （Ftwrl），当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的，数据库更新语句（增删改），数据库定义语句（建表，修改表结构），更新类事务的提交语句，都会被阻塞。
全局锁的使用场景
做全库逻辑备份，就是把整个库每个表都select出来存成文本。
存在的问题：如果在主库上备份，数据不能更新，业务就要停了。
如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。
如果允许数据库在备份的时候不加锁,会怎样？
会导致备份的数据和实际的数据存在误差。
如果在可重复读级别下开启一个事务导数据，且数据库引擎支持这个隔离级别，由于Mvcc的支持，这个过程数据是可以正常更新的。
既然是全库只读，为什么不适用 set global readonly =true
readonly确实可以让全库进入只读状态，但是还是建议使用FTERL
- 在有些系统中，readlonly的值会被用来做其他逻辑，比如用来判断一个库是从库还是主库，因此修改global变量的方式影响面更大。
- 在异常处理机制上有差异，如果执行FTWRL命令之后，客户端如果发生异常，那么mysql会自动释放这个全局锁，整个库回到可以正常更新的状态，而将整个库设置为readonly之后，如果客户端发生异常，数据库会一直保持在readonly的状态，导致整个库长时间处于不可写的状态
表级锁
mysql表级锁有两种，一种是表锁，一种是元数据锁（meta date lock）
表锁
表锁的语法是 lock tables ... read/write,可以unlock tables主动释放锁，也可以在客户端断开的时候自动释放，需要注意的是，lock tables语法除了限制其他线程的读写外，也限定了本线程接下来的操作对象。
举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
元数据锁MDL（metadate lock）
MDL不需要显式的使用，在访问一个表的时候会自动加上，MDL的作用是保证读写结果的正确性。
在mysql5.5版本引入了MDL，当对一个表进行增删改查操作的时候，加MDL读锁，当对表做数据表结构变更的时候，加MDL写锁
- 读锁之间不互斥，可以有多个线程同时对一张表进行增删改查操作。
- 读锁和写锁，写锁和写锁之间是互斥的，用来保证变更表结构的操作的安全性，如果有两个线程同时要给一个表加字段，那么要等其中一个线程执行完之后，才能执行另一个。
MDL虽然是默认加的，但是有个机制却不能忽略，否则在操作数据库时会掉坑里。
事务中的MDL锁，在语句执行的时候开始加锁，但是语句结束之后并不会马上释放，而是等整个事务提交之后再释放。
那么如果在一个事务中既有增删改操作又有对数据表结构操作的语句，
可以看到session A先启动，（session A这里开启了事务，如果没有加begin，session A执行完会自动提交），这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。
之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
如何安全给小表加字段
- 解决长事务，事务一直不提交就会一直占着MDL锁，如果你要做ddl更新的表刚好有长事务执行，要考虑暂停ddl或者kill掉这个事务。
在执行中的事务可以在MySQL的information_schema 库的 innodb_trx 表中
- 如果是高频访问的表，kill事务可能没用，因为新的请求马上来了，可以在ddl语句中添加等待时间，如果在等待时间内能拿到MDL写锁最好，拿不到就后面重试执行。
7.行锁功过：怎么减少行锁对性能的影响？
8.事务到底是隔离的还是不隔离的
一个疑问
如果是可重复读隔离界别，事务t启动的时候会创建一个视图，之后事务t执行期间，即使有其他的事务修改了数据，事务t看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像是独立的，不会与其他事务产生交集。
但是在行锁中，一个事务要更新一行，如果刚好有另一个事务拥有这一行的行锁，它就会被锁住，进入等待状态。那么等到这个事务自己获取到行锁，要更新数据的时候，它读到的值又是什么呢？
详解MVCC
事务的启动时机
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot 这个命令。
在可重复读隔离级别下，事务在启动的时候就拍了个快照，这个快照是基于整个库的。但是并不是拷贝整个库。
InnoDb里面每个事务有一个唯一的事务id，叫transaction id，它是在事务开始的时候向InnoDb的事务系统申请的，是按申请顺序严格递增的。而每行数据也都是有多个版本的，每次事务更新数据的时候，都会生成一个新的数据版本，并且把这个transaction id赋值给这个数据版本的事务id，记为row trx_id,同时旧的数据版本保留，并且在新的数据版本中，能够有信息可以直接拿到它。
也就是说数据库的每一行记录，可能有多个版本，每个版本有自己的row trx_id。
历史版本的数据并不是在屋里上真实存在的，而是每次需要的时候根据当前的版本和undo log计算出来的。
我们在回头看下，这个InnoDb的事务在启动时的快照是怎么实现的，
按照可重复读的定义，一个事务启动的时候，以启动的时刻为准，在此之前生成的数据，可见，在此之后生成的数据不可见。
在实际上，InnoDb为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在活跃的事务id。活跃指的就是启动了还未提交。
小于这个数组最小的row trx_id,说明这个事务是已提交的事务或者是当前事务自己生成的，这个数据是可见的。
如果大于 数组中最大的row trx_id 说明这个版本是由将来启动的事务生成的，不可见。
如果在数组中，说明是由还未提交的事务生成的，不可见。
所以InnoDb就是利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力。
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
- 版本未提交，不可见；
- 版本已提交，但是是在视图创建后提交的，不可见；
- 版本已提交，而且是在视图创建前提交的，可见。
回到最开始的问题，一个疑问的那个问题。
在更新数据的时候需要用到一个当前读的概念
当前读：更新数据都是先读后写的，而这个读只能读当前最新的值，称为当前读
那如果事务c更新之后没有立即提交呢，在提交前事务b发起了更新，这个时候事务b会怎么处理？
这个时候两阶段锁协议就上场了，事务c没提交，那么他的写锁还没有释放，事务b的当前读必须要加锁，所以就要等事务c释放这个锁，才能继续它的当前读。
9.普通索引和唯一索引如何选择
查询过程：
对于普通索引来说，查找 到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录
对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会 停止检索
但是性能几乎没有差距
更新过程
比较更新之前先了解下 change buffer的概念
当需要更新一个数据页的时候，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDb会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了，如果下次查询需要访问这个数据页，将数据页读入内存，然后执行change buffer中与合格页有关的操作，通过这个方式就能保证这个数据逻辑的正确性。
虽然名字叫change buffer，但是实际上它是可以持久化的数据，也就是说change buffer在内存中有拷贝，也会被写入到磁盘上。
将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭的过程中，也会执行merge操作。在数据库正常关闭的过程中，也会执行merge操作。
那什么时候使用到这个change buffer呢
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束，比如要插入某条记录，就要先判断表中是否已经存在k=4的记录，而这必须要将数据页读入内存中才能判断，如果已经读到内存，那直接更新内存会更快，没有必要使用 change buffer。
此处更新是先写入内存，再写入redo log日志，所以此时数据库宕机也不会影响到数据。
因此唯一索引的更新不会用到change buffer，实际上只有普通索引可以使用。
change buffer占用的是内存的容量，所以 不能无限增大，可以通过参数innodb_change_buffer_max_size来动态设置，50表示，大小最多占用mysql内存50%的大小。
当更新的目标页不在内存中时
- 对唯一索引来说，需要将数据页从磁盘读取到内存，判断到没有冲突，插入这个值，语句执行结束。
- 对普通索引来说，将更新记录在change buffer中，语句 执行就结束了。
但是将数据从磁盘读取到内存是，涉及随机IO的访问，是数据库中成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升很明显。
change buffer的使用场景
普通索引的所有场景，使用change buffer都可以起到加速作用吗？
如果是写多读少的操作，此时使用change buffer的收益最大，因为在merge之前进行了多次的变更，节省了很多次访问磁盘的操作。
如果是读多写少的业务，写完之后可能立马就会查询，就会触发merge操作，这样随机访问IO的次数没有减少，反而增加了维护 change buffer的代价，此时使用change buffer 反而起到了副作用。
WAL和change buffer的区别
WAL采用了redo log 节省了随机写磁盘的IO消耗，转为了顺序写redo log日志
change buffer 节省了随机读磁盘的IO消耗，（这里我不太理解，change buffer 让mysql在做更新操作的时候可以直接写入到内存中的 change buffer中（数据页不在内存中），不需要到磁盘中查询，但是在merge之后，还是要写入到磁盘中的吧，那么它是不是也节省了随机写磁盘的IO消耗呢。
10.MySQL为什么有时候会选错索引？
11.怎么给字符串字段加索引？
12.为什么我的MySQL会“抖”一下？
13.为什么表数据删掉一半，表文件大小不变？
14.count(×)这么慢，我该怎么办？
15.答疑文章（一）：日志和索引相关问题
多版本并发控制
MVCC：同一个事务在数据库中可以存在多个版本。
在mysql中，世界上每条记录在更新的时候队徽同时记录一条回滚操作，记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从1按顺序该成了2,3,4，在回滚日志里面就会有类似下面的记录。
当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图a，b，c里面这一个记录的值分别为1,2,4。同一条记录在系统中可以存在多个版本就是数据库的多版本并发控制，对于read-view A ，要得到1，就必须将当前值一次执行图中所有的回滚操作得到。
尽量不要使用长事务
长事务意味着系统中会存在很老的事务视图，由于这些事务随时可能访问数据库中的任何数据，所以在这个事务提交之前，数据库里面可能用到到回滚记录都必须保留，这就会导致大量占用存储空间。
在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有20GB，而回滚段有200GB的库。最终只好为了清理回滚段，重建整个库。
16.“order by”是怎么工作的？
17.如何正确地显示随机消息？
18.为什么这些SQL语句逻辑相同性能却差异巨大？
19.为什么我只查一行的语句也执行这么慢？
20.幻读是什么幻读有什么问题？
21.为什么我只改一行的语句锁这么多？
22.MySQL有哪些“饮鸩止渴”提高性能的方法？
23.MySQL是怎么保证数据不丢的？
24.MySQL是怎么保证主备一致的？
25.MySQL是怎么保证高可用的？
26.备库为什么会延迟好几个小时？
27.主库出问题了从库怎么办？
28.读写分离有哪些坑？
29.如何判断一个数据库是不是出问题了？
30.答疑文章（二）：用动态的观点看加锁
31.误删数据后除了跑路还能怎么办？
32.为什么还有kill不掉的语句？
33.我查这么多数据会不会把数据库内存打爆？
34.到底可不可以使用join？
35.join语句怎么优化？
36.为什么临时表可以重名？
37.什么时候会使用内部临时表？
38.都说InnoDB好那还要不要使用Memory引擎？
39.自增主键为什么不是连续的？
40.insert语句的锁为什么这么多？
41.怎么最快地复制一张表？
42.grant之后要跟着flush privileges吗？
43.要不要使用分区表？
44.答疑文章（三）：说一说这些好问题
45.自增id用完怎么办？
结束语.点线网面一起构建MySQL知识网络