MySQL索引详细解析-木庄网络博客

本文整理自网络，侵删。

1. MySQL 索引的最左前缀原则
2. 前缀索引
3. 索引下推（ICP——Index Condition Pushdown）
4. 查看 MySQL 语句是否用到索引
5. 为什么官方建议用自增长主键作为索引
6. 如何创建索引
7. 创建索引注意事项
8. 使用索引一定可以提高查询性能嘛
9. 索引失效

1. MySQL 索引的最左前缀原则

左前缀原则是联合索引在使用时要遵循的原则，查询索引可以使用联合索引的一部分，但是必须从最左侧开始。在创建联合索引时，要根据业务需求，where子句中将使用最频繁的一列放在最左边，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)时停止匹配。即范围列可以用到索引，范围列后面的列无法用到索引。

比如查询 a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a，b，d，c) 顺序的索引，d是用不到索引的，如果建立 (a，b，d，c) 的索引则都可以用到，a，b，d的顺序可以任意调整。可以调整 a，b，c 顺序的原因是 MySQL 具有查询优化器

MySQL 查询优化器

当按照索引中所有列进行精确匹配（“=” 或 “IN”）时，索引可以被用到，并且 type 为 const。理论上索引对顺序是敏感的，但是 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以 MySQL 在进行精确匹配时不存在因 where 子句的顺序问题而造成索引失效。

2. 前缀索引

定义：对于BLOB、TEXT，或者很长的VARCHAR类型的列，为它们的前几个字符(具体几个字符是在建立索引时指定的)建立索引，这样的索引就叫前缀索引。

优点：这样建立起来的索引更小，所以查询更快。

缺点：不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把前缀索引用作覆盖索引。

创建方法：

alter table table_name add key( column_name( prefix_length));

注：这里最关键的参数就是 prefix_length，这个值需要根据实际表的内容，来得到合适的索引选择性。

prefix_length 计算方法：

先计算完整列的选择性 :

select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 :

select count(distinct left(col_1,4))/count(1) from table_1

到最优长度之后，创建前缀索引 :

create index idx_front on table_1 (col_1(4))

3. 索引下推（ICP——Index Condition Pushdown）

定义：索引下推 Index Condition Pushdown(ICP) 是MySQL使用索引从表中检索行数据的一种优化方式，从 MySQL5.6 开始支持。5.6 之前，存储引擎会通过遍历索引定位基表中的行，然后返回给 Server层,再去为这些数据行进行 WHERE 后的条件的过滤。MySQL5.6之后支持 ICP，如果WHERE条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。ICP 能减少引擎层访问基表的次数（回表次数）和 Server层访问存储引擎的次数。

MySQL通过 optimizer_switch 参数中的 index_condition_pushdown 选项来控制,默认是开启的。

操作：

查看是否开启

show variables like'% optimizer_switch%';

设置 ICP

SET optimizer_switch = ‘index_condition_pushdown=off';

例子：

在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是

SELECT * FROM people WHERE zipcode='95054′ AND lastname LIKE ‘%etrunia%' AND address LIKE ‘%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。