我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ,欢迎关注收藏,也欢迎大家转载,但是请在文章开始地方标注文章出处,谢谢!
由于博客中有大量代码,通过页面浏览效果更佳。
本文为个人学习《Expert Oracle Database Architecture Techniques and Solutions for High Performance and Productivity(第四版本》一书过程中的笔记与理解分享,仅用于学习与交流,部分内容参考原书观点并结合>实际经验进行整理。若涉及版权问题,请联系删除或沟通处理。也请大家支持购买原版书籍。
揭开索引的两个常见误区
在数据库优化领域,关于索引有两个流传甚广的"常识",但实际上它们都是错误的认知。今天我们就来揭开这两个误区,帮助你更好地理解和使用索引。
误区一:索引空间永远不会被重用 ❌
误区的由来
很多人认为:一旦在索引中使用了某个位置,这个空间就永远被占用了。比如你在表中插入X=5,然后删除它,这个X=5使用的索引空间就永远闲置了,除非你再次插入X=5。
真相:空间是会被重用的 ✅
通过一个简单的实验就能证明这一点:
-- 创建测试表
CREATE TABLE test_table (id INT PRIMARY KEY);-- 插入一些数据
INSERT INTO test_table VALUES (1);
INSERT INTO test_table VALUES (2);
INSERT INTO test_table VALUES (999999);-- 然后进行大量删除和插入操作
实验结果显示:尽管我们不断地删除旧数据、插入新数据,索引占用的空间大小基本保持不变。这说明索引空间确实在被循环利用。
但是...这里有个重要的细节
虽然空间会被重用,但索引不会自动"整理碎片"。想象一下你的书架:
- 你有很多书(数据)放在书架上
- 你拿走了一些书(删除数据),留下空位
- 你可以放入新书(插入新数据)来填补空位
- 但书架不会自动重新整理让所有书紧挨着摆放
这就是索引的实际情况:删除数据会留下"空洞",只有合适大小的新数据才能填入这些空洞。如果需要彻底整理,需要手动执行:
ALTER INDEX index_name REBUILD; -- 重建索引
误区二:要把最具区分度的列放在索引最前面 ❌
传统的"常识"
很多人认为:在创建复合索引(多列索引)时,应该把区分度最高的列(唯一值最多的列)放在最前面。
比如表中有10万行数据:
- 列C1有10万个不同值(区分度极高)
- 列C2有2.5万个不同值(区分度较高)
按照这个误区,应该创建索引(C1, C2)
真相:顺序应该由查询需求决定 ✅
通过实际测试发现:
- 索引(C1, C2)和(C2, C1)占用的空间几乎相同
- 两者的查询性能也基本没有差别
- 唯一不同的是压缩效果:低区分度的列在前压缩效果更好
正确的决策方式
索引列的顺序应该根据实际查询需求来决定:
-- 情况1:如果经常这样查询
SELECT * FROM 表 WHERE C1 = ? AND C2 = ?;
SELECT * FROM 表 WHERE C2 = ?;-- 那么索引(C2, C1)更合适,因为:
-- 1. 两个查询都能使用这个索引
-- 2. 压缩效果更好(C2的重复值更多)
举个实际例子
假设你管理一个用户表,经常需要:
- 按城市和年龄查询用户
- 有时只按城市查询
那么索引应该这样设计:
CREATE INDEX idx_users ON users(城市, 年龄);
-- 而不是
CREATE INDEX idx_users ON users(年龄, 城市);
因为城市值的重复较多,放在前面压缩效果更好,而且支持更多种类的查询。
总结
误区 | 真相 | 建议 |
---|---|---|
索引空间不会被重用 | 空间会被循环利用 | 定期重建索引整理碎片 |
高区分度列必须放前面 | 顺序由查询需求决定 | 按实际查询模式设计索引顺序 |
记住这两个要点,你就能避免常见的索引设计陷阱,让数据库性能更上一层楼!
------------------作者介绍-----------------------
姓名:黄廷忠
现就职:Oracle中国高级服务团队
曾就职:OceanBase、云和恩墨、东方龙马等
电话、微信、QQ:18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)