preface

Programmers usually deal with mysql must be a lot, can be said to touch every day, but mysql a table in the end how much data can be stored? What are the calculations based on? Let’s talk about them one by one

Knowledge to prepare

Data page

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

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

Today we will not go into the details of the structure of the data page, but know that it is 16KB by default, that is, the data size of a node is 16KB

Index structure (InnoDB)

Mysql index structure as we all know, is the following b+ tree structureNormally the non-leaf nodes of a B + tree do not store data, only the leaf nodes (the lowest level) store data, so let’s go back to nodes, a node refers to (for the figure above)

Each part of the red box is called a node, not an element. Now that we know 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

Let’s say we set the data type to bigint and size to 8BThere is now a small space in the data itself to store the address of the next layer of indexed data pages, which is 6KB

So we can calculate a space with data of (8b+6b=14b) (in the case of Bigint). We just said that the size of a data page is 16KB, which is (16)

1024)b, then the root node is able to store (16

1024 over (8+6)) data, so it’s about 1170 data and if you compute it with the nodes, then it’s pretty easy.

Other layer nodes are calculated

The second layer is actually easier, because the data structure of each node is the same as that of the same node, and each element in the same node extends to a node, so the data amount of the second layer is 1170*1170=1368900. The problem lies in the third layer, because innoDB leaf node directly contains the whole mysql data. If there are a lot of fields, the data takes up a lot of space. We calculate it in 1KB here, so in the third layer, each node is 16KB, so each node can hold 16 data, so the final total data that mysql can store is

1170 * 1170 * 16 = 21902400

In fact, the calculation result is consistent with our usual work experience, generally mysql a table of data more than ten million is also have to carry out table operation.

conclusion

Finally, I will summarize today’s discussion with a picture. I hope you enjoy it