mysql 停止索引事务 mysql如何避免索引失效

MySQL查询语句如何避免索引失效

前言

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。因为采用索引查询数据的效率要比不使用索引查询的效率高，但是有些SQL查询语句会使得索引失效，导致利用索引提高效率的优势失去，本文讲解如何避免索引失效。

避免索引失效的规则

1.全值匹配 。对索引中所有列都指定具体值，这时索引生效，执行效率高

示例：

# 建立复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
# 为复合索引的每一个列指定具体值
select * from tb_seller where name='小米科技' and status='1' and address='北京市';

2.最左前缀法则

指的是对于复合索引，查询包含的索引，要从索引的最左前列开始，并且不跳过索引中的列。

• 如果没有从最左前列开始，则索引失效；
• 如果从最左前列开始，但是中间跳过索引，则只有跳过这个索引之前的索引有效，而后面的索引失效。

示例1：

# 因为复合索引 idx_seller_name_sta_addr 索引顺序依次是 name status address，下面的语句符合最左前缀
explain select * from tb_seller where name='小米科技';

示例2：

# 根据最左前缀法则，使用索引status之前必须同时使用name,所以以下查询不走索引
explain select * from tb_seller where status='1';

示例3：

# 因为下面的语句跳过了 status 索引，虽然也走了索引，但是比较示例2键的长度，可以看出它仅仅走了name的索引
select * from tb_seller where name='小米科技' and address='西安市';

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

对于范围查询之前使用的索引会有效，而范围查询之后，就算使用索引也会失效。

示例：

# status>'1'是范围查询，因此其后面的address索引失效
select * from tb_seller where name='小米科技' and status>'1' and address='西安市';

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

示例：

select * from tb_seller where substring(name,3,2)='科技' and status>'1' and address='西安市';

5. 数字型的字符串不加单引号，会造成包括其之后的索引失效。

示例：

# 因为status的值虽然是数值型的，但它本身是字符串类型，如果不加单引号，会导致其以及其后的索引失效
select * from tb_seller where name='小米科技' and status=1 and address='西安市';

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

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。如果查询列，超出索引列，也会降低性能。

示例：

# 访问是索引字段，效率要比访问非索引字段或select * 效率高
select name,status,address from tb_seller where name='小米科技' and status='1' and address='西安市';

Extra列的一些取值含义：

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表
查询数据

7. 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

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

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
• 解决方案：通过覆盖索引来解决，即查询(select)的都是索引列。

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

MySQL发现搜索的索引字段的值在数据库中占的比例非常大，那么不走索引，会更快。

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

原因和第9条一样，如果查询字段的数据值在数据库中占比很大，则数据库认为不走索引更好，使索引失效。

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

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