MySQL 优化方案

Note / 作者： /

查询优化

B+Tree

  1. 最左匹配（前缀）
    • 对于 like ‘C%’，由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。
    • 对于联合索引（A,B,C)，同理索引建立时是按ABC顺序的，例如只有A一样的情况下，B才是有序的。
    • 所以联合索引最左边最好是区分度最高的。
      composite-key-index
  2. 覆盖索引
    • 索引直接能满足需求，避免回表。
  3. 不对条件字段进行函数或表达式计算、尽量不用or查询（除非or的字段都有索引）、不用非等值查询。
  4. 关联查询（Join）优化：确保被驱动表的 Join 字段已经创建索引，并且数据类型一致；同时保证小表驱动大表，减少外层循环次数；
  5. 子查询优化：由于执行子查询时，MySQL 需要为内层查询语句建立临时表，外层查询从临时表中查询记录，执行完毕后又要销毁临时表，这样消耗过多CPU和IO资源。所以一般用 Join 替代子查询，不需要建立临时表且能够用到索引。
  6. Order By 优化：尽量使用索引列排序，WHERE A = X Order By B，如果A、B为同一字段就使用单列索引，不同字段则使用联合索引。
  7. Group By 优化：Group By 是先排序再分组，所以其使用索引的原则和 Order By 几乎一致，同时也遵照最左前缀匹配原则。
  8. 执行计划说明：
    • extra
      • Using temporary
        • 排序没有走索引、使用 union、子查询连接查询等 case。
      • Using filesort
        • 没有用索引排序，利用排序列+行指针放到到 sort buffer，然后进行快排；如果数据集大小超过 buffer 大小，那么会形成多个排序完成的小文件，再归并排序，消耗CPU，需要优化;也可以通过 sort buffer 调大，减少创建临时文件的成本，当然最好是做优化使用索引排序。
      • Using index
        • 覆盖索引
      • Using index condition
        • 索引下推（有这个标志不代表一定发生下推）
          • 发生在联合索引的前提下，例如用到联合索引第一个字段是范围查询或者Like等，正常是不会用到后续字段做联合查询的，但是开启索引下推后，引擎层会继续利用余下的索引字段做过滤（主要的核心点就在于把数据筛选的过程放在了存储引擎层去处理）ICP 的目的就是为了减少回表导致的磁盘 I/O，用在二级索引，是把 index filter 放在引擎层去做，引擎层用where条件利用索引直接过滤掉不符合的，减少回表数据量。
    • rows：预估扫描函数
    • type
      • all < index < range ~ index_merge < ref < eq_ref < const < system
    • key：实际选择的索引

参数优化

  1. innodb_buffer_pool_size

Innodb存储引擎缓存池大小（对于Innodb来说最重要的一个配置，如果所有的表用的都是Innodb，那么甚至建议将该值设置到物理内存的80%，Innodb的很多性能提升如索引都是依靠这个）

通过show engine innodb status \G我们可以看到free buffers、buffer pool hit rate以及evicted without access（数据被加载到buffer pool没有被访问前就又被推出buffer pool）。

  • 如果较长时间内即使业务高峰期，free buffers都很大，buffer pool hit rate 高于99.5%，evicted without access为0，则innodb_buffer_pool_size可能过量设置了。
  • 如果较长时间内尤其业务高峰期，free buffers都很小，buffer pool hit rate 低于99%，evicted without access不为0，则说明innodb_buffer_pool_size设置得太小，需要增大。
  1. innodb_log_file_size

事务日志，用来在mysql 崩溃后的恢复，大的logfile大小可以减少checkpoint的次数，减少disk I/O，但是也会增加崩溃后恢复时间

  1. innodb_flush_log_at_trx_commit

    定义数据库写redo log buffer到磁盘的频率以及方式，正常设置为 1

  2. sync_binlog

    控制 MySQL server 将 bin log 同步到磁盘的频率，MYSQL RDS默认设置为最安全的 1

参考 MYSQL RDS 写入性能参数优化

深分页优化

select id,name,balance from account where update_time> '2021-09-19' limit 100000,10;

深分页造成慢SQL的原因：

  • limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。

