MySQL|MySQL知识点串讲【适用于中高级开发】-持续更新事务|数据库索引|数据库锁|面

总述
- - 01 | 基础架构：一条SQL查询语句是如何执行的？
  - 02 | 日志系统：一条SQL更新语句是如何执行的？
  - 03 | 事务隔离：为什么你改了我还看不见？
  - 04 | 深入浅出索引（上）
  - 05 | 深入浅出索引（下）
  - 06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？
  - 07 | 行锁功过：怎么减少行锁对性能的影响？
  - 08 | 事务到底是隔离的还是不隔离的？
  - 09 | 普通索引和唯一索引，应该怎么选择？
  - 10 | MySQL为什么有时候会选错索引？
  - 11 | 怎么给字符串字段加索引？
  - 12 | 为什么我的MySQL会“抖”一下？
  - 13 | 为什么表数据删掉一半，表文件大小不变？
  - 14 | count(*)这么慢，我该怎么办？
  - 15 | 答疑文章（一）：日志和索引相关问题
  - 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用完怎么办？
  - 应用
  - 总结

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

文章图片

MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
- 连接器：
  - 客户端如果太长时间没操作，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
  - 因为连接过程非常复杂（三次握手、登陆验证、权限验证等），所以建议使用长连接。
  - 全部使用长连接有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。解决办法：
    - 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
    - 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
- 查询缓存：不建议使用。因为对一个表格的更新会将这个表格的所有缓存失效。MySQL 8.0 版本直接将查询缓存的整块功能删掉了
- 分析器：进行词法分析和语法分析。
- 优化器：优化器是在表里面多个索引的时候，决定使用哪个索引；或者一个语句有多个关联语句的时候，决定各个表的连接顺序。优化器阶段完成后语句的执行方案就确定下来了，生成执行计划。
- 执行器：MySQL会先进行权限校验，然后根据生成的执行计划调用存储引擎的API进行数据操作。
存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。

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

MySQL中两个重要的日志系统redo log和bin log比较：
- redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
- redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
- redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
- bin log有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
- redo log循环写入示意图：
  
  文章图片
执行器和 InnoDB 引擎在执行 update 语句时的内部流程。【update T set c=c+1 where ID=2; 】
- 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
- 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
- 示意图
  
  文章图片
如何根据日志文件进行数据恢复？
- 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
- 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。
- 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。
小结
- redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。
- sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。
- 两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案。

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

下面以innaDB存储引擎为例
事务的四大特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）
事务的隔离级别：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）
- 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
- 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
- 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
- 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
事务隔离级别使用场景
- 假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。
事务隔离级别的实现【以可重复读为例】
- 每次更新都将更新内容进行记录
- 假设三个事务A、B、C分别将值从1依次改为了2、3、4，回滚日志中会进行如下记录（类似于GIT等版本控制工具，将每次的修改记录下来）
  
  文章图片
- 当read-view A需要读取1时，需要将当前值4依次执行图中的回滚操作得到
- 回滚日志如何回收呢？没有其它事物线程还在使用当前版本的undo时候，purge进程进行回收。
- 所以尽量避免使用长事务

04 | 深入浅出索引（上）
请参考文章：学习MySql索引的数据结构，只需这一篇就够了
05 | 深入浅出索引（下）
请参考文章：学习MySql索引的数据结构，只需这一篇就够了
知识点补充：

自增主键防止页分裂，逻辑删除并非物理删除防止页合并
因为InnoDB是聚簇索引，所以要尽量减小主键的大小
要尽量通过覆盖索引减少回表次数
MySQL 5.6版本之后引入了索引下推，当一个表格建有（name，age）的联合索引时通过索引下推优化后的样子如图：
- SQL为：select * from tuser where name like '张%' and age=10
  
  文章图片

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

全局锁典型的应用场景是做全库的逻辑备份，当然也可以通过事务来做，但是不是所有引擎都支持事务
表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是：
- 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
- 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

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

