Mysql explain usage (Optimize Query), mysqlexplain

Mysql explain usage (Optimize Query), mysqlexplain

　　ExplainIt shows how mysql uses indexes to process select statements and connect tables. Can HelpSelect a better IndexAndWrite more optimized query statements.

1. Create a database

The created SQL statement is as follows:

/* Navicat MySQL Data Transfer Source Server         : localhost-newpassword Source Server Version : 50550 Source Host           : localhost Source Database       : testExplain Target Server Version : 50550 File Encoding         : utf-8 Date: 08/05/2016 18:06:12 PM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------  Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` int(11) NOT NULL,  `name` varchar(30) NOT NULL,  `age` int(11) NOT NULL,  `sex` tinyint(4) NOT NULL,  `isDeleted` tinyint(4) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `id_unidx` (`id`) USING BTREE,  UNIQUE KEY `name_unidx` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- ------------------------------  Records of `user`-- ----------------------------BEGIN;INSERT INTO `user` VALUES ('1', 'wwwwe', '11', '1', '0'), ('2', '222', '22', '1', '0'), ('3', '2222', '10', '0', '0');COMMIT;SET FOREIGN_KEY_CHECKS = 1;

2. explain usage

Usage: add the explain statement before the select statement, for example:

explain select * from user where id = 1

 

Result:

+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys    | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+| 1  | SIMPLE      | user  | const | PRIMARY,id_unidx | PRIMARY | 4       | const | 1    |       |+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+

3. explain Parameters

Id: Select identifier. This is the serial number of the select query. This is not important. The query sequence number is the sequence in which SQL statements are executed.

Select_typeThere are mainly the following values:

• SimpleIt indicates a simple select statement without union or subquery.
• PrimaryThe outermost select statement. In a subquery statement, the outermost select query is primary.
• UnionThe second or later of the union statement.
• Dependent unionThe second or subsequent SELECT statement in UNION depends on the external query.
• Union resultUNION result

Table: Tables used to output rows

Type: Display the connection type. The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and all.

• Const: The table can have at most one matching row. const is used to compare the primary key or unique indexes. Because only one row of data is matched, remember to use primary key or unique soon.
• Eq_regThe mysql manual says this: "for each row combination from the previous table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key ". Eq_ref can be used to use = to compare columns with indexes.
• Ref: Ref for each row combination from the preceding table, all rows with matching index values will be read from this table. If the join only uses 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), use ref. If the key used matches only a few rows, the join type is good.
• Range: For queries within a given range, use an index to check rows.
• Index: The join type is the same as that of ALL except that the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. (That is to say, although all and Index are read from the whole table, index is read from the Index, and all is read from the hard disk)
• ALLPerform a full table scan for each row combination from the previous table. If the table is the first table without the const Mark, this is usually not good, and usually in this caseVeryPoor. Generally, you can 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.

Possible_keys: Displays indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the where statement for the relevant domain.

Key: Actually used index. If it is null, no index is used. In rare cases, mysql selects an optimized index. In this case, you can use index (indexname) in the select statement to force an index or use ignore index (indexname) to force mysql to ignore the index.

Key_len: The length of the index used. Without compromising accuracy,The shorter the length, the better.

Ref: Displays which column of the index is used. If possible, it is a constant.

Rows: The number of rows that mysql considers to be checked to return the requested data,The larger the value, the worse the problem.The index is not used.

Extra: Extra information about how mysql parses the query. Using temporary and using filesort areWorst caseMysql cannot use indexes at all, and the result is that the retrieval will be slow.

 

 Meaning of the description returned by the extra column

Distinct: Once mysql finds the row that matches the row, it does not search again.

Not exists: mysql optimizes left join. Once it finds a row that matches the left join standard, it no longer searches.

Range checked for each record (index map: #): no ideal index is found. Therefore, for each row combination in the preceding table, mysql checks which index is used, use it to return rows from the table. This is one of the slowest connections using indexes.

Using filesort: When you see this, the query needs to be optimized. Mysql requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.

Using index: the column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are in the same index.

When using temporary sees this, the query needs to be optimized. Here, mysql needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.

Where used uses the where clause to limit which rows match the next table or return the rows to the user. If you do not want to return all rows in the table and the connection type is all or index, this will happen, or if there is a problem with the query interpretation of different connection types (sort by efficiency order)

The system table has only one row: system table. This is a special case of the const connection type.

Const: the maximum value of a record in the table can match this query (the index can be a primary key or a unique index ). Because there is only one row, this value is actually a constant, because mysql first reads this value and treats it as a constant.

Eq_ref: During the connection, mysql reads a record from the table from the Union of each record in the previous table during query, it is used when you query all data that uses the index as the primary key or unique key.

Ref: This connection type only occurs when the query uses keys that are not the only or primary key, or some of these types (for example, using the leftmost prefix. For each row union in the previous table, all records are read from the table. This type depends heavily on the number of records matched by the index-the fewer the better

Range: This connection type uses an index to return rows in a range, such as> or <what happens when something is searched

Index: This connection type performs a full scan of each record in the preceding table (better than all, because the index is generally smaller than the table data)

All: this connection type performs a full scan for each of the preceding records. This is generally poor and should be avoided as much as possible.

　　Thank you: Thank you for reading this article!