【原创】MySQL中的索引类型和索引方法归总

一、索引类型：MySQL的索引类型大体有两种划分方法

1、按照列划分：可分为单列索引和组合索引，组合索引则是由多列组合到一块形成，又称之为联合索引：

（1）单列索引应用总结：只要条件列中出现索引列，无论在什么位置，都能利用索引查询；

（2）联合索引应用总结：

• 查询条件中出现联合索引第一列，或者全部，则能利用联合索引；
• 条件列中只要条件相连在一起，例如last_name=’1′ and first_name=’1′与first_name=’1′ and last_name=’1′，无论前后,都会利用上联合索引；
• 查询条件中没有出现联合索引的第一列，而出现联合索引的第二列，或者第三列，都不会利用联合索引查询。

有一点大家可能都会比较迷惑，就是联合索引中，如果查询条件中，字段的排序与联合索引的排序不一致，还能命中吗？也就是联合索引总结中的第1、2条，下面通过测试结果来告诉大家真正的答案：

2、按照属性划分：又可分为主键索引（Primary）、普通索引（Normal）、唯一索引（Unique）、全文索引（Full Text）共计四种。

（1）主键索引：它是一种特殊的唯一索引，不允许有空值，一般是在建表的时候同时创建主键索引；

（2）普通索引：这是最基本的索引，它没有任何限制，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length；

（3）唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

（4）全文索引：全文索引的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。

二、索引方法：常用的方法有B-Tree、Hash

1、B-Tree索引：如果没有特别指明类型，多半说的就是btree索引，它使用btree数据结构来存储数据，大多数mysql引擎都支持这种索引，archive引擎是一个例外，5.1之前这个引擎不支持任何索引，5.1开始才支持单列自增的索引。innodb使用b+tree=btree（btree已经不使用了）

2、Hash索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列的值计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码不一样，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在mysql中，只有memory引擎显式支持哈希索引，这也是memory引擎表的默认索引类型，memory也支持btree，值得一提的是，memory引擎是支持非唯一哈希索引的。

3、B-Tree和Hash的对比：

MySQL中Hash索引结构的特殊性，使其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

 

MySQL练习手册：http://www.runoob.com/mysql/mysql-index.html

资源参照：

1）http://www.cnblogs.com/xiaoboluo768/p/5164342.html

2）http://www.cnblogs.com/vicenteforever/articles/1789613.html