一、SQL执行顺序
二、基础SQL优化
1、查询SQL尽量不要使用select *,而是具体字段
1)反例
SELECT * FROM student
2)正例
SELECT id,NAME FROM student
3)理由
2、避免在where子句中使用or来连接条件
查询id为1或者薪水为3000的用户:
1)反例
SELECT * FROM student WHERE id=1 OR salary=30000
2)正例
使用union all:
SELECT * FROM student WHERE id=1
UNION ALL
SELECT * FROM student WHERE salary=30000
分开两条SQL写:
SELECT * FROM student WHERE id=1
SELECT * FROM student WHERE salary=30000
3)理由
3、使用varchar代替char
1)反例
`deptname` char(100) DEFAULT NULL COMMENT '部门名称'
2)正例
`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
3)理由
4、尽量使用数值替代字符串类型
5、查询尽量避免返回大量数据
如果查询返回数据量很大,就会造成查询时间过长,网络传输时间过长。同时,大量数据返回也可能没有实际意义。如返回上千条甚至更多,用户也看不过来。
通常采用分页,一页习惯10/20/50/100条。
6、使用explain分析你SQL执行计划
SQL很灵活,一个需求可以很多实现,那哪个最优呢?SQL提供了explain关键字,它可以分析你的SQL执行计划,看它是否最佳。Explain主要看SQL是否使用了索引。
EXPLAIN
SELECT * FROM student WHERE id=1
返回结果:
7、是否使用了索引及其扫描类型
type:
性能排行:
System > const > eq_ref > ref > range > index > ALL
possible_keys:
key:
8、创建name字段的索引
提高查询速度的最简单最佳的方式。
ALTER TABLE student ADD INDEX index_name (NAME)
9、优化like语句
模糊查询,程序员最喜欢的就是使用like,但是like很可能让你的索引失效。
1)反例
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'
2)正例
EXPLAIN
SELECT id,NAME FROM student WHERE NAME LIKE '1%'
3)理由
未使用索引,故意使用sex非索引字段:
EXPLAIN
SELECT id,NAME FROM student WHERE NAME=1 OR sex=1
主键索引生效:
EXPLAIN
SELECT id,NAME FROM student WHERE id=1
索引失效,type=ALL,全表扫描:
EXPLAIN
SELECT id,NAME FROM student WHERE id LIKE '%1'
10、字符串怪现象
1)反例
#未使用索引
EXPLAIN
SELECT * FROM student WHERE NAME=123
2)正例
#使用索引
EXPLAIN
SELECT * FROM student WHERE NAME='123'
3)理由
为什么第一条语句未加单引号就不走索引了呢?这是因为不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为数值类型再做比较。
11、索引不宜太多,一般5个以内
12、索引不适合建在有大量重复数据的字段上
如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的,如果索引列有大量重复数据,Mysql查询优化器推算发现不走索引的成本更低,很可能就放弃索引了。
13、where限定查询的数据
数据中假定就一个男的记录。
1)反例
SELECT id,NAME FROM student WHERE sex='男'
2)正例
SELECT id,NAME FROM student WHERE id=1 AND sex='男'
3)理由
14、避免在索引列上使用内置函数
业务需求:查询最近七天内新生儿(用学生表替代下)
给birthday字段创建索引:
ALTER TABLE student ADD INDEX idx_birthday (birthday)
当前时间加7天:
SELECT NOW()
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY)
1)反例
EXPLAIN
SELECT * FROM student
WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
2)正例
EXPLAIN
SELECT * FROM student
WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
3)理由
15、避免在where中对字段进行表达式操作
1)反例
EXPLAIN
SELECT * FROM student WHERE id+1-1=+1
2)正例
EXPLAIN
SELECT * FROM student WHERE id=+1-1+1
EXPLAIN
SELECT * FROM student WHERE id=1
3)理由
16、避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先,实在没办法,就只能使用,并不是不能使用。如果不能使用,SQL也就无需支持了。
1)反例
EXPLAIN
SELECT * FROM student WHERE salary!=3000
EXPLAIN
SELECT * FROM student WHERE salary<>3000
2)理由
17、去重distinct过滤字段要少
#索引失效
EXPLAIN
SELECT DISTINCT * FROM student
#索引生效
EXPLAIN
SELECT DISTINCT id,NAME FROM student
EXPLAIN
SELECT DISTINCT NAME FROM student
1)理由
18、where中使用默认值代替null
环境准备:
#修改表,增加age字段,类型int,非空,默认值0
ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;
#修改表,增加age字段的索引,名称为idx_age
ALTER TABLE student ADD INDEX idx_age (age);
1)反例
EXPLAIN
SELECT * FROM student WHERE age IS NOT NULL
2)正例
EXPLAIN
SELECT * FROM student WHERE age>0
3)理由
三、高级SQL优化
1、批量插入性能提升
大量数据提交,上千,上万,批量性能非常快,mysql独有。
1)多条提交
INSERT INTO student (id,NAME) VALUES(4,'name1');
INSERT INTO student (id,NAME) VALUES(5,'name2');
2)批量提交
INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');
3)理由
2、批量删除优化
避免同时修改或删除过多数据,因为会造成cpu利用率过高,会造成锁表操作,从而影响别人对数据库的访问。
1)反例
#一次删除10万或者100万+?
delete from student where id <100000;
#采用单一循环操作,效率低,时间漫长
for(User user:list){
delete from student;
}
2)正例
#分批进行删除,如每次500
for(){
delete student where id<500;
}
delete student where id>=500 and id<1000;
3)理由
3、伪删除设计
1)商品状态(state)
2)理由
4、提高group by语句的效率
可以在执行到该语句前,把不需要的记录过滤掉。
1)反例:先分组,再过滤
select job,avg(salary) from employee
group by job
having job ='president' or job = 'managent';
2)正例:先过滤,后分组
select job,avg(salary) from employee
where job ='president' or job = 'managent'
group by job;
5、复合索引最左特性
创建复合索引,也就是多个字段。
ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)
满足复合索引的左侧顺序,哪怕只是部分,复合索引生效。
EXPLAIN
SELECT * FROM student WHERE NAME='name1'
没有出现左边的字段,则不满足最左特性,索引失效。
EXPLAIN
SELECT * FROM student WHERE salary=3000
复合索引全使用,按左侧顺序出现 name,salary,索引生效。
EXPLAIN
SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000
虽然违背了最左特性,但MYSQL执行SQL时会进行优化,底层进行颠倒优化。
EXPLAIN
SELECT * FROM student WHERE salary=3000 AND NAME='name1'
1)理由
6、排序字段创建索引
什么样的字段才需要创建索引呢?原则就是where和order by中常出现的字段就创建索引。
#使用*,包含了未索引的字段,导致索引失效
EXPLAIN
SELECT * FROM student ORDER BY NAME;
EXPLAIN
SELECT * FROM student ORDER BY NAME,salary
#name字段有索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME
#name和salary复合索引
EXPLAIN
SELECT id,NAME FROM student ORDER BY NAME,salary
EXPLAIN
SELECT id,NAME FROM student ORDER BY salary,NAME
#排序字段未创建索引,性能就慢
EXPLAIN
SELECT id,NAME FROM student ORDER BY sex
7、删除冗余和重复的索引
SHOW INDEX FROM student
#创建索引index_name
ALTER TABLE student ADD INDEX index_name (NAME)
#删除student表的index_name索引
DROP INDEX index_name ON student ;
#修改表结果,删除student表的index_name索引
ALTER TABLE student DROP INDEX index_name ;
#主键会自动创建索引,删除主键索引
ALTER TABLE student DROP PRIMARY KEY ;
8、不要有超过5个以上的表连接
9、inner join 、left join、right join,优先使用inner join
三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小。
1)理由
10、in子查询的优化
日常开发实现业务需求可以有两种方式实现:
如需求:查询所有部门的所有员工:
#in子查询
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);
#这样写等价于:
#先查询部门表
SELECT id FROM tb_dept
#再由部门dept_id,查询tb_user的员工
SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id
假设表A表示某企业的员工表,表B表示部门表,查询所有部门的所有员工,很容易有以下程序实现,可以抽象成这样的一个嵌套循环:
List<> resultSet;
for(int i=0;i
上面的需求使用SQL就远不如程序实现,特别当数据量巨大时。
1)理由
- 数据库最费劲的就是程序链接的释放。假设链接了两次,每次做上百万次的数据集查询,查完就结束,这样就只做了两次;相反建立了上百万次链接,申请链接释放反复重复,就会额外花费很多实际,这样系统就受不了了,慢,卡顿。
11、尽量使用union all替代union
1)反例
SELECT * FROM student
UNION
SELECT * FROM student
2)正例
SELECT * FROM student
UNION ALL
SELECT * FROM student
3)理由
- union和union all的区别是,union会自动去掉多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复;
- union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
- union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。
作者丨布诺i
来源丨网址:https://blog.csdn.net/weixin_53601359/article/details/115553449
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn
关注公众号【dbaplus社群】,获取更多原创技术文章和精选工具下载
页面更新:2024-03-21
本站资料均由网友自行发布提供,仅用于学习交流。如有版权问题,请与我联系,QQ:4156828
© CopyRight 2008-2024 All Rights Reserved. Powered By bs178.com 闽ICP备11008920号-3
闽公网安备35020302034844号