Today, I happened to read a good book on MySQL, so I want to share with everyone what I learned.
About MySQL, everyone has been using or using it for a number of projects from several users to millions of users. From blog websites to e-commerce sites, game servers …
With a small and small system, you probably don’t need to care much about how it works and how to get tired. But when the system has grown, having to handle thousands, millions of queries / s, it is extremely important to understand the mechanism of operation.
Mastering everything about MySQL is extremely difficult, but understanding something a little deeper will help us more than many people already.
So today I will introduce to you some problems that people are not yet understanding or misunderstanding about MySQL.
Target article:
・ Understand the functionality within MySQL
・ Know how to design data for the best
Target audience:
・ Someone who has worked with MySQL
・ People who want to optimize MySQL performance
1. I / O unit
When using MySQL, the majority is manipulation on records (records) of MySQL. So most of us think that data is processed in units of records. But in fact MySQL manipulates data in each page unit (page) has a predefined size.
To make it easier to understand, let’s see the image below:
Looking at the picture above, I think people can understand it. Then the smallest I / 0 unit in MySQL is not a record but a page.
With MySQL, the default page size is 16KB. (Here everyone should note 1 point is that if you want to change the size of the page, we have to delete the instance and re-create it to change, only mysql start stop cannot change it)
Because of that, AWS’s RDS doesn’t have the option to change the size of the page.
So up to now most people know what is the smallest unit of I / O in MySQL, right?
So know what this is for? Then go to the next section.
2. The great influence of AUTO_INCREMENT
Setting the primary key with AUTO_INCREMENT is sure everyone knows it. But how this setting has a great influence on the performance, let’s take a look at the 2 examples below.
To make it easier to understand, I will take an example in the game. That is to create a table to store character information (character) of the user.
Example 1:
1 2 3 4 5 6 7 | CREATE TABLE user_characters <span class="token punctuation">{</span> user_id INT NOT NULL DEFAULT 0, user_character_id INT NOT NULL DEFAULT 0, character_id INT NOT NULL DEFAULT 0, PRIMARY_KEY <span class="token punctuation">(</span> user_id, user_character_id <span class="token punctuation">)</span> , <span class="token punctuation">}</span> |
Example 2:
1 2 3 4 5 6 7 | CREATE TABLE user_characters <span class="token punctuation">{</span> <span class="token function">id</span> INT NOT NULL PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL DEFAULT 0, user_character_id INT NOT NULL DEFAULT 0, character_id INT NOT NULL DEFAULT 0, <span class="token punctuation">}</span> |
When we want to retrieve the character information that the user has ID = 4 1234 holding, we will execute the following SQL:
1 2 | SELECT * FROM user_characters WHERE user_id = 41234 |
So how is the performance when executing this SQL statement on the above two examples different?
Specifically as the image below: 
For example 1, the user_id is attached as the primary key. So the data will be sorted in order of user_id. As a result, the information retrieved from the user_characters is likely to be contained in one page. If the data is in one page, I / O will definitely be drastically reduced, and the query performance will be better.
But with example 2, it is completely different. Now our primary key is id. So the data of all characters that user_id = 41234 is holding will likely be scattered on many different pages. As a result, I / O will increase and query performance will be greatly reduced.
Therefore, with many simultaneous data usage as the above example, we try to arrange it together on one page.
Conclude
Today is like that. Other days I will add (I’m afraid to draw pictures). Hopefully this article will help everyone in the database design process for the best performance.
Have a nice weekend.
==============
To receive notifications when there are latest posts, you can like my fanpage below: