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

作者简介

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

案例说明

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

相关文章

来自专栏韩伟的专栏

面向对象的代码风格(上)

大家过年好呀!公众号从今天开始恢复更新,感谢大家不离不弃的关注。 今天的文末有投票,以助于我在新的一年里将公众号做得更好,踊跃参加一下吧! 本篇文章分两章发送,...

3618
来自专栏大数据钻研

如何设计优雅的类结构

注:正文中的引用是直接引用作者作者的话,两条横线中间的段落的是我自己的观点,其他大约都可以算是笔记了。 「Clean Code」这本书从这一章开始文风有些变化,...

2596
来自专栏顶级程序员

如何拿到半数面试公司Offer——我的Python求职之路

从八月底开始找工作,短短的一星期多一些,面试了9家公司,拿到5份Offer,可能是因为我所面试的公司都是些创业性的公司吧,不过还是感触良多,因为学习Pytho...

2925
来自专栏陈林峰的专栏

一个数据精度引发的血案

最近在做 X 项目的时候用到了弹性搜索引擎 ES(Elasticsearch),在检索遇到了一个诡异的问题,当存储(长)整型数据超过某个值(具体就是百万),就会...

3382
来自专栏java一日一条

如何拿到半数面试公司Offer——我的Python求职之路

从八月底开始找工作,短短的一星期多一些,面试了9家公司,拿到5份Offer,可能是因为我所面试的公司都是些创业性的公司吧,不过还是感触良多,因为学习Python...

441
来自专栏阿杜的世界

《重构》阅读笔记-代码的坏味道

开发者必须通过实践培养自己的经验和直觉,培养出自己的判断力:学会判断一个类内有多少个实例变量算是太大、学会判断一个函数内有多少行代码才算太长。

552
来自专栏平凡文摘

关于Java的10个误解

1174
来自专栏不想当开发的产品不是好测试

数据库索引问题

群上有一位同学咨询一个问题,两个查询语句,就一个limit 11, 一个limit 12,处理的效率相差巨大,如下图:

742
来自专栏崔庆才的专栏

MySQL 枚举类型的“八宗罪”

1464
来自专栏温安适的blog

你了解的工厂模式可能有误

我侃侃而谈道:“咱们公司,使用的设计模式不多,也就是 策略,门面,简单工厂,工厂,单例,命令,组合,模板等。”

1658

扫码关注云+社区