索引是在存储引擎由于快速查找记录的一种数据结构。
索引有很多种类型,可以为不同的场景提供更好的性能。在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。
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!