美国留学

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

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

问题1

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

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

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

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

下表中的索引有问题吗？

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

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

创建表t5 ( id INT NOT NULL, col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (id));在t5(col1, col3) 上创建索引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命令查看语句的执行计划。最好先对表格进行统计分析：

-- OracleEXPLAIN PLAN FORSELECT * FROM t1 WHERE TO_CHAR(dt, 'YYYY')='2019';SELECT * FROM TABLE(dbms_xplan.display);PLAN_TABLE_OUTPUT |---------------- -------------------------------------------------- --------|计划哈希值： 3617692013 | |------------------------------------------------ -------------- ----------------------------------||身份证|运营|名称|行|字节|成本（%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') | |注意|----- | - 动态统计信息used: 动态采样（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|执行|StmtText |StmtId|NodeId|Parent|PhysicalOp|LogicalOp |Argument |DefinedValues|EstimateRows|EstimateIO |EstimateCPU |平均行大小| TotalSubtreeCost |输出列表|警告|类型|并行|估计执行|----|--------|------------------------ -------------------------------------------------- -------------------------------|------|------|---- --|----------|----------|---------------------------- -------------------------------------------------- -------------|------------------------------------ ---- ----------|----------------|------------------ ---|---- ------------------|----------|------------ --------- |---------------------------------------- ----------|-- ------|--------|--------|------------ ------| 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 子句都可以使用索引（反向扫描），而不需要对任何行进行额外排序。可以使用上面的方法查看执行计划。

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

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

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

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

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

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