Why is the query speed slow

1. Slow refers to the long response time of a query. The process of a query:

  • The client sends a query to the server
  • The server checks the query cache first and returns the result stored in the cache immediately if a hit is made. Otherwise move on to the next level
  • SQL is parsed and preprocessed on the server, and then the optimizer generates the corresponding execution plan.
  • MySQL invokes the storage engine API to execute the query based on the execution plan generated by the optimizer.
  • Returns the result to the client

2. Data access

  • Whether unnecessary data is requested from the database
  • Whether to scan additional records

3. Query methods

  • One complex query or multiple simple queries
  • Shard query (split a large query into smaller queries and loop through smaller queries)
  • Decompose associated query

Slow query analysis

The problem of SQL

Divide a complex SQL into multiple simple SQL and execute it. It is slow to see which field is involved.

EXPLAIN

Shows the execution plan for how SQL uses the index.

Execution plan parameters:

Table shows which table the row is about

Type Displays what type of connection is used. The best to worst connection types are const, eq_reg, ref, range, Indexhe, and ALL

Possible_keys displays indexes that could be applied to the table. If empty, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain

Key Indicates the actual index used. If NULL, no index is used. In rare cases, MYSQL will select an index that is underoptimized. In this case, USE INDEX (indexName) in the SELECT statement to force the USE of an INDEX or IGNORE INDEX (indexName) to force MYSQL to IGNORE the INDEX

Key_len Specifies the length of the index used. With no loss of accuracy, the shorter the length, the better

Ref shows which column of the index is used and, if possible, is a constant

Rows Number of rows scanned for requested data

Extra Information about how MYSQL parses queries

PROFILE

Displays information about system resource consumption by SQL execution.

Query execution process

MySQL client/server communication protocol is “half duplex”. Both client and server can send data to each other, but not simultaneously. So we can’t and don’t have to cut a message into small pieces to send independently. This protocol does not allow for flow control. The size of packets the client sends requests is limited by the parameter max_allowed_packet. If the query is too large, the server rejects more data and throws an error. Multiple packets returned by the server must be fully accepted by the client.

1. Query the status SHOW FULL PROCESSLIST

mysql>SHOW FULL PROCESSLIST;
    Id  User    Host             db            Command    Time  State   Info                   
------  ------  ---------------  ------------  -------  ------  ------  -----------------------
     1  root    localhost:61316  laravel_blog  Query         0  (NULL)  show FULL processlist  
     2  root    localhost:61319  (NULL)        Sleep        94          (NULL)
Copy the code

For a connection, or a thread, there is a state at any given moment that represents what MySQL is currently doing.

2. Query the cache

Mysql > show variables like '%query_cache%'; mysql> show variables like '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 20971520 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+Copy the code

Check whether the SQL hit the cache. A hit is returned after checking the user’s permissions once, which is done through a case-sensitive hash lookup. A single byte difference between two queries will fail. Otherwise it will move to the next stage.

When there is uncertain data in SQL, it will not be cached. For example, the query results of user-defined functions, storage functions, user variables, temporary tables, and system tables in the mysql library are not cached.

3. Query optimization

Syntax parser and preprocessing

MySQL parses SQL statements by keyword and generates a corresponding parse tree. The procedure parser validates and parses mainly through syntax rules. For example, whether the SQL is using the wrong keywords or whether the keywords are in the correct order. Preprocessing further checks whether the parse tree is valid based on MySQL rules. For example, check whether the tables and columns to be queried exist.

Query optimizer

The syntax tree generated by the previous steps is considered valid and is turned into a query plan by the optimizer. In most cases, a query can be executed in many ways, all of which return the corresponding result. The optimizer’s job is to find the best execution plan.

MySQL uses a cost-based optimizer to select the smallest one by calculating the cost. Run the SHOW STATUS LIKE ‘Last_query_cost’ command; Look at costs. The smallest unit of cost is the cost of randomly reading a page of 4K data.

MySQL’s query optimizer is a very complex part that uses a number of optimization strategies to generate an optimal execution plan:

      • Redefine the order of associated tables
      • Convert an outer join to an inner join
      • Use the equivalence transformation rule
      • Optimize count (), min (), Max ()
      • Estimate and convert to a constant expression
      • Overwrite index description
      • Subquery optimization
      • Early termination of query
      • Equivalent transmission
      • Comparison of the list IN ()

As MySQL continues to evolve, optimization strategies used by the optimizer continue to evolve.

Query execution engine

After completing the parsing and optimization phase, MySQL will generate the corresponding execution plan, and the query execution engine will execute the results step by step according to the instructions given by the execution plan. Most of the execution is done by calling interfaces implemented by the storage engine, known as the Handler API.

Each table in the query is represented by a handler instance. In fact, MySQL creates a handler instance for each table during the query optimization phase. The optimizer can obtain information about the table based on the interface of these instances, including all column names of the table, index statistics, etc. The storage engine interface provides a lot of functionality, but there are only a few dozen interfaces underneath that do most of the work of a query like building blocks.

Returns the result

The final stage of query execution is to return the results to the client. Even if no data is retrieved, MySQL still returns information about the query, such as the number of rows affected by the query and the execution time.

If the query cache is enabled and the query can be cached, MySQL will also store the results in the cache.

The result set return to the client is an incremental and gradual return process. It is possible that MySQL will start gradually returning the result set to the client as soon as the first result is generated. In this way, the server does not need to store too many results and consume too much memory, and the client can get the results in the first time. Note that each row in the result set will be sent as a packet that meets the communication protocol described in ①, and then transmitted through TCP. During transmission, MySQL packets may be cached and sent in batches.

2021PHP gold nine silver ten big factory interview real questions collection, must see!

Four years of PHP technical articles collation collection – PHP framework

A collection of four years’ worth of PHP technical articles – Microservices Architecture

Distributed Architecture is a four-year collection of PHP technical articles

Four years of PHP technical essays – High Concurrency scenarios

Four years of elite PHP technical article collation collection – database