聊聊一个糟糕的数据库架构设计带来的问题。
技术人人都可以磨炼,但处理问题的思路和角度各有不同,希望这篇文章可以抛砖引玉。
以一个例子为切入点
一、问题背景
某系统已经线上运行多年,数据量随着时间的推移越来越大。公司业务量还在不断增加,已经潜在威胁数据库的运行效率,急需清理历史数据。
基础环境:
-
主机类型:云环境
-
操作系统: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 t1
AS
SELECT *
FROM dba_objects
WHERE 1 = 0;
ALTER TABLE t1
ADD (id int PRIMARY KEY);
CREATE TABLE t2
AS
SELECT *
FROM dba_objects
WHERE 1 = 0;
ALTER TABLE t2
ADD (id varchar2(10) PRIMARY KEY);
INSERT INTO t1
SELECT 'test', 'test', 'test', rownum, rownum
, 'test', SYSDATE, SYSDATE, 'test', 'test'
, NULL, NULL, NULL,NULL, NULL, rownum
FROM dual
CONNECT BY rownum <= 3000000;
INSERT INTO t2
SELECT 'test', 'test', 'test', rownum, rownum
, 'test', SYSDATE, SYSDATE, 'test', 'test'
, NULL, NULL, NULL,NULL, NULL, rownum
FROM dual
CONNECT 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、测试
相关代码如下:
select * from t1 where id>= 2999990;
11 rows selected.
--------------------------------------------------------------------------------
| Id | Operation | Name |Rows |Bytes|Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 690 | 4 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 11 | 690 | 4 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN |SYS_C0025294| 11 | | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
对于数值类型的字段,范围查询就是正常的索引范围扫描。
select * from t2 where id>= '2999990';
777788 rows selected.
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2417K| 140M| 8920 (2)| 00:01:48 |
|* 1 | TABLE ACCESS FULL| T2 | 2417K| 140M| 8920 (2)| 00:01:48 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
82568 consistent gets
0 physical reads
对于字符串类型字段的表,范围查询就是全表扫描。
测试结果符合预期。
4、原因探究
“select * from t2 where id>= '3199990'”执行返回777788条记录,不是直观上的10条记录,这是因为字符串类型的排序方式与我们的预期不同,字符类型在索引中是“乱序”的。
这也是当初在做表设计时,开发人员没有注意的问题。
字符类型还导致了聚簇因子很大,原因是插入顺序与排序顺序不同。详细点说,就是按照数字类型插入(1..3000000),按字符类型('1'...'30000000')排序。
select table_name,index_name,leaf_blocks,num_rows,clustering_factor
from user_indexes
where table_name in ('T1','T2');
TABLE_NAME INDEX_NAME LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
-------------- -------------- ---------------- ---------- ---------------------
T1 SYS_C0025294 6275 3000000 31520
T2 SYS_C0025295 13271 3000000 632615
在对字符类型使用大于运算符时,会导致优化器认为需要扫描索引大部分数据且聚簇因子很大,最终导致弃用索引扫描而改用全表扫描方式。
5、有没有其他解决方案
只考虑查询数据正确的话是有其他解决方案的,具体的解决方法如下:
select * from t2 where id between '2999990' and '3000000';
--------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes |Cost(%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6| 390 | 5 (0)|00:00:01|
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 6| 390 | 5 (0)|00:00:01|
|* 2 | INDEX RANGE SCAN | SYS_C0025295 | 6| | 3 (0)|00:00:01|
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
0 physical reads
将SQL语句由开放区间扫描(>=),修改为封闭区间(between xxx and max_value)。使得数据在索引局部顺序是“对的”。
不过采用这种方式仍然走全表扫描。
四、总结
这是一个典型的因为字段类型问题带来的执行计划异常的例子。
它给我们带来如下启示:
-
糟糕的数据结构设计往往是致命的,后期的优化只是补救措施。只有从源头上加以杜绝,才是优化的根本。
-
在设计初期能引入数据库审核,可以起到很好的作用。
更多精彩内容,关注公号“数据与人”