MySQL 优化与运维

MySQL 优化与运维

基本命名和约束规范

  • 表字符集选则 UTF-8，如果需要 Emoji 表情，需要使用 UTF8mb4 (MySQL 5.5.3 以后支持)
  • 存储引擎尽量使用 InnoDB
  • 变长字符串尽量使用 varchar
  • 单标数据量控制在 1 亿以下
  • 库名、表名、字段名不使用保留字
  • 库名、表名、字段名、索引名使用小写字母，使用划线分割，见名知意
  • 表名不要设计的过长，尽可能用最少的字符表达表的用途

字段规范

  • 所有字段均为 NOT NULL，除非真的想要存储 NULL (解释：NULL 的存储浪费表存储空间，InnoDB 需要额外一个字节存储，NULL 过多会影响优化器选择执行计划)
  • 字段类型满足要求条件下越小越好，使用 UNSIGNED 存储非负整数，实际需要存储负数的场景不多
  • 使用 TIMESTAMP 存储时间 (只能存储到 2038 年)
  • 使用 varchar 存储变长字符串，varchar(M) 的 M 指的是字符数，不是字节数，使用 UNSIGNED INT 存储 IPV4 地址，不过这种方式存储不了 IPV6
  • 使用 DECIMAL 存储精确浮点数，用 float 类型存储可能会存在数据误差
  • 少用 blob text

索引范围

  • 单个索引字段数不超过 5，单表索引数量不超过 5，索引设计遵循 B+Tree 索引最左前缀匹配原则
  • 选则区分度高的列作为索引
  • 建立的索引能覆盖 80% 主要的查询，不求全，解决问题的主要矛盾就好
  • DML 要和 order by、group by 字段建立合适的索引
  • 避免索引的隐式转换
  • 避免冗余索引

MySQL 可用性

无缝切换主库的方案：

核心思路：让新主库和从库停留在相同位置，主要依赖 slave start until 语句，结合双主结构，考虑自增问题。

MySQL 日志

性能优化

复制优化

MySQL 复制原理图，右边被框柱的向上的剪头，就是受人诟病的单线程问题。

单线程问题是主从延时的重要原因之一，解决方案有如下几种：

  • 官方 5.6 以上版本原生多线程同步方案。
  • Tungsten 为代表的第三方并行复制工具
  • Sharding

MySQL 5.6 版本的并行复制原理图，基于库级别的复制，如果只有一个库，则意义不大：

MySQL 5.7 引入基于 logical timestamp 的并行复制方案，不再受限于库的个数，效率大大提升。

MySQL 原生只支持异步复制，安全性比较差，从 5.5 开始支持半同步复制。

5.7 版本之后，半同步可以配置指定多个从库参与半同步复制，之前都是默认一个从库：

主从延时问题

从库压力比较大，非常容易导致延时，解决方案：

  • 定位延时瓶颈：I/O 压力通过升级硬件，如替换 SSD 解决；I/O 和 CPU 都不是问题，那么可能是 SQL 单线程问题，可以改为并行复制方案
  • 还有问题，考虑 Sharding 方案

通过 seconds behind master 判断是否延时，非常不靠谱，通过 heart 表插入时间戳这种机制判断延时更靠谱。

复制时注意的点：

  • binlog 格式，建议采用 row 格式，数据一致性更好。
  • Replication filter 应用。

UUID 函数会导致 Mixed 格式出现导致主从不一致的 SQL，Mixed 这种中间状态最坑人，row 的可靠性最好。

InnoDB 优化

主要优化参数：

  • innodb_file_per_table = 1
  • innodb_buffer_pool_size，根据数据量和内存合理设置
  • innodb_flush_log_at_trx_commit = 0 1 2，一般设置为 2 可以满足要求
  • innodb_log_file_size，可以适当设置大一些
  • innodb_page_size，压缩时可以用
  • innodb_flush_method = O_DIRECT
  • innodb_undo_directory，可以把 undo 文件单独放到告诉设备 (5.6 版本以上)
  • innodb_buffer_pool_dump_at_shutdown 可以用于数据预热 (5.6 版本以上)
  • innodb_undo_log_truncate，支持对单独存储的 undo 文件进行压缩 (5.7 版本以上)

