我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ,欢迎关注收藏,也欢迎大家转载,但是请在文章开始地方标注文章出处,谢谢!
由于博客中有大量代码,通过页面浏览效果更佳。
本文为个人学习《Expert Oracle Database Architecture Techniques and Solutions for High Performance and Productivity(第四版本》一书过程中的笔记与理解分享,仅用于学习与交流,部分内容参考原书观点并结合>实际经验进行整理。若涉及版权问题,请联系删除或沟通处理。也请大家支持购买原版书籍。
为什么你的索引“罢工”了?六种常见原因解析
在日常数据库工作中,很多开发者都会遇到一个令人困惑的问题:明明已经建立了索引,为什么查询还是那么慢?甚至发现数据库根本没有使用索引!
其实,索引不被使用的原因多种多样。下面我们就来解析六种最常见的情况,帮你揭开索引“罢工”的秘密。
情况一:没走对门——前导列缺失
想象一下你要在一本按“省份+城市”排序的电话黄页中查找所有叫“张三”的人。如果你不知道他在哪个省份,就只能翻遍整本书——这就是前导列缺失的问题。
具体表现:
- 索引建立在 (x, y) 列上
- 查询条件只使用了 y 列:
WHERE y = 5
- 优化器选择全表扫描而不是使用索引
例外情况:
-
只查询索引列:如果只需要 x 和 y 列,数据库可能会直接扫描索引
SELECT x, y FROM t WHERE y = 5; -- 可能使用索引
-
跳跃扫描:当前导列值种类很少时(如性别只有男/女),数据库可以“跳跃”查找
-- 虽然条件只有empno,但gender值很少时可能使用索引 SELECT * FROM employees WHERE empno = 1001;
情况二:数数不准——统计行数的陷阱
当你想要统计表中有多少行数据时:
SELECT COUNT(*) FROM table_name;
如果索引列允许为NULL,数据库宁愿全表扫描也不使用索引。为什么呢?因为完全为NULL的条目不会被收入索引,用索引统计会漏掉这些行,导致结果不准!
情况三:改头换面——函数转换的坑
这是很常见的错误:在索引列上使用函数。
SELECT * FROM table_name WHERE UPPER(name) = 'ZHANGSAN';
虽然name列有索引,但UPPER(name)
没有。数据库需要为每一行计算函数值,然后比较——这就像要求每个人先化妆再辨认,当然慢了!
解决方案:建立函数索引
CREATE INDEX idx_upper_name ON table_name(UPPER(name));
情况四:鸡同鸭讲——类型不匹配
数据库很“固执”,要求比较双方必须类型一致。
-- 字符列与数字比较
SELECT * FROM users WHERE char_column = 123; -- 错误!-- 相当于
SELECT * FROM users WHERE TO_NUMBER(char_column) = 123; -- 隐式转换!
正确做法:苹果对苹果,橙子对橙子
SELECT * FROM users WHERE char_column = '123'; -- 字符对字符
日期查询也很容易出错:
-- 错误:索引失效
SELECT * FROM orders WHERE TRUNC(order_date) = TRUNC(SYSDATE);-- 正确:使用范围查询
SELECT * FROM orders
WHERE order_date >= TRUNC(SYSDATE) AND order_date < TRUNC(SYSDATE) + 1;
情况五:聪明反被聪明误——全表扫描更快
这不是bug,而是feature!优化器很聪明,知道什么时候用索引反而更慢。
什么时候全表扫描更快?
- 需要查询大部分数据时(如超过20-30%的表数据)
- 表很小,索引反而增加额外开销
- 数据非常集中,随机访问反而慢
-- 查1%的数据:可能用索引
SELECT * FROM large_table WHERE id < 1000;-- 查99%的数据:肯定全表扫描
SELECT * FROM large_table WHERE id < 1000000;
不要盲目强制使用索引,先测试证明索引确实更快再说!
情况六:信息过时——统计信息陈旧
数据库优化器就像靠地图导航的司机,如果地图过期了,就会走错路。
典型场景:
- 表刚开始很小,优化器认为全表扫描快
- 表增长到很大,但统计信息没更新
- 优化器继续使用过时的执行计划
解决方案:定期更新统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
总结
索引不被使用通常不是数据库的bug,而是有合理原因的。下次遇到索引“罢工”,可以按这个清单排查:
- ✅ 检查是否缺少前导列
- ✅ 确认没有在索引列上使用函数
- ✅ 确保比较的类型一致
- ✅ 判断是否真的需要索引(可能全扫描更快)
- ✅ 更新统计信息
- ✅ 确认索引不是用于统计允许为NULL的行
理解这些原理,你就能更好地设计索引和编写高效的SQL语句,让数据库查询飞起来!
------------------作者介绍-----------------------
姓名:黄廷忠
现就职:Oracle中国高级服务团队
曾就职:OceanBase、云和恩墨、东方龙马等
电话、微信、QQ:18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)