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

最新推荐文章于 2024-05-05 22:15:36 发布

数据和云

最新推荐文章于 2024-05-05 22:15:36 发布

阅读量707

点赞数

本文链接：https://blog.csdn.net/enmotech/article/details/82111571

版权

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

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

640?wx_fmt=png

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

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

数据结构和元数据

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

640?wx_fmt=jpeg

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

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

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

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

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

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

关于这个条件，有朋友给了这样几个建议：

去掉 boyfriend ='no' ，你懂的（这位是老司机）
boyfriend 的名字叫 no 就尴尬了（这个直追 Null 那个梗）
有朋友还要加几个条件 and cost < 2000 RMB and beautiful > 85分 and height > 165 order by cost,beautiful desc,height desc （我不得不表示，孩子，你还是嫩啊）；
用 boyfriend is NULL （这是技术流）

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

关于性能

我们再来讨论一下性能。

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

将 between and 改写成 >= 和 <= ，这会减少Oracle自己的转换，同时减少了SQL字符长度，缩减了网络传输，Shared Pool占用；
可以将 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，在内存中进行列式压缩，可以极大的提高查询性能。

640?wx_fmt=png