MySQL优化高手笔记

语雀完整版：

https://www.yuque.com/g/mingrun/embiys/dv3btw/collaborator/join?token=zMBwPzSMfSGINLuv&source=doc_collaborator# 《MySQL优化高手笔记》

MySQL优化高手

一、MySQL架构

01 天天写CRUD,你知道你的系统是如何跟MySQL打交道的吗

通过驱动连接数据库，而且会创建多个连接，可通过连接池进行管理，避免频繁重复创建连接，浪费资源

02 为了执行SQL语句,你知道MySQL用了什么样的架构设计吗

除连接数据库维护一个连接池外，数据库本身也会维护一个连接池，其中有一个不变的原则，既然是网络连接，那就一定会有一个线程去监听处理，Mysql维护了一个sql接口用以接收各种语句，然后Sql接口将语句交给Sql解析器进行解析，然后找查询优化器选择一个最优的查询路径（好比到达终点有很多条路，选择最优的那个方案，特别是复杂大Sql）。

优化器选择好执行路径后，就交给存储引擎（去哪执行，去哪查，怎么执行）去真正的执行，

除此之外还有执行器，是他拿着优化器生成的执行方案去调用存储引擎。

03 用一次数据更新流程，初步了解InnoDB存储引擎的架构设计

04 借着更新语句在InnoDB存储引擎中的执行流程，聊聊binlog是什么

binlog与redo log不同，它不属于innodb这一个引擎，而是属于整个MySql Server，其中前四步是执行sql，5、6步是提交事务，redo log和bin log写入磁盘。
sync_binlog：是控制binglog刷磁盘的策略，默认是0，先进入Oscache，设置为1测试直接刷入磁盘。
当binlog 刷入磁盘后就算是事务提交完毕了，然后还需要在redo log中写入一个commit标记，和binlog的文件名、路径，写入一个commit标记是为了确保binlog已经刷入了磁盘。

接着就是一个Io线程随机的把脏数据缓存池中的数据刷回磁盘，如果此时宕机也不会造成问题，I/O线程依然可以根据redo把数据找回数据。

05 生产经验:真实生产环境下的数据机器配置如何规划

1. Java应用系统： 2c4g -> 4c8g  >> 一两百、每秒500左右

1. 数据库： 8c16g -> 16c32g  >> 一两千、三四千

1. 对于Java系统I/O操作比较少，耗时的一般是数据库，他会对磁盘文件进行大量的I/O操作，同时还有网络传输上的耗时。

06 生产经验:互联网公司的生产环境数据库是如何进行性能测试的

1. 最好对数据库先做压测，因为java系统可能和数据所能承载的访问量不一样，尝试每秒发送一千、两千个请求。

1. QPS（Query Per Second）和TPS（Transaction Per Second）：前者是每秒处理多少个请求，或者说数据库执行多少个SQL语句。 TPS则是每秒可以处理的提交或回滚事务。

1. 然后再从I/O指标，CPU负载，网络负载，内存负载，进行压测。

07 生产经验:如何对生产环境中的数据库进行360度无死角玉测

使用 sysbench  对数据库进行360全方位压测。

08 生产经验:在数据库的压测过程中.如何360度无死角观察机器性能

1. 逐渐加大请求量，观察机器的状态

1. top命令观察cpu负载，内存情况，dstat -d命令查看I/O吞吐量，dstat -n  查看网络流量情况。

09 生产经验:如何为生产环境中的数据库部署监控系统

Prometheus（数据采集）+Grafana（报表）：实现一套可视化监控系统

10 生产经验:如何为数据库的监控系统部署可视化报表系统

Prometheus（数据采集）+Grafana（报表）：实现一套可视化监控系统

二、Buffer Pool

11从数据的增删改开始讲起，回顾一下Buffer Pool在数据库里的地位

1. buffer pool是一个非常重要的组件，一般大磁盘的随机读写可能要几百毫秒，如果直接查找磁盘效率很低

1. 增删改的操作首先操作的就是buffer pool，同时结合了 redo log，刷盘机制，

12 Buffer Pool这个内存数据结构到底长个什么样子

1. 128兆默认的有点小 --> 15c32g（推荐2g）

1. 抽象的数据结构 -> 数据页：每一页中放了许多行

1. 磁盘中的数据每一页16kb,与此对应的缓存页也是16kb

1. 描述数据在缓存页前面，包含表空间、数据页的编号、此页在buffer pool中的地址等信息，在buffer pool中描述数据大概相当于缓存页的5%。

   

13从磁盘读取数据页到Buffer Pool的时候，free链表有什么用

1. 初始化buffer pool：初始化内存空间，每页16kb何其对应的800字节的描述数据，然后数据库运行时，执行增删改时再将数据加载进buffer pool里面

1. 哪些数据页是空闲的？
   buffer pool拥有一个Free 链表(双向)，他存放了空闲的缓存页的描述数据块的地址

1. 将磁盘中的数据加载进buffer pool：从链表中获取一个空闲的缓存页，然后就将数据页和对应的描述信息写入，最后再将这个节点从缓存页中删除。

   

1. 判断数据页是否已被缓存：

   

14当我们更新Buffer Pool中的数据时，flush链表有什么用

脏页：指那些在缓存池中修改，尚未刷回磁盘的数据，而有的缓存页是查询产生的，不需要刷回磁盘，于是就有了flush链表，他存放的是被修改过的缓存页的描述数据的地址（指针）。如果某个缓存页被修改，那么他就会加入这个filush 链表。

15当Buffer Pool中的缓存页不够的时候，如何基于LRU算法淘汰部分缓存

1. 把修改过数据的缓存页刷新到磁盘中去，腾出来新的空闲缓存页

1. 缓存命中率： 只从缓存中操作的次数/总请求次数， 可根据命中率决定淘汰哪个。

1. LRU链表：最近使用过的放在链表头部，然后就淘汰链表尾部的。

16简单的LRU链表在Buffer Pool实际运行中，可能遇到哪些问题

1. 预读机制

1. 1. 预读机制带来的问题：从磁盘加载数据页的时候，也会把它的左右邻居带上，并且会在LRU链表的前面，但实际上它的邻居并没有被访问，而合理的情况下是要把这些没人访问的邻居给清理掉。
   2. 触发预读机制的情况：
      innodb_read_ahead_threshold ，最多连续访问了相邻的56（默认）数据页，就会触发。
      innodb_random_read_ahead  ，默认Off，存放了13个相邻的，频繁访问，就触发。

1. 全表扫描（select * 并且没有where条件）
   会将这个表所有的数据页加载进缓存页里，占用lru链表的头部。

17 MySQL是如何基于冷热数据分离的方案，来优化LRU算法的

1. 设计预读就是感觉你连续访问了很多相邻页，那么很有可能接下来还是，理想很丰满，就是怕实际上没人访问。

1. 而mysql中因为上述的问题会将 lru链表分为冷热两个部分，冷数据由nnodb_old_blocks_pc,控制，默认占37%。

1. 数据页第一次被放到缓存的时候，就会被放到冷数据区的头部， 此后innodb_old_blocks_time (默认1s) 后，如果还有访问就放到热区头部，就是为了防止你只用了一次放到热区

   

