eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 数据安全:独立发布的Oracle严重 CVE-2018-3110 公告 | Blog首页 | 云盾安全管控平台,企业最贴心的数据库管家 »

一道面试题引出的系列数据库性能,数据安全问题及解决方案

友情链接:http://enmotech.com/web/classify/43.html

http://enmotech.com/web/classify/28.html

事件背景:

SELECT * FROM girls WHERE age BETWEEN 18 and 24 and boyfriend='no';

上周在朋友圈看到一张照片,随手转发并且提出了一个问题。

面试题一枚可好:请问以下SQL有什么可能的逻辑问题、语法问题、性能问题,可以怎样进行索引优化、逻辑优化以提升性能?

SELECT.jpg

这个问题在朋友圈引起了很多朋友的兴趣,转发并且提问,希望有一个标准答案输出作为参考。

标准答案没有,但是我可以说说我的看法,当然前提是『这个SQL中的一切都是可以质疑的』。管中窥豹,博方家一笑。

开发规范

首先从开发规范上来讲,『SELECT *』一般不是最佳实践,因为你不清楚这个表中有多少个字段(Column),这样的输出是无法格式化和预期的,其输出结果可能也不全部是你需要的,所以尽量明确定义你需要的字段名。

那么第二个问题来了,『SELECT *』和 『SELECT col1,col2 ..』,除了语义上,还有什么区别?

你要知道的是,当做出这样的改写,这条SQL的长度增加,SQLID改变,占用的Shared Pool共享内存也较以前增加了。进行数据字典的列验证增多,消耗也会增加,也就是CPU占用会增加(当然这可能是微微的改变);

其次,SQL语句中的,age 和 boyfriend 字段查询,都没有使用绑定变量,这对于不同查询,如 between 20 and 24,无法共享SQL,会进一步导致硬解析上的高CPU消耗,更多的Shared Pool共享内存的占用;

再次,当 age 和 boyfriend 查询不适用绑定变量时,可能为数据库带来安全注入的风险;

最后,根据代码格式规范,BETWEEN 之后的 and 也应该以大写 AND 形式出现。

这样的开发规范有助于大家改善代码质量,提高效率,规避风险,可是如果都靠DBA手工去做,显然难度太大,云和恩墨的SQM - SQL质量管控平台,可以自动的帮助我们审核SQL,管控SQL规范,详情请参阅:SQL审核,提升开发规范和性能

SE.2.jpg

数据结构和元数据

我们再从数据结构、数据模型和元数据的角度来思考一下,我相信大家见识过各种各样的数据结构命名法,我见过的远远超出想象,各种各样诡异的命名会坑到我们怀疑人生。

所以,对于以上的SQL查询,你一定要确定 girls 这个表名的真实定义,是否和你想想的相符合,难道你就笃定这里是『对面的女孩』大本营?

其次,很少有数据库里存在 age 字段的设计,因为那会是一个不断改变的事实,更合理的做法是存储『出生日期』,这是基本数据模型设计的守则。

再次,boyfriend 字段,你需要确认这里存储的确定是你期望的含义『男朋友』,如果是,并且存储的数据是 Yes | No,如果你不确定其大小写和格式规范,是应该用 upper 或者 lower 去对一端进行转换,否则你的查询可能无法顺利匹配;

最后,为什么我们户籍重要的登记信息是『婚姻状况』?因为 boyfriend 的状态可能极不稳定,也可能不是1:1的关系,更加该字段需要频繁变更,可能很难以维持一致性。

并且,如果这个字段存在,应该以 0 | 1 标识,可以极大减少存储的占用,并且提高查询效率。

当然,很多朋友说,应当去掉 boyfriend 这个限定条件,因为一旦加上这个约束,你可能面对的情况是 no rows return 的尴尬局面。

关于这个条件,有朋友给了这样几个建议:

  1. 去掉 boyfriend ='no' ,你懂的 (这位是老司机)

  2. boyfriend 的名字叫 no 就尴尬了(这个直追 Null 那个梗)

  3. 有朋友还要加几个条件 and cost < 2000 RMB and beautiful > 85分 and height > 165 order by cost,beautiful desc,height desc (我不得不表示,孩子,你还是嫩啊);

  4. 用 boyfriend is NULL (这是技术流)

