MySQL高级--索引失效

2023-02-01 10:53:47

索引失效的场景

参考网址：

索引失效的7种情况 - liehen2046 - 博客园
 MySQL索引失效 - 编程柚子 - 博客园
 MySQL 索引未命中情况

简介

失效场景

说明/示例

like 以%或者_开头

%和_只要不在开头，索引就有效。

or语句前后没有同时使用索引

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

联合索引没遵循最左前缀

见前边的“联合索引”

数据类型出现隐式转化

varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

例如：tb1表中name字段是字符串类型

索引有效：select * from tb1 where name = '123'

索引失效：select * from tb1 where name = 123

索引列上使用 IS NULL 或 IS NOT NULL

原因：索引无法通过空值计算出存储它的位置。

解决方法：

法1：把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找

法2：建立一个复合索引。例如：create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。

索引字段上使用not，<>，!=

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

对索引字段进行计算或者使用函数

示例：

select * from t where id*3=3000

select * from t where ABS(id)=3000

索引失效的原因是索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了。

order by使用错误

详见下方

全表扫描速度比索引速度快

此时mysql会使用全表扫描，索引失效。

例如：

数据量极少的表

表中索引字段数据重复率太高

where 子句中使用参数（可能失效）

可能会导致索引失效，与版本有关。

因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。此语句将进行全表扫描（可能）：select id from t where num = @num
可改为强制查询使用索引：select id from t with(index(索引名)) where num = @num

联合索引

其他网址

联合索引（多列索引）_数据库_lm1060891265的博客-CSDN博客
 mysql创建索引的原则_数据库_boss_way的博客-CSDN博客

【原创】面试官:谈谈你对mysql联合索引的认识？ - 孤独烟 - 博客园

简介

联合索引：对多个列进行索引，联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。

联合索引：遵循最左前缀（最左匹配）原则。

假定上图联合索引的为（a,b）。联合索引也是一棵B+树，不同的是B+树在对索引a排序的基础上，对索引b排序。所以数据按照（1,1), (1,2), (2,1), (2,4), (2,4), (3,1), (3,2)顺序排放。

a是有序的：1，1，2，2，3，3

b是无序的：1，2，1，4，1，2

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1，4也是有序状态。

因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

联合索引有效的情况

假设：创建联合索引：index index_name (a, b);

命令说明

selete * from table where a=XX 会使用索引。

selete * from table where a=XX and b=YY 显然是可以使用(a,b)联合索引的

select * from table where b=YY and a=XX
这条语句不符合最左匹配原则。但由于查询优化器的存在，MySQL优化器会自动调整where后的a，b的顺序与索引顺序一致。
优化：在联合索引中将选择性最高的列放在索引最前面。例如：在一个公司里以age 和gender为索引，显然age要放在前面，因为性别就两种选择男或女，选择性不如age。

联合索引失效的情况

假设：创建联合索引：index index_name (a, b);

命令说明

selete * from table where b=YY 叶子节点的b值为1,2,1,4,1,2，它不是有序的，因此不能使用(a,b)联合索引。

select * from table where a>XX and b=YY
此处只会用到a索引，不会用到b索引。

对于组合索引，会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

比如：a = 1 and b = 2 and c > 3 and d = 4，若建立(a,b,c,d)索引，d用不到索引。

使所有索引都有效的方案：将c放到建立索引的语句的最后，例如：建立(a,b,d,c)的索引（a,b,d的顺序可以任意调整），这样执行sql的时候，优化器会帮我们调整where后a,b,c,d的顺序（将c放到最后），让我们用上索引。

order by

其他网址

Mysql 在 order by 时索引的使用机制 - 程序员大本营
 MySQL-ORDER BY索引优化_luzhensmart的专栏-CSDN博客

简介

ORDER BY有两种实现方法：

利用有序索引自动实现。（利用有序索引的有序性而不再另做排序了（速度快））
也就是explain结果的Using index

把结果选好之后再排序。（速度慢）
也就是explain结果的Using filesort

查询和排序都走索引的情况

select字段与order by字段完全相同，或者select字段与order by字段组成联合索引。
例：select col1 from tb1 order by col1;

where字段与order by字段完全相同，或者where字段与order by字段组成联合索引。
例：select col1 from tb1 where col2=2 order by col2;

查询不走索引的情况

select字段不包含 order by 字段。
例：select col1 from tb1 order by col2

select字段包含 order by 字段 + 其他字段。
例：select col1,col3 from tb1 order by col2

where 字段不包含 order by 字段。
例：select col1 from tb1 where col2=2 order by col3

实例

