MySQL避免索引失效

创建索引

下面所有示例都是围绕这个索引来说

ALTER TABLE users ADD INDEX idx_users_accountSexUsername(account,sex,userName);

一、全值匹配

指的是创建的索引在查询条件里全部使用到，这种情况索引一般都不会失效

把where后面userName和sex的顺序反过来索引也不会失效

 

二、左前缀法则：

指的是查询从索引的最左前列开始并且不跳过索引中的列。

因为创建索引的顺序是account→userName→sex，下面案例没有使用account就直接使用了userName和sex导致索引都失效了

 

三、不在索引列上做任何操作

在索引列上做计算，函数，类型转换等操作，会导致索引失效而转向全表扫描

从下图可以看出实际上条件都是account = '13521214507'，但是图1.使用了函数索引就失效了

因为索引是在创建的时候已经进行了排序，所以对索引字段进行其他操作要重新排序这样就会导致索引失效

图1.

图2.

 

四、不能使用范围条件右边的列

存储引擎不能使用索引中范围条件右边的列

图1where后面使用了account和sex，key_len的值为68.再看看图2的条件比图1多一个userName，但是key_len也是68说明userName的索引已经失效了

图1.

图2.

注：使用不等于(!=或<>)，is nul，is not null，like等,like会在第六点做具体说明

 

五、尽量使用覆盖索引

只访问索引的查询(索引列和查询列一致)，减少使用select *

覆盖索引就是说select后面的字段只能包括建立索引的字段，如图2我们创建索引的字段是account,userName,sex，select后面的字段也是account,userName,sex或只有userName,sex，就是说select后面的字段只能是建立了索引的字段，这才叫覆盖索引。

通过图1和图2对比，图2的Extra比图1的多了Using index，此时查询的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回查询列表中的字段，而不必根据索引再次读取数据文件

图1.

图2.

 

六、like以通配符开头导致索引失效

like以通配符开头mysql索引失效会变成全表扫描，如图1

但是把通配符换到结尾就索引就不会失效。这个原理跟第三点的原因是一样的。比如说创建索引的时候会根据首字母进行排序，如果你把首字母打乱了，就需要重新做排序了

图1.

图2.

 

很多情况下都是需要用到Like '%字符串%' 但是使用通配符又会导致索引失效，这时候我们就要用到覆盖索引。

如下图，使用了覆盖索引之后使用like '%字符串%' 就不会索引失效

 

七、其他补充

1.字符串不加单引号索引会失效(这个规范问题就不多说了)

 

2.用or连接时索引会失效

 

 

注：上面都是通过学习自己理解后写下来的。如果发现不对有不对的麻烦指出错误，以免误导其他人