解决方案：

  1. 子查询优化：子查询内利用覆盖索引，外层查询使用索引即可查询，避免了深分页的回表查询 
    select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;（可以加下时间条件到外面的主查询）
  2. Inner Join 延迟关联：原理和子查询一样 
    SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;
  3. 滚动翻页：查询条件增加上次查询最大 / 小值，例如 where id > {last_max_id}，这种方式能够把深分页性能优化到毫秒级，但是这种方式的局限性：
    1. 在于要求查询的字段具备类似自增的特性；
    2. 不支持跳页查询；
    3. 在查询最后一页时，如果查询结果不满 pageSize，那么引擎需要遍历全表才能终止查询，数据量很大时存在性能问题。
  4. 区间限制：滚动翻页虽然可以极大优化深分页的性能，但是存在在最后一页查询效率骤减的问题。滚动翻页的问题在于最后一页查询不知道何时终止查询，此时我们可以（提前）查询出表数据中最大 / 最小 id 传入滚动翻页的查询语句中作为限制条件，类似 select MAX(id) AS max_id from table where condition = xx; where id > {last_max_id} and id < {max_id} and condition… ，另外这个 max_id 由于是最后一页才需要的，所以可以在第一次查询接口时就异步进行 max_id 的计算然后存入缓存中。

读写分离

主库写，从库读。但带来的问题是主从延迟的一致性问题。

主从延迟常见兼容/解决方案：

  1. 写后一定时间窗口内读主库（目前公司采用的方案：同一会话中，写请求结束后指定时间内的读请求也发到主库，通过MySQL 上的 Proxy 层实现）
  2. 区分核心场景和非核心场景，核心场景只读写主库（例如金融场景）
  3. 事务内的 sql 不进行读写分离，全部发到主库
  4. 缓存记录变更 key，用于标识读主从库，由于引入cache增加了rt

主从一致优化：

  1. 大事务拆小事务
  2. 同步复制
  3. 半同步复制 ( Semi-Sync )：master 节点在执行完客户端提交的事务后不是立刻返回结果给客户端，而是等待至少一个 slave 节点接收并写到 relay log 中才返回给客户端。但是半同步复制会在网络故障导致复制超时的情况下，使用异步复制作为兜底。仍然会丢失数据。

    Semi-Sync 几个重要参数：

    1. rpl_semi_sync_master_wait_for_slave_count 主库要求返回ACK的从库的个数
    2. rpl_semi_sync_master_timeout 主库等待从库返回ACK的最大时长,超时则转为异步复制
    3. rpl_semi_sync_master_wait_point 5.5默认 AFTER_COMMIT, 5.7默认AFTER_SYNC

    AFTER_COMMIT：先提交事务到engine，后等待 slave 写入，返回客户端。
    AFTER_SYNC：先等待 slave 写入，后提交事务到engine，返回客户端。

    AFTER_COMMIT 存在的问题：

    • case1 binglog未发送到从库：master提交事务后，其他客户端已经能够看到该事务数据，但是当前客户端需要等待从库同步，若master故障发生主从切换，已提交事务可能会丢失。导致其他客户端可能出现之前看到的数据丢失的情况。

