create table employees(
id int not null AUTO_INCREMENT,
name varchar(20) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
position varchar(20) not null default '' comment '职位',
hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY key (id),
key idx_name_age_position (name,age,position) USING BTREE
insert into employees(name,age,position,hire_time) values('HanMeimei',22,'pro',NOW());
insert into employees(name,age,position,hire_time) values('Lucy',22,'dev',NOW());
create procedure insert_temp()
begin
declare i int;
-- START TRANSACTION; -- 开启事务会块一些,尝试千万数据开启数据289s 不开启事务1400才跑到250w数据
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(concat('test',i),i,'dev');
set i=i+1;
end while;
-- COMMIT;
end;
call insert_temp();
-- 顺便记录一下坑,这段代码复制如果执行报错,自己手动敲,可能编码有问题
![](https://img-blog.csdnimg.cn/803022b404024a28ad83302292bde397.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6YKL6YGi6YGT,size_20,color_FFFFFF,t_70,g_se,x_16)
结论:联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描
2. 强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
对比一下看着好像行数少了,但是看下查询的时间
不加索引 0.211s 加了索引 0.28s(不同机器查询时间是不一致的,和硬件什么的有关系)
![](https://img-blog.csdnimg.cn/9aa5b74726254b339829698c445140e1.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6YKL6YGi6YGT,size_20,color_FFFFFF,t_70,g_se,x_16)
结论: 如果有联合索引,查询时最好完全覆盖字段(实在不行,尽可能多的覆盖到),那么扫描的行数,执行时间都会减少,加快效率。
4. in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
再加个数据少的表:
一样的语句
有可能就不走索引了,道理一样可能回表,数据不多还不如全表扫描
5. like XX% 一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
索引下推
MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数
为什么范围查找Mysql没有用索引下推优化?
![](https://img-blog.csdnimg.cn/d67709c86186404994d6ec991e7057f6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6YKL6YGi6YGT,size_20,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/f667b17fb8c443c1b7de4821fc7f5fc6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6YKL6YGi6YGT,size_20,color_FFFFFF,t_70,g_se,x_16)
常见sql深入优化
Order by 和 Group by(mysql 8环境测试)
情况1:
排序按索引最左前缀不会使用文件排序, desc 与索引排列方式不一致,导致文件排序
情况2:
不满足最左前缀,索引值使用了name
情况3:
对于排序来说,多个相等条件也是范围查询
情况4:
覆盖索引优化
分页优化
1.自增主键分页优化
优化前: select * from employees limit 90000,5; 正常分页 0.058s
数据量越大,越慢,和优化的差距越明显
优化后: select * from employees where id > 90000 limit 5; 优化分页 0.030s
对比执行计划,优化后明显扫描更少,type也比all优化很多
ps: 表中可能某些记录被删后,主键空缺,导致结果不一致
2. 根据非主键字段排序的分页查询
select * from employees e inner join (select id from employees order by name limit 2000000,5) ed on e.id = ed.id;
总结:
上百万级别首先不建议做分页查那么远的数据,尝试用记录上次查询主键来做,如果带条件也需要先用条件筛选出数据,再用主键索引查询。多种方式测试最终都不能达到完全优化。具体问题具体处理吧(如分表,做书签记录等,如果id是无序的,可能就应该上搜索引擎啥的了)
join的优化,in和exsits优化
尽量用小数据量的表做引擎表
in:当B表的数据集小于A表的数据集时,in优于exists
select * from A where id in (select id from B)
exists:当A表的数据集小于B表的数据集时,exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
count(*)查询优化
注意:以上4条sql只有根据某个字段count不会统计字段为null值的数据行
其实可以看到count(*)的效率反而最高,mysql并不会把全部字段取出来,而是专门做了优化,不取值,按行累加,效率很高,所以不需要用 count(列名)或count(常量)来替代 count(*)。
索引设计原则
总结:
最难写的一篇,理论和实践的地方很多不一致,可能是版本导致,也可能是其他优化的地方没有考虑到,优化是要结合各种实际情况,建议用explain结合索引结构分析,网上多找找解决思路,不断的尝试。
一个想过得更好的码农---邋遢道人