文档章节

索引让SQL举步维艰的秘密

问题终结者
 问题终结者
发布于 2017/08/20 12:03
字数 2543
阅读 8
收藏 2
点赞 0
评论 0
导读 在查询语句中,如果更新语句出现,索引的缺点将很明显。因为索引本身是有序的,而更新数据的过程中也要更新索引,更新完后还要保持索引的有序性,这就需要付出很大的开销了,索引不好的一面就从这里开始显示出来了。

任何事物都有它的两面性,索引也不例外,本文我们就来阐述一下索引的坏处。这里我们先简要综述一下索引的不足之处有哪些,而后详细阐述,随后一起和大家探索各种工作中的案例,最后为思考回顾。
总体学习思路如下图所示:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

一、索引的不足之处

关于索引的不足之处,我们可以从索引的开销和容易失效这两个方面来讨论,如下图所示:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

索引的各种开销

还记得前面关于索引结构的分析吗?通过系列步骤,我们明白了索引的结构,推导出索引的三大特性,并应用这些特性让SQL跑得更快。这只是索引好的一面。真正有问题的一面被掩盖了。那到底都有什么问题?

1.热块竞争

你看,索引最新的数据块一般是在最右边,而我们访问数据时正常来说也是访问比较新的数据,历史数据很少有人关注。然而问题来了,大家都一起访问最新的数据,不就都集中于同一个目标来访问了吗?这就很容易产生热块竞争。

2.回表开销

另外,大家都知道索引存储索引列的值和rowid,通过rowid来定位回到表中。其实这个回到表中的开销也是很大,具体情况我们随后可以了解到。

3.更新开销

索引的有序性是一个非常重要的特性,这个特性能够消除排序等开销,但是索引块要保持有序性,可不是一件容易的事。毕竟索引列的数据是随机插入的,比如你在原来的索引列中存储的是100、110、111等等时,现在要插入101,就应该在100和111之间插入,为了保证这个顺序索引需要做很多事,比如索引块分裂。而索引列的增删改的开销是很大的。

4.建立开销

还有千万别忽略了建立索引的开销,这也和索引的有序性有关。我们在建索引的过程中,首先把索引列的数据排序提取出来,再插入到块中形成索引块,这时如果数据不断地插入,排序提取这个动作什么时候能结束呢?所以还必须要锁表,这就是一个很大的开销(Online建索引是一种特殊的思路,这里不做描述)。当然建索引过程中排序这个动作本身也是一个不小的开销。

索引使用失效

索引的不足之处除了上述的几点外,从另一个维度看,还会有失效的可能。我们现在知道建索引对查询一般比较有利,对更新一般比较有害。不过有的时候,虽然建了索引,但其对查询毫无帮助,这种情况还是有的。比如索引失效了,这分为逻辑失效和物理失效两种。

1.逻辑失效

逻辑失效是索引本身并没有真正失效,只是由于写法的问题导致索引用不上,比如对SQL的条件列进行运算,类似select * from t where upper(name)=‘ABC’等,这时在name列上建了Btree索引是用不上的。再或者比如被人强制用了全表扫描的Hint等导致数据库被迫不用索引等。

2.物理失效

物理失效就是索引真的失效了,比如被人误设了unusable动作,或者是一些类似分区表的不规范操作导致的索引失效。对此后续有详细的例子说明。

二、感受美好索引另一面

前面简要描述了索引的不足之处,接下来我们进行更加详细的展开说明,具体细节如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

索引各种开销

1.访问集中导致热块的竞争

由于一般来说,最新的值都是最新产生的,所以访问它容易产生热块竞争。举例来说,如: select * from t where id=100000,select * from t where id=99999;select * from t where id=99998;select * from t where id=99997;这些数据很可能是相邻的,那么它们就会在同一个索引块上,这样很容易产生热点索引块竞争。

2.回表性能取决于聚合因子

结论:索引查询要尽可能避免回表,如果不可避免,则需要关注聚合因子是否过大。(注:这个例子在前面的章节已经说过了,这里就不再详述了。)在该例子中,构造脚本organized表的聚合因子比较小,回表的代价较低,产生了2900个buffer,如下:
索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密
而disorganized表的聚合因子比较大,回表的代价很高,如下,产生21360个buffer:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

3.索引更新方面的开销比较大

环境搭建:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

由上面代码可以看出,虽然t_small是小表,t_big是大表。但是插入一般不会随着记录的增加越插越慢。什么时候会越插越慢,就是当表有索引的时候。因为索引需要维护,越大维护越困难。我们继续做一组试验。

环境准备(建3张结构和记录都一样的表,只是索引分别是6个、2个及无索引):

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

分别往这三张表里插记录:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

表记录越大,索引越多,插入越慢,从试验结果来看,这一点还是非常明显的。

4.建索引的过程开销也很大

(1)建索引过程会产生全表锁
索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

(2)建索引过程会产生全表排序
未建索引前,观察一下数字字典中记录的系统排序情况,如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

