MySQL索引

索引主要是用来提高查询的性能，总的来说有三个优点：

• 索引大大减少服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机IO变为顺序IO。

而当然使用索引的代价就是由于需要维护索引，修改、插入和删除的性能会受影响，另外还有需要一定的空间。

索引的类型

主要有B-Tree索引，Hash索引，全文索引和空间数据索引等。

1.B-Tree索引

B+Tree索引结构.png

结构如上图所示，B-Tree索引（技术上是B+Tree）是一种适合范围查询的结构，因为叶子页存储的数据是按一定顺序排的。父页的值存储着指向子页的指针。

最左匹配：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边的列都无法使用索引优化查找。

2.哈希索引

哈希索引基于哈希表实现，需要所有索引列匹配才能有效。优点在于速度快。

缺点：

• 只包含哈希值和行指针，所以不能避免读取行，但是这点对性能影响不大。
• 不能用于排序
• 不支持部分索引匹配查找
• 只支持等值匹配查找
• 在哈希冲突较高的情况下，对性能会有很大影响，而且维护代价也很高。

我们也可以创建自定义哈希索引：比如url的查询，url一般比较长，如果直接创建索引会占用很大的空间，我们可以对url进行一次hash，再将hash后的值作为一个索引列url_hash来维护。查询的时候先匹配这一列，再匹配url列，这样可以提高性能。

3.全文索引

全文索引是一种特殊的索引，用于查找文本中的关键词。

4.空间数据索引（R-Tree）

MyISAM支持空间索引，作为地理数据存储，用MySQL的GIS相关函数来维护数据。但MySQL这方面不如PostgreSQL成熟。

索引的使用策略

1. 避免不走索引的情况

• 遵守最左匹配原则
• where后面的的查询列不要使用表达式，像：

select id form actor where actor_id + 1 = 5

• 尽量避免前置模糊查询 like '%哈哈%'
• 尽量避免in和not in, 可以用between和exists代替
• 尽量避免or，可以用union代替
• 尽量避免 !=或<>

2. 前缀索引

有时候字段过大的时候也可以采用前缀索引的方式，它类似手动创建哈希索引，都是创建一个相对短的字段作为索引列。

• 索引的选择性
  不重复的索引值和数据表的记录总数的比值。范围是0~1，索引选择性越大，性能越好。如果索引的时候选择性很低，会很影响性能，例如在性别列建索引，可能比不建索引查询还慢。但是选择性高了，占用的空间也越大，所以建立前缀索引的话我们要选择一个合适的前缀长度。

创建前缀索引：

alter table city add key (city(7));

3. 聚簇索引

聚簇索引的数据放在索引的叶子页，所以一个表只能有一个。如果没有定义主键，InnoDB会选一个唯一非空索引代替。如果没有这样的索引，InnoDB会隐式创建一个主键作为聚簇索引。

优点：

• 可以把数据放到一起，减少磁盘IO。
• 比一般索引访问更快，不需要查2次。
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

缺点：

• 插入速度依赖于插入顺序。如果不是按照主键的顺序加入，那么最好用OPTIMIZE TABLE命令组织一下表。
• 更新聚簇索引列的代价很高。
• 插入新行或者更新时，如果插入的页满了，会导致页分裂，同时造成空间碎片。
• 二级索引会比较大，因为叶子节点存储了主键值；而且要查两次，会慢一点。

MyISAM和InnoDB数据分布的区别：

• MyISAM的主键和其他索引在结构上没什么区别，数据插入时也不需要调整数据的存储顺序，叶子页直接存了数据的行指针。他的主键索引就是一个名为primary的唯一非空索引。可以认为MyISAM没有聚簇索引吧。
• InnoDB的聚簇索引里数据的存储顺序就是索引列的顺序，所以插入时需要调整顺序。二级索引的叶子页存的是主键值，所以通过二级索引要查2次才能访问到数据。

聚簇索引的插入尽量按照索引的顺序（如自增id），如果使用UUID大量插入的话会很影响性能，原因有：1、得先从磁盘中读取写入的目标页。2、页分裂会导致大量数据移动，一次页分裂至少修改3个页。3、页分裂还会产生数据碎片。

4、使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列值为“index”，则说明MySQL使用了索引扫描来做排序。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且索引列的顺序方向（正序或倒序）都一致时，MySQL才能够使用索引来对结果排序。如果查询需要关联多张表，则只有当ORDER BY字句引用的字段全部为第一个表时，才能够使用索引做排序，ORDER BY子句和查找类型查询的限制是一样的：需要满足索引的最左前缀的要求；否则无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

5、其他

• 覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。它可以避免回表，提高性能。
• 索引可以让查询锁定更少的行。

参考资料：《高性能MySQL》（第3版）