MySQL索引介绍

索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。不过索引却经常被忽略，有时候甚至被误解，所以在实际案例中经常会遇到由糟糕索引导致的问题。索引优化应该是对查询性能优化最有效的手段。

B-Tree索引

当人们谈论索引的时候如果没有特别的指明类型，一般说的是B-Tree索引。它使用B-Tree数据结构来存储数据。B-Tree通常意味着所有的键都是按顺序存储的并且每一个页到根的距离相同。由于是按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。

建立原则：

1.最左前缀匹配原则：非常重要的原则，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立key(a,b,c,d)顺序的索引，d是用不到索引的，如果建立key(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.索引列不能参与计算：如a + 5 = ${num}、from_unixtime(create_time) = ${date}，就不能使用到索引。因为B-Tree中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成a = ${num} - 5。

3.尽量的扩展索引：比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

有效查询：

1.全值匹配：指的是和索引中的所有列进行匹配，如key(a,b)：a = 1 and b = 2；

2.匹配最左前缀：就是按照索引的列从左向右开始查找，不能跳过索引中的列，如key(a,b,c,d)：a =1 and b=2为最左前缀，a =1 and c=3则不是；

3.匹配列前缀：可以匹配某一列的值的开头部分，如key(a)：a like‘${name}%；

4.匹配范围值：B-Tree对索引列是顺序存储的，所以很适合查找范围数据。除了按值查找之外，索引还可以用于ORDER BY操作；

5.精确匹配某一列并范围匹配另一列：参照最左匹配原则；

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MySQL中只有Memory引擎显示支持哈希索引。InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上在创建一个哈希索引，这样让B-Tree索引也具有哈希索引的一些有点。

因为索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这也让哈希索引查找速度非常快。

特性：

1.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

2.哈希索引数据并不是按照索引值顺序存储，所以无法用于排序。

3.哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值。

4.哈希索引只支持等值比较，包括=、IN()、<=>（注意<>和<=>是不同的操作），不支持范围查询。

5.访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中的所有的行指针，逐行进行比较，直到找到所有符合条件的行。如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

创建自定义哈希索引：

如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引。例如只需要很小的索引就可以为超长的键创建索引。

创建方法：在B-Tree的基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree索引进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

例如：需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大。若删除原来的URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

SELECT …… WHERE url=”http://www.mysql.com” AND url_crc = CRC32(“http://www.mysql.com”);

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的索引来完成查找。即使有多个记录有重复的索引值，查找仍然很快。

聚簇索引

聚簇索引也叫簇类索引，聚簇索引并不是一种单独的索引类型，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

具体的细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。

特点：

1.可以把相关数据保存在一起。这样只需要从磁盘读取少数的数据页就能获取全部需要的数据。

2.数据访问更快。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

4.聚簇索引最大限度地提高率I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了。

5.插入速度严重依赖于插入顺序。按照主键顺序插入是速度最快的方式。

6.更新聚簇索引的代价很高，会将每个被更新的行移动到新的位置。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为“覆盖索引”。使用索引来直接获取列的数据，这样就不在需要读取数据行。覆盖索引是非常有用的工具，能够极大的提高性能。MySQL只能使用B-Tree索引做覆盖索引。

1.索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大的减少数据访问量。

2.因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。

3.由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。

空间数据索引（R-Tree）

空间索引可以用作地理数据存储。空间索引会从所有纬度来索引数据。查询时可以有效地使用任意维度来组合查询。MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。

全文索引

全文索引是一种特殊类型的索引，他查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。在相同的列同时创建全文索引和B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作。

索引策略

独立的列：

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

例如：SELECT……  WHERE id + 1 = 5；

SELECT …… WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;

都是错误的写法。

 

前缀索引和索引选择性：

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，通常可以索引开始的部分字符串。但这样也会降低索引的选择性。一般情况下某列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。

多列索引：

很多人对多列索引的理解不够，一个常见的错误就是为每个列创建独立的索引或者按照错误的顺序创建多列索引。在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

使用索引扫描来做排序：

MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两种任务。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，既需要满足最左前缀要求，并且所有列的排序方向（顺序或者倒序）都一样时，MySQL才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。

多余的索引：

MySQL允许在相同的列上创建多个索引，MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。应该避免这样创建重复的索引，发现以后也应该立即移除。

可能还会有一些服务器永远不用的索引，这样的索引也应该考虑删除。