03-MySQL 优化

官网保平安：https://www.mysql.com/
MySQL 思维导图：https://www.processon.com/view/link/63bc2c8ea82ed9463ba99f38

数据库优化方案 ★★★

详细可参考：https://blog.csdn.net/fenglepeng/article/details/103400418

思路：

  • 第一：优化 SQL 语句和索引。

  • 第二：加缓存，如 Memcached、Redis。

  • 第三：主从复制或主主复制，读写分离，可以在应用层做，效率高，也可以用三方工具。

  • 第四：如果以上都做了还是慢，不要想着去做切分，MySQL 自带分区表，先试试这个，对你的应用是透明的，无需更改代码，但是 SQL 语句是需要针对分区表做优化的，SQL 条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，另外分区表还有一些坑，在这里就不多说了。

  • 第五：如果以上都做了，那就先做垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统。

  • 第六：才是水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的 sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，SQL 中尽量带 sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

优化 SQL

  • 设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
  • 合适的表字段数据类型和存储引擎，适当的添加索引。

加缓存

  • 利用 Redis、Memcache(常用数据放到缓存里，提高取数据速度)。
  • 缓存可能会造成雪崩、穿透等现象。

读写分离

利用数据库的主从分离：主库用于删除、修改、更新；从库用于查询。

  • 原生 SQL：select * from db.tb
  • ORM：model.User.object.all().using('default')
  • 路由：db router

分库

  • 当数据库中的表太多，将某些表分到不同数据库，例如：1W 张表时。
  • 代价：连表查询跨数据库，代码变多。

分表

  • 垂直分表：将一个表按照字段分成多表，每个表存储其中一部分字段。例如：博客+博客详情。
  • 水平分表：在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。如将某些历史信息，分到另外一张表中，例如：支付宝账单。

分库分表最常用的组件：Mycat\ShardingSphere

建议：一个表的数据量超过 500W 或者数据文件超过 2G，就要考虑分库分表了。

数据库分片的两种常见方案：

  • 客户端代理： 分片逻辑在应用端，封装在 jar 包中，通过修改或者封装 JDBC 层来实现。当当网的 Sharding-JDBC 、阿里的 TDDL 是两种比较常用的实现。
  • 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。Mycat 、360 的 Atlas、网易的 DDB 等都是这种架构的实现。

读写分离

什么是读写分离？

见名思意，根据读写分离的名字，我们就可以知道：读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

如何实现读写分离？

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

  1. 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
  2. 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
  3. 系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

1. 代理方式

代理方式实现读写分离

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

提供类似功能的中间件有 MySQL Router（官方， MySQL Proxy 的替代方案）、Atlas（基于 MySQL Proxy）、MaxScale、MyCat。

关于 MySQL Router 多提一点：在 MySQL 8.2 的版本中，MySQL Router 能自动分辨对数据库读写/操作并把这些操作路由到正确的实例上。这是一项有价值的功能，可以优化数据库性能和可扩展性，而无需在应用程序中进行任何更改。具体介绍可以参考官方博客：MySQL 8.2 – transparent read/write splitting。

2. 组件方式

在这种方式中，我们可以通过引入第三方组件来帮助我们读写请求。

这也是我比较推荐的一种方式。这种方式目前在各种互联网公司中用的最多的，相关的实际的案例也非常多。如果你要采用这种方式的话，推荐使用 sharding-jdbc ，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本。

你可以在 shardingsphere 官方找到 sharding-jdbc 关于读写分离的操作。

主从复制

主从复制原理是什么？

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

更具体和详细的过程是这个样子的（图片来自于：《MySQL Master-Slave Replication on the Same Machine》）：

MySQL主从复制

  1. 主库将数据库中数据的变化写入到 binlog
  2. 从库连接主库
  3. 从库会创建一个 I/O 线程向主库请求更新的 binlog
  4. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
  5. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
  6. 从库的 SQL 线程读取 relay log 同步数据到本地（也就是再执行一遍 SQL ）。

你一般看到 binlog 就要想到主从复制。当然，除了主从复制之外，binlog 还能帮助我们实现数据恢复。

🌈 拓展一下：

不知道大家有没有使用过阿里开源的一个叫做 canal 的工具。这个工具可以帮助我们实现 MySQL 和其他数据源比如 Elasticsearch 或者另外一台 MySQL 数据库之间的数据同步。很显然，这个工具的底层原理肯定也是依赖 binlog。canal 的原理就是模拟 MySQL 主从复制的过程，解析 binlog 将数据同步到其他的数据源。

另外，像咱们常用的分布式缓存组件 Redis 也是通过主从复制实现的读写分离。

