【官方】MariaDB Optimization and Indexes 中文版(五)

ACMUG征集原创技术文章。详情请添加 A_CMUG或者扫描文末二维码关注我们的微信公众号。有奖征稿,请发送稿件至:acmug@acmug.com。
3306现金有奖征稿说明:知识无价,劳动有偿,ACMUG特约撰稿人有奖回报计划(修订版)


作者: MariaDB官方授权资词翻译组翻译

资词翻译组,由数名数据库技术爱好者组成,旨在传播技术,帮助他人,提升自我。目前的成员有:田丽芳,强昌金,王竹峰,侯军伟,吕智超,刘启荣,周彦伟,古雷。
译事三难:信、达、雅。信者,真也,真者,不伪也;达者,至也,至者,无过无不及也;雅者,文学性也,文学性者,当雅则雅当俗则俗也。我们深知能达成此三事,绝非一日之功,亦非常人所能。然,苟利国家生死以,岂因祸福避趋之。我们还是希望竭尽所能,不遗余力,做一点微小的工作,提高姿势水平。由于能力有限,水平一般,有错误不妥之处,还请批评指正,希望能得到大家的资词。

资词翻译组获得MySQL Server团队和MariaDB官方授权,翻译相关技术文章。


Explain

语法

EXPLAIN tbl_name

或者

EXPLAIN [EXTENDED | PARTITIONS] 
  {SELECT select_options | UPDATE update_options | DELETE delete_options}

描述

EXPLAIN语句可以作为DESCRIBE的同义词使用,或者可以作为一种获取MariaDB如何执行SELECT(从MariaDB 10.0.5开始UPDATE和DLETE也同样适用)语句信息的方法:

  • 'EXPLAIN tbl_name' 等同于'DESCRIBE tbl_name'或者'SHOW COLUMNS FROM tbl_name'
  • 当你在 SELECT语句(或者,从MariaDB 10.0.5起,“`UPDATE“`和“`DELETE“`同样适用)之前加上关键词`EXPLAIN`时,MariaDB显示来自于优化器的关于这个查询执行计划的信息。也就是说,MariaDB解释它是怎么处理`SELECT`,`UPDATE`或`DELETE`的,包括表是怎么关联的以及关联顺序的信息。“`EXPLAIN EXTENDED“` 可以提供额外的信息。
  • EXPLAIN PARTITIONS 在MySQL 5.1.5 中被引入,它仅被用于检查含分区表的Query。细节可参考Partition pruning and selection
  • ANALYZE 语句MariaDB 10.1.0开始可用,它执行query并产生EXPLAIN样式的输出,提供实际的和估算的统计数据。
  • MariaDB 10.0.5开始,可以在slow query log中打印`EXPLAIN`输出了。细节可参考EXPLAIN in the Slow Query Log

MariaDB 10.0开始,SHOW EXPLAIN 语句显示运行中的语句的输出。有时候,它的输出比EXPLAIN更接近实际情况。

MariaDB 10.1开始, ANALYZE语句会执行一条语句并返回它的执行计划的信息。它还提供了附加的列,用来检查优化器关于过滤和找到行数的估算值接近现实的程度。

你可以通过在线的EXPLAIN Analyzer 跟他人分享`EXPLAIN`和`EXPLAIN EXTENDED`的输出。

EXPLAIN通过跟SELECT语句一样的方式获取元数据锁,故它需要知道表的元数据,甚至有时需要知道数据。

EXPLAIN … SELECT的列

列名称 描述
id 有序的数字,表示表之间的关联顺序
select_type 这个表来自于哪种类型的SELECT
table 表的别名,子query的物化临时表命名为 <subquery#>
type 数据行是怎么从表里被查询到的(join类型)
possible_keys 从表里取数据时可能用到的键
key 检索数据时用到的键的名字。NULL代表没有用到键
key_len 用到的键的长度字节数(显示我们是否只用到了多字段索引的一部分)
ref 键值的引用类型
rows 通过每个键查询我们能在表里找到的行数的估算值
Extra 关于此次关联的额外信息

接下来描述在EXPLAIN...SELECT中一些表负责的字段的取值:

“select_type”列

select_type列有以下取值:

描述
PRIMARY SELECT是PRIMARY查询
SIMPLE SELECT是SIMAPLE查询
DERIVED SELECT是PRIMARY派生查询
SUBQUERY SELECT是PRIMARY查询的子查询
DEPENDENT SUBQUERY 子查询是依赖查询
UNCACHEABLE SUBQUERY 子查询不可缓存
UNION SELECT是PRIMARY的UNION
UNION RESULT UNION的结果集
DEPENDENT UNION UNION是依赖查询
UNCACHEABLE UNION UNION不可缓存

“Type” 列

这个列包含了表如何被访问的信息。

