关于mysql优化:MySQL优化3explain分析执行计划字段说明

40次阅读

共计 23528 个字符，预计需要花费 59 分钟才能阅读完成。

应用 explain 的 12 个字段阐明

0. 前情提要: 用到的几个表阐明:

0.1. goods 表和 goods2 两个表构造和数据雷同(复制的表)-test 库

mysql> show create table goods;
CREATE TABLE `goods` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `price` double DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=150001 DEFAULT CHARSET=utf8

mysql> show create table goods2;
CREATE TABLE `goods2` (`id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `price` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

mysql> select count(*) from goods;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from goods2;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.05 sec)

0.2. t2 表是 goods 表中的前 10 条 -test 库

mysql> select * from t2;
+----+---------+--------+
| id | name    | price  |
+----+---------+--------+
|  1 | 商品 1   | 200.17 |
|  2 | 商品 2   | 200.87 |
|  3 | 商品 3   | 200.81 |
|  4 | 商品 4   | 200.43 |
|  5 | 商品 5   | 200.73 |
|  6 | 商品 6   | 200.36 |
|  7 | 商品 7   | 200.61 |
|  8 | 商品 8   | 200.98 |
|  9 | 商品 9   | 200.06 |
| 10 | 商品 0   | 200.38 |
+----+---------+--------+
10 rows in set (0.00 sec)
mysql> show create table t2;
CREATE TABLE `t2` (`id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `price` double DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

0.3. sakila 库的 film 表:

mysql> show create table film;
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.00 sec)

mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

0.4. sakila 库的 film_category 表

