这个索引专题N久未见阳光了、偶觉得特过去、不幸福、于是有了本文
坊间传闻、B*Tree 索引在应用开发阶段、常作"炮灰"状、而在维护时期、则是"马后炮"
即便身世坎坷常被冷眼相待、但到底是B*Tree、魅力无限、全身散发着令人 YY 的韵味、你比如:
如果存在主键或者唯一性约束、则不论是访问1w行、还是1亿行、这个CPU或IO的开销都相去无几呀
哇、、、美丽可爱大方如B*Tree、怎么能令人不爱呢、
说起 B*Tree 呀、俺的话就多了、可是话一多、又有些结巴、有些不知所措、有些不知所云
现就在混沌不知开窍的地方找点东东来小结一下、
B*Tree 索引都是唯一索引、如果你:
create index idx_t on t(col);
则Oracle内部会帮你:
create unique index idx_t on t(col,rowid)
SO、Oracle真是阿拉神灯呀、如果您不显示指定唯一的话、那么:
索引是按索引键值排序、然后、按rowid 升序排序哦
不过、要是您直接指定了唯一性索引、那就只有索引键值的排序鸟
有 2个参数蛮有可看性的:Blevel & Height
为啥有可看性嘞、那就看他活着的价值咯、
这两家伙纯粹就是为了衡量访问索引所需的I/O、这可从统计信息的:consistent gets 上看出
那么、哲学上有个问题是:你来自哪里?同问:这 2 货来自喵星球??
Blevel from user_indexes
Height from index_stats
Blevel+1、也就是Height哦
有地方要稍微注意下、收集信息的时候、
user_indexes 需调用 dbms_stats.gather_index_stats
index_stats 则要 analyze index index_name validate structure
嗯、嗯、那么一个表需要多少颗 B*Tree 才合理呀?
TOAD上有个健康巡检的工具、内建 6 颗为合理、超过要报警
但 6 到底合理吗?合理吗?毫无疑问、不合理、理由?自己想、哈哈
那、到底多少算合理哈、我也不知道哦、这个得看您的系统workload
这里推荐一个神功、欲练此功、必先、、、、、、、、
① 找出所有和该表相关的SQL
② 按 buffer gets 或physical read 排序、找出 TOP N
③ 找出 where 条件和连接条件
接下去、你都懂了、拿起小刀子、不、不、瑞士军刀、就这样、神功初成
来聊聊 B*Tree 的索引的类型吧、全部都讲也不现实哈、虽然俺也不懂、不过普通的B*Tree就是打死俺也不会说的
就举几个世界级、重量级的百闻不如一见、见面不如闻名的大BOSS
㈠ 函数索引
短评:任何系统、函数索引都是不可避免的、但你扪心自问下、您用了没?我敢打包票
没多少人有这个意识、要是有、你请我吃切糕、怎么样啦
㈢ 反转键索引
短评:能处理索引热块冲突、但请记住、无法处理 形如 like '%ooxx' 这来走索引
㈣ 降序索引
短评:谈谈这家伙的适用场景吧、存在一组列、有些降序、有些升序、咋办?比如:
order by emp_name desc,emp_id asc;
这时、您该笑了、小样儿得意洋洋的样子一看就是欠扁状态哦:
create index idx_emp on emp (emp_name desc,emp_id asc);
敲下去、准保你全身舒坦、比从足浴城出来打了一炮还爽
好像该说的都扯了、有点 bull shit 的样子哦、
再说啥、呀、还有哪"B*Tree 索引的访问方式"木有谈到哦、这个、、罪过
有 5 种、这里不详谈、那几个来对比一下、都说了、别人家的孩纸总是最好的嘛、不比不知道、
各位、各位、请注意、注意、第一组出场的将是:Index Full Scan vs Index Fast Full Scan 、我们欢迎:
比如有两个查询:
A:select emp_name from emp where emp_name is not null
B:select emp_name from emp where emp_name is not null order by emp_name、
毫无理由的、A采用Index Fast Full Scan 而B呢、当然是 Index Full Scan、为啥哩?别急哦、且听俺对比如下:
共同点:都把索引当瘦表来耍了
差异点:
IFS IFFS
单块读 多块读
有序 无序
db file sequential read db file scattered read (这两可从v$session_wait得出)
说完这两、接下来我们来谈谈 Index Unique Scan vs Index Range Scan
共同点:都是借助 rowid 回表
差异点:这 2条路径区别在于有木有对叶子节点进行扫瞄、显然、Index Unique Scan 是木有的
最后出场的、便是 Index Skip Scan、没啥好说的、就是注意、分段的数量严重影响着扫描的成本
到这里真没啥可扯谈的了、可就是憋也得憋出个结果来嘛、
好吧、俺承认了、俺废话多如泰山、可是重如鸿毛
什么时候使用 B*Tree 索引呢?网上有很多这方面经验、比如selectivity低的、比如OLTP、比如、、、
俺没啥经验、也没啥准则、更是没啥神功不可传阅、唯有家宝2 训:
① 仅当通过索引访问表中很少一部分行时、可用B*Tree
② 如果需要访问表中大部分行、且可用索引而不必用表时、可用B*Tree
坊间传闻、B*Tree 索引在应用开发阶段、常作"炮灰"状、而在维护时期、则是"马后炮"
即便身世坎坷常被冷眼相待、但到底是B*Tree、魅力无限、全身散发着令人 YY 的韵味、你比如:
如果存在主键或者唯一性约束、则不论是访问1w行、还是1亿行、这个CPU或IO的开销都相去无几呀
哇、、、美丽可爱大方如B*Tree、怎么能令人不爱呢、
说起 B*Tree 呀、俺的话就多了、可是话一多、又有些结巴、有些不知所措、有些不知所云
现就在混沌不知开窍的地方找点东东来小结一下、
B*Tree 索引都是唯一索引、如果你:
create index idx_t on t(col);
则Oracle内部会帮你:
create unique index idx_t on t(col,rowid)
SO、Oracle真是阿拉神灯呀、如果您不显示指定唯一的话、那么:
索引是按索引键值排序、然后、按rowid 升序排序哦
不过、要是您直接指定了唯一性索引、那就只有索引键值的排序鸟
有 2个参数蛮有可看性的:Blevel & Height
为啥有可看性嘞、那就看他活着的价值咯、
这两家伙纯粹就是为了衡量访问索引所需的I/O、这可从统计信息的:consistent gets 上看出
那么、哲学上有个问题是:你来自哪里?同问:这 2 货来自喵星球??
Blevel from user_indexes
Height from index_stats
Blevel+1、也就是Height哦
有地方要稍微注意下、收集信息的时候、
user_indexes 需调用 dbms_stats.gather_index_stats
index_stats 则要 analyze index index_name validate structure
嗯、嗯、那么一个表需要多少颗 B*Tree 才合理呀?
TOAD上有个健康巡检的工具、内建 6 颗为合理、超过要报警
但 6 到底合理吗?合理吗?毫无疑问、不合理、理由?自己想、哈哈
那、到底多少算合理哈、我也不知道哦、这个得看您的系统workload
这里推荐一个神功、欲练此功、必先、、、、、、、、
① 找出所有和该表相关的SQL
② 按 buffer gets 或physical read 排序、找出 TOP N
③ 找出 where 条件和连接条件
接下去、你都懂了、拿起小刀子、不、不、瑞士军刀、就这样、神功初成
来聊聊 B*Tree 的索引的类型吧、全部都讲也不现实哈、虽然俺也不懂、不过普通的B*Tree就是打死俺也不会说的
就举几个世界级、重量级的百闻不如一见、见面不如闻名的大BOSS
㈠ 函数索引
短评:任何系统、函数索引都是不可避免的、但你扪心自问下、您用了没?我敢打包票
没多少人有这个意识、要是有、你请我吃切糕、怎么样啦
㈢ 反转键索引
短评:能处理索引热块冲突、但请记住、无法处理 形如 like '%ooxx' 这来走索引
㈣ 降序索引
短评:谈谈这家伙的适用场景吧、存在一组列、有些降序、有些升序、咋办?比如:
order by emp_name desc,emp_id asc;
这时、您该笑了、小样儿得意洋洋的样子一看就是欠扁状态哦:
create index idx_emp on emp (emp_name desc,emp_id asc);
敲下去、准保你全身舒坦、比从足浴城出来打了一炮还爽
好像该说的都扯了、有点 bull shit 的样子哦、
再说啥、呀、还有哪"B*Tree 索引的访问方式"木有谈到哦、这个、、罪过
有 5 种、这里不详谈、那几个来对比一下、都说了、别人家的孩纸总是最好的嘛、不比不知道、
各位、各位、请注意、注意、第一组出场的将是:Index Full Scan vs Index Fast Full Scan 、我们欢迎:
比如有两个查询:
A:select emp_name from emp where emp_name is not null
B:select emp_name from emp where emp_name is not null order by emp_name、
毫无理由的、A采用Index Fast Full Scan 而B呢、当然是 Index Full Scan、为啥哩?别急哦、且听俺对比如下:
共同点:都把索引当瘦表来耍了
差异点:
IFS IFFS
单块读 多块读
有序 无序
db file sequential read db file scattered read (这两可从v$session_wait得出)
说完这两、接下来我们来谈谈 Index Unique Scan vs Index Range Scan
共同点:都是借助 rowid 回表
差异点:这 2条路径区别在于有木有对叶子节点进行扫瞄、显然、Index Unique Scan 是木有的
最后出场的、便是 Index Skip Scan、没啥好说的、就是注意、分段的数量严重影响着扫描的成本
到这里真没啥可扯谈的了、可就是憋也得憋出个结果来嘛、
好吧、俺承认了、俺废话多如泰山、可是重如鸿毛
什么时候使用 B*Tree 索引呢?网上有很多这方面经验、比如selectivity低的、比如OLTP、比如、、、
俺没啥经验、也没啥准则、更是没啥神功不可传阅、唯有家宝2 训:
① 仅当通过索引访问表中很少一部分行时、可用B*Tree
② 如果需要访问表中大部分行、且可用索引而不必用表时、可用B*Tree
不过、规则总有例外、小心驶得万年船、上线时多在测试机上跑、观察执行计划总是不会错的
By David Lin
2013-06-28
Good Luck