InnoDB 索引
1. InnoDB存储引擎索引:
B+树索引;全文索引;哈希索引
InnoDB引擎支持的哈希索引是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。
B+树索引,根据键值快速找到数据。B+树索引并不能找到一个给定键值的具体行,B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入内存,再在内存中进行查找,最后得到要查找的数据。
B+树是一个平衡树。
B+树为了保持平衡对于新插入的键值可能需要做大量的拆分页split操作。因为B+树结构主要用于磁盘,页的拆分意味着磁盘的操作,所以应该在可能的情况下尽可能减少页的拆分操作。因此,B+树同样提供了类似于平衡二叉树的旋转功能。
旋转发生在叶子页已经满,但是其左右兄弟没有满时。B+树并不会急于去做拆分页的操作,而是将记录移到所在页的兄弟节点上。通常先检查左兄弟节点。
B+树索引的本质就是B+树在数据库中的实现。但是B+树索引在数据库中有一个特点是高扇出性,因此在数据库中,B+树的高度一般都在2~4层,也就是说查找某一键值的行记录时最多只需要2到4次IO。
B+树索引可以分为聚集索引clustered index和辅助索引secondary index。
两者内部都是B+树,即高度平衡,叶子节点存放着所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的信息。
聚集索引:
InnoDB存储引擎表是索引组织表,即表中数据按照主键顺序存放。
聚集索引就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。
每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一颗B+树进行排序,因此每张表只能拥有一个聚集索引。
在多数情况下,查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外,由于定义了数据的逻辑顺序,聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
聚集索引按照特定顺序存放物理记录。它的存储并不是物理上连续的,而是逻辑上连续的。
页通过双向链表链接,页按照主键的顺序排序;每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储。
聚集索引对于主键的排序查找和范围查找速度非常快。
辅助索引:
也称为非聚集索引,叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签(bookmark)。
该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引不会影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。通过辅助索引来查找时,先找到其对应的聚集索引的键,再通过聚集索引找到数据所在的页。其速度一般是聚集索引的2倍。
B+树索引的管理
索引的创建和删除可以通过两种方法,一种是ALTER TABLE,另一种是CREATE/DROP INDEX。
通过ALTER TABLE创建索引的语法为:
ALTER TABLE tbl_name
ADD INDEX/KEY index_name
index_type (index_col_name,...) [index_option]...
ALTER TABLE tbl_name
DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
CREATE/DROP INDEX的语法同样很简单:
CREATE [UNIQUE] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
DROP INDEX index_name ON tbl_name
查看表中索引的信息,使用SHOW INDEX:
SHOW INDEX FROM tbl_name;
优化器在join或范围查询较多的数据量时不采用索引。
联合索引:索引由多列组成。
优化器会根据检索情况选择索引。也可自己用 FORCE INDEX(index_name) 来强制使用某个索引。
MRR:multi-range read优化,当通过非聚集索引读取大量数据时,先将读取的bookmarks放入缓存区,排序后再进行聚集索引的数据页查找,减少了IO操作和页替换操作,
ICP:index condition pushdown优化,旧版本中对索引的查找找到数据后,取出数据再对WHERE条件进行判断。使用ICP优化后,在索引查找时就进行where判断,减少了fetch无效数据带来的消耗。
InnoDB存储引擎的哈希算法:
InnoDB存储引擎使用哈希算法来对字典进行查找,其冲突机制采用链表方式。哈希函数采用除法散列方式。
对于缓冲池页的哈希表来说,在缓冲池中的Page页都有一个chain指针,它指向相同哈希函数值的页。
对于除法散列,m的取值为略大于2倍的缓冲池页数量的质数。
全文检索:
是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息,也可以进行各种统计和分析。
倒排索引:
倒排索引同B+树索引一样也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。
倒排索引有两种形式:{单词,单词所在文档的ID}、{单词,(单词所在文档的ID,在具体文档中的位置)}
倒排索引将word放入一张表中,称为辅助表auxiliary table。在InnoDB存储引擎中,为了提高全文检索的并行性能,公有6张辅助表,目前每张表根据word的Latin编码进行分区。
辅助表是持久的表,存放于磁盘上。
FTS Index Cache是一个红黑树结构,其根据(word,ilist)进行排序。