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

What the editor shares today is to understand the implementation plan explain in MySQL. Maybe you are no stranger to MySQL, or you have never known MySQL. But don't worry, today the editor will explain the principle of MySQL's execution plan explain with the simplest description.

I. usage and definition:

Explain is the execution plan of sql. Add the explain keyword before sql.

Such as: explain select * from tbl_emp

The noun explains:

Id: [order of operation tables]

The 1.id is the same, and the execution order of the table is from top to bottom.

2.id is different, the larger the id, the first to execute.

3.id the same and different together, the larger the execution first, and then the order from top to bottom.

Select_type: query types [distinguish between simple queries, subqueries, federated queries]

Generally, there are: SIMPLE [the simplest sql query], PRIMARY [if the query contains any complex subqueries, the outermost query is marked]

SUBQUERY [include subqueries in select or where lists]

UNION

.

Type: access type [best to worst]

System > const > eq_ref > ref > range > index > ALL

It is generally good to reach the level of range, and it is best to reach ref.

Possible: displays one or more indexes that may be applied to this table; if there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query.

To put it simply: MySQL speculates that the index may be used in theory, but it may not be actually used by the query.

Key: the index actually used in the query. If it is NULL, it is either not built or used, or the index is invalid.

Override index: the number and order of fields queried after select and the composite index created are exactly the same.

If not in theory, and it appears in key, then an overlay index is used

Ref: shows which column of the index is used

Rows: the smaller the better

Extra: do not appear Using filesort [intra-file sorting], using index (index scan, appear better)

Second, the function:

Example:

During the Spring Festival this year, three new "cloud database" products and services were launched: cloud database MySQL, cloud database Redis and cloud database Memcached. Among them, cloud database MySQL is an "online database service" that is ready-to-use, stable, reliable and flexible, with multiple security protection measures and a perfect performance monitoring system, and provides professional database backup, recovery and optimization solutions, so that enterprises and users can focus more on application development and business development. On the other hand, cloud database Redis is a ready-to-use, stable and reliable high-performance data caching service, which supports "master-slave hot backup" and provides "automatic disaster recovery switching, instance monitoring, online capacity expansion" and other functions. Another cloud database product, Cloud Database Memcached, is a memory-level, persistent cache service that also supports "master-slave hot backup" and provides "automatic disaster recovery switching, instance monitoring, online capacity expansion" and other features.

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.