数据库优化篇(二)—— SQL语句优化建议
书写高质量SQL优化建议
1、尽量使用 varchar 代替 char
# 反例:
`userName` char(100) DEFAULT NULL COMMENT '用户名';
# 正例:
`userName` varchar(100) DEFAULT NULL COMMENT '用户名'
原因:因为首先变长字段存储空间小,可以节省存储空间。
2、如果修改/更新数据过多,考虑批量进行
# 反例:
delete from account limit 500000;
# 正例:
for each(200次)
{
delete from account limit 500;
}
3、如果知道查询结果只有一条或者只要最大/最小一条记录,建议用 limit 1
# 反例:
select id,name from employee where name='Cherich';
# 正例:
select id,name from employee where name='Cherich' limit 1;
原因:加上 limit 1 后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。如果 name 是唯一索引的话,是不必要加上 limit 1 了,因为 limit 的存在主要就是为了防止全表扫描,从而提高性能。
4、避免在 where 子句中使用 or 来连接条件
# 反例:
select * from user where userid=1 or age =18;
# 正例:
# 使用union all
select * from user where userid=1
union all
select * from user where age = 18;
# 或者分开两条sql写:
select * from user where userid=1;
select * from user where age = 18;
原因:使用 or 可能会使索引失效,从而全表扫描。对于 or+没有索引的 age 这种情况,假设它走了 userId 的索引,但是走到 age 查询条件时,它还得全表扫描,也就是需要三步过程:全表扫描+索引扫描+合并,如果它一开始就走全表扫描,直接一遍扫描就完事。
5、 优化 limit 分页
# 反例:
select id,name,age from employee limit 10000,10;
# 正例:
# 方案一 :返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10.
# 方案二:order by + 索引
select id,name from employee order by id limit 10000,10
原因:当偏移量大的时候,查询效率就会越低,因为 MySQL 并非是跳过偏移量直接去取后面的数据,而是先走偏移量+要取的条数,然后再把前面偏移量这一段的数据抛弃掉再返回的。如果使用优化方案一,返回上次最大查询记录(偏移量),这样可以跳过偏移量,效率提升不少。方案二使用 order by+索引,也是可以提高查询效率的。
6、 优化 like 模糊查询语句
# 反例:
select userId,name from user where userId like '%王';
# 正例:
select userId,name from user where userId like '王%';
原因:把 % 放前面,并不走索引,放在后面,才会走索引,效率才会更高。
7、 where 从句中不对列进行函数转换和表达式计算
# 需求:查询最近3天内登陆过的用户(假设 lastTime 加了索引)。
# 反例:
select userId,lastTime from loginuser where Date_ADD(lastTime,Interval 3 DAY) >=now();
# 正例:
select userId,lastTime from loginuser where lastTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
原因:索引列上使用mysql的内置函数,索引失效。
8、尽量避免在 where 子句中使用!=或<>操作符
# 反例:
select age,name from user where age <>18;
# 正例:
select age,name from user where age <18;
select age,name from user where age >18;
原因:使用!=和<>很可能会让索引失效,放弃使用索引而进行全表扫描。
9、 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型
原因:相对于数字型,字符型会降低查询和连接的性能,并会增加存储开销。
10、 SQL 语句中连接多个表时,请使用表的别名,使语义更加清晰
11、为了提高 group by 语句的效率,可以在执行到该语句前,用where把不需要的记录过滤掉
# 反例
select job,avg(salary) from employee group by job having job ='president'
or job = 'managent'
# 正例
select job,avg(salary) from employee where job ='president'
or job = 'managent' group by job;
12、 where 后面的字段,留意其数据类型的隐式转换
select * from user where userid ='2021';