索引失效的几种情况

太过爱你忘了你带给我的痛 2024-03-27 18:37 17阅读 0赞

**目录**

数据准备：

1、查询条件中有or，索引会失效；

2、like查询以%开头

3、如果类型为字符串，查询条件中数据需用引号引起来，否则不走索引；

4、索引列上参与计算会导致索引失效

5、违背最左匹配原则

6、其他

--------------------

本文转载于：[索引失效的情况及解决(超详细)\_zyy\_demon的博客-CSDN博客][zyy_demon_-CSDN]

## 数据准备： ##

id为主键；设置name为普通索引；name和age为普通联合索引

CREATE TABLE `student` (
      `id` int NOT NULL COMMENT 'id',
      `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
      `age` int DEFAULT NULL COMMENT '年龄',
      `birthday` datetime DEFAULT NULL COMMENT '生日',
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`) USING BTREE,
      KEY `idx_name_age` (`name`,`age`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `student` VALUES (1, '张三', 18, '2021-12-23 17:12:44');
    INSERT INTO `student` VALUES (2, '李四', 20, '2021-12-22 17:12:48');

## 1、查询条件中有or，索引会失效； ##

![ebd33961c7eb450196d7a445f7978861.png][]

条件带有or：

![594dae6b6baf4bcfa481151e6d6f58a3.png][]

type=ALL，搜索时走了全表扫描，并未命中索引；

注意：查询条件中带有or 除非所有的查询条件都建有索引，否则索引会失效；

## 2、like查询以%开头 ##

![6a566533d13341f083750bb7da95f0e1.png][]

非模糊查询会命中name索引,当使用模糊查询后：

![b18c42d058014645b9cd1f09ced4806f.png][]

发现type=ALL，全表扫描，未命中索引;

## 3、如果类型为字符串，查询条件中数据需用引号引起来，否则不走索引； ##

![82c720cd4591478aae183e3cfd9c2f12.png][]

![81345d781d9a4da58ade178f0001f73f.png][]

## 4、索引列上参与计算会导致索引失效 ##

![1a74b1ca3f2040e3aebcbedb68fcd4cc.png][]

![4e94bcc346254848b9250def94104d74.png][]

## 5、违背最左匹配原则 ##

![556b671c26c24205a05885aaad46cb09.png][]

走联合索引：

![d1a7afa221244faea4dc478225488481.png][]

## 6、其他 ##

> 1) 没有查询条件，或者查询条件没有建立索引
> 
> 2) 在查询条件上没有使用引导列
> 
> 3) 查询的数量是大表的大部分，应该是30％以上。
> 
> 4) 索引本身失效
> 
> 5) 查询条件使用函数在索引列上，或者 对索引列进行运算， 运算包括(+，-，\*，/，! 等) 错误的例子：select \* from test where id-1=9; 正确的例子：select \* from test where id=10;
> 
> 6) 对小表查询
> 
> 7) 提示不使用索引
> 
> 8) 统计数据不真实
> 
> 9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
> 
> 10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu\_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select \* from test where tu\_mdn=13333333333; 正确的例子：select \* from test where tu\_mdn='13333333333';
> 
> 11) 1,<> 2,单独的>,<,(有时会用到，有时不会)
> 
> 12) like "%\_" 百分号在前.
> 
> 13) 表没分析.
> 
> 14) 单独引用复合索引里非第一位置的索引列.
> 
> 15) 字符型字段为数字时在where条件里不添加引号.
> 
> 16) 对索引列进行运算.需要建立函数索引.
> 
> 17) not in ,not exist.
> 
> 18) 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
> 
> 19) B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
> 
> 20) 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。

[zyy_demon_-CSDN]: https://blog.csdn.net/sy_white/article/details/122112440
[ebd33961c7eb450196d7a445f7978861.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/3b1118f5dadd4d5db229a306e73cb428.png
[594dae6b6baf4bcfa481151e6d6f58a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/120884414d4c4f4d966663b8658139ff.png
[6a566533d13341f083750bb7da95f0e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/8150026c236c4f9a9ff634109970a941.png
[b18c42d058014645b9cd1f09ced4806f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1f5c49df481f489ba82b60076923e121.png
[82c720cd4591478aae183e3cfd9c2f12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/4ae294cbe3d34e38be0966d57aeb3507.png
[81345d781d9a4da58ade178f0001f73f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/b74dc27352934dfcb3f0342ae9f39939.png
[1a74b1ca3f2040e3aebcbedb68fcd4cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/7d5e92eb724047149e8071de3c8e9a6d.png
[4e94bcc346254848b9250def94104d74.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1af06a4d7c764cd28d4e0752191392f4.png
[556b671c26c24205a05885aaad46cb09.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/a2099d75be064f13bdf14b00bc8d6934.png
[d1a7afa221244faea4dc478225488481.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/1cca4a5eb7514aa3a28cee01fba04a11.png