MySQL 常见问题（一） #

1. 出现 Unknown column 错误是在哪个阶段 #

MySQL 会在分析器中判断语句是否正确，表是否存在，列是否存在等，如果没有问题再提交给优化器。

2. 为什么对权限的检查不在优化器之前做 #

有些时候，SQL 语句要操作的表不只是 SQL 字面上那些。比如有个触发器，得在执行器阶段（过程中）才能确定，优化器阶段前是无能为力的。

客户端连接时，如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。一个用户成功建立连接后，即使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

3. 一个查询语句的执行流程是什么 #

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

4. bin log、redo log、undo log 的区别 #

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，记录的是「在某个数据页上做了什么修改」；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如「给 ID=2 这一行的 c 字段加 1」。

redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下图所示

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。擦除后“空着的部分”就是 write pos 到 3 号文件末尾，再加上 0 号文件开头到 checkpoint 的部分。

当 redo log 写满了时，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。

checkpoint 可不是随便往前修改一下位置就可以的。如上图中，把 checkpoint 位置从 CP 推进到 CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都 flush 到磁盘上。之后，图中从 write pos 到 CP’ 之间就是可以再写入的 redo log 的区域。

当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

5. MySQL “N叉树”中 N 的值是否可以被人工调整 #

通过改变 key 值来调整。N 叉树中非叶子节点存放的是索引信息，索引包含 Key 和 Point 指针。Point 指针固定为 6 个字节，假如 Key 为 10 个字节，那么单个索引就是 16 个字节。如果 B + 树中页大小为 16 K，那么一个页就可以存储 1024 个索引，此时 N 就等于 1024。我们通过改变 Key 的大小，就可以改变 N 的值。
改变页的大小。页越大，一页存放的索引就越多，N 就越大。数据页调整后，如果数据页太小层数就会太深，数据页太大，加载到内存的时间和单个数据页查询时间会提高，需要达到平衡才行。

6. 什么是长事务，为什么尽量避免使用长事务 #

长事务就是运行时间比较长，长时间未提交的事务，也可以称之为大事务。

MySQL 在回滚操作的时候需要用到 undo log（回滚日志），假设一个值从1被按顺序改成了2、3、4，在回滚日志里面就会有类似下面的记录。

回滚日志不会一直保留，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。而长事务意味着系统里面会存在很老的事务视图，由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除此之外，长事务还可能会占用锁资源。

7. 什么是索引下推 #

如果某张表存在联合索引（name, age），现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL 语句是这么写的：

select * from tuser where name like '张%' and age=10 and ismale=1;

由于存在前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。在 MySQL5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

如上这两个图里面，每一个虚线箭头表示回表一次。

左图中，在（name，age）索引里面特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把「name第一个字是『张』」的记录一条条取出来回表。因此，需要回表 4 次。

右图跟左图的区别是，InnoDB 在（name，age）索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

8. MySQL 中的锁 #

全局锁 #

全局锁就是对整个数据库实例加锁，MySQL 提供加全局读锁的方法Flush tables with read lock（FTWRL），这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。但是有如下风险：

如果在主库备份，在备份期间不能更新，业务停摆；
如果在从库备份，备份期间不能执行主库同步的 binlog，导致主从延迟。

官方自带的逻辑备份工具 mysqldump，当 mysqldump 使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。一致性读是好，但是前提是引擎要支持这个隔离级别。

如果要全库只读，为什么不使用set global readonly=true的方式？

在有些系统中，readonly 的值会被用来做其他逻辑，比如判断主备库，所以修改 global 变量的方式影响太大。
在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表锁 #

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

比如，在某个线程 A 中执行lock tables t1 read, t2 write;这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行unlock tables之前，也只能执行读 t1、读写 t2 的操作，连写 t1 都不允许，自然也不能访问其他表。

另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在 MySQL5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

需要注意一点的是，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

行锁 #

9. buffer pool 和 change buffer #

change buffer 的前身是 insert buffer，只能对 insert 操作优化，后来升级了，增加了 update/delete 的支持，名字也改叫 change buffer。

要理解 change buffer 还得先理解 buffer pool 是什么，顾名思义，硬盘在读写速度上相比内存有着数量级差距，如果每次读写都要从磁盘加载相应数据页，DB 的效率就上不来，因而为了化解这个困局，几乎所有的 DB 都会把缓存池当做标配（在内存中开辟的一整块空间，由引擎利用一些命中算法和淘汰算法负责维护和管理），change buffer 则更进一步，把在内存中更新就能可以立即返回执行结果并且满足一致性约束（显式或隐式定义的约束条件）的记录也暂时放在缓存池中，这样大大减少了磁盘 IO 操作的几率。

InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

还没有使用的；
使用了并且是干净页；
使用了并且是脏页。

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。如果内存不够用了，这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

10. 如何优化索引 #

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。可以使用show index方法，看到一个索引的基数。

排序字段加上索引，因为索引已经是有序的了，如果选择索引的话，不需要再做排序，一版情况下只需要遍历去获取数据集就可以了。而对要排序的数据，扫描行数会影响查询时间，所以区分度高的数据，利用索引查询会快。

11. 各种 count 的用法 #

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。所以，count(*)、count(主键id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

至于分析性能差别，可以有这么几个原则：

server 层要什么就给什么；
InnoDB 只给必要的值；

现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

对于count(主键id)来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别能对比出来，count(1)执行得要比count(主键id)快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是 null，按行累加。

无论使用哪种 count 方式，引擎都一定要逐行去读，只是在是否使用索引和是否返回给 server 层具体数据有区别。

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)，所以建议尽量使用count(*)。

12. order by 是如何工作的 #

MySQL 会为每个线程分配一个内存（sort_buffer）用于排序，该内存大小为 sort_buffer_size。

如果排序的数据量小于 sort_buffer_size，排序将会在内存中完成；
如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序；
在使用外部排序时，MySQL 会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件。

MySQL 会通过遍历索引将满足条件的数据读取到 sort_buffer，并且按照排序字段进行快速排序。

如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段；
该方式会造成随机 IO，在 MySQL5.6 提供了 MRR 的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表；
按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表。

全字段排序 #

通过索引将所需的字段全部读取到 sort_buffer 中；
按照排序字段进行排序；
将结果集返回给客户端。

缺点：

造成 sort_buffer 中存放不下很多数据，因为除了排序字段还存放其他字段，对 sort_buffer 的利用效率不高；
当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差。

优点：MySQL 认为内存足够大时会优先选择全字段排序，因为这种方式比 rowid 排序避免了一次回表操作

rowid 排序 #

通过控制排序的行数据的长度来让 sort_buffer 中尽可能多的存放数据，max_length_for_sort_data 是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
只将需要排序的字段和主键读取到 sort_buffer 中，并按照排序字段进行排序；
按照排序后的顺序，取id进行回表取出想要获取的数据；
将结果集返回给客户端。

优点：更好的利用内存的 sort_buffer 进行排序操作，尽量减少对磁盘的访问

缺点：回表的操作是随机 IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问