mysql实战45讲（15-22）

mysql实战45讲学习笔记

15 日志与索引的关系
15.1 日志

1，分析一下在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象。
如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。到这里，大家都可以理解。
大家出现问题的地方，主要集中在时刻 B，也就是 binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？我们先来看一下崩溃恢复时的判断规则。
1）如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
2）如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
a.  如果是，则提交事务；
b.  否则，回滚事务。
这里，时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。
2，binlog的完整性判断
一个事务的 binlog 是有完整格式的：
statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event。
另外，在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。所以，MySQL 还是有办法验证事务 binlog 的完整性的。
3，redolog和binlog关联】
它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：
如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。
4，redo log 设置多大
如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。
5，正常运行的实例，数据写入后落盘，是从redolog更新过来的还是从buffer pool更新过来的
实际上，redolog并没有记录数据页的完整数据，所以它并没有能力去自己更新磁盘数据页
1）如果是正常运行的话，数据页被修改后，变成脏页，最终落盘就是把脏页刷新到盘中，这与redolog毫无关系
2）在崩溃恢复中，如果一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存中，然后让redolog更新内存内容，更新完成后，内存页变成脏页，然后再刷新磁盘。

16 order by是怎么工作的
16.1 全字段排序
select city,name,age from t where city='杭州' order by name limit 1000  ;
这个sql里，city是有索引的。
mysql会给每个线程分配一块内存用于排序，称为sort_buffer
通常情况下，这个语句执行流程如下所示 ：
1）初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2）从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3）到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；4）从索引 city 取下一个记录的主键 id；
5）重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
6）对 sort_buffer 中的数据按照字段 name 做快速排序；
7）按照排序结果取前 1000 行返回给客户端。

排序的过程可能在内存中完成，也可以需要外部排序，这跟据排序所需内存和参数：sort_buffer_size(排序内存大小)。
如果排序内存小于所需要排序数据的大小，就会生成临时文件，mysql将需要排序的数据分为12份，每一份单独排序后存在这些临时文件中，然后把这12个有序文件再合并成一个有序文件。。
如果排序内存够用就不需要临时文件了。
16.2 rowid排序
上面那个排序方法，如果单行很大，会分成更多文件，效率不好。
mysql会根据max_length_for_sort_data参数判断单行长度。如果超过会改用另外一个算法
新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，
整个执行流程就变成如下所示的样子：
1）初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2）从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3）到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4）从索引 city 取下一个记录的主键 id；
5）重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6）对 sort_buffer 中的数据按照字段 name 进行排序；
7）遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
区别在于排好序后会回表再查一遍。

mysql一般认为只要内存够就多利用内存，尽量减少磁盘访问。
覆盖索引：索引上的信息足够满足查询的请求，不需要再回到主键索引上去取数据。
所以当有覆盖索引的查询的话就不需要前面两种那样麻烦了。

17 获取随机数
从一万行数据中随机取三个行。
mysql> select word from words order by rand() limit 3;
使用explain命令看下执行情况

可以看到Extra字段显示Using temporary，使用临时表，Using filesort需要排序。
这个sql，mysql会优先选择rowid排序。
这个sql的流程大致是：
1）新建一个临时表，注意这个表是没主键的。
2）把一万行数据的word取出来调用rand()函数，生成一个0-1之间的小数。此时扫描了1万行。
3）初始化sort_buffer，然后全表扫描，此时扫描行数就2万了。
4）在sort_buffer进行排序，然后取出前三个数。此时又要扫描3行，所以总扫描20003行
在有主键的时候，mysql会根据主键定位一行数据，在没有的时候，会自己生成6字节的rowid来作为主键，
所以，order by rand() 使用了内存临时表，又使用了rowid排序。
当然有时候临时表还存在于磁盘，当tmp_table_size(默认16M)设置太小的话，临时表就会存在磁盘中。
通过执行流程来看，会发现很麻烦的其实，也很浪费，因为排序了一万行，只用了3行。
17.1 优先队列算法
优先队列算法执行流程：
1）先取前三行，构成一个堆
2）取下一行，跟这个堆里的最大的比较，如果条件不成立，就替换掉堆里最大的
3）重复2的动作，一直排序
那上一篇order by文章中，为什么不用优先队列算法呢，因为那个sql是limit 1000，超过了sort_buffer_size大小，所以只能用归并排序算法。
17.2 随机排序方法
思路是：
取得这个表的主键 id 的最大值 M 和最小值 N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于 X 的第一个 ID 的行。
这样只需要扫描三行，但是这是不完善的，因为主键id可能不是顺序的，万一不连续的呢，X = (M-N)*rand() + N这个算出来的值随机概率并不是很大的。
17.3 随机排序方法2
1）取得整个表的行数，并记为 C。
2）取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
3）再用 limit Y,1 取得一行。
这样就解决了随机概率不均匀的问题。
当然，这些都并不是最佳的方案，还要根据业务实际情况，表的实际情况，做变动。