两阶段锁协议：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁和死锁监测
解决死锁的策略：
- 直接进入等待，直到超时。超时时间可以通过参数 innodb_lock_wait_timeout 来设置【该方法不好控制超时时间的设置，太短容易误伤，太长体验不好】
- 进行死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行，将参数innodb_deadlock_detect 设置为 on，表示开启这个逻辑。【当高并发同时更新同一行时，死锁监测成本很高，每次监测时间复杂度都是O(n)级别】
- 控制并发度：
  - 可以通过中间件实现或者修改MySQL源码，让相同更新在引擎之前排队
  - 或者将高频更新的数据拆解为多行记录，例如将高频修改的账户分为多条数据存储，这样并发请求就降低了。不过需要在业务中进行相应的控制

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

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
在可重复读隔离级别下，事务在启动的时候会基于整库拍一个快照
快照工作原理
- 每个事务在开始的时候都会向InnoDB事务系统中申请一个transaction id，该id是按照申请顺序严格递增的
- 每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id【数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id】
- 一个记录被多个事务连续更新后的状态如图
  
  文章图片
- 注意：V1-V4四个版本中真实存在的为V4，U1-U3三个回滚操作会记录到undo log（回滚日志）中，当需要之前的版本数据时会根据当前数据以及回滚日志计算出来
- 可重复读隔离级别下的事务，会以自己启动的时刻为准，如果一个数据版本是在启动之后生成的，就不认（不可见），判断基准是根据当前最新数据的row trx_id和自己启动时的transaction id进行比较判断
更新逻辑
- 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
- 即所有更新都是在当前数据版本的基础上进行的更新，而不是事务启动时的快照。
- 除了 update 语句外，select 语句如果加锁，也是当前读。
表结构不支持“可重复读”：这是因为表结构没有对应的行数据，也没有 row trx_id，因此只能遵循当前读的逻辑。MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可重复读。
请分析下面【事务 B 查到的 k 的值是 3，而事务 A 查到的 k 的值是 1】的原因

CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; insert into t(id, k) values(1,1),(2,2);

文章图片

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

查询过程两者差异（根据索引列等值查找）
- 普通索引在找到满足条件的记录后，需要查找下一条记录，直到遇到不满足的值为止；
- 唯一索引因为定义了唯一性，会停止继续建索
- 性能差异对比：基本没有差异，因为InnoDB的数据是按照数据页为单位进行读写的，所以普通索引需要查找的后续数据，一般是会一起加载到内存中的
更新过程操作过程
- change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
- merge：将change buffer中的操作更新到原数据页称为merge。
- merge的触发条件：1.当查询该数据页的时候。2. 后台空闲线程自动merge。3. 数据库正常shutdown的时候
唯一索引和普通索引都可以使用change buffer吗？
- 唯一索引不可以，普通索引可以使用change buffer
- 因为当开始change buffer后，需要更新的数据不在内存中才会写入change buffer，而唯一索引需要将数据加载到内存判断唯一性，所以更新时不能使用change buffer
更新过程两者差异（根据索引列等值查找）【包括插入新数据】
- 经过前面分析得知，更新过程需要分两种情况考虑。需要更新的数据页在内存中和不在内存中两种情况
- 数据页在内存中几乎没有差异，只是唯一索引多了一些判断
- 数据页不在内存中：
  - 当更新操作结束【很快】需要进行查询时：开启change buffer的普通索引性能【差】，因为很快触发了merge
  - 当更新操作结束【好久才】需要进行查询时：开启change buffer的普通索引性能【好】，因为不需要读取数据页进行磁盘IO
  - 关闭change buffer普通索引和唯一索引几乎没什么差异
- change buffer和redo log区别与联系
  - // TODO之后找大块时间来整理
- 总结，两种索引如何选择
  - 业务正确性优先。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。
  - 然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

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

一张表上可能会建有多个索引，我们在写SQL并没有指定索引，这样选择哪个索引是由优化器来决定的
优化器选择索引的目的是找到最优的执行方案，参考内容包括但不限于：预计扫描行、CPU资源消耗、是否使用临时表、是否排序等
扫描行数是如何判断的呢？MySQL通过采样统计获取索引的区分度来，再根据区分度来预估行数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过一定数量的时候，会自动触发重新做一次索引统计。
像并发删除修改之类的操作很容易造成选择索引错误
- 假如一个事务查询，另一个事务仍未提交，因此删除的数据只是标记删除，数据仍然在数据页中，后插入的数据需要找新的空位插入，这样查询时会扫描删除的数据+后插入的数据，同时算上回表扫描主键索引，因此比正常多好多
索引选择错误的处理
- force index 强行选择一个索引【但不建议这么做，因为一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。】
- 我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。
- 在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

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