🌕 简单总结一下：

MySQL 主从复制是依赖于 binlog 。另外，常见的一些同步 MySQL 数据到其他数据源的工具（比如 canal）的底层一般也是依赖 binlog 。

MySQL 的复制原理以及流程（主备同步）

MySQL 内建的复制功能是构建大型、高性能应用程序的基础。将 MySQL 的数据分布到多个系统上去，这种分布的机制，是通过将 MySQL 的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入 binlog 文件，并维护文件的一个索引以跟踪日志循环。这些日志由主服务器发送到从服务器。当一个从服务器连接主服务器时，它通知主服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。过程如下：

  • 备库B 和 主库A 建立了长链接，主库A 内部专门线程用于维护了这个长链接。
  • 在 备库B 上通过 change master 命令，设置 主库A 的IP端口用户名密码以及从哪个位置开始请求 binlog 包括文件名和日志偏移量。
  • 在 备库B 上执行 start-slave 命令，备库会启动两个线程：io_thread 和 sql_thread 分别负责建立连接和读取中转日志进行解析执行。
  • 主库A 提交完事务后，写入 binlog。
  • 主库A 创建 dump 线程，推送 binlog 到 slave。
  • 备库B 启动一个 IO 线程读取 主库A 传过来的 binlog 文件，备库B 收到文件写到本地成为中转日志。
  • 备库B 再启动一个 SQL 线程读取中继日志事件并执行，完成同步。
  • 备库B 记录自己的 binlog。
  • 后来由于多线程复制方案的引入，sql_thread 演化成了多个线程。

MySQL 支持的复制类型

  • 基于语句的复制：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。
  • 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。 从 MySQL5.0 开始支持。
  • 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

由于 MySQL 默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

  • 全同步复制：后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
  • 半同步复制：和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作完成。

MySQL 的一主一从和一主多从有什么区别

在一主一备的双 M 架构里，主备切换只需要把客户端流量切到备库；

在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。

什么是主从延迟

主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：

  • 有些部署条件下，备库所在机器的性能要比主库性能差。
  • 备库的 CPU、IO 等压力较大。
  • 大事务，一个主库上语句执行10分钟，那么这个事务可能会导致从库延迟10分钟。

如何避免主从延迟？

  • 强制走主库方案
  • sleep 方案
  • 判断主备无延迟方案
  • 配合 semi-sync 方案
  • 等主库位点方案
  • GTID 方案。
  • 实际生产中，先客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。

强制将读请求路由到主库处理

既然你从库的数据过期了，那我就直接从主库读取嘛！这种方案虽然会增加主库的压力，但是，实现起来比较简单，也是我了解到的使用最多的一种方式。

比如 Sharding-JDBC 就是采用的这种方案。通过使用 Sharding-JDBC 的 HintManager 分片键值管理器，我们可以强制使用主库。

1
2
3
HintManager hintManager = HintManager.getInstance();
hintManager.setMasterRouteOnly();
// 继续JDBC操作

对于这种方案，你可以将那些必须获取最新数据的读请求都交给主库处理。

延迟读取

还有一些朋友肯定会想既然主从同步存在延迟，那我就在延迟之后读取啊，比如主从同步延迟 0.5s，那我就 1s 之后再读取数据。这样多方便啊！方便是方便，但是也很扯淡。

不过，如果你是这样设计业务流程就会好很多：对于一些对数据比较敏感的场景，你可以在完成写请求之后，避免立即进行请求操作。比如你支付成功之后，跳转到一个支付成功的页面，当你点击返回之后才返回自己的账户。

总结

关于如何避免主从延迟，我们这里介绍了两种方案。实际上，延迟读取这种方案没办法完全避免主从延迟，只能说可以减少出现延迟的概率而已，实际项目中一般不会使用。

总的来说，要想不出现延迟问题，一般还是要强制将那些必须获取最新数据的读请求都交给主库处理。如果你的项目的大部分业务场景对数据准确性要求不是那么高的话，这种方案还是可以选择的。

什么情况下会出现主从延迟？如何尽量减少延迟？

我们在上面的内容中也提到了主从延迟以及避免主从延迟的方法，这里我们再来详细分析一下主从延迟出现的原因以及应该如何尽量减少主从延迟。

要搞懂什么情况下会出现主从延迟，我们需要先搞懂什么是主从延迟。

