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

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

問(wèn)題1

以下查詢(xún)語(yǔ)句是否存在性能問(wèn)題？

創(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 選項(xiàng)A：沒(méi)問(wèn)題；

選項(xiàng)B：有問(wèn)題。

問(wèn)題2

以下查詢(xún)語(yǔ)句是否存在性能問(wèn)題？

創(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 選項(xiàng)A：沒(méi)問(wèn)題；

選項(xiàng)B：有問(wèn)題。

問(wèn)題3

下表中的索引有問(wèn)題嗎？

創(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;選項(xiàng)A：沒(méi)問(wèn)題；

選項(xiàng)B：有問(wèn)題。

問(wèn)題4

以下查詢(xún)語(yǔ)句是否存在性能問(wèn)題？

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;選項(xiàng)A：沒(méi)問(wèn)題；

選項(xiàng)B：有問(wèn)題。

問(wèn)題5

如果有下面的表和兩條查詢(xún)語(yǔ)句，哪個(gè)查詢(xún)更快？

創(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;選項(xiàng)A：第一個(gè)查詢(xún)更快；

選項(xiàng)B：第二次查詢(xún)速度更快；

選項(xiàng)C：兩個(gè)查詢(xún)的性能大致相同。

解析

問(wèn)題1的答案是：B、存在性能問(wèn)題。因?yàn)樵谒饕侄紊鲜褂煤瘮?shù)或表達(dá)式會(huì)導(dǎo)致索引失敗。

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

-- OracleEXPLAIN PLAN FORSELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- -------------------------------------------------- --------|計(jì)劃哈希值： 3617692013 | |------------------------------------------------ -------------- ----------------------------------||身份證|運(yùn)營(yíng)|名稱(chēng)|行|字節(jié)|成本（%CPU）|時(shí)間||------------------------------------------------------------ ---------------------------------|| 0 |選擇語(yǔ)句| | 1 | 22 | 22 2 (0) | 2 (0) | 00:00:01 |||* 1 |表訪問(wèn)已滿(mǎn)| T1 | 1 | 22 | 22 2 (0)| 00:00:01 ||------------------------ -------------------------- --------------------------- | |謂詞信息（由操作id 標(biāo)識(shí)）: |---------------------------------------------------- -- ------------- | | 1 - 過(guò)濾器(TO_CHAR(INTERNAL_FUNCTION('DT'),'YYYY')='2019') | |注意|----- | - 動(dòng)態(tài)統(tǒng)計(jì)信息used: 動(dòng)態(tài)采樣（level=2） Oracle 使用全表掃描，沒(méi)有索引。再看看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 |簡(jiǎn)單|t1 | |索引| |idx1|4 | | 1| 100|使用地點(diǎn)； MySQL雖然使用了索引，但也需要對(duì)索引進(jìn)行轉(zhuǎn)換判斷；這不是最佳解決方案。

接下來(lái)是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 |輸出列表|警告|類(lèi)型|并行|估計(jì)執(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使用了索引，但是索引也需要進(jìn)行轉(zhuǎn)換判斷；這不是最佳解決方案。

最后看一下PostgreSQL：

-- PostgreSQLEXPLAIN SELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';查詢(xún)計(jì)劃|---------------------------------------- -------------------------------------------------- ----|t1 上的順序掃描（成本=0.00.49.55 行=11 寬度=8）| Filter: (to_char((dt):timestamp with time zone, 'YYYY':text)='2019':text)|PostgreSQL 使用是全表掃描，不使用索引。

正確的做法是修改查詢(xún)語(yǔ)句：

SELECT * FROM t WHERE dt BETWEEN '2019-01-01' 和日期'2019-12-31' 之間；注意：使用函數(shù)索引并不是最佳解決方案。只能用于特定的查詢(xún)條件；如果查詢(xún)條件改為T(mén)O_CHAR(dt, 'YYYY-MM-DD')='2019-06-01' 或其他形式的話，則無(wú)法使用索引。

問(wèn)題2的答案是：A、性能沒(méi)有問(wèn)題。該語(yǔ)句的WHERE 子句和ORDER BY 子句都可以使用索引（反向掃描），而不需要對(duì)任何行進(jìn)行額外排序?？梢允褂蒙厦娴姆椒▉?lái)查看執(zhí)行計(jì)劃。

問(wèn)題3的答案是：B、索引有問(wèn)題。因?yàn)榈诙€(gè)查詢(xún)不能使用索引或者效率不高。雖然有些數(shù)據(jù)庫(kù)可能會(huì)使用索引跳過(guò)掃描，但通過(guò)修改索引字段的順序可以獲得更好的性能：

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

問(wèn)題4的答案是：B，存在性能問(wèn)題。因?yàn)長(zhǎng)IKE條件下以通配符%或_開(kāi)頭的字符串無(wú)法建立索引。但是，以下語(yǔ)句可以使用索引：

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

-- PostgreSQLCREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);問(wèn)題5 答案是：A、第一次查詢(xún)比較快。因?yàn)樗恍枰獟呙杷饕↖ndex-Only Scan）即可得到結(jié)果；雖然第二個(gè)查詢(xún)可能返回的數(shù)據(jù)較少，但是需要通過(guò)索引來(lái)訪問(wèn)表，即返回表。

親愛(ài)的朋友，你答對(duì)了幾個(gè)呢？歡迎留言討論！