高性能MySQL读书笔记

MySQL架构

简要介绍了MySQL服务层和存储引擎层。

并发控制，事务，隔离级别。

快照读：读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

  • 普通的查询语句

当前读：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

  • 增删改（x锁）
  • select * from … for update（x锁）
  • select * from … lock in share mode

delete/update的具体流程：

  1. 当一个Update SQL语句被发送到MySQL服务器时，MySQL服务器会根据where条件找到第一条满足条件的记录。
  2. 找到这条记录后，InnoDB引擎会将这条记录返回给MySQL服务器，并对这条记录进行加锁，这种加锁操作被称为”current read”。
  3. MySQL服务器收到这条加锁的记录后，会发起一个Update请求，更新这条记录。
  4. 更新完这条记录后，MySQL服务器会继续查找下一条满足条件的记录，然后重复上述过程，直到没有满足条件的记录为止。
  5. 因此，一个Update操作内部实际上包含了一个”current read”操作。同样的，Delete操作也是这样。

Cluster Index：聚簇索引

InnoDB存储引擎的数据组织方式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列。

2PL：Two-Phase Locking

传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

img

快照读在RC/RR下的不同表现：

  • 在”可重复读”（RR）隔离级别下，事务开始时就会创建一个快照ReadView，之后的所有读操作（快照读）都会读取这个快照中的数据。也就是说，如果在一个事务中，连续两次执行相同的SELECT语句，总是会得到相同的结果，即使在两次查询之间，其他事务已经提交了更新。

  • 然而，在”读已提交”（RC）隔离级别下，每次执行SELECT语句，都会读取最新提交的数据。也就是说，如果在一个事务中，连续两次执行相同的SELECT语句，可能会得到不同的结果，因为在两次查询之间，其他事务可能已经提交了更新。

当前读在RC/RR下相同。

这部分介绍的很浅，但是面试常问的就是这部分。后面结合其他东西补充下。

可靠性工程世界中的监控

走读一眼。

performance schema

一个用于插桩记录MySQL运行过程中的统计数据的库。

包括开启/关闭插桩，开启/关闭消费者等。

本章基本就是字典，用时再查吧。

操作系统和硬件优化

简要介绍了下影响性能的几个关键硬件。

  • cpu
  • 内存
  • 固态硬盘
    • 内存和硬盘要平衡好，尽量使用内存做读取和写入的缓存
    • 多次写合并为一次
    • I/O合并
      • WAL日志->随机写变成顺序写
    • RAID
  • 网络
    • DNS
    • 大量连接，修改端口范围和TCP连接排队大小
  • 文件系统
    • 使用日志型文件系统，ext3/4，建议XFS
    • 磁盘队列调度器
    • 不要用CFQ调度器：因为它是每个进程一个队列，所有队列是公平的，但是MySQL服务器中，显然MySQL进程的IO请求必须高优先级
  • 内存和交换
    • 使用tcmalloc或jemalloc替代glibc用来分配内存
    • 避免交换swap
    • 内存极限情况下会发生OOM killer，要配置关键进程（MySQL,SSH）的OOM Killer分值，防止被首先kill。

优化服务器设置

MySQL的配置，查询配置文件读取位置

1
2
3
sh-4.4# /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

注意分清楚全局参数和会话参数。

innodb_dedicated_server参数：InnoDB 会根据服务器的物理内存大小来设置 innodb_buffer_pool_size（默认为物理内存的 75%）（读取和修改数据页的缓冲池）和 innodb_log_file_size（默认为物理内存的 10%）。innodb_flush_method 会被设置为 O_DIRECT_NO_FSYNC，以减少操作系统缓存的使用。注意，默认该参数是OFF。

innodb_flush_method 是 MySQL 中的一个配置参数，用于设置 InnoDB 存储引擎如何将数据和日志从内存刷新（flush）到磁盘。注意，这里的设置同时对日志文件和数据文件生效。

