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

友情链接: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有什么可能的逻辑问题、语法问题、性能问题,可以怎样进行索引优化、逻辑优化以提升性能?

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

标准答案没有,但是我可以说说我的看法,当然前提是『这个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最终不应该被执行,也不会有返回结果。

加油吧,少年!

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

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

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

751
来自专栏性能与架构

建立索引后的代价到底有多大?

前几天写的文章“MySQL 性能优化案例:覆盖索引”,介绍了使用覆盖索引优化查询的方式,受到了一个网友的批评 批评的内容为: “直接从索引放回数据很快是个常...

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

每秒执行6000的简单SQL优化(一)(r10笔记第62天)

最近看到一个系统的负载比较高,引起了我的注意,查看AWR报告发现,竟然是因为两条很简单的SQL语句导致。 语句有多简单呢,就是下面的两个SQL语...

34610
来自专栏idba

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

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

963
来自专栏Hadoop数据仓库

HAWQ取代传统数仓实践(六)——增加列

        业务的扩展或变化是不可避免的,尤其像互联网行业,需求变更已经成为常态,唯一不变的就是变化本身,其中最常碰到的扩展是给一个已经存在的表曾加列。  ...

2808
来自专栏数据库新发现

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

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

942
来自专栏沈唁志

2018年韩创科技旗下我图网PHP工程师面试题分享

4. 一张采用Innodb的User表,其中id为主键,name为普通索引,试从索引的数据结构角度分析,以下两条语句(均返回一条记录)在检索过程中有哪些区别

1442
来自专栏IT大咖说

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

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

1544
来自专栏芋道源码1024

慢讯!Sharding-Sphere 正式进入 Apache 孵化器

美国时间2018年11月10日,开源分布式数据库中间件生态圈Sharding-Sphere正式进入Apache基金会孵化器。

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

融会贯通学习trigger(r2笔记第4天)

很多的东西在工作中用到的时候才能理解深刻,有些东西停留在理论层面而不去实践,就不会真正理解。 昨天写了一个很简单的trigger,但是中间也费了一些周折。 系统...

3556

扫码关注云+社区

领取腾讯云代金券