一次真实的性能危机
去年底大连某客户的电商平台遭遇了严重的数据库性能问题——大促期间订单接口响应时间从正常的200ms飙升到8秒以上数据库CPU利用率持续100%导致整个站点卡顿用户投诉不断。作为技术支持方我们紧急介入进行了为期一周的数据库优化最终将核心接口响应时间稳定在150ms以内QPS承载能力提升了15倍。
下面把这次优化的完整过程和方法论分享出来希望对遇到类似问题的朋友有所帮助。
第一步：发现问题
性能优化的前提是准确度量问题。我们首先做了以下诊断：开启慢查询日志（set global slow_query_log = ON; set global long_query_time = 1; ——记录执行时间超过1秒的SQL语句）、查看当前状态（SHOW GLOBAL STATUS 关键指标：Threads_connected 当前连接数/Questions 查询总数/Slow_queries 慢查询数/Innodb_buffer_pool_read_requests 缓存读取次数等）、查看进程列表（SHOW PROCESSLIST 发现大量处于"Sending data"状态的查询——这是典型的慢查询特征）、使用pt-query-digest分析（Percona Toolkit的pt-query-digest工具可以将慢查询日志聚合分析生成清晰的报告——按执行时间/执行次数/扫描行数等维度排序一目了然）。
诊断结论：Top 5慢查询占据了95%以上的数据库资源其中最严重的一条订单列表查询每天执行约50万次平均耗时3.2秒扫描行数超过200万行。
第二步：分析执行计划
拿到慢SQL后第一步是用EXPLAIN分析其执行计划：
EXPLAIN SELECT o.*, u.username FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE o.status = 1 AND o.create_time > '2024-11-01' ORDER BY o.create_time DESC LIMIT 20;
EXPLAIN结果中的关键字段解读：type（访问类型——ALL表示全表扫描是最差的类型应该优化到range/ref/const级别）、key（实际使用的索引——NULL表示没用上任何索引）、rows（预估扫描的行数——越大越慢）、Extra（额外信息——Using filesort表示需要额外的排序操作Using temporary表示使用了临时表这些都是性能杀手）。
这条慢查询的问题一目了然：orders表的status字段没有索引导致WHERE过滤全表扫描；create_time字段虽然有索引但因为status条件的存在导致索引失效；ORDER BY触发了filesort。
第三步：索引优化
索引优化是性价比最高的优化手段。针对上面的案例我们创建了复合索引：
-- 创建复合索引（遵循最左前缀原则） CREATE INDEX idx_status_createtime ON orders(status, create_time DESC);
索引设计的原则：最左前缀（复合索引(a,b,c)可以匹配a/(a,b)/(a,b,c)但不能匹配(b,c)或单独b）、选择性高的列放前面（区分度越高放在索引越靠前的位置效果越好。可以用SELECT COUNT(DISTINCT col)/COUNT(*)计算选择性）、覆盖索引（如果查询的所有字段都在索引中就不需要回表——这是最快的查询方式称为Using index）、避免冗余索引((a,b)和(a)同时存在时后者是冗余的因为前者已经包含了后者的功能）。
创建索引后再次EXPLAIN：type从ALL变成了range rows从200万降到了约5000 执行时间从3.2秒降到了0.08秒——提升了40倍！一条索引就解决了问题。
第四步：SQL重写与Schema优化
有时候光靠索引不够还需要从SQL本身入手：避免SELECT *（只查询需要的列减少IO传输和内存消耗。特别要避免SELECT * 加 LIMIT 分页——即使只需要20行也要把整行数据读出来再丢弃）、子查询改JOIN（MySQL的子查询优化能力较弱很多时候JOIN的效率更高）、OR改UNION（当OR条件涉及不同字段时可能导致索引失效改为UNION ALL有时更快）、LIMIT深分页优化（LIMIT 1000000,20这种深分页非常慢——可以使用deferred join或记录上次最大ID的方式优化）、适当反范式化（为了查询性能故意做一些冗余——如在订单表中冗余存储用户名避免每次都要JOIN用户表。这是空间换时间的经典做法）。
Schema层面的优化：选择合适的数据类型（能用TINYINT就不用INT能用VARCHAR(20)就不用TEXT）、表垂直拆分（将大表中不常用的字段拆到扩展表中减少主表的IO压力）、表水平拆分（按时间/地域/哈希等维度将大表拆分为多个小表——即分表的初步形式）。
第五步：架构层面优化
当单机优化到达极限后就需要从架构层面入手：读写分离（主库负责写操作从库负责读操作通过中间件ProxySQL/MyCat或应用层路由实现读写分离。典型的主从架构可以将读能力提升2-5倍）、分库分表（当单表数据量超过千万级时考虑分表；当整个库的数据量/连接数超过单机承受能力时考虑分库。ShardingSphere是目前流行的开源分库分表中间件）、引入缓存（Redis缓存热点数据——如商品详情/用户session/热门排行榜等。缓存命中率做到90%以上就能极大减轻数据库压力。注意缓存穿透/击穿/雪崩的防护）、连接池优化（应用端的数据库连接池大小要合理设置——太小会导致连接等待太大则浪费资源。一般设置为CPU核心数的2-4倍）。
回到开头那个客户案例：我们最终实施的方案是——索引优化（解决了80%的慢查询）+ Redis缓存热点商品和分类数据（减少了60%的数据库读请求）+ 一主两从的读写分离架构（读能力提升3倍）。三管齐下彻底解决了性能问题。
持续监控与预警
优化不是一次性的工作而是持续的循环。建议建立数据库监控 Dashboard 实时关注：QPS/TPS（每秒查询/事务数）、慢查询数量和趋势、主从复制延迟（Seconds_Behind_Master 应接近0）、InnoDB缓冲池命中率（应大于99%）、连接数使用率（不应超过max_connections的80%）、磁盘IO等待（iowait %不应持续超过20%）。
设置合理的告警阈值——在问题影响用户体验之前就收到警报主动处理而不是被动救火。这才是专业运维的体现。