18 基于冷热数据分离方案优化后的LRU链表，是如何解决之前的问题的

完美

19 MySQL是如何将LRU链表的使用性能优化到极致的

1. 热数据区节点移动优化：
   为了防止移动过于频繁，实际上只有后3/4 的数据被访问了才会被移动到链表头部

20 对于LRU链表中尾部的缓存页，是如何淘汰他们刷入磁盘的

1. 使用回顾：从磁盘中加载一个缓存页，就会从free链表移除这个缓存页，然后在lru冷数据 头部加入这个缓存页。
   如果修改了一个缓存页，那么flush列表就会记录这个脏页，而且还可能会把这个脏页从lru冷数据头部移动到热数据头部。

1. 在执行CRUD操作时，如果缓存页已满则会把一些缓存页刷入磁盘，而刷入磁盘页也会有几个时机

1. 将LRU尾部缓存页刷入磁盘的几个时机：

• • 不等缓存页用完，有个定时任务，将lru尾部的缓存页刷入磁盘从flush链表中删除，加入free链表。

• • 定时将flush链表中的缓存页刷入磁盘，从而将其从lru链表中移除。

21 生产经验: 如何通过多个Buffer Pool来优化数据库的并发性能

1. buffler pool本质上就是一大块内存区域，里面存放了数据页和描述数据块，同时有free、flush、lru来辅助它的运行。

1. 多个线程操作buffer pool是要排队的，性能肯定会下降，所以可以调大内存设置多个buffer pool, 有多少个buffer pool ，每个buffer pool的大小。

   

22 生产经验: 如何通过chunk来支持数据库运行期间的Buffer Pool动态调整

1. 不可以直接动态调整buffer pool的大小： 比如从8G调整到 16G，会复制大量的缓存页，描述数据等，不可接受。

1. chunk：buffer pool中包含了多个chunk

   

1. 此时如果要增加内存 那就增加chunk的数量就行了。

23 生产经验:在生产环境中，如何基于机器配置来合理设置Buffer Pool

1. 要考虑系统内核运行所占用的内存，和其他人需要的内存，一般设置为总内存的 50% ~60%。

三、存储结构

24  我们写入数据库的一行数据,在磁盘上是怎么存储的

1. 逻辑概念：表&行&字段 ->  物理概念：表空间&数据区&数据页

1. 为什么不能直接操作磁盘？对磁盘的随机读写很耗时，而内存中修改数据则会省时不少，高配置的机器上才可以看下每秒几千的请求。

1. 每次都加载一条数据到磁盘中再修改，效率不高，所以有了数据页，每页16kb，包含多条数据。 要改一条数据就把这条数据的数据页假如缓存。
   而更新的时候，也是以页为单位，刷新多条数据。

1. 每一行数据在磁盘上如何存储？
   创建表时会指定一个行格式，比如COMPACT格式，实际每一行存储时就像下面这样

变长字段的长度列表，null值列表，数据头，column01的值，column02的值，column0n的值......

25 对于VARCHAR这种变长字段，在磁盘上到底是如何存储的

1. 一行数据在磁盘上存储的时候还包含其它描述数据。

1. 表里面很多行数据，最终放到磁盘就是一大坨数据挨着放到一块的。而变长数据如varchar，这时就变的难以读取，因为无法确定一行的长度，所以就引入了变长字段的长度列表，他记录这个变长数据的长度。

0x05 null值列表 数据头 hello a a 0x02 null值列表 数据头 hi a a

26 一行数据中的多个NULL字段值在磁盘上怎么存储

1. 实际上一个null 值，比如name字段的值为null，是不可能直接设置为null字符串的

1. null值是以 二进制的bit位来存储的，下面的案例中后四个都是为null的，且都是变长字段，

name  addreass  gender  job     school
jack    NULL      m     NULL   xx_school// 最终的行格式 其中0101是NULL值列表，它对应着上面四个可为null的字段，0不是null 1是null
0x09 0x04 0101 头信息 column1=value1 column2=value2 ... columnN=valueN// 实际上null值列表的长度是 8的倍数，不足高位补零
0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN

27 磁盘文件中，40个bit位的数据头以及真实数据如何存储

1. 除上面所述外，每一行数据还有 40个bit的数据头，用来描述这行数据

1. 1~2位 ：预留位，无意义

1. 3位：delete_mask该行是否已被删除

1. 4位：min_rec_mask每一层的非叶子节点都有最小值

1. 5~8：n_owned  记录数

1. 9~21：heap_no 当前这行数据在堆里面的位置

1. 22~24：record_type 这行数据的类型,0代表的是普通类型，1代表的是B+树非叶子节点，2代表的是最小值数据，3代表的是最大值数据

1. 25~40：next_record，下一条数据的指针

28 我们每一行的实际数据在磁盘上是如何存储的

1. 每一行数据在磁盘中的存储：

name  addreass  gender  job     school
jack    NULL      m     NULL   xx_school
// 变长字段的长度列表（字段倒叙） + NULL值列表（字段倒叙）+数据头+真实数据
0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_school 
//而实际上这些字符串在磁盘上存储的是指定编码集编码之后的
0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262

1. 实际上在真实数据部分 还有隐藏字段：
   DB_ROW_ID  每一行的一个标识
   DB_TRX_ID  事务Id
   DB_ROLL_PTR  回滚指针

// 至此真实数据应如下所示，而缓存池中加载的数据（每个数据页/缓存页装载的数据页） 实际上也如下所示
0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C（DB_ROW_ID）
00000000032D（DB_TRX_ID） EA000010078E（DB_ROL_PTR） 616161 636320 6262626262

29 理解数据在磁盘上的物理存储之后,聊聊行溢出是什么东西

1. 每个数据页/缓存页只有16kb大小，如果存储的真实数据过大，就会出现行溢出的这种情况，然后就会出现一行数据在多个数据页/缓存 页存储的情况。

   

30 于存放磁盘上的多行数据的数据页到底长个什么样子

1. 如果此时数据库一条数据都没有，要插入一条数据就要先从磁盘中加载一个空的数据页，然后向这个数据页插入数据行。 而实际上要明白磁盘中的数据行是和buffer pool中的缓存行是一一对应的，而后台的IO线程也会定时的根据lru链表和flush链表 将脏数据刷入磁盘

31 表空间以及划分多个数据页的数据区

1. 表空间：有的表空间如系统表空间对应多个磁盘上的.idb文件，有的只对应一个，然后每个表空间会对应多个数据页。

1. 数据区（extent）:表空间包含多个数据页，不便于管理，于是有了数据区，一个数据区对应连续64个数据页，一个数据页64kb大小，一个数据区就是1m大小，然后256个数据区划分为一组。

1. 第一个组的第一个数据区的前三个数据页是固定的，用来存放描述性数据：
   FSP_HDR：存放了表空间和这一组数据区的一些属性。
   IBUF_BITMA：这一组数据页的所有insert buffer的一些信息。
   INODE：也存放了一些特殊的信息

1. 每个表空间，除第一组数据区外，其它组的第一个数据区 的前两个数据页也是用来存放一些特殊信息的。

