Mysql summary-explain_PHP tutorial

Mysql summary. The explain statement is mainly used for select queries in SQL statements. it can display the index hits of the SQL statement, so as to better utilize the index and optimize the query efficiency. The Explain syntax is as follows: explain is mainly used for select queries in SQL statements. you can view the index hits of the SQL statement, so as to better utilize the index and optimize the query efficiency.

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

Extended is selected. if extended is used, you can use show warnings to view the optimization information after explaining, that is, the query actually executed in mysql.

Column name	Description	Description	Related links
Id	If there is no subquery or joint query, the id is 1.	Mysql executes the query in the ascending order of IDS. if the IDs are the same, the query is executed from top to bottom.
Select_type	Select type.		Common types
Table	The table referenced by the output row.	Sometimes we see Where N corresponds to the value of the id column.
Type	Mysql access method, connection access type.		Common types
Possible_keys	Indexes that may be used in the query process.	This column is created at the initial stage of optimization, but will be selected in the future optimization process according to the actual situation. Therefore, the indexes listed in this column may not be used in the subsequent process. If this column is NULL, there is no relevant index. you can check whether you need to add an index based on the actual situation.
Key	The index actually used in the access process.	It may not appear in possible_keys (the index may be overwritten even if there is no where in the query ). Possible_keys reveals which index is more effective. The key is the optimizer that determines which index may minimize the query cost. the query cost is based on the sum of system overhead and other factors. This may be due to the conflict of "execution time. If you FORCE mysql to USE or IGNORE the indexes in possible_keys, you need to use force index, use index, or ignore index in the query.
Key_len	Displays the number of bytes using the index.	Calculated based on the table structure, rather than the actual number of bytes of data. For example, ColumnA (char (3) ColumnB (int (11), in the UTF-8 character set, key_len = 3*3 + 4 = 13. When calculating this value, you must consider the character set corresponding to the character column. different character sets correspond to different bytes.	Correspondence between latin1, utf8, gbk characters, bytes, and Chinese characters in MySQL
Ref	Shows which fields or constants are used andkeyUse the query records in the table. Displays the fields or constants in other tables used as values in the index query.
Rows	It is estimated that the row needs to be scanned for the returned result set.	Instead of the final result set function, we can multiply all rows to estimate the number of rows to be checked for the entire query. It is inaccurate when there is a limit. (For Mao ?)
Extra	Additional mysql Query Information.		Common Information

Select type:

Simple: the query does not contain subqueries or joint queries.

Primary: select query at the outermost layer in a query that contains subqueries or joint queries.

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

Example: explain select (SELECT actor_id FROM actor) FROM film_actor

Dependent subquery: The first select in the subquery layer, which depends on the result set of the external query.

Example: explain select * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor)

Explain select * FROM actor WHERE actor_id IN (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: indicates a subquery, but the returned results cannot be cached. it must be recalculated based on the outer query. (Under what circumstances will this happen ?)

Derived: When a subquery is executed in the from clause, the result set of the subquery is placed in a temporary table (derived table ).

For example, explain select * FROM (SELECT * FROM actor) AS. The table column displays Where N corresponds to the value of the id column.

Union: the type of the second and subsequent select values in the union query.

Example: explain select * FROM film_actor union all select * FROM actor

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

Example: explain select * FROM (SELECT * FROM film_actor union all select * FROM actor)

Union result: obtains the result set from the temporary union table. For example, the last row in the preceding two query result sets. Among them, the list of IDs marked by 1, 2,... represents the id column. when the length of the id list exceeds 20 . Example.

Dependent union: the union in the subquery, which is all the select statements that start with the second select statement in the union statement. it also depends on the result set of the external query.

Example: explain select * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor union all select actor_id FROM film_actor)

Uncacheable union: union second or later select, but the result cannot be cached. it must be recalculated based on the outer query. (Under what circumstances will this happen)

Several common types of types are provided based on the efficiency from high to low:

NULL: mysql breaks down the query during the optimization process, and does not even need to access table data or indexes during execution, such as id =-1.

Example: explain select * FROM actor WHERE actor_id =-1

System: The queried table has only one row. This is a special case of the const join type. (If there is no index, only one data record exists. MyISAM displays system and InnoDB displays ALL)

Const: a maximum of one record can be matched. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const tables are fast because they are read only once. A unique key or primary key occurs, and the where clause sets a comparison value for it.

Example: explain select * FROM actor WHERE actor_id = 1 (WHERE actor_id is the primary key)

Eq_ref: if this index is used for search, a maximum of one matching record is returned. When you access data using a primary key or a unique index, we can see that except for the const type, this may be the best join type.

Example: explain select * FROM actor, actorsex WHERE actor. actor_id = actorsex. actor_id (actor_id is the primary key of actor and actorsex, and there is only one record in actorsex. if there is more than one record, it is not eq_ref)

Ref: this is an index access method. Only when a non-unique index or the non-unique prefix of the unique index is used (in other words, only one record cannot be obtained based on this value), the index is compared with a value, this value may be a constant or a result value from a multi-table query in the previous table. If the key used matches only a few rows, the join type is good.

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 will search for records containing NULL values during retrieval, which means that mysql must perform a secondary search to find NULL entries in the results of the first search.

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, and key_len contains the longest key element of these indexes.

Select * from test where column1 = 1 or column2 = 2 (no try! 555555)

Unique_subquery: used to optimize in with subquery, and this subquery is selected through a unique key. The combination of Fields returned by the subquery is a primary key or a unique index.

Example: explain select * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor)

