详细介绍MySQL的索引（下）

索引的使用

同一条数据在未创建索引的情况下耗时：

nick字段是未创建索引的

 select * from t_user WHERE nick = '邹丽';
 SHOW PROFILES;

耗时为：

user_account字段创建了唯一索引
 

 select * from t_user WHERE user_account = '13781945844';
 SHOW PROFILES;

查询耗时为：

当然以上的对比仍然是在表数据量较小的情况下，当表的数据量逐渐增多，创建索引的数据查询耗时是要远低于未创建索引的耗时。

索引失效 

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效（后面的字段索引失效）。

展示如下：

查看该表索引

show index FROM t_user;

可以看到为nick， authority，score这三个字段创建了联合索引，且创建顺序为nick，authority额，score。

按照索引正常查询

SELECT * from t_user WHERE nick = 'PIS' and authority = '1' and score = '11'

查看结果

查看索引覆盖情况

EXPLAIN SELECT * from t_user WHERE nick = 'PIS' and authority = '1' and score = '11';

观察可以发现，这里的索引并未失效。

当我们修改查询条件

EXPLAIN SELECT * from t_user WHERE nick = 'PIS' and authority = '1';

结果如下：

可以发现索引仍未失效。

当我们修改查询顺序

EXPLAIN SELECT * from t_user WHERE authority = '1' and score = '11' and nick = 'PIS';

 

MySQL会进行查询优化，当最左边的索引条件出现后，但可能不符合创建索引的顺序时，MySQL会自动优化查询，将查询条件重新组合。 

最左前缀法则

EXPLAIN SELECT * from t_user WHERE nick = 'PIS' and score = '11';

可以看到score索引发生了失效，这是因为没有符合最左前缀法则。

再举一个例子

EXPLAIN SELECT * from t_user WHERE authority = '1' and score = '11';

这里可以看到，索引全部都失效了。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

这里将authority字段类型改成int类型做测试
 

EXPLAIN SELECT * from t_user WHERE nick = 'PIS' and authority > 3 and score = '11';

 在业务允许的范围下，我们要进行范围查询最好带上等于号，比如>= 或者 <= 

EXPLAIN SELECT * from t_user WHERE nick = 'PIS' and authority >= 3 and score = '11';

 可以看到这样索引未失效。 

索引列运算

不要在索引列上进行运算操作,索引将失效。

查看索引情况

我们用user_account这个字段的唯一索引展示
正常未失效的情况如下：

explain SELECT * from t_user WHERE user_account = '17630150099';

 

在索引列上进行运算（包括函数运算）导致索引失效的情况如下：

explain SELECT * from t_user WHERE SUBSTRING(user_account,10,2) = '99';

 观察可知，索引发生了失效。

字符串不加引号

字符串类型字段使用时，不加引号,索引将失效。

explain SELECT * from t_user WHERE user_account = 17630150099;

可以发现，在唯一索引user_account中查询的时候未加引号，可以导致索引失效。

模糊查询

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

索引失效例子：

explain SELECT * from t_user WHERE nick LIKE '%I';

索引未失效例子：

explain SELECT * from t_user WHERE nick LIKE 'I%';

 

 OR连接的条件

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

 数据分布影响

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

 SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

user index: 告知数据库要用哪个索引。

SELECT * from t_user USE INDEX(user_index) WHERE user_account = '17630151111';

ignore index: 告知数据库忽略哪个索引。

SELECT * from t_user IGNORE INDEX(user_index) WHERE user_account = '17630151111';

force index:  强制数据库必须用某个索引。

SELECT * from t_user FORCE INDEX(user_index) WHERE user_account = '17630151111';

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中色经全部能够找到)，减少select *。

因为如果在查询的字段中出现了非索引的字段，那么将会发生回表查询，即查询了两次，性能相对于查询一次的较差。

在explain执行计划中有一列是Extra

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

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

 前缀索引

当字段类型为字符串(varchar , text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀抽取出来，建立索引，这样可以大大节约索引空间，从而提高索引效率。

创建前缀索引的语法

如下：

create index id_xxx on table_name(column(n))

column(n) 代表我要截取某个字段的钱n个字符来创建索引。

前缀索引的长度 

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

计算公式 ：

select count (distinct substring(column,x,y))/count(*) from table;

 算出来的值越趋近于一说明性能也是越好的，但是当y远大于x时，索引的存储空间也是一个需要考虑的问题。因此要根据实际的业务来选择。

单列索引&联合索引

单列索引:即一个索引只包含单个列。

联合索引:即一个索引包含了多个列。

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

多条件联合查询时，MySOL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

适当的覆盖索引使用可以避免回表查询，从而提升查询性能。

索引设计的原则

1.针对于数据量较大（超过一百多万），且查询比较频繁的表建立索引。

2.针对于常作为查询条件（where)、排序（order by)、分组(group by）操作的字段建立索引。

3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4．如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5．尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6．要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7． 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。