Tell the girl next door, “how does an SQL statement execute?”

Time:2021-8-20

preface

SQL, as a topic of web development, will never leave. Write SQL every day, but do you know how SQL is executed?

select name from user where id = 1;

The above is a simple query statement, which is handed over to the database for execution, and then returns name. It seems very simple, but many people don’t know the internal implementation process.

Today, take MySQL apart to see how it works.

SQL basic architecture

Tell the girl next door,

As can be seen from the above figure, MySQL is divided intoServerLayer andStorage engine tier

Server layer

Connector

The connector is mainly used to establish a connection with the client, including local socket and most TCP / IP communication based on client / server tools. After the connection is successful, the user’s permissions and other related security schemes will be verified at the same time. Such as our common connection method

mysql -h ip -P 3306 -u root -p

For connection, you can enter a password after – P, but it is not recommended to do so in consideration of security issues. After – P is the port number and – P is the password. Pay attention to case.

After the login is successful, the permission of the login account will be verified. All subsequent database operations are limited by the current permissions.Therefore, when the administrator modifies user permissions, it will not take effect immediately and will take effect only after reconnection.

MySQL by default, when a link is idle for more than 8 (60) 60 8) Automatically disconnect after hours. However, the connection pool thinks that the disconnected connection is still valid. At this time, if the client code sends a request, the connection pool will return the invalid code to the client. This will cause code exceptions.

adoptshow global variables like '%timeout%'You can view it. By default, wait is used_ The timeout field.

In addition, you can useshow processlist;Used to display the threads that the user is running.

Note: except that the root user can see all running threads, other users can only see their own running threads and can’t see the running threads of other users. Unless a single user is given process permission.

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 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

Query cache

After the connection is established and the SQL statement is executed, the cache query will be performed first (if the cache query is enabled). If the same SQL statement has been executed before, the result will be returned directly from the cache. This process can be understood as the mapping between SQL text and query results.

But can query caching really improve efficiency? In theory, it is not recommended to enable query caching

Because caching and invalidation will have additional resource consumption, cache invalidation will be caused when data changes or table structure changes. The worst case is that you just set up a cache and someone modifies the data on the other side. This will invalidate the cache and create a new cache.

These insert, update, delete, truncate, alter table, drop table, or drop database will invalidate the cached data. Therefore, query cache is suitable for applications with a large number of the same queries, not for applications with a large number of data updates

In MySQL 8.0, the query cache function is deleted.

If you are a version before 8.0, you can turn off the query cache by:

1. Temporarily close and execute the command line directly

set global query_cache_size=0

set global query_cache_type=0

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

query_cache_type=0

query_cache_size=0

analyzer

When the query result of the object is not found in the query cache, the analyzer needs to parse the SQL. For example, analyze the keywords of the response. For example, select, delete and so on. At the same time, the corresponding indication and field names will be analyzed. If the SQL syntax is wrong, it will tell us You have an error in your SQL syntax

optimizer

The actual execution order of SQL is not necessarily the order we write. Through the analysis of the analyzer, the database knows what we want to do. Then the SQL will be rewritten according to certain rules. When there are multiple indexes, the optimizer will also decide which index to use; When multiple tables are associated with a query, the link order of each table will also be determined. In short, the optimizer will give an optimal execution strategy through a series of algorithm rules.

Actuator

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

First, check whether you have permission to operate the table according to the connected account. If not, a permission error is returned. If you have permission, continue.

When opening a table, the executor will use the interface provided by the engine according to the table engine.

Storage engine tier

The storage engine layer is responsible for data storage and extraction.

Can passshow enginesView the MySQL storage engine. The storage engine has InnoDB MylSAM MEMORY MERGE wait..

Tell the girl next door,

But what we often use isInnoDBandMylSAM

InnoDB is the default storage engine after version 5.5.5

InnoDB

InnoDB is aTransactionalYour storage engine,There are row level locking and foreign key constraints, which provide transaction security with commit, rollback and crash recovery. However, compared with mylsam engine, the write efficiency is lower, and it will occupy more disk space to maintain data and indexes.

characteristic:

  1. Update multiple tables, suitable for handling multiple concurrent update requests.
  2. Support transactions.
  3. You can recover from a disaster (through bin log logs, etc.).
  4. Foreign key constraints. Only he supports foreign keys.
  5. Support auto adding column attribute_ increment。

MylSAM

Mylsam storage engine is independent of the operating system. In short, it can be used on windows or transfer data to lunex operating system. System compatibility is very good!!!. This storage engine creates three files when creating tables. Respectively(.frm, .MYD, .MYI)For a brief explanation,. Frm stores the definition of the table (that is, the table structure),. MyD is the data in the table, and. MyD stores the index. In this way, the operation of the operating system on large files is relatively slow. In this way, the table is divided into three files, so the. MyD file is used to store data separately, which can naturally optimize the query and other operations of the database.

characteristic:

1. Transaction not supported

2. Foreign keys are not supported

3. Query speed is very fast. If there are many database insert and update operations, it is inefficient to use table locking (InnoDB is recommended).

4. Lock the table

summary

The server layer covers most of the core functions executed by mysql, as well as various built-in functions, such as time, date, etc. no matter what storage engine is used, its server layer is the same. The above is a brief introduction to an SQL execution process. Thank you for reading!

Welfare at the end of the article

Liver the whole network, 43 Java mind maps, you need to take them yourself!!!

Java interview manual v1.0, HD PDF, available for free