千头万绪:从一道面试题看数据库性能和安全的方方面面

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

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

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

标准答案没有,但是我可以说说我的看法,当然前提是『这个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审核,提升开发规范和性能

数据结构和元数据

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

所以,对于以上的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 正可以帮助你自动发现数据库中安全隐患,参考:免费的白求恩自动巡检平台助你数据库平安

关于性能

我们再来讨论一下性能。

首先,在这个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,在内存中进行列式压缩,可以极大的提高查询性能。

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

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

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

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

关于隐私和安全

如果你以为就只有这些?

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

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

公民基本的身份信息,如姓名、地址和身份证号等; 网络数据,如位置、IP地址、Cookie数据和RFID标签等; 医疗保健 和 遗传数据; 生物识别数据,如指纹、虹膜等; 种族或民族数据; 政治观点; 性取向;

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

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

加油吧,少年!

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-08-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

学习Oracle和MySQL推荐的几本书

已经很多次收到后台网友的留言或者私信了,对于学习Oracle和MySQL,他们都有类似的疑问,我就索性放在一起来回答下。 简单来说,官方文档是最好的...

6196
来自专栏idba

数据库系统中的“黑天鹅”

一 前言 纳西姆.尼古拉斯.塔勒布的经典著作《黑天鹅》中对“黑天鹅现象”的定义是

963
来自专栏华章科技

R语言怎么给中文分词?

Rwordseg包依赖于rJava包。由于Rwordseg包并没有托管在CRAN上面,而是在R-Forge上面,因此在在R软件上面直接输入install.pac...

881
来自专栏木东居士的专栏

拉链表是什么

4215
来自专栏牛客网

九月初,终于稳了一波,可以放松一下了嘛 附面经

最近几年的经历教会我一个道理,那就是,只要你努力,世界上没有什么事搞不砸的。——尼克·霍恩比

1181
来自专栏数据库新发现

数据库性能和数据库安全:面试题上以小见大

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

930
来自专栏java架构学习交流

java 面试,java 后端面试,数据库方面对初级和高级程序员的要求

本内容摘自 java web轻量级开发面试教程 对于合格的程序员,需要有基本的数据库操作技能,具体体现在以下三个方面。 l  第一,针对一类数据库(比如MySQ...

2297
来自专栏IT大咖说

你是否知道怎样借助ES在不同场景下构建数据仓库

内容来源:2017 年 11 月 25 日,数说故事平台架构团队高级工程师吴文杰在“Elastic Meetup 广州交流会”进行《Data Warehouse...

1544
来自专栏Golang语言社区

服务器 数据库设计技巧--1

1. 原始单据与实体之间的关系 可以是一对一、一对多、多对多的关系。在一般情况下,它们是一对一的关系:即一张原始单据对应且只对应一个实体。在特殊情况下,它们可能...

3574
来自专栏牛客网

猫眼测开一二三面面经,给口头offer

一面: 计算机网络: 面试官:浏览器输入URL地址到呈现页面给用户,中间到底发生了什么?用到了什么协议。 我:balabala,扯到了DNS 面试官:DNS的查...

6619

扫码关注云+社区

领取腾讯云代金券