书写高质量sql，避免索引失效问题必看

书写高质量sql，避免索引失效问题必看

1.查询Sql不使用select * ，使用具体字段
原因：只取需要的字段，节省资源、减少网络开销
select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。
2.如果知道查询结果只有一条，或者只要最大/最小一条记录，建议用 limit 1
理由：加上 limit 1 后,只要找到了对应的一条记录,就不会继续向下扫描了，效率将会大大提高；
3. 应尽量避免在 where 子句中使用 or 来连接条件
理由：对于or+没有索引的情况，需要散步过程，全表扫描+索引扫描+合并
4. 优化 limit 分页
日常做分页需求时，一般会用 limit 实现。但是当偏移量特别大的时候，查询效率就变低。
– 方案一 ：返回上次查询的最大记录(偏移量)select id，name from employee where id>10000 limit 10;
– 方案二：order by + 索引select id，name from employee order by id limit 10000，10;
– 方案三：在业务允许的情况下限制页数：
理由：当偏移量最大的时候，查询效率就会越低。因为 MySQL 并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的
5. 优化 like 语句
日常开发中，如果用到模糊关键字查询很容易想到 like，但是 like 很可能让索引失效。
优化：将%放到关键字后面不会造成索引失效，‘123%’
6. 使用 where 条件限定要查询的数据，避免返回多余的行
理由：需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。
7.尽量避免在索引列上使用 MySQL 的内置函数
理由：索引列上使用 MySQL 的内置函数导致索引失效
反例：select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();
正例：explain select userId,loginTime from loginuser where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫
理由：虽然 age 加了索引，但是因为对它进行运算，索引直接迷路了
9. Inner join 、left join、right join，优先使用Inner join，如果是 left join，左边表结果尽量小
理由：如果 inner join 是等值连接，或许返回的行数比较少，所以性能相对会好一点；
同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。
10. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
11. 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则
理由：当我们创建一个联合索引的时候，如 (k1,k2,k3)，相当于创建了 (k1)、(k1,k2) 和 (k1,k2,k3) 三个索引，这就是最左匹配原则。
12. 对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描
13. 在适当的时候，使用覆盖索引
覆盖索引能够使得你的 SQL 语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。
14. 慎用 distinct 关键字
distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。
15. where 子句中考虑使用默认值代替 null
理由：并不是说使用了 is null 或者 is not null 就会不走索引了，这个跟 MySQL 版本以及查询成本都有关
如果把 null 值换成默认值，很多时候让走索引成为可能。同时，表达意思会相对清晰一点
16. 尽量用 union all 替换 union
如果检索结果中不会有重复的记录，推荐 union all 替换 union
理由：如果使用 union，不管检索结果有没有重复，都会尝试进行合并。然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用 union all 代替 union，这样会提高效率。
17. 索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段
因为 SQL 优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，MySQL 查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
18. 使用 explain 分析 SQL 的执行计划
日常开发写 SQL 的时候，尽量养成一个习惯吧。用 explain 分析一下你写的 SQL，尤其是走不走索引这一块。