一个糟糕的数据库架构设计优化案例

聊聊一个糟糕的数据库架构设计带来的问题。

技术人人都可以磨炼，但处理问题的思路和角度各有不同，希望这篇文章可以抛砖引玉。

以一个例子为切入点

一、问题背景

某系统已经线上运行多年，数据量随着时间的推移越来越大。公司业务量还在不断增加，已经潜在威胁数据库的运行效率，急需清理历史数据。

基础环境：

• 主机类型：云环境 
• 操作系统：CentOS release 7.8
• 存储：EMC
• 内存：128 G
• CPU型号：Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz ( 1 U * 8 core) 
• CPU核数：32CORE
• 数据库环境：11.2.0.4

问题现象：对某个百G大表进行清理时出现了问题。

简单说明：在很多应用场景中，SQL 的性能直接决定了系统的性能。此外，查询速度慢并不只是因为 SQL 语句本身，还可能是因为内存分配不佳、文件结构不合理、优化器判断异常等其他原因。

本文介绍一些通过调整 SQL 语句就能优化SQL的通用小技巧，优化 SQL 的方法不能解决所有的性能问题，但是却能处理很多因 SQL 写法不合理而产生的性能问题。

二、分析说明

• 通过分析定位问题，分析问题原因；
• 追溯历史数据，分析关键指标，这些关键指标可以用来做为参考指标。
• 用实际数据来验证推断，排除掉其它干扰因素，定位问题的根本原因，帮助快速修复。

三、疑问点排查及分析思路

1、分析说明：

这张表虽然比较大但并非分区表，最初的计划是按照主键字段的范围（运算符>=）进行清理。

但在实际操作中发现，涉及该表的SQL是全表扫描，尝试使用强制指定索引方式依然无效，SQL语句的执行效率达不到要求。

正常情况下应该走索引的，但实际情况都是全表扫描（有点头大）。

进一步分析发现，该表的主键是没有业务含义，数据来源依赖一个序列，分析到这里都是正常的。

关键问题在于，这个主键字段的类型是字符串类型，而不是通常的数字类型。

当初为什么这么定义该字段类型已无法求证，但结果表明正是这个字段的类型“异常”，导致了错误的执行计划。

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

2、准备数据

T1/T2两个表的数据类型相似，ID字段类型不同，各插入了300万数据，ID字段范围为1~3000000。

CREATE TABLE t1ASSELECT *FROM dba_objectsWHERE 1 = 0;
ALTER TABLE t1 ADD (id int PRIMARY KEY); CREATE TABLE t2ASSELECT *FROM dba_objectsWHERE 1 = 0;
ALTER TABLE t2 ADD (id varchar2(10) PRIMARY KEY);
INSERT INTO t1SELECT 'test', 'test', 'test', rownum, rownum , 'test', SYSDATE, SYSDATE, 'test', 'test' , NULL, NULL, NULL,NULL, NULL, rownumFROM dualCONNECT BY rownum <= 3000000;
INSERT INTO t2SELECT 'test', 'test', 'test', rownum, rownum , 'test', SYSDATE, SYSDATE, 'test', 'test' , NULL, NULL, NULL,NULL, NULL, rownumFROM dualCONNECT BY rownum <= 3000000;
COMMIT;
execdbms_stats.gather_table_stats(ownname => 'test', tabname => 't1', cascade => true, estimate_percent => 100);execdbms_stats.gather_table_stats(ownname => 'test', tabname => 't2', cascade => true, estimate_percent => 100);

3、测试

相关代码如下：

对于数值类型的字段，范围查询就是正常的索引范围扫描。

对于字符串类型字段的表，范围查询就是全表扫描。

测试结果符合预期。

4、原因探究

“select * from t2 where id>= ‘2999990’”执行返回777788条记录，不是直观上的10条记录，这是因为字符串类型的排序方式与我们的预期不同，字符类型在索引中是“乱序”的。

这也是当初在做表设计时，开发人员没有注意的问题。

字符类型还导致了聚簇因子很大，原因是插入顺序与排序顺序不同。详细点说，就是按照数字类型插入（1..3000000），按字符类型（’1’…’30000000’）排序。

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

5、有没有其他解决方案

只考虑查询数据正确的话是有其他解决方案的，具体的解决方法如下：

将SQL语句由开放区间扫描（>=），修改为封闭区间（between xxx and max_value）。使得数据在索引局部顺序是“对的”。

不过采用这种方式仍然走全表扫描。

四、总结

这是一个典型的因为字段类型问题带来的执行计划异常的例子。

它给我们带来如下启示：

糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上加以杜绝，才是优化的根本。

在设计初期能引入数据库审核，可以起到很好的作用。