Explain: Use the Explain keyword to simulate the optimizer executing SQL query statements to know how MySQL handles your SQL statements. Analyze the performance bottlenecks of your query statements or table structuresexpain+sql Statementsthe sequence number of a id:select query that contains a set of numbers that indicate the order in which a SELECT clause or table is executed in a querythree cases: Same ID, execution order from top to bottomID is different, if it is a subquery, the ID number is incremented, the greater the ID, the higher the priority, the first is executedID is the same as the same , the same ID can be considered a group, from the top down in order to execute, in each group the higher the priority of the ID, the more the first to be executedDerived derivativeselect_type: Query type: Mainly used to distinguish common query, joint query, sub-query and other complex queryThis includes simple select queries that do not include sub-queries or unionPrimary: The query contains any complex sub-parts, and the outermost layer is marked assubquery: A subquery is included in a select or wherederived the subquery contained in the From list is marked as derived (derived) MySQL recursively executes these subqueries, placing the results in a temporary table. Union: If the second select appears after the union, it is marked as union, and if the Union is contained in a subquery in the FROM clause, the outer select is marked as: DERIVEDUnion result gets the select from the Union tableTable shows which of the tables this query belongs toType shows the type of access, which is an important indicator, with the resulting values from best to worst:System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index _subquery > Range > Index > All,in general, it is best to ensure that the query reaches at least the range level, preferably ref. all:full table Scan, traversing the full table to find a matching rowINdex:full index Scan,index and all are distinguished by the index type only to traverse the tree. This is usually faster than all, because the index file is usually smaller than the data file. (That is, although all and index are read-only, index is read from the index and all is read from the hard disk)Range: Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used typically in your where statement between, <, >, in, and so on, the range Scan Index scan is better than a full table scan, because it only needs to start at a certain point in the index, and the conclusion is another point, do not scan all indexes. Ref: A non-unique index scan that returns all rows that match a single value. It is essentially an index access that returns all rows matching a single value, however, it may find multiple rows that match the criteria, so he should belong to the search and scan mixeq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common to primary key or unique index scansThe const indicates that it was found once through the index, and the const is used to compare primary key or unique index. Because it matches only one row of data, it's very quick to put the primary key in the where list, and MySQL can convert the query to a constantThe System table has only one row of records (equal to the systems table), which is a const type of special column, usually does not appear, this is also negligibleNote: In general, the query must be guaranteed to reach at least the range level, preferably ref. Possible_key Displays the indexes, one or more, that may be applied in this table. If there is an index on the field involved in the query, the index will be listed but not necessarily used by the queryThe index that is actually used in the key query, or null if the index is not usedIf an overwrite index is used in the query, the index appears only in the key listKey_len represents the number of bytes used in the index, which allows you to calculate the length of the index used in the query. The shorter the length the better, without loss of accuracythe value displayed by Key_len is the maximum possible length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition, not by the table-retrievedref shows which column of the index is being used and, if possible, a constant. Which columns or constants are used to find the values on the index columnRow calculates the number of rows needed to find the required records, based on table statistics and index selectionExtra contains additional information that is not suitable for display in other columns but is importantUsing filesort shows that MySQL uses an external index to sort the data instead of reading in the order of the indexes in the table. Sorting operations that are not available for indexing in MySQL are called "File sorting"Using temporary allows you to save intermediate results with temporary tables, and MySQL uses temporary tables when sorting query results. It is common to sort the order by and group by queries group BY. Using index: Indicates that an overwrite index (covering index) is used in the corresponding select operation to avoid accessing the table's data rows, and the efficiency is good! If a using where is present, the index is used to perform the lookup of the index key value; If a using where is not present, the index is used to read the data rather than perform a find action. Using Where: Indicates that the where filter is usedUsing join buffer: Table name uses a connection cacheimpossible the value of the WHERE clause is always false and cannot be used to get any tuplesSelect tables optimized away without a groupby clause, optimize the Min/max operation based on the index or optimize the count (*) operation for the MyISAM storage engine without having to wait until the execution phase to perform the calculation. The stage at which the query execution plan is generated completes the optimization. Distinct optimizes the distinct operation to stop finding the same value after the first matching tuple is found
MySQL performance optimization
