首页 文章 问答 51讲堂 活动
写文章 提问题 登录 | 注册
  • 0
  • 1
分享
  • 五个简单的SQL查询性能测试题，只有40%及格率，你敢来挑战吗？
  • 恬恬圈 2020-03-18 13:22:49 字数 7042 阅读 3423 收藏 1
    其它

下面是 5 个关于索引和 SQL 查询性能的测试题；其中 4 个题目都是答案二选一，1 个题目是三选一。只要答对 3 个就算及格，是不是貌似很简单？但实际上只有 40% 的人能够及格。我们在测试题的后面会给出答案解析，不过建议你先尝试一下，看看答对几个！


测试题

问题 1：以下查询语句有没有性能问题？

CREATE TABLE t1 (
  id INT NOT NULL,
  dt DATE,
  PRIMARY KEY (id)
);
CREATE INDEX idx1 ON t1(dt);
SELECT *
  FROM t1
 WHERE TO_CHAR(dt, 'YYYY') = '2019'; -- Oracle、PostgreSQL
 -- WHERE YEAR(dt) = '2019'; -- MySQL
 -- WHERE datepart(yyyy, dt) = '2019'; -- SQL Server

选项 A：没问题；选项 B：有问题。


问题 2：以下查询语句有没有性能问题？

CREATE TABLE t2 (
  id INT NOT NULL,
  i  INT
  dt DATE,
  v  VARCHAR(50),
  PRIMARY KEY (id)
);
CREATE INDEX idx2 ON t2(i, dt);
SELECT *
  FROM t2
 WHERE i = 99
 ORDER BY dt DESC
 FETCH FIRST 5 ROW ONLY; -- Oracle、SQL Server、PostgreSQL
 -- OFFSET 0 ROWS FETCH FIRST 5 ROW ONLY; -- SQL Server
 -- LIMIT 5; -- MySQL

选项 A：没问题；选项 B：有问题。


问题 3：下表中的索引有没有问题？

CREATE TABLE t3 (
  id   INT NOT NULL,
  col1 INT,
  col2 INT,
  col3 VARCHAR(50),
  PRIMARY KEY (id)
);
CREATE INDEX idx3 ON t3(col1, col2);
SELECT *
  FROM t3
 WHERE col1 = 99
   AND col2 = 10;
SELECT *
  FROM t3
 WHERE col2 = 10;

选项 A：没问题；选项 B：有问题。


问题 4：以下查询语句有没有性能问题？

CREATE TABLE t4 (
  id   INT NOT NULL,
  col1 INT,
  col2 VARCHAR(50),
  PRIMARY KEY (id)
);
CREATE INDEX idx4 ON t4(col2);
SELECT *
  FROM t4
 WHERE col2 LIKE '%sql%';

选项 A：没问题；选项 B：有问题。


问题 5：假如存在以下表和两个查询语句，哪个查询更快？

CREATE TABLE t5 (
  id   INT NOT NULL,
  col1 INT,
  col2 INT,
  col3 VARCHAR(50),
  PRIMARY KEY (id)
);
CREATE INDEX idx5 ON t5(col1, col3);
SELECT col3, count(*)
  FROM t5
 WHERE col1 = 99
 GROUP BY col3;
SELECT col3, count(*)
  FROM t5
 WHERE col1 = 99
   AND col2 = 10
 GROUP BY col3;

选项 A：第一个查询更快；选项 B：第二个查询更快；选项 C：两个查询性能差不多。


解析：

问题1：答案是B，性能有问题。

因为在索引字段上使用函数或者表达式，会导致索引失效。

你可以使用 EXPLAIN 命令查看该语句的执行计划，最好先执行一次表的统计分析：

-- Oracle
EXPLAIN PLAN FOR
SELECT *
  FROM t1
 WHERE TO_CHAR(dt, 'YYYY') = '2019';
SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT                                                         |
--------------------------------------------------------------------------|
Plan hash value: 3617692013                                               |
                                                                          |
--------------------------------------------------------------------------|
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     ||
--------------------------------------------------------------------------|
|   0 | SELECT STATEMENT  |      |     1 |    22 |     2   (0)| 00:00:01 ||
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    22 |     2   (0)| 00:00:01 ||
--------------------------------------------------------------------------|
                                                                          |
Predicate Information (identified by operation id):                       |
---------------------------------------------------                       |
                                                                          |
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("DT"),'YYYY')='2019')             |
                                                                          |
Note                                                                      |
-----                                                                     |
   - dynamic statistics used: dynamic sampling (level=2)                  |

