MySQL索引类型及索引方法

MySQL索引类型

主键索引

primary：主键索引。索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定）；

普通索引

normal：普通索引。索引列没有任何限制；

唯一索引

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

全文索引

fulltext：全文索引。目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题；

组合索引

组合索引是为了进一步榨取MySQL的效率，而建立的索引。就是将 name, city, age建到一个索引里SQL语句为：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 

建表时，name长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在name，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

---

MySQL索引方法

HASH

HASH是一种（key=>value）形式的键值对，如数学中的函数映射，允许多个key对应相同的value，但不允许一个key对应多个value。正是由于这个特性，hash很适合做索引。

特点如下：

1 .仅支持"=",“IN"和”<=>"精确查询，不能使用范围查询；
2.不支持排序： 由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
3.在任何时候都不能避免表扫描： 由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果；
4.检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于BTree索引；
5.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持BTree索引；

BTREE

BTREE是Mysql默认的索引方法，所有值都是经过二叉树算法排过序的，每个叶节点到跟节点距离相等。所以BTREE通常用在像 "=，>，>=，<，<=、BETWEEN、Like"等操作符查询，而且可以直接支持数据排序（ORDER BY）；

BTREE在MyISAM里的形式和Innodb稍有不同

在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.

而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.MySQL索引

通过比较发现，我们常用的是BTREE索引方式，当然Mysql默认就是BTREE方法。