MYSQL索引使用案例分析

目录

 

一、创建数据表

二、向数据表中插入字段

三、创建联合索引在a1,a2,a3,a4字段上

四、索引案例分析

1、字符串未使用引号,导致索引失效

2、符合索引最左原则使用不当,导致索引失效

3、范围查找导致索引失效

4、order by使用不当,导致索引失效

4、group by使用不当导致索引失效

5、通配符like的使用不当导致索引失效

6、MYSQL 中!=,<>导致索引失效


一、创建数据表

CREATE TABLE test (

id INT PRIMARY NOT NULL AUTO_INCREMENT,

a1 CHAR(10),

a2 CHAR(10),

a3 CHAR(10),

a4 CHAR(10),

a5 CHAR(10),

);

 

二、向数据表中插入字段

INSERT INTO test(a1,a2,a3,a4,a5)VALUES('a1','a2','a3','a4','a5');

INSERT INTO test(a1,a2,a3,a4,a5)VALUES('b1','b2','b3','b4','b5');

INSERT INTO test(a1,a2,a3,a4,a5)VALUES('c1','c2','c3','c4','c5');

INSERT INTO test(a1,a2,a3,a4,a5)VALUES('d1','d2','d3','d4','d5');

 

三、创建联合索引在a1,a2,a3,a4字段上

CREATE INDEX idx_test_a1234 ON test(a1,a2,a3,a4);

 

四、索引案例分析

1、字符串未使用引号,导致索引失效

select * from test where  a1 = a1;

select * from test where a1 = 'a1';

 

 

2、符合索引最左原则使用不当,导致索引失效

select * from test where a1 = 'a1' and a2 = 'a2' and a3 = 'a3' and a4 = 'a4';

select * from test where a4= 'a4' and a3 = 'a3' and a2 = 'a2' and a1= 'a1';

 

两面两个查询中,where条件中的索引位置是相反的,但是执行结果是一致的,这个是由mysql优化器来处理的,因为两个查询中都出现了联合索引a1,a2,a3,a4,MySQL优化器底层会进行优化处理,他们还是遵循最左原则,但是如果中间断掉或者去掉最左边的a1,情况就会大不相同

 

3、范围查找导致索引失效

select * from test where a1 = 'a1' and a2 = 'a2' and a3 >'a3' and a4 = 'a4';

select * from test where a1 = 'a1' and a2 = 'a2' and a4 >'a4' and a3= 'a3';

 
以上查询使用到a1,a2,a3三个索引


以上的查询虽然使用了范围查找,但是a4之后再无索引,所以此处用到了4个索引

 

4、order by使用不当,导致索引失效

select * from test where a1 = 'a1' and a2 = 'a2' and a4 = 'a4' order by a3

 


以上查询使用了两个索引,因为查找的where条件中跳过了a3,违背了索引的最左原则,导致索引a4失效

严格意义上来说,以上查询使用了3个索引,a3并未用于查找,但是在排序中使用到了,只是为统计到explain中,即满足索引的两大功能:查找和排序

 

select * from test where a1 = 'a1' and a2 = 'a2' and a4 = 'a4' order by a3

select * from test where a1 = 'a1' and a2 = 'a2' order by a3

MYSQL索引使用案例分析
 

以下排序中,索引使用不当,导致产生了文件内排序,影响性能

a2之后a3断开,导致a4索引失效,mysql 为了将结果展现出来,进行了内部排序

 select * from test where a1 ='a1' and a2 = 'a2'  order by a4;

 select * from test where a1 ='a1' and a5 = 'a5'  order by a2,a3;

select * from test where a1 ='a1' and a5= 'a5'  order by a3,a2;(索引失效)

select * from test where a1 ='a1' and a2 = 'a2'  order by a2,a3;

4、group by使用不当导致索引失效

 

select * from test where a1 ='a1'and a4 = 'a4' group by a2,a3;

select * from test where a1 ='a1'and a4 = 'a4' group by a3,a2;


 分组之前必排序,group by 表面上是分组,但是对索引的使用和order by 的使用大致相同,所以group by后面如果索引错乱,会产生临时表,导致mysql内部进行排序

 

5、通配符like的使用不当导致索引失效

select  * from test where a1 like '%a';

select  * from test where a1 like 'a%';

select  a1 from test where a1 like '%a';

 c% ---索引失效

c... ---索引失效

abc% ----索引不失效

如何避免两边%%,且索引不会失效,建议使用覆盖索引,可以避免全表扫描


 

6、MYSQL 中!=,<>导致索引失效

select * from test where a1 !=a1;

select a1 from test where a1 !=a1;

select * from test where a1 <>a1;

select a1 from test where a1 <>a1;

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页