SoFunction
Updated on 2025-04-07

MySQL statement query order, time-consuming analysis and query tuning skills sharing

In MySQL, the execution order, time-consuming analysis and query tuning of SQL query statements are important aspects of optimizing database performance. The following is a detailed explanation of these aspects.

1. The execution order of SQL query statements

Although the order in which we write SQL statements is usually

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

But the execution order of MySQL is different from that.

Here is the general execution order of MySQL:

  1. FROM: Determine the table to be queried.
  2. JOIN: If there are multiple tables, join the tables through the JOIN condition.
  3. WHERE: Filter records that do not meet the conditions.
  4. GROUP BY: Group data.
  5. HAVING: Filter the grouped data.
  6. SELECT: Select the desired column.
  7. DISTINCT: Remove heavy weight.
  8. ORDER BY: Sort the result set.
  9. LIMIT: Limit the number of rows returned.

2. Time-consuming analysis

MySQL provides a variety of tools and commands to analyze the time-consuming and performance bottlenecks of queries.

Here are some commonly used methods:

2.1 Using EXPLAIN

EXPLAINThe command can display how MySQL executes SQL queries, including the order of the query, the index used, the number of rows scanned, and other information.

Example:

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

Important fields in the output result include:

  • id: The identifier of the query, identifying the order of each subquery in the query.
  • select_type: The types of query, such as SIMPLE (simple query), PRIMARY (main query), SUBQUERY (subquery), etc.
  • table: Query table.
  • type: Access types, such as ALL (full table scan), index (index scan), range (range scan), etc.
  • possible_keys: Possible indexes.
  • key: The actual index used.
  • rows: Number of rows scanned.
  • Extra: Additional information, such as Using index, Using where, etc.

2.2 Using SHOW PROFILE

SHOW PROFILEThe command can display detailed execution information of the query, including the time-consuming time of each stage.

Example:

SET profiling = 1;
SELECT * FROM employees WHERE department_id = 5;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

2.3 Using Slow Query Log

Slow query logging execution time exceeds the specified threshold. Enable slow query logs and analyze their contents can help identify performance bottlenecks.

Configuration example:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Set the threshold for slow query to1Second

3. Query tuning skills

3.1 Using Index

Indexing is the key to improving query performance. Common index types include:

  • B-Tree Index: Suitable for most queries.
  • Hash index: Applicable to equivalent queries.
  • Full text index: Suitable for full-text search.
  • Spatial index: Suitable for geospatial data.

Example of creating an index:

CREATE INDEX idx_department_id ON employees(department_id);

3.2 Avoid full table scanning

Try to avoid full table scanning, you can use the following methods:

  • Use indexes.
  • Use appropriate query conditions.
  • Avoid function operations or operations on columns in the WHERE clause.

3.3 Optimization query statements

  • Selective query: Only select the required columns, do not useSELECT *。
  • Reduce subqueries: Try to use JOIN instead of subquery.
  • Optimize JOIN: Make sure that the join conditions use indexes and minimize the number of tables in JOIN.

3.4 Partition table

For large tables, partitioned tables can be used to improve query performance. Partitions can be performed in range, list, hash, etc.

Create a partition example:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department_id INT
) PARTITION BY RANGE (department_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN (30)
);

3.5 Using Cache

MySQL has query caching function, which can cache query results and reduce the overhead of duplicate queries.

Example of enabling query caching:

SET GLOBAL query_cache_size = 1048576;  -- Set the cache size to1MB
SET GLOBAL query_cache_type = ON;

It should be noted that the query caching function has been removed in MySQL 8.0 and above, and it is recommended to use application-layer caching or other caching mechanisms (such as Redis).

3.6 Adjust server configuration

Adjust the configuration parameters of the MySQL server according to actual needs, such asinnodb_buffer_pool_size、query_cache_size、tmp_table_sizeetc., which can significantly improve performance.

Summarize

By understanding the execution order of MySQL queries, using time-consuming analysis tools, and applying query tuning techniques, the query performance of the database can be significantly improved. Regular performance analysis and optimization can ensure that the database can still run efficiently under high loads.

The above is personal experience. I hope you can give you a reference and I hope you can support me more.