大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
刚做DBA那阵子，我对“加索引”这件事有一种朴素的信仰——查询慢？加索引。再慢？再加一个。
直到有一次，一条查询本来跑200ms，我信心满满地加了个索引，结果变成了2秒。
我当时的第一反应是：索引建错了？查了一下，索引建对了，字段类型也没问题。再跑EXPLAIN，优化器确实用了这个新索引。
用了索引，反而更慢了。
那次之后我才明白，索引不是“建了就行”。优化器对索引的选择，有一套自己的逻辑。这套逻辑在大多数时候是对的，但在特定场景下，它会做出看起来合理、实际很糟糕的决定。
先把这件事里绕不开的几个概念说清楚：
索引选择性——索引列中不同值的数量与总行数的比值。选择性越高（不同值越多），索引过滤效果越好。比如主键的选择性是1，status只有5个值，选择性是0.000005。
回表——通过二级索引找到主键值后，再回到聚簇索引读取完整行数据。每次回表是一次随机I/O。
Index Merge——优化器同时使用多个索引，把各自的结果集做交集（Intersection）或并集（Union），再合并输出。
一、优化器为什么会选错索引
优化器选择索引的依据是代价估算。它估算每个候选索引的I/O代价和CPU代价，选最低的那个。问题在于，这个估算有两个容易出偏差的地方。
偏差一：基数估算不准。
优化器用采样估算索引列的基数（不同值的数量）。如果数据分布倾斜，采样结果可能严重偏离实际。比如status列有5个值，但status='DONE'占了90%。优化器采样时如果恰好抽到几个PENDING的行，会以为status的选择性很高，选了这个索引。实际执行时，90%的行都要回表，回表次数远超预期。
偏差二：回表代价被低估。
优化器估算回表代价时，用的是固定的成本常数。但真实的回表代价取决于数据在磁盘上的分布——如果主键是随机的（UUID），回表就是随机I/O；如果主键是自增的，回表可能是顺序I/O。优化器不区分这两种情况。
我那次加索引反而变慢，就是踩了第二个偏差。新加的索引选择性看起来不错，但回表的主键是随机的，每次回表都是随机I/O，几十万次随机I/O的开销，远超全表扫描的顺序读。
二、Index Merge：看起来聪明，实际很贵
Index Merge是另一个容易踩坑的场景。
当WHERE条件涉及多个索引列时，优化器可能选择Index Merge——同时扫描两个索引，取交集或并集。听起来很合理，但实际上合并操作本身的开销可能超过全表扫描。
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'PAID';
如果user_id和status各有一个单列索引，优化器可能同时扫描两个索引，取交集。但如果user_id=12345返回10万行，status='PAID'返回50万行，交集运算要处理这两个结果集——合并、去重、排序，开销可能比直接全表扫描还大。
更危险的是Union类型的Index Merge（OR条件触发）。两个索引的结果集取并集，去重和排序的代价更高。
Index Merge的代价藏在三个地方：多次索引扫描、结果集在内存中的合并运算、以及多次回表。优化器在估算时，往往低估了合并运算和多次回表的开销。
三、怎么确认优化器选错了
方法一：EXPLAIN ANALYZE对比估算与实际
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'PAID';
MySQL 8.0.18+的EXPLAIN ANALYZE会输出每个步骤的实际执行行数和时间。对比rows（估算）和actual rows（实际），如果差距很大（比如差10倍以上），说明基数估算有偏差。
方法二：看Extra列的Index Merge信号
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345 OR status = 'PAID';
如果Extra列出现Using union(...)或Using intersect(...)，说明触发了Index Merge。这时候要评估合并的代价是否真的比全表扫描低。
方法三：用FORCE INDEX验证
-- 强制走单个索引，对比响应时间
SELECT * FROM orders FORCE INDEX(idx_user_id)
WHERE user_id = 12345 AND status = 'PAID';
如果强制走单个索引比让优化器自由选择更快，说明优化器选错了。这是最直接的验证方法。
四、解决方案
方案一：更新统计信息。
ANALYZE TABLE orders;
80%的索引选错问题，更新统计信息就能解决。优化器拿到准确的基数估算后，会重新评估代价。
方案二：建更匹配查询模式的联合索引。
-- 替代两个单列索引 + Index Merge
CREATE INDEX idx_user_status ON orders(user_id, status);
联合索引一次扫描就能定位到目标行，不需要Index Merge的合并运算，也不需要多次回表。这是比Hint更根本的解法。
方案三：删掉低效索引。
如果某个索引建了之后，优化器频繁选错，而且实际执行效果很差，考虑删掉它。索引不是越多越好——每个索引都会影响优化器的选择空间。
方案四：用FORCE INDEX作为临时方案。
确认优化器选错后，用FORCE INDEX强制走正确的索引。但这是临时方案——数据分布变化后，Hint可能产生反效果。
五、小结
索引建了查询反而变慢，根因往往在优化器的选择：基数估算偏差导致它选了“看起来选择性高”但回表代价巨大的索引，或者触发了Index Merge，合并运算的开销超过全表扫描。排查的核心是用EXPLAIN ANALYZE对比估算与实际、看Extra列是否出现Index Merge、用FORCE INDEX验证。解决方案优先更新统计信息和建联合索引，FORCE INDEX只是临时手段。
那次加索引反而变慢之后，我改掉了“慢就加索引”的习惯。加索引之前先看执行计划，加完之后用EXPLAIN ANALYZE验证实际效果。索引是工具，不是信仰。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
本文由 AI图鉴自动抓取并翻译，版权归原作者所有；如需查看原文请点下方链接。
Hello, I'm a kid. I only write for the pit I stepped on.
When I was a DBA, I had a simple faith in indexing -- slow searching? Index. Slower? One more.
Until one time, a query had run 200 ms, and I had the confidence to add an index, which turned into two seconds.
My first reaction was: was the index wrong? Check it out. The index is right. The field type is fine. Explain, the optimizer does use this new index.
The index was used, but it was slower.
It was only after that that that I realized that the index was not "just built." The optimizer has its own logic for index selection. This logic is most of the time right, but in a given context it makes what seems reasonable and actually bad decisions.
Let's start with a few concepts that we can't get around:
Index Select - the ratio of the number of different values in the index column to the total number of rows. The higher the selectivity (more different values), the better the index filter. For example, the primary key is optional, with only five values for Status, and optional is 0.00005.
Return table - When the primary key value is found through the secondary index, you return to the cluster index to read the full line data. Every return is a random I/O.
Index Merge - The optimizer uses multiple indices, which combines the results into an intersection or a combination (Union) and consolidates the output.
I. Why did the optimizer choose the wrong index?
The optimisation index is based on cost estimates. It estimates the I/O and CPU costs for each candidate index, the lowest. The problem is that this estimate has two vulnerable areas of error.
Discrepancies I: The base figure is incorrect.
The optimizer uses sampling to estimate the base number of index columns (number of different values). If the data distribution is tilted, the results of the sampling may deviate significantly from the actual situation. For example, status has five values, but status='Done's 90%. The index is selected by the optimist sample if it happens to have been taken to several PENDUING lines. At the time of actual implementation, 90 per cent of the lines had to be returned and the number of returns had far exceeded expectations.
Differing 2: The cost of return is underestimated.
The optimizer estimates the return cost using a fixed cost constant. But the real return costs depend on the distribution of data on disks - If the primary key is random (UUID), the return table is random I/O; if the primary key is spontaneous, the return table may be sequence I/O. Optimizers do not distinguish between the two situations.
I was slowing down the index, and I stepped on the second deviation. The new index appears to be a good choice, but the primary key to the return table is random, each time I/O is random, and hundreds of thousands of random I/O expenses go well beyond the sequence of full table scans.
Index Merge: Looks smart, actually expensive.
Index Merge is another scene that is easy to step on.
When the WHERE condition involves multiple index columns, the optimizer may choose Index Merge - scan both indexes at the same time, cross or combine. It sounds reasonable, but in practice the consolidation operation itself may cost more than a full-form scan.
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'PAID';
If there is a separate column index for user id and for Status, the optimizer may scan both indices at the same time and retrieve the intersection. However, if the user id=12345 returns 100,000 lines, the status=`PAID' returns 500,000 lines, and the transactional calculations deal with the two result sets - merging, rearranging, sorting - the cost may be greater than the direct full-scale scan.
More dangerous is the Union type Index Merge. The results of the two indices are taken together, and the cost of rearranging and sorting is higher.
The cost of Index Merge is hidden in three places: multiple index scans, combined calculations in the results set memory, and multiple returns. Optimizers tend to underestimate the costs of combined operations and multiple returns when estimating them.
III. How to confirm that the optimizer was wrong
Method I: EXPLAIN ANALIZE Comparative estimate versus actual
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345 AND status = 'PAID';
MySQL 8.0.18+ EXPLAIN ANALIZE will output the actual number of lines and time for each step. Compared with a large gap (e.g. more than 10 times the difference) between arows (estimate) and an actualrows (actual) indicates a deviation in the base figure estimates.
Method two: Look at the Index Merge signal in Extra.
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345 OR status = 'PAID';
If the Extra column appears Using UN(...) or Using interspect(...), it triggers Index Merge. At this point, it is important to assess whether the cost of consolidation is really lower than the full-scale scan.
Method III: Validate with FORCE INDEX
- Forced removal of individual indexes to compare response times
SELECT * FROM orders FORCE INDEX(idx_user_id)
WHERE user_id = 12345 AND status = 'PAID';
If it is faster to force a single index than to free the optimizer, the optimist is wrong. This is the most direct method of verification.
Solutions
Programme I: Updating statistical information.
ANALYZE TABLE orders;
The problem of 80 per cent of index errors can be solved by updating statistical information. The costs will be reassessed once the optimizer has obtained an accurate estimate of the base figure.
Option II: Establish a joint index that better matches the search mode.
-- Replace two separate columns index + Index Merge
CREATE INDEX idx_user_status ON orders(user_id, status);
A joint index scan allows the location of the target line and does not require a combined operation of Index Merge or multiple returns. This is a more fundamental solution than Hint.
Option 3: Delete the inefficient index.
If an index is created, the optimizer is frequently wrong and the actual performance is poor, consideration is given to deleting it. The index is not as good as it is - each index affects the choice of the optimizer.
Option IV: use FORCE INDEX as an interim programme.
Confirms that the optimizer is wrong and forces the correct index with FORCE INDEX. However, this is an interim programme - Hint may be counterproductive as data distribution changes.
Summary
Indexing slows down, as root causes tend to opt for optimizers: the base figure estimates deviations lead it to select an index that “looks selectively high” but returns to the table at a high cost, or triggers Index Merge, where the cost of the combined operation exceeds the full table scan. The core of the review was to compare EXPLAIN ANALIZE with reality, to see if the Extra column appeared in Index Merge, and to verify it with FORCE INDEX. The solution gives priority to updating statistical information and establishing a joint index, and FORCE INDEX is a temporary tool.
And after that indexing slows down, I changed the habit of "slow indexing." Read the implementation plan before indexing, and then use EXPLAIN ANALIZE to verify the actual effects. Indexes are tools, not beliefs.
Je's got it, SQL.
Anything else you want to know, please leave a message! I'm sure Yao knows everything... I'll see you next time.
Auto-fetched and translated by AI Atlas. All rights belong to the original author.