Mysql索引失效

最左前缀原则

     最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。是查询要从加了索引的最左边的列开始，而且不跳过加了索引         的列（表示按照加了索引的列的顺序执行）

     两条原则： 加索引的列的第一个列不能丢

                        加索引的列的中间列不能丢会造成后面的加索引列失效。

       表中必须加了复合索引

      简单创建了一个user表，字段有id, name, age，gender, address

//添加复合索引  顺序(name,age,gender)
alter table user add INDEX `sindex` (`name`,`age`,`gender`)  

正确的：

//EXPLAIN 表示执行计划 
EXPLAIN SELECT * FROM user WHERE name = '张三';

EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = '12';

剖析：该查询有name和age条件，根据最佳左前缀原则索引能够被使用到，但是是部分使用。

部分索引失效：

    最左前缀第2个原则：加了索引的多个列，不能跳过列(加索引的顺序不能跳过，如果跳过中间的任意一列，后面加索引的列会      失效)，会造成部分失效

    可以与正确部分中的第二张图中的ref作比较

EXPLAIN SELECT * FROM user WHERE name = '张三' AND gender= '男';

错误的：

EXPLAIN SELECT * FROM user WHERE age = '12' AND gender = '男';

剖析：查询缺少name，查询的条件复合索引最左侧name缺少，不符合最佳左前缀原则，导致索引失效，type属性为ALL，ALL表示全表扫描。

EXPLAIN SELECT * FROM user WHERE  gender = '男';

剖析：查询缺少name和age，查询的条件复合索引最左侧name和age缺少，不符合最佳左前缀原则，导致索引失效，type属性为ALL，ALL表示全表扫描。

 

索引列上做任何计算操作(计算,函数，（自动or手动）类型转换)会导致索引失效

错误的：

EXPLAIN SELECT * FROM user WHERE left(name,2) = '张三';

 

mysql  中like查询是以通配符%开头,会索引失效

错误的：

EXPLAIN SELECT * FROM user WHERE name LIKE '%张';

剖析：对name列做了普通索引，以%开头进行查询，结果索引失效

正确的：

EXPLAIN SELECT * FROM user WHERE name LIKE '张%';

 

Mysql中使用is not null 和is null无法使用索引，导致全表扫描

网上说的是is not null 和 is null 会索引失效，但是本人自己测试并不是就会造成索引失效。

 当我表中数据为2条时，is not null 索引就不会失效。

EXPLAIN SELECT * FROM user WHERE name is not null;

 

当表中数据大于2条时，is not null 索引就会失效。

EXPLAIN SELECT * FROM user WHERE name is not null;

 

当表中数据为2条时，is null 就不会索引失效。

EXPLAIN SELECT * FROM user WHERE name is null;

 

当表中数据大于2条时，is null 就索引失效。

EXPLAIN SELECT * FROM user WHERE name is null;

 

Mysql中使用or连接条件，会导致索引失效，全表扫描。

EXPLAIN SELECT * FROM user WHERE age = '15' or age = '23';

 

Mysql在使用不等于(!=或者<>)，会无法使用索引，全表扫描。

EXPLAIN SELECT * FROM user WHERE age != '23';

EXPLAIN SELECT * FROM user WHERE age <> '23';

 

Mysql中in会索引失效

 失效的原因：

  1. 字段类型

      如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。

      如果字段类型为int，则in查询中的值不需要添加引号，索引也会起叙用。

   2. 数据量

      数据量小的的情况下，mysql会认为扫描全表比使用索引快,自然不会使用索引.

   3. 条件

     如果in后面的条件过多的话，会导致索引失效，mysql会认为全表扫描要比索引快

     数据量大一些的情况下，它会使用索引

    

EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆');

条件过多的情况下索引失效

EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆','上海','天津');

 

  数据量小的情况下，索引失效

EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆');