MySQL Query optimization related skills

Use the explain statement to check optimizer action +----+-------------+----------+-------+---------------+------+---------+------+------+------- ---------- | ID | Select_type | Table | Type | Possible_keys | Key | key_len| Ref | Rows | Extra +----+-------------+----------+-------+---------------+------+---------+------+------+-----------------| 1 | Simple | Car_info | Range | name | name | 768 | NULL | 9 | The Using where; Using Index | +----+-------------+----------+-------+---------------+------+---------+------+------+----------------

Explain output interpretation

There are several types of Select_type: Simple SELECT statements that do not use a connection query or a subquery explain select * from Car_info;

PRIMARY: Outermost SELECT statement Explain SELECT * FROM (select name to Car_info where name like ' Cadillac% ') as A;

+----+-------------+------------+-------+---------------+------+---------+------+------+-------------

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+------------+-------+---------------+------+---------+------+------+---------------

| 1 | PRIMARY | | All | NULL | NULL | NULL | NULL | 9 | |

| 2 | DERIVED | Car_info | Range | name | name | 768 | NULL | 9 | The Using where; Using Index |

+----+-------------+------------+-------+---------------+------+---------+------+------+---------------

Union:union the second, or later, select statement explain select name from Car_info where ID =100 Union select name from Web_car_brands where id = 5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+--- | 1 | PRIMARY | Car_info | Const | PRIMARY | PRIMARY | 8 | Const | 1 | | | 2 | UNION | Web_car_brands | Const | Primary,id | PRIMARY | 4 | Const | 1 | | | NULL | UNION Result |

DEPENDENT Union:union the second or subsequent color of the traditional statement, depending on the outside query

Mysql> explain select * from T3 where ID in (select IDs from T3 where id=3952602 union ALL select IDs from T3);

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--

| 1 | PRIMARY | T3 | All | NULL | NULL | NULL | 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 | The Using where; Using Index |

| NULL | UNION Result |

+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+-

The result of the

Union result:union explain select name from Car_info where ID =100 Union select name from Web_car_brands where ID = 5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+----- | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | 1 | PRIMARY | Car_info | Const | PRIMARY | PRIMARY | 8 | Const | 1 | | | 2 | UNION | Web_car_brands | Const | Primary,id | PRIMARY | 4 | Const | 1 | | | NULL | UNION Result |

Subquery: The first SELECT statement in the subquery explain the select name from car_info where id = (SELECT id from web_car_series where id = 5); +----+-------------+----------------+-------+---------------+---------+---------+-------+------+----- | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+------ | 1 | PRIMARY | Car_info | Const | PRIMARY | PRIMARY | 8 | Const | 1 | | | 2 | subquery | web_car_series | Const | PRIMARY | PRIMARY | 4 | | 1 | Using Index | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-----

DEPENDENT subquery: The first select in a subquery, depending on the query outside

Explain select name from Car_info where ID. (SELECT ID from web_car_series where id = 5); +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+- | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+ | 1 | PRIMARY | Car_info | Index | NULL | name | 768 | NULL | 145 | The Using where; Using Index | | 2 | DEPENDENT subquery | web_car_series | Const | PRIMARY | PRIMARY | 4 | Const | 1 | Using Index | +----+--------------------+----------------+-------+---------------+---------+---------+-------+-----

DERIVED: Contains a subquery in the From list, and MySQL executes the subquery recursively and places the result in a temporary table

Explain select * FROM (select name from car_info where id = m) A; +----+-------------+------------+--------+---------------+---------+---------+------+------+- | ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+------+------+- | 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | Car_info | Const | PRIMARY | PRIMARY | 8 | | 1 | | +----+-------------+------------+--------+---------------+---------+---------+------+------+-

Type column: The way MySQL finds the rows you want in the table includes the following (from left to right, from worst to best): All-->index-->range-->ref-->eq_ref-->const,system-- >null

All: Data table Scan Index: Scan the table in the order of the index, read the index first, then read the specific data row, in fact, the whole table scan, the advantage is not to sort, in order of the index range: to read data row Ref: Non-unique index access eq_ Ref: Use unique index access (primary key or uniqueness index) const: Up to only one matching row, const is often used for numeric comparisons such as primary key

Null: Results have been obtained during the optimization process without access to the table or index such as: 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 the column is completely independent of the order of the tables shown in the explain output. This means that some keys in possible_keys cannot actually be used in the generated table order.

If the column is null, there is no index associated with it. In this case, you can improve your query performance by checking the WHERE clause to see if it references certain columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

The key Column key column shows the keys (indexes) that MySQL actually decides to use. To force MySQL to use or ignore the index in the Possible_keys column, use force index, using index, or ignore index in the query.

The Key_len column Key_len column shows the length of the key that MySQL decided to use. The length of the index used, without loss of precision, is as short as possible.

Rows column Rows column shows the number of lines that MySQL must check when it executes a query

MySQL Related optimization techniques

Try to compare data columns with the same data type

Allow indexed data columns to appear separately in a comparison expression

Do not use wildcard characters at the beginning of like mode, the index is not valid

Use numeric operations as much as possible, using less string manipulation

The data type is reasonable to choose, as far as possible "small", chooses the data format which applies to the storage engine

Try to declare a data column as NOT NULL because MySQL does not need to check whether the data column value is null during query processing

Consider using the enum data column, which is represented within MySQL as a series of numeric values, and is handled faster

Using the procedure analyse () statement, the statement can be listed in the data column as an enum-mode field, and the procedure analyse (16,256) statement indicates that there are more than 16 different values in the data column, or that the length exceeds 256 bytes, Suggestions for not presenting enum types

For data tables that are prone to fragmentation, for variable-length data columns, it is easy to fragment as data is modified or deleted, requiring periodic optimize table

Try to avoid indexing a blob or text value