服务周到的微网站建设,黑马程序员ppt课件,沈阳整站优化,wordpress专用空间1. 从一条慢SQL说起#xff1a;订单分页查询的困境 去年双11大促期间#xff0c;我们的订单系统突然出现了一批奇怪的慢查询。这些查询看起来非常简单——就是根据买家ID查询最近的订单列表#xff0c;但平均执行时间却达到了惊人的2秒。典型的SQL长这样#xff1a; SELE…1. 从一条慢SQL说起订单分页查询的困境去年双11大促期间我们的订单系统突然出现了一批奇怪的慢查询。这些查询看起来非常简单——就是根据买家ID查询最近的订单列表但平均执行时间却达到了惊人的2秒。典型的SQL长这样SELECT order_id FROM tcorder WHERE is_main1 AND buyer_id12345678 ORDER BY create_time DESC, order_id ASC LIMIT 0,10通过EXPLAIN分析执行计划发现虽然命中了buyer_id的二级索引但Extra列赫然显示着Using filesort。这意味着MySQL不得不把所有符合条件的记录都加载到内存中进行排序然后再取出前10条。对于一个日均订单量千万级的电商平台这种操作简直就是性能杀手。更诡异的是当我们去掉order_id的排序条件后查询速度立即恢复正常。这引出了两个关键问题为什么多一个排序条件会导致性能断崖式下跌为什么这个看似多余的order_id排序会被加到查询中2. BTree索引原理深度解析2.1 为什么索引能加速查询想象一下图书馆找书的场景。没有索引就像在书库里一本本翻找而索引就像图书目录——先找到分类号再定位到具体书架。MySQL的BTree索引就是这样一个多级目录结构非叶子节点存储索引键值和子节点指针类似历史类→中国史→明清史叶子节点存储索引键值和主键相当于具体书架位置所有叶子节点通过指针相连形成链表方便范围查询-- 查看索引统计信息 SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAMEtcorder;2.2 复合索引的排列玄机复合索引的字段顺序至关重要就像电话号码的区号必须在前。我们的索引idx_buyer_create(buyer_id,create_time)先按buyer_id排序相同buyer_id下按create_time排序但order_id完全无序这就是为什么ORDER BY create_time能用索引而ORDER BY create_time,order_id必须filesort——索引中order_id的排列就像乱放的书籍无法利用索引顺序。2.3 索引选择背后的数学MySQL优化器是个精明的会计它会计算每种执行计划的成本-- 查看优化器追踪信息 SET optimizer_traceenabledon; SELECT * FROM tcorder WHERE ...; SELECT * FROM information_schema.optimizer_trace;当它发现使用现有索引需要排序10万条记录全表扫描只需要过滤5万条 就会聪明地选择全表扫描。这就是为什么有时EXPLAIN结果反直觉。3. 实战订单表索引优化方案3.1 临时解决方案查询重写我们首先尝试最小化改动-- 移除order_id排序业务允许时 SELECT order_id FROM tcorder WHERE is_main1 AND buyer_id12345678 ORDER BY create_time DESC LIMIT 0,10 -- 或使用索引提示 SELECT order_id FROM tcorder FORCE INDEX(idx_buyer_create) WHERE is_main1 AND buyer_id12345678 ORDER BY create_time DESC, order_id ASC LIMIT 0,103.2 终极方案索引重构经过压测验证我们设计了新索引ALTER TABLE tcorder ADD INDEX idx_opt(buyer_id, create_time, order_id);这个索引的妙处在于buyer_id用于快速定位用户订单create_time和order_id已经按需排序覆盖查询所需全部字段无需回表3.3 灰度上线SOP大表索引变更必须慎之又慎先在备库验证执行计划使用pt-online-schema-change在线变更按分库分批次灰度监控QPS/CPU/慢查询新旧索引并行运行至少一周# 使用pt工具添加索引 pt-online-schema-change --alter ADD INDEX idx_opt(buyer_id,create_time,order_id) \ Dtest,ttcorder --execute4. 避坑指南索引优化的常见误区4.1 新手容易踩的坑过度索引每个查询一个索引导致写入性能下降解决方案使用复合索引覆盖多个查询无效索引区分度低的字段建索引如性别字段-- 查看字段区分度 SELECT COUNT(DISTINCT status)/COUNT(*) FROM tcorder;隐式转换字段类型不匹配导致索引失效-- buyer_id是varchar却用数字查询 SELECT * FROM tcorder WHERE buyer_id123456784.2 高级技巧索引跳跃扫描MySQL 8.0-- 即使索引(a,b)只查b也能用索引 SELECT * FROM table WHERE b1;降序索引优化倒序查询CREATE INDEX idx_desc ON tcorder(create_time DESC);索引合并的陷阱有时分开索引比复合索引更高效需要analyze table更新统计信息5. 性能对比优化前后的数字说话优化效果立竿见影指标优化前优化后下降幅度平均查询时间2017ms23ms98.8%CPU使用率75%32%57%慢查询数量31061299.6%通过这个案例我深刻体会到索引优化不是玄学而是建立在深入理解存储引擎工作原理基础上的精确手术。每个索引都应该有明确的使命就像图书馆的每本目录都要解决特定的查找需求。