MySQL该怎么提高查询效率?where条件优化.....

“ 本文知识点较多，篇幅较长，请耐心学习！”

• 优先建立索引

        查询应该优先提高存储结构，索引底层主要用B+实现，合理使用索引可以大幅度提高查询效率，这里说到了合理使用，如果不合理使用不会提升反而还会占用空间。首先应考虑在 where 及 order by 涉及的列上建立索引

• where条件中的小坑

    记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中

         2. 避免在索引列上使用IS NULL和IS NOT NULL

低效: (索引失效)

select id from t where num is null;

高效：(索引有效) 

-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0;

-- 因为此时到了B+树的第一根节点，人家都不知道怎么走，没有方向。你说abc，-- 那我到底走左边还是右边呢，但是如果你 like ab%，那我可就知道了，走ab开头的这边select id from t where name like '%abc%';

        4. 对于连续的数值，能用between and 就别用in

-- 对于范围查找，索引是非常建议且支持的，尽量能在一次遍历就能查找到全部需要的数据select id from t where num between 1 and 3;

        5. 避免在 WHERE 子句中对字段进行表达式操作

--包括不限于函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引-- 低效：select id from t where num/2=100;-- 高效：select id from t where num=100*2;-- 理由很简单，B+索引在你查找之前还是给你计算那如果你并非连续的岂不是要算很多次？

        6. 避免在WHERE 子句中对字段进行函数操作

-- 低效：-- name以abc开头的id ：select id from t where substring(name,1,3)='abc'-- '2021-09-13'生成的id：select id from t where datediff(day,createdate,'2021-09-13')=0-- 高效：select id from t where name like 'abc%'select id from t where createdate>='2021-09-12' and createdate<'2021-09-13'

• 用UNION替换OR (适用于索引列)
  

-- 低效: (索引失效)select id from t where num=10 or num=20;--高效：(索引有效) select id from t where num=10union all select id from t where num=20-- 这里提到一个onion和onion all的区别：简单来说：union去重并组合表 unionall不去重并且组合表                                   

• 复合索引使用注意事项：

      在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致

• 用EXISTS替代IN、用NOT EXISTS替代NOT IN        
  

     在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS

-- 低效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)

• 当索引数目大于一定比例时，索引则无效

    并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用

        到这里就涉及到一个优化器，可以去看我的第二篇文章，MySQL的执行流程。

•     索引数目最好保持在6个左右

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

•     SELECT子句中避免使用‘*’
  

           任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

•     每个表都应该设置一个ID主键        
  

           每个表都应该设置一个ID主键，最好的是一个INT型，并且设置上自动增加的  AUTO_INCREMENT标志，这点其实应该作为设计表结构的第一件必然要做的事，这里可以大幅度提高查询效率，因为有个聚簇索引。

•     最后一点 永远别要用复杂的mysql语句来显示你的聪明
  

          就我来说，看到一次关联了三，四个表的语句，只会让人觉得很不靠谱

以上优化，包括不限于Where条件语句优化，以及在where时索引如何使用，哪些失效以及避免产生的问题的总结

好了要睡觉了，00点04分，good night！！！