SQL优化-使用联合索引和函数索引

简介: 在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。

在用户的一次例行巡检中,发现了一条sql语句的CPU利用率很高,这条语句在查询中使用了to_char函数将日期转为字符串,比如下面这样的形式,

select col1 from tablename where col2=XXXX and to_char(col3 , 'YYYY-MM-DD') = to_char(sysdate, 'YYYY-MM-DD')

对这样的语句进行优化的第一步是对where条件里各列的选择性进行分析,选择性强的列加上索引,如果列组合起来的选择性更强,则创建复合索引。这条语句由于对日期列使用了函数运算,如果需要在col3上创建索引,oracle数据库在执行这条语句时,普通的b-tree索引是无法使用的,这时需要创建函数索引或者是在表上加一个虚拟列,在此虚拟列上创建索引。比如下面这样的表

SQL> desc BENCHMARKSQL.BMSQL_HISTORY;  Name                                      Null?    Type  ----------------------------------------- -------- ---------------------------- H_C_ID                                             NUMBER(38)  H_C_D_ID                                           NUMBER(38)  H_C_W_ID                                           NUMBER(38)  H_D_ID                                             NUMBER(38)  H_W_ID                                             NUMBER(38)  H_DATE                                             TIMESTAMP(6)  H_AMOUNT                                           NUMBER(6,2)  H_DATA                                             VARCHAR2(24)

运行下面的sql语句

select h_data from BENCHMARKSQL.BMSQL_HISTORY where H_C_ID=2758 and to_char(H_DATE,'YYYY-MM-DD')='2025-05-29';

1 没有任何索引的情况下的执行效率

   -----------------------------------------------------------------------------------            | Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |             -----------------------------------------------------------------------------------            |   0 | SELECT STATEMENT  |               |     1 |    29 |   342   (1)| 00:00:01 |             |*  1 |  TABLE ACCESS FULL| BMSQL_HISTORY |     1 |    29 |   342   (1)| 00:00:01 |             -----------------------------------------------------------------------------------             Predicate Information (identified by operation id):             ---------------------------------------------------             PLAN_TABLE_OUTPUT             ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M                           M-DD')='2024-12-10')

oracle在执行这条语句时选择了全表扫描,执行的成本是342,全表扫描过滤后返回了表中的一行数据。

2 在其中的一列上加上索引

2.1 在H_C_ID列上创建索引

create index idx_id on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID);

创建索引后对表进行分析,更新一下表的统计信息

analyze table  BENCHMARKSQL.BMSQL_HISTORY compute statistics;

现在这条语句的执行计划如下

-----------------------------------------------------------------------------------------------------
              | Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                    |               |     1 |    29 |    52   (0)| 00:00:01 |
              |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY |     1 |    29 |    52   (0)| 00:00:01 |
              |*  2 |   INDEX RANGE SCAN                  | IDX_          |    50 |       |     1   (0)| 00:00:01 |
              -----------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              
              PLAN_TABLE_OUTPUT
              ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              ---------------------------------------------------
              
                 1 - filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10')
                 2 - access("H_C_ID"=2758)

oracle在执行语句时选择了刚才创建的索引,在索引上执行了范围扫描,回表后根据H_DATE列进行了过滤,范围扫描的成本很低,这条语句的执行成本主要在id为1的操作,这个操作根据范围扫描得到的ROWID查找表中的数据,然后过滤。这里的数据库版本是Oracle 23AI,Oracle还是对这个操作进行了优化,这从操作后面的BATCHED 可以看出。回表的成本是比较高的,不做这条语句的总成本是52,比全表扫描时低了不少。

2.2 在H_DATE上创建索引

2.2.1 创建普通索引

create index idx_date on BENCHMARKSQL.BMSQL_HISTORY(H_DATE);

对表进行分析后,执行计划如下:

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    29 |   342   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BMSQL_HISTORY |     1 |    29 |   342   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M
              M-DD')='2025-05-29')

Oracle在执行这条语句时没有选择索引,而是进行了全表扫描,这时由于sql语句的选择条件对列进行了函数运算的缘故。

2.2.2 创建函数索引
create index idx_date_f on BENCHMARKSQL.BMSQL_HISTORY(to_char(H_DATE,'YYYY-MM-DD'));

