问：MySQL表过大，你有哪些优化实践？

当MySQL单表记录数过大时，数据库的CRUD（创建、读取、更新、删除）性能会明显下降。为了提升性能，我们需要采取一些优化措施。本文将详细介绍几种常见的优化方案。

1. 限定数据的范围

描述

务必禁止不带任何限制数据范围条件的查询语句。例如，在查询订单历史时，可以控制在一个月的范围内。

示例

-- 不推荐的查询
SELECT * FROM orders;

-- 推荐的查询
SELECT * FROM orders WHERE order_date >= '2023-09-01' AND order_date < '2023-10-01';

优点

提高查询性能，避免全表扫描。

缺点

需要在业务代码中添加范围限制逻辑。

2. 读/写分离

描述

经典的数据库拆分方案，主库负责写，从库负责读。

架构图

读写分离示意

主库(写) -> 从库(读)

示例

通过配置数据库连接池实现读写分离，例如使用MyCat等中间件。

优点

减轻主库压力，提高读写性能。

缺点

数据同步延迟问题。
架构复杂度增加。

3. 垂直分区

描述

根据数据库里面数据表的相关性进行拆分。例如，将用户表拆分成用户登录表和用户信息表。

示例

-- 用户登录表
CREATE TABLE user_login (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(50)
);

-- 用户信息表
CREATE TABLE user_info (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    phone VARCHAR(20)
);

优点

列数据变小，减少I/O次数。
简化表结构，易于维护。

缺点

主键冗余。
需要管理冗余列，引起Join操作。
事务变得更加复杂。

4. 水平分区

描述

保持数据表结构不变，通过某种策略存储数据分片。每一片数据分散到不同的表或库中。

示例

将用户表按用户ID进行水平拆分。

-- 用户表1
CREATE TABLE user_1 (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 用户表2
CREATE TABLE user_2 (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

策略

Range Partitioning（范围分区）
List Partitioning（列表分区）
Hash Partitioning（哈希分区）

优点

支持非常大的数据量。
提高查询性能。

缺点

分片事务难以解决。
跨节点Join性能较差。
逻辑复杂。

5. 数据库分片

客户端代理

描述

分片逻辑在应用端，封装在jar包中，通过修改或封装JDBC层来实现。例如当当网的Sharding-JDBC、阿里的TDDL。

示例

使用Sharding-JDBC进行分片。

// 配置数据源和分片规则
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds0", createDataSource("jdbc:mysql://localhost:3306/db0"));
dataSourceMap.put("ds1", createDataSource("jdbc:mysql://localhost:3306/db1"));

ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getBindingTableGroups().add("t_order");

TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order", "ds${0..1}");
orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_${order_id % 2}"));
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);

ShardingDataSource shardingDataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());

// 使用分片数据源
try (Connection conn = shardingDataSource.getConnection()) {
    Statement stmt = conn.createStatement();
    stmt.executeQuery("SELECT * FROM t_order");
}

中间件代理

描述

在应用和数据中间加了一个代理层，分片逻辑统一维护在中间件服务中。例如Mycat、360的Atlas、网易的DDB。

示例

配置Mycat进行分片。

<!-- mycat 配置文件 -->
<schema>
    <table name="t_order" primaryKey="order_id" dataNode="dn1,dn2" rule="sharding-by-order-id">
    </table>
</schema>

<dataNode>
    <name>dn1</name>
    <dataHost>localhost1</dataHost>
    <database>db1</database>
</dataNode>

<dataNode>
    <name>dn2</name>
    <dataHost>localhost2</dataHost>
    <database>db2</database>
</dataNode>

<rule>
    <columns>order_id</columns>
    <algorithm>sharding-by-order-id</algorithm>
</rule>

方案比较

方案	描述	优点	缺点	适用场景
限定数据范围	控制查询范围，避免全表扫描	提高查询性能	业务代码中需添加范围限制逻辑	所有查询操作
读写分离	主库负责写，从库负责读	减轻主库压力，提高读写性能	数据同步延迟，架构复杂度增加	读写操作频繁的系统
垂直分区	数据表按列拆分	列数据变小，减少I/O次数，简化表结构，易于维护	主键冗余，需管理冗余列，引起Join操作，事务复杂	表列多，部分列访问频繁
水平分区	数据表按行拆分	支持大数据量，提高查询性能	分片事务难解决，跨节点Join性能差，逻辑复杂	单表数据量巨大
客户端代理分片	分片逻辑在应用端，封装在JDBC层	应用端改造少，支持大数据量存储	分片事务难解决，跨节点Join性能差，逻辑复杂，需额外维护分片逻辑	中小型系统，客户端改造方便
中间件代理分片	分片逻辑在中间件，应用与数据库之间加代理层	应用端无需改造，支持大数据量存储，分片逻辑集中管理	中间件性能瓶颈，架构复杂度增加，需额外维护中间件	大型系统，需统一管理分片逻辑