那么如何去检查核定数据字典的信息,如何确保SQL的性能,及时发现和解决问题?云和恩墨的自动化巡检诊断平台 - Bethune 正可以帮助你自动发现数据库中安全隐患,参考:免费的白求恩自动巡检平台助你数据库平安

select.4.jpg

关于性能

我们再来讨论一下性能。

首先,在这个SQL中,Between AND 可以进行优化改写,between 18 and 24,最简单的有两种改写方法:

  1. 将 between and 改写成 >= 和 <= ,这会减少Oracle自己的转换,同时减少了SQL字符长度,缩减了网络传输,Shared Pool占用;

  2. 可以将 between and 改写成 IN (18,19 .. 24 ),数据库会将 In 值列表转换成几个等值比较,然后 CONCATENATION,其成本通常更低;

其次,这个表可能不是很小,而是很大,因为可能 boys 已经被分离出去,所以这个表应当考虑分区,辅助其他条件,通过分区剪裁快速缩减查询结果。

所以有朋友建议的SQL是这个样子的:

SELECT * FROM girls

WHERE (age between 18 and 24) and NOT EXISTS (SELECT * FROM boys WHERE girls.boyfriend_id = boys.boyfriend_id);

再次,有朋友建议的索引优化:对 age 加 bitmap 索引,开并行。

注意,bitmap 对于这个频繁变更的表不是一个好选择,鉴于我们的判断,boyfriend = 'no' 的记录数极少,那么关于 age + boyfriend 的复合索引就能快速的找到记录,如果你是乐观主义者,就加个 rownum 的限制,如果你是一个悲观主义者,那么就可以去掉boyfriend字段,然后加个 rownum 的限制。

最后,其实我们很容易发现对于age和boyfriend的存储,行存不如列式存储,如果使用Oracle 12c的IN-Memory,在内存中进行列式压缩,可以极大的提高查询性能。

select.5.jpg

如果您非常关注数据库的性能,那么云和恩墨的数据库性能监控平台,将会让您对数据库的性能一目了然,及时预警。参考:洞若观火,ZONE助力性能提升

select.6.jpg

可是注意,以上我们的推断,是基于Oracle数据库的考虑,如果数据库是 MySQL、Redis,或是其他产品,该如何去调整和优化呢?

如果是MySQL,是否数据量相当庞大之后,如何分库分表?

那么多数据库,那么多管理、监控和优化工作,你一定要关注一下云和恩墨最新推出的 zCloud 云管平台,让多云、多数据管理、优化,融为一体,何以解多数据库管理之忧?唯有zCloud。

select.7.jpg

关于隐私和安全

如果你以为就只有这些?

select.8.jpg

那么我还可以告诉你,如果完成以上查询,并且碰巧获得了输出,那么你可能已经违反了欧盟的GDPR法案,涉嫌侵犯了用户隐私(哈哈哈哈哈哈,开心吗)。

在2018年5月25日正式生效的 GDPR 法案,对于用户的隐私做出了严格的界定,如果侵犯滥用用户隐私信息,将遭遇高达2000万欧元或企业年营业额4%的高额惩罚,注意最后一条保护的隐私内容:

公民基本的身份信息,如姓名、地址和身份证号等;

网络数据,如位置、IP地址、Cookie数据和RFID标签等;

医疗保健 和 遗传数据;

生物识别数据,如指纹、虹膜等;

种族或民族数据;

政治观点;

性取向;

参考前文:GDPR 法案带来的思考。所以对于企业数据的管理者,如何保护数据安全,确保用户隐私不被任意访问,不被DBA不授权访问,都是一个值得重视的问题,云和恩墨已经提供针对 GDPR 的安全增强解决方案,欢迎垂询。

千言万语,千头万绪,汇成最后的答案:这条SQL最终不应该被执行,也不会有返回结果。

select.9.jpg

加油吧,少年!


历史上的今天...
    >> 2015-08-27文章:
    >> 2013-08-27文章:
    >> 2010-08-27文章:
           西藏记行 - 圣湖纳木措
    >> 2008-08-27文章:
    >> 2007-08-27文章:
    >> 2005-08-27文章:
           升级MT到3.2正式版

By eygle on 2018-08-27 11:58 | Comments (0) | Activity | Case | Internal | Jobs | SQL.PLSQL | 3307 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com