建索引后,继续观察,发现排序次数sorts (memory)增加了,如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

索引逻辑失效

(1)用索引反而代价变得更高

这个道理比较简单,如果应用索引范围检索数据,返回大量记录且几乎是所有的记录,这时候用索引肯定有错,索引范围查询访问一般适合返回少量记录的情况,否则用全表扫描或者全索引扫描就可以。

(2)发生索引列的类型转换

在表字段设计的时候有一个非常重要的原则,什么类型的字段存什么类型的值,否则就会发生类型转化。

实际上只有如下写法才可以用到索引,这个很不应该,如果什么类型的取值就设置什么样的字段,把ID字段类型改为Number,就顺畅了,如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

(3)对索引进行了各种运算

对索引列进行了各种运算,详见后面的案例部分。

索引物理失效

(1)long列调整导致索引失效

环境准备(建表,建long字段):

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

接下来将long修改为clob,发现索引失效了,必须重建索引,如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

(2)move操作会导致索引失效

move是一个危险系数非常高的操作,虽然它可以收缩表降低高水平位,却会导致索引失效,因而需要重建索引。

(3)分区表导致索引失效的操作

这在前面已经描述过了,这里就不再重复了,请读者自行回到前面的章节进行复习总结。归纳如下:

truncate分区会导致全局索引失效,不会导致局部索引失效。如果对truncate 增加update global indexes,则全局索引不会失效。

drop分区会导致全局索引失效,局部索引因为drop分区,所以也不存在该分区的局部索引了。如果对drop分区增加update global indexes,全局索引不会失效。

split分区会导致全局索引失效,也会导致局部索引失效。如果对split分区增加update global indexes,则全局索引不会失效。

add 分区不会导致全局索引失效,也不会导致局部索引失效。

exchange会导致全局索引失效,不会导致局部索引失效。如果对exchange分区增加update global indexes,则全局索引不会失效。

重要结论:

所有的全局索引,只要用到update global indexes ,都不会失效,其中add分区甚至不需要增加update global indexes都可以生效。

局部索引的操作都不会失效,除了split分区。切记split分区的时候,要将局部索引进行rebuild。

索引取舍控制

1.避免表交叉重复建立索引

假如t表有nbr、area_code两列的联合索引,单列的nbr索引就显得多余,因为nbr、area_code索引可以用在单列nbr索引上,具体如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

2.删除系统从未用到的索引

环境搭建,建表建索引并完成某列索引的监控:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

接下来继续执行一个用索引的查询,然后再观察Used字段,发现索引被用过:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

停止对索引的监控,观察v$object_usage状态变化,发现MONITORING的值为NO,且END_MONITORING记录了停止监控的时间,如下:

索引让SQL举步维艰的秘密索引让SQL举步维艰的秘密

3.组合列过多的索引很可疑

组合索引一般不宜过多,如果组合索引列达到4个以上,那这个索引本身就很大,就不一定高效。另外,索引更新也会出现比较大的性能问题。

原文来自:http://www.yunweipai.com/archives/18826.html

本文地址:http://www.linuxprobe.com/index-to-sql.html

© 著作权归作者所有

共有 人打赏支持
问题终结者
粉丝 9
博文 572
码字总数 68489
作品 0
运维
GitHub 正寻求 2 亿美元B轮融资

北京时间6月16日早间消息,据彭博社报道,相关消息人士透露,著名代码管理网站GitHub正在寻求新一轮2亿美元的秘密融资。在完成B轮融资以后,公司估值将达到20亿美元。 GitHub成立于2008年,在...

主编 ⋅ 2015/06/16 ⋅ 41

揭开索引让SQL举步维艰的另一面

作者介绍 任何事物都有它的两面性,索引也不例外,本文我们就来阐述一下索引的坏处。 大家可能没注意到,在查询语句中,如果更新语句出现,索引的缺点将很明显。因为索引本身是有序的,而更新...

梁敬彬 ⋅ 2017/05/31 ⋅ 0

Oracle专家调优秘密

在过去的十年中,Oracle已经成为世界上最专业的数据库之一。对于IT专家来说,就是要确保利用Oracle的强大特性来提高他们公司的生产力。最有效的方法之一是通过Oracle调优。它有大量的调整参数...

晨曦之光 ⋅ 2012/03/09 ⋅ 0

价值100亿美金的胸罩生意

翻开维多利亚的秘密母公司The Limited的财报,2015年销售额是121亿美金,净利润是12亿美金,截至目前,维多利亚的秘密在全球有超过2000间店铺,其中70%在美国,加拿大和英国。 这样一家专卖女...

挖数 ⋅ 2017/11/25 ⋅ 0

Sql server Insert执行的秘密(下) 带外键的INSERT分析

上一篇文章介绍了一个最简单INSERT语句的执行计划详细情况,这一篇分析一下带外键表的INSERT的例子。 本文所用的数据表结构如上图所示;其中Blog表上BlogID是自增的主键,并在CreateUserID和...

