索引失效原因总结

复合索引使用左前缀（不要跨列或无序使用）

建立了一个索引分别字段为 a b c：

    where a ... and b ... order by c，这种使用顺序就符合最佳做前缀，从左向右依次使用了索引

    where b ... and a ... order by c，这样很明显顺序不对，并不满足最佳左前缀，从而导致了索引失效


复合索引尽量使用全索引匹配：
    建立了一个复合索引 a b c，在查询的时候，尽量把这些索引字段都用上。

不要在索引上进行任何操作（计算（+，-，*，/）、函数）

如果在索引字段上进行任何操作，索引就必将失效（id + 1 > 10）

假设有 a b c 这些字段是复合索引，给 a 字段进行乘法操作，那么b c 字段都将会失效。

非关联子查询与关联子查询

关联子查询和非关联子查询的不同点在于，关联子查询依赖于父查询，父查询和子查询是有联系的。


非关联子查询：

    非相关子查询是独立于外部查询的子查询，子查询执行完毕后将值传递给外部查询。

    select * from emp where sal > (select max(sal) from emp);

    select * from student where student.stu_id in (select stu_id from teacher);


关联子查询：

    外部查询每执行一次，内部查询也会执行一次。

    外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录，之后外部查询根据返回的记录做出决策。

    select * from table1 where exists(select 1 from table2 where id>=30000 and table1.name=table2.name)

    select * from score a where a.score > (select avg(b.score) from score as b where a.cou_id = b.cou_id);

    优化：select * from table1 innner join table2 using(name) where table2.id>=30000    


在使用left jion时，on和where条件的区别如下：

    1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

    2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

    3、on只是对局部条件做限制，而where是对全局做限制

join 时 on 与 where 参考https://blog.csdn.net/wqc19920906/article/details/79785424

like 尽量以常量开头

在模糊查询是，不要以 '%' 开头，否则索引失效。

or 语句前后没有同时使用索引时失效

select * from user where name = '' or age > 0;

当 or 左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。

其他

数据类型出现隐式转化导致索引失效：
    如 varchar 不加单引号（''）的话会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用not，<>，!=可能导致索引失效：
    不等于操作符可能不会用到索引的，产生全表扫描。


not in(1,2)（非子查询）经常索引失效，in(1,2)（非子查询）一般走索引：
    因为认为 not in 时结果集会比较大，而 in 的时候结果集会比较小。

not in 和 not exists 子查询的情况，索引不一定失效：
    select * from student where stu_id not in (select stu_id from teacher);
    select * from student where not exists (select 1 from teacher where teacher.stu_id = student.stu_id);

in、exists 子查询的情况，索引一般也不会失效：
    select * from student where student.stu_id in (select stu_id from teacher);
    select * from student where exists (select 1 from teacher where teacher.stu_id = student.stu_id);

用exists代替in可以提高sql语句的执行效率：
    select 1 from table;
    与 select anycol(目的表集合中的任意一行) from table;
    与select * from table 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。
    select 1 from table; 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表。


如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

转载于:https://www.cnblogs.com/loveer/p/11562821.html