1. 关系梳理: 表->对应的表空间->对应磁盘上的idb文件->多组数据区->数据页

   

32 一文总结初步了解到的MySQL存储模型以及数据读写机制

1. 表是逻辑概念，对应物理层面就是表空间

1. 插入一条数据：先找到表空间，Extent组，extent,然后找到数据页，最后就可以加载到缓存池中了。

33 MySQl数据库的日志顺序读写以及数据文件随机读写的原理

1. 磁盘随机读：从磁盘中随机读一个数据页放到缓存中，这个数据页可能在磁盘中的任意一个位置，所以性能较差。

1. IOPS和响应延迟 ：这是随机读最需要关心的指标，代表磁盘每秒可以有多少次随机读操作

1. 磁盘顺序写：缓存页中数据更新后会写一条redo log, 它是顺序写的，在末尾进行日志追加，而如果走OSchache的话效率会更高

   

34 生产经验: Linux操作系统的存储系统软件层原理剖析以及IO调度优化原理

调度算法：  CFQ公平算法就是挨个排队，而deadline 算法，可能让IO等待时间更少的操作先执行

35 生产经验: 数据库服务器使用的RAID存储架构初步介绍

1. RAID架构：
   很多数据库部署时都采用他，它是一个磁盘冗余阵列，在实际生产中，如果服务器磁盘不够用就会加磁盘，而RAID就是管理机器中的多块磁盘的一种磁盘阵列技术，它可以告诉你该向那块磁盘进行读写。

1. 数据冗余机制：
   将数据在另一块磁盘中做冗余备份，如果一块磁盘挂掉，则还可以使用另一块磁盘，RAID技术则可以自动进行管理。而整体上RAID既有硬件层面也有软件层面。

   

36 生产经验:数据库服务器上的RAID存储架构的电池充放电原理

1. 多块磁盘组成RAID阵列时，会有一块RAID卡，这块卡是带一个块缓存的，缓存模式设置成write back后，数据就会先进入到缓存里，然后再慢慢写入磁盘，这样能大幅提高性能

1. 但如果SDRAM突然断电，那么数据就会丢失，所有有锂电池来保证供电，直到数据刷回磁盘，但是锂电池有性能衰减问题，要定时充放电，而在这个过程中，缓存模式就会设置成write through,直接写硬盘，性能从0.1毫秒级 退化成毫秒级。然后数据库的性能也会因此出现几十倍的抖动。

   

37 案例实战: RAID锂电池充放电导致的MySQL数据库性能抖动的优化

1. 一般厂商设置30天充放电一次，每次数据库出现性能抖动，性能下降10倍以上。

1. 可以使用RAID 设备提供的命令查看日志。

1. 解决这个问题有三种方案：
   换成电容，但不常用，更换麻烦，易老化。
   手动充放电，使用脚本在夜深人静时触发
   充放电时不关闭write back，它可以和上面的这个策略配合使用。

38 案例实战:数据库无法连接故障的定位，Too many connections

1. 两个Java系统设置最大连接数200个，那就有400个，而mysql也要建立400个网络连接，对于高配置Mysql服务器是完全可以办到的

1. 先检查 my.conf 中的max_connections ,再使用命令查看当前的连接数量show variables like 'max_connections'  再检查mysql的启动日志，如下所示，mysql无法为我们设置我们的期望值

Could not increase number of max_open_files to more than mysqld (request: 65535)
Changed limits: max_connections: 214 (requested 2000)
Changed limits: table_open_cache: 400 (requested 4096)

1. 上面的原因就是因为句柄的限制

   

39 案例实战:如何解决经典的Too many connections故障? 背后原理是什么

1. 解决：

ulimit -HSn 65535-- 然后就可以用如下命令检查最大文件句柄数是否被修改了
cat /etc/security/limits.conf
cat /etc/rc.local

1. 然后就重启服务器就ok了

1. linux系统作此限制的目的就是为了防止某一线程占用过多的资源，一般如kafka之类的中间件都需要自行设置参数。

四、Redo Log

40 重新回顾redo日志对于事务提交后，数据绝对不会丢失的意义

1. redo log记录着对数据库的修改，如果提交事务后能保证刷回磁盘，出现故障后就能基于它进行重做

1. 如果没有rodo log 那么最大的缺陷就是，在IO线程还没有把数据刷回磁盘就时突然宕机，而且也不可能提交一次事务就将数据刷新入磁盘，因为随机读写的效率很低。

1. redo log的格式大致为：对表空间XX中的数据页XX中的偏移量为XXXX的地方更新了数据XXX

1. 直接将数据刷入磁盘和将日志写入Redo log的区别：
   二者都是写入磁盘，但直接刷入磁盘要以数据页为单位，一个数据页16kb，仅仅修改了几个字节就要刷入整个数据页。
   而一行redo log就可能只占用几十个字节，就包含表空间号、数据页号、磁盘文件偏移量、更新值，写入磁盘速度很快

41 在Buffer Pool执行完增删改之后，写入日志文件的redo log长什么样

1. 根据修改的字节的个数不同 redo log划分了几种类型，MLOG_1BYTE （修改了一个字节），MLOG_2BYTE

//MOLOG_1BYTE
表空间ID，数据页号，数据页中的偏移量，具体修改的数据
//MLOG_WRITE_STRING 不知道具体修改了几个字节的数据
表空间ID，数据页号，数据页中的偏移量，修改数据长度，具体修改的数据

42 redo log是直接一条条写入文件的吗? 非也，揭秘redo log block

1. redo log 不是把所有行的日志直接写到一堆，而是用redo log block  来存放多个单行日志，一个block是512字节

   

1. 写入流程：先在内存中凑够一个数据块，然后在写入磁盘文件中

   

43 直接强行把redo log写入磁盘?非也，揭秘redo log buffer

1. redo log buffer：申请出来的一块连续的内存空间，划分出了连续的多个redo log block，然后上面说的redo log都是先写入这里的

1. 写时从第一个开始写入，而且实际上再一次事务中可能涉及到多个redo log，他们凑成一组写入到 redo log buffer中的 redo log block，最后再刷入磁盘中的redo log block。

   

44 redo log buffer中的缓冲日志，到底什么时候可以写入磁盘?

1. redo log buffer 写入磁盘的时机：

• • 写入redo log buffer中的日志已经占到了总容量的一半

• • 提交事务时要提交redo log，就要把redo log对应的redo log block刷入到磁盘中

• • 后台线程每隔一秒将redo log buffer刷入磁盘

• • mysql关闭的时候

1. 磁盘中默认有两个日志文件，每个48M，能容纳百万条redo log, 如果第二个写满了就写到第一个如此往复。

五、事务与锁机制

45 如果事务执行到一半要回滚怎么办?再探undo log回滚日志原理!

1. undo log用来做回滚，如果一次任务失败就要把buffer pool中的操作给回滚掉

1. 比如是insert语句，那么undo中存的就是主键和 delete语句，能把你的操作回退掉，如果是update语句，就记录原来的值，把旧值给更新回去。

46 一起来看看INSRET语句的undo log回滚日志长什么样?

1. insert语句的undo log的类型是TRX_UNDO_INSERT_REC,它包含了：

