Mysql query optimization skills

Use the EXPLAIN statement to check optimizer operations + ---- + ------------- + ---------- + ------- + ------------- + ------ + --------- + ------ + ----------------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra + ---- + ------------- + ---------- + ------- + ------------- + ------ + --------- + ------ + --------------- | 1 | SIMPLE | car_info | range | name | 768 | NULL | 9 | Using where; using index | + ---- + ------------- + ---------- + ------- + --------------- + ------ + --------- + ------ + ----------------
EXPLAIN output
Select_type has the following types:SIMPLE: a simple select statement that does not use a connection query or subqueryExplain select * from car_info;
PRIMARY: select statement on the outermost layerExplain select * from (select name from car_info where name like 'cadillac % ') as;
+ ---- + ------------- + ------------ + ------- + --------------- + ------ + --------- + ------ + -------------
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------ + ------- + --------------- + ------ + --------- + ------ + ---------------
| 1 | PRIMARY | <derived2> | ALL | NULL | 9 |
| 2 | DERIVED | car_info | range | name | 768 | NULL | 9 | Using where; Using index |
+ ---- + ------------- + ------------ + ------- + --------------- + ------ + --------- + ------ + ---------------
UNION: the second or the following select statement in unionExplain select name from car_info where id = 100 union select name from web_car_brands where id = 5; + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + -- | id | select_type | table | type | primary | key | key_len | ref | rows | Extra | + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + --- | 1 | PRIMARY | car_info | const | PRIMARY | 8 | const | 1 | | 2 | UNION | web_car_brands | const | PRIMARY, id | PRIMARY | 4 | const | 1 | NULL | union result | <union1, 2> | ALL | NULL | + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + ---

Dependent union: the second or subsequent color of the union statement is a traditional statement, depending on the external query.
Mysql> explain select * from t3 where id in (select id from t3 where id = 3952602 union all select id from t3 );
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------ + --
| 1 | PRIMARY | t3 | ALL | NULL | 1000 | Using where
| 2 | dependent subquery | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
| 3 | dependent union | t3 | eq_ref | PRIMARY, idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
| NULL | union result | <union2, 3> | ALL | NULL |
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------ +-

UNion result: RESULT of unionExplain select name from car_info where id = 100 union select name from web_car_brands where id = 5; + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + ----- | id | select_type | table | type | primary | key | key_len | ref | rows | Extra | + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + -- | 1 | PRIMARY | car_info | const | PRIMARY | 8 | const | 1 | | 2 | UNION | web_car_brands | const | PRIMARY, id | PRIMARY | 4 | const | 1 | NULL | union result | <union1, 2> | ALL | NULL | + ------ + -------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + ----
SUBQUERY: The first SELECT statement in the SUBQUERY.Explain select name from car_info where id = (select id from web_car_series where id = 5 ); + ---- + ------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + ----- | id | select_type | table | type | primary | key | key_len | ref | rows | Extra | + ---- + ------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ | 1 | PRIMARY | car_info | const | PRIMARY | 8 | const | 1 | | 2 | SUBQUERY | web_car_series | const | PRIMARY | 4 | 1 | Using index | + ---- + --------------- + -------------- + ------- + --------------- + --------- + ------- + ------ + -----

Dependent subquery: The first select in the subquery, depending on the external Query
Explain select name from car_info where id in (select id from web_car_series where id = 5 ); + ---- + keys + ---------------- + ------- + ------------- + --------- + ------- + ------ +-| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + -------------------- + ---------------- + ------- + --------------- + --------- + ------- + ------ + | 1 | PRIMARY | car_info | index | NULL | name | 768 | NULL | 145 | Using where; using index | 2 | dependent subquery | web_car_series | const | PRIMARY | 4 | const | 1 | Using index | + ---- + usage + ---------------- + ------- + ------------- + --------- + ------- + -----

DERIVED: contains a subquery in the from list. mysql recursively executes this subquery and places the result in a temporary table.
Explain select * from (select name from car_info where id = 100); + ---- + ------------- + ------------ + -------- + --------------- + --------- + ------ +-| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------------ + -------- + --------------- + --------- + ------ +-| 1 | PRIMARY | <derived2> | system | NULL | 1 | 2 | DERIVED | car_info | const | PRIMARY | 8 | 1 | + ---- + --------------- + ---------- + -------- + ------------- + --------- + ------ +-
Type column: MySQL can find the following rows in the table (from left to right, from worst to best ): all --> index --> range --> ref --> eq_ref --> const, system --> null
ALL: perform full data table scan index: scan the table according to the index order, read the index first, and then read the specific data rows. In fact, it is still a full table scan. The advantage is that no sorting is required, in the order of indexes, range: Read the data row according to a certain range. ref: Non-unique index access eq_ref: Use unique index access (primary key or unique index). const: Only one matching row is allowed, const is often used to compare values such as primary key.
Null: results are obtained during the optimization process. You do not need to access the table or index, for example, explain select min (id) from car_info;
Possible_keys column: The possible_keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the Order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot be used in the order of the generated table.

If this column is NULL, there is no relevant index. In this case, you can check the WHERE clause to see if it references certain columns or columns suitable for indexing to improve your query performance. If so, create an appropriate index and use EXPLAIN again to check the query
The key column of the key column displays the keys (indexes) actually determined by MySQL ). To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query.
The key_len column displays the key length determined by MySQL. The length of the index used, the shorter the length, the better without compromising accuracy.
The rows column in the rows column shows the number of rows that MySQL considers to be checked when performing a query.
Mysql-Related Optimization skills
Try to use columns with the same data type for comparison
Make the indexed data column appear separately in the comparison expression
Do not use wildcards at the beginning of the like mode. In this case, the index is invalid.
Use numeric operations as much as possible, with less string operations
Select a proper data type, and select a data format suitable for the storage engine.
Try to declare the data column as not null, because MYSQL does NOT need to check whether the data column value is NULL during query and processing.
Considering using the ENUM data column, ENUM is expressed as a series of values in MYSQL, which is fast to process
Procedure analyze () statement can be used to list fields in data columns using ENUM. procedure analyze (16,256) statement indicates that the data column contains 16 or 256 bytes with different values. We do not recommend the ENUM type.
Data tables that are prone to fragmentation are organized. For data columns of variable length, fragments are easily generated as data is greatly modified or deleted. Therefore, regular optimize table
Avoid indexing BLOB or TEXT values whenever possible