数据库

SQL书写建议

Posted by Laiaike on 2022-11-30

SQL书写建议

索引失效

1. 避免在where子句中使用or连接条件

  • 可能造成索引失效，导致全表扫描。
1
2
3
4
5
6
-- 反例
select * from user where user_id = 1 and age = 18;
-- 正例
select * from user where user_id = 1 
union all
select * from user where age = 18;

2. 优化like语句

  • 可能引起索引失效（最左前缀匹配）。

  • 使用覆盖索引（无需回表）优化。

1
2
-- 正例，其中id为主键，user_id为普通索引
select id, user_id from user where user_id like '%123%';

3. 尽量避免在索引列上使用MySQL的内置函数

1
2
3
4
5
-- 反例
select user_id, login_time from login_info where Date_ADD(login_time, Interval 7 DAY) >=  now();

-- 正例，把内置函数移到右边
select user_id, login_time from login_info where login_time >= Date_ADD(now, Interval - 7 DAY)

4. 避免在where子句中使用表达式, <>, !=

  • 造成索引失效，全表扫描。

  • 如<>，改为两条 > & <。

5. 使用联合索引时，注意索引列的顺序

  • 当创建了（a, b, c），相当于创建了（a）（a, b）（a, b, c）三个索引。

6. where子句中考虑使用默认值代替null

  • 可能导致索引无效。
1
2
3
4
-- 反例
select * from user where age is not null;
-- 正例
select * from user where age > 0;
  • 并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关。
  • 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效。
  • 一般情况下，查询的成本高，优化器自动放弃索引的。
  • 如果把null值，换成默认值，很多时候让走索引成为可能。

7. 类型转换

  • 如字段是字符串类型，但是查询的时候使用了数字型，将造成索引失效。

其他

1. 使用select具体字段，而不是select *

  • select * 可能不会使用覆盖索引，造成回表查询。

2. 查询结果只有一条或只要最大/最小的一条记录，建议使用limit 1

  • 如果字段是唯一索引，不必加limit 1。

3. 优化limit分页

  • MySQL将查询偏移量 + 要取的条数 - 偏移量。
1
2
3
4
-- 反例
select id, name, age from user limit 10000, 10;
-- 正例
select id, name, age from user where id > 10000 limit 10;

4. 使用where条件限定要查询的数据，避免返回多余的行

5. inner join, left join, right join中优先使用inner join(join)

  • 如果inner join造成内容减少，需使用left join的时候，应使left表尽量小（不单指行数，列数也要考虑）。

6. 考虑在where, order by涉及的列添加索引

7. 插入数据过多，考虑批量插入

8. distinct在字段很多的情况下可能降低效率

9. 删除冗余索引

  • 索引的维护，优化器选择索引等均可能影响性能。

10. 如果数据量较大，优化修改/删除语句

  • 避免同时修改或删除过多数据，造成cpu利用率过高，影响别人对数据库的访问。
1
2
-- 反例
delete from user where id < 10000;

11. 不要有超过5个以上的表连接

  • 需要更多的时间和开销。

  • 可读性低。

  • 如果的确需要许多表，说明设计存在一定问题。

12. 合理使用exist和in

  • exists先查主查询。

  • in先查子查询。

1
2
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id = B.id);

如果B的数据量大于A，适合选择exists，如果B的数据量小于A，适合使用in。

13. 尽量使用union all替换union

  • UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。如果表数据量大的话可能会导致用磁盘进行排序。

  • 推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

14. 索引不宜太多，一般5个以内

  • 提高了查询效率，降低了插入和更新的效率。TODO

  • insert和update有时可能重建索引。

15. 尽量使用数字型

16. 索引不适合建在有大量重复数据的字段上，如性别

17. 尽量避免向客户端返回过多的数据量

18. 尽可能使用varchar代替char

  • 节省存储空间。

  • 查询时在较小的空间查询，效率更高。

19. 随手explain

FEATURED TAGS
数据库
FRIENDS