drop table if exists test;
create table test(
id int primary key auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;
 
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('c1','b2','c7','c8','c9');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

CREATE INDEX idx_c1234 ON test(c1,c2,c3,c4);

结果：

等值查询

案例1：SELECT *，且WHERE与ORDER BY不一样且不遵循最左前缀

EXPLAIN SELECT * FROM test WHERE c2 = 'b2' ORDER BY c1;
结果：查询不走索引；排序不走索引

案例2：SELECT *，且WHERE与ORDER BY不一样且遵循最左前缀

EXPLAIN SELECT * FROM test WHERE c1 = 'c1' ORDER BY c2;
结果：查询不走索引；排序不走索引

范围查询

案例1：SELECT *，且WHERE与ORDER BY不一样

EXPLAIN SELECT * FROM test WHERE c2 > 'b1' ORDER BY c1;
结果：查询不走索引；排序不走索引

案例2：SELECT 不是*，WHERE与ORDER BY不一样，ORDER BY最左前缀

EXPLAIN SELECT c3 FROM test WHERE c2 > 'b1' ORDER BY c1;
结果：查询不走索引，排序走索引。这种情况相当于只有排序使用索引

（注意：将c3换成c1,c2等，结果都是一样的）

案例3：SELECT *，WHERE与ORDER BY一样且遵循最左

EXPLAIN SELECT * FROM test WHERE c1 > 'b1' ORDER BY c1;
结果：查询走索引；排序不用操作，直接用查询的结果即可

案例4：SELECT *，WHERE与ORDER BY不一样且遵循最左

EXPLAIN SELECT * FROM test WHERE c1 > 'b1' ORDER BY c2;
结果：查询走索引；排序不走索引

like模糊查询

参考网址

MySQL - 如何优化模糊查询（like 模糊查询)_数据库_Soinice的博客-CSDN博客
 【转】【MySQL】Mysql模糊查询like提速优化 - 梦琪小生 - 博客园
 SQL使用模糊查询like的优化 - 简书
 索引与优化like查询

简介

在MySQL中，模糊查询肯定要使用like关键字，然后再加 %，是代表前模糊还是后模糊。数据量小的情况下，不容易看出查询的效率，但是数据量达到百万级，千万级甚至更高的时候，查询的效率就很容易显现出来了。

本处假设要查询的字段（where子句）都已经加了索引。

项

索引是否失效

解决方法

col_name like 'ABC%'

索引有效

col_name like '%ABC'

索引失效，使用全表扫描

翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引

col_name like '%ABC%'

索引失效

locate, position,instr,find_in_set等方法

col_name like '%ABC'

本语句不走索引：

select object_name from t1 where object_name like '%ABC';

解决方法：

create index idx_t1_objectname2 on t1(reverse(object_name));
select object_name from t1 where reverse(object_name) like reverse('%ABC');

看执行计划：（改写后SQL走了索引。）

col_name like '%ABC%'

有三种情况：
1、ABC在字符串中位置不固定，可以通过改写SQL进行优化
2、ABC始终从字符串开始的某个固定位置出现，可以创建函数索引进行优化
3、ABC始终从字符串结尾的某个固定位置出现，可以创建函数组合索引进行优化

情况1

函数

作用

示例/说明

LOCATE(substr,str)

返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。

如果str是字段，则查找字段内容，如果str不是字段，则将str作为要查的库。

SELECT LOCATE('xbar',`foobar`); #返回0

#若`foobar`是字段，则去此字段查询，而不是”foobar”字符串

SELECT LOCATE('bar',`foobarbar`); #返回4

SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0

LOCATE(substr,str, pos)

返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。

SELECT LOCATE('bar',`foobarbar`,5); #返回7

POSITION('substr' IN `field`)

功能跟locate一样

SELECT `col` FROM `tb1` WHERE POSITION('keyword' IN `filed`)

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)

FIND_IN_SET(str,strlist)

查询字段(strlist)中包含(str)的结果，返回记录（没有则返回null）

str 要查询的字符串

strlist 字段名参数以”,”分隔如 (1,2,6,8,10,22)

SELECT FIND_IN_SET('b', 'a,b,c,d'); #返回2

SELECT name,role FROM `user` WHERE find_in_set('2',role)

find_in_set()和like的区别：

主要的区别就是like是广泛的模糊查询，而 find_in_set() 是精确匹配，并且字段值之间用‘,'分开。

SELECT userid,username,userrole 角色 FROM `user` WHERE userrole LIKE '%2%';

SELECT userid,username,userrole 角色 FROM `user` WHERE find_in_set('2',userrole)

情况2

先创建substr函数索引，再使用like ‘ABC%’。

假如ABC从字符串第五位出现：
create index idx_substr_t1_objname on t1 (substr(object_name,5,30));
select object_id,object_type,object_name from t1
where substr(object_name,5,30) like 'TAB%';

情况3：

查询%xx的记录
select count(c.c_ply_no) as COUNT
  from Policy_Data_All c, Item_Data_All i
  where c.c_ply_no = i.c_ply_no
  and i.C_LCN_NO like '%245'
在执行的时候，执行计划显示，消耗值，io值，cpu值均非常大，原因是like后面前模糊查询导致索引失效，进行全表扫描。

解决方法：这种只有前模糊的sql可以改造如下写法
select count(c.c_ply_no) as COUNT
  from Policy_Data_All c, Item_Data_All i
  where c.c_ply_no = i.c_ply_no
  and reverse(i.C_LCN_NO) like reverse('%245')

优化原理

用索引全扫描取代表的全扫描。因为索引全扫描的代价是全表扫描的1/N (即索引块数与数据块数的比例），表越大，优化效果越明显。
改写后SQL的执行计划，根据索引再回表的代价要看符合条件的记录数多少：如果in子查询返回的记录数很少，那么优化的效果就相当于效率提高了N倍；如果in子查询返回的记录数较多，两种SQL的性能区别就不是很明显了

失效场景	说明/示例
like 以%或者_开头	%和_只要不在开头，索引就有效。
or语句前后没有同时使用索引	当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
联合索引没遵循最左前缀	见前边的“联合索引”
数据类型出现隐式转化	varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。例如：tb1表中name字段是字符串类型索引有效：select * from tb1 where name = '123' 索引失效：select * from tb1 where name = 123
索引列上使用 IS NULL 或 IS NOT NULL	原因：索引无法通过空值计算出存储它的位置。解决方法：法1：把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找法2：建立一个复合索引。例如：create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。
索引字段上使用not，<>，!=	不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。
对索引字段进行计算或者使用函数	示例： select * from t where id3=3000 select from t where ABS(id)=3000 索引失效的原因是索引是针对原值建的二叉树，将列值计算后，原来的二叉树就用不上了。
order by使用错误	详见下方
全表扫描速度比索引速度快	此时mysql会使用全表扫描，索引失效。例如：数据量极少的表表中索引字段数据重复率太高
where 子句中使用参数（可能失效）	可能会导致索引失效，与版本有关。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。此语句将进行全表扫描（可能）：select id from t where num = @num 可改为强制查询使用索引：select id from t with(index(索引名)) where num = @num

命令	说明
selete * from table where a=XX	会使用索引。
selete * from table where a=XX and b=YY	显然是可以使用(a,b)联合索引的
select * from table where b=YY and a=XX	这条语句不符合最左匹配原则。但由于查询优化器的存在，MySQL优化器会自动调整where后的a，b的顺序与索引顺序一致。优化：在联合索引中将选择性最高的列放在索引最前面。例如：在一个公司里以age 和gender为索引，显然age要放在前面，因为性别就两种选择男或女，选择性不如age。

项	索引是否失效	解决方法
col_name like 'ABC%'	索引有效
col_name like '%ABC'	索引失效，使用全表扫描	翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引
col_name like '%ABC%'	索引失效	locate, position,instr,find_in_set等方法

函数	作用	示例/说明
LOCATE(substr,str)	返回 substr 在 str 中第一次出现的位置，如果 substr 在 str 中不存在，返回值为 0 。如果str是字段，则查找字段内容，如果str不是字段，则将str作为要查的库。	SELECT LOCATE('xbar',`foobar`); #返回0 #若`foobar`是字段，则去此字段查询，而不是”foobar”字符串 SELECT LOCATE('bar',`foobarbar`); #返回4 SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
LOCATE(substr,str, pos)	返回 substr 在 str 第pos个位置后第一次出现的位置，如果 substr 在 str 中不存在，返回值为0。	SELECT LOCATE('bar',`foobarbar`,5); #返回7
POSITION('substr' IN `field`)	功能跟locate一样	SELECT `col` FROM `tb1` WHERE POSITION('keyword' IN `filed`) SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
FIND_IN_SET(str,strlist)	查询字段(strlist)中包含(str)的结果，返回记录（没有则返回null） str 要查询的字符串 strlist 字段名参数以”,”分隔如 (1,2,6,8,10,22)	SELECT FIND_IN_SET('b', 'a,b,c,d'); #返回2 SELECT name,role FROM `user` WHERE find_in_set('2',role) find_in_set()和like的区别：主要的区别就是like是广泛的模糊查询，而 find_in_set() 是精确匹配，并且字段值之间用‘,'分开。 SELECT userid,username,userrole 角色 FROM `user` WHERE userrole LIKE '%2%'; SELECT userid,username,userrole 角色 FROM `user` WHERE find_in_set('2',userrole)

码农公寓