08 | 事务到底是隔离的还是不隔离的 事务的启动时机是什么时候 不同的启动方式，事务的启动时机不同； begin/start transaction 都可以开启一个事务，但这并不是事务的起点，一直到执行第一个操作 InnoDB 表的语句，事务才真正开始； 如果想立马开启事务，可以使用 start transaction with consistent snapshot 视图的创建时机 通过begin/start transaction启动事务，一致性视图在第一个读快照语句时生成； 通过tart transaction with consistent snapshot启动的事务，视图立即被创建 mysql里两种视图的概念 一个是 view，它是通过查询语句定义的虚拟表，查询方法和表一样 一个是InnoDB实现MVCC时用到的“一致性读”视图，用于支持 RC 和 RR InnoDB是如何秒级创建快照的 严格来说不是快照，实质上是保存了数据的多个版本，同时保存了各个版本之间的关系（undo log），由新的版本可以推算得出旧版本； 具体表现为数据表中的每行记录都可能存在多个版本，每个版本都对应着一个 transaction id，记为 row trx_id，这个 transaction id 是系统为每个事务分配的唯一id，它按照事务启动的顺序严格递增 快照在mvcc里是如何工作的 事务启动的时候会被分配一个 trx_id，当该事务获取某行数据时，会对比这行数据的当前trx_id， 发现比自己大，那说明这个是被自己之后的事务更新的数据，不认，需要继续查找上一个版本； 如果和自己的 trx_id 一样，说明是自己所做的更改，要认 如果比自己的 trx_id 小，说明是在自己启动以前这个数据就被更新掉了，也要认； 一致性视图里更新逻辑是怎样的 更新数据都是先读后写，并且这个读都是当前读 读提交和可重复复的逻辑区别是什么 可重复读隔离级别下，事务开启以后创建一个一致性读视图，整个事务过过程中读查询都使用这个视图 读提交隔离级别下，每个语句执行前都会重新计算出一个视图

09 | 普通索引和唯一索引，应该怎么选择 查询过程的区别 对于普通索引来说，查到满足条件的第一个记录，再查找下一个记录，直到查到一个不满足条件的记录，结束。 对于唯一索引来说，查找到满足条件的第一个记录，结束。 普通索引的查找过程看似比唯一索引多了几步，但考虑到索引的数据模型是B+树，且数据都是按页读取的，所以多出来的几步操作对象都是内存，性能影响可以忽略不计。 恰巧处于数据页边缘的情况也会存在，但是几率太小了，也可以忽略不计 change buffer使用区别 更新操作时，如果要被更新的数据页已经在内存中，直接更新即可。如果数据页不在内存中，此时会把更新操作先计入change buffer中，等到对应的数据页被访问的时候，再将其读入内存，和change buffer做一个merge操作，保证数据的正确性（当然系统也会有后台线程定期做merge操作）。 唯一索引的更新无法使用change buffer，因为每次更新都需要先判断是否违反唯一性约束，这需要把数据读入内存。 change buffer需要单独总结一篇，参考 普通索引和唯一索引的更新过程 要更新的目标页在内存中 唯一索引：找到指定的位置，判断没有冲突，则插入 普通索引：找到指定的位置，插入 要更新的目标页不在内存中 唯一索引：将数据页读入内存，判断有无冲突，没有冲突则插入 普通索引：将更新操作记录到 change buffer，结束 由此可见，普通索引的更新性能更好。 change buffer 的使用场景 仅限于普通索引，不适用于唯一索引 普通索引的所有场景，change buffer 都能起到加速作用吗？ change buffer 的主要原理是在更新操作时，减少了一次读磁盘的IO：数据不在内存，先记下更改，等下次读它的时候再顺便把更新做了（merge操作）。由此可知，在数据页做merge之前，change buffer 记录的变更越多越好。所以结论如下： 对于写多读少的业务，change buffer 使用效果最好 对于读多写少的业务，change buffer 会起到反作用 change buffer 与 redo log redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗 问题讨论 change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？change buffer 丢失可不是小事儿，再从磁盘读入数据可就没有了 merge 过程，就等于是数据丢失了。会不会出现这种情况呢？

