search
HomeDatabaseMysql TutorialMySQL EXPLAIN SQL 输出信息描述

EXPLAIN语句能够被用于获取一些关于SQL执行时的相关信息,比如表的连接顺序,对表的方式方式等等。通过对该相关信息进行进一步的

EXPLAIN语句能够被用于获取一些关于SQL执行时的相关信息,比如表的连接顺序,对表的方式方式等等。通过对该相关信息进行进一步的分析,我们可以通过对表添加适当的索引,以及优化连接顺序,使用提示等等手段来达到使SQL高效运行的目的。本文描述了EXPLAIN的用法并给出了相关示例。

一、EXPLAIN概述 EXPLAIN 语句主要是用于解析SQL执行计划,通过分析执行计划采取适当的优化方式提高SQL运行的效率。 EXPLAIN 语句输出通常包括id列,select_type,table,type,possible_keys,key等等列信息 MySQL 5.6.3后支持SELECT, DELETE, INSERT,REPLACE, and UPDATE. EXPLAIN EXTENDED支持一些额外的执行计划相关的信息 EXPLAIN PARTITIONS支持基于分区表查询执行计划的相关信息 二、EXPLAIN输出列描述 -- 下面通过示例来展示EXPLAIN输出列 (root@localhost) [sakila]> explain select sum(amount) from customer a, -> payment b where 1=1 and a.customer_id=b.customer_id and -> email='JANE.BENNETT@sakilacustomer.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: sakila.a.customer_id rows: 14 Extra: 1、各列表示的意义 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、各列上的具体描述 id: 包含一组数字,表示查询中执行select子句或操作表的顺序 id相同,执行顺序由上至下,否则id值越大(通常子查询会产生)优先级越高,越先被执行 id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行 select_type: 表示查询中每个select子句的类型(简单 OR复杂) select_type Value Meaning ------------- ----------------------------------------------- SIMPLE Simple SELECT (not using UNION or subqueries) PRIMARY Outermost SELECT 最外层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(通常为相关子查询) DERIVED Derived table SELECT (subquery in FROM clause) MATERIALIZED Materialized subquery UNCACHEABLE SUBQUERY A subquery for which the result cannot 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) table: 从哪个表(表名)上输出行记录,也可能是下列值: • : The row refers to the union of the rows with id values of M and N. • : 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. • : The row refers to the result of a materialized subquery for the row with an id value of N. partitions: 查询匹配的记录来自哪一个分区,当使用EXPLAIN,分区PARTITIONS关键字被指定时 type: 连接类型 system 表只有一行 const 表最多只有一行匹配,通用用于主键或者唯一索引比较时 eq_ref 每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种, 特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引 ref 如果每次只匹配少数行,那就是比较好的一种,使用=或,可以是左覆盖索引或非主键或非唯一键 fulltext 全文搜索 ref_or_null 与ref类似,但包括NULL index_merge 表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。 这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话) unique_subquery 在in子查询中,就是value in (select...)把形如“select unique_key_column”的子查询替换。 PS:所以不一定in子句中使用子查询就是低效的! index_subquery 同上,但把形如”select non_unique_key_column“的子查询替换 range 常数值的范围 index a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index); b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index); c.如果Extra中Using Index与Using Where同时出现的话,则是利用索引查找键值的意思; d.如单独出现,则是用读索引来代替读行,但不用于查找 all 全表扫描 possible_keys: 指出MySQL能使用哪个索引在表中找到行。 查询涉及到的字段上若存在索引则该索引将被列出,但不一定被查询使用。 如果改列为NULL,说明该查询不会使用到当前表上的相关索引,考虑是否有必要添加索引 key 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL 也可能存在key不等于possible_keys的情形,即possible_keys不适合提取所需的行 而查询所选择的列在使用其他索引时更高效 TIPS:查询中若使用了覆盖索引,则该索引仅出现在key列表中 key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度 ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值 rows 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数 对于InnoDB,该值为预估,不一定精确 Extra 包含不适合在其他列中显示但十分重要的额外信息 三、使用EXPLAIN EXTENDED 示例 (root@localhost) [sakila]> 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: sakila.country.country_id rows: 1 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec) (root@localhost) [sakila]> 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 `sakila`.`country` join `sakila`.`city` where ((`city`.`country_id` = `country`.`country_id`) and (`country`.`country` = 'China')) 1 row in set (0.00 sec) -- 从上面的extended使用可以看出,查询中多出了filtered列 -- 其次原来的SQL语句真正在执行的时候被改写,即原来的1=1的条件被去掉 -- 对于复杂的SQL语句我们可以通过该方式获得一个比较清晰的真正被执行的SQL语句 四、EXPLAIN PARTITIONS示例 (root@localhost) [sakila]> 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, -> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> 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) [sakila]> insert into actor_part select * from actor; Query OK, 200 rows affected (0.02 sec) Records: 200 Duplicates: 0 Warnings: 0 (root@localhost) [sakila]> explain select * from actor_part where actor_id=10; -- 未使用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) [sakila]> explain partitions select * from actor_part where actor_id=10; -- 使用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) -- 多出了partitions列

本文永久更新链接地址

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

How can you monitor MySQL server health and performance?How can you monitor MySQL server health and performance?Apr 26, 2025 am 12:15 AM

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

Compare and contrast MySQL and MariaDB.Compare and contrast MySQL and MariaDB.Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools