在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来
解决索引失效的问题,但如此一来带来的比如磁盘空间的占用以及列上过多的索引导致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倍。
分享到:
相关推荐
如果不是按照索引列最左列进行查找(使用过程中跨列使用索引字段),都会导致索引失效。例如,CREATE INDEX idx_column1_column2 ON table (column1, column2),在查询的时候,必须按照索引列的顺序进行查找,例如,...
此外,对索引列进行了隐式转换也可能导致索引失效。另外,索引失效还可能与查询的条件有关,例如在进行范围查询时,MySQL可能无法使用索引。 针对这些问题,可以通过优化查询语句来解决。避免对索引列进行函数操作...
比如,如果索引列是"timestamp"类型,但查询条件中使用了函数对该列进行了处理,索引可能失效。 表达式和函数的使用:当查询中使用了大量表达式或函数来处理列时,MySQL可能无法使用索引。这是因为MySQL只能在索引...
3. **范围查询**:如果查询条件包含索引列的范围,如`WHERE column BETWEEN a AND b`,MySQL通常只能使用索引的一部分,导致部分索引失效。 4. **使用否定条件**:如`WHERE column != value`,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,...
在案例中,问题在于对日期字段进行了`TO_CHAR`和`TO_DATE`的转换,这使得已有的函数索引无法发挥效果。解决方法是创建一个新的函数索引,以反映查询中实际执行的转换过程,即`TO_DATE(TO_CHAR(SHOHOU_DATE, '...
4. 使用计算字段、函数或表达式作为索引条件也会导致索引失效。例如,`INDEX(name)`不会在`SELECT length(name) FROM table`中使用,因为索引是基于原始`name`字段的。 5. 当在`WHERE`子句中使用`NOT IN`或`IN`操作...
下面将详细讨论索引的类型、存储方式以及可能导致索引失效的场景。 首先,我们来看一下索引的类型: 1. **主键索引(Primary Key Index)**:这是最特殊的索引类型,用于唯一标识表中的每一行记录,且不允许有NULL...
应尽量避免这些情况,如果必须使用函数,可以考虑创建函数索引,但其维护成本较高。 6. **索引管理**:在大量数据导入时,临时删除索引可以加速导入,之后再重建。索引和表应放在不同的表空间以减少竞争。索引扩展...
函数索引允许对索引列进行计算,使得基于计算结果的查询也能加速。 【索引优化策略】 选择合适的索引策略对数据库性能优化至关重要。创建连接索引在SELECT和WHERE子句涉及的列上,可以避免全表扫描,直接通过索引...
* 在查询条件中对索引列使用函数,就会导致索引失效。 * 在查询条件中对索引列进行表达式计算,也是无法走索引的。 * MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是...
6. **避免函数索引**:在WHERE子句中对索引列使用函数会使得索引失效。如果需要使用函数,考虑创建函数索引或重构查询。 7. **避免索引列上的计算**:不要在索引列上进行计算,这会导致索引失效。确保比较操作直接...
平安银行软件开发笔试题.pdf 该笔试题涵盖了软件开发中的多个方面,包括项目管理、java 编程、javascript、sql 等。...可以通过建立函数索引、避免隐式转换、避免对索引列进行运算等方法来避免索引失效。