这条日志的开始位置//主键中的每列的长度、值，如果没有主键就设置row_id为主键
主键的各列长度和值 
表id
undo log日志编号//TRX_UNDO_INSERT_REC
undo log日志类型
这条日志的结束位置

1. 有了该日志之后就知道了哪个表插入的数据，主键，注解定位到缓存页，然后删除掉之前插入的数据。

47 简单回顾一下，MySQL 运行时多个事务同时执行是什么场景?

多线程操作buffer pool

48 多个事务并发更新以及查询数据，为什么会有脏写和脏读的问题?

1. 脏写：A写，B写，A回滚，B读，发现数据不是自己写的。（B读了A修改后但还未提交的数据，A回滚会覆盖B的值）

1. 脏读：事务B用了事务A修改后的值，然后事务A回滚，导致事务B拿到的就是脏值。

1. 他们俩的问题就是 在别人还没提交事务的时候就进行读写操作。

49 一个事务多次查询一条数据读到的都是不同的值，这就是不可重 复读?

1. 不可重复读：A事务读取一个值->B事务修改一个值并提交->C事务修改一个值并提交，A事务再读就发现和自己原来设置的值不同了。

1. 可重复读：和上面相反。

50 听起来很恐怖的数据库幻读,到底是个什么奇葩问题 ?

1. 幻读：执行同一条sql语句比如 select *from table where id>10 , 多次执行中间有其它事务提交了数据导致数据增多，多次查询的结果不一致

51 SQL标准中对事务的4个隔离级别，都是如何规定的呢?

1. 根据并发可能出现的问题出现了几个隔离级别

1. 下图中少了个脏写，READ-UNCONMMITTEDA是可以防止脏写的。

1. 其中常用的是RC和RR

52 MySQL是如何支持4种事务隔离级别的? Spring事务注解是如何设置的?

1. MySQL的隔离级别和SQL标准中的略有不同，它默认的隔离级别是R-R，同时它的RR是可以防止幻读的。

1. @Transactional(isolation=Isolation.DEFAULT)  该注解就用mysql默认的读写机制

1. 如果要自行改动mysql的隔离级别的话，就可能会用READ-COMMITTED，让每次读的数据都不一样。

53 理解MVCC机制的前奏: undo log版本链是个什么东西?

1. Mysql之所以能做到多个事务平行执行，彼此之间的数据互不打扰是因为使用了MVCC(Multi-Version Concurrency Control) 多版本并发控制。

1. Undo log版本链：每行数据有两个隐藏的字段 txr_id（最近一次更新这条数据的 事务的Id）和roll_pointer (指向一个undo log)

1. 事务A插入一条数据，记录值A和新的事务Id，roll_pointer指向一个空的undo log

1. 事务B 将值更新为值B，同时要生成一个undo log，记录之前的值、事务id、roll_pointer
   生成后就把这行数据的roll_pointer 指向这个生成的undo log，至此就会出现一条undo log链条

   

54 基于undo log多版本链条实现的ReadView机制，到底是什么?

1. ReadView：每次执行事务的时候生成一个，最关键的东西有四个

一个是m_ids，这个就是说此时有哪些事务在MySQL里执行还没提交的；
一个是min_trx_id，就是m_ids里最小的值；
一个是max_trx_id，这是说mysql下一个要生成的事务id，就是最大事务id；
一个是creator_trx_id，就是你这个事务的id

1. ReadView读视图机制叙述：
   首先他是基于undo log 链实现的一套读视图，事务执行时会生成一个readView，在这次事务id之前的就是已经提交过的事务，可以直接读取。 在这次事务id之后的表明又有其它事务修改了数据，不能直接读到，需要沿着undo log链条找到自己的事务或者小于自己事务id的数据。

55 Read Comitted隔离级别是如何基于ReadView机制实现的?

56 MySQL最牛的RR隔离级别，是如何基于ReadView机制实现的?

1. RR隔离级别与上面样式ReadView用画了同一个流程图

1. RR隔离级别与RC不同的就是 它的ReadView 是这个事务第一次查询的时候只生成一次

57 停一停脚步:梳理一下数据库的多事务并发运行的隔离机制

1. 事务隔离级别

1. ReadView+undo log 链

58 多个事务更新同一行数据时，是如何加锁避免脏写的?

1. 并发更新一条数据就会有脏写问题，解决脏写问题则需要锁机制，然后在多个事务更新一条数据的时候就是串行化的

1. 事务A看到当前行未加锁就会创建一个锁，这个锁包含事务Id和等待状态。

1. 事务B过来时也要创建锁，只不过因为事务A它的等待状态 要设置为true。

1. 事务A执行完后 要更改事务B的等待状态，去唤醒他。

1. 总结：其实就是加了独占锁 才避免了脏写问题

59 对MySQL锁机制再深入一步，共享锁和独占锁到底是什么?

1. 上面多个数据更新同一行数据时加的是独占锁（Exclude）,也就是X锁，一次只能有一个在写数据，其他人排队，但是其它事务要读取数据的话是不用加锁的，因为有Mvcc机制的存在。

1. 如果在读取数据的时候也非要加锁，可以使用共享锁，在查询语句后面加上lock in share mode

1. 共享锁与独占锁互斥，加了共享锁就不能加互斥锁，反过来也一样

   

1. 查询操作也可以加独占锁， select * from table for update,代表我查询的时候别人都不能更新

60 在数据库里，哪些操作会导致在表级别加锁呢?

1. 一般情况下不建议 手动在SQL语句上加锁，那样加锁逻辑就会隐藏在sql语句中

1. 表级锁：一般指存储引擎的锁，而不是DDL语句的锁，虽然执行DDL语句的时候 其它对数据的操作会阻塞。

61 表锁和行锁互相之间的关系以及互斥规则是什么呢?

1. 加表锁很少用，加表锁的语法：

LOCK TABLES xxx READ：这是加表级共享锁
LOCK TABLES xxx WRITE：这是加表级独占锁

1. 一个事务对表进行 增删改操作的时候会加独占锁，读操作的时候回加共享锁，他们都是表级的意向锁，实际上没啥用

   

62 案例实战:线上数据库不确定性的性能抖动优化实践(上)

1. 执行一个sql语句，需要加载大量的缓存页，然后可能导致原来大量的脏页刷回磁盘中 来腾出空间，这个过程可能会持续几十毫秒到几秒，然后sql语句才能执行，从而出现性能抖动。

1. 磁盘中的redo log文件有两个，第一个写满了 写第二个，第二写满了写第一个，如此往复。
   但是循环向第一个写的时候 要先看看对应的缓存页是否都刷入了磁盘，没有的话就要刷入，此时也会造成性能波动。

63 案例实战:线上数据库莫名其妙的随机性能抖动优化(下)

1. 解决上面这两种问题 有两种思路，一种是减少缓存页flush到磁盘的频率，第二个是提升缓存页flush到磁盘的速度，就是控制频率和速度。

1. 控制频率 只有加大机器内存，其它就很难控制了

1. 控制速度，将缓存页flush到磁盘的耗时控制到最小，解决方案如下：
   使用SSD：它的随机读写速度较高
   innodb_io_capacity：该参数控制了 使用多的I/O速率将缓存页flush 到磁盘中
   innodb_flush_neighbors：设置为0 代表不加载相邻的缓存页。