常见方式有
- 直接创建完整索引，这样可能比较占用空间；
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
- 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。在实际应用中，你要根据业务字
具体通过那种方式创建索引需要根据业务和性价比来决定

12 | 为什么我的MySQL会“抖”一下？

你的SQL语句为什么变慢了？
- InnoDB的redo log写满了，这个时候系统会停止所有更新操作，将部分redo log日志flush磁盘上
- 内存不够了，必须淘汰旧的数据页释放内存才可加载新的数据页到内存中
  - 当淘汰的数据页是干净的数据页时直接释放就行，不会有太大性能影响
  - 当需要淘汰大量脏数据页时，因为需要flush脏页，所以SQL执行会变慢
    - 脏页是更新数据时更新内存，而引起的内存与磁盘不一致的时，此时的数据页为脏页
InnoDB刷脏页的控制策略
- 需要告诉InnoDB所在主机的IO能力，控制参数为：innodb_io_capacity
- 通过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
- InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。因为如果刷太慢，会出现内存脏页太多，以及 redo log 写满。
- 参数innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%

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

知识拓展：
- 一个 InnoDB 表包含两部分，即：表结构定义和数据。
- 在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。
- 而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。
- 因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。
- 参数：innodb_file_per_table
  - 设置为 OFF，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  - 设置为 ON，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
  - 从 MySQL 5.6.6 版本开始，它的默认值就是 ON 。
  - 建议设置为NO，因为一个表单独存储为一个文件更容易管理
- 基于innodb_file_per_table 设置为NO的数据删除流程
  - 删除数据InnoDB只做了删除标识，并未做真正的删除，当在删除的范围内插入新的数据时，会重用删除的位置
  - 当整个数据页被删除时，可以被复用到任何位置
- 删除数据会造成数据页的空洞，同样在中间插入数据也会造成数据页的空洞，因为会造成页分裂
- 重建表
  - 当需要对表进行空间收缩时需要重建表
  - MySQL5. 6之后的重建表流程【Online DDL】
    - 建立一个临时文件，扫描表 A 主键的所有数据页；
    - 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
    - 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
    - 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
    - 用临时文件替换表 A 的数据文件。
      
      文章图片

14 | count(*)这么慢，我该怎么办？

在不同的 MySQL 引擎中，count(*) 有不同的实现方式【未加过滤条件where】
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
- 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
用缓存系统保存计数？
- 因为数据库支持事务与并发，所以无法通过类似于Redis的缓存数据库，精确控制不同线程执行时刻的count数量
用数据库保存计数
- 如果我们把这个计数直接放到数据库里单独的一张计数表 C 中，又会怎么样呢？
- 我们来看下现在的执行结果。虽然会话 B 的读操作仍然是在 T3 执行的，但是因为这时候更新事务还没有提交，所以计数值加 1 这个操作对会话 B 还不可见。
  文章图片
- 又因为InnoDB支持崩溃恢复不丢失，所以逻辑上是一致的
InnoDB引擎提供数据原则：
- server 层要什么就给什么；
- InnoDB 只给必要的值；
- 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。
count不同字段差异
- count(主键id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
- count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
- count(字段)：
  - 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  - 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
  - 也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
- count(*)
  - 并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
- 所以结论是：按照效率排序的话，count(字段))，所以我建议你，尽量使用 count()。

15 | 答疑文章（一）：日志和索引相关问题
16 | “order by”是怎么工作的？

案例一
- SQL：select city,name,age from t where city='杭州' order by name limit 1000;【city字段建有索引】
- explain:
  文章图片
- Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
- 语句执行流程【全字段排序】
  - 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  - 从索引 city 找到第一个满足 city='杭州’条件的主键 id；
  - 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  - 从索引 city 取下一个记录的主键 id；
  - 重复步骤 3、4 直到 city 的值不满足查询条件为止；
  - 对 sort_buffer 中的数据按照字段 name 做快速排序；
  - 按照排序结果取前 1000 行返回给客户端。
  - 文章图片
