MySQL is probably the most popular database in the world today and probably for many years to come. Many of us have been working with MySQL but haven’t really maximized the power of this “national” database.

In the first part of the MySQL optimization series, I will introduce the architecture of MySQL, because only when we understand the “interior” inside can we easily “penetrate”, right? Let’s go.

MySQL’s Logical Architecture

As you can see in the picture, MySQL’s logical architecture consists of three main layers.

• The top layer shows the services that MySQL provides for client / server networks including connection handling, authentication, and security.
• The middle layer, which is the processing center of MySQL, includes query analysis, optimization, caching, and functions available in MySQL.
• The bottom layer includes the storage engine responsible for storing and retrieving data. The current popular storage engines of MySQL are InnoDB and MyISAM.

Connection Management and Security

Each client connection to the MySQL server will have a thread with the same process as the server process.

When the client connects to the MySQL server, the server will need to authenticate them via username, password, and host. We can authenticate with the server through the command

Optimization and Execution

MySQL will parse the completed query into a query tree and then apply various optimizations. This is an example of analytical tree of MySQL.

The ways MySQL uses to optimize a query can be to rewrite the query, select an index to use, and determine the execution order of the query. Thus, MySQL has assisted us in optimizing how to run a partial query. To see how MySQL we can use the keyword EXPLAIN . Optimizers don’t really care about the storage engine that the table uses. But with each different storage engine will affect different process of query optimization. For example, each storage engine will provide their own index types, not all storage engines will provide B-Tree Index, Hash Index.

Concurrency Control

Whenever there is more than one query that needs to be changed at the same time, the problem of concurrency will occur. MySQL will handle this problem at two levels: server and storage engine.

Read / Write Locks

This is the simplest way to handle concurrency. Only one client can write at a time, but the other client only has read permission. But such a lock will lead to inadequacies, because it is possible that clients can interact with different records and tables, so instead of locking the entire resource, we can lock certain data. The two most common lock mechanisms that solve this problem are Table locks and Row locks .

Table locks

When the client needs a change to the table (insert, update, delete), the whole table will be locked. MyISAM uses this locking mechanism.

Row locks

When a client needs a change to a specific record only that record is locked. InnoDB uses this locking mechanism.

Transaction

This is a concept that a lot of you give examples that confuse locking.

Transaction in MySQL is a set of queries, but it is considered as a unit of work. A transaction succeeds when all of its queries are successful, any failure that results in the transaction being rolled back. Transactions also need to ensure the ACID properties are:

• Atomicity: A transaction has been treated as a unit of work, so there is no such thing as partial success, success must be successful.
• Consistency: A transaction needs to ensure the integrity of the data even if the transaction succeeds or fails
• Isolcation: The result of the transaction is only shown when the transaction is completed.
• Durability: Once it has been commited, changes to transactions will be permanent.

Storage engines

MySQL has 2 popular storage mechanisms which are InnoDB and MyISAM. So why are these two mechanisms popular? Because these two simple storage mechanisms give users many features such as indexing, query optimization, …

InnoDB

Overview

Is the default data storage mechanism for MySQL. There is one piece of advice that ” You should use InnoDB for your tables unless you have a compiling need to use a different engine ” to speak of the popularity and usefulness of this storage mechanism. InnoDB stores data as a series of one or more files and is known as tablespace . Since MySQL 4.1, InnoDB can store each table and index table in separate files. If you are curious about this, turn on the terminal and type cd var/lib/mysql or where you have configured the data so it will show the .frm , .ibd files. Those are the files that contain our main data.

InnoDB uses MVCC mechanism to achieve high synchronization, InnoDB’s indexing method is also different from other MySQL storage mechanisms. I will present this problem in detail in the article about indexing.

MyISAM

Overview

Similar to InnoDB, MyISAM also stores data under files such as .myd , .myi . As I mentioned, MyISAM uses table locking mechanism. That is the biggest weakness of this storage mechanism. But MyISAm also has many internal highlights such as full text index support, compression, and repair mechanism. You can learn more about these outstanding features through the MyISAM doc

summary

Thus, in this article, I have summarized the basic knowledge of architecture, outstanding features, and storage mechanisms of MySQL. Hopefully, you will take the time to read the next articles in this series. Happy coding.

Refer

http://shop.oreilly.com/product/0636920022343.do

Updated

I have finished part 2 of this series. We look forward to your continued support of Optimizing Schema and Data Types