持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第9天，点击查看活动详情

记一些SQL注意事项

作为后端开发, 不可避免的要接触到数据库, 其中又一MySQL数据库居多, 在编写SQL过程中, 时常不小心就写出点BUG, 甚至严重的直接导致线上服务崩溃, 下面我们来看一些比较典型的问题:

delete from a where col = xx;

我们知道delete语句在执行过程中, 会将所有要删除的行加锁, 并且如果where条件没有加索引还会锁表, 上面这条SQL语句如果一次性删除很多行数据, 事务过长, 产生大量日志, 甚至可能使CPU飙升, 导致其他语句await, 直接造成业务不可用;

如果需要清空表数据, 可以直接truncate table截断表, 不应该使用delete语句清空表; 如果要删除很多数据, 必须在delete语句后加limit分多次执行, 直到执行结果为0, 注意不要开启多个连接同时执行, 有可能造成死锁

select id, name from a order by create_time desc limit 1, 20;

上面是一个非常常见的分页查询SQL, 使用创建时间倒序排列, 一切看似很正常 但是当出现当前页的最后一条数据的create_time和下一页的第一条create_time相同的时候, 就会出现排序错乱, 你会发现当前页的数据还会出现在下一页中,

这里的问题就是排序字段没有确保唯一性, MySQL无法确定相同的值如何排序, 所以我们在写有分页排序这种需求的sql时候, 我们可以添加辅助排序字段来确保排序字段的唯一性, 如 select id, name from a order by create_time desc, id desc limit 1, 20; , 当create_time相同时使用id排序

group by 和 distinct

distinct 是所有字段都不相同才合并, 用于数据去重

group by 用于数据分组聚合计算, 其他字段可以不一样只保留一条, MySQL5.7以上默认不支持group by多个列, 配置文件加上sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'开启支持

索引失效 使用EXPLAIN关键字分析索引使用情况

• 全模糊查询
• 函数计算, 如abs(a1), MySQL8之前不支持函数索引
• 值类型不同(类型转换), 如字段是varchar类型, 值是int类型
• in/not in的值过多, 考虑换成join方式
• 查询范围过大, 如时间范围过大, 考虑将时间范围拆分然后使用UNION ALL合并结果
• 索引不合理, 索引列的区分度不高