1. 表优化

字符集

数据库和表的字符集统一使用 UTF-8，使用 charset=utf8mb4。

字段类型

字段类型选择的原则是使用可以正确存储数据的最小数据类型。

例如，可以用 int 时不要用 bigint，字段长度固定时用 char，长度不固定时用 varchar 代替 char，最大长度超过 varchar 限制时才使用 text。

NULL

字段尽量使用 NOT NULL 并提供默认值，因为 NULL 类型的存储需要额外的标记来表示，查询优化也会存在性能问题。

大文件

不要用 MySQL 的字段存储大文件，而是要将其存在磁盘中或网络对象中，通过字段记录文件的访问地址。

外键、触发器、视图

不建议使用外键、触发器、视图，会降低代码的可读性，带来性能、维护和复杂性的问题，数据完整性校验应该由程序逻辑完成。

修复损坏的表

# 检查表是否损坏
CHECK TABLE user;

# 修复损坏的表
REPAIR TABLE user;

# 重建表，可以修复损坏、减少索引和数据碎片
ALTER TABLE user ENGINE=InnoDB;

维护和优化表

# 更新表的统计信息，有助于优化器生成更高效的执行计划
ANALYSE TABLE user;

# 重建表，优化存储空间，减少碎片
OPTIMIZE TABLE user;

2. 索引优化

整型自增主键

使用整型自增字段作为主键，减少数据插入时频繁的 B+ 树页面分裂造成的性能损耗。

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY

去除冗余索引

不要创建太多的不必要的索引，每个索引都需要维护一个 B+ 树空间，且增删改查的时候都会多一个选择索引和更新索引的时间代价。对于同样顺序的一至多列，如果创建了主键索引就没必要创建唯一索引了，同理创建了唯一索引就没必要创建普通索引了。

索引只包含必要列

只为用于搜索、排序、分组的列创建索引，出现在 select 列表的其他列不需要创建在索引内。

列的区分度

考虑索引列的值的区分度，选用区分度高的字段。如果一个列的不重复值占全部记录数的比例太低，每一列包含太多重复值，通过二级索引查询可能会执行过多的回表操作。

数据类型选择尽量小

索引列的数据类型尽量小。数据类型越小，索引站的存储空间就越少，一个数据页内就能存放更多的记录，从而减小磁盘 I/O 的性能消耗。特别是对于主键，因为除了聚簇索引，所有二级索引都会存储记录对应的主键值。如主键能选择 MEDIUMINT 就不要用 INT，能选择 INT 就不要用 BIGINT。

联合索引的字段选择

对多个字段建立联合索引时，把区分度高的字段放在前面。

存在等值判断和范围判断的条件时，将等值判断的列放在前面。

查询条件有两个列进行范围查询，将无法全用到索引。

索引覆盖

利用索引覆盖，当查询所需的列较少时，可以将查询条件的列和读取的列用来创建联合索引，该查询只需要读取该二级索引，无需再执行回表操作。

最左前缀原则

冗余索引会带来额外的存储空间要求和数据维护要求，应该避免。基于最左前缀原则，只要查询满足一个索引的前缀部份字段，也能利用该索引进行查询，也就不需要创建冗余的索引了。如已有联合索引 (k1, k2, k3)，通过 k1 或 k1 + k2 进行查询也能使用该索引，无需再为具体查询字段创建额外索引。已有主键索引或唯一索引，也无需再对同样的字段创建普通索引。

有时候出于查询性能的考虑需要冗余联合索引的前缀作为索引，如同时保留索引 (k1, k2) 和 (k1)，因为 k2 可能过大，导致只用 k1 查询时，走联合索引明显慢于走独立的索引。

前缀索引

对字符串列创建索引时，值可能会很大，导致在索引占用的存储空间很大，可以以列的前缀建立索引。索引中的 key 只会是列值的对应长度前缀，减少了索引的存储空间，而查询确定值的时候就以前缀为匹配去索引中查找。但该方式无法对该列排序时使用索引，将会退化为全表扫描。注意前缀索引无法用于 GROUP BY 和 ORDER BY，也无法用于覆盖扫描。

# 以字符串列创建索引
ALTER TABLE user ADD INDEX idx_key(name);

# 以字符串列的长度为 10 的前缀创建索引
ALTER TABLE user ADD INDEX idx_key(name(10));

如果字符串列前缀的区分度不够，前缀索引的查询效率不高，还可以尝试将该列倒过来存储，这样就是以后缀倒叙作为前缀了。或者新建一个字段，存储字符串字段的哈希值，以该哈希值字段创建索引。

不要用表达式、函数、类型转换

查询条件中的字段如果以表达式、函数、类型转换的方式来筛选，即使有索引也会进行全表扫描，为了使用索引执行，需要以列名的形式单独出现在查询条件中。

# 全表扫描
SELECT * FROM user where age + 20 > 60;

# 使用索引
SELECT * FROM user where age > 40;

排序

排序的字段应该放在联合索引的最后。

无法使用索引的场景

负向条件 !=、<>、not in、not exists、not like 无法

is null、is not null 无法使用索引。

