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

配置： wait_timeout：控制 Sleep 连接保持时间。线上未配置，默认 8 小时，

SQL 执行步骤

客户端-连接器（管理连接、权限验证）-查询缓存（命中则直接返回，基本没啥用）-分析器（词法分析、语法分析，语法错误在这就能看到了）-优化器（执行计划生成、索引选择）-存储引擎（存储数据、提供读写接口）

server 层

连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎

负责数据的存储和提取。

连接器

Sleep：表示是空闲连接。

数据库长连接和短连接： 长连接是连接成功后，如果客户端持续有请求，则一直使用同一个连接。优点：减少建立连接的动作，尽量使用 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

全部使用长连接问题：可能 MySQL 占用内存会涨的很快，因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，会在连接断开才释放，如果长连接积累，会导致内存占用较大。 解决办法：定期断开长连接；5.7 版本，执行比较大的操作的后，mysql_reset_connection 来重新初始化连接资源，恢复连接到刚刚创建时的状态。miproxy 连接数太多会自己断开

内存：操作系统 10G，应用 8G， 线程级别的内存占用， 一个连接占多大，tcp 占 1M，文件系统描述符。

为啥会用 swap 内存， 操作系统配置：是否使用 swap    cpu 结构有关 对称一致访问：每个核心访问最近的 32G 内存最快，开了的话，cpu0 访问 32G，这个进程用了 32G 就会用 swap 分区。 smp  numa

查询缓存

基本没用了

分析器

词法分析：识别关键字，以及表名之类。 语法分析：根据语法规则判断语法是否错误。

优化器

索引顺序选择，多表 join 顺序等。

执行器

执行前判断对表有没有相应权限。（没分析完词法，不知道是哪张表）

问题： 1.Sleep 连接是不是杀了一直没影响 2.miproxy 对连接的管理：长短连接，长连接的维护。功能怎么实现的， 3.MySQL 内存的使用。（innodb_buffer_size_pool 之外的内存）。 4.数据库权限和表权限验证时间不一样

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

线上配置： innodb_flush_log_at_trx_commit：为 1 时保证每次事务的 redo log 都会持久化到磁盘。 sync_binlog：为 1 时保证每次事务的 binlog 都持久化到磁盘。

更新流程：两阶段提交，涉及和 binlog

| | | | | |

redo log：保证了 MySQL crash-safe 能力。记录更新时，先把记录写到 redo log 并更新内存。 binlog：。。， crash-safe：有了 redo-log，可以保证数据库异常重启时，从 redo log 读取后写入内存再写入磁盘，保证数据部丢失。

两阶段提交： 更新写入到内存后，redolog 处于 prapare 阶段，然后写 binlog，再调用引擎的提交事务接口，将 redolog 改成提交状态。 server 是作为一个协调者。innodb 只知道干啥。发起 commit 时候，innodb redo  write  innodb 没完成不能马上写磁盘。

MySQL 磁盘满，会休眠，磁盘满。innodb 队列变长了，可能都建立连接不成功：gdbc 建立连接会 show 变量，要不建立连接不成功，要不就连接数暴涨。 原因：如果在写完 binlog 到提交事务之间 MySQL crash 的问题。或者从备份恢复的时候。

问题： 备份恢复 apply log 是在：将 redo log 的数据写入磁盘吗？

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

线上配置：

事务 ACID：Atomicity、Consistency、Isolation、Durability（原子性、一致性、隔离性、持久性）

多个事务同时执行会出现的问题：脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read） 隔离级别： 读未提交：可以看到未提交的事务 读提交：只有提交的事务才会被看到 可重复读：一个事务执行过程中看到的数据，总是和启动时看到的一样。不管其他的事务有没有启动。 串行化：顾名思义

|

幻读的原因：update 等操作的当前读，需要读取当前的数据，这个时候就会读到最新的更新数据了。表现为：单独 select 没有值，然后 insert 或者 update，发现值已经有了。

MVCC：undo log 实现

04 | 深入浅出索引（上）

innodb 索引类型： 主键索引和：根据叶子节点的内容 主键索引：叶子节点内容是整行数据 非主键索引：叶子节点内容是主键的值。也叫二级索引。使用二级索引，如果想获得整列的值，需要先从索引找到主键值，然后再根据主键查，需要回表。

索引维护： 分裂：某个数据页满了，新插入数据的时候。 合并：相邻两个页删除了数据，利用率很低的时候。 ps：自增主键是不会分裂的，因为一直是增长的。

问题： 1.同时插入较多数据时，索引维护是不是比较占资源。 2.业务字段做主键：只有一个索引；该索引必须是唯一索引？

05 | 深入浅出索引（下）

避免回表的方式： 覆盖索引（组合索引）： 索引使用注意： 1.最左前缀原则：不管是对于组合索引，还是对于 2.索引下推：MySQL5.6 优化，多个 and 条件的时候，对索引中的字段先过滤，减少回表次数。 2.索引列使用函数会导致索引失效

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

