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

With regard to the execution plan of MySQL, take a note, which can be used as a basis for optimization. Try to optimize the fourth column type to ref, at least to ensure that range mode can be used to overwrite the index. Then possible_keys shows that null does not mean that the index is not used. When overwriting the index, it may only be displayed in the key column, and possible_keys shows null. Then note that when temporary tables may be used when grouping and sorting, try not to use disk temporary tables; first, generate the execution plan: Explain syntax EXPLAIN SELECT... Variant: 1. EXPLAIN EXTENDED SELECT. Decompile the execution plan into SELECT statements, and run SHOW WARNINGS to get the query statements optimized by the MySQL optimizer, such as mysql > explain EXTENDED select CUST_ID, count (*) from biz_member_info group by CUST_ID limit 10 +- -+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | 17665850.00 | Using index | +- -+ mysql > show warnings +- -+ | Level | Code | Message | +- - -- + | Note | 1003 | / * select#1 * / select `cms`.`biz _ member_ info`.`Cust _ ID `AS `CUST_ ID` Count (0) AS `count (*) `from `cms`.`biz _ member_ info`group by `cms`.`biz _ member_ info`.`Cust _ ID `10 | + -+ 1 row in set (0.00 sec) 2. EXPLAIN PARTITIONS SELECT. EXPLAIN II for partitioned tables: parsing of execution plans mysql > explain select CUST_ID, count (*) from biz_member_info group by CUST_ID limit 10 +-+ | id | select_type | table | type | | possible_keys | key | key_len | ref | rows | Extra | + -+ | 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | Using index | + -+ 1 row in set (0.00 sec) 2.1, The larger the number of the first column id id column, the more it executes first. If the number is the same, then it is executed from top to bottom, and the id column null indicates that this is a result set and does not need to be used for query. 2.2.2.The second column select_type A:simple: represents a simple select query that does not require a union operation or contains no 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. And two select queries with only one C:union:union connection, select. From table1 union select. From table2; 's first query (i.e. select.... From table1) 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 subqueries 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, also known as derived table, may be called inline view or nested select 2.3 in other databases. The query table name shown in the third column table- 1) if the query uses an alias, then the alias is shown here. 2) if the operation on the data table is not involved, then this is shown as null. 3) if it is shown 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. 4) if it is enclosed in angle brackets, it is also a temporary table, indicating that the result comes from the result set of the id of the union query. 2.4.The fourth column type- shows the way to query data, a very important value! From good to bad: system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL, except 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 for 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 scans

C:eq_ref: appears in a query plan that joins multiple tables, driving the table to return only one row of data per join, and this row of data 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 will appear only when all columns are used for comparison. (only in this way can uniqueness be guaranteed.) in general, when using a unique key or primary key to query the equivalence of a single table, it is usually const. When multiple tables are joined, the first table returns only one row of data each time under the join condition, and this row of data can be retrieved by the primary key or unique index of the second table, and the unique key value is not empty, usually eq_ref. As follows:

D:ref: no primary key and unique index requirements, as long as the use of equal condition retrieval may occur, common and secondary index equivalent lookup. Or in a multi-column primary key or a multi-column 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 >, show global status like'% tmp%' +-+-+ | Variable_name | Value | +-+-+ | Created_tmp_disk_tables | 65 | Created_tmp_tables | 142 | +- -+-+ Created_tmp_disk_tables: the number of internal temporary tables generated by MySQL server on disk Created_tmp_tables: the number of all internal temporary tables generated by MySQL server; III: how does MySQL select memory temporary tables and disk temporary tables when we perform some special operations such as Order By,Group By that requires temporary tables to complete, MySQL may need to use temporary tables. When our temporary table is small (less than the size set by the tmp_table_size parameter), MySQL will create the temporary table into a temporary table in memory. Only when the size set by tmp_table_size cannot hold the entire temporary table will MySQL store the table created by the MyISAM storage engine on disk. However, when the size of another system parameter, max_heap_table_size, is smaller than tmp_table_size, MySQL uses the size set by the max_heap_table_size parameter as the maximum temporary memory table size, ignoring the value set by tmp_table_size. Moreover, tmp_table_size parameters are not available until MySQL 5.1.2. Previously, max_heap_table_size; I:using where has been used to indicate that not all the records returned by the storage engine meet the query criteria and need to be filtered in the server layer. If the index is not used, it simply indicates that the filter condition is used; J:firstmatch (tb_name): one of the new features of optimized subqueries introduced at 5.6.x is common in subqueries of type in () in where sentences. This may occur if the amount of data in the inner table is large; K:loosescan (m.. n): one of the new features of optimized subqueries introduced after 5.6.x, which may occur when duplicate records may be returned by subqueries of type in () L:Using index condiction: represents the use of ICP optimization, mainly for the optimization of where conditional filtering. ICP is introduced after 5.6.x to optimize queries of range, ref, eq_ref and ref_or_null types. ICP is the abbreviation of index condition pushdown. ICP is not supported in the version of MySQL before 5.6. when performing an index query, the storage engine layer first looks up records according to the index. Then in the server layer, the records are filtered according to the where conditions. After supporting ICP, the MySQL database will determine whether the where condition filtering can be carried out while the index is taken out by the storage engine layer, that is, part of the filtering operation of the where can be placed on the storage engine layer. Under some queries, the request for records by the upper sql layer can be greatly reduced, thus improving the overall performance of the database. 3.1.This column appears when filtered uses explain extended. Versions after 5.7have this field by default, so you don't need to use explain extended. This field indicates the number of records that satisfy the query / the proportion of the data returned by the storage engine after the data returned by the storage engine is filtered in the server layer. Note that it is the percentage, not the specific number of records; that is, the number of final results of filtered= / the amount of data returned by the storage engine layer.

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.