MySQL业务优化实战：从慢查询到大表分页的23种武器

作为业务快速发展的技术支撑，MySQL数据库优化是每个开发者必须掌握的技能。本文将聚焦业务场景下的MySQL优化手段，提供可直接落地的解决方案。

1. 查询性能优化实战

慢查询分析与优化

慢查询是业务系统最常见的性能瓶颈。通过配置long_query_time参数(建议0.1-0.5秒)，MySQL可以记录执行时间超过阈值的查询。

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.3;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

优化步骤：

使用mysqldumpslow工具分析慢查询日志
对高频慢查询使用EXPLAIN分析执行计划
重点关注全表扫描(ALL)、文件排序(filesort)等操作

执行计划深度解读

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

执行计划关键列解读：

type：从最优到最差依次为 system > const > eq_ref > ref > range > index > ALL
key：实际使用的索引
rows：预估需要检查的行数
Extra：额外信息，如"Using where", "Using temporary"

实践建议：

出现Using filesort时考虑添加合适的索引
出现Using temporary时可能需要优化JOIN或GROUP BY

避免全表扫描的黄金法则

为WHERE条件中的列建立索引
避免在索引列上使用函数或计算
使用覆盖索引(查询列都在索引中)

-- 反例：索引失效
SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';

-- 正例：索引有效
SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';

2. 数据模型优化策略

范式与反范式的平衡

订单表范式化设计：

反范式化设计(减少JOIN)：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    create_time DATETIME,
    total_amount DECIMAL(10,2),
    user_id INT,
    product_info JSON COMMENT '存储商品快照信息'
);

实践建议：

读多写少的业务适合适当反范式化
写多读少的业务保持范式化减少冗余

大字段存储优化

对于TEXT/BLOB类型的大字段：

垂直分表：将大字段拆分到单独表
文件存储：将内容存到文件系统，数据库只保存路径
压缩存储：使用COMPRESS()函数压缩存储

-- 垂直分表示例
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    created_at TIMESTAMP
);

CREATE TABLE article_contents (
    article_id INT PRIMARY KEY,
    content LONGTEXT,
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

3. 批量操作性能提升

批量插入最佳实践

低效方式：

for (Product product : products) {
    jdbcTemplate.update("INSERT INTO products VALUES (?,?,?)", 
        product.getId(), product.getName(), product.getPrice());
}

高效方式：

jdbcTemplate.batchUpdate("INSERT INTO products VALUES (?,?,?)",
    products.stream().map(p -> new Object[]{p.getId(), p.getName(), p.getPrice()})
    .collect(Collectors.toList()));

性能对比：

方式	1万条记录耗时
单条插入	~60s
批量插入	~0.5s

批量更新优化方案

方案1：使用CASE WHEN

UPDATE products
SET price = CASE id
    WHEN 1 THEN 10.99
    WHEN 2 THEN 20.99
    ELSE price
END
WHERE id IN (1,2);

方案2：使用临时表

CREATE TEMPORARY TABLE temp_updates (
    id INT PRIMARY KEY,
    new_price DECIMAL(10,2)
);

-- 批量插入到临时表
INSERT INTO temp_updates VALUES (1,10.99), (2,20.99);

-- JOIN更新
UPDATE products p JOIN temp_updates t ON p.id = t.id
SET p.price = t.new_price;

4. 分页查询深度优化

传统分页的性能问题

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

这种写法需要MySQL先读取1,000,010条记录，然后丢弃前1,000,000条。

优化方案1：延迟关联

SELECT * FROM orders 
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) AS tmp
ON orders.id = tmp.id;

优化方案2：游标分页

-- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 10;

-- 获取下一页(假设上一页最后一条记录的id是12345)
SELECT * FROM orders WHERE id < 12345 ORDER BY id DESC LIMIT 10;

性能对比：

方式	偏移量100万时耗时
传统LIMIT	~1.2s
延迟关联	~0.3s
游标分页	~0.01s

5. 冷热数据分离实战

按时间分区表示例

CREATE TABLE logs (
    id BIGINT,
    created_at DATETIME,
    content TEXT,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

操作优势：

删除旧数据只需ALTER TABLE logs DROP PARTITION p2020
查询最近数据自动命中最新分区

归档策略设计

-- 创建归档表(使用压缩存储)
CREATE TABLE logs_archive (
    id BIGINT PRIMARY KEY,
    created_at DATETIME,
    content TEXT
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

-- 数据迁移(分批执行)
INSERT INTO logs_archive 
SELECT * FROM logs WHERE created_at < '2020-01-01' LIMIT 10000;

-- 原表删除已归档数据
DELETE FROM logs WHERE created_at < '2020-01-01' LIMIT 10000;

6. 读写分离架构实践

业务层路由设计

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly() 
            ? "read" : "write";
    }
}

读延迟解决方案

重要业务强制走主库：

@Transactional(readOnly = false)
public Order getOrderForUpdate(Long orderId) {
 return orderRepository.findById(orderId);
}

写入后立即读取走主库：

public void updateOrder(Order order) {
 orderRepository.save(order);
 // 设置标记，后续查询走主库
 TransactionSynchronizationManager.setCurrentTransactionReadOnly(false);
}

7. 业务降级与限流

数据库层限流策略

-- 设置最大连接数(根据服务器配置调整)
SET GLOBAL max_connections = 500;

-- 单个用户最大连接数
SET GLOBAL max_user_connections = 50;

应用层熔断设计

@RestController
@RequestMapping("/api/orders")
public class OrderController {
    
    private final CircuitBreaker circuitBreaker;
    
    @GetMapping("/{id}")
    public Order getOrder(@PathVariable Long id) {
        return circuitBreaker.run(() -> orderService.getOrder(id),
            throwable -> getOrderFromCache(id));
    }
    
    private Order getOrderFromCache(Long id) {
        // 从缓存或备用接口获取数据
    }
}

总结

MySQL业务优化是一个系统工程，需要结合具体场景选择合适的策略。关键点总结：

查询优化：从执行计划入手，消灭全表扫描
数据设计：平衡范式与反范式，合理处理大字段
批量操作：减少网络往返，利用批量接口
分页优化：大数据量避免OFFSET，采用游标分页
架构设计：读写分离、冷热分离减轻主库压力
降级预案：高并发时保护数据库不被压垮

每种优化手段都有其适用场景，建议通过监控和压测验证效果，形成适合自己业务的优化方案。

MySQL业务优化实战：23种慢查询与大表分页解决方案