Oracle 中是全表扫描，没有走索引。再看 MySQL：

-- MySQL
EXPLAIN SELECT *
  FROM t1
 WHERE YEAR(dt) = '2019';
id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra                   |
--|-----------|-----|----------|-----|-------------|----|-------|---|----|--------|------------------------|
 1|SIMPLE     |t1   |          |index|             |idx1|4      |   |   1|     100|Using where; Using index|

MySQL 虽然使用了索引，但是也需要对索引进行转换判断；并不是最优方案。

接下来是 SQL Server：

-- SQL Server
SET STATISTICS PROFILE ON
SELECT *
  FROM t1
 WHERE datepart(yyyy, dt) = '2019';
Rows|Executes|StmtText                                                                                                 |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument                                                                                |DefinedValues                                 |EstimateRows|EstimateIO           |EstimateCPU          |AvgRowSize|TotalSubtreeCost     |OutputList                                    |Warnings|Type    |Parallel|EstimateExecutions|
----|--------|---------------------------------------------------------------------------------------------------------|------|------|------|----------|----------|----------------------------------------------------------------------------------------|----------------------------------------------|------------|---------------------|---------------------|----------|---------------------|----------------------------------------------|--------|--------|--------|------------------|
   0|       1|SELECT * FROM t1 WHERE datepart(yyyy, dt) = '2019'                                                    |     1|     1|     0|          |          |                                                                                        |                                              |           1|                     |                     |          |0.0032830999698489904|                                              |        |SELECT  |       0|                  |
   0|       1|  |--Index Scan(OBJECT:([hrdb].[dbo].[t1].[idx1]),  WHERE:(datepart(year,[hrdb].[dbo].[t1].[dt])=(2019)))|     1|     2|     1|Index Scan|Index Scan|OBJECT:([hrdb].[dbo].[t1].[idx1]),  WHERE:(datepart(year,[hrdb].[dbo].[t1].[dt])=(2019))|[hrdb].[dbo].[t1].[id], [hrdb].[dbo].[t1].[dt]|           1|0.0031250000465661287|1.5809999604243785E-4|        14|0.0032830999698489904|[hrdb].[dbo].[t1].[id], [hrdb].[dbo].[t1].[dt]|        |PLAN_ROW|       0|                 1|

SQL Server 使用了索引，但是也需要对索引进行转换判断；并不是最优方案。

最后看一下 PostgreSQL：

-- PostgreSQL
EXPLAIN SELECT *
  FROM t1
 WHERE TO_CHAR(dt, 'YYYY') = '2019';
QUERY PLAN                                                                      |
--------------------------------------------------------------------------------|
Seq Scan on t1  (cost=0.00..49.55 rows=11 width=8)                              |
  Filter: (to_char((dt)::timestamp with time zone, 'YYYY'::text) = '2019'::text)|

PostgreSQL 使用的是全表扫描，没有使用索引。


正确做法是修改查询语句：

SELECT *
  FROM t
 WHERE dt BETWEEN DATE '2019-01-01' AND DATE '2019-12-31';

备注：使用函数索引并不是最优解决方法，它只能用于特定的查询条件；如果查询条件改成 TO_CHAR(dt, 'YYYY-MM-DD') = '2019-06-01'或者其他形式就无法使用该索引了。


问题2答案是：A，性能没有问题。

该语句的 WHERE 子句以及 ORDER BY 子句都可以使用索引（反向扫描），不需要对任何行进行额外的排序。可以使用上面的方法查看执行计划。


问题3答案是：B，索引有问题。

因为第二个查询无法使用索引或者效率不高。虽然有些数据库可能采用索引跳跃扫描，但是可以通过修改索引字段的顺序获得更好的性能：

CREATE INDEX idx3 ON t3(col2, col1);

将 col2 放在索引的最左端，两个查询都可以利用索引；也就是说，复合索引应该遵循最左前缀原则。另外，基于 col2 再创建一个索引会导致索引重复，不是好的方案。


问题4答案是：B，性能有问题。

因为在 LIKE 条件中以通配符 % 或者 _ 开始的字符串无法使用索引。不过，以下语句可以使用索引：

SELECT *
  FROM t4
 WHERE col2 LIKE 'sql%';

对于 PostgreSQL 而言，还需要在创建索引时指定操作符类：

-- PostgreSQL
CREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);


问题5答案是：A，第一个查询更快。

因为它只需要通过扫描索引（Index-Only Scan）就可以得到结果；第二个查询虽然可能返回的数据更少，但是需要通过索引访问表，也就是回表。

