MYSQL能存储大数据么

文章目录

• 1. 简介

• 1.1. innodb 的数据页查询
• 1.2 B+ 树是如何检索记录？

• 2. 如何计算B+树的高度？

• 2.1 定位表的根页
• 2.2 找到mysql的数据目录
• 2.3 计算 page_level

• 3. 一棵树可以存放多少行数据？

• 3.1 指针数如何计算？
• 3.2 实战演练

1. 简介

计算机有五大组成部分：控制器，运算器，存储器，输入设备，输出设备。

存储器范围比较大，但是数据具体怎么存储，有自己的最小存储单元。

• 数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是 512个字节
• 文件系统的最小单元是块，一个块的大小是 4K
• InnoDB存储引擎，有自己的最小单元，称之为页，一个页的大小是16K

1.1. innodb 的数据页查询

mysql> show  variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql>

mysql数据库中，table表中的记录都是存储在页中，那么一页可以存多少行数据？假如一行数据的大小约为1K字节，那么按 16K / 1K = 16，可以计算出一页大约能存放16条数据。如果逐条遍历，性能肯定很差，为了提升查找速度，mysql引入了B+树，先来看下B+树的存储结构:

页除了可以存放数据（叶子节点），还可以存放健值和指针（非叶子节点），当然他们是有序的。这样的数据组织形式，我们称为索引组织表。

上图中 page number=3的页，该页存放键值和指向数据页的指针，这样的页由N个键值+指针组成

1.2 B+ 树是如何检索记录？

首先找到根页，每张表的根页位置在表空间文件中是固定的，即page number=3的页,找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中
然后再去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录

2. 如何计算B+树的高度？

2.1 定位表的根页

执行下面sql,表名换成你自己想查询的那张表，下面我以service_tree表为案例，
由于我没有创建任何索引，所以只显示主键索引

SELECT
	b.NAME,
	a.NAME,
	index_id,
	type,
	a.space,
	a.PAGE_NO 
FROM
	information_schema.INNODB_SYS_INDEXES a,
	information_schema.INNODB_SYS_TABLES b 
WHERE
	a.table_id = b.table_id 
	AND a.space <> 0 
	AND b.NAME LIKE '%service_tree';

从图中可以看出，该表的主键索引的根页的page number都是3；

在根页偏移量为64的地方存放了该B+树的page level。
主键索引B+树的根页在整个表空间文件中的第3个页开始，所以算出它在文件中的偏移量：16384*3 + 64 = 49152 + 64 =49216，前2个字节中。

2.2 找到mysql的数据目录

mysql> show global variables like "%datadir%" ;
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| datadir       | /usr/local/src/mysql/mysql/data/ |
+---------------+----------------------------------+
1 row in set (0.00 sec)

mysql>

进入该目录，找到数据库文件夹，然后找到相关表的数据，其中以.frm结尾的文件是表结构数据，以.ibd结尾的文件是索引数据文件

-rw-r-----. 1 es es     67 Nov 15 02:15 db.opt
-rw-r-----. 1 es es   8815 Nov 15 02:16 service_tree.frm
-rw-r-----. 1 es es 147456 Nov 15 02:16 service_tree.ibd
-rw-r-----. 1 es es   8773 Nov 15 02:16 user.frm
-rw-r-----. 1 es es  98304 Nov 15 02:16 user.ibd
-rw-r-----. 1 es es   8618 Nov 15 02:16 user_node.frm
-rw-r-----. 1 es es  98304 Nov 15 02:16 user_node.ibd
[es@localhost service@002dtree]$ pwd
/usr/local/src/mysql/mysql/data/service@002dtree
[es@localhost service@002dtree]$

2.3 计算 page_level

[es@localhost service@002dtree]$ hexdump -s 49216 -n 10  service_tree.ibd 
000c040 0100 0000 0000 0000 2900               
000c04a
[es@localhost service@002dtree]$

通过观察 0100 发现 page_level 值是 1，那么 B+树高度为 page level + 1 = 2;

• 查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。
• 表的检索速度跟树的深度有直接关系，毕竟一次页加载就是一次IO，而磁盘IO又是比较费时间。对于一张千万级条数B+树高度为3的表与几十万级B+树高度也为3的表，其实查询效率相差不大。

3. 一棵树可以存放多少行数据？

假设B+树的深度为2

这棵B+树的存储总记录数 = 根节点指针数 * 单个叶子节点记录条数

3.1 指针数如何计算？

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节。

那么一个页中能存放多少这样的组合，就代表有多少指针，即 16384 / 14 = 1170。那么可以算出一棵高度为2 的B+树，能存放 1170 * 16 = 18720 条这样的数据记录。

同理：
高度为3的B+树可以存放的行数 = 1170 * 1170 * 16 = 21902400

千万级的数据存储只需要约3层B+树，查询数据时，每加载一页（page）代表一次IO。所以说，根据主键id索引查询约3次IO便可以找到目标结果。

3.2 实战演练

以我本地的表 service_tree为案例

• 查看表service_tree状态，查看Avg_row_length 字段

mysql> show table status like 'service_tree'\G;
*************************** 1. row ***************************
           Name: service_tree
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 574
 Avg_row_length: 142
    Data_length: 81920
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-11-15 02:16:19
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 具体服务表
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

service_tree表的行平均大小为142个字节

• 查看表设计

mysql> desc service_tree;
+----------------+-------------+------+-----+-------------------+-------+
| Field          | Type        | Null | Key | Default           | Extra |
+----------------+-------------+------+-----+-------------------+-------+
| node_id        | bigint(20)  | NO   | PRI | NULL              |       |
| parent_node_id | bigint(20)  | YES  |     | 0                 |       |
| node_name      | varchar(64) | YES  |     |                   |       |
| create_time    | datetime    | YES  |     | CURRENT_TIMESTAMP |       |
| delete_flag    | tinyint(1)  | YES  |     | 0                 |       |
+----------------+-------------+------+-----+-------------------+-------+
5 rows in set (0.00 sec)

mysql>

发现主键是bigint 所以他的字节加上指针是8+6=14

• 计算B+树的行数

• 单个叶子节点（页）中的记录数 = 16K / 142 = 115
• 非叶子节点能存放多少指针， 16384 / 14 = 1170
• 前面算出树的高度为2，可以存放的记录行数 = 1170 * 115 = 134,550