第五章-索引与算法

5.1 InnoDB存储引擎索引概述 183

InnoDB存储引擎支持以下几种常见的索引:

• B+树索引
• 全文索引
• 哈希索引

InnoDB 存储引擎支持的哈希索引是自适应的，InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

B+树索引目前关系型数据库系统中查找最为常用和最为有效的索引。

B+树索引并不能找到一个给定键值的具体行，只能找到对应的页，然后把页读到内存，再在内存中进行查找。

5.2 数据结构与算法 184

5.2.1 二分查找法 184

5.2.2 二叉查找树和平衡二叉树 185

5.3 B+树 187

5.3.1 B+树的插入操作 187

5.3.2 B+树的删除操作 190

5.4 B+树索引 191

根据叶子节点存放的是否是一整行的信息，可将数据库中的B+树索引分为聚集索引(clustered inex)和辅助索引（非聚集索引）(secondary index)

5.4.1 聚集索引 192

聚集索引(clustered index)就是按照每张表的主键构造一棵 B+树，同时叶子节点中存放的即为表的行记录数据（所有叶子节点加起来就是整张表的行记录数据），也将聚集索引的叶子节点称为数据页。

聚集索引的存储并不是物理上连续的，而是逻辑上连续的。

5.4.2 辅助索引 196

