作者|董旭阳TonyDong，CSDN博客专家

编辑|唐晓音

标题图片| CSDN东方IC下载

出品| CSDN博客

下面是关于索引和SQL查询性能的5个测试题；其中4题为二选题，1题为三选题。您只需答对3 个即可通过。是不是看起来很容易？但真正通过的只有40%。我们会在试题的最后提供答案分析，但建议您先尝试一下，看看您答对了多少个！

问题1

以下查询语句是否存在性能问题？

创建表t1 (id INT NOT ,dt DATE,主键(id));在t1(dt) 上创建索引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

以下查询语句是否存在性能问题？

创建表t2 (id INT NOT ,i INTdt DATE,v VARCHAR(50),主键(id));在t2(i, dt) 上创建索引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

下表中的索引有问题吗？

创建表t3 (id INT NOT ,col1 INT,col2 INT,col3 VARCHAR(50),主键(id));在t3(col1, col2) 上创建索引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

如果有下面的表和两条查询语句，哪个查询更快？

创建表t5 (id INT NOT ,col1 INT,col2 INT,col3 VARCHAR(50),主键(id));在t5(col1, col3) 上创建索引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、存在性能问题。因为在索引字段上使用函数或表达式会导致索引失败。

可以使用EXPLAIN命令查看语句的执行计划。最好先对表格进行统计分析：

-- OracleEXPLAIN PLAN FORSELECT *FROM t1WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- - ------------------------------------------------- - ------|计划哈希值： 3617692013 ||---------------------------------------- -------- ------------------------------------------ -----||身份证|运营|名称|行|字节|成本（%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') ||注意|----- |- 动态统计信息used:动态采样(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虽然使用了索引，但也需要进行转换和判断；这不是最佳解决方案。

接下来是SQL Server：

-- SQL ServerSET STATISTICS PROFILE ONSELECT *FROM t1WHERE datepart(yyyy, dt)='2019';Rows|执行|StmtText |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument |DefinedValues|EstimateRows|EstimateIO |EstimateCPU|AvgRowSize |TotalSub treeCost |输出列表|警告|类型|并行|估计执行次数|----|--------|------------------------ --- ---------------------------------------------------------- --- ------------------------------------------|------|------|- ---- -|----------|----------|------------------------ ---- ---------------------------------------------------------- ---- ------------|--------------------------------- ------------ ---------|----------------|------------ ----------|----- ----------------|----------|----- ---------------| -------------------------------------------------- |--- -----|--------|--------|--------------------|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)) | [人力资源数据库]。 [德博]。 [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使用了索引，但是索引也需要修改转换判断；不是最好的解决方案。

最后看一下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；注意：使用函数索引并不是最佳解决方案。只能用于特定的查询条件；如果查询条件发生变化，变成TO_CHAR(dt, 'YYYY-MM-DD')='2019-06-01' 或其他形式，则无法使用索引。

问题2

答案是：A、性能没有问题。该语句的WHERE 子句和ORDER BY 子句都可以使用索引（反向扫描），而不需要对任何行进行额外排序。可以使用上面的方法来查看执行计划。

问题3

答案是：B、索引有问题。因为第二个查询不能使用索引或者效率不高。虽然有些数据库可能会使用索引跳过扫描，但通过修改索引字段的顺序可以获得更好的性能：

在t3(col2, col1) 上创建索引idx3;将col2放在索引的最左端，两个查询都可以使用该索引；即复合索引遵循最左前缀原则。另外，基于col2再创建一个索引会导致索引重复，这并不是一个好的解决方案。

问题4

答案是：B、存在性能问题。因为LIKE条件下以通配符%或_开头的字符串无法建立索引。但是，以下语句可以使用索引：

SELECT *FROM t4WHERE col2 LIKE 'sql%';对于PostgreSQL，创建索引时还需要指定操作符类：

-- PostgreSQLCREATE INDEX idx4 ON t4(col2 varchar_pattern_ops);问题5

答案是：A，第一次查询比较快。因为它只需要扫描索引（Index-Only Scan）即可得到结果；虽然第二个查询可能返回的数据较少，但是需要通过索引来访问表，即返回表。

亲爱的朋友，你答对了几个呢？欢迎留言讨论！

文章发表已获得作者授权，CSDN博客地址：

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