六、索引

64 深入研究索引之前，先来看看磁盘数据页的存储结构

1. 每个数据页直接看做一段在磁盘中连续的数据，他存储了有上一个数据页的地址，和下一个数据页的地址，构成了一个双向链表，然后每个数据页中存储的数据行，他们直接构成单链表。

   

65 假设没有任何索引，数据库是如何根据查询语句搜索数据的?

1. 数据行被分到不同的槽位中，每个数据页包含 页目录，存放了主键和槽位的对应关系

1. 如果根据主键查数据，直接在数据页中做二分查找，找到对应的槽位，再找到槽位中对应的行。

1. 而如果是非主键·则无法使用二分查找，只能进到数据页中对单向链表进行遍历查找。

1. 从第一个数据页开始，而且要先加载成缓存页，如果第一个没找到就继续加载后面的数据页，而且都要先加载成缓存页

1. 以上过程就是全表扫描，效率很低

   

66 不断在表中插入数据时，物理存储是如何进行页分裂的?

1. 插入的数据在数据页中会构成单列表，前面的数字都是类型，2代表最小的一行，3代表最大的一行

   

1. 页分裂：如果在一个数据页的数据写满了，就要挪到另外一个数据页，而索引运作的一个机制就是要求 后面的数据页的主键值要比前一个都大，然后如下图，第一个数据也中 比我大的都要挪到后面这个数据页。
   这个用来保证后面数据页比前面数据页主键大的， 数据挪动的过程就是页分裂。

   

67 基于主键的索引是如何设计的，以及如何根据主键索引查询?

1. 可以看成主键索引的 主键目录： 主键查找肯定不能走全表扫描，于是有了主键目录，他有每页的页号 和最小的主键值，这样就可以通过二分查找来进行快速的查找了。

   

68 索引的页存储物理结构，是如何用B +树来实现的?

1. 主键目录->索引页：一个主键目录 不能面对大量数据，存储大量的最小主键值。
   于是就采用了 将索引数据存放到数据也中的方式，然后就会形成很多的索引页。

   

1. 数据分了多个数据页，而索引多的时候也要分成索引页，而很多索引页最后页堆成了一个树的结构

   

69 更新数据的时候，自动维护的聚簇索引到底是什么?

1. 从索引树的顶层开始查找，最后查到索引页59号，然后找到对应的数据页

1. 每个数据页对应了一个页目录，然后就是会在这个页目录里面找到对应的槽。

1. 最下面的索引页也引用了数据页，同一级的索引页之间有双向链表。

1. 整体来看，索引页和数据页构成了一整棵B+树，数据页作为叶子节点，这样这颗B+树索引就叫做聚簇索引。

1. 页分裂过程会保证 后面的数据页的最小主键值 比前面数据页里面的所有主键值都大

1. 如果一个数据页越来越多，索引页放不下，就会拉出新的索引页，同时在搞一个上层的索引页。
   而一般情况下亿级别的大表索引的层级也就三四层。

1. 从聚簇节点的根节点开始进行二分查找，找到对应的数据页，再基于页目录 定位到主键对应的数据

70 针对主键之外的字段建立的二级索引，又是如何运作的?

1. 现有sql语句select * from table where name =‘张三’， 对name建立索引，下面将对其进行分析。

1. name字段的索引B+树：其它字段建立索引，比如name字段，插入数据的时候会新建一个B+树，这个B树的叶子节点也是数据页，与聚簇索引B+树不同的是，前者数据页只存放了主键和Name字段，后者则存放了一整行数据。

1. 搜索过程是通过排好序的 name值进行二分查找，但查到数据页的时候只能获得主键值，所以还要用主键值，到聚簇索引中获得其它数据，这个过程叫回表。而name这种普通索引称之为二级索引。一级索引就是聚簇索引。

   

   的B +树的?

1. 新建一个表：就是一个数据页，它是空的，并且属于聚簇索引的一部分。

1. 插入数据：并且维护了一个页目录，根据主键搜索没问题，这个初始的数据页就叫根页。

1. 插入更多数据：一个数据页放不下，新建一个数据页，把根页数据都拷贝过去，再新建一个，根据主键值大小进行挪动

1. 此时的根页：成为了索引页，里面存放了 两个数据页的最小主键值和数据页的页号

1. 继续增多：数据页继续分裂，索引页不断增多，索引页也会继续分裂，成为一颗B+树

1. Name索引的注意点：name索引的分裂过程和上面相似，需要注意的就是 它的索引页里面也存放了主键，因为同一级的name字段值 可能一样并且 指向不同的页号，这个时候就要根据主键判断了。

72 一个表里是不是索引搞的越多越好?那你就大错特错了!

1. 索引的顺序性：索引页内部的数据是按照从小到大的顺序 组成单向链表，数据页之间还有索引页之间构成双向链表，也是有序的。
   最终可以使用二分查找，效率很高。

1. 使用索引的缺点：
   一是占用磁盘空间，二是增删改数据还要维护索引

73 通过一步一图来深入理解联合索引查询原理以及全值匹配规则

1. 对于联合索引来说，就是依次按照各个字段来进行二分查找（比如name+age+gender，先根据name二分查，不是再找age）

1. 全值匹配：就是搜索条件与索引一 一对应，百分之百用上索引。

74 再来看看几个最常见和最基本的索引使用规则

1. 最左匹配原则：(class_name,student_name, subject_name)，从左边按照顺序匹配，如果查询条件是
   where class_name = ? and subject_name，那么后面的subject_name是没法作为索引查的。

1. 最左前缀匹配原则：百分号不能在前，在前的话没法在索引中定位。

1. 范围查找规则：如where class_name>'1班' and class_name<'5班'，可以使用索引，但是student_name就不可以，因为范围查找 只能对联合索引最左侧的列才能生效

1. 等值匹配+范围匹配：where class_name='1班' and student_name>'' and subject_name<'', student_name走索引，但是subject_name不行。

75 当我们在SQL里进行排序的时候，如何才能使用索引 ?

1. 直接在内存中排序肯定不行，数据量大的话，在磁盘中直接上排序算法也会要了老命。

1. 对于一个联合索引 INDEX(xx1,xx2,xx3),他本来就已经排好序了

1. 规则： order by条件里 不能有的升序有的降序，那样不走索引， 而且条件里面有不在索引里面的 也完蛋，带函数的也完蛋。

76 当我们在SQL里进行分组的时候，如何才能使用索引?

1. group by与order by一样，也是从索引的最左侧开始进行匹配，

1. 如果能利用上索引  就不再需要针对杂乱无章的数据 利用内存在进行重拍和分组了。

77 回表查询对性能的损害以及覆盖索引是什么 ?

1. 对于select * 这种语句，他需要扫描聚簇索引和联合索引，有时候mysql可能会认为还不如直接走全表扫描，
   但是如果加上limit数量限制还是会走联合索引的。

1. 覆盖索引的概念：指在索引树中就可以直接查询到要的数据

78 设计索引的时候，我们一般要考虑哪些因素呢? (上)

