InnoDB存储引擎 (第5章 索引与算法)
5.1 InnoDB存储引擎索引概述
如果索引太多，应用的性能可能会受到影响，如果索引太少，对查询性能又会产生影响，要找到一个合适的平衡点，这对应用的性能至关重要。
索引本质:
一种数据结构。索引中是包含一个表中列的值和它的物理地址的值，并且这些值存储在一个数据结构中。
常见的索引:
B+树索引，
全文索引,
hash索引。
哈希索引是自适应的，会根据表的使用情况自动为表生成hash索引，不能人为干预是否在一张表中生成hash索引。
B+树(b不是二叉,而是平衡的意思,因为B+树是从平衡二叉树演化而来,但不是一个二叉树);
B+树索引查找过程:
B+树索引并不能找到一个给定键值的具体行,只能找到的是被查找数据行所在的页;然后数据库通过把页读取到内存,再在内存中查找,才能得到要查找的数据;
5.2 数据结构与算法
5.2.1 二分查找法
每页的Page Directory中的槽是按照主键的顺序存放的,对于某一条具体记录的查询时通过对Page Directory进行二分查找得到的;
5.2.2 二叉查找树和平衡二叉树
- 二叉查找树：
左子树的键值总小于根节点的键值，右子树的键值总大于根节点的键值。因此可以通过中序遍历得到键值的排序输出,
- 平衡二叉树（AVL树）
平衡二叉树定义：首先符合二叉查找树的定义，其次必须满足任何节点的左右两个子树的高度最大差为1。
平衡二叉树对于查找的性能是比较高的，但不是最高的，只是接近最高性能。要达到最好的性能，需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量操作，因此我们一般只需建立一棵平衡二叉树即可。
平衡二叉树对于查询的速度的确很快，但是维护一个平衡二叉树的代价非常大，通常需要一次或多次左旋和右旋来得到插入或更新后树的平衡性。
5.3 B+树
B+树由B树和索引顺序访问方法(ISAM)演化而来。
B+树是为磁盘或其他直接存取辅助设备而设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶结点指针进行连接,
看一个B+树的例子，下面这个B+树高度为2，每页可存放4条记录，扇出（fan out）为5。
上图，所有记录都在叶节点，并且是顺序存放的，如果我们从最左边的叶节点开始遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。
5.3.1 B+树的插入操作
三种情况，涉及三种插入算法：
应在可能的情况下尽量减少页的拆分操作。B+ 树提供了类似于 AVL 树的旋转功能减少页的拆分。
5.3.2 B+树的删除操作
B+ 树使用填充因子来控制树的删除变化，50%是填充因子可设的最小值。
5.4 B+树索引
B+树索引其本质就是B+树在数据库中的实现。但是B+索引在数据库中有一个特点就是其高扇出性，因此在数据库中，B+树的高度一般都在2~3层，也就是对于查找某一键值的行记录，最多只需要2~3次IO。
数据库中的B+索引可以分为聚集索引和辅助聚集索引。不管是聚集索引还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。
5.4.1 聚集索引
前面提到，InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。
聚集索引就是按照每张表的主键构造一棵B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树结构一样，每个数据页都通过一个双向链表来进行链接。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在许多情况下，查询优化器非常倾向于采用聚集索引，因为聚集索引能够让我们在索引的叶节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够非常快的访问针对范围内的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。
聚集索引的另一个好处在于：它对于主键的排序查找和范围查找速度非常快。叶节点的数据就是我们要查询的数据，如我们要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，我们可以快速找到最后一个数据页，并取出10条记录。
如果要查找主键某一范围内的数据，通过叶节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。
页通过双向链表连接,页按照主键的顺序排序 ;
每个页中的记录也是通过双向链表进行维护的,物理存储上可以同样不按照主键存储;
数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。
聚集索引的存储并不是物理上连续的，而是逻辑连续的。
范围查询: 查找主键某一范围内的数据,通过页子节点的上层中间节点就可以得到页的范围,之后读取数据页即可;
例如: select * from table_xxx where id > 10 and id<1000;
5.4.2 辅助索引
secondary Index 也称非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。
辅助索引的书签就是相应的行数据的聚集索引键;
辅助索引查找数据的过程:
Innodb存储引擎会遍历辅助索引 (要遍历树) ;
通过叶级别的指针获取指向主键索引的主键;
通过主键索引(也要遍历树)找到完整的行记录;
下面是一个分析例子:
5.4.3 B+树索引的分裂
Innodb存储引擎的Page Header中有几个部分用来保存插入的顺序信息 :
- PAGE_LAST_INSERT
- PAGE_DIRECTION
- PAGE_N_DIRECTION
通过这些信息,innodb决定是向左还是向右进行分裂,同时决定将分裂点记录为哪一个;
例如 ,1,2,3,4,5,6,7,8,9 ;
插入10条记录需要进行分页操作;
若插入是随机的,则取页的中间记录作为分裂点的记录;
结果为两个页 : p1: 1,2,3,4 p2: 5,6,7,8,9,10 ; 当然这种情况P2会再分裂;
若往同一个方向进行插入的记录数量为5,并且目前已经定位(cusor) 到的记录之后还有3条记录,则分裂点的记录为定位到的记录后的第三条记录,否则分裂点记录就是待插入的记录;
定位到的记录 : innodb插入时,首先需要定位,为待插入记录的前一条记录 ;
向右分裂:
5.4.4 B+树索引的管理
- 索引管理
在非高峰时期，对应用程序下的几张核心表做 ANALYZE TABLE 操作，使得优化器和索引更好地工作。
查看索引: SHOW INDEX from test1 ;
--> 结果列分析:
- Table ：索引所在的表名
- Non_unique ：非唯一的索引。可以看到primary key是0，因为必须是唯一的。
- Key_name ：索引的名称。可以通过这个名称来DROP INDEX。
- Seq_in_index ：索引中该列的位置。联合索引idx_a_b就比较直观。
- Column_name：索引的列。
- Collection ：列以什么方式存储在索引中。可以是'A'或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，就会显示NULL，因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。
- Cardinality ：非常关键的值，表示索引中唯一值的数目的估计值。Cadibality/表的行数应尽可能接近1，如果非常小，那么需要考虑是否还需要建这个索引。
- Sub_part ：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示我们只索引b列的前100个字符。如果索引整个列，则该字段为NULL。
- Packed ：关键字如何被压缩。如果没有被压缩，则为NULL。
- Null ：是否索引的列含有NULL值。如果有则是Yes。
- Index_type ：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
- Comment ：注释。
- Fast Index Creation（FIC）
让InnoDB避免创建临时表，只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。
辅助索引的创建,Innodb会对创建索引的表加上一个S锁,(其他事物不可用)在创建过程中,不需要重建表,速度提高;
删除辅助索引,innodb只需要更新内部视图,并将辅助索引的空间标记为可用,同时删除mysql数据库内部视图上对该表的索引定义即可;
3.Online Schema Change（OSC-在线架构改变）
脚本复杂,有一定条件限制,比如不能有外键和触发器等,导致主从不一致问题;
4.Online DDL
create index ts_b_index
on ts (b)
LOCK = DEFAULT
;
创建或删除索引过程可以对表添加锁,可以选择:
NONE:无锁,不会阻塞 ;
SHARE:加S锁 ,支持并发读事务,写事务会等待;
EXCLUSIVE: X锁.读写事务都不能进行,阻塞所有线程
DEFAULT:从低级NONE到高级EXCLUSIVE选择;
innodb在线DDL操作原理:
执行创建或者删除操作同时,将INSERT,update,DELETE这类DML操作日志写入到一个缓存中,
待完成索引创建后再redo应用到表上,以此达到数据的一致性;
缓存大小参数: innodb_online_alter_log_max_size ,默认大小128MB,
还可以设置模式为SHARE,就不会有写事务发生;
5.5 Cardinality值
5.5.1 什么是Cardinality
基数 , 高选择性的列数据 适合做索引;
是估计值,不是准确值;
5.5.2 InnoDB存储引擎的Cardinality统计
5.6 B+树索引的使用
5.6.1 不同应用中B+树索引的使用
5.6.2 联合索引
联合索引是指对表上的多个列做索引。 前面情况都是只对表上的一个列进行了索引;
联合索引的创建方法：
alter table t add key idx_a_b(a,b);
从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。
从上图可以看到多个键值的B+树情况，其实和之前讨论的单个键值没什么不同，键值都是排序的，通过叶节点可以逻辑上顺序地读出所有数据。就上面例子来说即：(1,1)，(1,2)，(2,1)，(2,4)，(2,4)，(3,1)，(3,2)。数据就按(a,b)的顺序进行了存放。
- 因此，对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然可以使用(a,b)这个联合索引。
- 对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx 也是可以使用(a,b)索引。
- 但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx 不可以使用这棵B+树索引。因为叶节点上的b值为1,2,1,4,1,2，显然不是排序的，因此对于b列的查询使用不到(a,b)的索引。
联合索引的第二个好处是，可以对第二个键值进行排序。
例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，去除最近3次的购买记录，这是使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。
【注】：
对于相同的第一个键值的数据，第二个键值是排好序的。
(a是索引,并且a,b是联合索引) 对于单个列a的查询往往使用单个键的索引，因为其叶节点包含单个键值，能存放的记录更多。优化器选择单个索引而不是联合索引;
5.6.3 覆盖索引
覆盖索引（covering index），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
好处是: 辅助索引不包含整行记录的所有信息，所以其大小远小于聚集索引，能减少大量 IO 操作。
覆盖索引的另一个好处是针对某些统计问题而言的,Using index 就是代表了优化器进行了覆盖索引操作。
例如:
demo 表 有id 主键和 name 索引;
explain
select count(*) from demo;
-->结果:
实际选择的并不是聚集索引id,而是辅助索引name ;
另外查询条件中(a,b)联合索引的count时,也可能选择联合索引作为覆盖索引操作;
联合索引和覆盖索引都是辅助索引的应用。
5.6.4 优化器选择不使用索引的情况
使用 EXPLAIN 命令进行 SQL 分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描。
多发生于范围查找，JOIN 链接操作等情况。
可以使用force index来强制使用某个索引;
5.6.5 索引提示
INDEX HINT显示地告诉优化器使用哪个索引
使用情况;
- mysql数据库错误地选择了某个索引,导致SQL语句运行的很慢,(很少见) 这时候可以强制优化器使用某个索引,提高SQL运行的速度;
- SQ;L语句可以选择的索引非常多,这时候优化器选择执行计划时间的开销可能会大于SQL语句本身,(例如优化器分析RANGE查询比较耗时) ;
例如:
create table t_index_hint
(
a int,
b int,
key (a),
key (b)
) engine = INNODB;
insert into t_index_hint values (1,1);
insert into t_index_hint values (1,2);
insert into t_index_hint values (2,3);
insert into t_index_hint values (2,4);
insert into t_index_hint values (1,2);
explain
select * from t_index_hint where a = 1 and b = 2;
-->结果:
分析:
possible_keys : 显示可使用的索引a,b ;
key: 实际使用的索引同样为a,b ;
Extra : Using intersect(b,a) 表示根据两个索引得到的结果进行求交,最后得到结果 ;
explain
select * from t_index_hint force index (a) where a = 1 and b = 2;
- USING INDEX 只是告诉优化器选择指定的索引，优化器不一定真的会选择。
- FORCE INDEX 是强制优化器选择指定的索引。
5.6.6 Multi-Range Read优化 (MRR优化)
mysql5.6后才有的。
MRR优化的目的是: 减少磁盘的随机访问，并且将随机访问转化为顺序访问。
MRR优化的好处:
- 使得数据访问变为顺序; 在查询辅助索引时,先对得到的查询结果按照主键进行排序,并按照主键排列的顺序进行书签查找;
- 减少缓冲池页被替换的次数 ;
- 批量处理对键值的查询操作
对于InnoDB和MyISAM的范围查询和联接查询, MRR工作方式如下:
- 将查询得到的辅助索引键值存放于一个缓存中,这是缓存中的数据是根据辅助索引键值排序;
- 将缓存中的键值根据ROWID进行排序
- 根据rowId的排序顺序来访问实际的数据文件
MRR参数 : optimizer_switch ;
键值缓冲区大小参数 : read_rnd_buffer_size ;
5.6.7 Index Condition Pushdown（ICP）优化
mysql5.6后才有的。
ICP是一种根据索引来查询的优化方式;
支持ICP后,mysql会取出索引同时,判断是否可以进行where 条件过滤, 即 将where的部分过滤操作放在存储引擎层; (推送的索引条件)
在某些查询过程中,ICP 会达达减少上层SQL层对于记录的索取(fetch),从而提高数据库的整体性能;
优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层
1) storage engine从索引中读取下一条索引元组。
2) storage engine使用索引元组评估下推的索引条件。如果没有满足where条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据
3) 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
5.7 哈希算法
5.7.1 哈希表
由直接寻址表改进而来;
全域 U = {0, 1, ....m-1} 中存关键字;
用一个数组(即直接寻址表) T [0....m-1] 表示动态集合, 其中每个位置(称槽或桶) 对应全域U中一个关键字 ;
槽K指向 集合中一个关键字为K的元素,如果没有则T[K]=NULL;
利用散列函数 h , 根据关键字K计算出槽的位置;
不过这样有一个问题就是 两个关键字可能映射到同一个槽上(碰撞) ;
数据库中一般采用最简单的解决方法,即链接法;
链接法中,将同一个槽中的所有元素放在一个链表中;
5.7.2 InnoDB存储引擎中的哈希算法
Innodb使用散列算法对字典进行查找;
冲突机制采用链表方式;
散列函数采用除法散列方式;
对于缓冲池页的散列来说,
缓冲池中一个page页都有一个chain指针,指向相同散列函数值的页;
比如当前参数innodb_buffer_pool_size 大小为10MB,则 共有 640x16KB 的页, 对于缓冲池页内存的散列表来说,则需要分配 640*2=1280 个槽,(略大于2倍缓冲页数量的质数), 取比1280略大的质数1399, 则启动时会分配1399个槽的散列表,用来散列查询所在缓冲池中的页;
将要查找的页转换成自然数;
Innodb的表空间都有一个space号, 我们需要查找的应该是某个表空间的某个连续的16Kb的页, 即偏移量offset, Innodb将space左移20位,然后加上这个space和offset,
即 关键字 K=space <<20 +space +offset 然后通过除法散列到各个槽中去;
5.7.3 自适应哈希索引
由innodb自己控制,不受DBA或开发人员控制; 不过可以通过参数开启或禁用此特性;
查看自适应哈希索引的情况：
show engine innodb status ;
自适应哈希索引经散列函数映射到一个散列表中, 对于 where index_col = 'xxx' 这种字典类型查找非常快速;
哈希索引只能是等值查询。因为哈希函数映射后就是一个值，就是通过比较值来得到对应的槽。
范围查找是不能使用自适应哈希索引;
5.8 全文检索
5.8.1 概述
Full-Text Search） 搜索引擎根据 输入关键字进行全文查找;
5.8.2 倒排索引
全文检索通常使用倒排索引(inverted index) 来实现;
同 B+ 树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。有两种表现形式：
inverted file index：{单词，单词所在文档的ID ->documentId}
full inverted index：{单词，（单词所在文档ID，在具体文档中的位置） pair (DocumentId, Position}
full inverted index 占用更多空间，但能更好的定位数据，并扩展搜索功能。
例如 : 单词code (1 :6) 表示出现在文档 1 的第6个单词 ;
5.8.3 InnoDB全文检索
采用 full inverted index，将 （DocumentID, Position）看作一个"ilist"。
为了提高全文检索的并行性能，InnoDB 共有6张辅助表，每张表根据 word 的 latin 编码进行分区。
辅助表 (Auxiliary) 都是持久的表，存放于磁盘上。
FTS Index Cache 全文检索索引缓存，用来提高全文检索的性能，采用红黑树结构，根据 (word, ilist) 进行排序。
InnoDB 全文检索目前的限制：
- 每张表只能有一个全文检索的索引；
- 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则；
- 不支持没有单词界定符的语言，如中文，日韩语
辅助表 (Auxiliary table) 更新:
先是在FTS index cache缓存中, 辅助表中可能还没有更新, innodb会批量对辅助表进行更新,而不是每次插入后更新一次;
当全文检查查询时. 辅助表会将在FTS index cache中的数据合并到辅助表中,然后在进行查询;
FTS index cache作用和Inser buffer作用类似,但是结构不同,一个是B+树, 一个是红黑树;
show variables like '%innodb_ft_aux_table%' ;
新增(写入分词) 发生条件:
- 对于Innodb而言,总是在事务提交时将分词写入FTS index Cache,然后批量更新写入到磁盘 ;
- 数据库关闭时, FTS index Cache 中的数据会同步到磁盘的辅助表 中;
- 如果宕机,下次重启时,当用户对表进行全文检索(查询/插入操作) 时,InnoDb存储引擎会自动读取未完成的文档,然后进行分词操作,放到FTS index Cache中 ;
FTS index Cache 大小:
show variables like '%innodb_ft_cache_size%' ;
mysql8 默认8M, 当改缓存满时,会将其中部分分词信息同步到辅助表中;
FTS Document ID:
有一个列与word 进行映射,(FTS_DOC_ID) ;
分词的删除操作:
只是删除FTS index Cache中的记录,
手动将已经删除的记录从索引中删除设置参数:
show variables like '%innodb_optimize_fulltext_only%' ;
限制每次实际删除的分词数量(默认2000):
show variables like '%innodb_ft_num_word_optimize%' ;
举例:
create table fts_a
(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
BODY text,
primary key (FTS_DOC_ID)
)
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (1, 'Please porridge in the port');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (2, 'Please porridge hot, Please porridge cold');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (3, 'Nine days old');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (4, 'Some like it hot, some like it colde ');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (5, 'Some like in the port');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (6, 'Nine days old');
INSERT INTO test.fts_a (FTS_DOC_ID, BODY) VALUES (7, 'I like cold days');
create FULLTEXT INDEX idx_fts on fts_a(BODY);
设置参数来查看分词对应信息 :
set global innodb_ft_aux_table = 'test/fts_a';
select * from information_schema.INNODB_FT_INDEX_TABLE ;
-->结果 :
每个word 对应一个doc_id和position,
FIRST_DOC_ID : 该word第一次出现的文档ID,
last_doc_id : 该word最后一次出现的文档ID,
doc_count : 该word在多少个文档中存在;
stopword list: 表示word不需要对其进行索引分词操作 ;
5.8.4 全文检索
MySQL 数据库使用 MATCH（）。。。AGAINST（）语法支持全文检索的查询
MATCH: 指定了需要被查询的列,
AGAINST: 指定了使用任何种 方法进行查询;
语法为：
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}
以下为3中查询模式：
- Natural Language
默认模式,表示查询带有指定word的文档,
select * from fts_a where BODY like '%Please%' ;
select * from fts_a where match(BODY) AGAINST ('Please' IN NATURAL LANGUAGE MODE ) ;
select * from fts_a where match(BODY) AGAINST ('Please' )
执行计划显示: fulltext (全文检索的倒排索引) ;
where 条件中使用match函数,返回结果是根据相关性(Relevance)
进行降序排序的, 相关性最高的结果放第一位;
相关性Relevance:
非负浮点数, 0表示没有任何相关性;
相关性的计算基于 行(文档)中的单词数量、行中唯一单词的数量、集合中的单词总数、 以及包含特定单词的行数。
- Boolean
IN BOOLEAN MODE修饰符时,查询字符串的前后字符会有特殊的含义,
例如,查询字符串有Please但是没有hot的文档,其中+和-分别表示这个单词必须出现,或者一定不存在 :
select * from fts_a where match(BODY) AGAINST ('+Please -hot' IN BOOLEAN MODE ) ;
-->结果:
Boolean全文检索支持以下几种操作符
- + 表示该word必须存在
- - 表示该word必须被排除
- (no operator) 表示该word可选,但如果出现相关性会更高
- @distance 表示查询的多个单词之间的距离是否在distance之内,distance是字节; 这种全文检索称为 proximity Search (接近检索);
例子: select * from fts_a where match(BODY) AGAINST ('"Please hot"@30' IN BOOLEAN MODE ) ; d
- > 表示出现该单词时增加相关性
- < 表示出现该单词时降低相关性
- ~ 表示允许出现该单词,但是出现时相关性为负 (全文检索允许负相关性)
- * 表示该单词开头的单词
- '' 表示短语
- Query Expansion
支持扩展查询,
查询短语中添加with query expansion 可以开启blind query expansion (又称为automatic relevance feedback);
该查询分为两个阶段:
- 根据搜索的单词进行全文索引查询
- 根据第一阶段产生的分词再进行一次全文检索的查询
例如: 对于单词database的查询,用户可能查询包含mysql , oracle ,DB2, RDNMS的单词等;
5.9 小结
省略..
浙公网安备 33010602011771号