10 | MySQL为什么有时候会选错索引 优化器的逻辑 找到最优的执行方案，考虑的因素包括但不限于：扫描的行数、是否使用临时表、是否排序 索引的区分度 索引的区分度即是说一个索引中不同值的个数，又称之为“基数”，基数越大区分度越好 通过“采样统计”获取索引的区分度 InnoDB 默认选择N个页，统计每个页上的不同值，求平均数，然后乘以页面数，得到索引的基数。 索引的更新导致变更的行数超过 1/M 时，从新采样。 innodb_stats_persistent 设置为 on 时：N 为 20，M 为 10 innodb_stats_persistent 设置为 off 时：N 为 8，M 为 16 优化器有时会选择扫描行数更多的那个方案 如果使用普通索引预估扫描行数 3 万，不使用普通索引直接全表扫描 10 万行，由于使用普通索引还需要回表，这部分时间也考虑进去的话，优化器会认为全表扫描会更快 主动修正统计信息 analyze table table_name 索引选择异常的处理方案 使用 force index(index_name) 强制使用指定的索引 新建一个更合适的索引给优化器使用或者删掉误用的索引 问题讨论 通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢？

11 | 怎么给字符串字段加索引？ 给字符串加索引的方式 直接创建完整的索引 前缀索引 倒序存储，再创建前缀索引 使用hash字段 使用前缀索引的优缺点 优点 节省空间 缺点 可能会增加额外的记录扫描数 使用前缀索引导致无法使用覆盖索引（每行都需要回表确认） 使用前缀索引，定义好长度，可以打到既节省空间、又不会增加太多的查询成本。这里可以参考索引的区分度，区分度越高越好 select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7 from SUser; 问题讨论 如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？ 答案 只存入学年份加顺序编号，它们的长度是 9 位。而其实在此基础上，可以用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性

12 | 为什么我的MySQL会“抖”一下？ SQL 语句为什么变“慢”了？ 和数据相关的三个部分：数据文件、日志文件、内存 脏页：内存数据页和磁盘数据页内存不一致，这个内存页称为“脏页” 干净页：内存数据写入磁盘后，内存和磁盘上的数据页就一致了，我们称为“干净页” 平时执行很快的更新操作，其实就是在写内存和日志；偶尔变得很慢，很可能是在刷“脏页”（flush） 哪些场景下会刷脏页？ redo log 满了 系统内存不足 系统空闲的时候，主动刷脏 Mysql正常关闭前 刷脏是常态，但是要避免两种情况 一个查询要淘汰的脏页个数过多，会导致查询的响应时间明显变长 日志写满，更新全部堵住，写性能跌为0 InnoDB 刷脏页的控制策略 正确告诉 InnoDB 所在主机的IO能力，使其能够确定全力刷脏页的时候能刷多快；相关参数：innodb_io_capacity 避免刷脏页的时候顺带刷旁边的脏页；相关参数：innodb_flush_neighbors 问题讨论 一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例，正常会建议你将 redo log 设置成 4 个 1GB 的文件。 但如果你在配置的时候不慎将 redo log 设置成了 1 个 100M 的文件，会发生什么情况呢？又为什么会出现这样的情况呢？ 答案 磁盘压力很小，但是数据库出现间歇性的性能下跌。

13 | 为什么表数据删掉一半，表文件大小不变？ 表数据的存储 存储在共享表空间里 存储在单独的文件 通过innodb_file_per_table参数控制，且5.6.6开始，默认为 on 推荐设置为 on，单独存储易管理，执行 drop table 命令，系统会直接删除表对应的文件，如果存放在共享表里则不会删除 为什么删除数据，文件大小不变 前置须知：InnoDB 的数据模型是 B+ 树 执行删除操作后，对应的记录不会被真正的删除，仅仅是标记此条记录原本存在的位置可复用，如果恰好有一条数据需要插入到此位置，则复用，否则它就一直存在，像一个“空洞”； 如果是整页数据被删除，也是采用标记的形式，不会真正删除，只是这一页下次被使用时，位置可以改变，不局限于原来的位置。 类似的还有插入和更新操作，都会导致“空洞”，这些空洞就是导致删除记录，但文件大小不变但原因。 通过重建表，优化“空洞”问题 alter table A engine=InnoDB 建立一个临时文件，扫描表 A 主键的所有数据页； 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中； 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态； 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态； 用临时文件替换表 A 的数据文件。 问题讨论 假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：一个表 t 文件大小为 1TB；对这个表执行 alter table t engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了 1.01TB。你觉得可能是什么原因呢 ？ 答案 表本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。

