MySQL Summary of explain_php skills

Explain is mainly used for select queries in SQL statements, can be displayed to view the SQL Statement index hit situation, so that better use of indexing, optimize query efficiency.

The explain syntax is as follows: Explain [extended] select ...

Where extended is selected, if the use of extended, then explain can use show warnings to view the corresponding optimization information, that is, the actual implementation of MySQL internal query.

Column Name	Describe	Description	RELATED LINKS
Id	If there are no subqueries and federated queries, the ID is 1.	MySQL executes query in the order of the IDs from large to small, and executes from top to bottom with the same ID.
Select_type	Select Type.		Common types
Table	The table referenced by the row being exported.	Sometimes the <derivedn> is seen, where n corresponds to the value of the ID column.
Type	MySQL access method, connection access type.		Common types
Possible_keys	The index that may be used during the query.	The column is created in the early stages of optimization, but will be selected in the light of the actual situation, so the indexes listed in the column may not work in the subsequent process. The column null means that there are no related indexes, and you can see if you need to index them.
Key	The index that is actually used during the access process.	It may not appear in Possible_keys (you might use a overwrite index, even if there is no where in query). Possible_keys reveals which index is more efficient, key is the optimizer determines which index may minimize the query cost, query cost based on the sum of the system overhead factors, may be "execution time" contradictions. If you force MySQL to use or ignore the indexes in Possible_keys, you need to use force index, using index, or ignore index in query.
Key_len	Displays the number of bytes using the index.	The number of bytes calculated based on the table structure rather than the actual data. such as Columna (char (3)) COLUMNB (int (11)), in the Utf-8 character set, key_len=3*3+4=13. You need to consider the character set corresponding to the character column when you calculate the value, and the different character sets correspond to the different byte numbers.	The correspondence relationship between Latin1, UTF8, gbk characters, byte number and Chinese characters under mysql5.1.5
Ref	Shows which fields or constants are used and matched to the key query records from the table. Displays the fields or constants in the other table that are used as values in the index query.
Rows	Estimates the rows that need to be scanned to return a result set.	A function that is not a final result set, multiply all rows to estimate the number of rows the entire query needs to check. When there is limit, it will be inaccurate. (For Mao?) ）
Extra	Additional information for MySQL queries.		Common information

Select Type:

The simple:query does not contain subqueries or federated queries.

Primary: The outermost select query in query that contains a subquery or union query.

Subquery: subquery in the target of select, not in from, the first select of the subquery.

For example: EXPLAIN Select (select actor_id from actor) from Film_actor

Dependent subquery: The first select in the inner layer of the subquery relies on the result set of the external query.

For example: EXPLAIN select * from actor WHERE actor_id in (select actor_id from Film_actor)

EXPLAIN select * from actor where actor_id to (select actor_id from Film_actor where actor_id=1)

EXPLAIN select * from actor where actor_id = (select actor_id from Film_actor where actor_id=1)

Uncacheable subquery: Represents a subquery, but the return result cannot be cache, and must be recalculated based on the outer query. (Under what circumstances will this happen?) ）

Derived: subqueries in the FROM clause, the result set of the query is placed in the temporary table (derived table) when the query is executed.

For example: EXPLAIN select * FROM (SELECT * actor) as a. The table column displays <derivedn>, where n corresponds to the value of the ID column.

Union: The type that corresponds to the second and subsequent select in a union query.

For example: EXPLAIN select * from Film_actor UNION all select * from actor

If the union is contained within a from subquery, the first select in the From subquery is marked as derived.

For example: EXPLAIN select * FROM (SELECT * to Film_actor UNION all SELECT * from actor) A

Union results: Gets the result set from the Union temporary table. For example, the last row in the two query result set above. <union1,2,... >,... of 1,2 The ID list that is identified represents the ID column, and the following <union1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,... > is omitted after the ID list is longer than 20. Examples are shown above.

Dependent Union: The union in a subquery, and all the select after the second select in Union, also relies on the result set of the external query.