分库分表

  1. 原因：
    1. IO瓶颈
      • 读IO瓶颈：热点数据多，数据库缓存放不下，查询时产生大量磁盘IO，查询速度较慢，导致活跃连接数变多，可以采用主从，读写分离，分库分表来解决
      • 写IO瓶颈：出现大量写，只能分库分表
    2. CPU瓶颈
      • 查询存在大量函数或非索引字段查询，可以分库分表
  2. 分库/表中间件：
    1. Sharding-Jdbc

      客户端接入，对业务有一定侵入，但排查问题方便

    2. MyCat

      接入方便，代码耦合度低；需要单独部署，黑盒，排查问题复杂

  3. 方案细节：

    • 确定数据量级，如日订单量 1000W
    • 确定分表 Key，例如订单分库分表主要用于下单和查询，订单查询主要包括根据买家 id 和订单号查询，一般采用基因法分表，这样一来可以根据订单号定位分表，二来同一个买家数据会落到同一个表中，通过用户 ID 也可以定位到对应分表。

      基因法分表关键在于分表数量必须满足 2^N， 此时 A Mod B 和 A & (B-1) 结果一致，例如 9 % 4 = 1001 % 100 = 1001 & (100-1) = 1001 & 11 = 1，所以实际上这里取余的结果参与计算的仅为最后 N 位，那么我们可以在分表时，订单最后 N 位为用户 ID（或截取 N 位用户ID）。

  4. 常见问题：

    • 数据倾斜
      • 可以不用简单的 hash 分表，而是通过一致性hash，这样数据倾斜时，只会影响一小部分数据
    • 数据多维度查询困难
      • 通过 ES+HBASE 等异构数据库方案
        • 将索引与数据存储隔离。可能参与条件检索的字段都会在ES中建一份索引，例如商家，商品名称，订单日期等。所有订单数据全量保存到HBase中。我们知道HBase支持海量存储，而且根据rowkey查询速度超快。而ES的多条件检索能力非常强大。可以说，这个方案把ES和HBase的优点发挥地淋漓尽致。看一下该方案的查询过程：先根据输入条件去ES相应的索引上查询符合条件的rowkey值，然后用rowkey值去HBase查询，后面这一步查询速度极快，查询时间几乎可以忽略不计
    • 错误的 Hash 分片方案：
      • 非互质关系导致的数据偏斜 
         // 对库数量取余结果为库序号
 int dbIdx = Math.abs(hash % DB_CNT);
 // 对表数量取余结果为表序号
 int tblIdx = Math.abs(hash % TBL_CNT);

        以 10 库 100 表为例，如果一个 Hash 值对 100 取余为 0，那么它对 10 取余也必然为 0。

        这就意味着只有 0 库里面的 0 表才可能有数据，而其他库中的 0 表永远为空。

      • 扩容难以持续

        我们把 10 库 100 表看成总共 1000 个逻辑表，将求得的 Hash 值对 1000 取余

        // 1. 总分片数
int sumSlot = DB_CNT * TBL_CNT;
// 2. 分片序号
int slot = Math.abs(hash % sumSlot);
// 3. 计算库序号和表序号的错误案例
int dbIdx = slot % DB_CNT ;
int tblIdx = slot / DB_CNT ;

        使用了库数量作为影响表序号的因子，导致扩容时表序号偏移而无法进行

    • 常用 Hash 分片方案：

      • 标准的二次分片法： 
        // 1. 总分片数
int sumSlot = DB_CNT * TBL_CNT;
// 2. 分片序号
int slot = Math.abs(hash % sumSlot);
// 3. 重新修改二次求值方案
int dbIdx = slot / TBL_CNT ;
int tblIdx = slot % TBL_CNT ;
      • 关系表冗余
        • 可以将分片键对应库的关系通过关系表记录下来，我们把这张关系表称为”路由关系表”
      • 基因法
        • 使用原分片键中的某些基因(例如前四位)作为库的计算因子，而使用另外一些基因作为表的计算因子 
           int dbIdx = Math.abs(userId.substring(0, 4).hashCode() % DB_CNT );
 int tblIdx = Math.abs(userId.hashCode() % TBL_CNT);
      • 剔除公因数法
        • 针对不少业务从 N 库 1 表升级到 N 库 M 表下，需要维护库序号不变的场景下可以考虑
        int dbIdx = Math.abs(userId.hashCode() % DB_CNT);
// 计算表序号时先剔除掉公约数的影响
int tblIdx = Math.abs((userId.hashCode() / TBL_CNT) % TBL_CNT);
      • 一致性 Hash 法

冷热分离

在大部分老数据极少查看且进入终态不存在修改的情况下，可以采用冷热分离有效缓解数据库数据量过多的压力。

冷库可以采用 ES、HBase、CK等。

需要考虑的问题：

  1. 对于少数需要查看的场景，比如订单的退款，针对若干年前的订单要如何查询？（查冷库或从冷库恢复到热库）
  2. 新增字段时，冷库是否需要同步
  3. 迁移热数据一致性方案
  4. 唯一索引破坏（若唯一键为递增则没有这个问题）

架构优化

针对 MySQL 高可用优化的各种架构，核心思路一致：

  • 对主从复制集群中的Master节点进行监控
  • 通过VIP自动的对Master进行迁移
  • 重新配置集群中的其它slave对新的Master进行同步

MMM

MMM (Multi Master Replication Manager) 需要两个Master，同一时间只有一个Master对外提供服务，可以说是主备模式。

MMM

