mysql避免索引失效

索引设计原则：

     索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高 效的使用索引。 对查询频次较高，且数据量比较大的表建立索引。

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑 选最常用、过滤效果最好的列的组合。 使用唯一索引，区分度越高，使用索引的效率越高。 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨 船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然 会找到一个可用的索引，但无疑提高了选择的代价。 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效 率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有 效的提升MySQL访问索引的I/O效率。 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了 组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引: CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于 对name 创建索引 ; 对name , email 创建了索引 ; 对name , email, status 创建了索引 ;

通过以下几种方式避免索引失效：

1. 全值匹配

对索引中所有列都指定具体值

 2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

查询条件是name ，会使用索引

查询条件是name 、status ，会使用索引

查询条件是status 、address ，不会使用索引，因为不符合最左匹配原则

查询条件是name、address 使用了索引，但只使用了name的索引

3.范围查询右边的列，不能使用索引

name 和 status 使用到了索引 但address没有使用到索引

4.不要在索引列上进行运算操作， 索引将失效

5.字符串不加单引号，造成索引失效

以下sql 走了索引，但是使用了name字段的索引

 如下sql  status 加上'' ,name 和 status 都使用了索引 

6. 尽量使用覆盖索引，避免select * 

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 

如下sql 使用了索引 但是获取的是所有列 还会涉及到回表查询，所以Extra字段是 Using index condition

 

password 未建索引  所以也会涉及到回表查询

TIP :

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据 

7. 用or分割开的条件

如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 示例，name字段是索引列 ， 而ninkname不是索引列，中间是or进行连接是不走索引的 ：

如下sql  使用and 会走索引

8. 以%开头的Like模糊查询，索引失效

如下所示 %开头就不走索引

 解决方案 ： 通过覆盖索引来解决%在前面而索引失效的问题

9. 如果MySQL评估使用索引比全表更慢，则不使用索引

address先创建索引

如下sql 理应使用索引  实际并没有使用索引

如下sql 只是address改成西安市 却使用了索引

为什么第二条sql 使用索引 而第一天未使用，看表数据可知，北京市有11条数据，占总数据量的50%以上，mysql认为全表扫描更快

10. is NULL ， is NOT NULL 有时索引失效

mysql 数据库底层自行判断，如果走全表扫描快就走全表扫描。因为表中的数据都不是null  所以以下sql中第二条sql 不会使用索引

is null 使用了索引

is not null 未使用索引

 11. in 走索引， not in 索引失效

12. 单列索引和复合索引

尽量使用复合索引，而少使用单列索引 。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： name name + status name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);

create index idx_seller_status on tb_seller(status);

create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

创建单列索引 数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。 

如下图所示，对status  和 address 单独建立索引，最后查询语句使用的是address索引，mysql 会选择最优的那个索引，address只有一条数据匹配

，address这个索引辨识度最高，所以会选择它