14 | count(*)这么慢，我该怎么办？ count(*)在不同引擎的实现方式 MyISAM 引擎单独存储了表的总行数，执行 count(*) 时可以直接返回，效率高。（查询不能带有where条件） InnoDB 执行 count(*)，需要将数据从引擎里一行一行地读出来进行计数 为什么 innodb 不像 myisam 一样单独存储总行数 因为 MVCC。 在同一时刻的多个查询，由于多版本并发控制的原因，应该返回多少行是不一致的 innodb对count(*)做了哪些优化 对于 count(*) 这中操作，遍历数据表的任何一棵索引树结果都是一样的，因此 MySQL 优化器会选择最小的那棵树来进行遍历。一般不会是主键索引树，因为它的叶子节点存储的是数据，而普通索引树的叶子节点存储的是主键id，数据量会小很多。 在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。 show table status 返回的 table_rows 为什么不能使用 不能。TABLE_ROWS 是通过采样估算得来的，不准确。 计数的实现方案 用缓存系统保存计数 问题： 缓存系统可能会丢失更新； 逻辑上可能不精确； 两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图 数据库保存计数 count() 的语义 count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值 count(*)、count(主键 id)、count(1)、count(字段） count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数 效率：count(字段) < count(主键 id) < count(1) ≈ count(*) 问题讨论 从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

16 | “order by”是怎么工作的？ MySQL 在哪里排序 内存排序 : MySQL为每个线程分配一块内存用于排序，数据量少的时候在这里进行。具体大小通过 sort_buffer_size 控制。 磁盘临时文件排序 : 数据量大于 sort_buffer_size 时，使用多个磁盘临时文件排序，有点类似于归并排序 MySQL排序方案 建表如下： CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`) ) ENGINE=InnoDB; 排序语句如下： select city,name,age from t where city='杭州' order by name limit 1000; 1. 全字段排序 (1)初始化 sort_buffer，确定放入 name、city、age 这三个字段； (2)从索引 city 找到第一个满足 city=‘杭州’条件的主键 id (3)到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中； (4)从索引 city 取下一个记录的主键 id； (5)重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y； (6)对 sort_buffer 中的数据按照字段 name 做快速排序； (7)按照排序结果取前 1000 行返回给客户端。 2.

17 | 如何正确地显示随机消息？ 排序的选择 对于 InnoDB 表的排序，全字段排序会减少回表的次数，性能更佳，会被优先选择 对于存在于内存里的表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，所以行越小排序性能越好 order by rand() 的执行过程 建表语句 mysql> CREATE TABLE `words` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; 查询语句 select word from words order by rand() limit 3; explain 结果 Using temporary表示需要使用临时表；Using filesort 表示需要执行排序操作; 全字段排序流程图 rowid排序流程图 执行流程（假设内存足够放下临时表，内存不够时使用磁盘临时表） 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型(记录位置信息)。 从内存临时表中一行一行地取出 R 值和位置信息(类似rowid)，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。 执行流程图 优先队列排序算法 如果内存不足以存放临时表，除了使用磁盘临时表外，还有一个选择：使用优先队列排序算法。

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？ Mysql 规定，如果对字段做了函数计算，无法使用索引 建表语句 CREATE TABLE `tradelog` ( `id` int(11) NOT NULL, `tradeid` varchar(32) DEFAULT NULL, `operator` int(11) DEFAULT NULL, `t_modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`), KEY `t_modified` (`t_modified`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 查询语句 select count(*) from tradelog where month(t_modified)=7; 隐式类型转换相当于调用了 CAST 函数，无法使用索引 查询语句 select * from tradelog where tradeid=110717; 相当于调用 select * from tradelog where CAST(tradid AS signed int) = 110717; 隐式字符编码转换相当于调用了 CONVERT 函数，无法使用索引 建表语句