执行前面的表分析语句对表进行分析后,语句的执行计划是这样的

Plan hash value: 750191248 -----------------------------------------------------------------------------------| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |               |     1 |    32 |   342   (1)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| BMSQL_HISTORY |     1 |    32 |   342   (1)| 00:00:01 |----------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    1 - filter("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-M               M-DD')='2025-05-29')

Oracle并没有使用刚刚创建的函数索引,仍然选择了全表扫描。问题出在哪里呢?可能想到的是统计信息的问题,可是在创建索引后我们已经执行了表分析命令,更新了统计信息,使用hint强制Oracle使用刚才创建的函数索引,看一下执行计划

Plan hash value: 2550728382 -----------------------------------------------------------------------------------------------------| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                    |               |     1 |    35 |   352   (0)| 00:00:01 | |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY |     1 |    35 |   352   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN                  | IDX_DATE_F    |   413 |       |     4   (0)| 00:00:01 |----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    1 - filter("H_C_ID"=2758)    2 - access(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05-29') 15 rows selected.

这次Oracle数据库使用了刚才创建的函数索引,id为2的操作在创建的函数索引上进行了范围扫描,返回的行数为413行,查询数据表,依据语句中的where条件,确实返回了413行,这说明统计信息时准确的,Oracle因为回表成本过高而没有选择函数索引而选择了全表扫描。

2.2.3 换一条语句试试

刚才的语句数据库由于成本问题而没有使用函数索引,换一条语句试一下:

select count(*) from BENCHMARKSQL.BMSQL_HISTORY where to_char(H_DATE,'YYYY-MM-DD')='2025-05-29';

在有函数索引时,语句的执行计划如下:

--------------------------------------------------------------------------------| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |            |     1 |    11 |     4   (0)| 00:00:01 | |   1 |  SORT AGGREGATE   |            |     1 |    11 |            |          | |*  2 |   INDEX RANGE SCAN| IDX_DATE_F |   413 |  4543 |     4   (0)| 00:00:01 |-------------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    2 - access(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05               -29')

Oracle执行语句时选择了函数索引,由于这里不用回表,语句的执行成本基本就是范围扫描的成本。

在没有函数索引时,语句的执行计划如下:

------------------------------------------------------------------------------------| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |               |     1 |     7 |   343   (1)| 00:00:01 | |   1 |  SORT AGGREGATE    |               |     1 |     7 |            |          | |*  2 |   TABLE ACCESS FULL| BMSQL_HISTORY |  1511 | 10577 |   343   (1)| 00:00:01 |------------------------------------------------------------------------------------ Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    2 - filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2025-05-29'              )

全表扫描的成本为300多,远高于基于索引的范围扫描。

2.3 使用虚拟列

在创建函数索引时,Oracle会在相应的数据库表上创建一个隐藏列。这个列的信息在DBA_TAB_COLS视图里可以查询到。也可以使用虚拟列来达到和函数索引一样的效果。现在表上增加一个虚拟列,然后在此虚拟列上创建索引。在表上增加虚拟列前,要删除之前创建的列上的函数索引。否则,在增加虚拟列时会报重复的表达式错误,增加虚拟列会失败。在BMSQL_HISTORY表上增加虚拟列的语句如下:

alter table BENCHMARKSQL.BMSQL_HISTORY add (v_h_day as (to_char(H_DATE,'YYYY-MM-DD')) virtual);

增加虚拟列成功后,就可以在这个列上创建索引了。

create index idx_date_f on BENCHMARKSQL.BMSQL_HISTORY(to_char(H_DATE,'YYYY-MM-DD'));

然后再看一下前面的语句的执行计划

SQL>  explain plan for select count(*) from BENCHMARKSQL.BMSQL_HISTORY where to_char(H_DATE,'YYYY-MM-DD')='2025-05-29'; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 913241418 ---------------------------------------------------------------------------------| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |             |     1 |    11 |     4   (0)| 00:00:01 | |   1 |  SORT AGGREGATE   |             |     1 |    11 |            |          | |*  2 |   INDEX RANGE SCAN| IDX_V_H_DAY |   413 |  4543 |     4   (0)| 00:00:01 |--------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------    2 - access("BMSQL_HISTORY"."V_H_DAY"='2025-05-29') 14 rows selected.

Oracle使用了刚刚创建的索引进行了范围扫描。可以看到,在where条件中执行有对列的函数计算时,Oracle会自动检查相应的虚拟列,不需要我们把where条件改写为相应的虚拟列的形式。

虚拟列和函数索引的实现原理基本上相同的,具体选用哪种方式可以凭个人习惯和喜好决定,它们的实现的效果也相同。

3 创建复合索引

3.1 复合索引里没有函数表达式

从全表扫描的执行计划里,可以看到,在执行了where里面的筛选条件之后,返回的数据只有一行。从这一点可以看出,这两个条件组合起来,可能选出的行更少。如果想要得出具体的结论,需要对查询表,对表的数据的分布有更为准确的了解。在本例中这个表依据这两个条件的数据分布可用下面的语句来查询

select H_C_ID,to_char(H_DATE,'YYYY-MM-DD'),count(*) from BENCHMARKSQL.BMSQL_HISTORY group by H_C_ID,to_char(H_DATE,'YYYY-MM-DD')  having count(*)>2;

查询出来的结果没有大于5的,因此,对于这条语句创建复合索引可以达到很好的优化效果

create index idx_id_date on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID,H_DATE);

创建复合索引时,选择性较强的列放在前面,这里H_C_ID列的选择性更强,所以放在前面,对表分析后,执行计划如下:

 -----------------------------------------------------------------------------------------------------                | Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                 -----------------------------------------------------------------------------------------------------                |   0 | SELECT STATEMENT                    |               |     1 |    32 |     3   (0)| 00:00:01 |                 |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY |     1 |    32 |     3   (0)| 00:00:01 |                 |*  2 |   INDEX RANGE SCAN                  | IDX_ID_DATE   |     1 |       |     2   (0)| 00:00:01 |                 -----------------------------------------------------------------------------------------------------                 Predicate Information (identified by operation id):                 PLAN_TABLE_OUTPUT                 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                ---------------------------------------------------                    2 - access("H_C_ID"=2758)                        filter(TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10')

创建了复合索引后,语句的执行成本降低到3,这里id为2的操作显示依据H_C_ID列进行了范围扫描,由于h_date列也是复合索引的列,所以这里不用回表就可以依据h_date列进行过滤,减少了需要回表的rowid数量,降低了整个语句的执行成本。

3.2 复合索引里包含函数表达式

上一节的执行计划里,范围扫描依据的表达式是"H_C_ID"=2758,函数表达式条件则作为范围扫描的过滤条件。这里,可以做的更彻底一点,是函数表达式条件也作为范围扫描的依据。为此,创建以下索引

create index idx_func on BENCHMARKSQL.BMSQL_HISTORY(H_C_ID,to_char(H_DATE,'YYYY-MM-DD'));

索引创建后,Oracle并没有选择这个索引进行范围扫描,还是选择了上一节的索引。将上一节创建的索引drop掉后,上面的语句的执行计划如下

 -----------------------------------------------------------------------------------------------------              | Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |               -----------------------------------------------------------------------------------------------------              |   0 | SELECT STATEMENT                    |               |     1 |    32 |     5   (0)| 00:00:01 |               |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BMSQL_HISTORY |     1 |    32 |     5   (0)| 00:00:01 |               |*  2 |   INDEX RANGE SCAN                  | IDX_FUNC      |     1 |       |     3   (0)| 00:00:01 |               -----------------------------------------------------------------------------------------------------               Predicate Information (identified by operation id):               PLAN_TABLE_OUTPUT               ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------              ---------------------------------------------------                  2 - access("H_C_ID"=2758 AND TO_CHAR(INTERNAL_FUNCTION("H_DATE"),'YYYY-MM-DD')='2024-12-10                             ')               15 rows selected.

范围扫描的成本反而高于使用前面的索引时,在这个例子中,同时基于两个条件进行范围扫描的执行效率反而不如基于一个条件进行扫描然后进行过滤。想来是第二个条件过滤掉的数据比较少,达不到减少扫描数据的目的,反而影响了扫描效率。

相关文章
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
143 2
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
573 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
2月前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
2月前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中,COUNT函数是一个非常常用的聚合函数,用于计算表中满足特定条件的记录数
|
3月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
3月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