Shulou(Shulou.com)05/31 Report--

Editor to share with you how to implement the mysql plan, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Use the explain keyword to simulate the optimizer's execution of SQL query statements, to know how MySQL handles SQL statements, and to analyze performance bottlenecks in query statements or table structures. The fields are as follows:

Id 、 select_type 、 table 、 partitions 、 type 、 possible_keys 、 key 、 key_len 、 ref 、 rows 、 filtered 、 Extra

Mysql > explain select * from hvps where col8='3G2'

The sequence number of the id:select query that contains a set of numbers indicating the order in which the select clause or action table is executed in the query.

There are three situations:

1. Id is the same: execution order is from top to bottom.

2. Id is different: if a subquery is included, the higher the id value, the higher the priority, and the first to be executed.

Select_type: query type, used to distinguish between common queries, federated queries, subqueries, and other complex queries

1. SIMPLE: a simple select query that does not include subqueries or union

2. PRIMARY: the query contains any complex subsections, and the outermost query is marked as primary

3. SUBQUERY: subqueries are included in the select or where list

4. DERIVED: subqueries included in the from list are marked as derived (derivative), mysql or recursively execute these subqueries and put the results in the temporary table

5. UNION: if the second select appears after union, it will be marked as union;. If union is included in the subquery of the from clause, the outer select will be marked as derived.

6. UNION RESULT: the select that gets the result from the union table

Table: table name

Partitions:

Type: access type, a very important indicator in sql query optimization. The result values from good to bad are as follows:

System > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

A good sql query is at least range-level, preferably ref

1. System: the table has only one row of records (equal to the system table). This is a special case of the const type, which usually does not appear and can be ignored.

2. Const: indicates that it can be found once through the index, and const is used to compare primary key or unique indexes. Because you only need to match a row of data, all very quickly. If you put the primary key in the where list, mysql can convert the query into a const.

3. Eq_ref: unique index scan, for each index key, only one record in the table matches it. Common in primary key or unique index scans.

4. Ref: a non-unique index scan that returns all rows that match a single value. In essence, it is also an index access, which returns all rows that match a single value, but he may find multiple qualified rows, so it should be a mixture of lookup and scanning.

5. Range: only the rows in a given range are retrieved, and an index is used to select rows. The key column shows that that index is used. Generally speaking, queries such as bettween, in, and so on appear in the where statement. The range scan on this index column is better than a full index scan. You just need to start at one point and end at another without scanning all the indexes.

6. Index:Full Index Scan,index differs from ALL in that index only traverses the index tree. This is usually an ALL block, because the index file is usually smaller than the data file. Both Index and ALL read the whole table, but index reads from the index and ALL reads from the hard disk.

7. ALL:Full Table Scan, traversing the table to find matching rows.

Possible_keys: if there is an index on the field involved in the query, the index will be listed, but not necessarily used by the query.

Key: the index actually used. If it is NULL, the index is not used.

If an override index is used in the query, it appears only in the key list.

Key_len: indicates the number of bytes used in the index. The length of the index used in the query (the maximum possible length) is not the actual length used. In theory, the shorter the length, the better.

The key_len is calculated based on the table definition, not retrieved within the table.

Ref: the column that shows the index is used and, if possible, a constant const.

Rows: based on table statistics and index selection, roughly estimate the number of rows that need to be read to find the required records.

Filtered:

Extra: additional information that is not suitable for display in other fields, but is very important.

The above is all the contents of the article "how to implement the Plan of mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.