mysql索引总结

索引类型

1.B-Tree索引

B-Tree索引在InnoDB引擎中既存储数据行的地址又存储列数据本身

B-Tree索引的适用范围：

1）全值匹配。指的是与索引中的所有列全部匹配
2）匹配最左前缀。指的是对于多列索引，可以只是用第一列。
3）匹配列前缀。指的是只匹配某一列的开头部分字母，这里需要注意的是对于多列索引，如果后面的列要使用列前缀，则其前面列都必须匹配，不能跳过索引中的列。
4）匹配范围值。指的是搜索列中的某一范围而不是固定值是，索引也能匹配，对于多列索引只会用到第一列。
5）精确匹配某一列并范围匹配另外一列。
6）只访问索引的查询。即查询的列索引本身可以提供，不用再访问数据行。

B-Tree索引的限制

1）不是按照索引的最左列开始查找，则无法使用索引。即对于多列的索引，如果第一列未匹配，尽管匹配后续的列，然而索引不起作用。
2）不能跳过索引中的列。假设三个列组成的多列索引，查询条件只指定第一列和第三列，不指定第二列，则mysql只能使用索引的第一列。
3）如果查询中有某个列的范围查询，则其右边的所有列无法使用索引优化查找。假设三个列的多列索引，查询指定全部三个列，然而第二个列是范围查询，如使用like，则索引的第三列不发挥作用。

2.哈希索引

基于哈希表实现，只有精准匹配索引的所有列的查询才有效，哈希索引只存储对应的哈希值，索引结构十分紧凑，查找速度非常快。在mysql中，只有Memory引擎支持哈希索引。

InnDB引擎有个特殊功能叫“自适应哈希索引”，当某些索引被使用的很频繁，它会在内存之中基于B-Tree索引之上再建一个哈希索引，让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为。

其他类型的索引暂不总结。

三星系统（three-star system）

索引的三星系统：索引将相关的记录放到一起则获得一星；如果索引中的数据顺序和查找中的排序顺序一致则获得二星；如果索引中的列包含了查询中需要的全部列则获得三星。

索引策略

必须是独立的列

“独立的列”指的是索引列不能是表达式的一部分，也不能是函数的参数，如果查询中的列不是独立的，则mysql不会使用索引。

索引的选择性

选择性指的是，不重复的索引值（也称基数）和数据表记录总数的比值，比值越大则查询效率越高，因为高选择性的索引可以在查询时过滤掉更多的行，比如唯一索引的选择性是1，这是最好的。
对于前缀索引要选择足够长的前缀以保证较高的选择性，同时不能太长以节约空间。

选择合适的索引列顺序

对于多列的B-Tree索引，通常可以将选择性最高的列放在索引的最前列。

聚簇索引

1）聚簇索引不是一种单独的索引，而是一种数据存储方式，InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。
2）聚簇索引叶子页包含了行的全部数据，节点页只包含索引列。
3）InnoDB一般选择主键作为聚簇索引，如果没有主键，会选择一个唯一的非空索引，如果也没有这样的索引，就会隐式定义一个主键作为聚簇索引。
4）解释下InnoDB二级索引为什么比主键索引慢。二级索引的叶子节点保存的是引用行的主键列，意味着二级索引查找行时，存储引擎现在二级索引叶子节点获得对应的主键值，然后去聚簇索引中查找对应的行，相当于做了两次B-Tree查询。然而对于MyISAM索引，由于数据不是聚簇索引方式存储的，所以主键索引和其他索引没什么不同。
5）最好避免随机的聚簇索引，如以UUID作为聚簇索引，使得聚簇索引的插入变得完全随机，会造成页分裂和碎片，导致插入操作效率降低。应该尽可能按主键顺序插入，并尽可能使用单调增加的聚簇索引的值来插入。

覆盖索引

1. 一个索引如果包含需要查询的字段值，就是“覆盖索引”
2. 索引条目通常远小于数据行大小，如果只需要读取索引，mysql会极大减少数据访问。
3. 覆盖索引对于InnoDB特别有用，二级索引如果能覆盖查询，则可以避免对主键索引的二次查询。
4. 不是所有索引都可以作为覆盖索引，覆盖索引必须存储索引列的值，而哈希索引、空间索引、全文索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。
5. InnoDB的二级索引的叶子节点都包含了主键的值，意味着InnoDB的二级索引可以有效利用这些额外的主键列来覆盖查询。

使用索引扫描排序

1. MySQL生成有序结果的两种方式：排序操作和按索引顺序扫描。
2. 当索引的列顺序和ORDER BY子句顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引对结果做排序。若查询关联了多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
3. ORDER BY子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求。