1. 在系统开发好SQL语句都写好后，就根据索引的设计原则去加索引。

1. 设计的索引最好要包括  where、order by、group by里面的条件，而且要注意顺序，符合最左原则。

79 设计索引的时候，我们一般要考虑哪些因素呢? (中)

1. 基数比较大（就是取值要种类要多）的字段：那样才能发挥B树的潜力（关键）

1. 考虑字段类型的大小：字段类型尽量小，比如tinyint，而如果对varchar这种类型简历索引，可以考虑只取前面一部分内容。建立出来的索引如：KEY my_index(name(20),age,course)，这个就是前缀索引，但是他没办法用到group by和order by中。

80 设计索引的时候，我们一般要考虑哪些因素呢? (下)

1. 查询条件中不要放函数： 如where function(a) = xx，不走索引

1. 索引不要太多：建立两三个覆盖索引，覆盖所有查询

1. 不要使用UUID做主键：主键值都是有序的，这样搞会频繁导致页分裂

81 案例实战:陌生人社交APP的MySQL索引设计实战(一)

1. 实际场景中 where使用索引 和order by使用索引不可兼得

82 案例实战:陌生人社交APP的MySQL索引设计实战(二)

1. 一般优先 满足where条件使用索引的需求，筛选出来的数据再进行排序

1. 基数较低也不一定 就不用索引！
   比如我要建立索引：（province, city, sex），虽然基数较低，但是如果不建立索引每次就要先用其它索引查一遍，加载到内存中，然后再根据这几个条件过滤一遍。所以还不如直接放到联合索引的最左侧。

83 案例实战:陌生人社交APP的MySQL索引设计实战(三)

1. 中间缺了个条件怎么办？
   对于索引 （province, city, sex）, 我要查where province=xx and city=xx and age between xx and xx， 此时可以把索引建成 （province, city, sex，age），查询是 还把sex带上，只不过取所有的，where province=xx and city=xx and sex in ('female', 'male') and age >=xx and age<=xx。

1. 对于一些其它的枚举值，比如性格 爱好，取值类型不多，索引可以设计成（province, city, sex, hobby, character, age）
   查询语句写成：where province=xx and city=xx and sex in(xx, xx) and hobby in (xx, xx, xx, xx) and character=xx and age>=xx and age<=xx
   就是说 如果不需要根据某一个条件进行过滤 那就直接in 里面放所有

1. 范围查询的字段的索引，要放到最后，如果放到中间的话，后面的字段的索引也就用不上了。

84 案例实战:陌生人社交APP的MySQL索引设计实战(四)

1. 如果要再加一个条件，最近7天内登陆过的，latest_login_time <= 7，这个地方还会用到函数，而且放在age后面也不会走索引，此时可以加一个枚举值字段，用来直接表示最近天是否登陆过，does_login_in_latest_7_days,接下来建索引就可以把他放到age的前面了
   （province, city, sex, hobby, character,does_login_in_latest_7_days, age ）

1. 辅助索引在 低基数条件、有排序分页的情况下的运用：
   对于SQL语句 select xx from user_info where sex='female' order by score limit xx,xx,
   对此设计索引（sex, score），虽然

85 提纲挈领的告诉你，SQL 语句的执行计划和性能优化有什么关系?

执行计划：提交一个SQL给给MySQL，然后查询优化器 会生成一个执行计划，执行计划代表具体的怎么查询

86 以MySQL单表查询来举例，看看执行计划包含哪些内容(1) ?

1. const： const就是超高性能的查，就是直接从聚簇索引或者聚簇索引+二级索引（唯一类型的）查得的，速度极快

1. ref：普通的二级索引查询，包括了从左查询覆盖的索引，而对于name IS NULL的这种查询，因为判空函数，所以她不会是const，而是ref_or_null

87 以MySQL单表查询来举例，看看执行计划包含哪些内容(2) ?

1. Range：利用了返回查找的普通索引就是range

1. Index：对于索引KEY(x1,x2,x3), 查询语句select x1,x2,x3 from table where x2=xxx，索引树中的叶子节点 存储的是这三个值+主键，这种慢于上面三种，需要遍历二级索引，但强于遍历聚簇索引（全表扫描）

1. All：最次的一种全表扫描

88 再次重温写出各种SQL语句的时候，会用什么执行计划? (1)

1. 对于：

select * from table where x1=xx or x2>=xx
--索引
(x1,x3)，(x2,x4)

查询优化器 会选择行数比较少的，如 x1 == XX

1. 对于x1=xx and c1=xx and c2>=xx and c3 IS NOT NULL 这种查询，可能就只能走 x1一个索引，不肯能为每个条件都加行索引，所以就只能尽量保证  x1筛选出来的数据尽量少

89 再次重温写出各种SQL语句的时候，会用什么执行计划? (2)

1. 同时使用两个索引的情况select * from table where x1=xx and x2=xx，先对x1进行索引查找，再对x2进行索引查找，最后取交集。

1. 上面这种查询 比先查X1 然后回表，然后再根据X2过滤要好的多

1. 出现这种情况的条件是：如果有联合索引 那么这个联合索引 需要时全值全匹配，或者是主键+其它二级索引等值全匹配，这样才会出现多索引查询做交集。

90 再次重温写出各种SQL语句的时候，会用什么执行计划? (3)

总结

91 深入探索多表关联的SQL语句到底是如何执行的? (1)

1. 对于查询select * from t1,t2 where t1.x1=xxx and t1.x2=t2.x2 and t2.x3=xxx
   它是先从t1表过滤，然后t2表过滤，如果加了索引 二者各干各的，然后让t1表去关联t2表中的数据

1. 其中先查一波数据 这个叫做驱动表，然后去关联另一张表里面的数据 他就是被驱动表

92 深入探索多表关联的SQL语句到底是如何执行的? (2)

1. 内外连接的基本语义

93 深入探索多表关联的SQL语句到底是如何执行的? (3)

1. 嵌套循环关联（nested-loop join）： 就是比如一个表里面查出来10条数据，然后就得让这10条数据 循环匹配表二中的数据，如果索引没有加好 效率就会很低下

t1Rows = queryFromt1() // 根据筛选条件对t1标进行查询
for t1Row in t1Rows { // 对t1里每一条符合条件的数据进行循环t2Rows = queryFromt2(t1Row) //拿t1里的数据去t2表里查询以及做关联for t2Row in t2Rows { // 对t1和t2关联后的数据进行循环t3Rows = queryFromt3(t2Row) // 拿t1和t2关联后的数据去t3表里查间和关联for t3Row in t3Rows { 1// 遍历最终t1和t2和t3关联好的数据}}
}

94 MySQL是如何根据成本优化选择执行计划的? ( 上)

1. I/O 成本 1.0 ：主要指从磁盘中读取数据所需的成本，1.0是自定义的一个值

1. CPU成本 0.2 ：主要是指对数据的处理，0.2是自定义值。

1. 计算成本方法：

-- 执行命令 返回的 rows是inndb的估计值，还有data_length
show table status like "表名"
-- 计算有多少数据页
data_length / 16 = ()kb
-- I/O成本值计算
数据页数量 * 1.0 + 微调值
-- CPU成本值计算
行记录数 * 0.2 + 微调值
-- 然后总值相加即可
总成本 = I/O+Cpu