InnoDB 在 SSD 上的优化：

  • 5.5 版本以上，提供 innodb_write_io_threads 和 innodb_read_io_threads
  • innodb_io_capacity 需要调大
  • 日志文件和 redo 放到机械硬盘，undo 放到 SSD，建议这样做
  • atomic write，不需要 Double write Buffer
  • InnoDB 压缩
  • 单机多实例

InnoDB 随机读写的文件：

  • datadir
  • innodb_data_file_path
  • innodb_undo_directory

InnoDB 顺序读写的文件：

  • innodb_log_group_home_dir
  • log-bin

优化相关的 Case

隔离级别建议设置为已提交级别，有利于更高并发，并且可以满足大多数场景需求。

JOIN 优化

JOIN 有两种 JOIN 算法：

（1） Index Nested-Loop Join (NLJ)

SELECT * FROM t1 straight_join t2 ON (t1.a = t2.a);

执行流程：遍历 t1 表，取出每一行的 a 值，去表 t2 中查找满足条件的记录。

  • t1 表做全表扫描 (小表做驱动表)
  • t2 表走的是索引，即树搜索过程

（2）Block Nested-Loop Join (BNL)

如果 t2 表没有索引，那么会用 Simple Nested-Loop Join 算法：

SELECT * FROM t1 straight_join t2 ON (t1.a = t2.b);

那么每一次根据 a 去匹配的时候，都要去全表扫描 t2 表。这种算法看起来效率非常低下，MySQL 默认不会用这种算法，而使用了 BNL 算法。

执行流程：t1 数据读入线程内存 join_buffer，由于我们写的是 SELECT *，因此整个 b1 表都放入了内存，扫描表 t2，表 t2 的每一行取出来，根 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。这个对比是在内存中对比的，因此效率好很多。

join_buffer 大小由参数 join_buffer_size 设定，默认值 256K，如果放不下表 t1，那么就需要分段放，比如每次放入 100行，然后 t2 每一行跟 join_buffer 对比，对比完了满足条件的作为结果集的一部分返回；然后清空 join_buffer，继续放入下 100 行，如此反复。Block 的含义也在这个地方，就是分块 join。

如果 EXPLAIN 的 EXTRA 字段里面出现了 Block Nested Loop 字样，就代表使用的是 BNL 算法，这种应该尽量规避，不要使用这种算法。

（3）Multi-Range Read 优化

下面这条语句涉及到回表查询，那么回表默认情况下是不是一条一条的去查询数据 ?

SELECT * FROM t1 WHERE a >= 1 AND a <= 100;

MRR 优化思路：根据索引 a，定位到记录，将 id 放入 read_rnd_buffer 中；堆 read_rnd_buffer 中的 id 进行递增排序；排序后的 ID 数组，依次到主键 ID 索引中查询记录，作为结果返回。也就是改为了按照主键的 id 递增顺序去查询记录，接近顺序读，提升读性能。

（4）Batched Key Access

一次从 t1 表多拿出一些数据，不要每次拿一条了，这些拿出来的数据放到 join_buffer 中。

（5）使用临时表

有些表的字段不适合加上索引，所以可以使用临时表来优化：

craete temporary table temp_t(id int primary key, a int, b int, index(b)) engine = innodb;
insert into temp_t select * from t2 where b >= 1 and b <= 2000;
select * from t1 join temp_t on (t1.b = temp_t.b);

MySQL 不停机更换数据库

对 MySQL 做了分库分表之后，需要从原来的单实例数据库迁移到新的数据库集群上。系统从传统部署方式向云上迁移的时候，也需要从自建的数据库迁移到云数据库上。

