SQL "explain" a programmer's perspective

MySQL’s explain statement is commonly used to analyze the cause of slow SQL and returns the following columns:

select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra.

The most important fields are type, rows, key and extra.

Logical Architecture of MySQL

Let’s start with the basics, the logical architecture of MySQL.

Client -> (Query Cache, 5.7.20 deprecated, 8.0 removed) -> Parser (generates parse trees) -> Preprocessor (generates new parse trees) -> Query Optimizer (generates query plans) -> Execution Engine (execution scheduling) -> Storage Engine (data lookup) -> Execution Engine (data filtering, sorting, etc.) -> Client and Query Cache

“type”, “rows” and “key” fields

The “type” field is used to indicate the type of connection. Connection types can be classified from fast to slow as follows:

1. const, system: up to one matching row, using a primary key or unique index

2. eq_ref: returns a row of data, usually found in join, using a primary key or a unique index

3. ref: uses the leftmost prefix of the key, and the key is not a primary or unique key

4. range: index range scan, the scan of the index starts at a certain point and returns the matching row(s)

5. index: full table scan in the order of the index, the advantage is that there is no sorting, the disadvantage is that the whole table has to be scanned

6. all: full table scan

The “rows” field is MySQL’s estimate of the number of rows needed to be scanned to find the desired record, and is used as a reference for the query optimizer to select keys.

For types of “index” and “all”, the number of “rows” is usually very large for a full table scan. So “index” and “all” types should be avoided in an online environment.

The “key” field indicates which index is used, if no index is used, it is null.

The “index” type is actually quite confusing. It is index, but in fact there is no query using indices at all, just sorting with indices on the basis of a full table scan.

“extra”

The “extra” field is used to indicate additional important information. They can be classified from fast to slow as follows:

1. using index: index override, the query only uses the index, no need to read the data block

2. using where: filtering after the storage engine returns the records

3. using temporary: using temporary tables, usually seen when GROUP BY, ORDER BY appearing

4. using filesort: using non-index-ordered additional sorting, occurs when ORDER BY does not use indices (may be in-memory sorting, may also be external sorting)

In Online environments, “using temporary” and “using filesort” should be avoided.

Best practice to debug a slow SQL

1. Firstly, look at the “key” field, see whether indices are used.

2. Then look at the “type” field, it should not be “index” or “all”.

3. Finally, look at the “extra” field, it cannot be “using temporary” or “using filesort”.