当前位置：首页 > news >正文

MySQL-8.0.x 新特性之索引页合并

news 来源：原创 2024/5/1 7:20:30

【背景】

　　索引的重要是在些不表、在这里我想说的另一个问题；索引和数据一样在innodb中都是以page的形式来组织的，那么问题就来了。

　　比如果说索引 ix_person_name 的内容只要8个页面就能完整的保存下来，如果这个时候一条insert语句来了，由于索引的8个

　　页面都是满的、我们只能在新的页面中保存这条insert(所插入数据)的索引。

　　1): 如果insert插入数据的索引值比当前的都要大或是都要小，那么新的索引页面只要加入到列表的尾部或首部就行了，这两种情况

　　相对来说还是比较“廉价”的。

　　2): 如果insert插入数据的索引在已有索引之间，也就是说它比最小的大、但是比最大的小。这个就要对索引页面做“裂解”了，这个

　　这个就相对来说比较“重”了。

【问题在哪里】

　　之所以引起“裂解”是因为在索引页面适当的位置上没有空间可以用来保存键值关系了，为了防止把页面写满使得最后要做“裂解”,数据库

　　引入了一个“填充因子”的概念，比如说填充因子设置为80%就是说当页面中的空间80%都已经写入数据之后就可以为索引分配新的页面

　　了，不要等到页面空间用完。这个就是期望通过多个相对“廉价”的操作来避免“重”的操作。

【副作用也不小】

　　由于“填充因子”是80%所以之前8个页面就可以保存下来的内容现在用了10个页面，由于操作是以页面为单位的所以最“糟糕”的情况下

　　数据库的多了20%的开销(当然事实上并没有这么多、这只是从页面数据数量这个维度来说的)。

　　以上所说的还不是全部、通常真实的填充率不太可能有80%；比如说在经历过一段时间的oltp之后，有一些数据被删除了，所以这些

　　数据所对应的索引内容也会被回收、这些内容之前占的空间可能就是先放着等着后面的重用。

　　假设数据量没有变多少，可以实际上的填充率由之前的80%下降到了10%，极端数据库的工作量又增加了。

　　总的来说索引在页面中排列的越是“紧密”对于读来说它可以明显的减小开销，但是这个会增大写的开销。之前DBA只能“暗中观察”，

　　不能“有所作为”，但是MySQL-8.0.x版本下变天了；DBA可以在一定程序上左右“历史的进程”！

【手段】

　　在mysql-8.0.x中DBA可以指定索引页面合并的阀值，比如说把阀值设置为50%也就是说当相邻的两个索引页面，当他们的填充率

　　都小于或等于50%的情况下就可能把这两个页面合并成一个页面。也就是说索引页面合并有利于“读”操作

【例子】

　　假设我们有一个person表并把name列的索引的合并阀值设置为45%，birthday列索引的阀值设置为48%　　

use tempdb;

create table person(id int not null auto_increment primary key,
    name varchar(16),
    birthday datetime default now());

create index ix_person_name on tempdb.person(name) comment 'MERGE_THRESHOLD=45';


create index ix_person_birthday on tempdb.person(birthday) comment 'MERGE_THRESHOLD=48';

　　1): MERGE_THRESHOLD 是以comment的形式体现在SQL中的、MySQL并没有为它单开一个子句

　　2): MERGE_THRESHOLD 只能是大写的小写的话会被“无视”

【可以从information_schema中查询索引页的合并阀值】

select * from INNODB_INDEXES where name in ('ix_person_name','ix_person_birthday') ;
+----------+--------------------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME               | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+--------------------+----------+------+----------+---------+-------+-----------------+
|      147 | ix_person_name     |     1059 |    0 |        2 |       5 |     2 |              45 |
|      148 | ix_person_birthday |     1059 |    0 |        2 |       6 |     2 |              48 |
+----------+--------------------+----------+------+----------+---------+-------+-----------------+
2 rows in set (0.01 sec)