目前已经淘汰了，缺点：

  1. 由于 M – M 之间采用异步复制，故障情况下容易丢失事务（开启半同步复制缓解）
  2. 社区不活跃
  3. 不支持 GTID
  4. mmm-manager存在单点，一旦由于某些原因挂掉，整个集群就失去了高可用
  5. 只支持一主一从架构

MHA

MHA（MySQL Master High Availability）是由Facebook工程师Yoshinori Matsunobu开发的一款MySQL高可用软件。MHA只负责MySQL主库的高可用。主库发生故障时，MHA会选择一个数据最接近原主库的候选主节点（这里只有一个从节点，所以该从节点即为候选主节点）作为新的主节点，并补齐和之前Dead Master 差异的Binlog。数据补齐之后，即将写VIP漂移到新主库上。

目前公司比较多采用这个方案并结合 DbProxy 代理层实现例如分片路由、读写分离、负载均衡、安全认证、透明分表等能力。

MHA

优点：

  1. 支持多种复制拓扑结构，包括一主一从、一主多从、多主复制等。
  2. 能自动选出最合适的 slave 成为 master
  3. MHA会尝试从旧的Master中恢复旧的二进制日志，只是未必每次都能成功，这样的自动数据补偿能力，在主库异常崩溃时能够最大程度的保证数据的一致性。
  4. MHA除了支持日志点的复制还支持GTID的方式

缺点：

  1. 未对 slave 监控
  2. 存在数据丢失风险（自古CP难两全）
  3. MHA 管理节点单点问题（可以通过在每个 MySQL 节点部署agent，在 MHA 故障时进行投票选出新 master 节点，防止只通过 Manager 来切换，去除MHA单点）

MGR

MGR（MySQL Group Replication） 利用 Paxos 将一致性和切换过程下推到数据库内部，作为 MySQL 官方插件，向上层屏蔽了切换细节。MGR 由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。

主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。
MGR

MGR_PROC

优点：

  1. 强一致性：是通过分布式一致性算法 Paxos 来保证各节点状态相同。
  2. 高可用：只要不是大多数节点坏掉就可以继续工作。
  3. 自检机制：当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理。内置了自动化脑裂防护机制。
  4. 弹性：节点的新增和移除都是自动的。新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致。如果某节点被移除了，其他节点自动感知，自动维护新的group信息。
  5. 灵活：可用使用单主模式和多主模式。单主模式下，会自动选主，所有更新操作都在主上进行。多主模式下，所有节点都可以同时处理更新操作。

缺点：

  1. 必须开启 row 格式 binlog
  2. 不支持 Serializable 隔离级别

PXC

PXC（Percona XtraDB Cluster）是基于Galere协议的强一致性、无同步延迟的MySQL高可用解决方案（保证CP）。但是性能上有一定牺牲。目前国内去哪儿深度使用PXC方案，之前面试去哪儿被问到 PXC 一脸懵 -_-。

PXC

特性：

  • 1）同步复制，事务要么在所有节点提交或不提交。
  • 2）多主复制，可以在任意节点进行写操作。
  • 3）在从服务器上并行应用事件，真正意义上的并行复制。
  • 4）节点自动配置，数据一致性，不再是异步复制。

PXCFlow

流程：

  • 首先客户端先发起一个事务，该事务先在本地执行，执行完成之后就要发起对事务的提交操作了。
  • 在提交之前需要将产生的复制写数据集发给 Group，然后获取到一个全局的事务ID号，一并传送到其他节点上面。
  • 其他节点通过合并数据之后，发现没有冲突数据，执行apply_cd和commit_cb动作，否则就需要取消此次事务的操作。
  • 当前server节点通过验证之后，执行提交操作，并返回OK，如果验证没通过，则执行回滚。
  • 在生产中至少要有3个节点的集群环境，如果其中一个节点没有验证通过，出现了数据冲突，那么此时采取的方式就是讲出现不一致的节点踢出集群环境，而且它自己会执行shutdown命令，自动关机。

缺点：

  1. 木桶原理，由于需要所有节点同步，集群性能受限于性能最差节点
  2. 多节点并发写入，锁冲突严重、存在写扩大问题

参考
美团数据库高可用架构的演进与设想
网易对MGR的优化与实践
千万级数据深分页查询SQL性能优化实践

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

Subscribe
Subscribe
Scroll to Top