Loading

MySQL 进阶篇 Part 3

😉 本文共5623字，阅读时间约10min

目录

主从复制

什么是主从复制？

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

image

MySQL 复制的优点主要包含以下三个方面：

  • 主库出现问题，可以快速切换到从库提供服务。
  • 实现读写分离，降低主库的访问压力。
  • 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

MySQL主从复制的核心就是 二进制日志，具体的过程如下：

IO thread （binlog -> relay log）

SQL thread (relay log -> replay)

image

分库分表

问题分析

在单表单库的情况下，当数据库表的数据量逐渐累积到一定的数量时（5000W行或100G以上），操作数据库的性能会出现明显下降，即使我们使用索引优化或读写库分离，性能依然存在瓶颈。造成IO瓶颈和CPU瓶颈。

为了解决上述问题，我们需要对数据库进行分库分表处理。

image

分库分表的中心思想都是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。

拆分策略

分库分表的形式，主要是两种：垂直拆分和水平拆分。而拆分的粒度，一般又分为分库和分表，所以组成的拆分策略最终如下：

垂直拆分

  1. 垂直分库

    以表为依据，根据业务将不同表拆分到不同库中。

  2. 垂直分表

    以字段为依据，根据字段属性将不同字段拆分到不同表中。

水平拆分

  1. 水平分库

    可以把一个表的数据（按数据行）分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。

    它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题（数据路由问题后边介绍）。

  2. 水平分表

    可以把一个表的数据（按数据行）分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

在业务系统中，为了缓解磁盘IO及CPU的性能瓶颈，到底是垂直拆分，还是水平拆分；具体是分库，还是分表，都需要根据具体的业务需求具体分析。

水平分库 路由处理实现

  • shardingJDBC：基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
  • MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者。

image

mycat

分片规则

  1. 固定分片hash算法：类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与1111111111 进行位 & 运算

  2. 字符串hash解析算法：取字符串中的指定位置的子字符串, 进行hash算法， 算出分片。

  3. 按天、月分片

原理

image

在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。

水平分库的分布式事务问题

在提交订单时，除了创建订单之外，我们还需要扣除相应的库存。而订单表和库存表由于垂直分库，位于不同的库中，这时我们需要通过分布式事务来保证提交订单时的事务完整性。

通常，我们解决分布式事务有两种通用的方式：两阶事务提交（2PC）以及补偿事务提交（TCC）。

垂直分库的跨节点JOIN查询问题

用户在查询订单时，我们往往需要通过表连接获取到商品信息，而商品信息表可能在另外一个库中，这就涉及到了跨库JOIN查询。

通常，我们会冗余表或冗余字段来优化跨库JOIN查询。对于一些基础表，例如商品信息表，我们可以在每一个订单分库中复制一张基础表，避免跨库JOIN查询。而对于一两个字段的查询，我们也可以将少量字段冗余在表中，从而避免JOIN查询，也就避免了跨库JOIN查询。

水平分库的跨节点查询问题

我们知道，当用户在订单列表中查询所有订单时，可以通过用户ID的Hash值来快速查询到订单信息，而运营人员在后台对订单表进行查询时，则是通过订单付款时间来进行查询的，这些数据都分布在不同的库以及表中，此时就存在一个跨节点分页查询的问题了。

通常一些中间件是通过在每个表中先查询出一定的数据，然后在缓存中排序后，获取到对应的分页数据。这种方式在越往后面的查询，就越消耗性能。

全局主键ID问题

在分库分表后，主键将无法使用自增长来实现了，在不同的表中我们需要统一全局主键ID。因此，我们需要单独设计全局主键，避免不同表和库中的主键重复问题。

使用UUID实现全局ID是最方便快捷的方式，即随机生成一个32位16进制数字，这种方式可以保证一个UUID的唯一性，水平扩展能力以及性能都比较高。但使用UUID最大的缺陷就是，它是一个比较长的字符串，连续性差，如果作为主键使用，性能相对来说会比较差。
我们也可以基于Redis分布式锁实现一个递增的主键ID，这种方式可以保证主键是一个整数且有一定的连续性，但分布式锁存在一定的性能消耗。

我们还可以基于Twitter开源的分布式ID生产算法——snowflake解决全局主键ID问题，snowflake是通过分别截取时间、机器标识、顺序计数的位数组成一个long类型的主键ID。这种算法可以满足每秒上万个全局ID生成，不仅性能好，而且低延时。

扩容问题

range 来分，好处在于说，扩容的时候很简单，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了；缺点，但是大部分的请求，都是访问最新的数据。实际生产用 range，要看场景。

hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表。

读写分离

把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。读多写少。

image

一主一从读写分离

MySQL的主从复制，是基于二进制日志（binlog）实现的。

image

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。

