MySQL的各类小记录

在面试数据库这部分的内容的时候，很喜欢问关于sql优化的问题；
下面总结一下：

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num = 0；
3. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要；
4. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
5. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20；
6. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段；
7. 范式优化，消除冗余；
8. 用where子句替换掉hanving子句。
9. 硬件优化和Java代码的优化！！！

-----------------------------------2018年 12月12日
这次记录一个SQL，关于or和in的使用。在查询的时候，由于最后一个条件是多个，我最开始的想法是用or连接。于是写出了这样的SQL：

SELECT 
  COUNT(1) 
FROM
  geo_bed t1 
  LEFT JOIN geo_room t2 
    ON t1.`RM_ID` = t2.`RM_ID` 
  LEFT JOIN geo_floor t3 
    ON t2.`FL_ID` = t3.`FL_ID` 
  LEFT JOIN geo_building t4 
    ON t3.`BL_ID` = t4.`BL_ID` 
  LEFT JOIN geo_district t5 
    ON t2.`DISTRICT_ID` = t5.`DISTRICT_ID` 
  LEFT JOIN geo_bed_label t6 
    ON t1.`BED_ID` = t6.`BED_ID` 
  LEFT JOIN geo_bed_label_detail t7 
    ON t6.`Label_ID` = t7.`LABEL_ID` 
WHERE 1 = 1 
 AND t4.`BL_ID` = 19 
  AND t1.`STATUS` = 1 
  OR t1.`STATUS` = 4 
  OR t1.`STATUS` = 5 

很显然，and和or这样使用是不对的，为啥我会发现不对呢。因为得到的数据是错误的。于是口袋说了个in(1,4,5)。 使用in 进行连接多个条件

SELECT 
  COUNT(1) 
FROM
  geo_bed t1 
  LEFT JOIN geo_room t2 
    ON t1.`RM_ID` = t2.`RM_ID` 
  LEFT JOIN geo_floor t3 
    ON t2.`FL_ID` = t3.`FL_ID` 
  LEFT JOIN geo_building t4 
    ON t3.`BL_ID` = t4.`BL_ID` 
  LEFT JOIN geo_district t5 
    ON t2.`DISTRICT_ID` = t5.`DISTRICT_ID` 
  LEFT JOIN geo_bed_label t6 
    ON t1.`BED_ID` = t6.`BED_ID` 
  LEFT JOIN geo_bed_label_detail t7 
    ON t6.`Label_ID` = t7.`LABEL_ID` 
WHERE 1 = 1 
 AND t4.`BL_ID` = 19    
  AND   t1.`STATUS` IN(1,4,5)

这样是正确的，并且看着SQL很厉害的样子。继续查询才知道，and和or是可以连接的，只是刚刚的写法有问题，正确的写法：

SELECT 
  COUNT(1) 
FROM
  geo_bed t1 
  LEFT JOIN geo_room t2 
    ON t1.`RM_ID` = t2.`RM_ID` 
  LEFT JOIN geo_floor t3 
    ON t2.`FL_ID` = t3.`FL_ID` 
  LEFT JOIN geo_building t4 
    ON t3.`BL_ID` = t4.`BL_ID` 
  LEFT JOIN geo_district t5 
    ON t2.`DISTRICT_ID` = t5.`DISTRICT_ID` 
  LEFT JOIN geo_bed_label t6 
    ON t1.`BED_ID` = t6.`BED_ID` 
  LEFT JOIN geo_bed_label_detail t7 
    ON t6.`Label_ID` = t7.`LABEL_ID` 
WHERE 1 = 1 
 AND t4.`BL_ID` = 19    
  AND (t1.`STATUS` = 1 
  OR t1.`STATUS` = 4 
  OR t1.`STATUS` = 5 )

这样的or和in可以达到一样的效果。于是继续探究了or+（）与 in的区别是啥。
《mysql数据库开发的36条军规》里面提到：or和in的效率问题，文中提到or的效率为O(n)，而in的效率为O(logn), 当n越大的时候效率相差越明显。
具体有实验结果：
第一种：可以看出如果in和or所在列有索引或者主键的话，or和in没啥差别，执行计划和执行时间都几乎一样。
第二种：如果in和or所在列没有索引的话，性能差别就很大了。在没有索引的情况下，随着in或者or后面的数据量越多，in的效率不会有明显的变化，但是or会随着记录越多的话性能下降非常厉害，从第三中测试情况中可以很明显地看出了，基本上是指数级增长。
因此在给in和or的效率下定义的时候，应该再加上一个条件，就是所在的列是否有索引或者是否是主键。如果有索引或者主键性能没啥差别，如果没有索引，性能差别不是一点点！
这也是为什么在面试的时候，优化SQL的其中一条就是，在where子句中尽量避免使用or，因为我们需要的列绝大部分会有索引存在的。