MySQL explain分析执行计划

首先表结构为：

mysql> show create table film \G;
*************************** 1. row ***************************
       Table: film
Create Table: CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

解释查询语句：

mysql> explain select * from film where rating > 9\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

select_type：常见取值SIMPLE（简单表，即不使用表连接和子查询），PRIMARY（主查询，即外层查询），UNION（UNIION第二个及后面的查询），SUBQUERY（子查询中第一个select）
table：表名
type：查找方式，一般有ALL,index,range,ref,eq_ref,const,system,NULL（从左往右，执行效率从差到好）

type=ALL，全表扫描，不使用条件查询，或查询条件非索引字段，上面例子即为全表扫描

type=index，索引全扫描，MySQL匹配整个索引来查询匹配的行

mysql> explain select title from film\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_title
      key_len: 514
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

type=range，索引范围扫描，常见于<,<=,>,>=,between等操作

mysql> explain select *  from payment where customer_id >= 300 and customer_id <= 350\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 1350
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回某个单值的记录行

mysql> explain select * from payment where customer_id=350\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: const
         rows: 23
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

或在关联查询中，使用外键连接

mysql> explain select b.*, a.* from payment a, customer b where a.customer_id = b.customer_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.b.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

type=eq_ref，类似ref，区别是使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单地说就是多表连接中使用primary key或者unique index作为关联条件

mysql> explain select * from film a, film_text b where a.film_id = b.film_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.b.film_id
         rows: 1
     filtered: 100.00
        Extra: Using where
2 rows in set, 1 warning (0.01 sec)

type=const/system，单表最多有一行匹配，查询起来非常迅速，例如根据主键或唯一索引查询

mysql> explain select * from payment where payment_id = 30\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

type=NULL表示不用访问表或索引，直接就能返回

mysql> explain select 1 from dual where 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.01 sec)

possible_keys：表示查询时可能会用到的索引
keys：表示查询时实际用到的索引
key_len：索引字段的长度
rows：扫描的行数，行数大小影响查询效率
Extra：执行情况的说明和描述

posted on 2022-04-04 21:18 走过路过别错过阅读(49) 评论(0) 收藏举报

刷新页面返回顶部

导航

公告

MySQL explain分析执行计划