数据库索引整理

整理一下近段时间学习的数据库知识点~

说到索引,基本都是在设计数据库的时候被提及,或者是sql慢的令人崩溃的时候。的确索引能提升sql的查找速度,但是建立索引也会造成插入,删除数据消耗更多的时间。很多时候我们都浅尝辄止,了解到这就好了,但这是远远不够的。很多时候我们需要去探索发现,才能发现世界的瑰丽。

存储引擎

磨刀不误砍柴工,在了解索引前,先整理一下常用的几种存储引擎,这些知识点往往容易被忽略。

InnoDB

InnoDB是我们最常用的存储引擎,他的优点提供了提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全,以及支持外键和自增列。显而易见,保证了以上的功能的同时,它的缺点就是读写效率较差,占用的数据空间相对较大。后续讲的索引,都是基于InnoDB引擎的。

Memory

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表,而且数据全部放在内存中。有点类似Mc,之所以说像Mc而不是redis呢。因为它不能做到持久化,重启或关闭服务器的时候,会失去数据。Memory默认使用hash索引,后面会整理hash索引和btree索引的区别。

MyISAM

MyISAM是MySQL是MySQL比较早版本的默认存储引擎。MyISAM是MySQL中常见的存储引擎,曾经是MySQL的默认存储引擎。
需要注意的一点是MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。

聚集索引

注意:下面讨论的都为innodb引擎。

比较规范的数据库表设计都会有一条不成文的规定,那就是给每张表一个自增主键。我们就算不按着规范,也会习惯性的加上主键。这是为什么呢?

这里我们需要知道一个概念,那就是聚集索引。聚集索引并不是一种单独的索引类型,而是一种数据存储方式(不是数据结构,而是存储结构)。其实当没有聚集索引的时候,表的数据很像我们所认知的表,一行一行的排列的很整齐。但是,当表有了聚集索引,表并不能被称之为”表”了。那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是btree结构,换句话说,就是整个表就变成了一个索引。

数据库表的存储结构

这也是为什么我们会需要给每张表设计一个主键。这里很容易会造成一个错误的观点,那就是主键=聚集索引。这是错误的!
对于Innodb,主键毫无疑问是一个聚集索引。但是当一个表没有主键,或者没有一个索引的时候呢?

  • 1 如果一个主键被定义了,那么这个主键就是作为聚集索引
  • 2 如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引
  • 3 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。

另外我们会将主键设为自增,考究起来也是很有学问的。Innodb中的每张表都会有一个聚集索引,而聚集索引又是以物理磁盘顺序来存储的,自增主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。因为聚集索引的排序,必然会带来大范围的数据的物理移动,这里面带来的磁盘IO性能损耗是非常大的。

所以有时候我们采用UUID作为聚集索引是很不可取的。因为它使得聚集索引的插入变得完全随机,使得数据没有任何聚集特性。当UUID作为主键插入行不仅花费的时间更长,而且索引也更大,这一方面是因为主键字段变长了,另外一方面是由于页分裂导致时间变长和碎片导致的索引变大。因为主键的值是顺序的,所以Innodb把每一条记录都存储在上一条记录的后面,当达到页的最大填充因子时(innodb默认的最大填充因子是页大小的十六分之十五,留出部分空间用于以后修改),下一条记录就会写入新的页中,一旦数据按照这种顺序的方式加载,主键页就会近似被顺序的记录填满,这也正是所期望的结果。

上面提到的碎片,做一下解释。每当MySQL从列表中删除了一行内容,该段空间就会被留空,就会行成碎片。而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作,MySQL将尝试利用这些留空的区域,但仍然无法将其彻底占用,所以我们有时候需要对表进行”瘦身”。具体操作如下:

1.先查看某个表所占空间,以及碎片大小。
SELECT table_name,ENGINE,table_rows,data_length + index_length length,DATA_FREE
FROM information_schema. TABLES WHERE TABLE_SCHEMA = ‘db_name’;
2.如果有大量碎片,可以进行清理,清理的频率一般每周或者每月整理一次即可。
OPTIMIZE table table_name;
ALTER TABLE table_name ENGINE=’InnoDB’; //亲测两种方式都是可以的,版本试过5.6+的和5.7+的
3.这里有个注意的点,我自己测试的时候,发现在InnoDB下碎片清理不干净。表的体积比较大的时候,DATA_FREE的值会在2M,4M,5M左右。我猜测可能是mysql为表的预留空间,或者是InnoDB下,设计表字段类型大小或者索引影响的。不过对于一些碎片体积大的,清理一下还是有很显著的效果。

还是看下最权威的靠谱

以上讨论的都是基于单机的情况下,那如果在高并发工作负载下,顺序的主键插入就会造成很大的应能问题。主键的上界被称为热点,因为所有的插入都发生在这里,所以并发插入可能导致间隙锁争用,另一个热点可能是auto_increment锁机制。总而言之,就是在保持顺序的主键,会造成阻塞影响性能。

Mysql为提供了innodb_autoinc_lock_mode配置。会有三个参数可以选择:

  • 1 tradition(innodb_autoinc_lock_mode=0) 模式:在这一模式下,所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入。

  • 2 consecutive(innodb_autoinc_lock_mode=1) 模式:这一模式下去simple insert 做了优化,由于simple insert一次性插入值的个数可以立马得到确定,所以mysql可以一次生成几个连续的值,用于这个insert语句;总的来说这个对复制也是安全的这一模式也是mysql的默认模式,这个模式的好处是auto_inc锁不要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁

  • 3 interleaved(innodb_autoinc_lock_mode=2) 模式:取消了auto_inc锁,所以这个模式下的性能是最好的;但是它也有一个问题,就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

