Mysql 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

分类

从索引实现上的分类

1. 聚集索引 - 表记录的排列顺序与索引的排列顺序一致
2. 非聚集索引 - 指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致

一般分类

1. 普通索引
2. 唯一索引
3. 主键索引
4. 组合索引
5. 全文索引

InnoDB 聚簇索引和普通索引

在 InnoDB 中，又有聚簇索引和普通索引之分，聚簇索引根据主键来构建，叶子节点存放的是该主键对应的这一行记录，根据主键查询可以直接利用聚簇索引定位到所在记录。而普通索引根据申明这个索引时候的列来构建，叶子节点存放的是这一行记录对应的主键的值，根据普通索引查询需要先在普通索引上找到对应的主键的值，然后根据主键值去聚簇索引上查找记录，俗称回表。如果我们查询一整行记录的话，一定要去聚簇索引上查找，而如果我们只需要根据普通索引查询主键的值，由于这些值在普通索引上已经存在，所以并不需要回表，这个称为索引覆盖，在一定程度上可以提高查询效率。

• 聚簇索引
  1. 节点页只包含了索引列，叶子页包含了行的全部数据。聚簇索引“就是表”，因此可以不需要独立的行存储。
  2. InnoDB 对主键建立聚簇索引。 如果你不指定主键，InnoDB 会用一个具有唯一且非空值的索引来代替。 如果不存在这样的索引，InnoDB 会定义一个隐藏的主键，然后对其建立聚簇索引。 InnoDB 默认使用聚簇索引来组织数据，如果你用 InnoDB，而且不需要特殊的聚簇索引，一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据。最简单的做法就是使用一个 AUTO_INCREMENT 的列，这会保证记录按照顺序插入，而且能提高使用 primary key 进行连接的查询的性能。应该尽量避免随机的聚簇主键，例如字符串主键就是一个不好的选择，它使得插入操作变得随机。
• 二级索引
  1. 叶子节点保存的不是指行的物理位置的指针，而是行的主键值。
  2. 这意味着通过二级索引查找行，存储引擎需要两次 B-Tree 查找而不是一次：
     1. 找到二级索引的叶子节点获取对应的主键值，
     2. 根据这个主键值去聚簇索引中查找到对应的行。

索引的优缺点

优点

1. 索引大大减小了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机 IO 变成顺序 IO
4. 索引对于 InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在 MySQL5.1 和更新的版本中，InnoDB 可以在服务器端过滤掉行后就释放锁，但在早期的 MySQL 版本中，InnoDB 直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB 仅对需要访问的元组加锁，而索引能够减少 InnoDB 访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许 InnoDB 那样做（即索引达不到过滤的目的），MySQL 服务器只能对 InnoDB 返回的数据进行 WHERE 操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL 会进行全表扫描，并锁住每一个元组，不管是否真正需要。
5. 关于 InnoDB、索引和锁：InnoDB 在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存索引文件。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4. 对于非常小的表，大部分情况下简单的全表扫描更高效；
5. 索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。因此应该只为最经常查询和最经常排序的数据列建立索引。
6. MySQL 里同一个数据表里的索引总数限制为 16 个。