这个参数有以下几个可能的值：

  • fsync：这是默认值。在这种模式下，InnoDB 会使用 fsync() 系统调用来刷新数据和日志。这种方法在大多数系统上都能正常工作，但在某些情况下可能会导致性能问题。

  • O_DSYNC：在这种模式下，InnoDB 会使用 O_DSYNC 打开文件，然后使用 write() 系统调用来写入数据。这种方法可能会提高性能，但在某些文件系统上可能会导致数据一致性问题。

  • O_DIRECT：在这种模式下，InnoDB 会使用 O_DIRECT 打开文件，然后使用 write() 系统调用来写入数据。这种方法可以避免操作系统的缓存，直接将数据写入到磁盘，可能会提高性能。

  • O_DIRECT_NO_FSYNC：这是 innodb_flush_method 参数的一个新选项，只在 MySQL 8.0.11 及更高版本中可用。在这种模式下，InnoDB 会使用 O_DIRECT 打开文件，但不会在每次写入数据后调用 fsync()。这种方法可以进一步提高性能，但可能会增加数据丢失的风险。

在设置 innodb_flush_method 参数时，你需要根据你的硬件配置和工作负载来进行权衡。一般来说，如果你的系统有大量的内存和高速的磁盘，你可能会从 O_DIRECT 或 O_DIRECT_NO_FSYNC 选项中获得更好的性能。

日志相关配置：

  • 事务日志（redo log）:循环写入，文件大小由innodb_log_file_size（单个文件大小）和innodb_log_files_in_group（文件数量）控制，当缓冲区（缓冲区大小由innodb_log_buffer_size控制）满了、事务提交时，或者每秒1次（这三个条件以先满足者为准），InnoDB会将缓冲区刷新到磁盘上的日志文件中。
    • 如何刷新日志缓冲区：
      • 当InnoDB将日志缓冲区刷新到磁盘上的日志文件时，会使用互斥锁锁定缓冲区，将其刷新到所需的位置，然后将剩余的条目移动到缓冲区的前面。当释放互斥锁时，可能会有多个事务准备刷新其日志条目。InnoDB使用了一个组提交特性，可以在单次I/O操作中将一组日志全部提交。
      • innodb_flush_log_at_trx_commit：
        • 0：每秒将日志缓冲区写入文件，并将文件刷新（flush）到磁盘。不安全，MySQL挂了或者宕机都会导致数据丢失（没有flush到磁盘）。
        • 1：每次事务提交时，将日志缓冲区写入日志文件，并将其刷新到持久存储中。默认值。
        • 2：每次事务提交时都将日志缓冲区写入日志文件，但不执行刷新。InnoDB按计划每秒刷新1次。MySQL挂了，数据不丢失，因为日志已经写到了文件（虽然没flush到磁盘，但已经写道了系统的page cache），宕机会丢数据。

InnoDB表空间：InnoDB将数据保存在表空间中，表空间本质上是一个虚拟文件系统，由磁盘上的一个或多个文件组成。InnoDB将表空间用于多种用途，而不仅仅是存储表和索引。表空间中还包含了Undo日志（重新创建旧行版本所需的信息）、修改缓冲区、双写缓冲区和其他内部结构。建议使用innodb_file_per_table并限制共享表空间的大小

行的旧版本过多（MVCC）会导致表空间过大，注意清除延迟问题。

本章基本上就是介绍一些参数，感觉类似于手册，走读一遍结束。

schema设计于管理

数据类型

  1. 更小的更好
  2. 更简单的类型

我觉得这是MySQL中一个不好的地方，与PostgreSQL相比差一些。比如老生常谈的varchar和char到底用哪个：最好是varchar指定大小，在允许范围内限制varchar大小。我觉得这很蠢，Pg中直接用text即可，不需要关心背后的问题。类似的，还有count(*)和count(1)这种老掉牙问题，早些版本的MySQL的优化器居然无法把这两个查询优化到一样，真的是愚蠢。

先写道这吧，这本书暂时不想往下读了。我感觉这本书更像是一本最佳实践的手册，讲的是如何使得MySQL达到较好的性能，给出了一堆最佳实践（我感觉也没多少干活。。。）。其中程序员比较关注的“schema设计于管理”，“创建高性能的索引”，“查询性能优化”这几章，剩下的可有可无或者不是程序员需要关注的，更适合DBA这样的角色关注。

后面会去读一下掘金上MySQL相关的小册，看目录感觉更有用。