不知不觉让数据库索引失效的“坑”

阅读目录(Content)

• 前言
• 坑1：隐式类型转换
• 问题描述
• 问题分析
• 解决方案
• 经验总结
• 坑2：条件字段做了“函数操作”
• 什么是“函数操作”？
• “函数操作”为何导致表面上的“索引失效”？
• 经验总结
• 坑3：隐式字符集编码转换
• 问题描述
• 解决方案
• 经验总结

 

回到顶部(go to top)

前言

提到 “数据库的查询耗时过长”， 很多情况下都与索引有关系：

• 或是因为索引忘记创建
• 或是因为SQL查询条件未满足“最左前缀原则”

以上问题的迷惑性和隐蔽性还不太强，一般都可以迅速发现root cause并拿出解决方案。

 

但是如果编程人员已经明确：

• 我已经加了所需的索引
• 我写的SQL也满足了所谓的“最左前缀原则”

却依然出现了查询超时的问题，就很容易让人费解。

 

为什么明明设置了索引，却似乎完全失效了？

本文将结合作者自己的实际经验，分享几个不知不觉让索引失效的“坑”。

 

回到顶部(go to top)

坑1：隐式类型转换

问题描述

笔者负责的可比券管理服务，需要获取某个债券（以2105778.IB）在一段时间里的中债估值信息，以绘制其估值价格走势。

因此需要查询 FICC_DWODS.FICC_RDS_BOND_VALUATION 这张表以获取中债估值信息。

 

在编写sql之前，笔者已提前查阅了该表的索引情况，并计划使用红框中的索引进行查询。

 

因此具体sql如下，满足最左前缀原则：

select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where 
TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and 
HEADS_SECURITY_ID in ('2105778.IB') and 
VALUATION_SOURCE  IN ('chinabond')

 

令人意外的是，该SQL在生产环境上，出现了查询超时的错误。

 

问题分析

日志显示，前后两个请求语句相差的时间是，2分22秒

• 2023-09-26T14:13:02,065
• 2023-09-26T14:15:24,367

也就是说，查询18条数据就耗时2分22秒。

2023-09-26T14:13:02,065 [ComparableSecurity-thread-1] INFO  com.huatai.nats.api.impl.client.Client - Query done(total=6), sql=select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2105778.IB') and VALUATION_SOURCE  IN ('chinabond')
2023-09-26T14:13:02,065 [ComparableSecurity-thread-1] INFO  com.huatai.quant.utils.ComparableSecurityUtil - Query out [6] results, using sql query = [select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2105778.IB') and VALUATION_SOURCE  IN ('chinabond')]
2023-09-26T14:15:24,367 [ComparableSecurity-thread-1] INFO  com.huatai.nats.api.impl.client.Client - Query done(total=18), sql=select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') and VALUATION_SOURCE  IN ('chinabond')
2023-09-26T14:15:24,367 [ComparableSecurity-thread-1] INFO  com.huatai.quant.utils.ComparableSecurityUtil - Query out [18] results, using sql query = [select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') and VALUATION_SOURCE  IN ('chinabond')]

 

为确认索引是否起效，用Explain语句进行分析：

Explain plan FOR select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where 
TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) 
and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') 
and VALUATION_SOURCE  IN ('chinabond');

--查看索引使用明细
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

 

发现这里是ACCESS FULL，说明是查询了全表，并未走索引。

并且发现系统对TRADE_DATE字段，进行了默认的格式转换TO_NUMBER()

 

而原本TRADE_DATE字段的格式是VARCHAR2(128)，因此推测怀疑是因为格式转换问题，导致未能走索引

 

解决方案

将原SQL里TRADE_DATE的查询值，从数字（20220919）修改为VARCHAR（'20220919'），再次尝试分析是否走索引

Explain plan FOR select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where 
TRADE_DATE in ('20220919','20220920','20220921','20220922','20220923','20220926') 
and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') 
and VALUATION_SOURCE  IN ('chinabond');

--查看索引使用明细
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

 

执行计划显示“TABLE ACCESS BY INDEX”，并且走的索引就是预期的 IDX_RDS_BOND_VALUATION_TRADEDATE_HEADSID

 

经验总结

1.当字符串和数字做比较的话，会默认将 "字符串" 转换成 "数字"

2.新上线的sql, 尽量用explain命令执行一下，确认语句按预期走了索引

 

 

回到顶部(go to top)

坑2：条件字段做了“函数操作”

什么是“函数操作”？

假设有一张表名为TRADE_LOG, 其中字段ID被设置为index, 那么：

 

函数操作，表现为：

• 在“=”左边，对索引字段进行运算，例如：select * from TRADE_LOG where ID + 1 = 10000;

非函数操作，表现为：

• 在“=”右边，进行一些预运算,例如：select * from TRADE_LOG  where ID = function(other_id)

 

查询语句中，对索引字段进行“非函数操作”，并没有影响。

但一旦对索引字段被做了函数操作，数据量很大的情况下会导致耗时急速增加，表面看起来像是“索引失效”了一样。

 

“函数操作”为何导致表面上的“索引失效”？

使用索引字段做“函数操作”时，函数会破坏索引值的“有序性”，导致做全索引扫描而让性能降低。

并非索引失效，依然是通过索引全表扫描；只是无法使用索引的快速搜索能力。

 

经验总结

避免在索引字段上，进行函数操作

 

回到顶部(go to top)

坑3：隐式字符集编码转换

问题描述

该坑容易出现在多张表级联查询时，驱动表设置的字符集，是被驱动表的超集。就会引发索引失效。

 

例如：

有table1, table2两张表，均设置有tradeid索引

但是table1.tradeid的字符集是utf8; table2.tradeid的字符集是utf8mb4

 

那么在下面的SQL里，t2是驱动表，t1是被驱动表：

select t1.* from table1 t1, table2 t2 where t1.tradeid=t2.tradeid and t2.id=2;

 

 

在t1.tradeid=t2.tradeid比较时，t1.tradeid会被迫转换类型为utf8mb4，相当于该SQL变为：

select * from t1 where CONVERT(traideid USING utf8mb4)=$t2.tradeid.value;

 

 

同样会导致索引有序性失效，引起查询耗时大幅增加等情况。

 

解决方案

把驱动表的字符集，转换为和被驱动表一样的，即：

select t1.* from table1 t1, table2 t2 where t1.tradeid=CONVERT (t2.tradeid USING utf8) and t2.id=2;

 

 

经验总结

字符集 utf8mb4 是 utf8 的超集，一旦两者去比较/等价查询，utf8需转换为utf8mb4

 

 

本文若有不准确之处，请各位老师斧正。

谢谢阅读。