EXPLAIN 描述了 MySQL 是如何计划, 去执行给定的查询的. 即这是一个预执行的考虑, 不要和查询性能的分析混淆了.
在评估了许多可能的执行方式后, EXPLAIN 打印出了最佳的执行计划.
在 "possible_keys" 中有可以简明地看到其他可能选择的计划, 但一般来说你需要在 "OPTIMIZER_TRACE" 看这些信息.
我们推荐一直使用 EXPLAIN FORMAT=JSON, 因为它会打印出查询代价(默认格式则没有). 查询代价很关键, 它让我们可以量化地思考和交流, 曾听到一些数据库从业者说 "创建临时表不好","连表查询不好", 你很难直接回应这些评价, 因为缺少必要的上下文信息; 水在过多的情况下同样是对人有害的.
和上面一样, 你也可以用 EXPLAIN FORMAT=JSON FOR CONNECTION <connection_id > 去看一个运行中的数据库连接是如何最佳化执行的. 这在诊断偶现的错误时是很有用的, 因为数据和底层参数的修改会影响执行计划的选择.
例子 1: 查询亚洲中人口多于 500W 的国家, 一次全表扫描
- EXPLAIN FORMAT=JSON
- SELECT * FROM Country WHERE continent='Asia' and population> 5000000;
- {
- "query_block": {
- "select_id": 1,
- "cost_info": {
- "query_cost": "53.80" # 这次查询的代价
- },
- "table": {
- "table_name": "Country",
- "access_type": "ALL", # ALL 即全表扫描
- "rows_examined_per_scan": 239, # 访问了全表 239 行
- "rows_produced_per_join": 11,
- "filtered": "4.76",
- "cost_info": {
- "read_cost": "51.52",
- "eval_cost": "2.28",
- "prefix_cost": "53.80",
- "data_read_per_join": "2K"
- },
- "used_columns": [
- ...
- ],
- "attached_condition": "((`world`.`Country`.`Continent` ='Asia') and (`world`.`Country`.`Population`> 5000000))"
- }
- }
- }
例子 1 中 EXPLAIN 表明了这次查询会执行全表扫描, 需要花费 53.80 的代价. 信息表明没有可用的索引 possible_keys(这一项通常就在 access_type 后面).
rows_examined_per_scan 这个数字 239 是全表扫描的行数, 近似全世界国家的总数, 需要指出这个数字不会是百分之百精确的, 因为那样会影响 EXPLAIN 的性能.
attached_condition 附加状态这一项表明在读取每一行时, 会用到过滤器, 条件就是 "在亚洲中, 人口多于 500W". 在有索引的情况下, 这些条件在读取行之前就可以判断了, 但条件被列出来就说明不是这种情况.
我们来对比一下 扫描过的行数(239) 和 返回结果的行数(32), 发送给客户端的结果行在执行完查询才能得知. 这次查询中服务端扫描了是返回结果 7.5 倍 的行数, 表明是可以优化的.
非常小的比例 (甚至 1:1) 并不意味着不能优化. 要追求更好, 可以再优化分区或分页大小.
需要指出, 扫描 239 行不算多, 而且所有行都可以放进内存. 但全表扫描还是很消耗内存的, 而随着表的增大性能会下降得越来越快. 添加索引可以改善性能, 并在数据增长时维持这种性能.
译自:
Explain - The Unofficial MySQL 8.0 Optimizer Guide
来源: http://www.jianshu.com/p/b2d8389006a5