img

  • 优点：从库支持读，分担了主库的压力，提升了并发度。一个机器故障了可以自动切换，操作比较简单。
  • 缺点：一台从库，并发支持还是不够，并且一共两台机器，还是存在同时故障的机率，不够高可用。

MHA

开源的MYSQL故障切换方案；全称：Master High Availability，故障切换时间10-30s，主库挂了，从库升级为master

原理：

  1. MHA由node和manager组成。
  2. node: 就是所有数据库机器都需要部署的， master是一个node，slave也是一个node; 相当于监控客户端。
  3. manager: Manager相当server，它会对node进行管理，配置检查，binlog和relay log的获取，执行切换任务等；
  4. MHA切换主要涉及下面几个步骤：
    1. MHA每隔3秒钟探测一次MASTER；
    2. 如果manager探测到MASTER 故障，无法访问，Manager会执行下面操作：
      1. 从其他node发起连接，检查MASTER实例是否能够登陆；
    3. 如果所有Node均连接失败，则开始故障转移。选新slave，对比所有SLAVE，从最新SALVE中同步差异realy log给其他slave;最终确保所有SLAVE数据一致。

主从同步延迟

  • 二次查询
    先去从库读取数据，找不到时就去主库进行数据读取。该操作容易将读压力返还给主库，为了避免恶意攻击，建议对数据库访问API操作进行封装，有利于安全和低耦合。
  • 根据业务特殊处理
    根据业务特点和重要程度进行调整，比如重要的，实时性要求高的业务数据读写可以放在主库。对于次要的业务，实时性要求不高可以进行读写分离，查询时去从库查询。

一主多从

img

  • 优点：多个从库支持读，分担了主库的压力，明显提升了读的并发度。
  • 缺点：只有一台主机写，因此写的并发度不高

mycat分片集群

img

  • mycat 分片存储，每个分片配置一主多从的集群。
    • 优点：解决高并发高数据量的高可用方案
    • 缺点：维护成本比较大。

MRR 多范围读取

Using MRR

每次根据主键回表时，虽然是按照非聚集索引排好序的，但是这些记录的主键id是无序的，也就是说，这些非聚集索引记录对应的聚集索引记录所在的页面也是无序的。每次回表都要重新定位页的位置，将聚集索引页读取出来，这些非连续I/O的性能开销很大。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

于是，便有了MRR(Disk-Sweep Multi-Range Read，多范围读取)的优化措施。即先读取一部分满足条件的非聚集索引记录，将它们的主键值排好序之后再统一回表，相比于每读取一条非聚集索引记录就回表，这样会节省一些I/O开销（比如记录更有可能在同一页）。

对于覆盖查询或者读取全表数据的情况，Mysql不会使用MRR优化，因为并不会产生优化效果。

ICP 索引下推

Using Index Condition

索引下推（index condition pushdown，ICP），有效的减少了回表次数，提高了查询效率。

过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。对于二级索引，只要能把条件甩给下面的存储引擎，存储引擎就会进行过滤，不需要我们干预。

例子：联合索引即使后续不满足条件，也可以进行后续where 条件的过滤，也就是将where的部分过滤放在了存储引擎层。这样减少了数据量，就减少回表次数。

索引下推

索引合并优化

当你兴高采烈地在各个字段建立索引，并在这些索引上使用WHERE+OR的SELECT操作，以为MySQL不会聪明到使用各个列的索引时，神奇的事情发生了：你看到了从没见过的type和Extra：

img

这是因为MySQL使用了名为index merge的优化操作。

这种优化会在你用多种不同的type为range的select检索行，并且最后将它们合并成一个时出现。这个优化仅仅能够合并单个表里的索引扫描，并不能跨表。这些索引合并可以产生底层扫描出的结果后的**并集、交集。

  1. 索引交/并集算法对所有使用的索引执行同时扫描，并生成从合并索引扫描接收的行序列的交/并集。

小表驱动大表

  1. 驱动表与被驱动表
    1.当使用left join时，左表是驱动表，右表是被驱动表
    2.当使用right join时，右表是驱动表，左表是被驱动表
    3.当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

  2. sql优化中，永远是以小表驱动大表。

MySQL IO

随机IO和顺序IO

随机IO：寻道、旋转、数据传输

顺序IO远快于随机IO，随机IO一次IO读到的有效数据太少

插入

如果你要执行2条insert语句，但是你的log file正好只能容纳一条，那么在写入磁盘的时候。只有一条可以执行，那么比如正好在磁盘6。过了一段时间在执行第二条语句，数据跑到了磁盘32的位置。这种操作是随机io。

如果log file正好可以全部容纳2条语句，两条语句同时被刷新到硬盘。第一条在磁盘6，第二条在磁盘7。那么这就是顺序io。

查询

为什么要使用聚集索引，因为B+树的叶子都是数据。如果数据的基本是连续的，那么基本需要顺序i/o即可。如果底层存的是指针，那么就会走随机i/o性能查了一大截（因为你需要根据指针一个一个找数据）。

