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

作者简介

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

案例说明

这是某公司后台的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 条评论
登录 后参与评论

相关文章

来自专栏python3

python3--python的出生与应用

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

1112
来自专栏我的博客

领域专用语言(DSL)和通用编程语言(GPL)

DSL解释 领域专用语言(Domain Specific Language/DSL) DSL 通过在表达能力上做的妥协换取在某一领域内的高效(世界级软件开发...

39313
来自专栏大数据文摘

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

2336
来自专栏IT笔记

JAVA工作三年面试(一)

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

1.3K8
来自专栏圆方圆学院精选

【许晓笛】EOS 数据库与持久化 API —— 实战

上次的文章详细讲解了 EOS 数据库的架构,本文将以官方示例为基础,详解 EOS 数据库的开发实战。

883
来自专栏大数据

No.67 Hadoop 实践案例——记录去重

转载声明 本文为灯塔大数据原创内容,欢迎个人转载至朋友圈,其他机构转载请在文章开头标注:转自:灯塔大数据;微信:DTbigdata 编者按:灯塔大数据将每周持续...

1938
来自专栏微信公众号:Java团长

Java基础05 实施接口

在Java基础04 封装与接口中,private关键字封装了对象的内部成员。经过封装,产品隐藏了内部细节,只提供给用户接口(interface)。

732
来自专栏架构师之路

深入浅出搜索架构引擎、方案与细节(上)

一、缘起 《100亿数据1万属性数据架构设计》文章发布后,不少朋友对58同城自研搜索引擎E-search比较感兴趣,故专门撰文体系化的聊聊搜索引擎,从宏观到细节...

6395
来自专栏令仔很忙

Spring从入门到精通(一)----IoC(控制反转)

在采用面向对象方法设计的软件系统中,它的底层实现都是由N个对象组成的,所有的对象通过相互合作,最终实现系统的业务逻辑。

932
来自专栏互联网开发者交流社区

SQL基础用法(实例一)

1533

扫码关注云+社区