MYSQL的索引使用注意

索引并不是时时都会生效的，比如以下几种情况，将导致索引失效 

最左前缀法则

show index from tb_user;

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。

 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

explain select * from tb_user where profession = '软件工程' and status = '0';

思考 

范围查询

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0' ;

explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

索引列运算

不要在索引列上进行运算操作， 索引将失效。在tb_user表中，除了前面介绍的联合索引之外，还有一个索引，是phone字段的单列索引。

explain select * from tb_user where phone = '17799990015';

当根据phone字段进行函数运算操作之后，索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

 字符串类型的字段，不加单引号

 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

模糊查询时， % 加在关键字之后

explain select * from tb_user where profession like '软件%';

模糊查询时， % 加在关键字之前

explain select * from tb_user where profession like '%工程';

我们发现，在 like 模糊查询中，在关键字后面加 % ，索引可以生效。而如果在关键字

前面加了 % ，索引将会失效。

or连接条件

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

explain select * from tb_user where id = 10 or age = 23;

由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

create index idx_user_age on tb_user(age);

再次执行上述的SQL语句

 当or连接的条件，左右两侧字段都有索引时，索引才会生效。

数据分布影响

explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

 SQL提示

use index

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

 ignore index

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index

强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

查询id,profession,age, status字段

explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;

 查询id,profession,age, status,name字段

explain select id,profession,age, status,name from tb_user where profession = '软件工程' and age = 31 and status = '0' \G;

前缀索引

语法

create index idx_xxxx on table_name(column(n)) ; 1

前缀长度

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

create index idx_email_5 on tb_user(email(5));

我们先来看看 tb_user 表中目前的索引情况， 在查询出来的索引中，既有单列索引，又有联合索引。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引。

总结

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。