How much data can a MySQL table store?

This article brings you relevant knowledge about mysql. Programmers must deal with mysql a lot. It can be said that they are exposed to it every day, but how much data can a mysql table store? What is the basis for calculation? Let’s discuss it together, I hope it will be helpful to everyone.

1. Knowledge preparation

1.1. 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 MySQL, in order to improve throughput, 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 study, 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)

We don’t go into the specific structure pointer of our data page today. Just know that it defaults to 16kb, which means that the data size of a node is 16kb

1.2. Index structure (innodb)

We should all know the index structure of mysql, which is the following b-tree structure

Usually b-tree non-leaf nodes do not store data, only leaf nodes ( The data is stored only at the bottom layer), so let's go back to the node. A node refers to (for the above picture)

The part selected in each red box is called is a node, not an element. After understanding the concept of nodes and the size of each node being 16kb, it is much easier for us to calculate how much data mysql can store

2. Specific calculation method

2.1. Root node calculation

First we only look at the root node

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

In the data itself, there is now a small Block space is used to store the address of the next level index data page. The size is 6kb

, so we can calculate a space with data of (8b 6b=14b) (Take bigint as an example) We just mentioned that the size of a data page is 16kb, which is (16*1024)b. Then the root node can store (16*1024/(8 6)) pieces of data. The result is probably If the 1170 data can be calculated according to the node calculation method, then the next step will be easy.

2.2. 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 node, and each element of the node will extend into 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 nodes of innodb directly contain the entire mysql data. If there are many fields, the space occupied by the data is not small. Here we Calculated based on 1kb, so in the third layer, each node is 16kb, then each node can store 16 data, so the total data that mysql can store in the end is

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

In fact, the calculation results are consistent with our usual work experience. Generally, if the data in a MySQL table exceeds tens of millions, it must be divided into tables.

3. Summary

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

Recommended Learning: mysql video tutorial

The above is the detailed content of How much data can a MySQL table store?. For more information, please follow other related articles on the PHP Chinese website!