星尘

MySQL 高级 - 5

发表于 更新于 分类于

count(*) 计数

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

  1. MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
  2. 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

InnoDB 对 count(*) 的优化

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

show table status 看到的表行数准确吗

show table status 命令显示的行数是不准确的，因为索引统计的值是通过采样来估算的。

count(*)、count(主键 id)、count(字段) 和 count(1) 的性能

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。即 count(列名) 会过滤为null 的情况，所以相同条件的查询下，count(*) 的数量一定是大于等于 count(列名)的。

1
2
3
4
5
6
7
count(字段）：遍历整张表，需要取值，判断 字段 != null，按行累加； 
count(id) ：遍历整张表，需要取ID，判断 id !=null，按行累加； 
count(1) ： 遍历整张表，【不需要】取值，返回的每一行放一个数字1，按行累加； 
count(*) : 【不需要取字段】，count(*)，按行累加； 
因为count(*) 和 count(1) 不取字段值，减少往 server层的数据返回，所以比其他count(字段)要返回值的【性能】较好；

性能：count(字段）< count(id) < count(1)≈count(*)

两阶段提交不同时刻的奔溃

在两阶段提交的不同瞬间，MySQL 如果发生异常重启，是怎么保证数据完整性的？

  1. 在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库

  2. 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

  3. 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

    a. 如果是，则提交事务；

    b. 否则，回滚事务。

B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

MySQL 怎么知道 binlog 是完整的

一个事务的 binlog 是有完整格式的：

​ statement 格式的 binlog，最后会有 COMMIT；

​ row 格式的 binlog，最后会有一个 XID event。

在 MySQL 5.6.2 版本以后，引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现

redo log 和 binlog 是怎么关联起来的

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

为什么要两阶段提交？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，满足两个日志都完整不就可以了？

两阶段提交就是为了给所有人一个机会，当每个人都说“我 ok”的时候，再一起提交。

只用 binlog 来支持崩溃恢复，又能支持归档，可不可以？

这样的情况下 binlog 还是不能支持崩溃恢复的。原因是 binlog 没有能力恢复“数据页”。InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页，由于数据页的信息都是存储在 redo log 里面的，所以这里就不能恢复数据页，会导致一部分数据丢失。

反过来，只用 redo log，不要 binlog 行不行

不行。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog 的话，这些下游系统就没法输入了。

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

redo log 会在事务提交的时候再写入到文件中，之前是一直存在于 redo log buffer 之中的。

order by 排序是怎么工作的

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

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

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

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

rowid 排序

​ a. 通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
​ b. 只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
​ c. 按照排序后的顺序，取id进行回表取出想要获取的数据
​ d. 将结果集返回给客户端

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

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

MySQL 主从备份

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

  1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
  2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
  3. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
  4. sql_thread 读取中转日志，解析出日志里的命令，并执行。