在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来
解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致DML性能的下降。本文描述的是一个索引列上使用函数使
其失效的案例。
一、数据版本与原始语句及相关信息
1.版本信息
2.原始语句与其执行计划
从执行计划可以看出,SQL语句使用了全表扫描,而where 子句中只有唯一的一列business_date
3.表上的索引信息
从索引的情况上来看有一个基于主键的索引包含了BUSINESS_DATE列,而查询语句并没有走索引而是选择的全表扫描,而且预估所返回
的行Rows与bytes也是大的惊人,cost的值96399,接近10W。
二、分析与改造SQL语句
1.原始的SQL语句分析
SQL语句中where子句的business_date列实现对记录过滤
business_date <= '20110728'条件不会限制索引的使用
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函数,限制了优化器选择索引
基于business_date列来建立索引函数,从已存在的索引来看,必要性不大
2.改造SQL语句
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的实质是等于当月,即限制返回的行为从2011.7.1日至2011.7.28
因此其返回的记录大于等于2011.7.1,且小于2011.7.28
做如下改造
business_date >=to_char(last_day(add_months(to_date('20110728','yyyymmdd'),-1)) + 1,'yyyymmdd')
3.改造后的SQL语句
4.改造后的执行计划
改造后可以看到SQL语句的执行计划已经由原来的全表扫描改为执行INDEX SKIP SCAN,但其cost也并没有降低多少
三、进一步分析
1.表的相关信息
2.索引的相关信息
3.尝试在BUSINESS_DATE列上创建索引
建立索引后聚簇因子较小,差不多接近表上块的数量
4.使用新创建索引后的执行计划
从上面的执行计划看出,SQL语句已经选择了新建的索引
尽管返回的rows,bytes没有明显的变化,但cost已经少了近7倍。
分享到:
相关推荐
此外,对索引列进行了隐式转换也可能导致索引失效。另外,索引失效还可能与查询的条件有关,例如在进行范围查询时,MySQL可能无法使用索引。 针对这些问题,可以通过优化查询语句来解决。避免对索引列进行函数操作...
比如,如果索引列是"timestamp"类型,但查询条件中使用了函数对该列进行了处理,索引可能失效。 表达式和函数的使用:当查询中使用了大量表达式或函数来处理列时,MySQL可能无法使用索引。这是因为MySQL只能在索引...
• 在索引列上使用mysql的内置函数,索引失效。 • 对索引列运算(如,+、-、*、/),索引失效。 • 索引字段上使用(!= 或者 ,not in)时,可能会导致索引失效。 • 索引字段上使用is null, is not null,可能...
1. 随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO计算走索引花费大于走全表) 2. 统计信息失效 需要重新搜集统计信息 3. 索引本身失效 需要重建索引 下面是一些不会使用到索引的原因 ...
在索引列上使用mysql的内置函数,索引失效。 对索引列运算(如,+、-、*、/),索引失效。 索引字段上使用(!= 或者 ,not in)时,可能会导致索引失效。 索引字段上使用is null, is not null,可能导致索引失效。...
在索引列上使用mysql的内置函数,索引失效。 对索引列运算(如,+、-、*、/),索引失效。 索引字段上使用(!= 或者 ,not in)时,可能会导致索引失效。 索引字段上使用is null, is not null,可能导致索引...
3.2 索引列参与了运算,会导致全表扫描,索引失效(除主键索引外) 3.3 模糊查询时(like语句),模糊匹配的占位符位于条件的首部 3.4 索引列参使用了函数 3.5 参数类型与字段类型不匹配,导致类型发生了隐式转换,...
在索引列上使用mysql的内置函数,索引失效。 对索引列运算(如,+、-、*、/),索引失效。 索引字段上使用(!= 或者 ,not in)时,可能会导致索引失效。 索引字段上使用is null, is not null,...
2 索引失效 2.1like后面的通配符在前面,索引会失效。 2.2 没有使用联合索引的第一列,not in,!=,使用MySQL函数,类型转换,or等都无法用到索引。 3 SQL和索引的优化 3.1 慢查询日志 3.2 查磁盘的I/O读写的数据量 ...
1.29 强制索引失效 18 1.30 避免在索引列上使用计算 19 1.31 自动选择索引 20 1.32 避免在索引列上使用NOT 20 1.33 用>=替代> 21 1.34 用UNION替换OR (适用于索引列) 21 1.35 用IN来替换OR 22 1.36 避免在索引列上...
实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对...在sql语句中,将索引列作为表达式的一部分、参与函数/数学等运算,将会导致索引失
oracle 时间函数
这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。 使用的是mysql 5.6版本,innoDB引擎 实际情况如下 下面我们来看一下执行的结果 在上面的描述中我们还得...
索引无效原因 最近遇到一个Oracle SQL语句的性能问题,修改功能之前的运行时间平均为0.3s,可是添加新功能后,时间达到了4~5s。虽然几张表的数据量都比较大(都在百万级以上),但是也都有正确创建索引,不知道到底...
4.4 索引失效 5.表空间 6.表分区 6.1表空间及分区表的概念 6.2表分区的具体作用 6.3.表分区的优缺点 6.4表分区的几种类型及操作方法 6.5有关表分区的一些维护性操作 第5章 PL/SQL程序设计 1. PL/SQL简介 ...
1. 索引失效 1.1 普通索引 场景一:SELECT * FROM user_info WHERE id + 1 = 2; 索引的列如果是表达式的一部分或者是函数的参数,则失效。 场景二:SELECT * FROM user_info WHERE name LIKE '%ook'; like查询前面...
但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引,不走全表扫描。 2、like keyword% 索引有效。 3、like %keyword% 索引失效,也无法使用反向索引。 使用mysql的explain简单测试如下: ...
10.1.2 函数索引 271 10.1.3 反向键索引 272 10.1.4 全文索引 272 10.2 走进其他索引的世界 272 10.2.1 位图索引 273 10.2.2 函数索引 278 10.2.3 反向键索引 282 10.2.4 全文索引 282 10.3 其他索引的相关...
10.1.2 函数索引 271 10.1.3 反向键索引 272 10.1.4 全文索引 272 10.2 走进其他索引的世界 272 10.2.1 位图索引 273 10.2.2 函数索引 278 10.2.3 反向键索引 282 10.2.4 全文索引 282 10.3 其他索引的相关...
一、MYSQL随机产生指定范围的字符串;...十一、设置/取消唯一索引; 十二、MYSQL事件失效; 十三、解决MYSQL表被锁住的问题; 十四、MYSQL日期函数大全; 十五、终止MYSQL存储过程; 十六、MYSQL存储过程