Mysql索引类型

 

--------------------------------------------------------------------------------

 

索引的优缺点

优点:有了索引.对于记录数量很多的表,可以提高查询速度。

缺点：索引是占用空间的。索引会影响update insert delete速度。

索引的四大类型

一，FULLTEXT：全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。【使用情况】：由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法。

 

二，HASH：hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能. 只有Memory支持hash索引【使用情况】：正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

 

三，RTREE：RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。【优势】：相对于BTREE，RTREE的优势在于范围查找。

 

四，BTREE：BTREE分为B-tree和B+tree【使用情况】：对于BTREE这种Mysql默认的索引类型，具有普遍的适用性。

 

（1），B-tree介绍: B-Tree是一种多路搜索树（并不是二叉的）：

a.定义任意非叶子结点最多只有M个儿子；且M>2；

b.根结点的儿子数为[2, M]；

c.除根结点以外的非叶子结点的儿子数为[M/2,M]；

d.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）

e.非叶子结点的关键字个数=指向儿子的指针个数-1；

f.非叶子结点的关键字：K[1], K[2],…, K[M-1]；且K[i] < K[i+1]；

g.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1],K[i])的子树；

h.所有叶子结点位于同一层。

 

搜索过程：B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点。

注：多路查询，并且小于17选择P1指针对应的磁盘块，或者就是大于17小于35的则选择P2指针对应的磁盘块，大于35的选择P3指针对应的磁盘块

 

B-树的特性：

      1.关键字集合分布在整颗树中；

      2.任何一个关键字出现且只出现在一个结点中；

      3.搜索有可能在非叶子结点结束；

      4.其搜索性能等价于在关键字全集内做一次二分查找；

      5.自动层次控制；

为什么选用B-Tree：

    如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。

 

（2），B+tree介绍: B+树是B-树的变体，也是一种多路搜索树：

1.其定义基本与B-树同，除了：

2.非叶子结点的子树指针与关键字个数相同；

3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i],K[i+1])的子树（B-树是开区间）；

5.为所有叶子结点增加一个链指针；

6.所有关键字都在叶子结点出现；

 

搜索过程：B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找。

注：按区间查找，5到28选择P1，28到65选择P2，小于5，前面还有一个搜索树，而大于65的也是一样的道理，搜索树对应的一段区间的值。

 

B+的特性：

   1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

   2.不可能在非叶子结点命中；

   3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

   4.更适合文件索引系统；

为什么选用B+Tree：

   B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。

 

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 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

 

MyisAM索引与InnoDB索引的比较

（1）MyisAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持。

 

（2）InnoDB支持事务，MyisAM不支持。

 

（3）MyisAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值；

 

（4）MyisAM键值分离，索引载入内存（key_buffer_size），数据缓存依赖操作系统；InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池；MyisAM主键（唯一）索引按升序来存储存储，InnoDB则不一定。

 

（5）MyisAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyisAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内。

 

（6）MyisAM处理字符串索引时用增量保存的方式，如第一个索引是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance’，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引。

 

 

常见的索引

主键索引

PRIMARY KEY（主键索引）  ALTER TABLE `table_name` ADD PRIMARY KEY (`column` );

【应用场景】大数据时，主键可用于where条件和order by条件。

 

唯一索引

UNIQUE(唯一索引)     ALTER TABLE `table_name` ADD UNIQUE(`column`);

【应用场景】创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

 

普通索引

INDEX(普通索引)     ALTER TABLE `table_name` ADD INDEXindex_name ( `column` );

【应用场景】加快数据的访问速度。

 

全文索引

FULLTEXT(全文索引)      ALTER TABLE `table_name` ADD FULLTEXT (`column` );

【应用场景】解决模糊查询效率低的问题。

 

组合索引

组合索引   ALTER TABLE `table_name`ADD INDEX index_name ( `column1`, `column2`, `column3` );

【应用场景】Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效