For details about the official documentation of mysql, see the official documentation.

Mysql SQL execution flowchart

Establish a connection

By default, the mysql server listens on port 3306. Clients can use different protocols to establish connections with the server (common protocols include TCP and Unix socket).

Viewing Connection Details
  • The command
show global status like 'Thread%';
Copy the code
  • Query results and explanations
field meaning
Threads cached Number of thread connections in the cache
Threads connected Number of connections currently open
Threads created Number of threads created to process connections
Threads running The number of connections in a non-sleeping state, usually concurrent connections

Each time a client creates a connection or session, a thread is created on the server to process the connection. Mysql check status, connection count, thread count, and use and optimization of Mysql performance monitoring tool doDBA.

Key Connection Parameters
  • The default timeout period is 28800 seconds, 8 hours
-- Non-interactive timeout times, such as for JDBC programs
show global variables like 'wait timeout';
Interactive timeouts, such as database tools
show global variables like 'interactive timeout';
Copy the code
  • Maximum number of connections: The default is 151 in version 5.7, and the maximum can be set to 100000
show variables like 'max connections';
Copy the code

The query cache

Mysql cache query is disabled by default in version 5.7, and deprecated after version 8.+.

  • The first is that it requires the SQL statement to be identical, with an extra space in the middle, and to be considered different depending on the case of the letter.
  • The second is that when any data in the table changes, all the caches in the table will be invalidated, so it is not suitable for applications with a large number of data updates.

The parser

Syntax parsing

Break down SQL statements into words

Lexical Parsing (Parser)

MySQL > create a parse tree based on the SQL statement as shown in the following figure:

Preprocessor

The preprocessor checks the generated parse tree to resolve semantics that the parser cannot resolve. For example, it checks for table and column names, names and aliases to make sure there is no ambiguity.

The result of preprocessor processing is a new parse tree.

The Optimizer (Optimizer)

An SQL statement can be executed in many ways and ultimately return the same result. They are equivalent. The purpose of the optimizer is to generate different Execution plans based on the parse tree, and then select the best Execution Plan. MySQL uses a cost-based optimizer, and selects the least expensive Execution Plan (this is also why it is sometimes impossible to determine whether a query is indexed from SQL).

  • You can use this command to see the cost of the query:
show status like 'Last query cost';
Copy the code

The optimizer mainly gets the execution plan after processing.

MySQL provides a tool to execute a plan. We can see the execution plan by prefacing the SQL statement with EXPLAIN, for example:

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

The storage engine

Storage engines store data. Storage engines provide standard apis for actuators to operate data. So mysql’s storage engine is dynamically selected.

The storage engine stores file details
  • Check the directory where the mysql database is stored (each database is a folder)
show variables like 'datadir';
Copy the code

Different storage engine files can be stored in the following modes:

  • Different storage engines store files
    • FRM stores the file of table structure definition, which every storage engine has
    • Different storage engines store data in different ways and produce different files
      • Innodb has only one data file
      • Memory is stored in memory, so there are no files on disk
      • Myisam isa binary file, table_name.MYD holds data, table_name.MYI holds index
Comparisons between storage engines
  • MylSAM (3 files)

The scope of application is relatively small. Table-level locking limits read/write performance and is therefore commonly used for read-only or read-heavy work in Web and data warehouse configurations. Features:

  • Table level locks are supported (inserts and updates lock tables).
  • Transactions are not supported. High insert and select speed.
  • The number of rows of the table is stored (count is faster).

How do I quickly insert 1 million pieces of data into the database? There is a way to insert data with MylSAM and then change the storage engine to InnoDB. Good for: read-only data analysis projects.

-InnoDB (2 files) Default storage engine in mysql 5.7. InnoDB is a transaction-safe (ACID-compatible) MySQL storage engine with commit, rollback, and crash recovery capabilities to protect user data. InnoDB row-level locking (not upgraded to coarse-grained locking) and Consistent, oracle-style non-locking reads improve multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports foreign key reference integrity constraints. Features:

  • Support transactions
  • Foreign keys are supported, ensuring data integrity and consistency.
  • Row-level and table-level locks are supported
  • Support read/write concurrent write without blocking read (MVCC)
  • The special index storage method reduces I/O and improves query efficiency

Please refer to:Dev.mysql.com/doc/refman/… After 5.5.5, mysql’s default storage engine was InnoDB

Actuator (Execution engine)

The execution engine uses the corresponding API provided by the storage engine to complete the operation, and returns the processing data returned by the storage engine to the client

conclusion

MySQL is divided into three layers: the connection layer that connects to clients, the service layer that actually performs operations, and the storage engine layer that deals with hardware.

  • The connection layer

To connect to MySQL server port 3306, our client must establish a connection with the server, manage all connections, and verify the identity and permissions of the client. These functions are completed in the connection layer.

  • The service layer

The connection layer will hand over the SQL statement to the service layer, which contains a series of processes: such as the judgment of the query cache, call the corresponding interface according to the SQL, and parse our SQL statement lexicographical and linguistic (such as how to identify keywords, how to identify aliases, syntax errors, etc.). Then there is the optimizer, MySQL bottom will optimize our SQL statement according to certain rules, and finally give the executor to execute.

  • The storage engine

The storage engine is where our data is actually stored, and it supports different storage engines in MySQL. Then you have memory or disk.