当前位置: 首页 > 资讯 >

Explain详解(优化查询好帮手)-Part2(possible_keys、key、key_len、ref、rows、filtered、Extra、Json格式的执行计画)

此篇为前篇的延续唷!

方便阅读再贴一次基本的explain及测试表

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.05 sec)
mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的uk_key2二级索引,且是唯一
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3列建立的idx_key_part二级索引,这也是个联合索引

而single_table2跟single_table一样。


possible_keys与key
possible_keys表示可能会用到的索引,key表示分别使用索引比较成本後最终决定使用的索引。

key_len
我们知道要使用索引来查询时,要先搞清楚扫描区间及对应的边界条件,但有些情况下我们希望从执行计画中就可以看出行成扫描区间的边界条件是什麽!这时key_len就可以派上用场。
这边是根据资料储存的类型(固定长度类型或是可变长度类型)所占用的长度及是否可以储存NULL值来计算这个数字,
透过这个数字可以判断出形成扫描区间的边界条件是什麽。

ref
跟type有点关系,显示与索引相等匹配的条件是什麽。

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

以上面的例子,与索引相等匹配的条件就是常数(const)。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子,与索引相等匹配的条件就是ryan_demo_db.single_table2.id。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子,与索引相等匹配的条件就是ryan_demo_db.single_table2.id。

rows
如果是全表资料扫描就是预估的表行数,如果是使用索引查询就是要扫描的索引纪录数。

filtered
计算驱动表扇出的策略。看下面的例子会比较明白。

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

以这个叙述会形成扫描区间(5000,+无限大),边界条件是key1 > 5000
并可以看出使用的索引是idx_key1,使用该索引要扫描的纪录数是5073,而这边的filtered是10,意思就是最佳化查询工具『预测』这5073笔纪录有10%的资料符合common_field = 'a'条件。

extra
额外补充说明的资讯。这边列出一些可能会看不懂的。

  • No tables used:叙述没有from子句会出现
  • Impossible where:where子句内的条件永远为false
  • Using index/Using index condition:前面表示扫描索引的树就可以得到需要的值并不需要回表操作,而後面则是有用索引但却没有生成合适的边界条件减少扫描数量。
  • Using temporary:使用临时表(去重、排序等)

Json格式的执行计画

这个可以辅助我们看出目前的执行计画到底好不好,每个栏位的说明我直接写在下面的内容里。

mysql> explain format=json select * from single_table s1 inner join single_table2 s2 on s1.key1 = s2.key2  where s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,//整个查询只有一个select关键字,id为1
    "cost_info": {
      "query_cost": "4105.04"//查询预计成本4105.04
    },
    "nested_loop": [//使用槽状结构回圈连接演算法执行
      {
        "table": {
          "table_name": "s2",//驱动表
          "access_type": "ALL",//存取方法
          "possible_keys": [
            "uk_key2"
          ],
          "rows_examined_per_scan": 1,//查一次s2表大约要扫描的纪录数
          "rows_produced_per_join": 1,//扇出值
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.40",
            "prefix_cost": "0.65",//单次查询s2表的成本
            "data_read_per_join": "1K"//读取的资料大小
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      },
      {
        "table": {
          "table_name": "s1",//被驱动表
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 10146,
          "rows_produced_per_join": 101,
          "filtered": "1.00",
          "using_join_buffer": "hash join",//当被驱动表不能有效利用索引,mysql会分配一块join_buffer给它加快速度
          "cost_info": {
            "read_cost": "3698.55",
            "eval_cost": "40.58",
            "prefix_cost": "4105.04",
            "data_read_per_join": "153K"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`ryan_demo_db`.`s1`.`common_field` = 'a') and (cast(`ryan_demo_db`.`s1`.`key1` as double) = cast(`ryan_demo_db`.`s2`.`key2` as double)))"
        }
      }
    ]
  }
}
1 row in set, 3 warnings (0.00 sec)

ERROR: 
No query specified

Extended Explain

可以在exlain後使用show warnings显示与这个查询计划有关的扩充资讯。
Level:通常就是Note
Code:通常就是1003
Message:如code是1003,这边显示就是查询最佳化工具将叙述重新定义後的叙述!

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `ryan_demo_db`.`single_table`.`id` AS `id`,`ryan_demo_db`.`single_table`.`key1` AS `key1`,`ryan_demo_db`.`single_table`.`key2` AS `key2`,`ryan_demo_db`.`single_table`.`key3` AS `key3`,`ryan_demo_db`.`single_table`.`key_part1` AS `key_part1`,`ryan_demo_db`.`single_table`.`key_part2` AS `key_part2`,`ryan_demo_db`.`single_table`.`key_part3` AS `key_part3`,`ryan_demo_db`.`single_table`.`common_field` AS `common_field` from `ryan_demo_db`.`single_table` where ((`ryan_demo_db`.`single_table`.`key1` > '5000') and (`ryan_demo_db`.`single_table`.`common_field` = 'a'))
1 row in set (0.04 sec)

相关文章:

  • 我虽是灾难吸引器-但真的没有发色情连结
  • 资料治理法规(Data Governance for Regulation)
  • [Day30] Angular 的 Routing
  • Day14 参加职训(机器学习与资料分析工程师培训班),Django实作 & 深度学习
  • 2021 — 找工作 (下)
  • xlsx档与json档转换
  • [Day14] 永丰shioaji API,期货篇
  • .NET 新手 无痛入职 _ Day2 环境与框架
  • Day 30 从土里冒嫩芽
  • Stream Processing (1-1) - Transmitting Event Streams
  • 《Day29》Oracle Database 实体档案
  • 模板中的 Directive 指令 (下)
  • 解决venv中无法安装numpy的问题(Could not build wheels for numpy which use PEP 517)
  • Day02 何谓Django?
  • [Day 04] - 用Spring Boot连接Mongo DB
  • PHP安装环境怎么搭建(LAMP环境怎么搭建):教你搭建PHP+Mysql服务器环境
  • 软件分享:xshell6/xftp6个人版下载,无需破解,永久免费使用
  • Windows服务器推荐:便宜好用稳定靠谱的国外Windows服务器推荐
  • Gutenberg 10.4 在自定义程序中引入了块小工具
  • 让 Rank Math SEO 输出关键词 keywords meta 字段信息
  • 移动怎么开通香港电话号码
  • 阿里云国际版怎么注册绑卡购买香港美国新加坡云主机【2020年最新教程】
  • Astra主题产品方案名称更新,附减价20%优惠码
  • 亚马逊卖家教程入门:如何做好亚马逊?教你怎么做亚马逊
  • Goolge play怎么更改地区下载Apps : 谷歌应用商店更改地区教程
  • 最便宜的国外VPS推荐:5美金以下的VPS大全
  • VPS优惠信息:阿里云/限时活动/新用户1C2G1M/69元/年起
  • 教程/魔改BBR 一键安装脚本 for CentOS/Debian 7+
  • 最新虚拟信用卡无限开卡渠道大全
  • 微信小程序搭建教程:怎么用CentOS搭建小程序服务器