95 MySQL是如何根据成本优化选择执行计划的? (中)

1. 根据二级索引 先刷新到I/O中，一般的等值查询 和范围查询较少的 可以直接认为是 1*1.0 或者 n**1.0

1. 拿出的数据 还要经过过滤处理，估算拿到100条数据，那么cpu成本就是 100**0.2+微调值

1. 然后再回表去聚簇索引中查找完整数据（100 *1+微调值），然后再对数据进行过滤处理(100 * 0.2 + 微调值)

1. 最后总的成本就是 1 + 20 + 100 + 20 = 141

1. 执行计划 根据最小的成本值去执行

1. 这种成本不是精确计算 而是大致计算的。

96 MySQL是如何根据成本优化选择执行计划的? (下)

1. 多个表的关联查询成本计算

97 MySQL如何基于各种规则去优化执行计划的? (上)

1. 常量替换
   i = 5 and j > i这样的SQL，就会改写为i = 5 and j > 5

1. select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1
   改写为
   select t1表中id=1的那行数据的各个字段的常量值, t2.* from t1 join t2 on t1表里x1字段的常量值 =t2.x1

98 MySQL如何基于各种规则去优化执行计划的? (中)

1. 对于 select * from t1 where x1 = (select x1 from t2 where id=xxx)，它是先查询 括号内语句，然后就相当于普通的连表查了

1. 另外一种效率则较低 select * from t1 where x1 = (select x1 from t2 where t1.x2=t2.x2)，子查询用到了外表的字段，

99 MySQl是如何基于各种规则去优化执行计划的? (下)

1. simi join 查询：将select * from t1 where x1 in (select x2 from t2 where x3=xxx)
   优化成select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx，其中semi join是mysql内部的一种用法

100~108 透彻研究通过explain命令得到的SQL执行计划

explain 开篇 （100）

1. explain 命令：SQL前面加一个 explain explain select * from table

1. 如果是简单的 sql语句那么就会查出来一行，如果是复杂的sql语句 就会是多行，因为执行计划包含了多个步骤。

   

1. 其中
   id：每次查询计划的Id
   select_type  : 查询类型
   table：表名称
   partitions ：分区
   type  ： 当前对这个表的访问方法，就好比之前的index all等
   possible_keys  ： 可能使用的索引
   key_len：索引的长度
   ref：使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信
   息
   rows：是预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据。
   filtered：就是经过搜索条件过滤之后的剩余数据的百分比。extra是一些额外的信息，不是太重要。

简单说明：

1. explain select * from t1

   

   
   id：
   select_type  : simple 表示一个简单的查询
   table：t1表
   partitions ：分区
   type  ： 没加任何条件就是all
   rows：查出来大概 3457条数据
   filtered：100%全查出来了

1. explain select * from t1 join t2

   

   
   先是表1 all扫描
   extra：Nested Loop  代表嵌套执行
   上面的这个用的是同一个查询计划，id都一样，如果有子查询id则会等于二

1. EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';

   

   
   因为有两个select 所以id不同
   第一条：select_type是PRIMARY  代表主查询
   第二条：select_type是SUBQUERY  代表子查询

1. EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2

   

   
   主要是第三个查询计划，他干的就是去重的活，<union 1,2>  是一个临时的表名，extra中有一个 using
   temporary  代表使用临时表。

1. join buffer：   EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.x2 = t2.x2
   如果两个表都没有用索引，会使用此技术 在内存中做一些优化 减少t2表的全表扫描次数

   

1. 如果排序条件没有走 索引那么extra就会是 filtered，性能比较差，直接在内存中进行的排序，如果是分组 distinct操作 则会是temporary 效率同样非常低

109~117 案例实战

千万级用户场景下的运营系统SQL调优

1. 需求描述：
   用户量是百万级别的，现在要根据一些条件 取一些用户做一些消息推送 这样的业务需求

1. SQL语句：
   SELECT id, name FROM users WHERE id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx)
   sql中除掉基础的用户表之外还有 用户信息的扩展表，通常这种查询要先看下数据量，前面可以改写为SELECT count(id) 如果超过1000 那么是可以用limit 进行限制，每次取出来1000条数据，多分几次推送消息。 但是上面的这种sql语句 在千万级的表中还是会跑出来几十秒的耗时，不可行的。

1. 执行计划分析：

+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+
| 1 | SIMPLE | | ALL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | users | ALL | NULL | 49651 | 10.00 | Using where; Using join buffer(Block Nested Loop) |
| 2 | MATERIALIZED | users_extent_info | range | idx_login_time | 4561 | 100.00 | NULL |

先看第三行： 针对latest_login_time 做 range类型的索引 ，MATERIALIZED表明是将结果集物化成一个临时表，落到磁盘中 速度较慢。
第一条执行计划是物化表的，第二条是users表和第一个表做join的，所以会有using join buffer, 并且filter 是10%

1. 为什么会这么慢：
   第一点：将临时表几千条数据物化出来，而是users表需要和这个物化出来的表 做全表扫描，所以很慢
   第二点：使用命令show warnings，会看到 semi join这个关键字，他也是原因

1. 怎么优化：
   什么是semi join：只要users表里的一条数据，在物化临时表里可以找到匹配的数据，那么users表里的数据就会返回，这就叫做semi join，他是用来筛选的。
   执行 SET optimizer_switch='semijoin=off 速度就会提升 但一般我们不能在生产环境这么做
   改变sql语句 来反正半连接：

-- 时间上后面的那个or条件 是没用的
SELECT COUNT(id)
FROM users
WHERE (id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < xxxxx) OR id IN (SELECT user_id FROM users_extent_info WHERE latest_login_time < -1)
)

1. 都用上索引其实才是王道

亿级数据量商品系统的SQL调优实战

1. 场景和需求：
   晚高峰，商品系统非常繁忙，TRS每秒几千，每分钟的慢查询超过了 10w+，数据库连接阻塞，用户无法查看商品相关的内容

1. SQL语句：
   就是根据一些条件查询商品信息，但他查询需要几十秒，数据库连接被打满

SELECT* 
FROMproducts 
WHEREcategory = 'xx' AND sub_category = 'xx' 
ORDER BYid DESC LIMIT xx,xx

1. 原因分析：
   如果是正常走索引，亿级表也不过1s钟，查看执行计划 发现possible_keys  的确是category  索引，但是实际上用的key却不是，而且extra里面写了 using where
   救急 使用force index：
   select * from products force index(index_category) where category='xx' and sub_category='xx' order by id desc limit xx,xx
   产生的问题 由此产生了下面的三个问题

1. 为什么在这个案例中MySQL默认会选择对主键的聚簇索引进行扫描？
   为什么没使用index_category这个二级索引进行扫描？
   mysql担心 从二级索引拿出来的数据会过多，而且还可能需要在内中排序，而且你是select * ，需要进行回表，那么我还不如直接走聚簇索引，using where
   其实走聚簇索引也不慢，这里也是因为看到你 limit 10, 正常扫描聚簇索引也应该不会超过1s起码

