面试总结-SQL优化

最近的面试中经常提到这个SQL优化的问题，在这整理下方便记忆

1. SQL语句全部大写 （所有SQL语句执行时都会转换成大写）
2. 避免使用星号 * ,用具体字段替代（使用星号会在查询时增加一个查询列的操作）
3. 尽量避免在 where 子句中对字段进行 null 值判断，最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库 （不然会进行全表扫描，影响效率）
   -反例：select id from t where num is null
   -可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0
4. 应尽量避免在 where 子句中使用 != 或 <> ，in 或 not in 操作符 （同上）
5. 能用between就不要使用in，或者用exists替换in
   -select num from a where num in(select num from b)
   用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
6. 尽可能的使用 varchar/nvarchar 代替 char/nchar （节省字段存储空间）

Mysql分页优化

Mysql在使用limit进行分页查询时有明显的缺陷，比如：
SELECT * FROM table ORDER BY id LIMIT x,y;
当x越来越大时，因为会查询出x跳记录然后过滤出需要的y条，查询效率会显著降低，为此，有几下集中解决方案：

SELECT * FROM table WHERE id>= (SELECT id FROM table LIMIT1000000, 1) LIMIT 10`

如果id是连续的，建议使用between

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

还有一种，可以在查询下一页时把上一页的行id作为参数传递给客户端程序，然后sql就改成了

select * from table where id>3000000 limit 10;