SQL性能探讨

SQL性能探讨

作者:admin |  时间:2014-04-13 |  浏览:881 |  1 条评论

问题一:WHERE语句中的函数

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

查询出所有2012年的行:

1
2
3
4
5
CREATEINDEX 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网站的相关页面上。

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

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

按时间远近排行:

1
2
3
4
5
6
7
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一定会对数据排序,然而这个索引,使你没有没有必要对整个数据集排序,所以它就像查询唯一索引键一样快。

问题三:索引列的顺序

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

两个查询语句:

1
2
3
4
5
6
7
8
9
10
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上再加一个索引,从而无缘无故的带来了很大的性能开销。不幸地是我看到很多人都这么做。

问题四:模糊查询

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

查询一个句子:

1
2
3
4
5
CREATE INDEX tbl_idx ON tbl (text);
 
SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';

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

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

问题五a  Index-only  scan

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

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

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

1
2
3
4
5
6
CREATE INDEX tab_idx ON tbl (a, date_column);
 
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
 GROUP BY date_column;

从一百万行中选出十行

1
2
3
4
5
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操作,估计要取出更多的数据行,会使查询变的很慢。

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

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

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

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

1
2
3
4
5
6
7
8
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';
 
(365rows)

数据分布如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT count(*)
  FROM tbl
 WHERE date_column >= CURRENT_DATE - INTERVAL'5'YEAR;
 
 count
-------
  1826
 
SELECT count(*)
  FROM tbl
 WHERE state ='X';
 
 count
-------
 10000

参与者有两个选项:

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

相关推荐

  1. 尚吾网 2014-4-25 16:37 #1 回复TA

    坚持更新博客就像坚持写日记一样,不仅是习惯,也是耐力,表示支持

发表评论

电子邮件地址不会被公开。

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>