原文地址：https://blog.csdn.net/horses/article/details/103028340
下面是 5 個關於索引和 SQL 查詢性能的測試題；其中 4 個題目都是答案二選一，1 個題目是三選一。只要答對 3 個就算及格，是不是貌似很簡單？但實際上只有 40% 的人能夠及格。我們在測試題的後面會給出答案解析，不過建議你先嚐試一下，看看答對幾個！
問題 1
以下查詢語句有沒有性能問題？
<code>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/<code>
選項 A：沒問題；
選項 B：有問題。
問題 2
以下查詢語句有沒有性能問題？
<code>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/<code>
選項 A：沒問題；
選項 B：有問題。
問題 3
下表中的索引有沒有問題？
<code>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;/<code>
選項 A：沒問題；
選項 B：有問題。
問題 4
以下查詢語句有沒有性能問題？
<code>zuioCREATE 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;/<code>
選項 A：沒問題；
選項 B：有問題。
問題 5
假如存在以下表和兩個查詢語句，哪個查詢更快？
<code>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;/<code>
選項 A：第一個查詢更快；
選項 B：第二個查詢更快；
選項 C：兩個查詢性能差不多。
解析
- 問題 1
答案是：B，性能有問題。因為在索引字段上使用函數或者表達式，會導致索引失效。
你可以使用 EXPLAIN 命令查看該語句的執行計劃，最好先執行一次表的統計分析：
<code>-- 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) /<code>
Oracle 中是全表掃描，沒有走索引。再看 MySQL：
<code>-- 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;/<code>
MySQL 雖然使用了索引，但是也需要對索引進行轉換判斷；並不是最優方案。
接下來是 SQL Server：
<code>-- 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|/<code>
SQL Server 使用了索引，但是也需要對索引進行轉換判斷；並不是最優方案。
最後看一下 PostgreSQL：
<code>-- 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)|/<code>
PostgreSQL 使用的是全表掃描，沒有使用索引。
正確做法是修改查詢語句：
<code>SELECT *
FROM t
WHERE dt BETWEEN DATE '2019-01-01' AND DATE '2019-12-31';/<code>
備註： 使用函數索引並不是最優解決方法，它只能用於特定的查詢條件；如果查詢條件改成 TO_CHAR(dt, 'YYYY-MM-DD') = '2019-06-01'或者其他形式就無法使用該索引了。
- 問題 2
答案是：A，性能沒有問題。該語句的 WHERE 子句以及 ORDER BY 子句都可以使用索引（反向掃描），不需要對任何行進行額外的排序。可以使用上面的方法查看執行計劃。
- 問題 3
答案是：B，索引有問題。因為第二個查詢無法使用索引或者效率不高。雖然有些數據庫可能採用索引跳躍掃描，但是可以通過修改索引字段的順序獲得更好的性能：
<code>CREATE INDEX idx3 ON t3(col2, col1);/<code>
將 col2 放在索引的最左端，兩個查詢都可以利用索引；也就是說，複合索引應該遵循最左前綴原則。另外，基於 col2 再創建一個索引會導致索引重複，不是好的方案。
- 問題 4
答案是：B，性能有問題。因為在 LIKE 條件中以通配符 % 或者 _ 開始的字符串無法使用索引。不過，以下語句可以使用索引：
<code>SELECT *
FROM t4
WHERE col2 LIKE 'sql%';/<code>
對於 PostgreSQL 而言，還需要在創建索引時指定操作符類：
<code>-- PostgreSQL
CREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);/<code>
答案是：A，第一個查詢更快。因為它只需要通過掃描索引（Index-Only Scan）就可以得到結果；第二個查詢雖然可能返回的數據更少，但是需要通過索引訪問表，也就是回表。
親愛的朋友，你答對了幾個？歡迎留言討論！
閱讀更多 編程仔日常 的文章
關鍵字: PostgreSQL SQL Microsoft
