深入剖析:优化,要从根源开始

作者简介

今天与大家分享一则表结构设置不合理引发故障的案例。

案例说明

这是某公司后台的ERP系统,系统已经上线运行了10多年。随着时间的推移,累积的数据量越来越大。随着公司业务量的不断增加,数据库系统运行缓慢的问题日益凸显。为提高运行效率,公司计划有针对性地对部分大表进行数据清理。在DBA对某个大表进行清理时出现了问题。这个表本身有数百GB,按照指定的清理规则只需要根据主键字段范围(运算符为>=)选择出一定比例(不超过10%)的数据进行清理即可。但在实际使用中发现,该SQL是全表扫描,执行时间大大超出预期时间。DBA尝试使用强制指定索引方式清理数据,依然无效,整个SQL语句的执行效率达不到要求。

按照正常理解,执行索引范围扫描应该是效率较高的一种处理方式,但实际情况都是全表扫描。进一步分析发现,该表的主键是没有业务含义的,仅仅是自增长的数据,其来源是一个序列。但奇怪的是,这个主键字段的类型是变长文本类型,而不是通常的数字类型。当初定义该字段类型的依据,现在已经无从考证,但实验表明正是这个字段的类型“异常”,导致了错误的执行路径。

下面通过一个实验重现这个问题。

1、数据准备

两个表的数据类型相似(只是ID字段类型不同),各插入了320万数据,ID字段范围为1~3200000。

2、模拟场景

相关代码如下:

对于普通的采用数值类型的字段,范围查询就是正常的索引范围扫描,执行效率很高。

对于文本类型字段的表,范围查询就是对应的全表扫描,效率较低是显而易见的。

3、分析结论

字符类型在索引中是“乱序”的,这是因为字符类型的排序方式与我们的预期不同。从“select * from t2 where id>= '3199990'”执行返回755 565条记录可见,不是直观上的10条记录。这也是当初在做表设计时,开发人员没有注意的问题。

字符类型还导致了聚簇因子很大,原因是插入顺序与排序顺序不同。详细点说,就是按照数字类型插入(1..3200000),按字符类型('1'...'32000000')t排序。

在对字符类型使用大于运算符时,会导致优化器认为需要扫描索引大部分数据且聚簇因子很大,最终导致弃用索引扫描而改用全表扫描方式。

4、解决方法

将SQL语句由开放区间扫描(>=),修改为封闭区间(between xxx and max_value)。使得数据在索引局部顺序是“对的”。如果采用这种方式仍然走索引扫描,还可以进一步细化分段或者采用“逐条提取+批绑定”的方法。

案例启示

这是一个典型的由不好的数据类型带来的执行计划异常的例子。它给我们带来如下启示:

糟糕的数据结构设计往往是致命的,后期的优化只是补救措施。如果从源头上加以杜绝,这才是优化的根本。在设计初期能引入数据库审核,可以起到很好的作用。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2016-10-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据文摘

史上最强算法论战:请不要嘻哈,这是哈希

2126
来自专栏数据和云

Oracle构造序列的方法分析对比

编辑手记:关于Oracle的序列,相信大家并不陌生,但很多人平时只用到connect by 的方式来构造序列,今天一起来学习更多的构造序列的方法及每个方法的优缺...

3437
来自专栏木子昭的博客

用chrome学编程利用chrome学编程,一切都变得简单...

利用chrome学编程,一切都变得简单... 用chrome学编程,安装浏览器就可以上手,javascript本身也足够强大,前后端通吃,容易出成果,有了成果...

4287
来自专栏编程一生

看Lucene源码必须知道的基本概念

1076
来自专栏Java呓语

权限之数据权限概念原型实现抽象

随着业务的演进,逐渐衍生出精细化管理数据的诉求。我遇到的业务场景是在企业级数据管理中,对不同职级的员工展示不同的数据。我的业务上的诉求是对SELECT进行权限控...

992
来自专栏北京马哥教育

shell十三问,为linux学习打基础(三)

本文整理并转自CU上的帖子[学习共享] shell 十三問?,此贴是2003年发表的,但却是相当不错的linux基础知识汇集贴,原帖主使用的台湾风格,本文加以简...

3586
来自专栏python3

python3--python的出生与应用

python的创始人为吉多·范罗苏姆(Guido van Rossum)。1989年的圣诞节期间,吉多·范罗苏姆(中文名字:龟叔)为了在阿姆斯特丹打发时间,决心...

802
来自专栏Java技术

动态代理之投鞭断流!看一下MyBatis的底层实现原理!

一日小区漫步,我问朋友:Mybatis中声明一个interface接口,没有编写任何实现类,Mybatis就能返回接口实例,并调用接口方法返回数据库数据,你知道...

512
来自专栏Java学习网

想成为优秀程序员必知的要点

想成为优秀程序员必知的要点 1.永远不要重复代码 不惜一切代价也要避免重复代码。如果你有几个不同的地方经常性地要使用某个代码片段,那么可以将它重构成函数。代码...

2137
来自专栏IT笔记

JAVA工作三年面试(一)

博主毕业快满三年了,由于种种原因想换一份工作。学了 7 年计算机技术,在目前的行业用到的技术不多,还是想去寻找一份互联网行业的工作,这样更能学以致用发挥自己的特...

7558

扫码关注云+社区