对于辅助索引(Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签就是相对应行数据的聚集索引键。

5.4.3 B+树索引的分裂 200

如果定位到中间，从中间靠右三个记录开始分裂

如果定位到尾部，直接从插入记录开始分裂。

5.4.4 B+树索引的管理 202

1. 索引管理

   创建索引和删除索引的语法

   使用SHOW INDEX命令可以查看表中索引的信息

   其中，Cardinality属性表示索引中唯一值的数目的估计值。优化器会根据这个值来判断是否使用这个索引。但是这个值不是实时更新的，所以获取到的值不一定准确，如果需要更新索引的信息，可以使用analyze table t命令。在非高峰期，对核心表做analyze table操作，可以让优化器和索引更好为你工作。Cardinality/表总行数越大越接近1，创建索引的效果越好。

2. Fast Index Creation(FIC 快速索引创建)：

   过去创建索引要新建整张临时表，InnoDB存储引擎从InnoDB 1.0.x开始采用了FIC技术：对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，可进行读操作，不可进行写操作。因此FIC仅适用于辅助索引的创建，对于聚集索引还是要新建临时表。

3. Online Schema Change(在线架构改变 OSC)

   在事务创建的过程中，可以有读写事务对表进行操作，提高了原有MySQL数据库在DDL操作时的并发性。

4. Online DDL（在线数据定义操作）

   采用OSC时，索引创建时会阻塞表上的DML操作。Online DDL允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE, DELETE这类DML操作，这极大地提高了MySQL数据库在生产环境中的可用性。

5.5 Cardinality值 210

5.5.1 什么是Cardinality 210

查询结果是表中很少一部分数据时使用B+树索引才有意义。像性别字段，根据男或女来查，可能查出全表百分之50的数据，没有意义。

如果某个字段的取值范围很广，几乎没有重复，则属于高选择性，高选择性的字段适合使用B+树索引

如何查看索引是否是高选择性的呢？ 通过Cardinality值。

**Cardinality值：**表示索引中不重复记录数量的预估值。

Cardinality/n_ rows_ in_ table 应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

5.5.2 InnoDB存储引擎的Cardinality统计 212

本节介绍数据库是如何统计Cardinality值的。

首先，不可能索引一发生变化就更新Cardinality值，其次表特别大，统计Cardinality值时间会很长。

所以，数据库对于Cardinality的统计都是通过采样( Sample)的方法来完成的。

InnoDB 存储引擎内部对更新Cardinality信息的策略为:

• 表中1/16的数据已发生过变化。

• stat modified_ counter> 2 000 000 000。（因为只在一行上频繁操作，策略1检测不出来）

  stat modified_ counter用来表示发生变化的次数

Cardinality值通过采样进行更新，即用局部数据的变化情况预测整体。

InnoDB的参数

5.6 B+树索引的使用 215

5.6.1 不同应用中B+树索引的使用 215

联机事务处理OLTP（on-line transaction processing）应用中，每次查询结果只从数据库中取得一小部分的数据，建立B+树索引非常有意义。

联机分析处理OLAP（On-Line Analytical Processing）应用中，索引的添加根据的应该是宏观的信息，而不是微观，因为最终要得到的结果是提供给决策者的。

5.6.2 联合索引 215

联合索引是指对表上的多个列进行索引。

从本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

好处：对第二个键值也做了排序处理。

5.6.3 覆盖索引 218

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

通常情况下，诸如(a,b)的联合索引，一般不可以选择列b中所谓的查询条件，但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择。

5.6.4 优化器选择不使用索引的情况 219

对于不能进行索引覆盖的情况，优化器选择辅助索引的时候，查找的数据是少量的；如果数据量占整表的百分之20以上，会使用聚集索引，因为顺序读效率远远大于离散读。

5.6.5 索引提示 221

MySQL数据库支持索引提示(INDEX HINT),显式地告诉优化器使用哪个索引。

有以下两种情况可能用到INDEX HINT：

• MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。

• MySQL语句可以选择的索引非常多，这时优化器选择索引的时间开销可能会大于执行SQL语句本身。

• USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。而FORCE INDEX则是强制让优化器使用某个索引。

  5.6.6 Multi-Range Read优化 223

Multi-Range Read 多范围读(MRR), 它的作用针对基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。

MySQL 将根据辅助索引获取的结果集根据主键进行排序，将乱序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读。

其次MMR会将某些范围查询的查询条件进行拆分，从而避免许多无用的数据被取出。

5.6.7 Index Condition Pushdown（ICP）优化 226

一般是，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。

Index Condition Pushdown优化是指在查询索引的时候就进行WHERE`条件的判断（存储引擎层面筛选），前提是WHERE可以过滤的条件是该索引可以覆盖到的范围。

在某些查询下，可以大大减少上层SQL层对记录的索取( fetch)，从而提高数据库的整体性能。

5.7 哈希算法 227

5.7.1 哈希表 228

哈希算法是一种常见的算法，时间复杂度为O(1)

在数据库中一般采用最简单的碰撞解决技术，这种技术被称为链接法( chaining)。

为了使碰撞在最小程度下产生。一般来说，都将关键字转换成自然数，然后通过除法散列、乘法散列或全域散列来实现。数据库中一般采用除法散列的方法。

5.7.2 InnoDB存储引擎中的哈希算法 229

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

5.7.3 自适应哈希索引 230

自适应哈希索引采用之前讨论的哈希表的方式实现。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于等值查询非常快速，如SELECT * FROM TABLE WHERE index_ col= ’ xxx’。但是对于范围查找就无能为力了。

5.8 全文检索 231

5.8.1 概述 231

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

从InnoDB 1.2.x 版本开始，InnoDB 存储引擎开始支持全文检索，其支持MyISAM
存储引擎的全部功能，并且还支持其他的一些特性，这些将在后面的小节中进行介绍。

5.8.2 倒排索引 232

全文检索通常使用倒排索引(inverted index)来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表(auxiliary table)中存储了单词与单词自身在一个或多
个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式:

• inverted file index,其表现形式为{单词，单词所在文档的ID}

• full inverted index,其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)}

例子

原来存储的表：

inverted file index关联的数组：

full inverted index关联的数组：

5.8.3 InnoDB全文检索 233

InnoDB存储引擎从1.2.x 版本开始支持全文检索的技术，其采用full inverted index
的方式。在InnoDB存储引擎中，将(DocumentId, Position) 视为一个“ilist”。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上有设有索引。

倒排索引需要将word存放到一张表中，这个表称为Auxiliary Table (辅助表)。

全文索引的更新会先放在FTS IndexCache中，然后InnoDB 存储引擎会批量对Auxiliary Table进行更新。

在InnoDB存储引擎中，为了支持全文检索，必须有一个列与word进行映射，在InnoDB中这个列被命名为FTS_ DOC ID，其类型必须是BIGINT UNSIGNED NOT NULL，并且InnoDB存储引擎自动会在该列上加入一个名为FTS DOC ID_ INDEX的Unique Index。

stopword列表(stopword list)其表示该列表中的word不需要对其进行索引分词操作。

当前InnoDB存储引擎的全文检索还存在以下的限制:

• 每张表只能有一个全文检索的索引。

• 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

• 不支持没有单词界定符(delimiter) 的语言，如中文、日语、韩语等。

  5.8.4 全文检索 240

MySQL数据库通过MATCH()…AGAINST()语法支持全文检索(Full-Text-Search)的查询，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。下面将对各种查询模式进行详细的介绍。

1. Natural Language
   全文检索通过MATCH函数进行查询，默认采用Natural Language模式，其表示查询带有指定word的文档。

   例：

   

2. Boolean
   MySQL数据库允许使用IN BOOLEAN MODE修饰符来进行全文检索。当使用该
   修饰符时，查询字符串的前后字符会有特殊的含义。

例：

Boolean全文检索支持以下几种操作符：

3. Query Expansion
   MySQL数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要implied knowledge (隐含知识)时进行。例如，对于单词database的查询，用户可能希望查询的不仅仅是包含database 的文档，可能还指那些包含MySQL、Oracle、 DB2、RDBMS的单词。而这时可以使用Query Expansion模式来开启全文检索的implied knowledge.

该查询分为两个阶段：

- 第一阶段:根据搜索的单词进行全文索引查询。
- 第二阶段:根据第一阶段产生的分词再进行一次全文检索的查询。

例：

<img src="https://gitee.com/wang992997290/markdown_pictures/raw/master/jvm_book_notes_pictures/20200101182244.png" style="zoom:50%;" />

5.9 小结 248

本章介绍了一些常用的数据结构，如二分查找树、平衡树、B+树、直接寻址表和哈
希表，以及InnoDB1.2版本开始支持的全文索引。从数据结构的角度切人数据库中常见的B+树索引和哈希索引的使用，并从内部机制上讨论了使用上述索引的环境和优化方法。