欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【数据库】之MySQL索引优化

程序员文章站 2022-06-02 08:57:55
...
DROP TABLE IF EXISTS `staffs`;
CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  `pos` varchar(20) NOT NULL,
  `add_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into staffs(name,age,pos,add_time) values ('z3',22,'manager',NOW());
insert into staffs(name,age,pos,add_time) values ('july',23,'dex',NOW());
insert into staffs(name,age,pos,add_time) values ('2000',23,'manager',NOW());
 
alter table staffs add index idx_staffs_nap(name,age,pos);

1.全值匹配我最爱

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

【数据库】之MySQL索引优化

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

【数据库】之MySQL索引优化

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

【数据库】之MySQL索引优化

2.最佳左前缀法则

如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 23 AND pos = 'dev';

【数据库】之MySQL索引优化

最左前列索引不能丢

EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';

【数据库】之MySQL索引优化

不能跳过中间列索引

只用到了第一个索引

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND pos = 'dev';

【数据库】之MySQL索引优化

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

【数据库】之MySQL索引优化

3.不在索引列上做任何操作

计算、函数、(自动or手动)类型转换等,会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

【数据库】之MySQL索引优化

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

【数据库】之MySQL索引优化

4.存储引擎不能使用索引中范围条件右边的列

范围之后全失效

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'manager';

【数据库】之MySQL索引优化

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = 'manager';

【数据库】之MySQL索引优化

5.尽量使用覆盖索引

只访问索引的查询即索引列和查询列一致或在索引列内,减少select *

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'manager';

【数据库】之MySQL索引优化

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'manager';

【数据库】之MySQL索引优化

6.MySQL在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

explain select * from staffs where name = 'July';

【数据库】之MySQL索引优化

explain select * from staffs where name != 'July';

【数据库】之MySQL索引优化

explain select * from staffs where name <> 'July';

【数据库】之MySQL索引优化

7.is null,is not null 也无法使用索引

explain select * from staffs where name is null;

【数据库】之MySQL索引优化

explain select * from staffs where name is not null;

【数据库】之MySQL索引优化

8.like以通配符开头(’$abc…’)MySQL 索引失效会变成全表扫描操作。

like百分号写右边

explain select * from staffs where name like '%July';

【数据库】之MySQL索引优化

explain select * from staffs where name like '%July%';

【数据库】之MySQL索引优化

explain select * from staffs where name like 'July%';

【数据库】之MySQL索引优化

问题:解决like’%字符串%'时索引不被使用的方法

实验证明,覆盖索引,可以解决左右百分号全表扫描的问题。

CREATE TABLE `tbl_user`(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  `email` VARCHAR(20)  DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
insert into tbl_user(name,age,email) values ('1aa1',21,'[email protected]');
insert into tbl_user(name,age,email) values ('2aa2',222,'[email protected]');
insert into tbl_user(name,age,email) values ('3aa3',265,'[email protected]');
insert into tbl_user(name,age,email) values ('4aa4',21,'[email protected]');
select * from tbl_user;

【数据库】之MySQL索引优化

select * from tbl_user where name like '%aa%';

【数据库】之MySQL索引优化

select * from tbl_user where name like 'aa%';
# Empty set
select * from tbl_user where name like '%aa';
# Empty set

建索引前,全表扫描,建立索引后用覆盖索引

索引建立前后对比

create index idx_user_name_age on tbl_user(name,age);
explain select name,age from tbl_user where name like '%aa%'; 

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select age from tbl_user where name like '%aa%';

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select id from tbl_user where name like '%aa%';   

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select name from tbl_user where name like '%aa%';  

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select id, name, age from tbl_user where name like '%aa%';

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select id, name, age,email from tbl_user where name like '%aa%';   

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

explain select * from tbl_user where name like '%aa%'; 

【数据库】之MySQL索引优化
【数据库】之MySQL索引优化

9.字符串不加单引号索引失效

desc staffs;

【数据库】之MySQL索引优化

name是varchar类型,但是有个值是2000。条件查询的时候,这个2000的值可以有单引号也可以没有。接下来我们看两种情况的区别。

select * from staffs where name = '2000';

【数据库】之MySQL索引优化

select * from staffs where name = 2000;

【数据库】之MySQL索引优化

如果不用单引号,就会把2000从整型自动转换成字符型。对应上面的第3条规则(不能有隐式的类型转换)。

explain select * from staffs where name = '2000';

【数据库】之MySQL索引优化

explain select * from staffs where name = 2000;

【数据库】之MySQL索引优化

10.少用or,用它连接时会索引失效

select * from staffs where name = 'July' or name = 'z3';

【数据库】之MySQL索引优化

explain select * from staffs where name = 'July' or name = 'z3';

【数据库】之MySQL索引优化

总结

index(a,b,c)

WHERE语句 索引是否被使用
where a = 3 Y,使用到 a
where a = 3 and b = 5 Y,使用到 a,b
where a = 3 and b = 5 and c = 4 Y,使用到 a,b,c
where a = 3 或者 where b = 3 and c = 4 或者 where c = 4 N
where a = 3 and c = 5 使用到 a,但是 c 不可以,b 中间断了
where a = 3 and b > 4 and c = 5 使用到 a 和 b,c 不能用在范围之后,b 断了
where a = 3 and b like ‘kk%’ and c = 4 a 能用,b 不能用,c 不能用