SQL性能小测试得出的惊人结果：60%失败

2011年，我开展了“3分钟测试你对SQL性能知道多少?”的测试活动。其中包含五个问题，它们是这样的：每个问题有一个query/index查询，问你这样是否正确使用了索引。至今， 这个测试已经成了  Use The Index, Luke网站上的一个热点。这个测试已经被回答了28,000次。

提醒一下：也许你不想被我剧透，你可以提前自己测试一下自己。

尽管这个测试是为了教育，我很好奇自己是否可以从中找到一些规律，我认为可以的。当你看这些结果时，要记住几点，第一，这些测试因为很出人意料才惹人眼球，也就是说，有的测试看着性能很高，其实性能不高。有的反之。只有一个问题答案符合你的第一印象。很有意思的是，这个测试并不知道参与者是谁，所有人都可以参与，为了获得一个好的分数，你也可以再来一遍。要晓得这个测试不是为了对索引进行科学研究。然而，我认为结果仍可以给人一些启示。

下面我对每个问题展示两个不同的统计图。第一，每个问题平均没正确回答多少次。第二，对于MySQL, Oracle, PostgreSQL 和 SQL Server统计数据有什么不同。也就是说，是否MySQL 使用者会比PostgreSQL 使用者更懂索引呢？我很幸运获得这样的统计数据，原因是不同的数据库提供商有自己独特的语法定义。像MySQL和PostgreSQL 中的 LIMIT  到了SQL Server中就成了 TOP。因此参与者开始时要选择一种数据库，问题是针对所选数据库的。

问题一：WHERE语句中的函数

从性能上来看，下面的SQL语句是好的实践吗？

查询出所有2012年的行：

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT text, date_column
  FROM tbl
 WHERE TO_CHAR(date_column, 'YYYY') = '2012';

这个例子 SQL语句使用了Oracle和PostgreSQL  的特有函数，在MYSQL中这个问题就使用YEAR(date_column)，在SQL SERVER中则为datepart(yyyy, date_column)。当然我可以使用EXTRACT(YEAR date_column)，但我觉得还是使用通用一点的语法好一点。

参与者有两个选项：

•  好的实践 ，没有大的性能改进可以采用了
•  坏的实践，有大的性能改进可以采用

答案是“坏实践”，原因是虽然在date_column上有索引，但 是在date_column字段上加了函数以后，索引就失效了。你如果不信，你可以看看一些可以证明我的结论的脚本和最后的解释说明。详细的解释都在  Use The Index, Luke网站的相关页面上。

如果你不知道在字段上加函数时怎么吧索引的功能给抹杀了，很多人都和你一样。只有2/3的人给出了正确答案。算上有些人选了两次，有些人是蒙的。这样说来差不多只有一半的人答对，无疑是很少的。我用下面这张图强调一下

这是我平时工作中最常见的一个问题，当你在 VARCHAR 类型的字段上使用 UPPER,  TRIM等函数时同样会碰到这个问题。请记住，当你对WHERE语句中使用的字段加上函数的时候，它的索引功能就失去了作用。

尽管这个结果很令人失望——只比随便碰对的概率高17%，但这都没让我感到惊奇。让我惊奇的是在不同数据库使用者中结果的不同。

实施上 MYSQL使用者只得到了55%的分数——就像纯粹蒙一样低。PostgreSQL 使用者却获得了83%的分数。

也许产生这个结果的原因是MYSQL不支持 function-based indexes而Oracle 和 PostgreSQL支持。Function-based 索引允许你使用索引表达式像TO_CHAR(date_column, ‘YYYY’)，虽然对这个测试来说，这样做不是推荐的解决方案。但仅仅是这个特性的存在让Oracle 和 PostgreSQL使用者对这个问题更有意识。SQL Server提供了类似的特性，虽然不能直接使用索引表达式，但是你可以创建所谓的computed column，这个列是可以被索引的。

虽然以上可以解释为什么MySQL 使用者的效率比较低，但这不是借口。不管支持function-based indexes与否，那个 query/index句子总之效率很低。很有效果的改进是不在索引字段上使用函数：

SELECT text, date_column
  FROM tbl
 WHERE date_column >= TO_DATE('2012-01-01', 'YYYY-MM-DD')
   AND date_column <  TO_DATE('2013-01-01', 'YYYY-MM-DD');

索引字段不必改变。这种解决方案很灵活，因为它支持广泛的类型——星期或月份。这是我推荐的解决方案。

我很好奇，我想知道那些正确回答问题的人怎么在function-based索引上考虑复合索引。我最好把这种回答认为是正确了一半。

