作者|董旭陽TonyDong，CSDN博客專家

編輯|唐曉音

標題圖片| CSDN東方IC下載

出品| CSDN博客

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

問題1

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

創(chuàng)建表t1 (id INT NOT ,dt DATE,主鍵(id));在t1(dt) 上創(chuàng)建索引idx1;SELECT *FROM t1WHERE TO_CHAR(dt, 'YYYY')='2019'; -- Oracle、PostgreSQL- - WHERE YEAR(dt)='2019'; -- MySQL -- WHERE datepart(yyyy, dt)='2019'; -- SQL Server選項A：沒問題；

選項B：有問題。

問題2

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

創(chuàng)建表t2 (id INT NOT ,i INTdt DATE,v VARCHAR(50),主鍵(id));在t2(i, dt) 上創(chuàng)建索引idx2;SELECT *FROM t2WHERE i=99ORDER BY dt DESCFETCH 僅前5 行； -- Oracle、SQL Server、PostgreSQL -- OFFSET 0 ROWS 僅獲取前5 行； -- SQL Server -- 限制5； -- MySQL選項A：沒問題；

選項B：有問題。

問題3

下表中的索引有問題嗎？

創(chuàng)建表t3 (id INT NOT ,col1 INT,col2 INT,col3 VARCHAR(50),主鍵(id));在t3(col1, col2) 上創(chuàng)建索引idx3;SELECT *FROM t3WHERE col1=99AND col2=10;SELECT *來自t3，其中col2=10；選項A：沒問題；

選項B：有問題。

問題4

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

CREATE TABLE t4 (id INT NOT ,col1 INT,col2 VARCHAR(50),PRIMARY KEY (id));CREATE INDEX idx4 ON t4(col2);SELECT *FROM t4WHERE col2 LIKE '%sql%';選項A：沒問題；

選項B：有問題。

問題5

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

創(chuàng)建表t5 (id INT NOT ,col1 INT,col2 INT,col3 VARCHAR(50),主鍵(id));在t5(col1, col3) 上創(chuàng)建索引idx5;選擇col3, count(*)FROM t5WHERE col1=99GROUP BY col3;SELECT col3, count(*)FROM t5WHERE col1=99AND col2=10GROUP BY col3;選項A：第一個查詢更快；

選項B：第二次查詢速度更快；

選項C：兩個查詢的性能大致相同。

解析

問題1

答案是：B、存在性能問題。因為在索引字段上使用函數(shù)或表達式會導致索引失敗。

可以使用EXPLAIN命令查看語句的執(zhí)行計劃。最好先對表格進行統(tǒng)計分析：

-- OracleEXPLAIN PLAN FORSELECT *FROM t1WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- - ------------------------------------------------- - ------|計劃哈希值： 3617692013 ||---------------------------------------- -------- ------------------------------------------ -----||身份證|運營|名稱|行|字節(jié)|成本（%CPU）|時間||------------------------------------------------------------ ----------------------------------|| 0 |選擇語句| | 1 | 22 | 22 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)統(tǒng)計信息used:動態(tài)采樣(level=2)|Oracle是全表掃描，沒有索引。再看看MySQL：

-- MySQLEXPLAIN SELECT *FROM t1WHERE 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雖然使用了索引，但也需要進行轉(zhuǎn)換和判斷；這不是最佳解決方案。

接下來是SQL Server：

-- SQL ServerSET STATISTICS PROFILE ONSELECT *FROM t1WHERE datepart(yyyy, dt)='2019';Rows|執(zhí)行|StmtText |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument |DefinedValues|EstimateRows|EstimateIO |EstimateCPU|AvgRowSize |TotalSub treeCost |輸出列表|警告|類型|并行|估計執(zhí)行次數(shù)|----|--------|------------------------ --- ---------------------------------------------------------- --- ------------------------------------------|------|------|- ---- -|----------|----------|------------------------ ---- ---------------------------------------------------------- ---- ------------|--------------------------------- ------------ ---------|----------------|------------ ----------|----- ----------------|----------|----- ---------------| -------------------------------------------------- |--- -----|--------|--------|--------------------|0| 1|從t1 中選擇*，其中datepart(yyyy, dt)='2019' | 1| 1| 0| | | | | 1| | |0.0032830999698489904| | 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)) | [人力資源數(shù)據(jù)庫]。 [德博]。 [T1]。 [ID]，[hrdb]。 [德博]。 [DT] | 1 | 0.003125000046661287 | 1.5809999604243785e-4 | 14 | 14 0.003289999999904 | [[[ hrdb].[dbo].[t1].[id],[hrdb].[dbo].[t1].[dt]| |計劃行| 0| 1|SQL Server使用了索引，但是索引也需要修改轉(zhuǎn)換判斷；不是最好的解決方案。

最后看一下PostgreSQL：

-- PostgreSQLEXPLAIN SELECT *FROM t1WHERE TO_CHAR(dt, 'YYYY')='2019';查詢計劃|---------------------------- -------------------------------------------------- ---|t1 上的序列掃描(cost=0.00.49.55 rows=11 width=8) |Filter: (to_char((dt):timestamp with time zone, 'YYYY':text)='2019':text)|由PostgreSQL 使用這是不使用索引的全表掃描。

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

選擇*FROM tWHERE dt BETWEEN '2019-01-01' 和日期'2019-12-31' 之間的dt；注意：使用函數(shù)索引并不是最佳解決方案。只能用于特定的查詢條件；如果查詢條件發(fā)生變化，變成TO_CHAR(dt, 'YYYY-MM-DD')='2019-06-01' 或其他形式，則無法使用索引。

問題2

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

問題3

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

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

問題4

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

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

-- PostgreSQLCREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);問題5

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

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

文章發(fā)表已獲得作者授權(quán)，CSDN博客地址：

https://blog.csdn.net/horses/article/details/103028340