Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL EXPLAIN SQL output description     - Three binary tree traversal (recursive, non-recursive traversal and Morris) (Programming)

- AIX system file security aspects (Linux)

- NGINX Plus now fully supports HTTP / 2 (Server)

- Use IP address spoofing Intrusion Prevention Firewall (Linux)

- Linux common commands: nslookup, ls md5sum, uname, history, etc. (Linux)

- How to view the Linux QPS (Linux)

- Ubuntu Tutorial: How to Upgrade a New Linux Kernel 3.12.7 on Ubuntu (Linux)

- Docker use Dockerfile created since the launch of the service support SSH container mirror (Server)

- Ubuntu install driver manager Mint Driver Manager and Device Driver Manager (Linux)

- Android Studio Getting Started Hello World (Programming)

- Linux System Getting Started Learning: the local time in between Perl and UNIX timestamp conversion (Linux)

- Construction CA certificate using OpenSSL command line (Server)

- Java logging performance of those things (Programming)

- Linux instructions and examples GPG encryption and decryption (Linux)

- A simple shell script for monitoring in Linux (Linux)

- Linux find and xargs (Linux)

- Transfer files and permissions from Windows to Linux system by Samba (Linux)

- Java inner classes (Programming)

- Oracle11g build physical standby database (Database)

- OpenGL Programming Guide (8th edition of the original book) - compute shader (Programming)

  MySQL EXPLAIN SQL output description
  Add Date : 2018-11-21      
  EXPLAIN statement can be used to obtain some information on when SQL execution, such as the connection order of the table, the table manner as the like. By the relevant information for further analysis, we can add the appropriate index on the table, as well as to optimize the connection order, usage tips, and so on means to achieve the purpose of SQL allows efficient operation. This article describes the use of EXPLAIN and give relevant examples.

One, EXPLAIN Overview

EXPLAIN statement is primarily used for parsing SQL execution plan and take appropriate manner to optimize efficiency by analyzing running SQL execution plan.
EXPLAIN statement output generally includes the id column, select_type, table, type, possible_keys, key column information, etc.
EXPLAIN EXTENDED supports some additional information related to the implementation plan
EXPLAIN PARTITIONS supports partition table query execution plan infos
Two, EXPLAIN output Column Description

- The following by way of example to show EXPLAIN output column
(Root @ localhost) [test]> explain select sum (amount) from customer a,
    -> Payment b where 1 = 1 and a.customer_id = b.customer_id and
    -> Email='abc@abc.org '\ G
*************************** 1. row ******************** *******
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 590
        Extra: Using where
*************************** 2. row ******************** *******
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: test.a.customer_id
         rows: 14
1, showing the significance of each column

Column Meaning
------ ------------------------------------
id The SELECT identifier
select_type The SELECT type
table The table for the output row
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate of rows to be examined
filtered Percentage of rows filtered by table condition
Extra Additional information
2, the detailed description of each column

    It contains a number that represents the order of the operating table or select clause of the query execution
    The same id, the execution order from top to bottom, or the greater the id value (usually subquery produces) a higher priority, the first to be executed
    id if the same can be considered as a group, down from the sequential execution; in all groups, id value, the higher the priority, the more the first implementation

    Each type represents the query select clause (simple OR complicated)
    select_type Value Meaning
    ------------- ------------------------------------- ----------
    SIMPLE Simple SELECT (not using UNION or subqueries)
    PRIMARY Outermost SELECT outermost select
    UNION Second or later SELECT statement in a UNION
    DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
    UNION RESULT Result of a UNION.
    SUBQUERY First SELECT in subquery
    DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query (usually for the relevant sub-query)
    DERIVED Derived table SELECT (subquery in FROM clause)
    MATERIALIZED Materialized subquery
    UNCACHEABLE SUBQUERY A subquery for which the result can not be cached and must be reevaluated
                             for each row of the outer query
    UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable
                             subquery (see UNCACHEABLE SUBQUERY)

    From which table (table name) on the output line record, it could be the following values:
     < UnionM, N>: The row refers to the union of the rows with id values of M and N.
     < DerivedN>: The row refers to the derived table result for the row with an id value of N.
                  A derived table may result, for example, from a subquery in the FROM clause.
 < SubqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N.

    Which match the query records from a partition when using EXPLAIN, partitions PARTITIONS keyword is specified

    Connection Type
    system table only one row
    const tables at most only one line matching, common for the primary key or unique index comparison
    eq_ref each table combined with the previous row in the table to read only one line, which is in addition to the system, const best kind,
                    Characterized by the use =, and all parts are involved in join indexes and index is a primary key or unique key blank
    ref If every match only a small number of lines, it is better kind, use = or < =>, may be left covering index or a primary key or unique key
    Full-text search fulltext
    Similar ref_or_null the ref, but including NULL
    index_merge indicates that an index merge optimization (including the intersection, union and intersection between the union), but does not include cross tables and full-text indexing.
                    The more complex, the current understanding of the range of an index scan combined single-table (if the cost estimate to be better than normal range, then)
    unique_subquery in the sub-query is value in (select ...) to the form "select unique_key_column" subquery replacement.
                    PS: it is not necessarily in the sub-clause of the query is inefficient!
    index_subquery above, but the form as "select non_unique_key_column" subquery replacement
    Range range of constant values
    . Index a query when the index is covered, that is, all the data are available (Extra in Using Index) from the time the index tree acquired;
                    . B in order to find the index data rows full table scan (no Using Index) from the index;
                    . C If the Extra Using Index and Using Where occur simultaneously, then the key is to use the index to find the meaning;
                    d. As appears alone, it is used to read the index instead of the line read, but not for finding
    all full table scan

    Which noted that MySQL can use an index to find the rows in the table.
    On the field the queries involved if there is an index, the index will be listed, but not necessarily queries.
    If re-classified as NULL, indicating that the query does not use the relevant index on the table currently considering whether it is necessary to add index

    Display index MySQL actually used in the query, if not use the index, shown as NULL
    There may also be situations possible_keys key is not equal, that possible_keys not suitable to extract the required line
    And the column selected by the query while using other more efficient index
    TIPS: If the query uses a covering index, the index appears only in the key list

    The number of bytes used represents an index, you can calculate the index used in the query through the column length

    The above table represents the connection to match the conditions, namely which columns or constants are used to find the value of the index column

    MySQL Table showing statistics and index selection, the estimated number of rows to find the desired record to be read
    For InnoDB, the estimated value, not necessarily accurate

    However, additional information is very important unsuitable appear in other columns
