一、核心性能优化原则

1. 数据最小化原则

• 只SELECT需要的字段（避免SELECT *）
• 使用TINYINT代替布尔值存储
• 对长文本采用垂直分表（分离大字段到附加表）

2. 索引黄金法则

sql

-- 坏示例：全表扫描
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 优化后：覆盖索引
ALTER TABLE orders ADD INDEX idx_time_status (create_time, status);
SELECT id, status FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

二、查询优化进阶技巧

3. 深度分页优化方案
   传统分页问题：

sql

SELECT * FROM big_table LIMIT 1000000, 20; -- 需要扫描前100万+20行

优化方案：

sql

SELECT * FROM big_table 
WHERE id > (SELECT id FROM big_table ORDER BY id LIMIT 1000000, 1)
LIMIT 20;

4. JSON字段查询优化
   错误方式：

sql

SELECT * FROM products 
WHERE JSON_EXTRACT(properties, '$.color') = 'red'; -- 全表扫描

优化方案：

sql

ALTER TABLE products ADD COLUMN color VARCHAR(20) AS (JSON_UNQUOTE(JSON_EXTRACT(properties, '$.color'))) STORED;
CREATE INDEX idx_color ON products(color);

三、服务器配置调优

5. InnoDB核心参数配置

ini

# my.cnf配置
[mysqld]
innodb_buffer_pool_size = 80% of total RAM # 通常设为物理内存的60-80%
innodb_flush_log_at_trx_commit = 2 # 适用于可容忍秒级数据丢失的场景
innodb_file_per_table = ON
innodb_log_file_size = 4G # 建议设置为buffer pool的25%

6. 连接管理优化

sql

-- 查看连接状态
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 配置建议
max_connections = 1000
thread_cache_size = 100
wait_timeout = 600

四、架构级优化策略

7. 读写分离方案
   
   使用ProxySQL实现自动路由：

sql

-- 配置规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES 
(1, 1, '^SELECT', 10, 1),   -- 读操作路由到从库组
(2, 1, '.*', 20, 1);        -- 写操作路由到主库

8. 分库分表实战
   使用ShardingSphere进行水平分片：

yaml

# 分片配置示例
rules:
- !SHARDING
  tables:
    orders:
      actualDataNodes: ds_${0..1}.orders_${0..15}
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: mod_hash
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake

五、高级监控与诊断

9. 性能分析三板斧
   a. Explain执行计划：

sql

EXPLAIN FORMAT=JSON 
SELECT * FROM orders WHERE user_id = 123 AND status = 'PAID';

b. 慢查询日志分析：

ini

slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

c. Performance Schema监控：

sql

SELECT event_name, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS sec 
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

10. 死锁分析与预防
    查看最近死锁信息：

sql

SHOW ENGINE INNODB STATUS\G

关键预防措施：

• 保持事务短小
• 按固定顺序访问多表
• 合理设置索引减少锁冲突

六、现代方案集成

11. 列式存储方案
    使用ClickHouse作为分析型补充：

sql

CREATE TABLE orders_analytics ENGINE = MergeTree()
ORDER BY (order_date) AS 
SELECT * FROM mysql('mysql-server:3306', 'shop', 'orders', 'user', 'pass');

12. 缓存策略设计
    多级缓存架构：

客户端 → CDN缓存 → Redis热点缓存 → MySQL缓冲池 → 持久化存储

使用Redis进行查询缓存：

python

def get_order(order_id):
    key = f"order:{order_id}"
    data = redis.get(key)
    if not data:
        data = db.execute("SELECT * FROM orders WHERE id=%s", order_id)
        redis.setex(key, 3600, serialize(data))
    return deserialize(data)

七、关键优化检查清单

1. 确认所有查询都使用到合适的索引（）
2. 定期分析慢查询日志并优化（）
3. 检查缓冲池命中率 > 99%（）
4. 配置合理的自动递增步长防止主键冲突（）
5. 使用SSD存储并配置合适的IO调度策略（）

持续优化建议：

• 每周运行pt-query-digest分析慢查询
• 使用Prometheus + Grafana搭建监控平台
• 定期进行压力测试（sysbench/tpcc-mysql）
• 关注MySQL 8.0新特性（如窗口函数、直方图统计）

通过系统性地实施这些优化策略，可以将典型OLTP系统的TPS提升3-10倍，并显著降低P95延迟。建议根据实际业务场景选择适合的优化手段，并通过监控数据持续验证优化效果。