InnoDB--索引

最新推荐文章于 2022-01-06 18:20:30 发布

llyilo

最新推荐文章于 2022-01-06 18:20:30 发布

阅读量140

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/llyilo/article/details/79441836

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

索引类型:

1. B+ 树索引

a. 聚集索引

b. 辅助索引

c. 索引的分裂

d. 索引的管理

i. 索引管理

ii. Fast Index Creation( 快速索引创建 )

对于辅助索引的创建， InnoDB 存储引擎会对创建索引的表加上一个 S 锁。不需要建表，因些速度提高了很多。删除时，只需要更新内部视图，并将辅助索引的空间标记为可用，同时删除 MYSQL 数据库内部视图上对该表的索引定义即可。

iii. OnlineSchema Change

在经架构改变，在事务的创建过程中，可以有读写事务对表进行操作，提高了原有 MYSQL 数据库在 DLL 操作时的并发性。

iv. OnlineDLL

允许辅助索引创建的同时，还允许如下操作：

1). Insert、update、delete

2). 辅助索引的创建与删除

3). 改变自示增长值

4). 添加或删除外键约束

5). 列的重命名

新alter table 语法

Alter table tbl_name

| ADD { INDEX | KEY } [ index_name ]

[ index_type ] {index_col_name , ...} [index_option] …

ALGORITHM [ = ] {DEFAULT | INPLACE | COPY}

LOCK [ = ] {DEFAULT | NONE | SHARED | EXCLUSIVE}

ALGORITHM: 指定了创建或删除索引的算法， COPY 表示按照 MYSQL5.1 版本之前的工作模式，即创建临时表的方式。 INPLACE 表示索引创建或删除操作不需要创建临时表。 DEFAULT 表示根据参数 old_alter_table 来判断是通过 INPLACE 还是 COPY 的算法，默认为 OFF, 表示采用 INPLACE 的方式。

LOCK ：索引创建或删除时对表添加锁的情况，选项：

1). NONE

执行索引创建或者删除操作时，对目标表不添加任何锁，即事务仍然可以进行读写操作，不会受到阻塞。因此这种模式可以获得最大的并发度。

2). SHARE

FIC 类似，执行索引创建或删除操作时，对目标表加上一个 S 锁。对于并发读事务，仍然可以执行，遇到写事务，就会发生等待操作。如果引擎不支持 SHARE ，会返回一个错误信息。

3). EXCLUSIVE

执行索引创建或删除操作时，对目标加上一个X锁。读写事务都不能进行，因此会阻塞所有的线程。

4). DEFAULT

首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发改天来判断执行DLL的模式。

e. B+树索引的使用

i. 不同应用中 B+ 树索引的使用

OLTP中，取出很少数据时，使用B+索引非常有意义

OLAP中，对于多张表联合查询，仍然有意义，但使用HASH JOIN则变得意义不

ii. 联合索引

好处：

1). 条件语句中，使用前一个列，仍然可以使用索引

2). 已经对第二个键值进行了排序处理。

iii. 覆盖索引

从辅助索引中就可以但询记录；使用覆盖索引的好处是，辅助索引不包含整行记录的所有信息，故其大小远小于聚集索引，因此可以减少大量IO操作。

iv. 优化器选择不使用索引的情况

如果要求访问的数据量很少，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据蛮大一部分时（一盘20%），优化器会选择通过聚集索引来查找数据。

v. 索引提示

（index hint）使用的情况：

1). MYSQL优化器错误的选择某个索引

2). 选择的索引非常多

语法：

vi. Multi-Range Read 优化

Multi-Range Read 优化的目的是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。

好处：

1). MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。

2). 减少缓冲池中页被替换的次数

3). 批量处理对键值的查询操作

范围查询和JOIN的工作方式：

1). 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。

2). 将缓存中的键值根据ROWID进行排序

3). 根据ROWID的排序顺序来访问实际的数据文件

是否启用MRR，通过参数optimizer_switch中的标记（flag）来控制。当mrr为on时，表示启用MRR优化。 Mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。若将mrr设为on，mrr_cost_based设为 off，则总是启用MRR优化。

vii. Index condition Pushdown (ICP) 优化

在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE条件的过滤操作放在了存储引擎层。

2. 全文索引

是将存储于数据库中的整本书或整篇文章中的任意内容信息进行各种统计和分析。

a. 倒排索引

全文检索通常使用倒排索引来实现。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。通常利用关联数组实现，其拥有两种表现形式：

i. Inverted file index，其表现形式为{单词，单词所在文档的ID}

ii. Full inverted index，表现形式为{单词，（单词所在文档的ID，在具体文档中的位置）}

b. InnoDB全文检索

InnoDB存储引擎从1.2.x版开始支持全文检索，采用Full inverted index方式。在InnoDB存储引擎中，将 (DocumentId,Position)视为一个“ilist”。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上有设有索引。此外，由于InnoDB存储引擎在ilist字段中存放了Position信息，故可以进行proximity Search.

c. 全文检索

语法: