Geektime – Lin Xiaobin 2018-11-14

MySQL is roughly divided into server layer and storage engine layer.

The Server layer includes connectors, query cache, analyzers, optimizers, actuators, etc. It covers most of the core service functions of MySQLl, as well as all the built-in functions (such as date, time, math and encryption functions, etc.). All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, attempts, etc.

Storage engines are responsible for storing and extracting data. Its architecture mode is plug-in, supporting InnoDB, MylSAM, Memory and other storage engines. The most commonly used storage engine is InnoDB, which has been the default since MySQL5.5.5.

The connector

Step 1: Establish a connection to the database. Connectors establish connections to clients, obtain permissions, and maintain and manage connections.

Mysql -h$IP -p $port -u$user -pCopy the code

If the client does not request the connection for a long time after the connection is established, the connector is automatically disconnected. This time is controlled by the wait_timeout parameter, which defaults to 8 hours.

In the database, a long connection means that the same connection is used all the time if the client continues to have requests after a successful connection. Short connection means that the database is disconnected after a few operations, and a new connection is established the next time.

The process of establishing a connection is relatively complex, so minimize the action of establishing a connection in use, that is, use a long connection as far as possible.

After using all long connections, you may find that MySQL memory usage increases very quickly in some cases, because the memory temporarily used by MySQL during execution is managed in connection objects. These resources are released when the connection is disconnected. If long connections are accumulated, the system may use too much memory and kill the MySQL server forcibly (OOM). In this case, MySQL restarts abnormally.

Solution:

  1. Disconnect long connections periodically. Use for a period of time, or in the program to determine the execution of a large memory – consuming query, disconnect, then query and reconnect.

  2. MySQL 5.7 and later can be executed after each large operation

    mysql_reset_connection
Copy the code

    To reinitialize the connection resource. This process does not require reconnection and re-authentication of permissions, but restores the connection to the shipboard state.

The query cache

Step 2: Query the cache

When MySQL gets a query request, it goes to the query cache to see if the query has been executed before. The machine results of previously executed statements may be directly cached in memory as key-value pairs. Key is the query statement and value is the query result. If your query finds the key directly in the cache, the value will be returned directly to the client.

But do not use query caching in most cases, as it does more harm than good

The query cache invalidates so frequently that whenever a table is updated, the query cache on that table is emptied. Maintained caches are mostly cleared before they are used.

You can set the query cache to “on demand.” Set the query_cache_type parameter to DEMAND so that the default SQL does not use caching. For SQL statements that use caching, you can use SQL_CASHE to display the specification

select SQL_CACHE * FROM T WHERE ID = 10
Copy the code

* After MySQL 8.0, the query cache module is removed.

analyzer

Step 3: SQL statement parsing

The parser first does a “lexical analysis”. Identify what SQL strings represent.

The optimizer

Step 4: Perform prior optimization processing

When there are multiple indexes in a table, determine which index to use. Or when a multi-table join statement is used to determine the order in which each table is joined.

actuator

Step 5: Start executing

Check whether you have permission to execute the table before executing it. If you do not have permission, an error will be returned. If you have permission, open the table and continue. When a table is opened, the executor uses the interface provided by the table engine according to its definition.