sort_buffer_size
- MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
- 使用临时文件辅助排序时MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。（归并排序算法）
rowid 排序
- 参数：max_length_for_sort_data
  - 是 MySQL 中专门控制用于排序的行数据的长度的一个参数。
  - 它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法，因为当单行数据量太大时，全部缓存到内存中进行排序显然不是一个很好的办法
- 设置方式SET max_length_for_sort_data = https://www.it610.com/article/16;
- 新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
案例二（使用rowid 排序）【同样使用案例一的表结构和sql】
- 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
- 从索引 city 找到第一个满足 city='杭州’条件的主键 id；
- 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
- 从索引 city 取下一个记录的主键 id；
- 重复步骤 3、4 直到不满足 city='杭州’条件为止；
- 对 sort_buffer 中的数据按照字段 name 进行排序；
- 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
全字段排序 VS rowid 排序
- 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
- 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
- 这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
- 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
案例三【建立(city,name)联合索引】
- 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
- 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
- 从索引 (city,name) 取下一个记录主键 id；
- 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
案例四【建立(city,name,age)联合索引】–使用到覆盖索引
- 从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
- 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
- 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

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

如何从一个单词表中随机选出三个单词呢？
- 使用order by rand() 来实现
  - select word from words order by rand() limit 3;
  - explain结果为：
    文章图片
  - 该条语句的执行流程为：
    - 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
    - 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
    - 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
    - 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
    - 从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
    - 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
    - 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。
    - 总结：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。
      - 对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
      - 对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
      - MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。
- 随机排序方法 - 1【先把问题简化，只随机选择一个word值】
  - 取得这个表的主键 id 的最大值 M 和最小值 N;
  - 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  - 取不小于 X 的第一个 ID 的行。
  - 当id不连续就不能使用该方法获取了
- 随机排序方法 - 2【先把问题简化，只随机选择一个word值】
  - 取得整个表的行数，并记为 C。
  - 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
  - 再用 limit Y,1 取得一行。
- 当id连续时使用随机算法1，当id不连续时使用随机算法2
- 其他办法：
  - 如果按照业务需求，随机取三个，数据库还在设计阶段,可以增加一个主键字段,用来记录每行记录的rowid，这样一万行，那就是连续的一万，然后随机，用该随机rowid回表查询该行记录

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作
- SQL：select count(*) from tradelog where month(t_modified)=7;
- 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
案例二：隐式类型转换
- SQL：select * from tradelog where tradeid=110717;
  - tradeid字段为varchar(32)类型
  - MySQL里的转换规则：字符串和数字做比较的话，是将字符串转换成数字。
- 则上面语句相当于：select * from tradelog where CAST(tradid AS signed int) = 110717;
案例三：隐式字符编码转换
- SQL：select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
- 当表 l 和表 d 的 tradeid字段字符集不一致时，也会放弃索引进行全表扫描

19 | 为什么我只查一行的语句，也执行这么慢？

查询长时间不返回：
-SQL： select * from t where id=1;
- 原因：大概率是表格被锁住了
- 解决办法：执行show processlist,看看当前语句处于什么状态，再进行处理
  - 等待MDL锁
    - Waiting for table metadata lock
      文章图片
    - 这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
    - 这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。
    - 复现步骤
      文章图片
  - 等待flush
    - Waiting for table flush
      文章图片
    - 可能的情况是有一个flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
    - 场景复现
      文章图片
    - 解决办法，kill掉第一条语句
      文章图片
  - 等行锁
    - 场景复现
      文章图片
    - 解决办法，通过sys.innodb_lock_waits 表查询获得锁的线程，然后kill掉
      - SQL ：select * from t sys.innodb_lock_waits where locked_table='test.t'\G
      - kill掉4这个连接
        文章图片
查询慢：
- 扫描行数多，需要建立索引
- 该条语句被后开启的其他事务大量修改，产生了大量的undo log
  - 复现方式
    文章图片
  - 原理分析：
    文章图片

尽量避免使用长事务
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

【MySQL|MySQL知识点串讲【适用于中高级开发】-持续更新】▄█?█●各位同仁，如果我的代码对你有帮助，请给我一个赞吧，为了下次方便找到，也可关注加收藏呀
如果有什么意见或建议，也可留言区讨论