首先要做的就是，把旧库的数据复制到新库中。因为旧库还在服务线上业务，所以不断会有订单数据写入旧库，我们不仅要往新库复制数据，还要保证新旧两个库的数据是实时同步的。所以，我们需要用一个同步程序来实现新旧两个数据库实时同步。

怎么来实现两个异构数据库之间的数据实时同步，我们可以使用 Binlog 实时同步数据。如果源库不是 MySQL 的话，就麻烦一点儿，但也可以参考我们讲过的，复制状态机理论来实现。这一步不需要回滚，原因是，只增加了一个新库和一个同步程序，对系统的旧库和程序都没有任何改变。即使新上线的同步程序影响到了旧库，只要停掉同步程序就可以了。

然后，我们需要改造一下订单服务，业务逻辑部分不需要变，DAO 层需要做如下改造：

  • 支持双写新旧两个库，并且预留热切换开关，能通过开关控制三种写状态：只写旧库、只写新库和同步双写。
  • 支持读新旧两个库，同样预留热切换开关，控制读旧库还是新库。

然后上线新版的订单服务，这个时候订单服务仍然是只读写旧库，不读写新库。让这个新版的订单服务需要稳定运行至少一到二周的时间，期间除了验证新版订单服务的稳定性以外，还要验证新旧两个订单库中的数据是否是一致的。这个过程中，如果新版订单服务有问题，可以立即下线新版订单服务，回滚到旧版本的订单服务。

稳定一段时间之后，就可以开启订单服务的双写开关了。开启双写开关的同时，需要停掉同步程序。这里面有一个问题需要注意一下，就是这个双写的业务逻辑，一定是先写旧库，再写新库，并且以写旧库的结果为准。

旧库写成功，新库写失败，返回写成功，但这个时候要记录日志，后续我们会用到这个日志来验证新库是否还有问题。旧库写失败，直接返回失败，就不写新库了。这么做的原因是，不能让新库影响到现有业务的可用性和数据准确性。上面这个过程如果出现问题，可以关闭双写，回滚到只读写旧库的状态。支持双写新旧两个库，并且预留热切换开关，能通过开关控制三种写状态：只写旧库、只写新库和同步双写。1.支持读新旧两个库，同样预留热切换开关，控制读旧库还是新库。2.

切换到双写之后，新库与旧库的数据可能会存在不一致的情况，原因有两个：一是停止同步程序和开启双写，这两个过程很难做到无缝衔接，二是双写的策略也不保证新旧库强一致，这时候我们需要上线一个对比和补偿的程序，这个程序对比旧库最近的数据变更，然后检查新库中的数据是否一致，如果不一致，还要进行补偿。

开启双写后，还需要至少稳定运行至少几周的时间，并且期间我们要不断地检查，确保不能有旧库写成功，新库写失败的情况出现。对比程序也没有发现新旧两个库的数据有不一致的情况，这个时候，我们就可以认为，新旧两个库的数据是一直保持同步的。

接下来就可以用类似灰度发布的方式，把读请求一点儿一点儿地切到新库上。同样，期间如果出问题的话，可以再切回旧库。全部读请求都切换到新库上之后，这个时候其实读写请求就已经都切换到新库上了，实际的切换已经完成了，但还有后续的收尾步骤。

再稳定一段时间之后，就可以停掉对比程序，把订单服务的写状态改为只写新库。到这里，旧库就可以下线了。注意，整个迁移过程中，只有这个步骤是不可逆的。但是，这步的主要操作就是摘掉已经不再使用的旧库，对于在用的新库并没有什么改变，实际出问题的可能性已经非常小了。

对比和补偿程序

