五个 SQL 查询性能测试题,只有 40% 及格率,你敢来挑战吗? sql测试数据

新闻资讯2024-08-09 22:29小乐

五个 SQL 查询性能测试题,只有 40% 及格率,你敢来挑战吗? sql测试数据

原文地址:https://blog.csdn.net/horses/article/details/103028340

下面是关于索引和SQL查询性能的5个测试题;其中4题为二选题,1题为三选题。您只需答对3 个即可通过。是不是看起来很容易?但真正通过的只有40%。我们会在试题的最后提供答案分析,但建议您先尝试一下,看看您答对了多少个!

问题1

以下查询语句是否存在性能问题?

创建表t2 (id INT NOT NULL, i INT dt DATE, v VARCHAR(50), PRIMARY KEY (id));在t2(i, dt) 上创建索引idx2;SELECT * FROM t2 WHERE i=99 ORDER BY dt DESC仅获取前5 行; -- Oracle、SQL Server、PostgreSQL -- OFFSET 0 ROWS 仅获取前5 行; -- SQL Server -- 限制5; -- MySQL 选项A:没问题;

选项B:有问题。

问题2

以下查询语句是否存在性能问题?

创建表t2 (id INT NOT NULL, i INT dt DATE, v VARCHAR(50), PRIMARY KEY (id));在t2(i, dt) 上创建索引idx2;SELECT * FROM t2 WHERE i=99 ORDER BY dt DESC仅获取前5 行; -- Oracle、SQL Server、PostgreSQL -- OFFSET 0 ROWS 仅获取前5 行; -- SQL Server -- 限制5; -- MySQL 选项A:没问题;

选项B:有问题。

问题3

下表中的索引有问题吗?

创建表t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上创建索引idx5;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:有问题。

问题4

以下查询语句是否存在性能问题?

zuioCREATE TABLE t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上创建索引idx5;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:有问题。

问题5

如果有下面的表和两条查询语句,哪个查询更快?

创建表t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上创建索引idx5;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命令查看语句的执行计划。最好先对表格进行统计分析:

-- OracleEXPLAIN PLAN FORSELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- -------------------------------------------------- --------|计划哈希值: 3617692013 | |------------------------------------------------ -------------- ----------------------------------||身份证|运营|名称|行|字节|成本(%CPU)|时间||------------------------------------------------------------ ---------------------------------|| 0 |选择语句| | 1 | 22 | 22 2 (0) | 2 (0) | 00:00:01 |||* 1 |表访问已满| T1 | 1 | 22 | 22 2 (0)| 00:00:01 ||------------------------ -------------------------- ------------------------ | | |谓词信息(由操作ID 标识): |---------------------------------------------------- -- ------------- | | 1 - 过滤器(TO_CHAR(INTERNAL_FUNCTION('DT'),'YYYY')='2019') | |注意|----- | - 动态统计信息used: 动态采样(level=2) Oracle 使用全表扫描,没有索引。再看看MySQL:

-- MySQLEXPLAIN SELECT * FROM t1 WHERE YEAR(dt)='2019';id|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra |--|------ ------|-----|----------|-----|-------------|----|-- ----|---|----|--------|---------------------------- | 1 |简单|t1 | |索引| |idx1|4 | | 1| 100|使用地点; MySQL虽然使用了索引,但也需要对索引进行转换判断;这不是最佳解决方案。

接下来是SQL Server:

-- SQL ServerSET STATISTICS PROFILE ONSELECT * FROM t1 WHERE datepart(yyyy, dt)='2019';Rows|执行|StmtText |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument |DefinedValues|EstimateRows|EstimateIO |EstimateCPU |平均行大小| TotalSubtreeCost |输出列表|警告|类型|并行|估计执行|----|--------|------------------------ -------------------------------------------------- -------------------------------|------|------|---- --|----------|----------|---------------------------- -------------------------------------------------- -------------|------------------------------------ ---- ----------|----------------|------------------ ---|---- ------------------|----------|------------ --------- |---------------------------------------- ----------|-- ------|--------|--------|------------ ------| 0|1|从t1 中选择*,其中datepart(yyyy, dt)='2019' | 1| 1| 0| | | | 1||| |0.0032830999698489904| | |选择|0| | 0|1| |--索引扫描(OBJECT:( [hrdb].[dbo].[t1].[idx1]), WHERE:(datepart(年份,[hrdb].[dbo].[t1].[dt])=(2019 )))| 1| 2| 1|索引扫描|索引扫描|OBJECT:([hrdb].[dbo].[t1].[idx1]), WHERE:(datepart(年份,[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:

-- PostgreSQLEXPLAIN SELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';查询计划|---------------------------------------- -------------------------------------------------- ----|t1 上的顺序扫描(成本=0.00.49.55 行=11 宽度=8)| Filter: (to_char((dt):timestamp with time zone, 'YYYY':text)='2019':text)|PostgreSQL 使用是全表扫描,不使用索引。

正确的做法是修改查询语句:

SELECT * FROM t WHERE dt BETWEEN '2019-01-01' 和日期'2019-12-31' 之间;注意:使用函数索引并不是最佳解决方案。只能用于特定的查询条件;如果查询条件改为TO_CHAR(dt, 'YYYY-MM-DD')='2019-06-01' 或其他形式的话,则无法使用索引。

问题2的答案是:A、性能没有问题。该语句的WHERE 子句和ORDER BY 子句都可以使用索引(反向扫描),而不需要对任何行进行额外排序。可以使用上面的方法查看执行计划。

问题3的答案是:B、索引有问题。因为第二个查询不能使用索引或者效率不高。虽然有些数据库可能会使用索引跳过扫描,但通过修改索引字段的顺序可以获得更好的性能:

在t3(col2, col1) 上创建索引idx3;将col2放在索引的最左端,两个查询都可以利用该索引;即复合索引遵循最左前缀原则。另外,基于col2再创建一个索引会导致索引重复,这并不是一个好的解决方案。

问题4的答案是:B,存在性能问题。因为LIKE条件下以通配符%或_开头的字符串无法建立索引。但是,以下语句可以使用索引:

SELECT * FROM t4 WHERE col2 LIKE 'sql%';对于PostgreSQL,创建索引时还需要指定操作符类:

-- PostgreSQLCREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);问题5 答案是:A、第一次查询比较快。因为它只需要扫描索引(Index-Only Scan)即可得到结果;虽然第二个查询可能返回的数据较少,但是需要通过索引来访问表,即返回表。

亲爱的朋友,你答对了几个呢?欢迎留言讨论!

猜你喜欢