1. 即使用了聚簇索引，为什么这个SQL以前没有问题，现在突然就有问题了？
   这个就因为 有些商品分类并没有对应的商品，就是说使用category进行搜索的时候 有些数据找不到，这就导致sql会做全表扫描，而且扫来扫去也找不到数据

数十亿数量级评论系统的SQL调优实战

1. 对于一件热销的商品，它的销量有几百万，评论多达几十万，用户可任意翻找评论，总结一下就是
   针对一个商品几十万评论的深分页问题。

1. 查询语句：SELECT * FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000,20
   此语句的问题：
   如果只走了一个index_product_id 索引，里面没有is_good_comment，那就要对查出来的几十万条数据每一条数据 都要回表去比对 is_good_comment，总之就是他不适合用二级索引去查询，因为回表次数过多。
   进行改造：SELECT * from comments a,(SELECT id FROM comments WHERE product_id ='xx' and is_good_comment='1' ORDER BY id desc LIMIT 100000,20) b WHERE a.id=b.id
   这个sql语句会先执行 子查询，他会走聚簇索引，按照主键倒序排列，然后找到符合条件的20条数据，然后这20条数据作为结果集，回表去聚簇中查一次就行了。

千万级数据删除导致的慢查询优化实践 115~117&120

1. 情况描述：发现了大量慢查询，而查看语句发现 它不应该会导致慢查询，可以推测是Mysql服务本身的问题，如磁盘的I/O负载比较高，或者网络的负载超高，还有就是CPU负载，CPU过于繁忙，然后排查，方向也并不是这里的问题，这是就需要用profilling工具去排查

1. profiling(剖析) 工具：

-- 开始profiling
set profiling = 1
-- 从返回结果中可以看到 查询的语句，对应的id和耗时
show profiles
-- 其中查询出来的结果里面有一个send_data
show profile cpu, block io for query 16

1. 分析结果
   使用剖析工具查询出来的 send_data 耗时很高，然后使用show engine innodb status发现 history list length 很高，到了上万，它就是undo log版本链条的长度，事务提交后就应该被 purge清理掉，而这个list很长就代表 有事务长时间没有提交

1. 原因 和解决方法
   原因就是那个时候在清理上千万条数据，事务一直没提交掉，而且对于删除只是加了个标记，这样查询的时候就导致没查到 就走全表扫描
   然后对于长事务而言，这么多数据，每次查询的时候还会生成大量的readView，而且这删除的几千万条数据 还都会生成 undo log链条，这也就解释了 history list为什么那么大，而且一直都不被purge清理掉。

118 ~119 我们为什么要搭建一套MySQL的主从复制架构

1. 主从架构：主节点和从节点数据保持一致，一旦主节点挂掉那么从节点还可以提供服务
   这就是它的第一个作用：保证高可用

   

1. 主从架构的第二个作用：可以作为读写分离架构
   比如读写 各有3000 的qps（一般读比写高的多），一台机器只能承载5000，此时就可以使用读写分离，而且可以添加多个从节点，其中还可以融入中间件 来实现故障转移等功能。

   

主从复制的基本原理

1. binlog中记录了所有的增删改操作。

1. 主机点就负责生成binlog，然后用dump线程把日志传输给从库，从库就使用I/O线程把日志写入到本地的relay中去，然后从库会再基于它 重新把sql语句执行一遍，来达到一个数据同步的效果，当然数据并不是完全一致的。

   

121 ~123 如何为MySQL搭建一套主从复制架构？

创建简单的 主从库 (异步 数据同步有延迟)

1. 主库创建一个用于主从复制的账号

1. 不能在主库还在提供服务的时候  让从库从零开始导入数据，要选择一个时间进行维护，导入和数据备份。

1. 使用mysql自带的 msqldump工具 进行备份
   /usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot --master-data=2 -A > backup.sql
   然后就把这个 backup.sql 复制到从库里面，在从库执行他，

1. CHANGE MASTER TO MASTER_HOST='192.168.31.229', MASTER_USER='backup_user',MASTER_PASSWORD='backup_123',MASTER_LOG_FILE='mysqlbin.000015',MASTER_LOG_POS=1689;

半同步的 主从库复制（一般采用这种方式）

1. 半同步有两种方式
   一种是after_commit，非默认主库将binlog 复制到从库之后，提交事务，然后等待从库的响应，然后再讲主库提交事务成功的消息给从库
   另一种是 mysql 5.7默认的方式：主库将binlog 给从库，然后等待从库相应成功，然后再提交事务，并把提交成功的消息告诉从库

1. 半同步复制 只需安装插件，然后可以再配合高可用切换机制，就可以实现数据库的高可用

GTID 搭建方式（较为简便）

1. 主从库想要配置下配置文件  主要是server_id,其余配置就和简单搭建一样了

1. 然后可以配合 mycat中间件和sharding-sphere 中间件来实现 主从复制 都没问题

124 主从复制架构中的数据延迟问题，应该如何解决？

1. 延迟问题的产生：比如多个线程向主库中写入，从库单个线程拉取数据，这个问题如果是读写分离的话就会导致写进去的数据 会有一会儿读不出来。

1. 如何解决：
   mysql 5.7支持的并行复制：在从库中设置 slave_parallel_workers>0 slave_parallel_type设置为LOGICAL_CLOCK

1. 如果需要数据要能被立刻读取到的话 可以使用MyCat或者Sharding-Sphere之类的中间件里设置强制读写都从主库走。

125 ~127  数据库高可用：基于主从复制实现故障转移

1. 所谓靠可用 的核心就是要有故障转移，主库挂了要能立刻把从库切换为主库】、

1. 使用工具MHA（Master High Availability Manager and Tools for MySQL） ,它探测到节点挂掉之后就立刻搞出来新的节点作为主节点

1. 搭建过程 。。。。

128 案例实战:大型电商网站的上亿数据量的用户表如何进行水平拆分?

1. 背景 ：数据量大，单表搜索扛不住

1. 建议：单表不超过1000w，最好不超过500w， 100w是最佳的选择，几千万的用户数据 也就几个gb，可以让一台服务器 放两个库

1. 数据库分发：
   一般是用 数据库id来取模，但是要用用户名来查的话  就要建立一个专门存储用户名和用户id的表，但这样相当于查两次，所以也可以用es来做用户的复杂查询（监听binlog）

129 案例实战: 一线电商公司的订单系统是如何进行数据库设计的?

1. 背景： 用户去查自己的订单

1. 设计方案： userid和orderid做一个映射放在表里，并且是根据userid来做分表，最终拿到的orderid 再可以去es中查询完整数据

130 案例实战:下一个难题，如果需要进行垮库的分页操作,应该怎么来做?

1. 尽量不要搞，可以用es 或者在 userid和orderid的那张表里面放上要查询的条件

131 案例实战：当分库分表技术方案运行几年过后，再次进行扩容应该怎么做

在开始的时候多增加表，扩容的时候就括服务器，把数据库迁移到上面去就行了

132 专栏总结：撒花庆祝大家对数据库技术的掌握更进一步

很好，就是后面的主从复制、高可用、分库分表这些讲的比较初级

★,°:.☆(￣▽￣)/$:.°★ 。