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

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

面试题一枚可好：请问以下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最终不应该被执行，也不会有返回结果。

加油吧，少年！