MySQL的查询需要遍历几次B+树，理论上需要几次磁盘I/O？

  1. 查询遍历几次？

    主键索引从上至下遍历一次B+树，直到找到具体的主键，拿到叶子结点存储的数据。

    二级索引需要遍历两次B+树，第一次遍历是找到对应的主键，第二次遍历是根据主键找到具体的数据。

  2. 聚集索引和非聚集索引执行一次sql的io次数

    1. 聚集索引：
      1. 数据量小的话，直接把索引放到内存中，内存的O(logn)消耗是远远低于磁盘io的,所以可以忽略不计
      2. 假设B+树层数为3，也就是3次IO
      3. 一般来说B+Tree的高度一般都在2-4层，MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作（根节点的那次不算磁盘I/O）。
    2. 二级索引：
      1. 参考上面对于B+树的解释，辅助索引获取主键的时间复杂度是 lognN（假设第二层级是n个节点）
      2. 再通过lognN获取主键对应的数据列。如果二级索引有3层，聚簇索引有3层，那么最多花费的IO次数是：3+3 = 6
    3. 一行是这样的，但一次sql可能影响多行，实际IO不确定。另外，查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。即一次页加载就是一次IO。

二级索引为何有时会直接选择走聚簇索引全盘扫描？

IO开销

当搜索得到的结果范围很大的情况下，从二级索引叶子节点查到的每一个主键值都要回表，此时优化器宁愿全表扫描也不会使用索引+回表的查询方式。后者会有2个坏处：

1、一次回表会在主键索引（从根节点到叶子节点的过程）发生多次磁盘IO。

2、这些磁盘IO都是随机IO。

全表扫描则是直接从叶子节点沿着链表指针往后遍历，不用每次都从根节点到叶子节点，相比于回表节省了多次IO次数，而且沿着链表指针的磁盘IO会有很多次顺序IO（也会有随机IO），速度会比回表的随机IO快。

例如：

有一张用户表 id(主键), username (二级索引),age

id是主键索引 username是二级索引，当我们通过二级索引查询返回记录时，例如：

select * from t where username = “bobo”;

① 通过二级索引找到 bobo记录和主键id

② 在通过主键id去主键索引B+tree找到行记录

这里就会出现一个问题，当我们的用户表有150w条记录时， 有时候查询并没有走索引，而是通过All全盘的扫描。这是为什么了？下面我们就简单的介绍一下全盘扫描和走索引的IO。

假如我们一行数据的大小是100字节， bobo的有50W行数据. 假设用户表的B+树高度=3

通过username索引的话：每次回表需要3次IO，50w记录需要回表50w次。那么IO = 50W * 3

通过全盘扫描：一个页可以存放16k / 100字节 = 16 * 1024 / 100 = 163条记录 全盘扫描则需要 150w / 163 = 0.92W 差不多是1w次IO，

通过上面对比，肯定全盘要效率高一些，这就是为什么有时候我们建立了索引，但是通过explain检测的时候我们的sql没有走索引。因为回表的IO开销还是比较大的。

所以覆盖索引很有用。

MRR 简单的来说就是上面的回表更加效率了，如果需要50w次的回表，效率太慢了，这时候Mysql就把需要回表的主键id进行排序，然后排序过后的id再到主键B+树去取数据库，这样就大大的增加了效率。我们可以通过 explain中的Extra列中的Using MRR来判断是否使用了MRR.

mysql的一个update需要经历什么最终持久化到磁盘？

update T set c = c+1 whereid = 2;
  1. 执行器先调用存储引擎的接口获取“id=2”的数据行。
  2. 如果这一行所在的数据页在内存中，则存储引擎直接返回给执行器；否则需要存储引擎先去磁盘中获取数据，读取到内存中，然后再返回。
  3. 执行器拿到存储引擎返回的这行数据，对其进行更新操作，将c的值加+1，得到新的数据，在调用存储引擎接口，写入这行数据。
    1. 先将原始数据从磁盘中读入到内存（Buffer Pool）中，修改内存拷贝；
    2. 生成redo log并写入redo log buffer（内存），记录的是数据被修改后的值；此时还有两阶段提交相关内容。
    3. 当事务commit时，将redo log中的内容刷新到redo log file（磁盘），对redo log file采用追加写的方式；
    4. 定期将内存（Buffer Pool）中修改的值刷新到磁盘；

image-20230113204502877

  1. 读取数据时，首先从Buffer Pool中读取，如果Buffer Pool中没有，则加载磁盘中的数据到Buffer Pool中；
  2. 写入数据的时候，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘（这一过程被称为刷脏）。
posted @ 2023-01-13 20:46  iterationjia  阅读(93)  评论(0)  编辑  收藏  举报