Mysql优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4387字,纯文字阅读大概需要7分钟。
内容图文
![Mysql优化](/upload/InfoBanner/zyjiaocheng/510/74eb8bd64ca241ad8eaebc42596efde0.jpg)
CREATE TABLE `staffs`(
id int primary key auto_increment,
name varchar(24) not null default "" comment‘姓名‘,
age int not null default 0 comment ‘年龄‘,
pos varchar(20) not null default "" comment‘职位‘,
add_time timestamp not null default current_timestamp comment ‘入职时间‘
)charset utf8 comment ‘员工记录表‘;
insert into staffs(name,age,pos,add_time) values(‘z3‘,22,‘manage‘,now());
insert into staffs(name,age,pos,add_time) values(‘july‘,23,‘dev‘,now());
insert into staffs(name,age,pos,add_time) values(‘2000‘,23,‘dev‘,now());
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘;
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ AND age = 25 AND pos = ‘dev‘
当建立了索引列后,能在wherel条件中使用索引的尽量所用。
2. 策略2.最佳左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列(和顺序无关,但是不能丢)。
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = ‘dev‘
EXPLAIN SELECT * FROM staffs WHERE pos = ‘dev‘
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘
3. 策略3.不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘;
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = ‘July‘;
4. 策略4.范围条件放最后
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ ;
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ and age =22;
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ and age =22 and pos=‘manager‘
中间有范围查询会导致后面的索引列全部失效
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ and age >22 and pos=‘manager‘
5. 策略5.覆盖索引尽量用
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ and age =22 and pos=‘manager‘
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = ‘July‘ and age =22 and pos=‘manager‘
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘ and age >22 and pos=‘manager‘
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = ‘July‘ and age >22 and pos=‘manager‘
6.策略6.不等于要甚用
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July‘;
EXPLAIN SELECT * FROM staffs WHERE NAME != ‘July‘;
EXPLAIN SELECT * FROM staffs WHERE NAME <> ‘July‘;
如果定要需要使用不等于,请用覆盖索引
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME != ‘July‘;
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME <> ‘July‘;
7. 策略7.Null/Not 有影响
注意null/not null对索引的可能影响
7.1. 自定定义为NOT NULL
EXPLAIN select * from staffs where name is null
EXPLAIN select * from staffs where name is not null
在字段为not null的情况下,使用is null 或 is not null 会导致索引失效
解决方式:覆盖索引
EXPLAIN select name,age,pos from staffs where name is not null
7.2. 自定义为NULL或者不定义
EXPLAIN select * from staffs2 where name is null
EXPLAIN select * from staffs2 where name is not null
EXPLAIN select * from staffs2 where name is not null
Is not null 的情况会导致索引失效
解决方式:覆盖索引
EXPLAIN select name,age,pos from staffs where name is not null
8. 策略8.Like查询要当心
like以通配符开头(‘%abc...‘)mysql索引失效会变成全表扫描的操作
EXPLAIN select * from staffs where name =‘july‘
EXPLAIN select * from staffs where name like ‘%july%‘
EXPLAIN select * from staffs where name like ‘%july‘
EXPLAIN select * from staffs where name like ‘july%‘
解决方式:覆盖索引
EXPLAIN select name,age,pos from staffs where name like ‘%july%‘
9. 策略9.字符类型加引号
字符串不加单引号索引失效
EXPLAIN select * from staffs where name = 917
解决方式:覆盖索引
EXPLAIN select name,age,pos from staffs where name = 917
解决方式:请加引号
10. 策略10.OR改UNION效率高
EXPLAIN
select * from staffs where name=‘July‘ or name = ‘z3‘
EXPLAIN
select * from staffs where name=‘July‘
UNION
select * from staffs where name = ‘z3‘
解决方式:覆盖索引
EXPLAIN
select name,age from staffs where name=‘July‘ or name = ‘z3‘
Mysql优化
标签:man 范围 记录 类型转换 顺序 dev 单引号 ima through
本文系统来源:https://www.cnblogs.com/lys-lyy/p/11186590.html
内容总结
以上是互联网集市为您收集整理的Mysql优化全部内容,希望文章能够帮你解决Mysql优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。