非聚集索引

拉回正题,讲完聚集索引,接下来就是非聚集索引,也就是我们平时使用的常规索引。

非聚集索引和聚集索引一样,同样是采用btree作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段,假如给user表的name字段加上索引,那么索引就是由name字段中的值构成,如果给表中多个字段加上索引,那么就会出现多个独立的索引结构,每个索引互相之间不存在关联。 如下图:

非聚集索引

这里先给大家安利一波chrome的插件Gliffy Diagrams,绘图相当好用,主要格调不错。^.^

看图,可以发现每次给字段建一个新索引,字段中的数据就会被复制一份出来,用于生成索引。因此,给表添加索引的同时会增加表的体积,占用磁盘存储空间。非聚集索引和聚集索引的区别在于,通过聚集索引可以查到需要查找的数据,而通过非聚集索引可以查到记录对应的主键值,再使用主键的值通过聚集索引查找到需要的数据。所以当我们能利用聚焦索引的时候,就尽量使用聚焦索引。因为查询表的操作最终都会利用主键通过聚集索引来定位到数据,聚集索引中的数据指针是通往数据的唯一路径。

一般用了唯一的时候一定要注意,因为一不小心就会啪啪啪的打脸。虽然正常的逻辑下,我们的查询拿到数据最终是要通过聚焦索引。但是也是有特例的,被称为覆盖索引,也就是平时所说的联合索引或复合索引。当我们为字段建立索引以后,字段中的内容会被同步到索引之中,那我们指定两个字段,这个两个字段的内容都会被同步到索引。

百言不如一例:

create index index_score_and_name on user_info(score, name);//建立索引 姓名,分数
select name from student where score = 100;//查询分数为100分的学生名字
上面通过非聚集索引index_score_and_name查找分数为100的叶节点的内容,因为叶节点中保存了聚集索引的key值还有name的内容,所以不需要省去通过聚集索引去查找数据行的步骤,直接返回name,大大的提高了查询性能。

btree

大家在建普通索引(非聚焦索引)的时候,在Innodb下往往会有两种索引方法,一种是btree,一种是hash。这里的btree是指b+tree。这里就会有个容易混淆的东西,那就是b+tree和b-tree。

b+tree 如图:
b+tree

b-tree 如图:
b-tree

观察两种结构,我们可以发现b-tree和b+tree最重要的一个区别就是B+tree只有叶子节点存放指向记录的指针,而B-tree是每个节点都会有指向记录的指针。另外b+tree中所有叶子节点都是通过指针连接在一起,而b-tree不会。

所以相对b-tree,b+tree的层数会更少,因为它的叶子节点存放数据,所以它层数很少,可以达到减少磁盘IO次数的效果,当查询数据的时候,最好的情况就是很快找到目标索引,然后读取数据。而B-tree的每个结点都有data域,这无疑增大了节点大小,说白了增加了磁盘IO次数(磁盘IO一次读出的数据量大小是固定的,单个数据变大,每次读出的就少,IO次数增多,每次IO都是相当耗时的),而B+树除了叶子节点其它节点并不存储数据,节点小,磁盘IO次数就少。这是优点之一。另外就是b+tree的叶子节点之间通过指针来连接,范围扫描将十分简单,而对于b-tree来说,则需要在叶子节点和内部节点不停的往返移动

Mysql采用的是b+tree,而不是b-tree,不是说b-tree不好,而是b+tree更加适合mysql,更加适合关系型数据库。为什么这么说呢,因为我们大家熟知的Mongodb采用的就是b-tree。Mongodb是文档型的nosql,它是以Bson(Binary JSON)格式作为存储
目的就是高性能,高可用,易扩展。首先它摆脱了关系模型,MongoDB使用B-树,对于在内部节点的数据,可直接得到,不必根据叶子节点来定位,只要找到指定索引就可以进行访问,无疑提升单次查询的效率。

本人才疏学浅,不知道有没有理解的错误,大家可以看下大神的回答Differences between B trees and B+ trees

hash

讲完了btree,那剩余的就是hash了。hash大家都不陌生,其原理是首先根据key值和哈希函数创建一个哈希表(散列表),然后根据键值,通过散列函数,定位数据元素位置。所以hash的效率非常的高,是大于btree的,但是它有它的一些弊端,查阅了一些资料,搬运过来如下:

  • 1 由于存放的是hash值,仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询。
  • 2 hash索引无法通过操作索引来排序,这是因为存放的时候经过hash计算,但是计算的hash值和存放的不一定相等,所以无法排序.
  • 3 Hash 索引不能利用部分索引键查询。对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
  • 4 Hash 索引在任何时候都不能避免表扫描。因为不同索引键存在相同Hash值,所以即使取满足某个Hash 键值的数据的记录条数,也无法从 Hash索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
  • 5 当存在大量相同hash值得时候,hash索引的效率会变低.

以上就是我的一点点学习的总结和见解,欢迎大家交流拍砖,共同进步~
完。^.^

更多精彩 戳我