问题二：索引过之后的TOP-N查询

从性能上来看是好的实践还是坏的实践？

按时间远近排行：

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT id, a, date_column
  FROM tbl
 WHERE a = ? 
 ORDER BY date_column DESC
 LIMIT 1;

注意，那个问号是个占位符。因为我经常推荐开发者使用绑定变量。

参与者有两个选项：

•  好的实践 ，没有大的性能改进可以采用了
•  坏的实践，有大的性能改进可以采用

这个问题看着有性能危险，但其实不是。一般看来order by一定会对数据排序，然而这个索引，使你没有没有必要对整个数据集排序，所以它就像查询唯一索引键一样快。

正确率接近与 “随便蒙” ，我认为人们对这个问题基本上没有概念。

这个结果让人难以接受，  我看到人们平时建立缓存表，恰恰为了避免我们介绍这种查询，经常被计划任务填满。有趣的是这种日常任务经常引起性能问题，因为它需要在很小的时间间隔内确认缓存表中是否存在新的数据。然而，正确的索引应该是你的第一选择。

这里，我要提一下Oracle 数据库使用者要特别注意一下这个技巧。到12c 版本的Oracle数据库仍然没有提供像 LIMIT or  TOP等便利的语法糖。你可以使用 ROWNUM的伪式的数据列。

SELECT *
  FROM (
        SELECT id, date_column
          FROM tbl
         WHERE a = :a
         ORDER BY date_column DESC
       )
 WHERE rownum <= 1;

这个多余的复杂度让Oracle使用者得到了错误的结果，比“随便蒙”对的概率还低。 

对于这个问题回馈的另一个争论是如果包含ID列将允许  index-only scan，尽管这是正确的，但我不认为不这样做就是一个“坏实践”。因为查询的只有一行。index-only scan可以避免单表访问，很多情况下你可以使用它提高性能，但一般情况下我认为这是一种过早优化，这是只是我的观点。但这个争论可以让我们看到PostgreSQL 使用者获得最好的分数。PostgreSQL直到9.2版本才有index-only scans。在2012年九月才发布这个特性。因此PostgreSQL 没有掉入认为只有index-only scan才能提高性能的陷阱。

问题三：索引列的顺序

从性能上来看是好的实践还是坏的实践

两个查询语句：

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT id, a, b
  FROM tbl
 WHERE a = ?
   AND b = ?;

SELECT id, a, b
  FROM tbl
 WHERE b = ?;

参与者有两个选项：

•  好的实践 ，没有大的性能改进可以采用了
•  坏的实践，有大的性能改进可以采用

答案是坏实践，因为第二个查询语句没有正确地使用索引。把索引列的顺序改为(b, a)可以使两个查询语句都能使用索引从而获得很高的性能。在b上再加一个索引，从而无缘无故的带来了很大的性能开销。不幸地是我看到很多人都这么做。

结果是令人失望的，但是我已经猜到了。比“随便蒙”只高12.5% 。

这也是一个我每天都遇到的问题，人们就是不知道复合索引是怎么工作的。

不同数据库的使用者的回答很接近，可能是因为（不同数据库）没有很大语法区别和的特性影响回答的结果。Oracle的不为人知的Skip Scan特性有很小的影响。通常来讲index-only  scan 的意识可能有影响，但这次它的影响是让参与者更有可能回答对问题。

总之，统计表明，一些数据库的使用者比另一下更了解索引。有趣的是PostgreSQL 使用者第三次获得最高分。

问题四：模糊查询

从性能上来看是好的实践还是坏的实践？

查询一个句子：

CREATE INDEX tbl_idx ON tbl (text);

SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';

我这次给出了不一样的答案：

• 银弹 ，总是运行的很快
• 噩梦，有性能危险

正确答案是噩梦因为匹配符中使用了前缀通配符，反之如果使用匹配符“TERM%”就会更有效率。大部分人都能回答对这个问题。我可以说大部分人还是知道 LIKE 不是用来全文搜索的。

这个与众不同的结果各种数据库使用者的正确率相差无几。

这一次PostgreSQL 使用者不是那么牛逼了。我们仔细审视一下PostgreSQL 面对的问题就知道为什么了。

CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops);

SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';

注意我们对索引字段的补充修饰（varchar_pattern_ops），在PostgreSQL中这个操作符类使的索引对后缀通配符无效。我加上这个是想知道人们是否意识到在模糊查询是前缀通配符会带来问题。没有操作符类，它不工作有两个原因：（1）前缀通配符；（2）没有操作符类，我认为这是显然的。

问题五a  Index-only  scan

