创建索引
下面所有示例都是围绕这个索引来说
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连接时索引会失效
注:上面都是通过学习自己理解后写下来的。如果发现不对有不对的麻烦指出错误,以免误导其他人