MySQL 主从同步延时是指从库的数据落后于主库的数据，这种情况可能由以下两个原因造成：

  1. 从库 I/O 线程接收 binlog 的速度跟不上主库写入 binlog 的速度，导致从库 relay log 的数据滞后于主库 binlog 的数据；
  2. 从库 SQL 线程执行 relay log 的速度跟不上从库 I/O 线程接收 binlog 的速度，导致从库的数据滞后于从库 relay log 的数据。

与主从同步有关的时间点主要有 3 个：

  1. 主库执行完一个事务，写入 binlog，将这个时刻记为 T1；
  2. 从库 I/O 线程接收到 binlog 并写入 relay log 的时刻记为 T2；
  3. 从库 SQL 线程读取 relay log 同步数据本地的时刻记为 T3。

结合我们上面讲到的主从复制原理，可以得出：

  • T2 和 T1 的差值反映了从库 I/O 线程的性能和网络传输的效率，这个差值越小说明从库 I/O 线程的性能和网络传输效率越高。
  • T3 和 T2 的差值反映了从库 SQL 线程执行的速度，这个差值越小，说明从库 SQL 线程执行速度越快。

那什么情况下会出现出从延迟呢？这里列举几种常见的情况：

  1. 从库机器性能比主库差：从库接收 binlog 并写入 relay log 以及执行 SQL 语句的速度会比较慢（也就是 T2-T1 和 T3-T2 的值会较大），进而导致延迟。解决方法是选择与主库一样规格或更高规格的机器作为从库，或者对从库进行性能优化，比如调整参数、增加缓存、使用 SSD 等。
  2. 从库处理的读请求过多：从库需要执行主库的所有写操作，同时还要响应读请求，如果读请求过多，会占用从库的 CPU、内存、网络等资源，影响从库的复制效率（也就是 T2-T1 和 T3-T2 的值会较大，和前一种情况类似）。解决方法是引入缓存（推荐）、使用一主多从的架构，将读请求分散到不同的从库，或者使用其他系统来提供查询的能力，比如将 binlog 接入到 Hadoop、Elasticsearch 等系统中。
  3. 大事务：运行时间比较长，长时间未提交的事务就可以称为大事务。由于大事务执行时间长，并且从库上的大事务会比主库上的大事务花费更多的时间和资源，因此非常容易造成主从延迟。解决办法是避免大批量修改数据，尽量分批进行。类似的情况还有执行时间较长的慢 SQL ，实际项目遇到慢 SQL 应该进行优化。
  4. 从库太多：主库需要将 binlog 同步到所有的从库，如果从库数量太多，会增加同步的时间和开销（也就是 T2-T1 的值会比较大，但这里是因为主库同步压力大导致的）。解决方案是减少从库的数量，或者将从库分为不同的层级，让上层的从库再同步给下层的从库，减少主库的压力。
  5. 网络延迟：如果主从之间的网络传输速度慢，或者出现丢包、抖动等问题，那么就会影响 binlog 的传输效率，导致从库延迟。解决方法是优化网络环境，比如提升带宽、降低延迟、增加稳定性等。
  6. 单线程复制：MySQL5.5 及之前，只支持单线程复制。为了优化复制性能，MySQL 5.6 引入了 多线程复制，MySQL 5.7 还进一步完善了多线程复制。
  7. 复制模式：MySQL 默认的复制是异步的，必然会存在延迟问题。全同步复制不存在延迟问题，但性能太差了。半同步复制是一种折中方案，相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟（还是有一定程度的延迟）。MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。并且，MySQL 5.7 引入了 增强半同步复制 。

《MySQL 实战 45 讲》这个专栏中的读写分离有哪些坑？这篇文章也有对主从延迟解决方案这一话题进行探讨，感兴趣的可以阅读学习一下。

分库分表

读写分离主要应对的是数据库读并发，没有解决数据库存储问题。试想一下：如果 MySQL 一张表的数据量过大怎么办?

换言之，我们该如何解决 MySQL 的存储压力呢？

答案之一就是 分库分表。

什么是分库？

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

举个例子：说你将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。

垂直分库

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

水平分库

什么是分表？

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

分表

什么情况下需要分库分表？

遇到下面几种场景可以考虑分库分表：

  • 单表的数据达到千万级别以上，数据库读写速度比较缓慢。
  • 数据库中的数据占用的空间越来越大，备份时间越来越长。
  • 应用的并发量太大（应该优先考虑其他性能优化方法，而非分库分表）。

不过，分库分表的成本太高，如非必要尽量不要采用。而且，并不一定是单表千万级数据量就要分表，毕竟每张表包含的字段不同，它们在不错的性能下能够存放的数据量也不同，还是要具体情况具体分析。

之前看过一篇文章分析 “InnoDB 中高度为 3 的 B+ 树最多可以存多少数据”，写的挺不错，感兴趣的可以看看。

