数据库系统优化是通过调整数据库结构、参数及应用程序以提升系统性能的技术，核心目标是将低效SQL语句转换为高效等效语句。人工智能自动优化技术通过重写SQL语句实现性能提升。优化技术演变历经三代工具：第一代解析执行计划，第二代推荐索引优化，第三代提出语法改进建议。优化策略贯穿数据库生命周期，其中设计阶段优化成本最低且收益最大。

参考文章：

【MySQL调优】如何进行MySQL调优？从参数,数据,建模,索引,SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）

一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案

表设计优化

混合业务分表、冷热数据分表

例如把一个大的任务表，分离成任务表和历史任务表，任务表里任务完成后移动到历史任务表。任务表是热数据，历史任务表是冷数据，提高查询性能。

混合业务分表：

根据业务逻辑，将不同的业务数据分开存储在不同的表中。每个业务模块的数据单独存储，减少了单表的大小和查询的复杂度。

示例：将一个大的日志表，拆分成交易日志表、操作日志表、登录日志表等。这些要在项目设计期间就根据预估的数据量进行拆分。

冷热数据分表：

将频繁访问的“热数据”和不常访问的“冷数据”分开存储。这种策略有助于提高热数据的查询性能，并且在存储和备份方面更加灵活。

示例1：把一个大的任务表，分离成任务表和历史任务表，任务表里任务完成后移动到历史任务表。任务表是热数据，历史任务表是冷数据，提高查询性能。
示例2：或者将日志表分成日志表和历史日志表，使用定时任务将三个月前的日志都迁移到历史日志表，用户查看操作记录时，默认只显示近三个月的数据，从而提高性能。

联合查询改为中间关系表

例如属性表和属性分组表，不使用连接查询，使用“属性-属性分组表”存储每条属性与“属性关系”的id。

对于复杂的数据库设计，使用关系表是一种常见的方法，特别是在多对多的关系中，例如学生表和课程表、商品表和商品属性表、用户和角色表、作者和书籍表、部门和人员表。

示例：

# 部门表 (departments)
departments(department_id, department_name)
# 人员表 (employees)
employees(employee_id, employee_name)
# 部门员工关联表 (department_employees)
department_employees(id, department_id, employee_id)
# department_id：外键，指向 departments 表；employee_id：外键，指向 employees 表

关系表的优点：

多对多关系：通过中间表可以管理多对多的映射关系。
支持扩展：关系表可以添加额外的字段来描述关系的属性。
提高查询性能：减少表连接的次数，提高查询性能。

遵循三个范式

每个属性不可再分、表必须有且只有一个主键、非主键列必须直接依赖于主键

字段建议非空约束

可能查询出现空指针问题；
导致聚合函数不准确，因为它会忽略null
不能用“=”判断，只能用is null判断；
null和其他值运算只能是null，可能让你不小心把它当成0；
null值比空字符更占用空间，空值长度是0，null长度是1bit；
不覆盖索引情况下，is not null无法用索引

反范式：使用冗余字段

虽然列字段不能太多，但为查询效率可增加冗余字段。

反范式：为提高查询效率，可添加不常更新的字段为冗余字段。

反范式化是数据库设计的一种策略，通过在设计中引入冗余数据来提高查询性能，简化查询操作，或满足其他业务需求。

使用场景：

将多读少写的字段，增加为冗余字段，从而不再需要每次都连表查询这个字段。

注意：

增加冗余字段后，当这个冗余字段对应的数据改动后，必须同步更改这个冗余字段。

例如成绩表除了有student_id字段外，增加冗余字段student_name，因为学生名基本不会变化，但在修改姓名的接口里，修改学生名后要同步修改成绩表的student_name字段。

数据类型优化

整数类型

考虑好数值范围，前期可以使用int保证稳定性。非负数类型要用UNSIGNED；同样字节数，存储的数值范围更大。主键一般使用bigint，布尔类型tinint
能整数就不要用文本类型：跟文本类型数据相比，大整数往往占用更少的存储空间。
避免使用TEXT、BLOB数据类

