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

作者简介

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

案例说明

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

相关文章

来自专栏丑胖侠

《Drools 7 规则引擎教程》番外篇-规则条件匹配机制

问题场景 QQ技术群(593177274)中有同学遇到这一个问题场景:在多条规则(比如3条)的when中使用了一个相同的静态方法,并在静态方法中打印相关的日志信...

2038
来自专栏Golang语言社区

【go语言】Goroutines 并发模式(二)

摘要 接上一篇博客,主要是对GO语言中的并发编程模式做一个粗略的归纳总结,文中示例参考自golang conference中的一些演讲和博客,go涉及到的Go语...

2213
来自专栏王亚昌的专栏

采用epoll模型服务器连接管理器实现

    在网络服务器中,需要维护所有连接信息,通常是以fd做为key,连接信息结构体做为value。每次有新连接接入时,需求加入一个映射关系;每次有新数据到达时...

721
来自专栏BeJavaGod

Zookeeper 分布式系统企业级开发实战与应用(2)- 分布式系统单点故障的解决方案(分布式锁)

1.分布式系统中的单点故障:分布式系统通常都会使用主从模式,就是一个主节点计算机连接多个处理从节点。主节点负责分发任务,从节点负责处理任务,但是一旦主节点宕机时...

3165
来自专栏灯塔大数据

每周学点大数据 | No.21磁盘算法概述

No.21期 磁盘算法概述 Mr. 王:现在我们谈谈磁盘算法的问题。根据你的了解,跟我说说计算机中都采用了哪些种类的存储器? 小可:这个我还是略知一二...

2647
来自专栏iOS开发攻城狮的集散地

堆和栈

1385
来自专栏三丰SanFeng

Linux Kernel CMPXCHG函数分析

最近看到Linux Kernel cmpxchg的代码,对实现很不理解。上网查了内嵌汇编以及Intel开发文档,才慢慢理解了,记录下来以享和我一样困惑的开发者。...

27210
来自专栏技术分享

Redis 数据结构与内存管理策略(上)

Redis 数据结构与内存管理策略(上) 标签: Redis Redis数据结构 Redis内存管理策略 Redis数据类型 Redis类型映射 作者:王清培(...

2747
来自专栏java一日一条

关于 Java 的10个谎言

下面的这些都算是比较高级的问题了,面试中一般也很少问到,因为它们可能会把面试者拒之门外。不过你可以自己找个时间来实践一下。

331
来自专栏编程

浅谈Java学习方法和后期面试技巧 含学习笔记

下面简单列举一下大家学习java的一个系统知识点的一些介绍: 一、java基础部分:java基础的时候,有些知识点是非常重要的,比如循环系列。For,while...

1798

扫描关注云+社区