江苏龙网

  1. 主页 > 生活百科 > >

30条书写高质量SQL的建议，太有用了！( 四 )

SQL


如果 MySQL 优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件 !=，>isnull，isnotnull 经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的 。
如果把 null 值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点 。
19、不要有超过 5 个以上的表连接
连表越多，编译的时间和开销也就越大 。把连接表拆开成较小的几个执行，可读性更高 。如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了 。
20、exist&in 的合理利用
假设表 A 表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下 SQL：
select * from A where deptId in (select deptId from B); 这样写等价于：
先查询部门表Bselect deptId from B再由部门deptId，查询A的员工select * from A where A.deptId = B.deptId可以抽象成这样的一个循环：
List<> resultSet ;for(int i=0;i<B.length;i++) {for(int j=0;j<A.length;j++) {if(A[i].id==B[j].id) {resultSet.add(A[i]);break;}}}显然，除了使用 in，我们也可以用 exists 实现一样的查询功能，如下：
select * from A where exists (select 1 from B where A.deptId = B.deptId);  因为 exists 查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果(true 或者 false)，来决定主查询的数据结果是否得意保留 。
那么，这样写就等价于：
select * from A,先从A表做循环select * from B where A.deptId = B.deptId,再从B表做循环.同理，可以抽象成这样一个循环：
List<> resultSet ;for(int i=0;i<A.length;i++) {for(int j=0;j<B.length;j++) {if(A[i].deptId==B[j].deptId) {resultSet.add(A[i]);break;}}}数据库最费劲的就是跟程序链接释放 。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次;相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了 。
即 MySQL 优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优 。
因此，我们要选择最外层循环小的，也就是，如果 B 的数据量小于 A，适合使用 in，如果 B 的数据量大于 A，即适合选择 exist 。
21、尽量用 union all 替换 union
如果检索结果中不会有重复的记录，推荐 union all 替换 union 。
反例：
select * from user where userid=1 unionselect * from user where age = 10正例：
select * from user where userid=1 union allselect * from user where age = 10理由：如果使用 union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序 。
如果已知检索结果没有重复记录，使用 union all 代替 union，这样会提高效率 。
22、索引不宜太多，一般 5 个以内
原因如下：

  • 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率 。
  • insert 或 update 时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定 。
  • 一个表的索引数最好不要超过 5 个，若太多需要考虑一些索引是否没有存在的必要 。
23、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型
反例：
king_id` varchar(20) NOT NULL COMMENT '守护者Id' 正例：
`king_id` int(11) NOT NULL COMMENT '守护者Id'` 理由：相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销 。
24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段
因为 SQL 优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，MySQL 查询优化器推算发现不走索引的成本更低，很可能就放弃索引了 。
25、尽量避免向客户端返回过多数据量
假设业务需求是，用户请求查看自己最近一年观看过的直播数据 。
反例：
//一次性查询所有数据回来select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)正例：
//分页查询select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;


推荐阅读


上一篇：ASP.NET Core和Https

下一篇：菊花茶选择什么牌子的好,哪个牌子的桐乡胎菊最好