这两个大数据类型，排序时不能使用临时内存表，只能使用磁盘临时表，效率很差，建议别用，或分表到单独扩展表里。LongBlob类型能存储4G文件；
避免使用枚举类型：排序很慢。
使用TIMESTAMP存储时间：

TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。缺点是只能存到2038年，MySQL5.6.4版本可以参数配置，自动修改它为BIGINT类型。
DECIMAL存浮点数

Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

读写分离

读写分离：读写分离能有效提高查询性能。读写分离基于MySQL的主从同步，一台主库负责写，多台从库负责读，每次主库发生写操作后，通过binlog和relay log，将修改操作同步到从库，从而保持主库和从库的数据一致性。

主从同步：一台或多台MySQL数据库(slave，即从库)从另一台NySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现从库的数据和主库的数据保持一致。MySQL主从复制是NySQL数据库自带功能，无需借助第三方工具。

主从同步实现步骤：

主服务器把数据更改记录到二进制日志（binlog，记录改不记录读，用于数据复制和数据恢复）中；
从服务器异步近似实时地把主服务器的二进制日志复制到自己的中继日志（relay log）中；
从服务器重做中继日志中的操作，把更改应用到自己的数据库上，以达到数据的最终一致性。

主从同步的延时问题：

延时问题：是主服务器压力大导致的复制延时问题。
解决方案：
- 网络带宽优化：如果是网络延时问题，可以通过增大服务器的带宽解决。
- 硬件调优：如果是因为硬件配置差导致同步延时，可以通过提升服务器配置解决。

参数调优：

[mysqld]
# 多线程复制（MySQL 5.7及以上版本）：设置并行线程数
slave_parallel_workers = 4  
# 开启半同步复制（MySQL 5.5及以上版本）
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
# 同步延时时间：1秒超时。如果频繁写，则适当缩小；如果频繁读少写，则适当增大，以降低服务器压力
rpl_semi_sync_master_timeout = 1000

缩小事务粒度：一些代码中直接在整个Controller或者整个Service方法上加个@Transcational，而整个业务中其实只有一小段需要保持事务，这样是很影响性能的，因为事务不是那个，MySQL的连接会一直被占用，对数据库的压力很大。

解决方案：减小代码中事务的粒度，例如将大事务拆解成小事务，将其中的一些查询操作脱离出去，只在写操作的方法中加事务；

复制原理：

主库二进制日志转储线程：负责将二进制日志发给从库。强制从主库读取数据时（/master/ SELECT * FROM user），会给二进制日志加锁，读完解锁。
从库I/O 线程：负责连接主库，并向主库发送请求和复制二进制日志到中继日志。
从库SQL 线程：负责读取并执行中继日志中的更新语句，实现主从同步。

主从库数量：

每个 Master 可以有多个 Slave
每个 Slave 只能有一个唯一的服务器ID，只有一个 Master。

分库分表

基本概念

只分表：单表数据量大，读写出现瓶颈，这个表所在的库还可以支撑未来几年的增长。
只分库：整个数据库读写出现性能瓶颈，将整个库拆开。
分库分表：单表数据量大，所在库也出现性能瓶颈，就要既分库又分表。
垂直拆分：把字段分开。例如spu表的pic字段特别长，建议把这个pic字段拆到另一个表（同库或不同库）。
水平拆分：把记录分开。例如表数据量到达百万，我们拆成四张20万的表。

拆分原则

一般情况下，单表数据量到达千万级别，就可以考虑分库分表了。

具体是否分库分表看具体业务场景，例如流水表、记录表，数据量非常容易到达千万级、亿万级，需要在设计数据库表的阶段就进行分表，还有一些表虽然数据量只有几百万，但字段非常多，而且有很多text、blog格式的字段，查询性能也会很慢，可以考虑分库分表。