Index_subquery: the join type is similar to unique_subquery. the returned result Field combination in a subquery is an index or an index combination, but it is not a primary key or a unique index.

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 clause contains between or>, the ref column is NULL. When using the =, <>,>, >=, <, <=, is null, <=>, BETWEEN, or IN operator, you can use range to compare the keyword columns with constants.

Index: scans data in the order of indexes. Because sorting is avoided by index scanning, data in the whole table is also scanned. the overhead of random reading is higher. If the extra column displays the using index, it indicates that the index is overwritten (covering the index: containing the indexes of all data columns meeting the query needs ). It is particularly useful for InnoDB tables. in this case, you only need to access the index data. you no longer need to obtain the original data rows based on the primary key information, avoiding secondary queries, the optimization effect of MyISAM table is less obvious than that of InnoDB.

All: scan all table data by row, unless the limit or extra column in the query displays the use of distinct, notexists, and other restrictions.

Extra information:

Distinct: when mysql finds the first matched result value, it stops querying the value and continues the query of other values in the column.

Not exists: in the left join, the optimizer can use the optimization method by changing the original query combination. When a matched row is found, it is no longer searched for the previous row. this can partially reduce the number of data accesses. For example, for tables t1 and t2, WHERE t2.id IS not null, for SELECT * FROM t1 left join t2 ON t1.id = t2.id WHERE t2.id is null; because t2.id IS not empty, therefore, it is only possible that T1. T2. Left join was originally intended to be joined on both sides. now Mysql optimization only needs to find t2.id in t2 according to t1.id to jump out.

Const row not found: the involved table is an empty table with no data in it.

Full scan on NULL key: this is an optimization method used by the optimizer to subqueries. this optimization is performed when NULL values cannot be accessed through indexes.

Impossible Having: The Having clause is always false and cannot select any columns. For example, having 1 = 0

Impossible WHERE: The Where clause is always false, and no columns can be selected. For example, where 1 = 0

Impossible WHERE noticed after reading const tables: mysql reads "const/system tables" and finds that the Where clause is false. That is to say, in the where clause, the table corresponding to the false condition should be const/system tables. This is not the result of mysql statistics, but the conclusion obtained after actually accessing the data. When a primary key or non-null unique index is specified for a table, a query can only hit one result. mysql will give priority to searching corresponding records based on this condition before the explain statement, replace the actual values of the record with all the places in the query that use the attributes of the table.

Example: select * from a, B where a. id = 1 and B. name = a. name

The execution process is as follows. id = 1 find a record (1, 'name1'), and then set B. replace name with 'name1', and then use. name = 'name1'. If no hit record is found, "Impossible WHERE noticed after reading const tables" is returned ".

No matching min/max row: No row meets the following query conditions.

For example, explain select min (actor_id) FROM actor WHERE actor_id> 3 (only two records exist)

When actor_id is a unique index, "No matching min/max row" is displayed; otherwise, "using where" is displayed ".

No matching row in const table: a join query that contains an empty table or does not have data that meets a unique index condition.

No tables used: the query does not have a From clause, or there is a From Dual (dual: virtual table, in order to satisfy the select... from... habit) clause.

Example: explain select version ()

Range checked for each record (index map: N): Mysql finds that there is no good index, but if it finds that the value of the column in the next join table is obtained, some indexes can be used through range. Mysql does not find an appropriate available index. Instead, for each row join in the previous table, it performs a test to determine which index to use (if any ), use this index to retrieve records from the table. This process is not very fast, but it is always faster than doing table join joins without any indexes.