第五个问题有点棘手，因为在这个测试开始时，PostgresSQL不支持  index-only scans。因此我稍微调整，两组的这个问题不一样。 MySQL, Oracle and SQL Server中是关于index-only  scan。另一个是针对PostgresSQL 使用者出的关于索引列的顺序问题。我把结果都展示在这里。先看关于index-only scans:的问题。

从第一个到第二个查询性能会怎么改变？

从一百万行中选出一百行：

CREATE INDEX tab_idx ON tbl (a, date_column);

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
 GROUP BY date_column;

从一百万行中选出十行

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
   AND b = 42
 GROUP BY date_column;

这个问题有点不同，因为我给了四个答案：

• 查询性能大体相同
• 依赖数据的不同
• 查询会变很慢（影响>10%）
• 查询会变很快（影响>10%）

在我出这个测试的时候，我十分晓得五五分的答案没有什么意义，要在让参与者快速抓住要点并回答和给出准确答案之间做权衡。

简单来说，正确答案是查询会变的很慢，因为原来的查询使用了index-only scan，这个查询只使用了索引中的数据就能给出答案而不需要到实际的表中获取数据。第二个查询需要检查数据列B，而数据列B不在索引中，因此数据库要花费多余的开销到拿出候选的行来判断是否符合条件，它要从表中取出100行，这正是第一个查询中要返回的数据行数。因为有group by操作，估计要取出更多的数据行，会使查询变的很慢。

因为有多个选项，总体分数明显下降，掉到了比“随便蒙”低39% or 14%。

我会说有39%的参与者知道正确答案这个结论是错误的，它们虽然给出了正确答案，但是我估计有25% 的人是蒙的。

分开各种数据库使用者后，结果更是无聊。

但是，我们仍然要看一下人们是怎么回答的：

我非常吃惊，“大体相同” 和 “依赖具体的数据”这两个选项都获得了25%的选择——它们可能都是猜的。这是否表明一半的参与者只是在胡乱猜。还是因为这是最后一个问题，很多人都想快点做完看看答案，恩，很有可能。然而正确答案“会变的很慢”获得了38.8%的选择，导致只有10.9%的人选择“会变的很快”选项。

我的本意是把人误导选择“会变的很快”，因为后者数据量更少——只有使用了index-only scan的情况下会变得不同，但是我假设我得到这个结果是因为人们通常会认为很明显的答案肯定是错的。这样的话，我想验证多少人会知道index-only scan的本意根本没有得到证明。

问题5b：索引列顺序和范围操作符

这个问题只是给PostgreSQL 使用者的。

从性能上来看是好的实践还是坏的实践？

查询状态的X并且不超过五年的实体。

CREATE INDEX tbl_idx ON tbl (date_column, state);

SELECT id, date_column, state
  FROM tbl
 WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR
   AND state = 'X';

(365 rows)

数据分布如下：

SELECT count(*)
  FROM tbl
 WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR;

 count 
-------
  1826

SELECT count(*)
  FROM tbl
 WHERE state = 'X';

 count 
-------
 10000

参与者有两个选项：

•  好的实践 ，没有大的性能改进可以采用了。
•  坏的实践，有大的性能改进可以采用。

正确答案是“坏实践”，因为索引的数据列的顺序不对。通常的索引列排序是规律是，如果等号运算符放在左边就经常有很高的性能，过滤之后，再使用范围操作符也很有效率。然而，如果范围操作符放在左边，就会丧失索引的好处，之后的的索引列也不能高效率的使用。

像以上没有修改的查询语句，我们要在索引中找出1826个实体（它们都符合 date_column 列的过滤），然后对它们进行 state 列过滤。如果过滤顺序改变一下，数据库就使得两次过滤都很有效，直接把要过滤的行数限制在了365 行内。

人们是这样回答的：

等一下，竟然比随便猜猜的正确概率还低，人们不仅对次没有意识，而且大多数人都有了错误的理解。然而我得承认这个”大多数“是有水分的。当我运行这个例子时，快的不只是一倍，竟然加速了70%。

总体分数：多少人通过了测试？

单独看每个例子很有趣，但是那不能让你知道有多少人答对了5个题目，下面的图可以告诉你。

最后，我想把这张图归结为一个数字：到底多少人通过了测试？

考虑到只有五个问题，并且每个问题只有两个选项，公平的说，我想答对三个不足以说明你通过了测试，答对五个又明显要求过高。答对四个通过测试，我觉得这样界定是很明智的。使用这个定义，38.2%通过了测试。多说一句，随便猜通过的概率为12.5%。

SQL性能小测试得出的惊人结果：60%失败，首发于 博客 - 伯乐在线。