作者 | 董旭阳TonyDong，CSDN 博客专家
责编 | 唐小引
头图 | CSDN 下载自东方 IC
出品 | CSDN 博客
下面是 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）就可以得到结果；第二个查询虽然可能返回的数据更少，但是需要通过索引访问表，也就是回表。
亲爱的朋友，你答对了几个？欢迎留言讨论！
文章发布已获作者授权，CSDN 博文地址：
https://blog.csdn.net/horses/article/details/103028340
【End】
推荐阅读
☞小米回应 50 亿疫情贷款申请；爱奇艺 App 崩溃；OpenSSH 8.2 发布 | 极客头条
☞小米 10 年再创业，高端 5G 手机和 AIoT 有多少机会？
你点的每一个在看，我认真当成了喜欢
转载：https://blog.csdn.net/csdnnews/article/details/104368454