Explanation of MySQL's horizontal and vertical table partitioning

In my previous article, I said that the optimization of MySQL statements has limitations. The optimization of MySQL statements revolves around index optimization. If the index in MySQL cannot solve the problem of slow query of massive data, then horizontal and vertical table partitioning will appear (I just record my understanding)

Horizontal table:

As shown in the figure above: The other three tables have the same structure, except that the data is stored separately in these three tables. If you want to insert or query, you need to take the modulus of the id and then concatenate the table names, so that a complete table_name

But what if I need to separate the name table or the email?

Then you need to use MD5 to encrypt because the encrypted MD5 number is in hexadecimal, so you can take the remainder, the idea is the same as above.

Vertical table:

Why is vertical partitioning necessary?

Because if there is a large field in a table that does not need to be displayed or is not currently needed, then even if it is not deliberately queried, when querying based on ID or other indexes, the large field will be found together, which will seriously affect the query performance. Therefore, vertical partitioning is used.

Please see the following figure for details:

The above is an idea of ​​horizontal and vertical table division. Please point out any shortcomings. Thank you.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links