mysql优化资料

全表扫描是数据库搜寻表的每一条记录的过程，直到所有符合给定条件的记录返回为止。通常在数据库中，对无索引的表进行查询一般称为全表扫描；然而有时候我们即便添加了索引，但当我们的SQL语句写的不合理的时候也会造成全表扫描。

以下是经常会造成全表扫描的SQL语句及应对措施：
1. 使用null做为判断条件
如：select account from member where nickname = null;
建议在设计字段时尽量将字段的默认值设为0，改为
select account where nickname = 0;
2. 左模糊查询Like %XXX%
如：select account from member where nickname like ‘%XXX%’ 或者 select account from member where nickname like ‘%XXX’
建议使用
select account from member where nickname like ‘XXX%’
如果必须要用到做查询，需要评估对当前表全表扫描造成的后果；
3. 使用or做为连接条件
如：select account from member where id = 1 or id = 2;
建议使用union all,改为
select account from member where id = 1 union all select account from member where id = 2;
4. 使用in时(not in)
如：select account from member where id in (1,2,3)
如果是连续数据，可以改为
select account where id between 1 and 3;
当数据较少时也可以参考union用法；
或者：
select account from member where id in (select accountid from department where id = 3 )可以改为select account from member where id exsits (select accountid from department where id = 3)
not in 可以对应 not exists;
5.使用not in时
如select account where id not in (1,2,3)
6.使用！=或<>时
建议使用 <,<=,=,>,>=,between等；
7.对字段有操作时也会引起权标索引
如select account where salary * 0.8 = 1000 或者 select account where sustring(nickname,1,3) = ‘zhangxiaolong’;
8.使用count(*)时
如select count(*) from member；
建议使用select count(1) from member;
9.使用参数做为查询条件时
如select account from member where nickname = @name`
由于SQL语句在编译执行时并不确定参数，这将无法通过索引进行数据查询，所以尽量避免；

MySQL · 性能优化 · MySQL常见SQL错误用法
https://zhuanlan.zhihu.com/p/26043916