MySQL进阶-索引与算法

InnoDB存储引擎索引概述

• 常见的索引
  • B+树索引
    • 找到被查找数据行所在的页，数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据
  • 全文索引
  • 哈希索引

数据结构与算法

介绍B+树索引之前，介绍相关的数据结构与算法，便于理解B+树索引

二分查找法

• 二分查找(binary search)，折半查找
  • 将记录有序化排列，在查找过程中采用跳跃式方式查找。
  • 查找效率高
  • 对于某一条具体记录的查询是通过对Page Directory进行二分查找得到的

二叉查找树和平衡二叉树

• 二叉查找树，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值
  • 二叉查找树的平均查找速度比顺序查找来得更快
  • 但是当二叉查找树左右子树的高度差距极大时，极大地影响查找速度，若是想最大性能地构造一棵二叉树，需要这棵二叉查找树是平衡的
• 平衡二叉树（AVL树），首选符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1.
  • 查询速度快，但是维护一棵AVL树代价非常大

B+树

• B+树中，所有记录节点都是按键值的大小顺序放在同一层叶子节点上，由各叶子节点指针进行连接。

B+树的插入操作

• 必须保证插入后叶子节点的记录依然排序
  
• 为了保持平衡对新插入的键值可能需要做大量拆分页的操作。由于B+树结构主要用于磁盘，页的拆分意味着磁盘操作，所以应当尽可能减少页的拆分操作。
  • 因此B+树针对LeafPage满的情况进行旋转操作

B+树的删除操作

B+树索引

• B+树索引的本质就是B+树在数据库中的实现，其特点有高扇出性
• 在数据库中，B+树的高度一般是2-4层，即查找某一个键值的行记录时最多只需要2到4次IO
• B+树索引
  • 聚集索引
  • 非聚集索引

聚集索引

• 聚集索引按照每张表的主键构建一棵B+树，同时叶子节点中存放的为整张表的行记录数据，因此聚集索引的叶子节点也称为数据页
  • 数据页通过一个双向链表进行连接
  • 每张表只能拥有一个聚集索引，因为实际的数据页只能按照一张B+树进行排序

非聚集索引

• 辅助索引（Secondary Index，非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行还包含了一个书签
  • 书签，告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据，就是相应行数据的聚集索引键。
  • 每张表可以有多个辅助索引。
  • 通过辅助索引寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录
    

B+树索引管理

索引管理

• 创建索引

  • ALTER TABLE
  • CREATE [UNIIQUE] INDEX index_name

• 删除索引

  • DROP INDEX

• 查看索引信息

  • SHOW INDEX

  哈希算法

  哈希表

  • hash table，也称为散列表，由直接寻址表改进而来。在哈希方式下，该元素处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域U映射到哈希表T[0…m-1]的槽位上
  • 哈希碰撞：两个关键字映射到同一个槽上
    • 在数据库中的解决方式：链接法，把散列到同一个槽中的所有元素都放在一个链表中
    • 哈希函数必须可以很好地进行散列

InnoDB存储引擎中的哈希算法

• InnoDB存储引擎使用哈希算法对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列表方式。

自适应哈希索引

• 经哈希函数映射到一个哈希表中，对于字典类型的查找非常快速，无法进行范围查找，只能用于搜索等值的查询

全文检索

• 全文检索（Full-Text Search）是将数据库中的整本书或整篇文章的任意内容信息查找出来的技术。可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。
• InnoDB和MyISAM都支持全文检索

倒排索引

• 全文检索通常使用倒排索引（inverted index）实现，是一种索引结构。在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。