索引
对于索引的使用有个误区,并不是越多越好,也不是在有性能问题的时候才想起来要dba加个索引,应该是在最初表设计的时候就考虑到该表的使用场景,设计好索引。
索引的存在就是为了减少磁盘io次数,磁盘io是数据库检索过程中最耗时的部分。
innodb中常见的有三种索引,B+树、倒排索引、哈希索引,其中哈希索引是自适应的,不可人为干预。
B+树
B+树是为磁盘或者其他存储辅助设备设计的一种多路平衡查找树,B是balance的意思,目的是为了减少IO。
B树
B树是一种平衡的多分树,通常我们说m阶的B树,它必须满足如下条件:
-
每个节点最多只有m个子节点
-
每个非叶子节点(除了根)具有至少⌈ m/2⌉子节点
-
如果根不是叶节点,则根至少有两个子节点
-
具有k个子节点的非叶节点包含k -1个键
-
所有叶子都出现在同一层
一个B树的结构如下:
B树高度
一棵含有n个总关键字数的m阶(B树中一个节点的子节点数目的最大值称为阶)的B树的最大高度是:
证明过程如下:
B+树
B+树由B树变形而来,是为了满足外存设备的需求。它必须满足以下条件:
-
有m个子树的中间节点包含有m个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引
-
所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)
-
所有的非叶子结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)
一个B+数的结构如下:
用B+树做索引的原因
-
B+树索引效率高:B+树非叶子节点不存储实际数据,所以每个节点能存储更多的关键key信息,一次性读入内存的关键key就越多,相当于减少了io次数
-
B+树查询效率稳定:所有数据存储在叶子节点,叶子节点的高度都一样,所以查询耗时均衡,不会像B-树一样因为数据所在节点的高度而影响耗时
-
B+树范围查询更方便:B+树叶子节点构成双向链表,所以仅需要进行链表遍历就可以进行范围查询,而B-树则需要从root节点重新查询
在真实的数据库中,数据量几十万和几千万的表,B+树索引层高也就3-4层,所以如果通过B+树索引,几十万的数据量和几千万的数据量查询效率不会相差太大,因为都是3次io
聚簇索引
聚簇索引(clustered index)就是按照每张表的主键所建立的一棵B+树,所以每张表只能有一个聚簇索引。
查询优化器会尽量选择采用聚簇索引来检索,因为聚簇索引能够在B+树叶子节点直接找到目标数据,由于叶子节点数据之间是靠双链表连接,所以能很方便的进行范围查询。
辅助索引
也成为非聚簇索引(secondary index),与聚簇索引最大的不同在于,辅助索引叶子节点保存的是聚簇索引,关系如下:
所以,如果通过辅助索引来检索,过程就是这样的:先通过辅助索引找到聚簇索引,在通过聚簇索引找到实际数据(这个过程称为回表)。如果辅助索引和聚簇索引层高都是3,那么通过辅助索引检索就需要6次io,而通过聚簇索引,只需要3次,这也是为什么在实际应用中应该多多使用聚簇索引。
Cardinality
cardinality的值非常关键,优化器会根据该值来决定是否要走这索引。可以使用show index from table xxx
命令来查看。它表示索引中唯一值的估计,它与总行数的比值应该接近于1,如果比值很小,那么应该删除该索引。
它的计算方式:随机对8个叶子节点进行分析然后更新。更新cardinality的策略如下:
-
表1/16分之一的数据已经发生过变化
-
stat_modified_counter
(发生变化的次数)> 20亿
也可以手动更新:analyze table xxx
,如果表很大,该操作会比较耗时,生产核心库需要谨慎操作。
所以,这个值的并不是特别准确,因为它的计算方式是随机采样的。
cardinality指示了哪些列适合建来建B+树索引,那些不重复的、没有范围的、散列的列才适合建索引。工作中经常看到有同事会在enum类型的列上建索引(也不能作为联合索引的第一项),这是不对的,因为这些列的取值是重复的、范围恒定的、聚集的,建的B+树分叉太少,层数太高,长的很像链表,大大加大了io次数,走这些索引会增大耗时,也许超过全表扫描。
联合索引
联合索引指的是对表上的多个列进行索引,单个索引只针对于一个列,原理和单个索引一模一样,示例如下:
联合索引有个好处是:第二个key做了排序处理。所以,如果结果集需要根据第二个key做排序,走联合索引可以减少一次内存排序的操作,可以提升查询效率。
索引覆盖
如果能够从辅助索引中就能获取到目标记录,不需要进行回表操作(不需要检索聚簇索引),那么这个行为就称为索引覆盖。
索引覆盖有两个好处:
-
不需要回表:减少了聚簇索引检索的时间
-
优化统计查询count( ):由于辅助索引保存的是聚簇索引,所以它的大小远远小于聚簇索引,也就是辅助索引一个叶子节点的数据页里保存的行数更多,在进行统计查询时通过辅助索引更能够减少磁盘io。
对于统计查询count( )如果不带where条件,myisam的效率远远大于innodb,因为myisam只支持页级锁,所以它有行数计数器,那么它的结果返回速度与表大小无关,查询耗时也可以忽略不计;而innodb支持行锁并且由于MVCC存在,每次count( )都需要对每行进行判断是否对当前事务可见,所以效率很低。
优化器选择不使用索引的情况
明明你在某些列上建了索引,并且你的查询条件中也使用了这些列,但是解释器告诉你他走了全表扫描,也就是使用了聚簇索引。这种情形有可能是由以下两种情况引起的:
-
索引不合理:参考上面分析过的cardinality。
-
范围查询、join连接:如果要查询的数据量占据总表的20%时,优化器会选择全表扫描。虽然可以通过辅助索引能够顺序的查出聚簇索引,效率很高,但是再根据聚簇索引去查询具体数据时,就变成了随机访问磁盘,通过上面的磁盘分析可以知道该操作会大大降低io效率,所以优化器会选择直接顺序的全表扫描。(这里的顺序依然是逻辑上的顺序,不可能做到全部数据是物理磁盘上的连续。innodb是按照区来分配空间的,每个区1M,一次分配连续的1-5个区,这里的连续是指物理上的连续,但是不可能做到所有数据都是物理上连续的,因为那样做的成本太高了)。
索引提示
索引提示的意思就是你显示的告诉优化器使用哪个索引,有两种情况:
-
在有多个可使用索引的情况下,你认为优化器选择的那个不是最优的,你可以提示或者强制优化器使用某个索引
-
在可选择的索引非常多的情况下,优化器选择执行计划的耗时可能都比执行脚本本身。这时,dba或者研发需要显示的告诉优化器使用哪个索引
上面两种情况都告诉我们,建太多索引不但不能提升效率,还有可能降低效率,此外,innodb还会花资源来维护索引,进一步降低性能。
索引提示有两种语法:
-
select * from table xx use index(xxx) where xx=xx : 这种方式只是提示优化器可以选择该索引,但是优化器有自己的想法,最终的执行计划如何取决于优化器的选择
-
select * from table xx force index(xxx) where xx=xx : 这种方式是告诉优化器把自己的想法收起来,乖乖按照我指定的索引执行,这里的好处是优化器不用在耗费时间去在多个索引中找一个最优的
倒排索引
通过上面的分析可以得知,等值查询、范围查询、like的前缀查询(like xxx%)等都可以利用到B+树的高效优点,但是对于like %xxx% 的模糊查询,B+树无能为力。倒排索引(inverted index)的引入就是为了弥补这个innodb在这个场景的不足。
数据结构
通过一张辅助表(auxiliary table)来存储关键字和关键字在一个或多个文档中所在位置的映射,有两种表现形式:
-
Inverted file index:(key,key所在文档的id)
-
Full inverted index:(key,(key所在文档的id,在文档中的偏移量))
Full inverted index 不仅存了文档id还存了偏移量,所以它占更多的存储空间,但是能更快的定位实际位置。这两种方式都是将一篇没有结构的文档转换为结构化的数据,相当于为每个词都建立了索引,从而可以做到全文的搜索。
全文检索
为了提高并发性,在innodb存储引擎中共有6张auxiliary table,每张表根据key的latin编码进行分区。为了获取key的latin编码,首先需要进行分词,分词的操作是在插入的事务提交后完成的。英文分词很简单,根据空格就可以,而中文分词有较多的算法,并且比较复杂还会有二义性,所以innodb不支持中文的全文检索,不过innodb5.7之后提供了ngram全文检索插件,用来支持中文分词。使用方式如下:
1、在mysql配置文件中设置分词的大小
ngram_token_size=2
分词越小,索引占用空间越大。
2、添加全文索引
alter table xx add fulltext index testfulltext(clumn1,clumn2) with PARSER ngram
因为auxiliary table是持久的表,并且存在磁盘上,所以为了提高性能,innodb引入了全文检索索引缓存(FTS index cache),其结构是一个红黑树。由于是内存和磁盘的关系,所以此处会涉及缓存有效及宕机恢复等问题,不做过多介绍,只需知道innodb利用缓存提升了检索性能。
innodb的全文检索有以下限制:
-
每张表只能有一个全文检索索引
-
由多个列组成的全文检索索引必须使用相同的字符集和排序规则
-
不支持没有单词界定符(delimiter)的语言,如汉语、日语、韩语等,但可以使用ngram插件来解决
检索模式
innodb 使用全文检索的语法为:
select * from xx where match(clumn1,clumn2) against('key' in xxx mode)
innodb会将在where 条件中使用match( )函数的查询结果根据相关性进行降序排列,相关性是一个非负浮点数,0表示没有任何相关性,其计算依据如下:
-
key是否在文档中出现
-
key在文档中出现的次数
-
key在索引列中的数量
-
包含该key的文档的数量
innodb支持四种检索模式
-
nature language : innodb默认的全文检索模式。所以查询时候可以省略mode修饰,直接使用
select * from xx where match(clumn1,clumn2) against('key')
-
boolean : 更为复杂查询模式,表示待查询key前后的操作符有特殊含义,支持的操作符如下:
-
+ : 该key必须存在
-
- : 该key必须不存在
-
(no operator) : 该key是可选的,但是如果出现,相关性会更高
-
@distance : 表示多个查询的key的距离是否在distance范围之内,单位是字节。'"dog cat"@30' IN BOOLEAN MODE表示单词dog和cat之间的距离需要在30个字节之内
-
> : 出现该key时增加相关性
-
< : 出现该key时降低相关性
-
~ : 出现该key时相关性为负(全文检索允许出现负相关性)
-
* : 出现以该key为前缀的词
-
" : 表示短语
-
-
query expansion : 全文检索的扩展查询
哈希索引
当服务器内存100多G甚至更大时候,想要找到一个被缓存的页,虽然内存中io速度很快,但也不能每次都要通过遍历的方式来查找,那是憨憨的做法。这时候需要对于字典操作只需要O(1)的哈希算法。
哈希算法
时间复杂度为O(1)的常见的简单的算法,就是将k个关键字映射到m个槽的某一个去,即k mod m,如果产生碰撞就采用链表法拉出一个链表。查询时候如果发现链表就进行遍历,此时遍历的数量会大大降低。
这里的链表法和HashMap的处理碰撞的方式如出一辙,只不过jdk1.8之后,HashMap引入了红黑树,当链表长度大于8后就转换为红黑树,这样再次降低了碰撞后的查询时间复杂度,从O(n)降到O(log n)。不过增加了操作的复杂度,因为增加了红黑数的左旋和右旋,还有链表转红黑树和红黑树转链表。但是总体性能1.8优于1.7。
自适应哈希索引
innodb中的哈希索引由mysql自身创建和管理,dba和研发不能进行干预,所以说它是自适应的。通过命令
SHOW ENGINE INNODB STATUS
可以看到当前自适应哈希索引的使用状况。需要注意的是,只有等值查询才能用到哈希索引,其实从上面的哈希算法分析就能得出该结论。
-
本文部分图片来自网络,侵权必删!若有不恰当之处,还望指教,谢谢