If a query is considered a task, it is made up of several sub-tasks, each of which consumes a certain amount of time. To optimize a query, we essentially need to optimize its sub-tasks, either by eliminating some of them or by reducing the number of times they are executed. Generally speaking, the lifecycle of a query can be seen in the following order: from the client to the server, where it is parsed, an execution plan is generated, executed, and then results are returned to the client. Among these steps, "execution" is considered the most important phase, which involves numerous calls to the storage engine to retrieve data, as well as data processing such as sorting and grouping. These operations consume time on network latency, CPU computation, statistical information generation, execution plan creation, and lock waiting (mutex waiting), especially during data retrieval calls to the underlying storage engine. Depending on the storage engine, there could also be a lot of context switching and system calls.

1. Is Unnecessary Data Being Requested?

The most basic cause of poor query performance is the retrieval of too much data. Many poorly performing queries can be optimized by reducing the amount of data accessed. For inefficient queries, the following two steps are always effective for analysis:

1. Determine if the application is retrieving more data than necessary. This means accessing too many rows or columns.

2. Determine if the MySQL server is analyzing more rows than needed.

Some queries request more data than required, and the extra data is discarded by the application. This imposes an additional load on the MySQL server and increases network overhead (especially if the application server and database are not on the same machine). It also consumes CPU and memory resources on the application server. Typically, enterprises prohibit the use of SELECT * queries.

2. Is Extra Data Being Scanned?

Once you confirm that the query is returning only the necessary data, the next step is to check if the query is scanning too much data. For MySQL, the simplest metrics for measuring query overhead are response time, number of rows scanned, and number of rows returned. These metrics are recorded in the slow query log (SHOW VARIABLES LIKE "%slow%").

1. Response Time: This is the sum of service time and queue time. Service time refers to the actual time MySQL spends processing the query. Queue time is the time the server spends waiting for resources (e.g., waiting for I/O operations or locks). Unfortunately, the response time cannot be broken down into these components.

2. Rows Scanned and Rows Returned: When analyzing a query, the number of rows scanned is very helpful. However, not all row accesses are equally costly. Accessing shorter rows is faster, and in-memory rows are much faster to access than disk-based rows. Ideally, the number of scanned rows and the number of returned rows should be the same, but this is rarely the case. For example, in a join query, the server may need to scan multiple rows to generate a single result.

3. Scan Type and Row Access: Different types of access may require scanning more rows. MySQL offers several methods to find and return a row. Some methods may scan many rows to return a single result, while others can return results without scanning. The type column in the EXPLAIN statement reflects the scan type, ranging from full table scans to index scans, range scans, unique index lookups, and constant references. The speed increases from slow to fast as the number of scanned rows decreases.

If the query scans many rows but only returns a few, the following techniques may help optimize it:

1. Use index covering scans to include all required columns in the index, so the storage engine doesn't need to perform additional lookups.

2. Restructure the table, e.g., by using summary tables.

3. Rewrite the complex query in a way that allows the MySQL optimizer to execute it more efficiently.

3. Complex Query OR Multiple Simple Queries?

Sometimes, it’s possible to rewrite a query in a different way that produces the same results but performs better. It may also be possible to modify application code to achieve the same goal in a more efficient manner.

When designing a query, one important consideration is whether to break a complex query into multiple simpler queries. In traditional implementations, the database layer was expected to do as much work as possible because network communication, query parsing, and optimization were considered expensive. However, this is not the case with MySQL. MySQL is designed for lightweight connection and disconnection, and it can efficiently return small query results. Today’s network speeds are also much faster, and even a typical server can handle over 100,000 queries per second.

4. Breaking Queries into Smaller Ones

Sometimes for a large query, you need to "divide and conquer" by splitting the query into smaller ones. Each query performs the same function but only handles a small part, returning a small portion of the query result. Deleting old data is a good example. When deleting large amounts of data, executing a single large query may lock many records, fill up the transaction log, consume system resources, and block smaller but important queries. Breaking a large DELETE query into smaller ones can minimize the impact on MySQL performance and reduce replication delays. Deleting 10,000 rows per second is a generally efficient and low-impact practice.

5. Decomposing JOIN Queries

