MySQL explain parsing SQL statements

Given what has been done recently, it is necessary to solve the problem of slow SQL, now add a little bit of SQL statement performance analysis of the explain !

Comprehensive return data situation, analysis of the parameters, you can understand the SQL

How to use: Explain + SQL statements

Such as: Explain select ID as Paid,userid from patientattachments where ID =1000 ORDER by ctime desc limit 10;

The meaning of each property

The serial number of the ID select query

The type of select_type Select query is mainly the difference between common queries and complex queries such as federated queries and subqueries.

table The row referenced by the table output.

The type used by the Type union query.

Type shows the type of access, which is an important indicator, and the resulting values from good to bad are:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

In general, it is best to ensure that the query reaches at least the range level, preferably ref.

Possible_keys

Indicates which index MySQL can use to find rows in the table. If it is empty, there is no index associated with it. To improve performance, you can examine where clauses to see if some fields are referenced, or check that the fields are not appropriate for the index.

Key

Displays the keys that MySQL actually decides to use. If no index is selected, the key is null.

Key_len

Displays the key lengths that MySQL decides to use. If the key is null, the length is null. Documentation tips pay particular attention to this value to derive a multi-primary key in what part of MySQL is actually used.

Ref

Shows which field or constant is used together with the key.

Rows

This number indicates how much data MySQL will traverse to find and is inaccurate on InnoDB.

Extra

If it is only index, this means that information is retrieved only from the information in the index tree, which is faster than scanning the entire table.

If it is a where used, the where limit is used.

If it is impossible where means no where, it is generally not found out what.

If this information shows the using Filesort or using temporary, then the where and order by indexes are often out of balance, and if the index is determined by where, then the order by will inevitably cause the using Filesort, it depends on whether to filter and reorder the cost, or first sort and then filter the cost.

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------

The Type column is important, showing which category the connection is using, and if there are any indexes used.

The best to worst connection types are:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

(1) System
This is a special case of the const join type. Only one row of the table satisfies the condition.

2) const
The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is fast because they are read only once!
3) Eq_ref

For each row combination from the preceding table, a row is read from the table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY.
Eq_ref can be used for indexed columns that use the = operator comparison. The comparison value can be a constant or an expression that uses a column of a table that was read earlier in the table.

(4) ref
For each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or primary key (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good.

Ref can be used with indexed columns that use the = or <=> operator.

(5) Ref_or_null
The join type is like ref, but adding MySQL can specifically search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.
In the following example, MySQL can use the Ref_or_null join to handle Ref_tables:
SELECT * from Ref_table
WHERE key_column=expr OR Key_column is NULL;

(6) Index_merge
The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

(7) Unique_subquery
This type replaces the ref of the in subquery in the following form:
Value in (SELECT primary_key from single_table WHERE some_expr)
Unique_subquery is an index lookup function that can completely replace a subquery and be more efficient.

(8) Index_subquery
The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in the following form of subqueries:
Value in (SELECT key_column from single_table WHERE some_expr)

(9) Range
retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. The Key_len contains the longest key element of the index being used. In this type, the ref column is null.
When you compare key columns with constants using the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators, you can use range

mysql> Explain select * from T3 where id=3952602 or id=3952603;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| 1 | Simple | T3 | Range | primary,idx_t3_id | idx_t3_id | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
1 row in Set (0.02 sec)

(Ten) index
The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.
When a query uses only columns that are part of a single index, MySQL can use that join type.

--------------------------------------------------------------------------------------------------------------- ------------------------------

(11) All
For each row combination from the previous table, complete the table scan. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------

After analyzing the SQL statement performance, learn about some of the things that are relevant to improving SQL performance:

What to note when indexing is used:

1. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by.

2. Avoid null-valued fields in the WHERE clause, which will cause the engine to discard full-table scans using the index, such as:
Select ID from t where num is null
You can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query:
Select ID from t where num=0
3. Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

4. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for a full table scan, such as:
Select ID from t where num=10 or num=20
You can query this:
Select ID from t where num=10
UNION ALL
Select ID from t where num=20


5.in and not in should also be used with caution, otherwise it will result in full table scans, such as:
Select ID from t where num in
For consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3


6. The following query will also cause a full table scan:
Select ID from t where name like '%abc% '
To be more efficient, consider full-text indexing.


7. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan:
Select ID from t where [email protected]
You can force the query to use the index instead:
Select ID from T with (index name) where [email protected]


8. You should try to avoid expression operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:
Select ID from t where num/2=100
should read:
Select ID from t where num=100*2


9. You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. Such as:
Select ID from t where substring (name,1,3) = ' abc '--name ID starting with ABC
Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30 ') =0--' 2005-11-30 ' generated ID
should read:
Select ID from t where name like ' abc% '
Select ID from t where createdate>= ' 2005-11-30 ' and createdate< ' 2005-12-1 '


10. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system.


11. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.


12. Do not write meaningless queries, such as the need to generate an empty table structure:
Select Col1,col2 into #t from T where 1=0
This type of code does not return any result sets, but consumes system resources and should be changed to this:
CREATE TABLE #t (...)


13. It is a good choice to replace in with exists in many cases:
Select num from a where num in (select num from B)
Replace with the following statement:
Select num from a where exists (select 1 from b where num=a.num)


14. Not all indexes are valid for queries, SQL is query-optimized based on data in the table, and when there is a large amount of data duplication in the index columns, SQL queries may not take advantage of the index, as there are fields in the table Sex,male, female almost half, So even if you build an index on sex, it doesn't work for query efficiency.


15. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.


16. You should avoid updating clustered index data columns as much as possible, because the order of the clustered index data columns is the physical storage order of the table records, which can consume considerable resources once the column values change to the order in which the entire table is recorded. If your application needs to update clustered index data columns frequently, you need to consider whether the index should be built as a clustered index.


17. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.


18. Use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable length field storage space is small, can save storage space, second, for the query, in a relatively small field in the search efficiency is obviously higher.


19. Do not use SELECT * from t anywhere, replace "*" with a specific field list, and do not return any fields that are not available.


20. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).


21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.


22. Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.


23. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create table to mitigate the resources of the system tables. Then insert.


24. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.


25. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.


26. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient.


27. As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data. Routines that include "totals" in the result set are typically faster than using cursors. If development time permits, a cursor-based approach and a set-based approach can all be tried to see which method works better.


28. Set NOCOUNT on at the beginning of all stored procedures and triggers, set NOCOUNT OFF at the end. You do not need to send a DONE_IN_PROC message to the client after each statement that executes the stored procedure and trigger.


29. Try to avoid large transaction operation and improve the system concurrency ability.

30. Try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.

31. Implicit conversions result in index invalidation. This should be taken seriously. It is also a common mistake in development. Because the table field TU_MDN is defined as VARCHAR2 (20),
However, the field is passed to Oracle as the number type in the query, which causes the index to be invalidated.
Example of error: SELECT * from Test where tu_mdn=13333333333;
Right Example: SELECT * from Test where tu_mdn= ' 13333333333 ';

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------

Attach an article about the use and creation of composite indexes and single indexes:

Http://note.youdao.com/share/web/file.html?id=a71cc615a39e92c8bb57100a8020c056&type=note

by CXR

MySQL explain parsing SQL statements