Mysql优化-索引类型介绍

索引有很多种类型,可以为不同的场景提供更好的性能.
在Mysql中,索引时在存储引擎层而不是服务器层实现的.因此,并没有统一的索引标准.不同的存储引擎的索引工作方式并不一样.即使多个存储引擎支持相同类型的索引,其底层的实现也很可能不同.
下面我们先来看看Mysql支持的索引类型.以及它们的优点和缺点.

1.B-Tree 索引

Btree是一种是为磁盘等外存储设备设计的一种平衡查找树.在create table时,如果没有指定索引类型,会默认使用该类型.大多数mysql引擎都支持这种索引.
不同的存储引擎会以不同的方式使用b-tree索引.性能也各有不同,各有优劣.例如 MyISAM使用前缀压缩技术使得索引更小.而InnoDB则按照源数据格式进行存储.再如MyISAM通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行.
B-tree意味着所有值都是按顺序存储的.并且每一个叶子页到根的距离相同.B-tree索引能够加快数据的访问速度,因为存储引擎不再需要扫描全表,取而代之的是通过索引的根节点进行搜索.

B-tree索引支持的查询类型 :

• 全列匹配.也就是和索引中的所有列进行匹配.
• 最左前缀匹配.也就是可以使用索引最左开始的n个列进行查询.
• 匹配范围值.

B-tree索引的限制 :

• 最左前缀原则.也就是如果查询条件不是按照索引的最左列开始查找,那么无法使用索引.
• 不能跳过索引中的列.假设有联合索引A,B,C,D,如果查询条件不指定C,则Mysql只能使用索引的A,B列.

因为这些限制,在优化性能时,可能需要创建相同的列但顺序不同的索引来满足不同类型的查询需求.

2.Hash(哈希)索引

哈希索引基于哈希表实现.只有精确匹配索引的所有列的查询才有效.对于每一行数据,哈希索引都会根据所有的索引列计算一个hash code.
哈希索引会将所有的hash code存储在索引中,同时在哈希表中保存指向每个数据行的指针.

因为索引本身只存储hash code,所以索引的结构十分紧凑,这也让哈希表的速度非常快.然而.哈希表也有它的限制:

• 不支持排序.
• 不支持通过部分索引列进行查找.
• 不支持范围查询,只支持等值比较查询.
• 哈希碰撞可能导致的性能下降问题.
• 存储的是指向数据行的指针，所以不支持覆盖索引.

因为这些限制,哈希表只适用于某些特定的场合.而一旦适用哈希索引,它带来的性能提升会非常显著.比如,在关联多个表查询时的中间表,哈希索引就非常适合它的需求.

3.R-tree(空间数据索引)

MyISAM支持空间数据索引,可以用作地理数据存储.目前来说,Mysql对GIS的支持并不完善.所以大部分人不会使用这个特性…

4.全文索引

全文索引是一种特殊类型的索引,它比较的是文本中的关键词,而不是直接比较索引中的值.它更类似搜索引擎,而不是简单的where查询.