Many high-performance applications decompose join queries. You can query each table individually and then join the results in the application layer. For example:

SELECT * FROM teacher t
JOIN student s ON t.id = s.t_id
JOIN class c ON t.id = c.t_id
WHERE t.name = 'Li';

Can be decomposed as:

SELECT * FROM teacher t WHERE t.name = 'Li';
SELECT * FROM student s WHERE s.id = 12;
SELECT * FROM class c WHERE c.id IN (13,45,65);

The advantages of decomposing join queries are:

1. Better Caching: Many applications can easily cache single-table query results. For example, if the teacher table is cached, the application can skip the first query. Similarly, if records with IDs 12 and 45 are cached, the third query can reduce the number of IDs in the IN() clause.

2. Reduced Lock Contention: Executing individual queries reduces lock contention compared to a single large join.

3. Easier Database Sharding: By joining at the application level, it becomes easier to partition databases for better scalability and performance.

4. Improved Query Efficiency: In some cases, replacing a join with an IN() query may allow MySQL to execute the query more efficiently by scanning the IDs in order rather than performing a random join.

5. Reduced Redundant Data: Performing joins in the application means each record is only queried once, while a database join might access some data repeatedly. This approach also reduces network and memory usage.

6. Hash Join Implementation: This approach effectively implements a hash join at the application level, which can be more efficient than a nested loop join in MySQL.

6. LIMIT and UNION Limitations

MySQL cannot propagate outer LIMIT conditions to inner queries, which prevents the inner query optimizations from being applied. If you want to apply LIMIT to each part of a UNION, you need to include it in each subquery. For example, to merge two subquery results and then return the top 20 records:

-- This will scan all rows before limiting
(SELECT first_name, last_name FROM people_A ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name FROM people_B ORDER BY last_name)
LIMIT 20;

This query first stores all rows from people_A and people_B in a temporary table and then returns the top 20. To optimize this, you can apply LIMIT in both subqueries:

(SELECT first_name, last_name FROM people_A ORDER BY last_name LIMIT 20)
UNION ALL
(SELECT first_name, last_name FROM people_B ORDER BY last_name LIMIT 20)
LIMIT 20;

This reduces the temporary table size to 40 rows.

7. Optimizing COUNT() Queries

COUNT() can count non-NULL column values or row counts. When counting rows, COUNT(*) simply counts all rows, which is fast for MyISAM as it doesn't require scanning the entire table. MySQL uses the storage engine's features to directly retrieve this value without scanning the table.

For optimization:

• Use COUNT(*) for row counts in MyISAM without WHERE conditions, as it’s extremely fast.

• For large datasets, consider using summary tables or cache systems like Memcached.

8. Optimizing LIMIT for Pagination

When paginating through large datasets, using LIMIT with an offset can become costly, especially when the offset is large. For example, LIMIT 10000, 20 requires scanning 10,020 rows before discarding the first 10,000. To optimize this, you can use an index-only scan:

SELECT id, description FROM tab ORDER BY title LIMIT 10000, 20;
-- Optimized using a covering index:
SELECT f.id, f.description FROM tab f 
INNER JOIN (SELECT id FROM tab ORDER BY title LIMIT 10000, 20) t
USING(id);

This minimizes the number of pages scanned.

9. Sorting Optimization

Sorting is an expensive operation, and performance should be considered when possible. If the data fits the "sort buffer", it is sorted in memory; otherwise, MySQL uses disk-based sorting. The process is called "filesort" when no index is used.

MySQL uses in-memory quicksort when possible, and if it needs to sort data on disk, it divides the data into chunks, sorts each chunk, and then merges them.

10. Query States

To analyze query performance, it's useful to check the query status of a MySQL connection using SHOW FULL PROCESSLIST. This command shows the current state of each query in the server. Some common states include:

1. Sleep: The thread is waiting for a new client request.

2. Query: The thread is executing a query or sending results to the client.

3. Locked: The thread is waiting for a table lock.

4. Analyzing and statistics: The thread is collecting statistics and generating an execution plan.

5. Copying to tmp table [on disk]: The thread is copying results to a temporary table, usually after a GROUP BY or UNION.

6. Sorting result: The thread is sorting query results.

7. Sending data: The thread is transferring data between different states or clients.