你答对了几个？欢迎留言讨论！


本文为51Testing经授权转载，转载文章所包含的文字来源于作者：董旭阳TonyDong。如因内容或版权等问题，请联系51Testing进行删除。原文链接：https://blog.csdn.net/horses/article/details/103028340.

  • 【留下美好印记】
    赞赏支持
登录 后发表评论
+ 关注

热门文章

    最新讲堂

      • 推荐阅读
      • 换一换
          • 【性能测试】性能测试干货问答篇
            06-10
                  沐沐今天想和大家分享一下性能测试过程中一些常见的问题，可以帮助性能学习初学者解决一些小疑惑。一、性能测试都分为哪些种类？ 负载测试：主要检查被测系统在既定负载下的性能表现，即通过逐步加压的方法，达到既定的性能阈值的目标（阈值的设定应是小于等于某个值，例如cpu<=80%等）。压力测试：主要检查被测系统在极端条件下的表现，即通过逐步加压的方法，使得系统的某些资源达到饱和，简言之就是测试什么条件下可以把系统压奔溃。并发测试：主要是验证系统的并发处理能力，即在同一时间内，多个虚拟用户同时访问同一模块、同一功能，通常的测试方法是设置集合点。基准...
            大佬养成计划
            2 0 4055
            分享
          • Maven 跳过单元测试——软件测试圈
            08-09
            1、背景Maven 构建生命周期为我们提供了对项目执行各种操作，例如验证，清理、打包、测试和部署项目。而有时候我们需要跳过单元测试，例如，在处理新模块时，还有未通过的单元测试时。在这些情况下，我们可以跳过测试以避免编译和运行测试时发生的时间。在本文中，我们将了解可用于跳过 maven 项目中的测试的各种方法。2、几种跳过的方法可以在插件标签的配置中设置跳过测试元素属性；通过在 Maven 执行命令中使用 -DskipTests 参数的命令行；通过在触发 maven 命令以执行阶段时使用 maven.test.skip 属性；在使用surefire时，通过在pom.xml的插件标签中使用excl...
            移动测试单元测试
            0 0 1778
            分享
          • 软件测试面试常见问题——软件测试圈
            06-18
            1.请做一下自我介绍吧面试题要点:考察你的语言表达能力，以及获取你的基本信息和大致情况。分析:包含内容有名字+工作经历+做过的项目。一定要提前准备好，保证表达的流畅。控制在1分钟左右。注意:不要说离职原因，不要说转行，不要给自己挖坑。--外企项目，英文自我介绍-内容也差不多。参考示例:我叫xxx，16年从xx学校xxx专业毕业，毕业至今从事测试工作，到现在有3年了。3年中我待过2家公司，第一家是一个x，主要业务是做xx的。我最近的一家公司吧，主要是做互联网金融业务的，它有web端和APP端的项目，我这两个项目都有参与测试。我负责的主要测试内容包括在web和APP端前端页面还没有完成之前，完成产...
            职业发展
            0 0 1021
            分享
          • Groovy def关键字妙用
            04-18
            在之前的文章和视频中，我分享过Groovy语法中def关键字的基本使用方法。当时对def理解是：不定类型变量，资料中也有说是无类型变量，感觉两个意思大差不差，就是不显式声明对象类型。基本使用方式如下：def a = 1def b = "FunTester"类似这样的方式，当时Java新版也已经支持了var可以替代一些显式声明变量类型的代码，但是除了使用范围上，Groovy的def还是明显优于var，而且功能上也是强不少（当然有人理解为弱不少，后续讲解）。本质区别在于Groovy的def不仅可以替代显式的类型声明，还...
            万字更文
            0 0 832
            分享
          • 软件测试技术之接口测试的N种玩法——软件测试圈
            9小时前
            　　在我看来接口测试相对其他类型的测试是比较简单的。对于最常见的HTTP接口，只需要知道接口的 URL、方法、参数类型、返回值 ... 就可以对接口进行测试了。　　apifox　　如果你是入门级选手，那么apifox应该很适合你，它是一款类似postman的接口测试工具。而且功能也很强大，定位 Postman + Swagger + Mock + JMeter。你值得尝试。　　为什么不postman? 嗯，你说的都对！　　HttpRunner　　如果你不想用工具，也不想写代码。想更加简单高效的编写接口测试用例，那么HttpRunner2.x 就很适合你了。　　{ 　　  &...
            测试技术接口测试
            0 0 15
            分享
      • 51testing软件测试圈微信