mysql> show create table film_actor;
CREATE TABLE `film_actor` (`actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> select count(*)from  film_actor;
+----------+
| count(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.00 sec)

0.5. sakila 库的 film_category 表

mysql> show create table film_category;
CREATE TABLE `film_category` (`film_id` smallint(5) unsigned NOT NULL,
  `category_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`,`category_id`),
  KEY `fk_film_category_category` (`category_id`),
  CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

mysql> select count(*) from film_category;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

0.6. sakila 库的 actor 表

mysql> show create table actor;
CREATE TABLE `actor` (`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`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4  

mysql> select count(*) from actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

explain 一共有 12 个字段, 上面具体介绍:

1. id= 步骤编号: 示意以后执行打算的第几步

执行打算可能分很多步, 每一行示意一个步骤, id= 1 示意是第 1 步;

如果编号 id 雷同, 执行程序就是从上到下;

id 越大越 先执行

id:1

1.1 id 越大的越先执行

mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film  | NULL       | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | SUBQUERY    | actor | NULL       | index | NULL          | idx_actor_last_name | 182     | NULL |  200 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

上面的 2.row id=2 最大, 第二步就先执行;

第二步:

  1. 是子查问;
  2. 连贯类型是 index;
  3. 用到的索引名是 idx_actor_last_name;
  4. 扫描的索引长度 182;
  5. 可能会检测的行数 200;
  6. 扩大信息: 应用了索引;

1.2 id 雷同的后面的先执行

2. select_type= 查问类型: 示意是简略查问还是简单查问

select_type=primary 简单查问 = 蕴含 union 查问或者蕴含子查问

select_type=simple 简略查问 = 不蕴含 union, 也不蕴含子查问;

select_type=union

select_type=union result

select_type=dependent union

select_type=subquery

select_type=derived

select_type=materializaton

2.1. select_type=simple: 简略查问

示意不须要 union 操作, 或者不蕴含子查问

有连贯查问时, 外层的查问为 simple, 且只有一个;

2.2. select_type=primary: 简单查问(有 union 操作或有子查问)

简单查问: 有 union 操作; 或者有子查问;

2.2.1 union 查问实例:

mysql> explain select film_id from film union all select film_id from film_actor;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

有 union 查问的步骤, 位于最外层的查问的 select_type 即为 primary, 且只有一个;

2.3 select_type=union 连贯查问

union 查问 = 连贯查问, 连贯的是 2 个 select 查问:

第一个查问是: derived 派生的表; 除了一个之外, 第二个当前的表 select_type 都是 union;

2.4 select_type=union result 此类型 id 为空(mysql5.7.30 当前都没有了)

2.5 select_type=dependent union

此查问跟 union 一样, 呈现在 union 或 union all 语句中, 然而这个查问要受内部查问的影响;

mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+
| id | select_type        | table         | partitions | type   | possible_keys  | key            | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | film_category | NULL       | ALL    | NULL           | NULL           | NULL    | NULL | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | film          | NULL       | eq_ref | PRIMARY        | PRIMARY        | 2       | func |    1 |   100.00 | Using index |
|  3 | DEPENDENT UNION    | film_actor    | NULL       | ref    | idx_fk_film_id | idx_fk_film_id | 2       | func |    5 |   100.00 | Using index |
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

2.6 select_type=subquery 子查问

除了 from 子句中蕴含的子查问外, 其余中央呈现的子查问都可能是 subquery;

mysql> explain select (select 1 from actor limit 1) from film;
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film  | NULL       | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | SUBQUERY    | actor | NULL       | index | NULL          | idx_actor_last_name | 182     | NULL |  200 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

2.7 select_type=derived from 子句中呈现的子查问也叫派生表

然而在 5.7 当前的版本中没有 derived 这个类型了, 做了优化;

5.6 还是有的, 看: 5.7.30:

mysql> explain select (select 1 from actor where film_id=1) from (select * from film where film_id=1) der;
+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key                 | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | film  | NULL       | const | PRIMARY       | PRIMARY             | 2       | const |    1 |   100.00 | Using index              |
|  2 | DEPENDENT SUBQUERY | actor | NULL       | index | NULL          | idx_actor_last_name | 182     | NULL  |  200 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------------------+---------+-------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

2.8 select_type=materializaton 物化(具体化): 将子查问后果作为一个长期表来放慢执行速度

失常来讲是常驻内存, 下次查问会再次援用长期表;

如果是一个大表, 大表作为一个子查问, 就会在第一次查问时生成一个长期表, 前面每次援用;

mysql> explain select * from (select * from goods) s where id in (select id from goods2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref  | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE       | goods       | NULL       | ALL    | id            | NULL       | NULL    | NULL | 149548 |   100.00 | NULL        |
|  1 | SIMPLE       | <subquery3> | NULL       | eq_ref | <auto_key>    | <auto_key> | 8       | func |      1 |   100.00 | Using where |
|  3 | MATERIALIZED | goods2      | NULL       | ALL    | NULL          | NULL       | NULL    | NULL | 149692 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

subquery3 的子查问依赖 3 的物化的, 物化先执行, 而后是 goods, 最初在是子查问;

物化前提是: 表记录比拟多

3. table= 表名: 示意以后这一步波及的表都有哪些

  1. table 示意查问应用的表名, 如果查问应用了别名, 显示的就是别名;
  2. 如果并不波及数据表的操作, table=null;
  3. 如果显示为 <derived N> 就示意这是个长期表, N 就是步骤的 id;
  4. 如果显示为 <union M, N> 也是个长期表, 示意, 是 M, N 两个步骤 id 的后果集;

table:film

4. partitions= 分区情况

partitions:NULL

5. type= 连贯类型: 用的全表扫描 / 有没有走索引

type 的效率由高到低排序:

type=system 最高效;
type=const 用到索引: 且是惟一索引或主键且 + where 返回了只有 1 行;
type=eq_ref 用到索引: 连贯查问中前表每个后果, 都只匹配后表一行后果. 且比拟是惟一索引, 查问效率较高. 
type=ref  用到索引: 多表的 join 查问, 非惟一或非主键索引, 或者是应用了最左前缀 规定索引的查问.
type=fulltext 用到全文索引
type=ref_or_null  用到索引, 相当于 ref+ xxx is null 的条件
type=unique_subquery 用于在 in 模式查问, 子查问返回不反复的惟一值(略)
type=index_subquery 用于 in 模式子查问用到辅助索引或者 in 常数列表, 子查问可能返回反复值 (略)
type=range 字段上有索引的范畴扫描: 常见在索引字段应用 >, <, is null, between, in, like 等运算符的查问中;
type=index_merge 应用了 2 个以上的索引, 常见: and, or 的多个列都有索引, 依据索引查出来进行合并;
type=index 索引从头到尾扫一遍; 不必查表了; select name from t2; name 上有索引, 然而不指定 where 条件;
type=all 性能最差: 全表扫描数据, 而后在 server 层返回数据过滤返回合乎的数据;

5.1 type=system 最高效

type=system 表只有 1 行数据 或是空表; 且只用于 myisam 和 memory 表; innodb 的 type 列通常为 all 或 index;

5.2 type=const 惟一索引或主键 + 返回一行记录时

应用惟一索引或主键, 返回后果肯定是 1 行记录的等值 where 条件时, type=const 通常

先来一个一般索引:

mysql> alter table t2 add index(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t2 where id=2;
+----+---------+--------+
| id | name    | price  |
+----+---------+--------+
|  2 | 商品 2   | 200.87 |
+----+---------+--------+
1 row in set (0.00 sec)

mysql> explain select * from t2 where id=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | id            | id   | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

看到 type= 试试 ref; 不是 const;

改为惟一索引试试, 先删掉一般索引

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | id       |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> drop index id on t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t2;
Empty set (0.00 sec)

新建惟一索引, 再试试:

mysql> alter table t2 add unique(id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where id>3;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | range | id            | id   | 8       | NULL |    7 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t2 where id=2;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | const | id            | id   | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

id>3 是 type=range; id= 2 时, 惟一索引, type=const

5.3 type=eq_ref 多表 join 查问时, 对于前表的每一行数据, 后表中只返回一行匹配

阐明: t2 和 goods 表中数据是一样的, 只是 goods 中有近 15 万条数据, t2 中只有 10 条且 t2 表 id 有 unique 索引;

mysql> explain select * from t2 left join goods on t2.id=goods.id;
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL | NULL    | NULL       |   10 |   100.00 | NULL  |
|  1 | SIMPLE      | goods | NULL       | eq_ref | id            | id   | 8       | test.t2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from t2 right join goods on t2.id=goods.id;
+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref           | rows   | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+
|  1 | SIMPLE      | goods | NULL       | ALL    | NULL          | NULL | NULL    | NULL          | 149548 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | id            | id   | 8       | test.goods.id |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+---------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

5.4 type=ref 多表的 join 查问, 非惟一或非主键索引, 或者是应用了最左前缀规定索引的查问.

mysql> explain select * from film where title = 'film2';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ref  | idx_title     | idx_title | 514     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

title 列不是惟一索引, 也不是主键列; 然而它也是索引列:

KEY idx_title (title),

5.5 type=fulltext 用到全文索引

全文索引的优先级很高, 如果全文索引和一般索引同时存在 ,mysql 不论性能代价, 会优先应用全文索引;

5.6 type=ref_or_nul 理论用的少: 相似 ref, 但减少了 null 值的比拟

mysql> alter table t2 add index(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where name='商品 1' or name is null;
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | ref_or_null | name          | name | 33      | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

如果没有 null 判断, 就是 ref 了;

5.7 type=unique_subquery 有索引: 在 where 中 in, 子查问返回不反复的惟一值

5.8 type=index_subquery 用于 in 模式子查问用到辅助索引或者 in 常数列表, 子查问可能返回反复值

5.9 type=range 有索引的范畴扫描: 常见在索引字段应用 >, <, is null, between, in, like 等运算符的查问中;

5.10 type=index_merge 应用了 2 个以上的索引, 常见: and, or 的多个列都有索引, 依据索引查出来进行合并;

5.11 type=index 索引从头到尾扫一遍; 不查表; select name from t2; name 上有索引, 然而不指定 where 条件;

5.12 type=all 性能最差: 全表扫描数据, 而后在 server 层返回数据过滤返回合乎的数据;

小结: 好的查问个别至多达到 range 级别, 最好达到 ref;

放大下范畴: 常见的有:

system/const/eq_ref/ref/range/index/all

6. possible_keys= 可能用到的索引: 以后这一步有可能用到的索引有哪些, 都列出来

possible_keys:NULL

7. key= 索引: 确定用了的索引

key:NULL

8. key_len= 索引长度: 越小越好(越短越好)

key_len:NULL

9. ref= 索引具体在哪一列上

ref:NULL

9.1 如果应用的常数等值查问, 会显示 const;

9.2 如果是连贯查问, 被驱动表 的执行打算此处会显示 驱动表 的关联字段;

mysql> explain select * from t2 left join goods on t2.id=goods.id;
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL | NULL    | NULL       |   10 |   100.00 | NULL  |
|  1 | SIMPLE      | goods | NULL       | eq_ref | id            | id   | 8       | test.t2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

9.3 如果是条件应用了表达式或函数, 或条件列产生了外部隐式转换, 此处显示为 func;

mysql> explain select * from film_category where film_id in (select film_id from film union all select film_id from film_actor);
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+
| id | select_type        | table         | partitions | type   | possible_keys  | key            | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | film_category | NULL       | ALL    | NULL           | NULL           | NULL    | NULL | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | film          | NULL       | eq_ref | PRIMARY        | PRIMARY        | 2       | func |    1 |   100.00 | Using index |
|  3 | DEPENDENT UNION    | film_actor    | NULL       | ref    | idx_fk_film_id | idx_fk_film_id | 2       | func |    5 |   100.00 | Using index |
+----+--------------------+---------------+------------+--------+----------------+----------------+---------+------+------+----------+-------------+

10. rows= 以后这一步可能会检测的行数

rows:1000

估算行, 非准确值;

11. filtered= 过滤后返回数据的百分比: 通过 server 过滤后理论返回客户端的百分比

filtered:100.00

12. Extra= 扩大信息: 有没有排序 / 有没有用长期表, 很多种类型

Extra:NULL

12.1 no table used

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

12.2 using index 应用到了索引

mysql> explain select name from t2 ;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | name | 33      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

12.3 NULL: 查问到的列有未被索引笼罩到的(就是查了几个列, 其中有的没索引)

mysql> explain select name, price from t2 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

不是纯正用索引, 然而也用到了索引;

或者, 就是未被索引笼罩到; 查了没用索引的列, 也都是 NULL;

12.4 using where 查问的 where 条件是没有索引的

mysql> explain select price from t2 where price=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select price from t2 where name='s' and price=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | name          | name | 33      | const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

where 语句中有的列没有索引;

12.5 using where using index: 查问的合乎索引, 然而不是第一个列, 用不到索引

mysql> alter table t2 add index(name, price);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t2 where price=2;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | name_2 | 42      | NULL |   10 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

相当于又找到索引, 又用了 where, 为什么呢? 有索引, 然而用不到, 最初还是用了 where 扫表;

12.6 using index condition: 与 using where 类似, 查问的列没有齐全被索引笼罩

12.7 using temporary: 应用了长期表存储两头后果

12.8 using filesort: 也要思考优化: 对后果应用了内部索引排序, 而不是依照索引秩序从表里读数据行.

正文完
mysql优化
发表至： mysql优化
2020-09-12
 0
关于mysql优化:霜皮剥落紫龙鳞下里巴人再谈数据库SQL优化索引一级二级聚簇非聚簇原理
关于mysql优化:mysql优化预备篇开启慢查询日志
关于mysql优化:MySQL-binlog-远程备份方法详解
关于mysql优化:MySQL优化2慢查询日志工具ptquerydigest