How much data can be stored in a MySQL table?

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!

You may also be interested in:
  • Use pymysql to query the database, save the results as a list and get the specified element index instance
  • Analysis of how to create a stored procedure in MySQL to add new fields to a data table
  • Django uses the existing data table method of Mysql database
  • MySQL 3 ways to modify the data table storage engine
  • MySQL dynamically creates tables and stores data in tables

<<:  react+antd.3x implements ip input box

>>:  How to install Windows Server 2008 R2 on Dell R720 server

Recommend

Solution to forgetting the MYSQL database password under MAC

Quick solution for forgetting MYSQL database pass...

Tutorial on installing Microsoft TrueType fonts on Ubuntu-based distributions

If you open some Microsoft documents with LibreOf...

MySQL transaction concepts and usage in-depth explanation

Table of contents The concept of affairs The stat...

Springboot+Vue-Cropper realizes the effect of avatar cutting and uploading

Use the Vue-Cropper component to upload avatars. ...

Detailed tutorial on installing Anaconda3 on Ubuntu 18.04

Anaconda refers to an open source Python distribu...

jQuery achieves full screen scrolling effect

This article example shares the specific code of ...

Example of implementing TikTok text shaking effect with CSS

In daily development, front-end students often ar...

Detailed steps to install mysql 8.0.18-winx64 on win10

1. First go to the official website to download t...

Concat() of combined fields in MySQL

Table of contents 1. Introduction 2. Main text 2....

A brief understanding of the difference between MySQL union all and union

Union is a union operation on the data, excluding...

Vue implements a small weather forecast application

This is a website I imitated when I was self-stud...

Problems and solutions for MYSQL5.7.17 connection failure under MAC

The problem that MYSQL5.7.17 cannot connect under...

An example of refactoring a jigsaw puzzle game using vue3

Preface It took two days to reconstruct a puzzle ...

Python3.6-MySql insert file path, the solution to lose the backslash

As shown below: As shown above, just replace it. ...

JS ES6 asynchronous solution

Table of contents Initially using the callback fu...