加锁范围分类：全局锁、表级锁和行锁。 全局锁 加全局锁方法：Flush tables with read lock (FTWRL)。使用场景：全库逻辑备份（innodb 已经不需要了，可以使用–single-transaction） 表级锁 分为表锁和元数据锁（MDL） 表锁：lock tables … read/write。 元数据锁：当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。所以给小表加字段，如果读取较多的话，也会阻塞，会阻塞所有的查询和更新，如果客户端有重试机制，会导致该库线程打满。

尽量使用 FTWRL 而不是 set global read_only=1： 1.read_only 可能会用来判断是主库还是备库。 2.FTWRL 命令时，如果客户端异常断开，MySQL 会自动释放，set global read_only 会导致 MySQL 一直只读。

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

线上配置： innodb_lock_wait_timeout：10 innodb_deadlock_detect：on 两阶段锁协议：行锁在需要的时候才加上，但是在事务结束后才释放。所以要把可能影响并发的锁放在后面。 死锁和死锁检测策略： 一直等待到超时：innodb_lock_wait_timeout：一直等待锁，直到超时。值不能太大，也不能太小。 死锁检测：发现死锁后，主动回滚死锁链条中的某偶一个事务，让其他事务执行。

问题： 死锁检测成本：每个新来的线程，都要要判断会不会由于自己的加入导致死锁，如果同时更新同一行的线程很多的时候。CPU 消耗。 原理怎么做的。

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

undolog：实现了 MVCC 数据可见性，锁是实现隔离级别。 查询和更新是当前读还是一致性读。 一致性读：可重复读隔离级别下，读取数据时，会根据 row trx_id 找到是否能读到当前值，不在的话会根据 redo log 往前找。 当前读：操作数据都是要读当前。可重复读隔离级别下，如果需要更新数据，只能拿最新的数据更新，这个时候虽然别的事务还没有提交，但还是能看到的。 undolog 大小。共享表空间

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

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

线上配置： innodb_flush_neighbors：1 innodb_io_capacity：5000 innodb_max_dirty_pages_pct：75

为什么会抖一下： 1.redo log 满了。redo log 中的 checkpoint 前移动，需要刷脏页。（应该避免，因为此时数据库服务已经阻塞，需要控制刷脏页频率）  一般不会设置，不同百分比的时候不一样。 2.内存不足，需要淘汰脏页。buffer_pool     3.日常刷脏页。对应 MySQL 的配置。 4.MySQL 正常关闭。

正常使用的数据库，buffer 一般未被使用的情况比较少，如果一个查询，需要的内存比较大，查询需要从磁盘读取很多页的时候，也要将脏页刷到磁盘中。 可用的页，刷脏页，写到磁盘，redolog checkpoint 往前走，free list flush list lru list

脏页刷盘参考：一个是脏页比例，一个是 redo log 写盘速度。

如果高配机器 redo log 设置比较小的话：redo log 很容易被写满，就会导致经常要因为 redo log 满了而刷盘。表现为：磁盘压力很小，但是数据库出现间歇性的性能下跌

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

：怎么正确的回收表空间 innodb_file_per_table：1 默认值 删除整个表数据会删除，但是删除表中的部分数据，空间是怎么回收的。

delete 删除一条记录：将记录标记为已删除，之后可以复用。 delete 删除多条记录：如果整个页被删除，则整页都被标记为可复用。相邻的两页使用率低，会合并。但空间均不会被释放。同理：插入数据，如果当前页已满，肯定会开启新页，也会造成数据空洞。

压缩空间：alter table A engine=InnoDB。5.5 之前不要用，会阻塞 DML。5.6 版本之后：Online DDL（先建新表，并将旧表的更新写在一个文件中，新表搞好之后，再读取文件中的更新。会有很短时间的 MDL 写锁）

DDL 过程如果是 Online 的，就一定是 inplace 的？

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

不加 where 条件： MyISAM 引擎：把一个表的总行数存在了磁盘上，因此执行 count(*)的时候会直接返回这个数，效率很高； InnoDB 引擎：数据一行一行地从引擎里面读出来，然后累积计数。 show table status：不准确。

加 where 条件： InnoDB 引擎：选择从引擎读

效率排序：count(字段)<count(主键 id)<count(1)≈count() count()：特殊处理过，因为不会为 null，server 会按行累计。

count(1)：InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。 count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。 count(字段)：根据是否为 not null 进行判断。

15 | 答疑文章（一）：日志和索引相关问题

日志相关： 1.两阶段提交不同瞬间 MySQL 异常重启怎么保证数据完整性： 测试

16 | “order by”是怎么工作的？

全字段排序

排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

max_length_for_sort_data

 rowid 排序