18 sql逻辑一样，性能相差很大
18.1 对索引字段做函数操作，可能会破坏索引值的有序性，无法走快速定位功能，因此优化器就决定放弃走树搜索功能，走全表扫描。
18.2 不注意的类型转换
select * from tradelog where tradeid=110717;
这里tradeid类型是varchar,这样的话，即便tradeid有索引，也会不走索引，为什么呢，
因为这个sql实际的语句是：
select * from tradelog where  CAST(tradid AS signed int) = 110717;
这里对tradeid做个函数，当然不走索引了。
18.3 因为字符集的问题导致
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
这个sql，tradelog走了索引，trade_detail没走索引。
因为tradelog字符集是utf8mb4，trade_detail字符集是utf8,优化器会把utf8的字符集加上函数转成utf8mb4，因为utf8mb4>utf8，向上转换，这个时候执行的真正语句是：
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 
这里用了CONVERT函数，也就是第一点的吗，不能给索引字段加函数。
但是如果sql改成：
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and d.id=2;
以trade_detail为驱动表(主表)，
优化器执行的是：
select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 
这里CONVERT函数是加在输入参数上的，这样就可以用上tradelog(被驱动表)的traideid索引了。
18.4 解决优化
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
18.4.1 修改字符集，改成一样的utf8mb4。
18.4.2 修改sql
select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
总结：这节最重要的就是sql不要在索引字段上加函数。

19 只查一行数据，为啥这么慢
mysql> select * from t where id=1;
19.1 查询长时间不返回的情况
原因一：等MDL锁
就是有个线程正在表上请求或者持有MDL写锁，所以堵住了。
解决：通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的process id，把这个连接用 kill 命令断开即可。
原因二：等flush
通过select * from information_schema.processlist where id=1;查询出来这个线程可能的状态是：Waiting for table flush，表示现在有一个线程正要对表做flush操作。
出现这个的原因是：有一个flush table命令被别的语句堵住了，然后它又堵住了我们的查询。
解决：也是一样，kill掉线程就行。
原因三：等行锁
19.2 查询慢
第一种，没有索引，导致扫描行数多。select * from t where c=50000 limit 1;
这个没啥，很好理解
第二种：select * from t where id=1；有索引，但是还是很慢
原因是：一致性读。

20 幻读
1，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询读到了前一次查询没有看到的行。
1）在可重复读隔离级别下(innodb默认)，普通的查询是快照读，是不会看到别的事务插入的数据的，因此，幻读在“当前读”下才会出现。
2）上面sessionB的修改结果，被sessionA之后的select 语句用“当前读”看到，不能称为幻读，幻读仅专指“新插入的行”。

查询加了for update，就是当前读。
2，幻读的问题
2.1 语义会被破坏
2.2 会导致数据不一致，即便所有记录都有行锁，还是阻止不了新插入的记录。
2.3 Innodb的解决
2.3.1 间隙锁(gap lock)
锁的就是两个值之间的间隙，就是在一个值更新同时又插入的时候，就会加上锁，组织插入

我们知道行锁分为读锁和写锁，读锁和写锁的关系：

但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作，间隙锁之间都不存在冲突。
间隙锁和行锁合称：next-key lock，每个next-key lock是前开后闭的。
间隙锁和 next-key lock 的带来的“困扰”：
会产生死锁
可能会导致同样的语句锁住更大的范围，这其实是影响了并发度
2.3.2 读提交
将事务隔离级别设置为读提交，并且binlog格式设置为row

21 加锁规则（可重复读隔离级别）
锁就是加在索引上的，这是基础设定。
1）原则1:加锁的基本单位是next-key lock，前开后闭区间
2）原则2:查找过程中访问到对象才会加锁
3）优化1:索引上等值查询，给唯一索引加锁的时候，只有行锁
4）优化2:索引上等值查询，向右遍历且最后一个值不满足等值条件的时候，退化为间隙锁
5）一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
具体理解看文章例子，不好做笔记：https://time.geekbang.org/column/article/75659

22 临时提高性能的方法
1，短连接
1.1 先处理掉那些占着连接但是不工作的线程
怎么判断空闲连接呢：
执行show processlist 看那些线程处理sleep中，看事务具体状态的话，可以查information_schema库的innodb_trx表：trx_mysql_thread_id=？谁就是在事务中
然后kill connection + id命令杀死线程
1.2 减少连接过程的消耗
跳过权限验证：重启数据库，并使用-skip-grant-tables参数启动，这样就会跳过所有的权限验证阶段，8.0后，数据库默认把-skip-networking参数打开，表示数据库只能被本地客户端连接，
跳过权限验证风险很大，慎用。
2，慢查询
2.1 索引没设计好
重新设计索引：1）在备库b上执行set sql_log_bin=off，关闭binlog,然后执行加索引。
2）执行主备切换
3）然后这个时候主库是b，备库是a，在a上执行set sql_log_bin=off，然后加索引。
2.2 语句没写好
检查sql,看看是不是由于函数原因没走索引，优化sql
2.3 mysql选错索引了
上线前在测试环境模拟线上，打开慢查询日志，long_query_time = 0，然后检查sql走的索引，没有按期望的话那就使用force index
3，QPS突增问题
1）一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
2）如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
3）如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。