长平狐 ⋅ 2012/06/08 ⋅ 0

揭露QPS增高后的秘密

这种方式降低了系统的请求量,但是降低了系统的QPS吗?这种做法系统更安全了还是更危险了? 首先来介绍一下基本概念。 1 .性能的关键指标 系统吞吐量(Throughput) 吞吐量指单位时间内系统处理的...

linuxprobe ⋅ 2016/09/16 ⋅ 0

sql多表查询之一:Where 和 On的秘密

今天给大家转载一篇《sql多表查询之一:Where 和 On的秘密》,对于还在SQL初级阶段的朋友来说,sql多表查询问题是一个比较有趣也容易出错的技术。什么时候会用到sql多表查询呢?是在两张或两...

oecp ⋅ 2011/07/27 ⋅ 1

Sql server Insert执行的秘密(上)一个最简单的INSERT分析

本文使用的是Sql server 2005 准备工作: 我需要一张User表,这张表有几个字段,还有一个自增长的数字id,表结构如下: 这是一张比现实应用中简单的多的用户表,UserID是自增长字段。 开始执...

长平狐 ⋅ 2012/06/08 ⋅ 0

谁动了我的索引(三)

环境介绍: SQL> desc t1; Name Null? Type ----------------------------------------- -------- ---------------------------- NN NOT NULL NUMBER CNN NUMBER R ROWID SQL> select count(......

日久不生情 ⋅ 2017/11/07 ⋅ 0

文件管理软件的功能

① 集中存储,统一的文档共享 ② 权限管理,可针对用户、部门及岗位进行细粒度的权限控制,控制用户的管理、浏览、阅读、编辑、下载、删除、打印、订阅等操作 ③ 全文索引,可以索引Office、...

申马 ⋅ 2014/06/11 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Redis 单线程 为何却需要事务处理并发问题

Redis是单线程处理,也就是命令会顺序执行。那么为什么会存在并发问题呢? 个人理解是,虽然redis是单线程,但是可以同时有多个客户端访问,每个客户端会有 一个线程。客户端访问之间存在竞争...

码代码的小司机 ⋅ 48分钟前 ⋅ 0

到底会改名吗?微软GVFS 改名之争

微软去年透露了 Git Virtual File System(GVFS)项目,GVFS 是 Git 版本控制系统的一个开源插件,允许 Git 处理 TB 规模的代码库,比如 270 GB 的 Windows 代码库。该项目公布之初就引发了争...

linux-tao ⋅ 58分钟前 ⋅ 0

笔试题之Java基础部分【简】【二】

1.静态变量和实例变量的区别 在语法定义上的区别:静态变量前要加static关键字,而实例变量前则不加。在程序运行时的区别:实例变量属于某个对象的属性,必须创建了实例对象,其中的实例变...

anlve ⋅ 今天 ⋅ 0

Lombok简单介绍及使用

官网 通过简单注解来精简代码达到消除冗长代码的目的 优点 提高编程效率 使代码更简洁 消除冗长代码 避免修改字段名字时忘记修改方法名 4.idea中安装lombnok pom.xml引入 <dependency> <grou...

to_ln ⋅ 今天 ⋅ 0

【转】JS浮点数运算Bug的解决办法

37.5*5.5=206.08 (JS算出来是这样的一个结果,我四舍五入取两位小数) 我先怀疑是四舍五入的问题,就直接用JS算了一个结果为:206.08499999999998 怎么会这样,两个只有一位小数的数字相乘,怎...

NickSoki ⋅ 今天 ⋅ 0

table eg

user_id user_name full_name 1 zhangsan 张三 2 lisi 李四 `` ™ [========] 2018-06-18 09:42:06 星期一½ gdsgagagagdsgasgagadsgdasgagsa...

qwfys ⋅ 今天 ⋅ 0

一个有趣的Java问题

先来看看源码: public class TestDemo { public static void main(String[] args) { Integer a = 10; Integer b = 20; swap(a, b); System.out......

linxyz ⋅ 今天 ⋅ 0

十五周二次课

十五周二次课 17.1mysql主从介绍 17.2准备工作 17.3配置主 17.4配置从 17.5测试主从同步 17.1mysql主从介绍 MySQL主从介绍 MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主...

河图再现 ⋅ 今天 ⋅ 0

docker安装snmp rrdtool环境

以Ubuntu16:04作为基础版本 docker pull ubuntu:16.04 启动一个容器 docker run -d -i -t --name flow_mete ubuntu:16.04 bash 进入容器 docker exec -it flow_mete bash cd ~ 安装基本软件 ......

messud4312 ⋅ 今天 ⋅ 0

OSChina 周一乱弹 —— 快别开心了,你还没有女友呢。

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @莱布妮子 :分享吴彤的单曲《好春光》 《好春光》- 吴彤 手机党少年们想听歌,请使劲儿戳(这里) @clouddyy :小萝莉街上乱跑,误把我认错成...

小小编辑 ⋅ 今天 ⋅ 9

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部