Third, the use EXPLAIN EXTENDED example

(Root @ localhost) [test]> explain extended select * from city where country_id in
    -> (Select country_id from country where country = 'China') and 1 = 1 \ G
*************************** 1. row ******************** *******
           id: 1
  select_type: SIMPLE
        table: country
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 109
     filtered: 100.00
        Extra: Using where
*************************** 2. row ******************** *******
           id: 1
  select_type: SIMPLE
        table: city
         type: ref
possible_keys: idx_fk_country_id
          key: idx_fk_country_id
      key_len: 2
          ref: test.country.country_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

(Root @ localhost) [test]> show warnings \ G
*************************** 1. row ******************** *******
  Level: Note
   Code: 1003
Message: / * select # 1 * / select `city`.`city_id` AS` city_id`, `city`.`city` AS` city`, `city`.`country_id`
AS `country_id`,` city`.`last_update` AS `last_update` from` test`.`country` join `test`.`city` where
 (( `City`.`country_id` =` country`.`country_id`) and ( `country`.`country` = 'China'))
1 row in set (0.00 sec)

- It can be seen from the above extended use, the more filtered query column
- Secondly, the original SQL statement is rewritten in real time execution, that the original condition 1 = 1 is removed
- For complex SQL statements we can get a clearer real SQL statements to be executed by the way

(Root @ localhost) [test]> CREATE TABLE `actor_part` (
    -> `Actor_id` smallint (5) unsigned NOT NULL AUTO_INCREMENT,
    -> `First_name` varchar (45) NOT NULL,
    -> `Last_name` varchar (45) NOT NULL,
    -> PRIMARY KEY ( `actor_id`),
    -> KEY `idx_actor_last_name` (` last_name`)
    ->) Partition by hash (actor_id) partitions 4;
Query OK, 0 rows affected (0.11 sec)

(Root @ localhost) [test]> insert into actor_part select * from actor;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0

(Root @ localhost) [test]> explain select * from actor_part where actor_id = 10; - when not in use partitions
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | actor_part | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+ ---- + ------- + ------------ + ------------- + --------- ------ + --------- + --------- + ------- + ------ + ------- +
1 row in set (0.00 sec)

(Root @ localhost) [test]> explain partitions select * from actor_part where actor_id = 10; - when using partitions
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
| Id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
| 1 | SIMPLE | actor_part | p2 | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+ ---- + + ------------ + ------------ + ------------- ---- --- + --------------- + --------- + --------- + ------- + - ---- + ------- +
1 row in set (0.00 sec)
- More out of the partitions column
- Proficient in C / C ++ can also find a very good job (Programming)
- MySQL Tutorial: Using tpcc-mysql pressure measurement (Database)
- Talk about Java EE Learning (Programming)
- Nginx Beginner Guide (Server)
- C ++ 11 feature: auto keyword (Programming)
- Learn about EditText little depth (Programming)
- Linux command line to put on your coat GUI (Linux)
- True and false in Perl (Programming)
- How to remove the Linux memory Cache, Buffer and swap space (Linux)
- Linux set the maximum number of open files nofile and nr_open, file-max Description (Linux)
- Python in yield (Programming)
- How to query the role of Linux services (Linux)
- Getting Started with Linux: Learn how to install and access CentOS 7 Remote Desktop on a VPS (Server)
- JavaScript object - Flexible and dangerous (Programming)
- Binary tree and some basic operations with binary list (Programming)
- Android graphic introduction NDK installation and simple jni demo implementation (Linux)
- Use Visual Studio to compile and use WinGDB remote debugging embedded Linux programs (Programming)
- Amazon EC2 server using Rsync + Inotify real-time synchronization (Server)
- Nginx server load balancing dual-system availability (Server)
- To setup CentOS LAMP environment (Server)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.