5.3 性能调优实战：典型业务场景下的优化案例
📚 学习目标
通过本节学习，你将掌握：
- ✅ 不同业务场景下的性能问题诊断方法
- ✅ 电商、金融、社交等典型场景的优化案例
- ✅ 性能调优的系统化方法论
- ✅ 从问题发现到解决方案的完整流程
- ✅ 性能优化的最佳实践和经验总结
🎯 学习收获
学完本节后，你将能够：
- 问题诊断：快速识别不同业务场景下的性能问题
- 优化实施：针对性地实施性能优化方案
- 效果验证：验证优化效果并持续改进
- 经验积累：形成系统化的性能调优方法论
💡 实际场景引入
场景一：电商大促性能问题
问题描述：某电商平台在双11大促期间，订单查询接口响应时间从平时的100ms增加到5秒，严重影响用户体验。
你的任务：如何快速诊断和优化性能问题？
场景二：金融系统高并发问题
问题描述：某金融系统的交易接口在高并发场景下出现大量超时，系统负载高但CPU和内存使用率不高。
你的任务：如何优化高并发场景下的性能问题？
在实际的数据库运维工作中，性能调优往往需要结合具体的业务场景来进行。不同的业务类型、数据特征和访问模式都会对数据库性能产生不同的影响。本节将通过多个典型的业务场景案例，深入分析MySQL性能问题的诊断过程和优化方案，帮助您掌握在实际工作中解决性能问题的方法和技巧。
电商系统订单查询优化
业务场景分析
-- 电商订单表结构CREATETABLEorders(order_idBIGINTAUTO_INCREMENTPRIMARYKEY,user_idINTNOTNULL,product_idINTNOTNULL,order_noVARCHAR(32)NOTNULLUNIQUE,order_statusTINYINTNOTNULLDEFAULT1COMMENT'1:待付款 2:已付款 3:已发货 4:已完成 5:已取消',payment_methodTINYINTNOTNULLDEFAULT1COMMENT'1:支付宝 2:微信 3:银行卡',total_amountDECIMAL(10,2)NOTNULL,discount_amountDECIMAL(10,2)NOTNULLDEFAULT0.00,shipping_addressTEXT,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_user_created(user_id,created_at),INDEXidx_status_created(order_status,created_at),INDEXidx_created(created_at),INDEXidx_order_no(order_no))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 订单商品明细表CREATETABLEorder_items(item_idBIGINTAUTO_INCREMENTPRIMARYKEY,order_idBIGINTNOTNULL,product_idINTNOTNULL,quantityINTNOTNULL,unit_priceDECIMAL(10,2)NOTNULL,total_priceDECIMAL(10,2)NOTNULL,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,INDEXidx_order_id(order_id),INDEXidx_product_id(product_id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-- 用户表CREATETABLEusers(user_idINTAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(50)NOTNULLUNIQUE,emailVARCHAR(100)NOTNULLUNIQUE,phoneVARCHAR(20),created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,INDEXidx_username(username),INDEXidx_email(email))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;性能问题诊断
-- 1. 模拟慢查询场景-- 用户订单列表查询（用户量大，订单量更大）SELECTo.order_id,o.order_no,o.order_status,o.total_amount,o.created_at,COUNT(oi.item_id)asitem_countFROMorders oLEFTJOINorder_items oiONo.order_id=oi.order_idWHEREo.user_id=12345ANDo.created_at>='2023-01-01'ANDo.created_at<'2024-01-01'GROUPBYo.order_id,o.order_no,o.order_status,o.total_amount,o.created_atORDERBYo.created_atDESCLIMIT20;-- 2. 使用EXPLAIN分析执行计划EXPLAINFORMAT=JSONSELECTo.order_id,o.order_no,o.order_status,o.total_amount,o.created_at,COUNT(oi.item_id)asitem_countFROMorders oLEFTJOINorder_items oiONo.order_id=oi.order_idWHEREo.user_id=12345ANDo.created_at>='2023-01-01'ANDo.created_at<'2024-01-01'GROUPBYo.order_id,o.order_no,o.order_status,o.total_amount,o.created_atORDERBYo.created_atDESCLIMIT20;-- 3. 分析表统计信息SELECTtable_name,table_rows,ROUND(((data_length+index_length)/1024/1024),2)AS'size_mb',ROUND((index_length/(data_length+index_length)*100),2)AS'index_ratio'FROMinformation_schema.tablesWHEREtable_schema='ecommerce'ANDtable_nameIN('orders','order_items');-- 4. 分析索引使用情况SELECTs.TABLE_NAME,s.INDEX_NAME,s.COLUMN_NAME,s.SEQ_IN_INDEX,s.CARDINALITY,t.TABLE_ROWS,ROUND((s.CARDINALITY/t.TABLE_ROWS)*100,2)ASselectivity_percentFROMinformation_schema.STATISTICSsJOINinformation_schema.TABLEStONs.TABLE_SCHEMA=t.TABLE_SCHEMAANDs.TABLE_NAME=t.TABLE_NAMEWHEREs.TABLE_SCHEMA='ecommerce'ANDs.TABLE_NAMEIN('orders','order_items')ORDERBYs.TABLE_NAME,s.INDEX_NAME,s.SEQ_IN_INDEX;优化方案实施
-- 1. 创建复合索引优化查询-- 原查询条件：user_id + created_at范围 + GROUP BY + ORDER BYCREATEINDEXidx_user_created_compositeONorders(user_id,created_at,order_id);-- 2. 优化分页查询-- 使用延迟关联技术优化LIMIT查询SELECTo.order_id,o.order_no,o.order_status,o.total_amount,o.created_atFROMorders oINNERJOIN(SELECTorder_idFROMordersWHEREuser_id=12345ANDcreated_at>='2023-01-01'ANDcreated_at<'2024-01-01'ORDERBYcreated_atDESCLIMIT20)ASlimited_ordersONo.order_id=limited_orders.order_idORDERBYo.created_atDESC;-- 3. 预聚合统计信息-- 创建订单统计表减少实时计算CREATETABLEuser_order_stats(user_idINTPRIMARYKEY,total_ordersINTNOTNULLDEFAULT0,total_amountDECIMAL(15,2)NOTNULLDEFAULT0.00,last_order_timeTIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,INDEXidx_total_orders(total_orders),INDEXidx_last_order_time(last_order_time));-- 4. 使用覆盖索引CREATEINDEXidx_cover_user_ordersONorders(user_id,created_at,order_id,order_no,order_status,total_amount);-- 优化后的查询（使用覆盖索引）SELECTorder_id,order_no,order_status,total_amount,created_atFROMordersWHEREuser_id=12345ANDcreated_at>='2023-01-01'ANDcreated_at<'2024-01-01'ORDERBYcreated_atDESCLIMIT20;-- 5. 异步统计更新DELIMITER//CREATEPROCEDUREUpdateUserOrderStats(INp_user_idINT)BEGININSERTINTOuser_order_stats(user_id,total_orders,total_amount,last_order_time)SELECTp_user_id,COUNT(*)astotal_orders,SUM(total_amount)astotal_amount,MAX(created_at)aslast_order_timeFROMordersWHEREuser_id=p_user_idONDUPLICATEKEYUPDATEtotal_orders=VALUES(total_orders),total_amount=VALUES(total_amount),last_order_time=VALUES(last_order_time),updated_at=CURRENT_TIMESTAMP;END//DELIMITER;性能监控与验证
-- 1. 创建性能测试表CREATETABLEperformance_test_results(idINTAUTO_INCREMENTPRIMARYKEY,test_nameVARCHAR(100)NOTNULL,query_sqlTEXT,execution_time_msDECIMAL(10,3),rows_examinedINT,rows_sentINT,buffer_pool_readsINT,created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,INDEXidx_test_name(test_name),INDEXidx_created_at(created_at));-- 2. 性能测试脚本DELIMITER//CREATEPROCEDUREPerformanceTest()BEGINDECLAREstart_timeBIGINT;DECLAREend_timeBIGINT;DECLAREexec_timeDECIMAL(10,3);DECLARErows_examinedINT;DECLARErows_sentINT;-- 开始测试前的状态SET@initial_reads=(SELECTVARIABLE_VALUEFROMinformation_schema.GLOBAL_STATUSWHEREVARIABLE_NAME='Innodb_buffer_pool_reads');-- 记录开始时间SETstart_time=UNIX_TIMESTAMP(NOW(6))*1000000+MICROSECOND(NOW(6));-- 执行优化前的查询SELECTo.order_id,o.order_no,o.order_status,o.total_amount,o.created_at,COUNT(oi.item_id)asitem_countFROMorders oLEFTJOINorder_items oiONo.order_id=oi.order_idWHEREo.user_id=12345ANDo.created_at>='2023-01-01'ANDo.created_at<'2024-01-01'GROUPBYo.order_id,o.order_no,o.order_status,o.total_amount,o.created_atORDERBYo.created_atDESCLIMIT20;-- 记录结束时间SETend_time=UNIX_TIMESTAMP(NOW(6))*1000000+MICROSECOND(NOW(6));SETexec_time=(end_time-start_time)/1000.0;-- 获取执行统计SELECTSUM_ROWS_EXAMINED,SUM_ROWS_SENTINTOrows_examined,rows_sentFROMperformance_schema.events_statements_summary_by_digestWHEREDIGEST_TEXTLIKE'%orders o LEFT JOIN order_items oi%'ORDERBYLAST_SEENDESCLIMIT1;-- 记录测试结果INSERTINTOperformance_test_results(test_name,query_sql,execution_time_ms,rows_examined,rows_sent,buffer_pool_reads)VALUES('Before Optimization','SELECT o.order_id, o.order_no, o.order_status, o.total_amount, o.created_at, COUNT(oi.item_id) as item_count FROM orders o LEFT JOIN order_items oi ON o.order_id = oi.order_id WHERE o.user_id = 12345 AND o.created_at >= ''2023-01-01'' AND o.created_at < ''2024-01-01'' GROUP BY o.order_id, o.order_no, o.order_status, o.total_amount, o.created_at ORDER BY o.created_at DESC LIMIT 20',exec_time,COALESCE(rows_examined,0),COALESCE
