数据库调优这件事大多数时候卡死在两块索引怎么建以及查询怎么写。只要把这两个问题摸索清楚慢查询、全表扫描、锁等待这些老大难问题至少去掉一半。这篇笔记是“SQL核心操作笔记”系列的第二篇紧接上一篇的环境准备与基础查询这次把索引创建和数据查询这两个核心方向一次性讲透。我见过不少团队线上出慢查询第一反应就是加索引。但加完之后查询是快了写入却变慢了还有的项目连主键都没建全表扫描起来能让你怀疑服务器是不是在被挖矿。说到底是没弄明白索引到底在帮你干什么也没搞清楚查询优化器是怎么“看”索引的。这篇文章就直接从执行计划的角度反推索引设计聊聊索引创建的正确姿势再拆解几个高频数据查询场景包括去重、窗口函数、慢SQL优化最后附上这些年我踩过的坑和排查心得。1. 索引创建的核心逻辑与常见陷阱1.1 聚簇索引与非聚簇索引的分工不是“快慢”而是“用途”很多新手把索引理解成“一张加速表”其实这个理解太粗糙。聚簇索引和非聚簇索引的区别几乎决定了你后续所有查询优化的方向。聚簇索引的意思是表的数据行物理存储顺序跟着这个索引的键值走。SQL Server默认会在主键上建聚簇索引MySQL的InnoDB也强制要求表必须有一个聚簇索引一般就是主键。换句话说聚簇索引就是表本身数据行就挂在索引的叶子节点上。所以通过主键查找一次就能定位到数据效率极高。非聚簇索引在MySQL里叫二级索引则是另一张独立的结构它的叶子节点存的是索引键加上一个“指向数据行的指针”。SQL Server里这个指针是聚簇索引键或者RIDMySQL InnoDB里存的是主键值。所以当一条查询用了二级索引但需要的列没有被这个索引完全覆盖时数据库还得拿主键值去聚簇索引里再查一次这个动作叫回表。回表是慢查询的重灾区。我之前负责过一个电商订单库订单表千万级。运营经常用“客户编号下单时间”查订单明细一开始只有主键索引每次按客户查都要回表单次查询几十毫秒并发一上来直接飙到一秒多。后来加了一个覆盖索引-- SQL Server CREATE INDEX ix_orders_customer_date ON dbo.Orders (CustomerID, OrderDate) INCLUDE (Status);-- MySQL CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date, status);同样的查询执行时间直接降到五毫秒左右。原因很简单索引叶子节点已经包含查询需要的列不需要回表了。所以你在设计索引时先问自己一句这个查询能不能被一个索引完全覆盖能覆盖就尽量覆盖这是性价比最高的一招。1.2 SQL Server与MySQL创建索引的语法对照索引创建的语法本身不难但两个数据库在细节上差异不小尤其是“包含列”这个概念很多人容易搞混。场景SQL Server 写法MySQL 写法普通索引CREATE INDEX idx_col ON tbl(col);同左唯一索引CREATE UNIQUE INDEX idx_col ON tbl(col);同左复合索引CREATE INDEX idx_a_b ON tbl(a, b);同左包含列索引CREATE INDEX idx_a ON tbl(a) INCLUDE (b, c);无INCLUDE改用复合索引删除索引DROP INDEX idx_col ON tbl;DROP INDEX idx_col ON tbl;MySQL没有INCLUDE语法通常的做法是把需要返回的列直接放进复合索引里。但要注意InnoDB的二级索引叶子节点本来就会带上主键列所以如果查询只需要索引列加主键你甚至不用额外加主键列。SQL Server的包含列设计其实很聪明。它把不是过滤条件、只是查询返回结果的列放在叶子节点但又不参与索引键的排序和大小计算这样既减少了回表又不会让索引键变得过大。用的时候别把区分度很低的列比如性别、状态放在索引键开头否则索引体积飙升收益却很小。还有一个小细节SQL Server里老的删除索引语法是DROP INDEX tbl.idx新版本推荐DROP INDEX idx ON tbl写错了会直接报语法错误。MySQL则一直用DROP INDEX idx ON tbl。这些差异看似小跨库迁移的时候特别容易踩。1.3 复合索引设计最左前缀原则与覆盖索引最经典的问题场景一个表有三个查询条件你给每个条件各建了一个单列索引结果查询还是慢。因为数据库通常只能在一个索引上做主要过滤其它两个条件要回表之后再去比对。正确的做法是建复合索引但复合索引的顺序有讲究核心就是最左前缀原则。CREATE INDEX idx_abc ON t (a, b, c);下面这些写法可以用上索引WHERE a 1 AND b 2 AND c 3 WHERE a 1 AND b 2 WHERE a 1但下面这些就基本用不上了WHERE b 2 WHERE b 2 AND c 3 WHERE c 3这个规律就像查字典先按拼音首字母定位再按音节、最终落到具体字。你跳过了第一层后面再精确也找不到位置。那复合索引的列顺序怎么排除了把等值查询的列放前面还要考虑区分度。区分度高的列过滤掉的行多放前面能让索引更快收窄范围。常见误区是机械地把“最常用的列”放第一位却没看它的区分度。比如一个status字段只有三个值把它放在索引最左边等于把索引变成了一个大文件夹里面每个小夹子里都塞了几十万行效率自然差。覆盖索引是另一个进阶玩法。如果索引包含了查询需要的所有列数据库就不会回表。判断方法很简单执行计划里的“书签查找”或“回表”变少说明覆盖成功了。SQL Server可以直接用INCLUDE把多余返回列塞进叶子节点MySQL就在复合索引末尾追加返回列但注意别塞太多大字段比如TEXT类型那会让索引跟一张小表似的得不偿失。2. 数据查询的关键场景拆解2.1 去重查询DISTINCT、GROUP BY还是ROW_NUMBER“SQL语句去重”这个问题我几乎每次面试都能遇到但真正问清楚需求的人不多。你要先弄明白是整行完全重复还是按某几个键去重、保留业务上需要的那一行。整行重复最简单直接DISTINCTSELECT DISTINCT customer_id, sign_date FROM user_sign_log;如果只要“每个客户最新的签到记录”并且想保留整行比如积分、渠道这些字段DISTINCT就无能为力了。这时候标准的做法是窗口函数ROW_NUMBERSELECT id, customer_id, sign_date, points FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sign_date DESC, id DESC) AS rn FROM user_sign_log ) t WHERE rn 1;PARTITION BY把每个客户的记录分成一组ORDER BY决定组内排序然后取每组的第一行。这套写法既能拿到最新记录又能保住整行信息是数据清洗里的万能模板。还有一种场景是删除重复数据只保留每组最小的id。MySQL里可以直接用关联删除SQL Server 2008以后也可以这样写DELETE log FROM user_sign_log log WHERE NOT EXISTS ( SELECT 1 FROM user_sign_log keep WHERE keep.customer_id log.customer_id AND keep.sign_date log.sign_date AND keep.id log.id );意思很直白如果存在一条同客户、同日期但id更大的记录当前这条就是垃圾数据删掉。至于GROUP BY去重它更常用在“按分组做统计”的场合比如查每个客户的签到次数。单纯去重用GROUP BY反而会让没被分组的列没法处理容易把业务搞复杂。2.2 窗口函数一次解决排名、累计值和前后行对比窗口函数这些年几乎是面试标配从SQL Server 2005到MySQL 8.0各家都支持得不错。它最大的特点是在不改变行数的情况下同时得到明细行和聚合计算结果比子查询和自连接清晰太多。比如统计每个客户的累计消费金额SELECT order_id, customer_id, order_amount, SUM(order_amount) OVER ( PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM orders;这就是额度累计后面一项会带上前面所有行。没有窗口函数的时候要么用相关子查询要么做一次自连接数据量稍微一上来就跑不动。再看一个特别实用的场景找“比上一单金额高的订单”。用LAG函数轻松实现SELECT order_id, customer_id, order_amount, LAG(order_amount, 1) OVER ( PARTITION BY customer_id ORDER BY order_date ) AS prev_amount FROM orders;拿到prev_amount后套一层判断就可以了完全不必要写自连接。排名方面ROW_NUMBER、RANK、DENSE_RANK三者的区别经常把人绕晕。简单记ROW_NUMBER是唯一连续编号相同分数也不会并列RANK相同分数会并列后续编号跳过比如1、1、3DENSE_RANK同样并列但不跳号比如1、1、2。业务上如果要“并列”用RANK如果要“第几名且名次连续”用DENSE_RANK如果只关心唯一的行标识用ROW_NUMBER。窗口函数容易踩的坑是在ORDER BY字段和PARTITION BY字段上没建索引导致窗口排序时把整个分组数据拉出来重新排。我就见过一张千万级表的统计SQL跑一次十几秒最后在分组列和排序列上各加了一个索引直接把时间砍到一秒以内。窗口函数看着是“语法层面”的东西优化时还是要回到索引层面去找真相。2.3 写查询语句的几个隐性习惯很多慢SQL不是被复杂业务拖慢的而是从写第一句开始就埋了雷。第一个雷就是SELECT *。它会强行把非索引列也捞出来破坏覆盖索引的效果。更要命的是如果表里有个大字段比如备注或者JSON查询会连带把这些大块数据一起传输网络I/O直接爆炸。正确做法是只写需要的列这也是让索引覆盖成为可能的先决条件。第二个雷是在WHERE里对索引列做函数运算。比如WHERE DATE(create_time) 2024-06-01这是索引失效的经典写法因为函数让优化器无法直接比较索引键值。改成范围查询就好WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00第三个雷是隐式类型转换。比如整型列和字符串比较WHERE user_id 123MySQL会隐式地把字符串转成数字看似没什么问题但可能让索引失效。SQL Server同理比较两边的类型不一致时优化器就得在索引键上做一层转换索引照样用不上。写查询时保持类型一致是成本最低的优化方法。3. 慢SQL优化的完整思路3.1 一条慢查询的标准排查流程我一般拿到慢SQL会按下面四个步骤走不绕弯子。第一步确认SQL本身是不是“可优化”的写法。先把执行计划拉出来SQL Server用SET STATISTICS IO ON和SET STATISTICS TIME ONMySQL用EXPLAIN。这一步看的是扫描类型是全表扫描还是索引查找还是索引扫描后大量回表。第二步检查过滤条件列上有没有合适的索引以及索引的顺序对不对。比如下面这条查询SELECT order_id, customer_id, order_date, amount FROM orders WHERE status 1 AND order_date BETWEEN 2024-06-01 AND 2024-06-30;orders表有500万行status字段区分度很低只有0和1两个值。这时候单独给status建索引基本没意义正确做法是把order_date放前面CREATE INDEX idx_orders_date_status ON orders (order_date, status);执行计划会从ALL全表扫描变成range范围扫描CPU时间从几百毫秒一下降到几十毫秒。第三步看统计信息是不是过期了。优化器靠统计信息估算行数统计信息旧了估算偏差大就可能选错计划。最常见的现象是同一个SQL白天跑得挺快晚上大批量更新完数据之后就突然慢了往往不是写法变了而是统计信息没跟上。第四步验证改动不能只看执行时间。要看逻辑读、CPU时间、预估行数和实际行数的差异。SQL Server里把实际执行计划打开对比预估行数和实际行数如果差了十倍百倍多半是统计信息或参数嗅探问题不是简单加索引能解决的。3.2 索引失效的高频场景与可SARGable写法“SARGable”这个词听着吓人其实就是“能不能用索引键做范围或等值比较”。我用一张表总结高频失效场景。场景错误写法正确写法列上套函数WHERE DATE(create_time) 2024-06-01WHERE create_time 2024-06-01 AND create_time 2024-06-02隐式转换WHERE user_id 123WHERE user_id 123前导通配符WHERE name LIKE %张WHERE name LIKE 张%OR连接不同列WHERE a 1 OR b 2WHERE a 1 UNION ALL WHERE b 2对索引列做计算WHERE price * 0.8 100WHERE price 100 / 0.8特别注意LIKE %keyword%这种全文检索性质的写法索引只能干瞪眼。如果真要做模糊搜索应该考虑全文索引或搜索引擎别在普通B树索引上死磕。OR连接不同列的情况在MySQL里很容易变成全表扫描尽管优化器有索引合并的机制但触发条件苛刻性能也不稳定。我通常建议拆成两个查询再用UNION ALL合并至少能让每段查询走自己的索引。3.3 执行计划里最值得警惕的几个信号看执行计划不是看“有没有用索引”而是看瓶颈在哪。MySQL的EXPLAIN里type列从好到差大致是const、eq_ref、ref、range、index、ALL。一旦看到ALL就是全表扫描必须回头检查索引。看到index也不是万事大吉它表示“扫描了整个索引”如果索引很大性能依然糟糕常见原因是查询跳过了最左前缀列或者选了区分度很低的索引键。SQL Server方面我最关注三个地方表扫描或聚集索引扫描说明优化器选择遍历全表代价高。关键查找Key Lookup说明二级索引找到了行指针但还要回聚簇索引拿其它列回表次数一多就慢。预估行数和实际行数严重不符说明统计信息或参数嗅探出问题需要更新统计信息或考虑用OPTION (RECOMPILE)处理参数敏感场景。看执行计划时我习惯把鼠标悬停在每个运算符上看“估计的行数”再看“实际执行的行数”。这两个数字对不上比什么索引失效警告都更直接地暴露问题。4. 长期维护与常见问题速查4.1 索引维护碎片整理、统计信息更新索引不是建完就完事的。频繁的增删改会制造页碎片让索引的逻辑顺序和物理顺序不一致。碎片多了原来一个范围查询能顺序读的页变成到处跳着读磁盘I/O自然上涨。SQL Server可以用下面这条语句查看碎片率SELECT OBJECT_NAME(ps.object_id) AS table_name, ps.index_id, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, LIMITED) ps;碎片率低于30%用ALTER INDEX ... REORGANIZE整理一下就行在线操作影响小高于30%说明碎片已经比较严重考虑ALTER INDEX ... REBUILD重建索引。但要注意REBUILD在SQL Server低版本里基本上是要锁表的最好放在业务低峰期执行。MySQL对应的是OPTIMIZE TABLE同样会在维护窗口里重建表并整理碎片。统计信息更新同样不能忘。SQL Server里手动更新用UPDATE STATISTICS 表名 索引名MySQL用ANALYZE TABLE 表名。大批量导入、大量删除、数据分布发生明显变化后第一时间跑一遍比盲目重建索引管用得多。4.2 高频问题排查速查表现象可能原因处理方法建了索引但执行计划没走索引区分度低、统计信息过期、隐式转换检查type/扫描类型更新统计信息修正比较类型查询没变但突然变慢统计信息过期或参数嗅探SQL Server考虑RECOMPILEMySQL更新统计信息写入越来越慢表上索引过多删掉低效索引监控索引使用统计复合索引部分条件无法命中没遵守最左前缀原则调整查询条件顺序或重建索引二级索引命中后仍然慢回表次数太多改用INCLUDE或把返回列加进索引连接提示密码过期SQL Server密码策略强制修改用ALTER LOGIN更新密码并调整策略导入SQL脚本执行后部分失败脚本编码或分隔符问题SQL Server用sqlcmd指定编码MySQL用source命令执行脚本这个事顺带说一句。很多人用图形工具导入SQL脚本遇到中文字符乱码或者分号冲突就抓瞎。MySQL命令行执行整库脚本推荐mysql -uroot -p --default-character-setutf8mb4 dump.sqlSQL Server用sqlcmdsqlcmd -S 服务器地址 -U 用户名 -P 密码 -d 数据库名 -i script.sql脚本文件尽量用UTF-8或对应数据库的兼容编码保存避免执行到一半因为编码问题中断。另外一个我多次帮同事排查的问题连接SQL Server时报“密码已过期”尤其是在默认安全性要求比较高的环境里。最简单的处理是直接重设密码ALTER LOGIN sa WITH PASSWORD 新的强密码, CHECK_POLICY OFF;CHECK_POLICY OFF可以暂时关闭复杂度策略但生产环境不建议长期关闭这只是应急手段。4.3 底线参数化查询不是选项是必须前面聊了索引、执行计划、窗口函数最后补一个和性能并列重要的点安全。很多人写SQL喜欢直接用字符串拼接这个习惯必须改掉。应用层查询永远优先用参数化方式比如Java的PreparedStatement或者.NET的SqlParameter// Java示例 PreparedStatement ps conn.prepareStatement( SELECT * FROM users WHERE user_id ? ); ps.setInt(1, userId);让数据库引擎把参数当值处理而不是把用户输入“拼进SQL语句”再执行。这样才能从根本上避免SQL注入这类风险。ORM框架内部大多是参数化执行的但如果你习惯手写原生SQL一定要自己把参数绑定做对。但凡从外部传入的字符串都不应该直接拼进SQL。这条底线守住数据和系统安全就都稳了一大半。最后说个实际感受。数据库优化从来不是建一个索引就高枕无忧的事真正让我觉得踏实的工作流程是先看数据分布再设计索引然后写可SARGable的查询最后用执行计划反复核对。夜间大批量更新之后花几分钟重新统计一下表、看一眼碎片率这种“体力活”反而比一次盲目加索引更有效。希望这篇笔记能让你建立起从索引创建到数据查询的完整排查思路下次再遇到慢SQL先别急着加索引稳稳地拆一遍执行计划很多问题其实都藏在最简单的细节里。