mysql关于索引的两种结构、常见索引、各种索引的区别和选择索引的数据类型知识讲解

索引是在存储引擎由于快速查找记录的一种数据结构。

索引有很多种类型，可以为不同的场景提供更好的性能。在mysql中，索引是在存储引擎层而不是在服务器层实现的。所以没有统一的索引标准：不同存储引擎的索引的工作方式并不是一样的，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎都支持同一种类型索引，底层实现也不一定是相同的。

mysql索引主要有两种结构：b+tree索引和hash索引.

1.hash索引

mysql中，只有memory（memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持hash索引，是memory表的默认索引类型，尽管memory表也可以使用b+tree索引。hsah索引把数据的索引以hash形式组织起来，因此当查找某一条记录的时候,速度非常快。当时因为是hash结构，每个键只对应一个值，而且是散列的方式分布。所以他并不支持范围查找和排序等功能。

2.b+树索引

b+tree是mysql使用最频繁的一个索引数据结构，是inodb和myisam存储引擎模式的索引类型。相对hash索引，b+树在查找单条记录的速度比不上hash索引，但是因为更适合排序等操作，所以他更受用户的欢迎。毕竟不可能只对进行单条记录的操作。

带顺序访问指针的b+tree

b+tree所有索引数据都在叶子结点上，并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。

这样做是为了提高区间查询效率，例如查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

大大减少磁盘i/o读取

数据库的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次i/o就可以完全载入。

为了达到这个目的，在实际实现b- tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次i/o。

b-tree中一次检索最多需要h-1次i/o（根节点常驻内存），渐进复杂度为o(h)=o(logdn)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的i/o渐进复杂度也为o(h)，效率明显比b-tree差很多。

3.选择索引的数据类型

mysql支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在mysql中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储ip地址。

(3)尽量避免null：应该指定列为not null，除非你想存储null。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4.选择主键类型

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑mysql是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

(1) 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为auto_increment。

(2) 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

5.mysql常见索引

primary key（主键索引） alter table `table_name` add primary key ( `col` )

unique(唯一索引) alter table `table_name` add unique (`col`)

index(普通索引) alter table `table_name` add index index_name (`col`)

fulltext(全文索引) alter table `table_name` add fulltext ( `col` )

组合索引 alter table `table_name` add index index_name (`col1`, `col2`, `col3` )

6.mysql各种索引区别

普通索引：最基本的索引，没有任何限制

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它 是一种特殊的唯一索引，不允许有空值。

全文索引：仅可用于 myisam 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。

组合索引最左字段用in是可以用到索引的，最好explain一下select。