问：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>

方案比较

结语

优化大表的方法多种多样，选择哪种方案取决于具体的业务需求和系统架构。在优化过程中，需要权衡各种因素，如性能、复杂度、维护成本等。希望本文的内容能帮助大家更好地理解和应用大表优化方案，提升数据库性能。