mysql容易忽略的影响查询效率的点

有时候写sql语句，会忽略一些点，导致引起不必要的查询问题，所以记录一下，提醒自己，也希望能提醒大家，对于explain的type和extra解释可以参考我另外两篇文章extra，type
（1）负向比较（例如：!=）会引发全表扫描；
（2）如果允许空值，不等于(!=)的查询，不会将空值行(row)包含进来，此时的结果集往往是不符合预期的，此时往往要加上一个or条件，把空值(is null)结果包含进来；
（3）or可能会导致全表扫描，此时可以优化为union查询；

  explain select * from user where id=1;

索引字段id上的等值查询，能命中索引 （1）type=ref，走非唯一索引；（2）rows=1，预估扫描1行；

explain select * from user where id is null;

索引字段id上的null查询，也能命中索引（1）type=ref，走非唯一索引；（2）rows=1，预估扫描1行；

explain select * from user where id=1 or id is null;

如果放到一个SQL语句里用or查询，则会全表扫描（1）type=ALL，全表扫描；（2）rows=4，全表只有4行；

 explain select * from user where id=1 
    union
    select * from user where id is null;

此时应该优化为union查询，又能够命中索引了（1）type=ref，走非唯一索引；2）rows=1，预估扫描1行；

（4）建表时加上默认(default)值，这样能避免空值的坑；

回表查询： 普通索引无法直接定位行记录，所以要先定位主键值，再定位行记录，所以 它的性能较扫一遍索引树更低。

利用索引覆盖来优化SQL？
场景1：全表count查询优化
原表为：user(PK id, name, sex)；
直接：select count(name) from user;
不能利用索引覆盖。
添加索引：alter table user add key(name);
就能够利用索引覆盖提效。
场景2：列查询回表优化
select id,name,sex … where name=‘tianyuan’;
这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
场景3：分页查询
select id,name,sex … order by name limit 500,100;
将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

另：如果是InnoDB存储引擎，主键不宜过长；如果是MyISAM存储引擎，影响不大；