Shulou(Shulou.com)06/01 Report--

1), the larger the number of the id column, the first to execute. If the number is the same, then it will be executed from top to bottom. The table in the id column null is that this is a result set, so you do not need to use it for query. 2) the common select_ type columns are: A:simple: a simple select query that does not require union operations or does not contain subqueries. When there is a join query, the outer query is simple, and there is only one B:primary: a select that requires a union operation or contains a subquery, and the select_type of the outermost unit query is primary. Two select queries with only one C:union:union join, the first query is a dervied derived table, except for the first table, the second subsequent table select _ type is union D:dependent union: like union, it appears in the union or union all statement, but this query is affected by the external query E:union result: contains the result set of union, in the union and union all statements, because it does not need to participate in the query So the id field is null F:subquery: except for the subquery contained in the from sentence, the subquery that appears elsewhere may be subquery G:dependent subquery: similar to dependent union, it means that the query of this subquery is affected by the external table query, the subquery that appears in the H:derived:from sentence is also called derived table, other databases may be called inline view or nested select 3), query table name displayed by table, if the query uses aliases So what is shown here is the alias, if the operation on the data table is not involved, then it is shown as null, if it is enclosed in angle brackets, it means that this is a temporary table, and the N after it is the id in the execution plan, indicating that the result is generated by this query. If it is enclosed in angle brackets, it is also a temporary table, indicating that the result comes from the result set of the union query whose id is MJN. 4), type from good to bad: system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL, except all, all other type can use index, except index_merge, other type can only use one index A:system: there is only one row of data in the table or an empty table, and can only be used in myisam and memory tables. In the case of an Innodb engine table, the type column is usually all or index B:const in this case: when using a unique index or primary key and returning the equivalent where condition that the record must be a row of records, the type is usually const. Other databases are also called unique index scan C:eq_ref: appear in the query plan that joins a table, driving the table to return only one row of data, which is the primary key or unique index of the second table, and must be not null. When the unique index and primary key are multiple columns, eq_ref D:ref will appear only when all columns are used for comparison: unlike eq_ref, the join order is not required. There are no requirements for primary keys and unique indexes, which may occur as long as they are retrieved using equal conditions, and it is common to find the equivalent of a secondary index. Or in a multi-column primary key or unique index, using a column other than the first column as an equivalent lookup may also appear. In short, an equivalent lookup that returns data that is not unique may occur. E:fulltext: full-text index retrieval, it should be noted that the priority of full-text index is very high. If full-text index and general index exist at the same time, mysql will give priority to using full-text index F:ref_or_null regardless of cost: similar to ref method, it only increases the comparison of null values. It doesn't use much in practice. G:unique_subquery: used for the in form subquery in where. The subquery returns the unique value H:index_subquery with no duplicate value: for the in form subquery, the secondary index or in constant list is used, and the subquery may return duplicate values. You can use the index to deduplicate the subquery. I:range: index range scanning, commonly used in >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.