在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

五個簡單的 SQL 查詢性能測試題，只有 40% 及格率。你敢來挑戰(zhàn)嗎？

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        測試題
            問題 1
            問題 2
            問題 3
            問題 4
            問題 5
        解析
            問題 1
            問題 2
            問題 3
            問題 4
            問題 5
        

下面是 5 個關(guān)于索引和 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，性能有問題。因為在索引字段上使用函數(shù)或者表達(dá)式，會導(dǎo)致索引失效。

你可以使用 EXPLAIN 命令查看該語句的執(zhí)行計劃，最好先執(zhí)行一次表的統(tǒng)計分析：

– Oracle
EXPLAIN PLAN FOR
SELECT *
FROM t1
WHERE TO_CHAR(dt, ‘YYYY’) = ‘2019’;

SELECT * FROM TABLE(dbms_xplan.display);

                                                                      |
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 ||
--------------------------------------------------------------------------|
|

                                                                      |

1 - filter(TO_CHAR(INTERNAL_FUNCTION(“DT”),‘YYYY’)=‘2019’) |
|

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

– MySQL
EXPLAIN SELECT *
FROM t1
WHERE YEAR(dt) = ‘2019’;

MySQL 雖然使用了索引，但是也需要對索引進(jìn)行轉(zhuǎn)換判斷；并不是最優(yōu)方案。

接下來是 SQL Server：

– SQL Server
SET STATISTICS PROFILE ON

SELECT *
FROM t1
WHERE datepart(yyyy, dt) = ‘2019’;

SQL Server 使用了索引，但是也需要對索引進(jìn)行轉(zhuǎn)換判斷；并不是最優(yōu)方案。

最后看一下 PostgreSQL：

– PostgreSQL
EXPLAIN SELECT *
FROM t1
WHERE TO_CHAR(dt, ‘YYYY’) = ‘2019’;

PostgreSQL 使用的是全表掃描，沒有使用索引。

正確做法是修改查詢語句：

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

備注：使用函數(shù)索引并不是最優(yōu)解決方法，它只能用于特定的查詢條件；如果查詢條件改成 TO_CHAR(dt, ‘YYYY-MM-DD’) = '2019-06-01’或者其他形式就無法使用該索引了。
問題 2

答案是：A，性能沒有問題。該語句的 WHERE 子句以及 ORDER BY 子句都可以使用索引（反向掃描），不需要對任何行進(jìn)行額外的排序?？梢允褂蒙厦娴姆椒ú榭磮?zhí)行計劃。
問題 3

答案是：B，索引有問題。因為第二個查詢無法使用索引或者效率不高。雖然有些數(shù)據(jù)庫可能采用索引跳躍掃描，但是可以通過修改索引字段的順序獲得更好的性能：

CREATE INDEX idx3 ON t3(col2, col1);

將 col2 放在索引的最左端，兩個查詢都可以利用索引；也就是說，復(fù)合索引應(yīng)該遵循最左前綴原則。另外，基于 col2 再創(chuàng)建一個索引會導(dǎo)致索引重復(fù)，不是好的方案。
問題 4

答案是：B，性能有問題。因為在 LIKE 條件中以通配符 % 或者 _ 開始的字符串無法使用索引。不過，以下語句可以使用索引：

SELECT *
FROM t4
WHERE col2 LIKE ‘sql%’;

對于 PostgreSQL 而言，還需要在創(chuàng)建索引時指定操作符類：

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

問題 5

答案是：A，第一個查詢更快。因為它只需要通過掃描索引（Index-Only Scan）就可以得到結(jié)果；第二個查詢雖然可能返回的數(shù)據(jù)更少，但是需要通過索引訪問表，也就是回表。