vlambda博客
学习文章列表

数据库优化篇(二)—— SQL语句优化建议

    书写高质量SQL优化建议

    1、尽量使用 varchar 代替 char
    # 反例:`userName` char(100DEFAULT NULL COMMENT '用户名';# 正例:`userName` varchar(100) DEFAULT NULL COMMENT '用户名'
    原因:因为首先变长字段存储空间小,可以节省存储空间。
    2、如果修改/更新数据过多,考虑批量进行
    # 反例:delete from account limit 500000;# 正例:for each(200次){ delete from account limit 500;}
    原因:大批量操作会会造成主从延迟。大批量操作会产生大事务,阻塞;数据量过大,会把cpu打满。

    3、如果知道查询结果只有一条或者只要最大/最小一条记录,建议用 limit 1

    # 反例:select idname from employee where name='Cherich';# 正例:select idname 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 idname,age from employee limit 1000010;# 正例:# 方案一 :返回上次查询的最大记录(偏移量)select idname from employee where id>10000 limit 10.# 方案二:order by + 索引select idname from employee order by id  limit 1000010
    原因:当偏移量大的时候,查询效率就会越低,因为 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';
    原因:因为加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为数字再做比较,最后导致索引失效。

    好,今天学习到这里。本节已经整理完关于SQL语句优化的一些建议。想要提高查询效率,做好SQL优化工作,不仅要优化语句,还要具备建立索引及索引优化的能力。明天继续学习索引相关知识,一起加油!