数据量增长情况	数据表类型	优化核心思想
数据量为千万级，是一个相对稳定的数据量	状态表	能不拆就不拆读需求水平扩展
数据量为千万级，可能达到亿级或者更高	流水表	业务拆分，面向分布式存储设计
数据量为千万级，可能达到亿级或者更高	流水表	设计数据统计需求存储的分布式扩展
数据量为千万级，不应该有这么多的数据	配置表	小而简，避免大一统

分库分表步骤简述

MySQL调优：数据量能稳定在千万级，近几年不会到达亿级，其实是不用着急拆的，先尝试MySQL调优，优化读写性能。
目标评估：评估拆几个库、表，举例: 当前20亿，5年后评估为100亿。分几个表? 分几个库?解答:一个合理的答案，1024个表，16个库按1024个表算，拆分完单表200万，5年后为1000万.1024个表*200w≈100亿。
表拆分：
- 业务层拆分：混合业务拆分为独立业务、冷热分离
- 数据层拆分：
  - 按日期拆分：这种方式较普遍，尤其是按照日期维度的拆分，其实在程序层面的改动很小，但扩展性方面的收益很大。
    - 日维度拆分，如test_20191021
    - 月维度拆分,如test_201910
    - 年维度拆分,如test_2019
  - 按主键范围拆分：例如【1,200w】主键在一个表，【200w，400w】主键在一个表。优点是单表数据量可控。缺点是流量无法分摊，写操作集中在最后面的表。
  - 中间表映射：表随意拆分，引入中间表记录查询的字段值，以及它对应的数据在哪个表里。优点是灵活。确定是引入中间表让流程变复杂。
  - hash切分：sharding_key%N。优点是数据分片均匀，流量分摊。缺点是扩容需要迁移数据，跨节点查询问题。
  - 按分区拆分：hash,range等方式。不建议，因为数据其实难以实现水平扩展。
sharding_key（分表字段）选择：尽量选择查询频率最高的字段，然后根据表拆分方式选择字段。
代码改造：修改代码里的查询、更新语句，以便让其适应分库分表后的情况。
数据迁移：最简单的就是停机迁移，复杂点的就是不停机迁移，要考虑增量同步和全量同步的问题。
1. 全量同步：老库到新库的数据迁移，要控制好迁移效率，解决增量数据的一致性。
  1. 定时任务：定时任务查老库写新库
  2. 中间件：使用中间件迁移数据
2. 增量同步：老库迁移到新库期间，新增删改命令的落库不能出错
  1. 同步双写：同步写新库和老库；
  2. 异步双写（推荐）： 写老库，监听binlog异步同步到新库
  3. 中间件同步工具：通过一定的规则将数据同步到目标库表
数据一致性校验和补偿：假设采用异步双写方案，在迁移完成后，逐条对比新老库数据，一致则跳过，不一致则补偿：
1. 新库存在，老库不存在：新库删除数据
2. 新库不存在，老库存在：新库插入数据
3. 新库存在、老库存在：比较所有字段，不一致则将新库更新为老库数据
灰度切读：灰度发布指黑（旧版本）与白（新版本）之间，让一些用户继续用旧版本，一些用户开始用新版本，如果用户对新版本没什么意见，就逐步把所有用户迁移到新版本，实现平滑过渡发布。原则：
1. 有问题及时切回老库
2. 灰度放量先慢后快，每次放量观察一段时间
3. 支持灵活的规则：门店维度灰度、百 (万)分比灰度
停老用新：下线老库，用新库读写。

分库分表步骤详细

分库分表的原则：能不分就不分

优先MySQL调优，能不分就不分

数据量能稳定在千万级，近几年不会到达亿级，其实是不用着急拆的，先尝试MySQL调优，优化读写性能。只有在MySQL调优已经无法解决慢查询问题时，才可以考虑分库分表。
分片数量尽量少。

分片尽量均匀分布在多个 DataHost 上，因为一个查询 SQL 跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。
不要一个事务里跨越多个分片查询

尽量不要在一个事务中的 SQL 跨越多个分片，分布式事务一直是个不好处理的问题。

目标评估

