本文是个人学习极客时间专栏《MySQL实战45讲》过程中所记录的一些笔记，内容来源于专栏
MySQL锁
全局锁
全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。该命令的典型使用场景是做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 —**single-transaction **的时候，导数据之前就会启动一个事务，来确保拿到一致性视图（可重复读隔离级别下）。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。但该方法只适用于所有的表使用事务引擎的库
表级锁
MySQL表级锁有两种，一种是表锁，一种是元数据锁（Meta data lock，MDL）
表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁或等待客户端断开时自动释放
元数据锁（MDL）在 MySQL 5.5 版本引入，不需要显示使用，在访问一个表时会被自动加上。当对一个表做增删改查操作（DML）的时候，加 MDL 读锁；当要对表做结构变更操作（DDL）的时候，加 MDL 写锁。事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放
行锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
死锁应对策略
直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
加锁规则
加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间
查找过程中访问到的对象才会加锁
索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁
唯一索引上的范围查询会访问到不满足条件的第一个值为止
常见配置参数
innodb_lock_wait_timeout：设置锁申请的最长等待时间，默认值为50s
innodb_deadlock_detect：设置是否开启死锁检测机制，默认值为on
MySQL视图
View
它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样
一致性读视图
用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”
使用 begin/start transaction 启动事务时，一致性视图是在执行第一个快照读语句时创建的
使用 start transaction with consistent snapshot 启动事务时（在可重复读隔离级别下才有意义），一致性视图是在执行该命令时创建的
“快照”在 MVCC 里是怎么工作的？
InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。通过当前版本和 undo log可以计算出历史版本
在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。
对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能
如果落在已提交事务区域，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在未开始事务区域，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在未提交事务区域，那就包括两种情况
若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
通俗点的判断规则：一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。
事务启动时根据某个条件读取到的数据，直到事务结束时，再次执行相同条件，还是读到同一份数据，不会发生变化，称为“一致性读”
如果事务隔离级别为 REPEATABLE READ，则同一事务中的所有一致读将取该事务中第一个读请求所建立的快照
如果事务隔离级别为 READ COMMITTED 隔离级别，则在每次读请求时都会重新创建一份快照
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”，除了 update 语句外，select 语句如果加锁，也是当前读
为什么我的MySQL会“抖”一下
Innodb脏页
可能引发脏页Flush的情况
redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，对应的所有脏页都 flush 到磁盘上，redo log 留出空间可以继续写
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘
MySQL 认为系统“空闲”的时候，会进行刷脏页的操作
MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上
刷脏页的控制策略
- InnoDB 的刷盘速度要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度
常见配置参数
innodb_io_capacity：定义了 InnoDB 后台任务每秒可用的I/O操作数(IOPS)，比如刷新 buffer pool 的页面和合并 change buffer 中的数据。默认值为
200innodb_max_dirty_pages_pct：用来控制 buffer pool 中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB 会启动刷脏页的操作，默认值为
90innodb_flush_neighbors：用来控制 buffer pool 刷脏页时是否把脏页邻近的其他脏页一起刷到磁盘，5.7版本默认值为1， 8.0版本默认值为
00：表示刷脏页时不刷其附近的脏页
1：表示刷脏页时连带其附近毗连的脏页一起刷掉
2：表示刷脏页时连带其附近区域的脏页一起刷掉，1与2的区别是2刷的区域更大一些
为什么表数据删掉一半，表文件大小不变
删除表数据
delete 命令只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的（标记删除）。这些可以复用，而没有被使用的空间，看起来就像是“空洞”
不止是删除数据会造成“空洞”，插入或更新数据因页分裂操作也会产生“空洞”
重建表
可以使用 alter table t engine=InnoDB 命令来重建表
重建表的流程（Online DDL）
建立一个临时文件，扫描表 A 主键的所有数据页
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件
用临时文件替换表 A 的数据文件
常见配置参数
innodb_file_per_table：用来控制表数据是存在共享表空间里，还是单独的文件，默认值为
ONON：每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中
OFF：表的数据放在系统共享表空间，也就是跟数据字典放在一起
count计数
count(*)的实现方式
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（没有where条件的情况）
InnoDB 引擎执行 count(*) 的时，需要把数据一行一行地从引擎里面读出来，然后累积计数（因为MVCC，无法像 MyISAM 那样）
不同的 count 用法
count() 的语义：count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值
count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加
count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加
count(字段)：InnoDB 引擎会遍历整张表，把每一行的字段值都取出来，返回给 server 层。server 层拿到字段值后，判断是不可能为空的，就按行累加。
count(*)：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加
按照效率排序的话，count(字段) < count(主键id) < count(1) ~= count(*)
“order by”是怎么工作的
全字段排序 VS rowid 排序
全字段排序：server 从引擎获取查询的所有字段（含排序字段）放入 sort_buffer 中，再根据排序字段进行排序，按照排序结果返回给客户端
rowid 排序：server 从引擎获取排序字段和主键id放入 sort_buffer 中，再根据排序字段进行排序，按照排序结果拿 id 的值回到原表中取出待查询的字段返回给客户端（对于InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择）
常见参数配置
- sort_buffer_size：MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。默认值为
256KB
为什么只查一行的语句，也执行这么慢
查询长时间不返回
等MDL锁（show processlist 命令查看 Waiting for table metadata lock 的状态，处理方式就是找到谁持有 MDL 写锁，然后把它 kill 掉）
**等 flush **（show processlist 命令查看 Waiting for table flush 的状态（出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句，处理方式就是干掉阻塞源头）
等行锁
查询慢
无索引，走全表扫描
当一致性读需要回溯的版本很多时，会比较慢
短期提高性能的做法
短连接风暴
处理掉那些占着连接但是不工作的线程
减少连接过程的消耗，如让数据库跳过权限验证阶段
慢查询性能问题
索引没有设计好。通过紧急创建索引来解决
SQL 语句没写好。通过改写 SQL 语句来处理，MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式
MySQL 选错了索引。使用查询重写功能，给原来的语句加上 force index
QPS 突增问题
数据库端下掉业务的处理方法
白名单机制。如果能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉
账号机制。如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接
把压力大的SQL使用查询重写做降级
I/O性能问题
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。可能会增加语句的响应时间，但没有丢失数据的风险。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据