在上面的整个切换过程中，如何实现这个对比和补偿程序，是整个这个切换设计方案中的一个难点。这个对比和补偿程序的难度在于，我们要对比的是两个都在随时变换的数据库中的数据。这种情况下，我们没有类似复制状态机这样理论上严谨实际操作还很简单的方法，来实现对比和补偿。但还是可以根据业务数据的实际情况，来针对性地实现对比和补偿，经过一段时间，把新旧两个数据库的差异，逐渐收敛到一致。

像订单这类时效性强的数据，是比较好对比和补偿的。因为订单一旦完成之后，就几乎不会再变了，那我们的对比和补偿程序，就可以依据订单完成时间，每次只对比这个时间窗口内完成的订单。补偿的逻辑也很简单，发现不一致的情况后，直接用旧库的订单数据覆盖新库的订单数据就可以了。

这样，切换双写期间，少量不一致的订单数据，等到订单完成之后，会被补偿程序修正。后续只要不是双写的时候，新库频繁写入失败，就可以保证两个库的数据完全一致。

比较麻烦的是更一般的情况，比如像商品信息这类数据，随时都有可能会变化。如果说数据上有更新时间，那我们的对比程序可以利用这个更新时间，每次在旧库取一个更新时间窗口内的数据，去新库上找相同主键的数据进行对比，发现数据不一致，还要对比一下更新时间。如果新库数据的更新时间晚于旧库数据，那可能是对比期间数据发生了变化，这种情况暂时不要补偿，放到下个时间窗口去继续对比。另外，时间窗口的结束时间，不要选取当前时间，而是要比当前时间早一点儿，比如 1 分钟前，避免去对比正在写入的数据。

如果数据连时间戳也没有，那只能去旧库读取 Binlog，获取数据变化，然后去新库对比和补偿。

有一点需要说明的是，上面这些方法，如果严格推敲，都不是百分之百严谨的，都不能保证在任何情况下，经过对比和补偿后，新库的数据和旧库就是完全一样的。但是，在大多数情况下，这些实践方法还是可以有效地收敛新旧两个库的数据差异，你可以酌情采用。

场景

连接池满了

1.查看连接数配置（MySQL服务器允许的最大连接数16384）

show variables like '%max_connections%'

2.查看当前连接数

  • 方法1：show full processlist （需要用管理员帐号）
  • 方法2：mysqladmin -u root -proot status（Threads 值是当前连接数）

如果当前连接数和连接数配置接近，说明连接数差不多满了。

海量数据寻找记录

从海量数据中快速找出想要的数据，假设有一张用户表，保存了几年间用户每日的登录时间，数据超过万亿条，如果快速找出每个用户最早的登录时间？

MAP REDUCE 的思路：DUMP 文件到日志队列，按照用户 ID 进行 HASH 分区，然后多台机器读取队列，只保持最早时间的，最后合并，想要加快速度的话，每个分区可以多个消费者。

Online DDL (改表)

改表会直接触发表锁，改表过程非常耗时，对于大表修改，无论是字段类型调整还是字段增删，都需要谨慎操作，防止业务表操作被阻塞，大表修改往往有以下几种方式。

  • 主备改表切换，先改冷库表，再执行冷热切换；
  • 直接操作表数据文件，拷贝文件替换；
  • 使用类似 percona-toolkit 工具操作表。

常见方法：

原生 MySQL 执行 DDL 需要锁表，在锁表期间无法写入数据，这对服务影响很大，下面给出 Facebook OSC 和 5.6 原生 OSC 的 2 种靠谱方案：

MySQL 5.6 的 OSC 方案解决不了 DDL 时到从库延时的问题，所以建议使用 Facebook OSC，这种思路更优雅。

后来 Percona 公司根据 Facebook OSC 的思路，用 Perl 重写了一版，就是现在使用的很多的 pt-online-schema-change，它的优点：

  • 无阻塞写入
  • 完善的条件检测和延时负载策略控制

它的限制如下：

  • 比 alter table 时间更长
  • 修改的表需要有唯一键或主键
  • 在同一端口上的并发修改不能太多

参考