评估需要拆分成几个库、几个表。

举例：当前20亿，5年后评估为100亿。分几个表? 分几个库?

解答：一个合理的答案，1024个表，16个库按1024个表算，拆分完单表200万，5年后为1000万.1024个表*200w≈100亿

表拆分

业务层面拆分

混合业务拆分

将混合业务拆分为独立业务。业务场景举例：

电商网站：一个典型的混合业务，包含用户信息、订单信息、商品信息等。可以将用户信息、订单信息和商品信息分别拆分到不同的库或表中，以减少数据冗余并提高访问效率。
社交媒体平台：含用户信息、好友关系、动态信息等。可将用户信息和好友关系分离存储，以便更好支持好友关系的查询和更新。
在线游戏：涉及角色信息、道具信息、战斗日志等。可以将角色信息和道具信息拆分到不同的表中，以提升查询效率，并将战斗日志存储到日志数据库中，以减轻主数据库的负载。
物流系统：包含订单信息、配送信息、运输信息等。可以将订单信息、配送信息和运输信息分别拆分到不同的表中，以便更好地支持订单的查询和跟踪。

冷热分离

将常用的“热”数据和不常使用的“冷”数据分开存储。即在处理数据时将数据库分成冷库和热库，冷库存放那些走到终态、不常使用的数据，热库存放还需要修改、经常使用的数据。

什么情况下可以使用冷热分离？

数据走到终态后只有读没有写的需求。例如订单完结后基本只会读不会改。
用户能接受新旧数据分开查询。比如某电商网站默认只让查询3个月内的订单，若要查询3个月前的订单，还需要访问其他的页面。

业务场景举例：

邮件系统：邮件系统中最近邮件是用户经常访问和修改的，三个月前的邮件或已归档的邮件不经常访问的。可以将用户的收件箱、发件箱里最近三个月的邮件放在一个库里（热库），之前的邮件或者已读的邮件放在另一个库里（冷库）。
日志系统：在大型应用中，日志数据是非常庞大的，但并不是所有日志都需要经常查询或分析。可以将最近一段时间的活动日志存放在热库中，而将过去的历史日志存放在冷库中，以减轻热库的负载和优化查询性能。
社交媒体平台：社交媒体平台上的用户数据量通常很大，但是只有少部分用户是活跃的，并且只有少量用户的数据会频繁访问和更新，如果所有用户都放在同一个库里，势必会影响活跃用户的查询效率。可以将活跃用户的个人信息、好友关系等存放在热库中，而将不活跃用户的数据存放在冷库中，以提升热库的性能和减少冷库的存储成本。
电商平台：电商平台上的商品数据也可以进行冷热分离。热库中存放热门商品的基本信息和库存等，以支持频繁的查询和更新操作，而将不活跃或下架的商品信息存放在冷库中，以减少热库的负载和优化查询性能。
客服工单：在我们日常操作时，经常能看到查询历史工单时会有个“近三个月工单”的选项，实际业务场景中，用户基本只会关注近三个月工单，而且这些工单也会经常需要进行修改、删除的操作，而对很早期的历史订单基本就没有修改、删除的需求，只有少量的查询需求。

数据层面拆分

按日期拆分：这种使用方式比较普遍，尤其是按照日期维度的拆分，其实在程序层面的改动很小，但是扩展性方面的收益很大。
- 日维度拆分，如test_20191021
- 月维度拆分,如test_201910
- 年维度拆分,如test_2019
按主键范围拆分：例如【1,200w】主键在一个表，【200w，400w】主键在一个表。优点是单表数据量可控。缺点是流量无法分摊，写操作集中在最后面的表。
中间表映射：表随意拆分，引入中间表记录查询的字段值，及它对应的数据在哪个表里。虽然灵活但是引入中间表让流程变复杂。
hash切分：sharding_key%N。优点是数据分片均匀，流量分摊。缺点是扩容需要迁移数据，跨节点查询问题。
按分区拆分：hash,range等方式。不建议，因为数据其实难以实现水平扩展。