常见的分片算法有哪些？

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。

常见的分片算法有：

  • 哈希分片：求指定分片键的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。哈希分片可以使每个表的数据分布相对均匀，但对动态伸缩（例如新增一个表或者库）不友好。
  • 范围分片：按照特定的范围区间（比如时间区间、ID 区间）来分配数据，比如 将 id 为 1~299999 的记录分到第一个表， 300000~599999 的分到第二个表。范围分片适合需要经常进行范围查找且数据分布均匀的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
  • 映射表分片：使用一个单独的表（称为映射表）来存储分片键和分片位置的对应关系。映射表分片策略可以支持任何类型的分片算法，如哈希分片、范围分片等。映射表分片策略是可以灵活地调整分片规则，不需要修改应用程序代码或重新分布数据。不过，这种方式需要维护额外的表，还增加了查询的开销和复杂度。
  • 一致性哈希分片：将哈希空间组织成一个环形结构，将分片键和节点（数据库或表）都映射到这个环上，然后根据顺时针的规则确定数据或请求应该分配到哪个节点上，解决了传统哈希对动态伸缩不友好的问题。
  • 地理位置分片：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
  • 融合算法分片：灵活组合多种分片算法，比如将哈希分片和范围分片组合。
  • ……

分片键如何选择？

分片键（Sharding Key）是数据分片的关键字段。分片键的选择非常重要，它关系着数据的分布和查询效率。一般来说，分片键应该具备以下特点：

  • 具有共性，即能够覆盖绝大多数的查询场景，尽量减少单次查询所涉及的分片数量，降低数据库压力；
  • 具有离散性，即能够将数据均匀地分散到各个分片上，避免数据倾斜和热点问题；
  • 具有稳定性，即分片键的值不会发生变化，避免数据迁移和一致性问题；
  • 具有扩展性，即能够支持分片的动态增加和减少，避免数据重新分片的开销。

实际项目中，分片键很难满足上面提到的所有特点，需要权衡一下。并且，分片键可以是表中多个字段的组合，例如取用户 ID 后四位作为订单 ID 后缀。

分库分表会带来什么问题呢？

记住，你在公司做的任何技术决策，不光是要考虑这个技术能不能满足我们的要求，是否适合当前业务场景，还要重点考虑其带来的成本。

引入分库分表之后，会给系统带来什么挑战呢？

  • join 操作：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。不过，很多大厂的资深 DBA 都是建议尽量不要使用 join 操作。因为 join 的效率低，并且会对分库分表造成影响。对于需要用到 join 操作的地方，可以采用多次查询业务层进行数据组装的方法。不过，这种方法需要考虑业务上多次查询的事务性的容忍度。
  • 事务问题：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。这个时候，我们就需要引入分布式事务了。关于分布式事务常见解决方案总结，网站上也有对应的总结：https://javaguide.cn/distributed-system/distributed-transaction.html 。
  • 分布式 ID：分库之后， 数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 ID 了。关于分布式 ID 的详细介绍&实现方案总结，可以看我写的这篇文章：分布式 ID 介绍&实现方案总结。
  • 跨库聚合查询问题：分库分表会导致常规聚合查询操作，如 group by，order by 等变得异常复杂。这是因为这些操作需要在多个分片上进行数据汇总和排序，而不是在单个数据库上进行。为了实现这些操作，需要编写复杂的业务代码，或者使用中间件来协调分片间的通信和数据传输。这样会增加开发和维护的成本，以及影响查询的性能和可扩展性。
  • ……

另外，引入分库分表之后，一般需要 DBA 的参与，同时还需要更多的数据库服务器，这些都属于成本。

分库分表有没有什么比较推荐的方案？

Apache ShardingSphere 是一款分布式的数据库生态系统，可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理、影子库、数据加密和脱敏等功能。

ShardingSphere 提供的功能如下：

ShardingSphere 提供的功能

ShardingSphere 的优势如下（摘自 ShardingSphere 官方文档：https://shardingsphere.apache.org/document/current/cn/overview/）：

  • 极致性能：驱动程序端历经长年打磨，效率接近原生 JDBC，性能极致。
  • 生态兼容：代理端支持任何通过 MySQL/PostgreSQL 协议的应用访问，驱动程序端可对接任意实现 JDBC 规范的数据库。
  • 业务零侵入：面对数据库替换场景，ShardingSphere 可满足业务无需改造，实现平滑业务迁移。
  • 运维低成本：在保留原技术栈不变前提下，对 DBA 学习、管理成本低，交互友好。
  • 安全稳定：基于成熟数据库底座之上提供增量能力，兼顾安全性及稳定性。
  • 弹性扩展：具备计算、存储平滑在线扩展能力，可满足业务多变的需求。
  • 开放生态：通过多层次（内核、功能、生态）插件化能力，为用户提供可定制满足自身特殊需求的独有系统。