描述
ALL 对此表进行全表扫描(所有的行被读取)。如果表非常大并作为一个前序表被关联的话,这是非常糟糕的。这发生在优化器找不到任何可用的索引去访问数据的时候
const 在表里仅有一个可能匹配的行。这行数据在优化阶段之前读取,表中的所有字段被作为常量对待。
eq_ref 用唯一索引来查找数据行。这是找到一行的最佳可行的执行计划。
fulltext 用全文索引来访问数据行
index_merge 用几个索引的‘范围’查找,同时对找到的行进行合并。在Key字段中显示使用了哪些键。
index_subquery 这跟ref类似,但是是用来在子查询上的,这些子查询被转换成键查询。
index 在使用的索引上的全扫描。它比ALL好,但是如果索引很大并且表被用做关联前序表的话,也是很糟的。
range 表通过一个键的一个或者多个范围值来访问。
ref_or_null 类似 ‘ref’,但如果第一个值没有找到的话,会有一个额外的对’null’值的查找。这经常发生在子查询里面。
ref 使用非唯一索引或者唯一索引前缀来查找数据行。如果前缀没有匹配很多行的话,这种方式是很好的。
system 表有0或1行数据。
unique_subquery 这类似于eq_ref,但是用在被转换成键查找的子查询上的。

“Extra” 列

这个字段由下面的一个或者多个值组成,用’;’分割。
注意有些值是在优化阶段之后才检测出来的。

优化阶段可以对Where语句做如下变更:

  • 用ON和USING语句给WHERE添加表达式
  • 常量传播:如果有 column=constant的话,用这个常量替换所有的字段实例
  • const表的值替换所有字段。
  • WHERE中移除用到的键字段(因为这会作为键查询的一部分被检测)。
  • 移除子表达式中不可能的常量。例如 Where '(a=1 and a=2) OR b=1'变成b=1
  • 用有同样值的字段替代其他字段:例如 ‘WHERE a=b and a=c’可以加工为‘WHERE a=b and b=c and b=c'
  • 为提前检查不可能的行的情况添加额外条件。这主要发生在OUTER JOIN里,我们由于某种原因需要在WHERE里检测NULL(’Not exists‘优化的一部分)。这可能在Extra列里会引起意外的’Using where‘。
  • 在表的层面,我们会移除在我们读前面的行时已经检测过的表达式。例如:当用WHERE 't1.a=1 and t1.a=t2.b' 来关联表t1t2时,如果在检查 ‘t2’的行时已知这个表达式('t2.b=1')为真,那么我们就不需要再检查't1.a=1'了。