分表字段（sharding_key）选择

最佳的分表字段应该是能够让数据分布均匀、频繁查询的字段及不可变的字段。通过选择最佳的分表字段，可提高系统性能和查询效率。

常用字段

主键ID：频繁查询并且唯一，非常适合作分表字段。例如，在用户表中，用户ID作为分表字段是一个不错的选择，因为用户ID是唯一的，而且在查询用户信息时经常会用到。
时间字段：如果业务需要按时间范围查询数据，那么选择时间字段作为分表字段是合理的。例如，在日志表中，可以选择时间戳字段作为分表字段，以便按天、按月或按年分割数据，方便查询和维护。
地理信息字段：如果业务需要按地区查询数据，那么选择地理信息字段作为分表字段是合适的。例如，在订单表中，可以选择订单地区字段作为分表字段，以便将订单数据按地区进行拆分，方便查询和扩展。
关联字段：如果业务需要频繁进行关联查询，那么选择订单号等关联字段作为分表字段。例如，在订单表中，可以选择订单号作为分表字段，因为订单号唯一且包含业务信息，并且日常查询、关联查询都是根据订单号查询的，很少根据id查询，方便查询和维护。

选择分表字段的原则

数据分布均匀

最佳的分表字段应该是能够让数据分布均匀的字段，这样可以避免某个表的数据过多，导致查询效率降低。在用户表中，如果以地区作为分表字段，可能会导致某些地区的数据过多，而某些地区的数据过少。
频繁查询的字段

尽量选择查询频率最高的字段（例如主键id），然后根据表拆分方式选择字段。在一个订单表中，如果经常需要根据用户ID查询订单信息，那么以用户ID作为分表字段是一个不错的选择。
不可变字段

最佳的分表字段还应该是不可变的字段，这样可以避免在数据迁移时出现问题。在一个商品表中，如果选择以商品名称作为分表字段，那么当商品名称发生变化时，就需要将数据移动到不同的表中，这样会增加系统的复杂度。

代码改造

修改代码里的查询、更新语句，以便让其适应分库分表后的情况。

查询语句改造：

单库查询改为跨库查询：对于需要查询的字段，需要明确指定查询的库和表，以避免查询到错误的数据。例如：

1 2	SELECT * FROM users WHERE id = 1; # 原查询语句 SELECT * FROM db.table_name WHERE id = 1; # 修改后：其中 db 为目标数据库，table_name 为目标表

单表查询改为跨表查询：例如投诉记录表根据哈希取余的方式分成10个表，如果id%1=0，则查0号表complaint_records_0。

数据迁移

最简单的就是停机迁移，复杂点的就是不停机迁移，要考虑增量同步和全量同步的问题。

增量同步

老库迁移到新库期间，增删改命令的落库不能出错

同步双写：同步写新库和老库；
异步双写（推荐）： 写老库，监听binlog异步同步到新库
中间件同步工具：通过一定的规则将数据同步到目标库表

全量同步

老库到新库的数据迁移，要控制好迁移效率，解决增量数据的一致性。

定时任务查老库写新库
使用中间件迁移数据，例如Dbmate、Apache NiFi、Ladder、Phinx、Flyway、TiDB等。

数据一致性校验和补偿

假设采用异步双写方案，在迁移完成后，逐条对比新老库数据，一致则跳过，不一致则补偿：

新库存在，老库不存在：新库删除数据
新库不存在，老库存在：新库插入数据
新库存在、老库存在：比较所有字段，不一致则将新库更新为老库数据

灰度切读

灰度发布指黑（旧版本）与白（新版本）之间，让一些用户继续用旧版本，一些用户开始用新版本，如果用户对新版本没什么意见，就逐步把所有用户迁移到新版本，实现平滑过渡发布。遵循原则如下：

有问题及时切回老库
灰度放量先慢后快，每次放量观察一段时间
支持灵活的规则：门店维度灰度、百 (万)分比灰度

停旧库、写新库

下线老库，用新库读写。