9. MySQL 性能优化

EnigmaGcl
152 阅读6分钟

9. MySQL 性能优化.png 性能优化策略：

  • 最大化利用资源
  • 最小化使用资源
  • 业务优先

1. 部署优化

1.1 单机多实例还是单机单实例

建议单机多实例，MySQL 的设计很难将机器资源吃满，单机多实例能提升资源利用率，且对后续的分库分表或者是迁移等更友好

1.2 要不要绑定 CPU

不建议绑定，MySQL 吃 CPU，但是更吃 IO，如果绑定，会造成 CPU 浪费，相反 redis 更多是 CPU 操作，倒是建议绑定 CPU

1.3 编译时是否建议使用 tcmalloc/jemalloc

建议使用，控制内存使用方式，默认行为为申请使用完就退还给 OS，但是这两个不会，会申请一块内存，用完不归还，关闭才释放，性能会好些

1.4 针对固定业务场景，从编译环节入手提升性能

MySQL 8.0.19 及后续版本支持 PGO（配置文件优化）

首先编译时添加参数 -DFPROFILE_GENERATE=ON 打开 PGO 功能，编译出 mysqld 后（仅后续收集用，线上不使用），利用测试集在这个 mysqld 上跑压测，压测过程中就会生成对应配置文件，用于后续优化，然后再打开参数 -DFPROFILE_USE=ON 编译得到线上使用的 mysqld，如下：

1.5 semi-sync

semi-sync 建议至少设置为一主两从，退化时间设置为 1 秒，semi-sync 一般是两地三中心，主库一个中心，两个从库分别一个中心，主库和其中一个从库同一个城市，另外一个从库在其他城市，建议这样设置：

1.6 单元化

单元化部署不仅需要数据库支持，更需要公司整个技术框架支持，从 CI/CD 到各种中间层都需要改造支持单元，好处是就近访问，提升访问速度，且每个地域数据库是全量数据，符合异地容灾，高可用等要求，但是对技术栈要求较高，成本高

1.7 double write buffer -> 原子写

MySQL 一页是 16K，引擎在刷脏调用 pwrite 时，一次是写一整页 16K 的数据，但是有个问题，操作系统只能 4k、4k 的写，如果一次 pwrite 过程中出现写了一半掉电的情况，那么这个页的数据就不能用了，因为页头数据修改了，checksum 对不上，如下：

如何确保每次刷盘数据不丢失呢？MySQL 引入了 double write buffer 方案，听名字就知道是双写，先写一遍再写一遍，虽然叫 buffer，但其实本质是磁盘上的一块空间，具体思路是 innodb 调用 pwrite 刷盘时，先写 double write buffer，写完后再写磁盘，这样不管写 double write buffer 或者写磁盘过程中出现任何问题，都能恢复

  • 比如写 double write buffer 时掉电，那磁盘上整页还是干净的，重启后重新走整个流程即可
  • 如果写磁盘时掉电，重启后发现磁盘上对应页损坏，那可以从 double write buffer 中恢复数据

整体流程如下：

可以看到，引入 double write buffer 后解决了刷盘数据丢失问题，但也引入了新的问题，即性能砍半，本来一次刷盘就结束，现在需要刷两次

如果硬件提供原子写能力，能保证一次刷盘 16K 要么成功，要么失败，那就能关掉 double write buffer 功能，提升刷盘性能，现在很多云厂商都支持该功能，比如阿里云、腾讯云等，如果自建，需要购买支持原子写能力的硬件才行

2. MySQL 内部性能优化

2.1 group commit

不管是 binlog 还是 redolog 这种与慢设备交互的场景，在写盘时都会启用 group commit 功能，将一组数据一起提交

2.2 更新短路

MySQL 8.0 RR隔离级别，row_image=full 的情况下如果在更新时，发现整行数据都没有变更，那会直接跳过本次更新，这算一个优化，也算一个 bug，如下：

begin;
select * from tt where id = 1;
                                    update tt set c = 2 where id = 1;
update tt set c = 2 where id = 1;
select * from tt where id = 1;

查询结果为

可以看到，事务自己修改的数据，结果自己看不到，这就是更新短路优化的影响

2.3 查询短路

select count(*) from tt where id is null，这条语句会触发查询短路，id 不可能为 null，因此不会进入引擎层读数据计数，直接返回 0

select distinct id from tt where ……，这条语句由于 id 是主键，因此可以不使用临时表，如下： image.png 可以看到 Extra 中没有 Using temporary，没有用到临时表

2.4 AHI（Adaptive Hash Index）

自适应 hash 索引，buffer pool 中的索引，buffer pool 中页也是按照 B+ tree 组织的，因此查询时间复杂度为 O(logN)，如果某些页访问非常频繁，MySQL 会在 AHI 中直接创建 hash 索引，能将该页查询的时间复杂度优化为 O(1)，如下图所示：

AHI 中存放的数据 Key 是索引值组合的 hash 值，组合中，为了避免冲突严重，还会带上索引 id，Value 直接指向数据页中对应行的 物理位置（如页号 page_id + 行偏移量 offset）。

如何判断 AHI 是否有效，需要开启呢？看 hash searches 和 non-hash searches 的比例，如果能到 70、80% 以上，那我们认为 AHI 是有效的，建议开启，否则建议关闭，因为 AHI 有些 bug 还未发现和修复，如果不是性能很好，就关闭

2.5 change buffer

仅对二级非唯一索引有效，执行时将语句记录在 change buffer 中，可以将随机读写优化为顺序写，当真正需要查询数据时，将页从磁盘读到 buffer pool，然后看是否需要应用 change buffer，如果需要就应用后再返回

change buffer 也不存在丢失问题，因为 Change Buffer 的 元数据 存储在 内存（InnoDB Buffer Pool）  中，但所有变更操作都会记录到 Redo Log（磁盘文件），确保其内容的持久性。

数据写入流程： 当对二级索引的修改通过 Change Buffer 缓冲时：

  1. 变更操作记录在 内存中的 Change Buffer。
  2. 同时生成对应的 Redo Log 条目，并快速写入磁盘。
  3. 变更最终合并到实际索引页时，再写入磁盘的索引文件（.ibd）。

3. 使用优化

  1. time_zone

    默认配置为：time_zone=system， 建议设置为time_zone=+8:00，因为如果为 system，那么每次查询时都会去查询系统获得 time_zone，如果直接配置为东八区+8:00，那就不用查询系统了

  2. 减少交互次数

  3. 长短链接选择，连接中 binlog_cache 是只有等到连接断开才会被回收的，如果长链接过多，会占用过多内存

  4. redis，如何解决缓存一致性问题

    如果要求强一致性，可以用分布式锁强制双写

    如果只要求最终一致性，可以考虑基于 binlog 的同步，canal 解析 binlog 发送 kafka，消费者更新 redis，或者是直接双写，先写 MySQL，完了更新 redis

  5. 异构索引，从库单独加非唯一索引，开启 change buffer

  6. index skip scan，允许查询在缺少组合索引前导列的情况下，仍然利用该索引加速查询，从而避免全表扫描

  7. index condition pushdown（ICP）

  8. 前缀索引

  9. 全实例 mysqldump，指定库 restore 怎么办？

    1. 文本处理
    2. 权限处理
    3. blackhole 黑洞引擎
avatar
文章
阅读
粉丝