描述
const row not found 此表是个系统表(表里本应有一行数据),但是没有数据。
Distinct 是否使用distinct优化(移除重复行)。这个仅标记SELECT中的最后一个表。
Full scan on NULL key 表示子查询的一部分,如果用来匹配子查询的数据值为NULL的话,我们将进行全表扫描。
Impossible HAVING HAVING语句恒为false,所以SELECT不返回数据。
Impossible WHERE noticed after reading const tables. WHERE语句恒为false,所以SELECT不返回数据。这种情况发生在我们读取了所有const表之后,并且在WHERE语句中被使用的字段值为常数。例如:WHERE const_column=5,同时const_column只有一个值为4.
Impossible WHERE WHERE语句恒为false,所以SELECT不返回数据。例如:WHERE1=2。
No matching min/max row 在前期优化MIN()/MAX()值的时候发现,没有行可以匹配WHERE语句。MIN()/MAX()函数会返回NULL。
no matching row in const table 此表是一个常量表(表里可能会有一行匹配的数据),但是没有数据。
No tables used SELECT是一个没有用到任何表的子查询。例如:没有FROM语句或者双重FROM语句。
Not exists 如果找到一个匹配行,我们就停止搜寻更多行。这个是针对LEFT JOIN的优化,明确地搜索在LEFT JOIN TABLE里不存在的行。例如:SELECT * FROM t1 LEFT JOIN t2 on (…) WHERE t2.not_null_column IS NULL。因为t2.not_null_column 只有在不匹配的行的条件下才可能为NULL,所以优化器在找到一个匹配行之后,就可以停止搜索。
Open_frm_only 针对INFORMATION_SCHEMA表。对于每个匹配行,只有frm(被打开的表定义文件)被打开。
Open_full_table 针对INFORMATION_SCHEMA表。对于每个匹配行,全表被打开用来检索需要的信息。(慢)
Open_trigger_only 针对INFORMATION_SCHEMA表。对于每个匹配行,只有触发器定义文件被打开。
Range checked for each record (index map: ...) 这个发生的场景是,在没有好的默认索引可用但是当我们把前序表的所有字段作为常量时就有些索引可以用。对每一个行组合,优化器会决定用哪个索引从表里检索一行出来。这不够快,但作为唯一的备选也比全表扫描快。索引图是一个位掩码,它表示了对于每一行的情况哪个索引被使用了。
Scanned 0/1/all databases 针对INFORMATION_SCHEMA表。表示我们不得不进行目录扫描的次数。
Select tables optimized away 关联中所有表被优化掉了。当我们在SELECT中仅仅使用COUNT(*),MIN()和MAX()函数时,并且我们可以在那里用常量替换所有这些(函数),这种情况才会发生。
Skip_open_table 针对INFORMATION_SCHEMA表。被查询的表不需要打开。
unique row not found 在早期优化阶段,检测到表是一个常量表(仅有一个可能匹配行的表),但是没有数据。
Using filesort 解决此查询需要用Filesort。这意味着有一个额外阶段,我们首先收集所有需要排序的列,再进行基于磁盘的合并排序,然后使用排序后的集合有序地检索数据行。如果列集合比较小,我们在排序文件里会存储所有的列,就不需要再去数据库里面检索他们了。
Using index 仅用索引从表中检索需要的信息。这就不需要为检索实际数据而进行额外的查找了。
Using index condition 类似于Using where,但是where条件会被往下推至表引擎,在索引层面进行内部优化。
Using index condition(BKA) 类似Using index condition,但是额外的,我们使用批量键访问来检索数据。
Using index for group-by 使用索引来解决GROUP BY或者DISTINCT查询。不读数据行。如果表有很多同一的索引入口并被作为重复行而迅速跳过,那么这会非常有效率。
Using intersect(...) 针对index_merge关联。显示哪个索引是intersect的一部分。
Using join buffer 我们将先前的行组合存储在行缓冲区中,以便能够一次就将每个行与连接缓冲区中的所有行组合进行匹配。
Using sort_union(...) 针对index_merge关联。显示哪个索引是union的一部分
Using temporary 创建临时表以保存结果集。 如果您使用GROUP BY,DISTINCT或ORDER BY,通常会发生这种情况。
Using where WHERE表达式(除了可能的键查找之外)用于检查行是否应该被接受。 如果’Using where’没有跟关联类型AL在一起,你可能遇到了错误!
Using where with pushed condition 类似于Using Where,但是WHERE条件被下推至表引擎,在行层面进行内部优化。
Using buffer UPDATE语句将首先缓冲行,然后运行更新,而不是动态更新。 有关详细说明,请参阅使用缓冲区UPDATE算法(UPDATE语句将首先缓冲行,然后运行更新,而不是动态更新。 有关详细说明,请参阅使用缓冲区UPDATE算法。)。

EXPLAIN EXTENDED

EXTENDED关键字将另一列,filtered,添加到输出。 这是将由条件过滤的表的行数的百分比估计。
EXPLAIN EXTENDED总会输出一个警告,因为它向后续的SHOW WARNINGS语句添加了额外的消息信息。 这包括在应用优化和重写规则后SELECT查询语句的样子,以及优化器如何修饰列和表。

示例

DESCRIBE 和 SHOW COLUMNS FROM是相同的:

DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | YES  |     | NULL    |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

一个简单的例子来看看EXPLAIN如何识别不好的索引使用:

CREATE TABLE IF NOT EXISTS `employees_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(40) NOT NULL,
  `position` varchar(25) NOT NULL,
  `home_address` varchar(50) NOT NULL,
  `home_phone` varchar(12) NOT NULL,
  `employee_code` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `employee_code` (`employee_code`),
  KEY `first_name` (`first_name`,`last_name`)
) ENGINE=Aria;

INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`)
  VALUES
  ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'),
  ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'),
  ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'),
  ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'),
  ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'),
  ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1');

SHOW INDEXES FROM employees_example;
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees_example |          0 | PRIMARY       |            1 | id            | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          0 | employee_code |            1 | employee_code | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            1 | first_name    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| employees_example |          1 | first_name    |            2 | last_name     | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

在主键上查询:

EXPLAIN SELECT * FROM employees_example WHERE id=1;
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table             | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | employees_example | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+

类型是const,这意味着只有一个可能的结果可以返回。 现在,返回相同的记录,但通过他们的电话号码检索:

EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employees_example | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

这里,类型是All,这意味着不能使用索引。 查看rows可知,必须执行全表扫描(所有六行)才能检索记录。 如果需要通过电话号码搜索,则必须创建索引。

SHOW EXPLAIN 示例:

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

ref_or_null优化的示例:

SELECT * FROM table_name
  WHERE key_column=expr OR key_column IS NULL;

ref_or_null是在使用带有NOT IN的子查询时经常发生的,因为如果第一个值没有匹配的行,则必须对NULL值执行额外的检查。

参考

SHOW EXPLAIN


注:ACMUG收录技术文章版权属于原作者本人所有。如有疑问,请联系作者。

看完转发,手留余香。关注我们,一起进步。
关注ACMUG公众号,参与社区活动,交流开源技术,分享学习心得,一起共同进步。

发表评论