Select tables optimized away: when we use some aggregate functions to access a field with an index, the optimizer will directly locate the required data row through the index to complete the entire query. When using certain aggregate functions such as min and max queries, you can directly access the leftmost leaf node or rightmost leaf node of the storage structure (tree B or tree B +, these can be solved through index. Select count (*) from table (excluding clauses such as where). MyISAM stores the total number of records and can directly return results. Innodb needs to scan the entire table. The Query cannot contain group by operations.

Unique row not found: for SELECT... FROM tbl_name, no row meets the unique index or primary key. An Impossible WHERE noticed after reading const tables is displayed when the query id does not exist in the table.

Using filesort: Mysql uses external sorting instead of sorting results by index. If the data volume is small, the data is sorted from memory; otherwise, the data is sorted from disk. The Explain statement does not show the sort that the client uses.

Using index: This indicates that Mysql uses the overwriting index to avoid full table scanning and does not need to perform secondary data search in the table. Be sure not to confuse it with the index type in type.

Using index for group-by: Similar to Using index, you only need to read the required data. if the query contains a group by or distinct clause, if the group field is also indexed, extra will display this value.

Using temporary: Mysql creates a temporary table to accommodate intermediate results. For group by and order by, if necessary. For example, if the group by clause is a non-key column, The Optimizer creates a temporary table with a unique key constructed according to the group by clause. then, for each query result (ignore group ), try to insert to the temporary table. if the insert fails due to the unique key, the existing record will be updated accordingly. For example, there is no index on name, SELECT name, COUNT (*) FROM product group by name. to sort, Mysql needs to create a temporary table. Using filesort is usually displayed.

Using where: Mysql will filter the results extracted by storage engine. For example, if price does not have an index, SELECT * FROM product WHERE price = 1300.00. Many where conditions include columns in the index and can be filtered during search. therefore, not all queries with the where clause will display the Using where clause.

Using join buffer: a value later than version 5.1.18. The return column of join can be obtained from buffer and joined to the current table.

Example: explain select * from t1, t2 where t1.col <10 and t2.col <10

Scanned N databases: the number of directories to be Scanned when processing the information_schema query.

Example: explain select TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES

The internet says that this query will show Scanned all databases. I tried to leave the extra column empty.

Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table: indicates the optimization of file enabling when information is queried from information_schema. Skip_open_table: The table information is obtained and does not need to be opened. Open_frm_only: only open the. frm file. Open_trigger_only: only open the. trg file. Open_full_table: not optimized .. The frm,. myd, and. myi files are all opened.

Using sort_union (...), Using union (...), Using intersect (...) : All appear in the index_merge read type. Using sort_union: extracts data with two or more keys, but the optimizer cannot ensure that each key is extracted to a naturally sorted result. therefore, to exclude unnecessary data, additional processing is required. For example, the state of the customer, (lname, fname) is the key, but the lname is not the key, select count (*) FROM customer WHERE (lname = 'Jones ') OR (state = 'ut'), because there is no key in the lname, so use (lname, fname), the results may not follow the order, the optimizer requires additional work. Using union: Extract data Using two or more keys. The obtained results are sorted. the correct results can be obtained through merging. For example, the state AND (lname, fname) in customer are key, select count (state) FROM customer WHERE (lname = 'Jones 'AND fname = 'John ') OR (state = 'ut '). Using intersect: Extract data with two or more keys. The obtained results are sorted. the correct results can be obtained by performing the intersection operation. For example, the state AND (lname, fname) in customer are key, select count (state) FROM customer WHERE (lname = 'Jones 'AND fname = 'John ') AND (state = 'ut ').

Using where with pushed condition: used only on ndb. Mysql Cluster uses Condition Pushdown to optimize and improve direct comparison between non-index fields and constants. Condition is put down by pushed to the data node of the cluster, and estimated at the same time for all data nodes, removing unqualified columns to avoid network transmission.

Ing between latin1, utf8, gbk characters, bytes, and Chinese characters in MySQL and:

Latin1:

1 character = 1 byte,1Chinese character = 2 characterA field is defined as varchar (200) and can store 100 Chinese characters or 200 characters, occupying 200 bytes. Especially when the field content is composed of letters and Chinese characters, it is recommended that the field content is composed of Chinese characters and the field length is set accordingly.

Utf8:

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

Gbk:

1 character = 2 bytes, 1 Chinese character = 1character a field is defined as varchar (200), it can store 200 Chinese characters or 200 letters, occupying 400 bytes.

Pack and download the word version

Bytes. The Explain syntax is as follows :...