另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

不过，还是要多提一句：现在很多公司都是用的类似于 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离）！如果公司条件允许的话，个人也是比较推荐这种方式！

分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

  • 我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。 这样就能保证，咱们新库里的数据是最新的。
  • 在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
  • 重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

总结

  • 读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。这样的话，就能够小幅提升写性能，大幅提升读性能。
  • 读写分离基于主从复制，MySQL 主从复制是依赖于 binlog 。
  • 分库 就是将数据库中的数据分散到不同的数据库上。分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。
  • 引入分库分表之后，需要系统解决事务、分布式 id、无法 join 操作问题。
  • 现在很多公司都是用的类似于 TiDB 这种分布式关系型数据库，不需要我们手动进行分库分表（数据库层面已经帮我们做了），也不需要解决手动分库分表引入的各种问题，直接一步到位，内置很多实用的功能（如无感扩容和缩容、冷热存储分离）！如果公司条件允许的话，个人也是比较推荐这种方式！
  • 如果必须要手动分库分表的话，ShardingSphere 是首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

订单表数据量越来越大导致查询缓慢，如何处理

分库分表。

由于历史订单使用率并不高，高频的可能只是近期订单，因此将订单表按照时间进行拆分，根据数据量的大小考虑按月分表或按年分表。订单 ID 最好包含时间(如根据雪花算法生成)，此时既能根据订单ID直接获取到订单记录，也能按照时间进行查询。

分库分表之后，id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

  • UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
  • 数据库自增 id : 两台数据库分别设置不同步长，生成不重复 id 的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  • 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  • Twitter 的 snowflake 算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
  • 美团的 Leaf 分布式 ID 生成系统 ：Leaf 是美团开源的分布式 id 生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper 等中间件。感觉还不错。

雪花算法（SnowFlake）

雪花算法最早是 Twitter 公司在其内部用于分布式环境下生成唯一ID。在 2014 年开源 scala 语言版本。

雪花算法的原理就是生成一个的 64 位比特位的 long 类型的唯一ID。

  1. 最高 1 位固定值 0，因为生成的 id 是正整数，如果是 1 就是负数了。
  2. 接下来 41 位存储毫秒级时间戳，2^41/(1000*60*60*24*365)=69，大概可以使用 69 年。
  3. 再接下 10 位存储机器码，包括 5 位 datacenterId 和 5 位 workerId。最多可以部署 2^10=1024 台机器。
  4. 最后 12 位存储序列号。同一毫秒时间戳时，通过这个递增的序列号来区分。即对于同一台机器而言，同一毫秒时间戳下，可以生成 2^12=4096 个不重复 id。

原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？

从大到小可分为四种情况：

  • MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
  • SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
  • 索引使用不当，没有走索引。
  • 表中数据的特点导致的，走了索引，但回表次数庞大。

解决：

  • 考虑采用 force index 强行选择一个索引。
  • 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把 order by b limit 1 改成 order by b,a limit 1，语义的逻辑是相同的。
  • 第三种方法是，在有些场景下可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。如果确定是索引根本没必要，可以考虑删除索引。

短时间提高 MySQL 性能的方法

  • 第一种：处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。kill connection + id
  • 第二种：减少连接过程的消耗。慢查询性能问题在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：索引没有设计好；SQL 语句没写好；MySQL 选错了索引（force index）。

MYSQL 数据库服务器性能分析的方法命令有哪些

1
show status

慢查询是什么，怎么解决

用慢查询来记录一些执行时间比较长的 SQL 语句。

我们可以用 explain 命令来查看这些 SQL 语句的执行计划。查看该 SQL 语句有没有使用索引，有没有做全表扫描。

线上可以开启慢查询，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

如何开启慢日志查询

可以通过修改配置文件开启

1
2
3
4
slow_query_log=ON       # 是否开启慢日志记录
long_query_time=2       # 时间限制，超过此时间，则记录
slow_query_log_file=/usr/slow.log  # 日志文件
long_queries_not_using_indexes=ON  # 是否记录使用索引的搜索
#MySQL #面试
03-MySQL 优化
https://flepeng.github.io/interview-41-数据库-41-MySQL-03-MySQL-优化/
作者
Lepeng
发布于
2020年8月8日
许可协议