Um den MySQL-Ausführungsplan zu erhalten, können wir ihn über die EXPLAIN-Methode anzeigen. Die EXPLAIN-Methode scheint einfach zu sein, enthält aber tatsächlich viel Inhalt, insbesondere den Typ das Ausgabeergebnis. Das Verständnis dieser verschiedenen Typen ist für unsere SQL-Optimierung sehr wichtig. In diesem Artikel wird nur die Typspalte in den Explian-Ausgabeergebnissen beschrieben und demonstriert.
Eine vollständige Beschreibung der Explian-Ausgabe finden Sie unter: MySQL EXPLAIN SQL-Ausgabeinformationsbeschreibung
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 全表扫描
1、all-- 环境描述 (root@localhost) [sakila]> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.26 | +---------------+--------+ MySQL采取全表遍历的方式来返回数据行,等同于Oracle的full table scan (root@localhost) [sakila]> explain select count(description) from film; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | film | ALL | NULL | NULL | NULL | NULL | 1000 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 2、index MySQL采取索引全扫描的方式来返回数据行,等同于Oracle的full index scan (root@localhost) [sakila]> explain select title from film \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 Extra: Using index1 row in set (0.00 sec) 3、 range 索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询 等同于Oracle的index range scan (root@localhost) [sakila]> explain select * from payment where customer_id>300 and customer_id<400\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 2637 Extra: Using where1 row in set (0.00 sec) (root@localhost) [sakila]> explain select * from payment where customer_id in (200,300,400)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 86 Extra: Using index condition1 row in set (0.00 sec) 4、ref 非唯一性索引扫描或者,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找 (root@localhost) [sakila]> explain select * from payment where customer_id=305\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 25 Extra: 1 row in set (0.00 sec) idx_fk_customer_id为表payment上的外键索引,且存在多个不不唯一的值,如下查询 (root@localhost) [sakila]> select customer_id,count(*) from payment group by customer_id -> limit 2; +-------------+----------+ | customer_id | count(*) |+-------------+----------+ | 1 | 32 || 2 | 27 | +-------------+----------+-- 下面是非唯一前缀索引使用ref的示例 (root@localhost) [sakila]> create index idx_fisrt_last_name on customer(first_name,last_name); Query OK, 599 rows affected (0.09 sec) Records: 599 Duplicates: 0 Warnings: 0(root@localhost) [sakila]> select first_name,count(*) from customer group by first_name -> having count(*)>1 limit 2; +------------+----------+| first_name | count(*) | +------------+----------+| JAMIE | 2 || JESSIE | 2 | +------------+----------+2 rows in set (0.00 sec) (root@localhost) [sakila]> explain select first_name from customer where first_name='JESSIE'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer type: refpossible_keys: idx_fisrt_last_name key: idx_fisrt_last_name key_len: 137 ref: const rows: 2 Extra: Using where; Using index1 row in set (0.00 sec) (root@localhost) [sakila]> alter table customer drop index idx_fisrt_last_name; Query OK, 599 rows affected (0.03 sec) Records: 599 Duplicates: 0 Warnings: 0--下面演示出现在join是ref的示例 (root@localhost) [sakila]> explain select b.*,a.* from payment a inner join -> customer b on a.customer_id=b.customer_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 13 Extra: NULL2 rows in set (0.01 sec) 5、eq_ref 类似于ref,其差别在于使用的索引为唯一索引,对于每个索引键值,表中只有一条记录与之匹配。 多见于主键扫描或者索引唯一扫描。 (root@localhost) [sakila]> explain select * from film a join film_text b -> on a.film_id=b.film_id; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 1000 | NULL | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.film_id | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ (root@localhost) [sakila]> explain select title from film where film_id=5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | film | const | PRIMAR | PRIMARY | 2 | const | 1 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+6、const、system: 当MySQL对查询某部分进行优化,这个匹配的行的其他列值可以转换为一个常量来处理。 如将主键或者唯一索引置于where列表中,MySQL就能将该查询转换为一个常量 (root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique); Query OK, 0 rows affected (0.05 sec) (root@localhost) [sakila]> insert into t1 values(1,'robin'),(2,'jack'),(3,'henry'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost) [sakila]> explain select * from (select * from t1 where ename='robin')x; +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | t1 | const | ename | ename | 23 | const | 1 | NULL | +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ 2 rows in set (0.00 sec) 7、type=NULL MySQL不用访问表或者索引就可以直接得到结果 (root@localhost) [sakila]> explain select sysdate();+----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)
Um den MySQL-Ausführungsplan zu erhalten, können wir ihn über die EXPLAIN-Methode anzeigen Scheint einfach zu sein, enthält aber tatsächlich viel Inhalt, insbesondere die Typspalte im Ausgabeergebnis. Das Verständnis dieser verschiedenen Typen ist für unsere SQL-Optimierung sehr wichtig. In diesem Artikel wird nur die Typspalte in den Explian-Ausgabeergebnissen beschrieben und demonstriert.
Eine vollständige Beschreibung der Explian-Ausgabe finden Sie unter: MySQL EXPLAIN SQL-Ausgabeinformationsbeschreibung
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 全表扫描
1、all-- 环境描述 (root@localhost) [sakila]> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.26 | +---------------+--------+MySQL采取全表遍历的方式来返回数据行,等同于Oracle的full table scan (root@localhost) [sakila]> explain select count(description) from film; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | film | ALL | NULL | NULL | NULL | NULL | 1000 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 2、index MySQL采取索引全扫描的方式来返回数据行,等同于Oracle的full index scan (root@localhost) [sakila]> explain select title from film \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 1000 Extra: Using index1 row in set (0.00 sec) 3、 range 索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询 等同于Oracle的index range scan (root@localhost) [sakila]> explain select * from payment where customer_id>300 and customer_id<400\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 2637 Extra: Using where1 row in set (0.00 sec) (root@localhost) [sakila]> explain select * from payment where customer_id in (200,300,400)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 86 Extra: Using index condition1 row in set (0.00 sec) 4、ref 非唯一性索引扫描或者,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找 (root@localhost) [sakila]> explain select * from payment where customer_id=305\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 25 Extra: 1 row in set (0.00 sec) idx_fk_customer_id为表payment上的外键索引,且存在多个不不唯一的值,如下查询 (root@localhost) [sakila]> select customer_id,count(*) from payment group by customer_id -> limit 2; +-------------+----------+ | customer_id | count(*) |+-------------+----------+ | 1 | 32 || 2 | 27 | +-------------+----------+-- 下面是非唯一前缀索引使用ref的示例 (root@localhost) [sakila]> create index idx_fisrt_last_name on customer(first_name,last_name); Query OK, 599 rows affected (0.09 sec) Records: 599 Duplicates: 0 Warnings: 0(root@localhost) [sakila]> select first_name,count(*) from customer group by first_name -> having count(*)>1 limit 2; +------------+----------+| first_name | count(*) | +------------+----------+| JAMIE | 2 || JESSIE | 2 | +------------+----------+2 rows in set (0.00 sec) (root@localhost) [sakila]> explain select first_name from customer where first_name='JESSIE'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer type: refpossible_keys: idx_fisrt_last_name key: idx_fisrt_last_name key_len: 137 ref: const rows: 2 Extra: Using where; Using index1 row in set (0.00 sec) (root@localhost) [sakila]> alter table customer drop index idx_fisrt_last_name; Query OK, 599 rows affected (0.03 sec) Records: 599 Duplicates: 0 Warnings: 0--下面演示出现在join是ref的示例 (root@localhost) [sakila]> explain select b.*,a.* from payment a inner join -> customer b on a.customer_id=b.customer_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 13 Extra: NULL2 rows in set (0.01 sec) 5、eq_ref 类似于ref,其差别在于使用的索引为唯一索引,对于每个索引键值,表中只有一条记录与之匹配。 多见于主键扫描或者索引唯一扫描。 (root@localhost) [sakila]> explain select * from film a join film_text b -> on a.film_id=b.film_id; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 1000 | NULL | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.film_id | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ (root@localhost) [sakila]> explain select title from film where film_id=5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | film | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 6、const、system: 当MySQL对查询某部分进行优化,这个匹配的行的其他列值可以转换为一个常量来处理。 如将主键或者唯一索引置于where列表中,MySQL就能将该查询转换为一个常量 (root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique); Query OK, 0 rows affected (0.05 sec) (root@localhost) [sakila]> insert into t1 values(1,'robin'),(2,'jack'),(3,'henry'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost) [sakila]> explain select * from (select * from t1 where ename='robin')x; +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL || 2 | DERIVED | t1 | const | ename | ename | 2 3 | const | 1 | NULL | +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ 2 rows in set (0.00 sec) 7、type=NULL MySQL不用访问表或者索引就可以直接得到结果 (root@localhost) [sakila]> explain select sysdate(); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec)
Das Obige ist der Inhalt des Beispiels für den MySQL-Verbindungstyp. Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php. cn)!