一、架构层优化：构建高性能数据库的基石

1.1 分库分表策略设计

当单表数据量超过千万级时，垂直拆分（按业务维度）与水平拆分（按ID哈希/范围）成为必选项。某电商平台的订单表拆分案例显示，采用用户ID哈希分片后，TPS从800提升至3200，但需注意跨分片事务的复杂性。建议使用ShardingSphere等中间件简化分片逻辑，并通过全局ID生成器（如雪花算法）保证主键唯一性。

1.2 读写分离架构实践

主从复制架构可有效分担读压力，但需解决主从延迟问题。某金融系统通过半同步复制将延迟控制在50ms内，配合ProxySQL实现自动读写路由。关键配置参数包括：

-- MySQL半同步配置示例
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000; -- 10秒超时

1.3 缓存层集成方案

Redis作为热点数据缓存层，需设计合理的缓存策略。某新闻系统采用多级缓存架构：

• CDN缓存静态页面（TTL=10分钟）
• Redis缓存动态数据（TTL=5分钟）
• 本地Cache缓存元数据（TTL=1分钟）
  通过互斥锁解决缓存击穿问题，代码示例：

  public String getData(String key) {
    String value = redis.get(key);
    if (value == null) {
        synchronized (key.intern()) {
            value = redis.get(key);
            if (value == null) {
                value = fetchFromDB(key);
                redis.setex(key, 300, value);
            }
        }
    }
    return value;
  }

二、索引优化：提升查询效率的关键路径

2.1 索引设计原则

遵循”三左原则”：联合索引需满足最左前缀匹配。某社交平台的用户关系表索引设计：

-- 错误示例：无法使用index_follower索引
SELECT * FROM user_relations WHERE followee_id = 100;
-- 正确设计：建立(follower_id, followee_id)联合索引
ALTER TABLE user_relations ADD INDEX idx_follower (follower_id, followee_id);

2.2 索引维护策略

定期分析索引使用情况，某物流系统通过performance_schema发现30%的索引从未被使用，删除后写入性能提升15%。推荐使用pt-index-usage工具进行索引审计。

2.3 索引类型选择

• B+树索引：适合等值查询和范围查询
• 哈希索引：仅适合等值查询（Memory引擎）
• 全文索引：用于文本搜索（需配置ngram解析器）
• 空间索引：用于地理数据（需使用MyISAM或InnoDB的R-tree）

三、查询优化：消除性能瓶颈的核心手段

3.1 EXPLAIN深度解析

通过EXPLAIN输出识别全表扫描（type=ALL）、临时表（Extra=Using temporary）等性能问题。某支付系统的查询优化案例：

-- 优化前：全表扫描
EXPLAIN SELECT * FROM transactions WHERE amount > 1000;
-- 优化后：使用索引
ALTER TABLE transactions ADD INDEX idx_amount (amount);
EXPLAIN SELECT * FROM transactions WHERE amount > 1000;
-- 优化后type变为range，rows从500万降至5万

3.2 慢查询治理

设置long_query_time=1s捕获慢查询，某视频平台通过分析慢查询日志发现：

• 20%的查询包含OR条件导致索引失效
• 15%的查询使用SELECT *传输不必要字段
• 10%的查询缺少适当的WHERE条件

3.3 查询重写技巧

• 避免使用SELECT *，明确指定字段
• 将IN子查询改为JOIN操作
• 使用LIMIT限制返回数据量
• 复杂查询拆分为多个简单查询

四、资源管理：保障系统稳定性的最后防线

4.1 连接池配置

某在线教育平台通过调整连接池参数解决连接泄漏问题：

# HikariCP配置示例
spring.datasource.hikari.maximum-pool-size=50
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=10000

4.2 内存优化策略

调整innodb_buffer_pool_size至可用内存的50-70%，某金融系统配置为64GB后，缓冲池命中率从92%提升至99%。监控命令：

SHOW ENGINE INNODB STATUS;
-- 关注BUFFER POOL AND MEMORY部分

4.3 参数调优矩阵

参数	默认值	推荐值	影响
innodb_io_capacity	200	1000	I/O吞吐量
sync_binlog	1	100	写入性能与数据安全平衡
query_cache_size	0	64M	小数据量查询加速

五、工具链建设：提升优化效率的利器

5.1 监控体系搭建

构建包含QPS、RT、连接数、缓存命中率等指标的监控看板，某游戏公司通过Prometheus+Grafana实现实时告警，将故障发现时间从30分钟缩短至2分钟。

5.2 自动化巡检脚本

定期执行以下检查项：

#!/bin/bash
# 检查表碎片率
mysql -e "SELECT table_schema, table_name, data_free/1024/1024 MB 
           FROM information_schema.tables 
           WHERE data_free > 1048576 ORDER BY data_free DESC"
# 检查未使用索引
mysql -e "SELECT * FROM sys.schema_unused_indexes"

5.3 压测方案设计

使用sysbench进行基准测试：

sysbench oltp_read_write --threads=32 --time=300 \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=root --mysql-password=123456 \
--db-driver=mysql --tables=10 --table-size=1000000 \
prepare/run/cleanup

六、行业最佳实践总结

1. 渐进式优化：遵循”监控→定位→优化→验证”的闭环流程
2. 80/20法则：优先解决占用80%资源的20%查询
3. 灰度发布：优化方案先在测试环境验证，再逐步扩大流量
4. 文档沉淀：建立优化知识库，记录每个优化项的背景、方案和效果

某银行核心系统通过系统化优化，实现查询响应时间从2.3s降至0.8s，TPS从1200提升至3500的显著效果。数据库优化是持续的过程，需要结合业务特点建立长效机制，定期进行健康检查和性能调优。