How much data can be stored in a MySQL table?

Programmers must deal with MySQL a lot, and it can be said that they come into contact with it every day, but how much data can a MySQL table store? What is the basis for calculation? Next, let’s discuss them one by one

Knowledge Preparation

Data Page

In the operating system, we know that in order to interact with the disk, the memory is also paged, with a page size of 4KB. Similarly, in order to improve throughput in MySQL, data is also paged, but the data page size of MySQL is 16KB. (To be exact, the InnoDB data page size is 16KB). For detailed learning, please refer to the official website. We can query it with the following command.

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

Today we will not delve into the specific structure pointer of the data page, just know that it defaults to 16kb, which means that the data size of a node is 16kb.

Index structure (innodb)

We all know the index structure of MySQL, which is the following b+ tree structure

Usually, non-leaf nodes of a b+ tree do not store data. Only leaf nodes (the bottom layer) store data. So let's talk about nodes. A node refers to (for the above figure)

Each part selected by a red box is called a node, rather than an element. After understanding the concept of nodes and the size of each node is 16kb, it is much easier to calculate how much data MySQL can store.

Specific calculation method

Root node calculation

First, let's just look at the root node

For example, the data type we set is bigint, and the size is 8b

There is now a small space in the data itself, which is used to store the address of the next level index data page, with a size of 6kb

So we can calculate a space of (8b+6b=14b) data (taking bigint as an example). We just said that the size of a data page is 16kb, which is (16 1024)b, so the root node can store (16 1024/(8+6)) data, which is about 1170 data. If it is calculated with the node calculation method, then the next step will be easy.

Calculation of nodes in other layers

The second layer is actually easier, because the data structure of each node is the same as that of the root node, and each element of the root node will extend to a node, so the amount of data in the second layer is 1170*1170=1368900. The problem lies in the third layer, because the leaf node of InnoDB directly contains the entire MySQL data. If there are many fields, the space occupied by the data is not small. We calculate it as 1kb here, so in the third layer, each node is 16kb, then each node can hold 16 data, so the total data that MySQL can store in the end is

1170 * 1170 * 16 = 21902400 (tens of millions)

In fact, the calculation results are consistent with our usual work experience. Generally, if the data in a MySQL table exceeds 10 million, it has to be split into tables.

Summarize

Finally, I would like to use a picture to summarize what we discussed today. I hope you like it.

This is the end of this article about how much data a MySQL table can store. For more information about how much data a MySQL table can store, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!