mysql查询优化(不定时更新)

1. 不要select *，不要查询不需要的列
2. 不要在多表关联返回全部的列
3. 不要重复查询，应当写入缓存
4. 尽量使用关联查询来替代子查询
5. 尽量使用索引优化。如果不使用索引。mysql则使用临时表或者文件排序。如果不关心结果集的顺序，可以使用order by null 禁用文件排序
6. 优化分页查询，最简单的就是利用覆盖索引扫描，而不是查询所有的列
7. 应尽量避免在 where 子句中使用 !=或<> 操作符，否则将引擎放弃使用索引而进行全表扫描
8. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
9. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

   select * from user where name is null

10. 尽量不要使用前缀%，如：

select * from user where name like '%a'

11. 很多时候用 exists 代替 in 是一个好的选择
12. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
13. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，所以表设计的时候应该尽量避免字段为空,尽可能设默认值,如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0;
14. in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3;
15. 在数据量很大的情况下，查询单条数据,适当的使用LIMIT 1对查询操作的优化效果还是相当明显的;

索引相关

索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

索引的优点

1. 减少服务器扫描表的次数
2. 避免排序和临时表
3. 将随机io变成顺序io

建索引的几大原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

不能使用索引的情况

1. 查询使用了两种排序方向

   select * from user where login_time > '2018-01-01' order by id des ,username asc

2. order by中含有了一个没有 索引的列

   select * from user where name = '11' order by age desc; //age 没有索引

3. where 和 order by 无法形成最左前缀
   • 索引列的第一列是范围条件
   • 在索引列上有多个等于条件，这也是一种范围。不能使用索引