like 语句模糊查询的前导模糊如 like ‘%XX’ 查询无法使用索引，可以使用搜索引擎来解决。

3. SQL优化

select 字段

查询时，select 的字段只列出用到的字段，不要盲目 select *，因为这会返回过多字段，给磁盘 IO 带来额外的压力。

优化 WHERE 条件

优先使用 = 或 IN，避免使用 != 或 NOT IN。

大分页查询

在查询一个表根据某个字段排序的分页数据，但 offset 较大时，除了从业务上优化避免出现较大分页外，还可以优化 SQL。

例如如下查询，表的 age 字段有索引，该查询会扫描并跳过前十万行数据。

SELECT * FROM user where age > 40 ORDER BY age OFFSET 100000 LIMIT 10;

通过只查询所需要的字段，为这些字段创建一个覆盖索引，避免回表操作。

CREATE INDEX idx_covering on user (age, name, sex);

SELECT name, sex FROM user where age > 40 ORDER BY age OFFSET 100000 LIMIT 10;

或者使用延迟关联，通过覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的行。

SELECT * FROM user
JOIN (
  SELECT id FROM user ORDER BY age LIMIT 100000, 10
) x on user.id = x.id;

表连接

表连接时使用小表驱动大表，这里的小表和大表不是指表本身的数据量，而是对各表根据条件过滤后的行数相对而言。

确保表连接的字段有索引，右表连接的字段为主键更好。

使用表连接代替子查询，在大多数场景都更高效，因为表连接可以更好地被优化，可以并行查询，而子查询很可能会创建临时表，查询也只能串行执行。

# 子查询，性能差
SELECT A.* FROM A WHERE A.id not in (SELECT B.id FROM B);

# 表连接，更高效
SELECT A.* FROM A LEFT JOIN B on A.id = B.id where B.id IS NULL;

不要对太多张表连接查询，最多只能 3 - 4 个表进行连接。

OR

OR 条件可能会导致索引失效和全表扫描，可以改为 UNION，以更好利用索引和并行处理能力。

UNION

当确认两部份数据没有重复数据或不需要去重时，将 UNION 换为 UNION ALL。

只查询一行

如果只需要查询一行或者确定查询只有一行，可以加上 LIMIT 1，让数据库更好利用索引，优化查询行为。

事务

尽量缩短事务执行时间，避免长事务，尝试将长事务拆分为多个小事务，减少资源占用和锁竞争。

写操作分批

对于数量较大的写操作如 INSERT、DELETE、UPDATE，一次执行会花费很多时间，还会产生大量的锁，本身变成了一个大事务。应该将数据分批执行，如 100 行数据一批地写。

4. 配置优化

服务器配置

max_connections 配置服务器最大连接数量，默认为 151，可以调高以支持更多连接。

wait_timeout 配置客户端超时时间，默认为 28800 秒即 8 小时，可以调低以及时释放不活动连接。

slow_query_log、slow_query_log_file、long_query_time 打开慢查询日志，对慢日志进行分析以针对性优化 SQL。

客户端配置

客户端连接池大小通常是在应用程序中进行配置的，复用连接可以减少和服务器建立连接的额外开销，但连接池不是越大越好，盲目加大很可能会加大服务端性能开销降低效率，一个推荐的连接池大小是 CPU 核心数 * 2 + 1。

5. 架构优化

主从读写分离

在很多读多写少的业务场景，可以配置主库和从库，主库负责处理插入删除更新的写操作，主库和从库都可以接受查询的读操作，提升数据库的读性能。

还可以让主库只接受写操作，从库只接受读操作，实现读写分离。从库支持水平扩展，可以根据实际需求配置一到多个从库，分散读请求的压力和提升性能，降低单点故障风险，提升系统的可用性。

不过这时候需要关注主从数据备份的时间差，差距过大会使从库数据的时效性降低。

分库分表

垂直分库，按业务逻辑拆分为不同的数据库，减轻请求的并发压力。这样做两个数据库之间的表就不能直接做连接查询了，需要根据业务场景进行拆分。

垂直分表，将列比较多的表按照经常使用的字段（热字段）和不常用到的字段（冷字段）拆分为不同的表，提升查询速度。

水平分表，将单张表的数据拆分为多个表，可以解决存储的瓶颈。可以创建 MySQL 分区表来讲数据拆分到多个分区。也可以按照某个字段将数据保存在不同的表，如根据日期将数据写到对应日期的表 user_data_20250101、user_data_20250102、user_data_20250103 等。或者使用自带分区的分布式数据库如 TDSQL。

缓存

在服务和数据库之间，通过增加一层缓存如 Redis，针对频繁访问的热点数据，可以实现更快速的查询，同时可以降低数据库的读取压力。

这种方式需要考虑好缓存更新和淘汰的策略，避免数据大量失效过期对底层数据库的骤增压力。

消息队列

在同一时间访问数据库的请求过大，会对数据库造成很大的压力。如果是可以异步处理的请求，如数据扫描和同步，可以先将请求发给消息队列，再按照实际处理能力从消息队列获取请求进行处理，以对流量削峰。