索引失效

最左匹配原则

数据库结构如下

表示查询的时候,写上全部的索引,之间用and连接

-- create index idx_name_dep_age on employee (name,dep_id,age) #一个键名设置了多个字段
-- EXPLAIN select * FROM employee WHERE NAME = "鲁班" and dep_id=1 AND age = 10  -- 最佳的情况,全值匹配
-- EXPLAIN select * FROM employee WHERE  age = 10 AND dep_id =1 -- 去掉最左边的索引会发现没有匹配到任何索引
-- EXPLAIN select * FROM employee WHERE NAME = "鲁班"  AND age = 10 -- 去掉中间会只匹配到左边的
EXPLAIN select * FROM employee WHERE NAME = "鲁班"  AND age = 10 AND dep_id =1 -- 打乱顺序没有关系

函数计算

函数计算会导致索引失效

EXPLAIN SELECT * FROM employee where TRIM(name)='鲁班' -- 去空格函数,会导致匹配不到索引

范围条件

范围条件会导致索引失效

-- 这行sql name和age依旧能够匹配到,但dep_id匹配不到
EXPLAIN SELECT * FROM employee where name='鲁班' and dep_id>0 and age=10 

查询条件符号

 EXPLAIN SELECT * FROM employee where name !='鲁班' -- 这样也会造成索引失效
 EXPLAIN SELECT * FROM employee where name is not null -- is not null,索引失效
 EXPLAIN SELECT * FROM employee where name or dep_id=1 -- 使用or连接,索引失效

likle通配符与字符串不加单引号

explain SELECT * FROM employee where name like '鲁%' -- 这里能正确用到索引
explain SELECT * FROM employee where name like '%鲁%' -- 这里不会用到索引

EXPLAIN SELECT * FROM employee where name = 200 -- 就算能查到数据也会匹配不到索引

覆盖索引

查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引

排序与分组优化

避免UsingFileSort

在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。

说明mysql会对数据使用一个外部的索引排序,
而不是按照表内的索引顺序进行
Mysql中无法利用索引完成排序操作称为"文件排序"

使用order by时,如果没有按照索引顺序,会出现(Extra)using filesort

-- EXPLAIN select name,dep_id,age from employee ORDER BY name,age -- 会造成using filesort,因为中间空了一个字段
-- EXPLAIN select name from employee ORDER BY dep_id,name,age -- 会造成using filesort,因为打乱了排序
-- EXPLAIN select * from employee ORDER BY name,dep_id,age -- 会造成using filesort,因为使用了*
-- EXPLAIN select salary from employee ORDER BY name,dep_id,age -- 会造成using filesort,因为使用了其他字段
-- EXPLAIN select name from employee ORDER BY name,dep_id,age -- 不会造成
-- EXPLAIN select name from employee where name='鲁班' ORDER BY dep_id,age -- 不会造成,因为使用了name
EXPLAIN select name from employee ORDER BY name desc,dep_id,age asc -- 会出现,因为其中即出现了降序,也出现了升序排序

避免Using temporary

使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
常见于排序orderby 和分组查询group by

-- Using index; Using temporary; Using filesort 创建了临时表 将asc改成desc就能去除
-- 规则和order by 相似
EXPLAIN select name from employee GROUP  BY name desc,dep_id asc 

分页

依次执行

DROP TABLE IF EXISTS `testemployee`;
CREATE TABLE `testemployee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dep_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `cus_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

set global log_bin_trust_function_creators=TRUE

#随机生成一个指定个数的字符串
delimiter $$
create function rand_str(n int) RETURNS VARCHAR(255)
BEGIN
#声明一个str 包含52个字母
DECLARE str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
#记录当前是第几个
DECLARE i int DEFAULT 0;
#生成的结果
DECLARE res_str varchar(255) default '';
while i < n do 
set  res_str = CONCAT(res_str,substr(str,floor(1+RAND()*52),1));
set i = i + 1;
end while;
RETURN res_str;
end $$ 
delimiter ;

delimiter $$
create procedure insert_emp(in max_num int)
BEGIN
declare i int default 0;

set autocommit = 0;

repeat 
set i = i + 1;
insert into testemployee (name,dep_id,age,salary,cus_id) values(rand_str(5),floor(1 + rand()*10),floor(20 + rand()*10),floor(2000 + rand()*10),floor(1 + rand()*10));
until i = max_num
end REPEAT;
commit;

end $$
delimiter ;

call insert_emp(1000000) -- 插入100万条数据 

查看执行sql需要消耗的时间

使用limit随着offset增大,查询的速度会越来越慢,会把前面的数据都取出,找到对应位置

使用子查询的方式优化分页

让耗时的操作走索引

#SELECT * from testemployee LIMIT 0,10 -- 0.015s

#SELECT * from testemployee LIMIT 990000,10 -- 0.243

-- select * from testemployee t inner join
-- (SELECT id FROM testemployee t limit 990000,10)tmp
-- on t.id=tmp.id -- 0.150 速度变快

SELECT * from testemployee
where id>(SELECT id FROM testemployee t limit 990000,1)
LIMIT 10 -- 0.155s 速度也得到了替身

使用id限定方式优化分页

#SELECT * from testemployee LIMIT 0,10 -- 0.015s

#SELECT * from testemployee LIMIT 990000,10 -- 0.243

select * from testemployee where id >99000 LIMIT 10 -- 0.014s 假如知道了数据开始的位置,使用where来查询

在设计的时候,也要考虑好分页的问题,比如最多限定用户只能翻100页,其他的都要通过搜索来进行

函数

max

SELECT max(age) from testemployee -- 0.174s 

如果把age设置为索引会大大加快速度,因为优化器中有统计信息,其中就有最大值,并没有从行中进行检索

小表驱动大表

小表驱动大表,即小的数据集驱动大的数据集

for(int i5;...){
    for(int 1000;...){
    
    }
}

如果小的循环在外层,对于数据库连接来说只连接五次,进行1000次操作

如果1000在外,则需要进行1000此数据库连接操作,从而浪费资源,增加消耗这就是为什么小表驱动大表