五个简单的 SQL 查询性能测试题,只有 40% 及格率。你敢来挑战吗?
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
测试题
问题 1
问题 2
问题 3
问题 4
问题 5
解析
问题 1
问题 2
问题 3
问题 4
问题 5
下面是 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 |
|
- 1
--------------------------------------------------------------------------|
| 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 |
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)就可以得到结果;第二个查询虽然可能返回的数据更少,但是需要通过索引访问表,也就是回表。