For example: EXPLAIN select * from actor WHERE actor_id in (select actor_id from Film_actor UNION all SELECT actor_id from Film_acto R

Uncacheable Union: The second or subsequent select of Union, but the result cannot be cache and must be recalculated according to the outer query. (Under what circumstances it will appear)

Several common type types are given in terms of efficiency from high to Low:

Null:mysql breaks down query during optimization, and executes without even having to access table data or indexes, such as Id=-1.

For example: EXPLAIN SELECT * from actor WHERE actor_id =-1

System: A query has only one row for a table. This is a special case of the const join type. (In the absence of any index, there is only one piece of data, MyISAM will show that SYSTEM,INNODB will show all)

Const: There will be at most one record match. 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 quick because they are read only once. Occurs when there is a unique key or a primary key, and the WHERE clause sets a comparison value for it.

For example: EXPLAIN SELECT * from actor where actor_id = 1 (where actor_id is the primary key)

Eq_ref: Use this index lookup to return up to a record that matches the criteria. When accessing data using a primary key or uniqueness index, it is possible that this is the best join type except for the const type.

For example: EXPLAIN SELECT * from actor, actorsex where actor.actor_id = actorsex.actor_id (where actor_id is the primary key of actor, Actorsex, And there is only one record in the Actorsex, if more than one record is not eq_ref

Ref: This is an index access. Only a non-uniqueness prefix with a non-uniqueness index or a uniqueness index (in other words, occurs only if a record cannot be obtained from this value, the index is compared to a value that may be a constant, or it may be the result value of a multiple-table query from the previous list. If you use a key that matches only a few rows, the join type is good.

For example: EXPLAIN SELECT * from Film_actor,actor WHERE film_actor.actor_id=actor.actor_id and Film_actor.actor_id=1

Ref_or_null: similar to ref. The difference is that MySQL searches for additional records that contain null values, which means MySQL must make two lookups and find null entries in the results of the initial lookup.

Index_merge: Two or more indexes are used in the query, and then the index results are merged. In this case, the key column contains all the indexes used, key_len the longest key element that contains the indexes.

SELECT * FROM Test where column1 = 1 or Column2 = 2 (not tried!) 555555)

Unique_subquery: Used to optimize in with subqueries, and the subquery is selected through a unique key. The combination of fields returned by a subquery is either a primary key or a unique index.

For example: EXPLAIN select * from actor WHERE actor_id in (select actor_id from actor)

Index_subquery: The join type is similar to Unique_subquery, and the returned result field combination in a subquery is an index or index combination, but not a primary key or a unique index.

For example: EXPLAIN select * from Film_actor WHERE film_id in (select film_id from Film_actor)

Range: Scans indexes within a certain range. If the where contains between or, the ref column is null. When using the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators, you can use range when you compare key columns with constants.

Index: Scans data by index order. Because sorting is avoided by an index scan, the whole table data is scanned, and the overhead of random reads is even greater. If the extra column displays a using index, the description uses an overlay index (overlay index: An index that contains all the data columns that meet the needs of the query). For the InnoDB table is particularly useful, at this time only access to index data, no longer based on the primary key information to obtain the original data row, to avoid two queries, and MyISAM table optimization effect is not so obvious compared to InnoDB.

All: Scan all table data by row unless there are limit or extra columns in the query that use qualifiers such as distinct or notexists.

Extra Information:

Distinct: When MySQL finds the first matching result value, it stops the query for that value, and then continues the query for the other values in the column.

NOT exists: In the left connection, the optimizer can use the optimization method by changing the original query combination. When a matching row is found, no further retrieval for the previous row can partially reduce the number of data accesses. For example, table T1, T2, where t2.id is not NULL, for the SELECT * from T1 left JOIN T2 on t1.id=t2.id WHERE t2.id is null, because the t2.id is non-empty, so it is only possible in T1, and not in T2 , so the result is equal to the difference. Left join originally is to both sides join, now MySQL optimization only need to follow t1.id in T2 find a t2.id can jump out.

Const row not found: The table involved is empty and there is no data in it.

Full scan on null key: is an optimization of the optimizer subquery, which is optimized when null values cannot be accessed through the index.

The Impossible having:having clause is always false and cannot be selected for any columns. For example having 1=0

The Impossible Where:where clause is always false and cannot be selected for any columns. For example where 1=0

Impossible where noticed after reading Const TABLES:MYSQL discovers the WHERE clause is false by reading "Const/system tables". That is, the table in the WHERE clause corresponding to the false condition should be const/system tables. This is not MySQL through statistical information to make, but really to actually visit the data after the conclusion. When a table is assigned a primary key or an equivalent condition on a non-empty unique index, a query can only hit a single result, and MySQL takes precedence over this condition before explain, and replaces all the places in query that are used from the table's properties with the actual value of the record.

For example: SELECT * from a,b where a.id = 1 and b.name = A.name

The execution process is as follows: First find a record (1, ' name1 ') according to a.id = 1, then replace B.name with ' name1 ', then search through a.name = ' name1 ', find no hit record, and finally return "impossible WHERE noticed After reading const tables ".

No matching Min/max row: No rows meet the following query criteria.

For example: EXPLAIN SELECT MIN (actor_id) from actor WHERE actor_id > 3 (only two records)

When ACTOR_ID is a unique index, "No matching Min/max row" is displayed, or "Using where" is displayed.

No matching row in const table: A query that has a join, contains an empty table, or no data satisfies a unique index condition.

No tables used: The query does not have a FROM clause, or there is a from Dual (Dual: Virtual table, to satisfy select...from ...). HABIT) clause.

For example: EXPLAIN SELECT VERSION ()

Range checked for every record (index map:n): MySQL found no good index, but found that some index can be used by range, if the value of the column in the next join table is further obtained. MySQL did not find the appropriate index available. Instead, for each row of the previous table, it does a test to determine which index to use, if any, and use the index to get the records from the tables. This process is not fast, but it is faster than making a table connection without any index.

Select tables Optimized away: When we use certain aggregate functions to access a field that has an index, the optimizer navigates through the index directly to the desired data row to complete the query. In query using some aggregate functions such as Min, Max, direct access to the leftmost leaf node or the rightmost leaf node of the storage structure (b-or + + tree) can be resolved by index. Select Count (*) from table (without a WHERE clause), MyISAM saves the total number of records and returns the results directly, while INNODB requires a full table scan. You cannot have a GROUP by action in query.

Unique row not found: for select ... From Tbl_name, no row satisfies the unique index or primary key. A value that does not exist from the query ID in the table displays impossible WHERE noticed after reading const tables.

Using filesort: means that MySQL will arrange the results in an external sort rather than in the index order. Sort from memory when the data is low, or sort from disk. Explain does not show what sort of order to tell the client.

Using index: Indicates that MySQL uses the overlay index to avoid full table scans, and does not need to go to the table to find data two times. Be careful not to confuse with the index type in type.

Using index for group-by: The required data only needs to read the index when there is a group BY or DISTINCT clause in query, and extra displays the value if the Grouping field is also in the index.

The Using Temporary:mysql creates a temporary table to accommodate intermediate results. At the time of group by and order by, if necessary. For example, group by a non-key column, the optimizer creates a temporary table, has a unique key built according to the group by condition, and then attempts to insert into a temporary table for each query result (ignoring group by), if the unique Key causes the insert to fail, the existing record is updated accordingly. For example, there is no index on name, SELECT name,count (*) from product GROUP by name, and in order to sort, MySQL needs to create temporary tables. A using Filesort is also typically displayed at this time.

Using Where: Indicates that MySQL will filter the results of the storage engine extraction. For example, price does not have Index,select * from Product WHERE price=1300.00. There are many where conditions that can be filtered when searching because they contain columns in index, so not all queries with a WHERE clause display the using where.

The value that is only available after the Using join buffer:5.1.18 version. The return column of the join can be obtained from the buffer and join with the current table.

For example: Explain select * from T1,t2 where T1.col < and T2.col < 10

Scanned N databases: refers to how many directories need to be scanned when processing INFORMATION_SCHEMA queries.

For example: EXPLAIN SELECT table_name, Row_format from INFORMATION_SCHEMA. TABLES

Online said this query will show scanned all databases, I tried the extra column is empty.

Skip_open_table, Open_frm_only, Open_trigger_only, open_full_table: Indicates the optimization of file opening when querying information from INFORMATION_SCHEMA. Skip_open_table: Table information has been obtained and does not need to be opened. Open_frm_only: Open. frm file only. Open_trigger_only: Open. trg file only. Open_full_table: No optimizations. Frm,.myd and. myi files are open.

Using Sort_union (...), using Union (...), using intersect (...) : All appear in the Index_merge read type. Using sort_union: Extracts data with two or more than two keys, but the optimizer cannot ensure that each key extracts a naturally ordered result, so additional processing is required to eliminate redundant data. For example, the customer's state, (Lname,fname) is a key, but lname is not key,select COUNT (*) from the customer WHERE (lname = ' Jones ') OR (state = ' UT '), by the There is no key on the lname, so use (lname,fname) so that the result may not be in order, the optimizer needs some extra work. Using union: Extracts the data with two or more keys, the results are sorted separately, and the correct results can be obtained by merging. For example, the state and (Lname,fname) in the customer are Key,select COUNT (state) from the customer WHERE (lname = ' Jones ' and fname= ' John ') or (state = ' UT '). Using intersect: Extracts data with two or more keys, respectively, and the results are sorted, and the correct results can be obtained by intersection. For example, the state and (Lname,fname) in the customer are Key,select COUNT (state) from the customer WHERE (lname = ' Jones ' and fname= ' John ') and (stat E = ' UT ').

The using where with pushed condition: used only on NDB. Mysql Cluster uses condition Pushdown optimizations to improve direct comparisons between non indexed fields and constants. The condition is pushed down to the cluster data node and is estimated at the same time at all data nodes, eliminating the undesirable columns to avoid network transmission.

mysql5.1.5 latin1, UTF8, gbk characters, byte number, Chinese characters of the corresponding relationship:

Latin1:

1character=1byte, 1 Chinese characters =2character a field is defined as varchar (200), which can store 100 characters or 200 characters and occupy 200 bytes. Especially when the field content is composed of letters and Chinese characters, try to assume that the field content is composed of Chinese characters, so as to set the length of the field.

UTF8:

1character=3bytes, 1 Chinese characters =1character a field is defined as varchar (200), it can store 200 characters or 200 letters and occupy 600 bytes.

Gbk

1character=2bytes,1 Kanji =1character A field that is defined as varchar (200), it can store 200 characters or 200 letters and occupy 400 bytes.

Word version package download