Explain can parse the execution of the SELECT statement, which is the "execution plan" of MySQL.
One, type columnMySQL finds the desired line in the table. Include (from left to right, from worst to best): | All | Index | Range | Ref | Eq_ref | Const,system | null |
All (All)Full table Scan, MySQL scans the entire table to find rows from beginning to end. Mysql> Explain select * from A\g ...
Type:all if a limit such as SELECT * from a limit of 100 MySQL will scan the line, but the scanning mode will not change, or scan from beginning to end.
Index (indexed)According to the index to read the data, if the index already contains the query data, simply scan the index tree, otherwise perform a full table scan and all similar; CREATE Table A (a_id int not NULL, key (A_ID)); insert into a value (1), (2); m Ysql> explain select a_id from A\g
...
Type:index
Range (ranges)Scan index tables in scope: CREATE TABLE A (a_id int not NULL, key (A_ID)); insert into a values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); Mysql> Explain select * from a where a_id > 1\g
...
Type:range ... The in comparer is also represented by range:mysql> explain select * from a where a_id in (1,3,4) \g
...
Type:range
... `
ref (Reference)Non-unique index access list: Create Table A (a_id int not NULL, key (A_ID)); insert into a values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); Mysql> Explain select * from a where a_id=1\g
...
Type:ref ...
Eq_ref (equivalent reference)Use a unique index lookup (primary key or uniqueness index) to build a table and insert data: CREATE TABLE A (ID int primary key), CREATE TABLE a_info (ID int primary KEY, title char (1)); Inse RT into a value (1), (2), insert into A_info value (1, ' a '), (2, ' B ');mysql> explain select * from a join a_info using (ID);
...+--------+--------+...
...| Table | Type |
...+--------+--------+...
...| A | Index |
...| A_info | Eq_ref |
...+--------+--------+... At this point a_info each record corresponds to a one by one, associated with the primary key ID, so the type of A_info is eq_ref. Delete A_info primary key: ALTER TABLE ' a_info ' DROP PRIMARY key; now A_info is no longer indexed:mysql> explain SELECT * from a join a_info using (ID);
+----+...+--------+--------+...| ID |...| Table | Type |...+----+...+--------+--------+...| 1 |...| A_info | All |...| 1 |...| A | Eq_ref |...+----+...+--------+--------+ ... This time MySQL adjusts the order of execution, first scans the A_info table in the full table, and then eq_ref the table A, because the A-table ID or the primary key. Delete the primary key of a: ALTER TABLE a drop primary key; Now a also has no index:mysql> explain select * from a join a_info using (ID);
...+--------+------+...
...| Table | Type |
...+--------+------+...
...| A | All | ...
...| A_info | All | ...
...+--------+------+... Now all two tables are scanned using a full table. Build table and Insert data: CREATE TABLE A (ID int primary key), CREATE TABLE a_info (id int, title char (1), key (ID)), insert into a value (1), (2); INSERT into A_info value (1, ' a '), (2, ' B '); Now a_info table ID column becomes normal index (non-uniqueness index):mysql> explain SELECT * from a join a_info USI Ng (id) where a.id=1;
...+--------+-------+...
...| Table | Type |
...+--------+-------+...
...| A | Const |
...| A_info | Ref | ...
... +--------+-------+...a_info table type becomes ref type. Therefore, the uniqueness index will appear eq_ref (the non-unique index will appear ref), because it is unique, so only one record is returned, and no further lookups are found, so it is faster than ref.
const (constant connection)Known as the "constant", the word is not good to understand, but the occurrence of a const is the following two cases: in the entire query process, the table will have a maximum of one matching row, such as the primary key id=1 must be only a row, just read the table data to get the desired results, And the table data is read when the execution plan is exploded. The return value is placed directly in the SELECT statement, similar to select 1 as f. You can select to view internal processes through extended: CREATE TABLE and insert data: Created tables A (ID int primary KEY, C1 char () NOT NULL, C2 text NOT NULL, C3 text not nul l); INSERT into a values (1, ' asdfasdf ', ' asdfasdf ', ' asdfasdf '), (2, ' asdfasdf ', ' asdfasdf ', ' asdfasdf ');mysql> explain Extended SELECT * from a where id=1\g
...
Type:const
Possible_keys:primary
Key:primary
... See how MySQL is optimized with show warnings:mysql> show Warnings\g
...
Message:select ' 1 ' as ' id ', ' asdfasdf ' as ' C1 ', ' asdfasdf ' as ' C2 ', ' asdfasdf ' as
' C3 ' from ' Test '. ' A ' where 1 query returns the result for:mysql> select * from a where id=1;
+----+----------+----------+----------+
| ID | C1 | C2 | C3 |
+----+----------+----------+----------+
| 1 | ASDFASDF | ASDFASDF | ASDFASDF |
+----+----------+----------+----------+ as you can see, the field values in the returned results appear directly in the optimized SELECT statement in the form of value as field name. Modify Query:mysql> Explain select * from a where ID in (\g)
...
Type:range ... When more than 1 results are returned, the type is no longer const. Re-build the table and insert the data: CREATE TABLE A (id int not null); Insert to a value (1), (2), (3);mysql> explain select * from a where id=1\g
...
Type:all there is only one id=1 record in the current table, but type is all, because only a unique index can guarantee that there is at most one record in the table, and only then can the type be const. When you add a normal index to an ID, the type becomes ref, and when you add a unique or primary key index, the type becomes const.
Second, Extra column
Extra represents additional information, which is common in the following categories (also ranked by query efficiency from high to low):
- Using index: Indicates that the index is used, if only using index, indicating that he did not query to the data table, only the index table to complete the query, which is called the Overwrite index. If the using where is present, the index is used to find the read record, but it can be indexed, but it needs to be queried to the data table.
- Using where: Represents a conditional query, and if you do not read all of the table's data, or you can get all the required data by simply indexing it, a using where is present. If the type column is all or index, and the information does not appear, you may be executing the wrong query: all data is returned.
- Using Filesort: Not the meaning of "Use file index"! Filesort is a sort strategy implemented by MySQL, which is typically used when you use the order by of a sort statement.
- Using temporary: Indicates that in order to get results, a temporary table is used, which is usually present in multi-table union queries, where the results are sorted.
If explain appears with the next two messages (Using filesort,using temporary), and rows is larger, it usually means that you need to adjust the query statement, or you need to add an index, you need to eliminate both of these information.
MySQL explain type column & extra column
