preface

SQL as a Web development is always a topic, write SQL every day, but do you know how a SQL is executed?

select name from user where id = 1;
Copy the code

Above is a simple query statement that the database executes and returns name. It seems simple enough, but the internal implementation process is unknown to many people.

Take MySQL apart today to see how it works.

SQL Basic Architecture

As can be seen from the figure above, MySQL is divided into Server layer and storage engine layer

Server layer

The connector

Connectors are mainly used to establish connections with clients, including local sockets and most TCP/IP-like communication based on client/server tools. After the connection is successful, the user’s permission will be verified at the same time, and other relevant security schemes. As we commonly used to establish a connection

mysql -h ip -P 3306 -u root -p
Copy the code

You can enter the password after -p, but this operation is not recommended for security reasons. -p is the port number and -p is the password. Pay attention to case.

After a successful login, the permissions of the registered account are verified. All subsequent database operations are restricted by the current permission. Therefore, the modification of user rights by the administrator does not take effect immediately. The modification takes effect only after the user is reconnected.

MySQL by default disconnects automatically when a link has been idle for more than 8 (60 * 60 * 8) hours. However, connection pooling assumes that disconnected connections are still valid. At this point, if the client code sends a request, the connection pool will return the invalid code to the client. This will cause code exceptions.

You can view the show global variables like ‘%timeout%’. By default, wait_timeout is used.

You can also use show processList; Used to show which thread the user is running.

Note: With the exception of the root user who can see all running threads, other users can only see their own running threads, not other users’ running threads. Unless a single user has PROCESS privileges.

mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 4 event_scheduler | localhost | NULL | Daemon | 461 | Waiting on empty queue | NULL | | 13 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+Copy the code

The query cache

When the connection is established, the SQL statement is executed with a cached query (if enabled). If the same SQL statement was executed earlier, the result is returned directly from the cache. This process can be understood as a mapping of SQL text to query results.

But does query caching really improve efficiency? Theoretically, it is not recommended to enable query caching

Because both caching and invalidation have additional resource consumption, cache invalidation can occur when data changes or table structures change. The worst case scenario is that you have just created a cache and someone else is modifying the data on the other side. This invalidates the cache and creates a new cache.

** Having these inserts, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE invalidates cached data. So the query cache is suitable for applications with a large number of the same queries, not for applications with a large number of data updates **

The query cache feature was removed in MySQL8.0.

If you are pre-8.0, you can turn off the query cache by:

1. Close the command temporarily and run the command directly

set global query_cache_size=0

set global query_cache_type=0
Copy the code

2, permanently close, modify the configuration file my.cnf, add the following configuration.

query_cache_type=0

query_cache_size=0
Copy the code

analyzer

When the query result of the object is not found in the query cache, the parser is required to parse the SQL. Such as parsing out the key words of the response. Such as Select (query), Delete (Delete), and so on, but also the corresponding representation, field name are analyzed. You have an error in your SQL syntax

The optimizer

The actual order in which SQL is executed is not necessarily the order in which we write it. By parsing the parser, the database knows what to do. The SQL is then rewritten according to certain rules. When there are multiple indexes, the optimizer also decides which index to use; When multiple tables are associated with a query, the link order of each table is determined. In summary, the optimizer uses a set of algorithmic rules to give an optimal execution strategy.

actuator

SQL knows what to do through the parser, and it knows how to do it through the optimizer. Finally, it enters the execution stage through the actuator.

First, the system checks whether it has the permission to operate the table based on the connected account. If not, a permission error is returned. If yes, continue the operation.

When a table is opened, the actuator uses the interface provided by the table engine.

Storage engine layer

The storage engine layer is responsible for storing and extracting data.

You can run show Engines to check the storage engine of MySQL. Storage engines include InnoDB, MylSAM, MEMORY, MERGE, etc..

But the basic ones we use are InnoDB and MylSAM.

InnoDB is the default storage engine since version 5.5.5

InnoDB

InnoDB is a transactional storage engine with row-level locking and foreign key constraints, providing transaction security with commit, rollback and crash recovery, but writing is less efficient than MyLSAM and takes up more disk space to hold data and indexes.

Features:

  1. Tables with multiple updates are suitable for handling multiple concurrent update requests.

  2. Support transactions.

  3. You can recover from disasters (through bin-log, etc.).

  4. Foreign key constraints. Only he supports foreign keys.

  5. Supports automatic increment of column attributes auto_INCREMENT.

MylSAM

The Mylsam storage engine is operating system independent and can be used on Windows as well as moving data to Lunex. System compatibility is good!! . When the storage engine builds a table, it creates three files. .frm stores the definition of a table,.myd stores the data in a table, and.myd stores the index. The operating system is slow to handle large files, so the table is divided into three files, then. MYD is a separate file to store data naturally can optimize database queries and other operations.

Features:

1. Transactions are not supported

2. Foreign keys are not supported

3, the query speed is very fast. If the database insert and update operations are a lot of table locking is inefficient (innoDB is recommended).

4. Lock the table

conclusion

The Server layer covers most of the core functions performed by MySQL, as well as a variety of built-in functions such as time, date, etc. Its Server layer is the same regardless of the storage engine used. That is a brief introduction to an SQL execution process. Thanks for reading!

At the end of the article’s welfare

Liver the whole network, 43 Copies of Java mind map, need to take!! !

Java Interview Manual V1.0, free in hd PDF