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方法。