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

下面是關于索引和SQL查詢性能的5個測試題；其中4題為二選題，1題為三選題。您只需答對3 個即可通過。是不是看起來很容易？但真正通過的只有40%。我們會在試題的最后提供答案分析，但建議您先嘗試一下，看看您答對了多少個！

問題1

以下查詢語句是否存在性能問題？

創(chuàng)建表t2 (id INT NOT NULL, i INT dt DATE, v VARCHAR(50), PRIMARY KEY (id));在t2(i, dt) 上創(chuàng)建索引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

以下查詢語句是否存在性能問題？

創(chuàng)建表t2 (id INT NOT NULL, i INT dt DATE, v VARCHAR(50), PRIMARY KEY (id));在t2(i, dt) 上創(chuàng)建索引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

下表中的索引有問題嗎？

創(chuàng)建表t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上創(chuàng)建索引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) 上創(chuàng)建索引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

如果有下面的表和兩條查詢語句，哪個查詢更快？

創(chuàng)建表t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上創(chuàng)建索引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命令查看語句的執(zhí)行計劃。最好先對表格進行統計分析：

-- OracleEXPLAIN PLAN FORSELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- -------------------------------------------------- --------|計劃哈希值： 3617692013 | |------------------------------------------------ -------------- ----------------------------------||身份證|運營|名稱|行|字節(jié)|成本（%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') | |注意|----- | - 動態(tài)統計信息used: 動態(tài)采樣（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|執(zhí)行|StmtText |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument |DefinedValues|EstimateRows|EstimateIO |EstimateCPU |平均行大小| TotalSubtreeCost |輸出列表|警告|類型|并行|估計執(zhí)行|----|--------|------------------------ -------------------------------------------------- -------------------------------|------|------|---- --|----------|----------|---------------------------- -------------------------------------------------- -------------|------------------------------------ ---- ----------|----------------|------------------ ---|---- ------------------|----------|------------ --------- |---------------------------------------- ----------|-- ------|--------|--------|------------ ------| 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 子句都可以使用索引（反向掃描），而不需要對任何行進行額外排序?？梢允褂蒙厦娴姆椒ú榭磮?zhí)行計劃。

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

在t3(col2, col1) 上創(chuàng)建索引idx3;將col2放在索引的最左端，兩個查詢都可以利用該索引；即復合索引遵循最左前綴原則。另外，基于col2再創(chuàng)建一個索引會導致索引重復，這并不是一個好的解決方案。

問題4的答案是：B，存在性能問題。因為LIKE條件下以通配符%或_開頭的字符串無法建立索引。但是，以下語句可以使用索引：

SELECT * FROM t4 WHERE col2 LIKE 'sql%';對于PostgreSQL，創(chuàng)建索引時還需要指定操作符類：

-- PostgreSQLCREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);問題5 答案是：A、第一次查詢比較快。因為它只需要掃描索引（Index-Only Scan）即可得到結果；雖然第二個查詢可能返回的數據較少，但是需要通過索引來訪問表，即返回表。

親愛的朋友，你答對了幾個呢？歡迎留言討論！