轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL EXPLAIN


EXPLAIN语句提供有关语句执行计划的信息 SELECT

EXPLAIN返回SELECT语句中使用的每个表的一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行,然后在第二个表,第三个表中找到匹配的行,依此类推。处理完所有表后,MySQL将通过表列表输出所选列和回溯,直到找到有更多匹配行的表。从该表中读取下一行,并继续下一个表。

使用EXTENDED关键字时, EXPLAIN通过在SHOW WARNINGS语句后面发出语句来生成可以查看的额外信息 EXPLAIN。 EXPLAIN EXTENDED还会显示该filtered列。请参见 第8.8.3节“扩展EXPLAIN输出格式”

注意:您不能在同一语句中一起使用EXTENDED和 PARTITIONS关键字 EXPLAIN

注意

MySQL Workbench具有Visual Explain功能,可以直观地显示 EXPLAIN输出。请参阅 教程:使用说明来提高查询性能

EXPLAIN输出列

本节介绍生成的输出列 EXPLAIN。后面的部分提供有关type 和 Extra 列的其他信息 。

每个输出行EXPLAIN 提供有关一个表的信息。每行包含表8.1“EXPLAIN输出列”中汇总的值 ,并在表后面进行了更详细的描述。

表8.1 EXPLAIN输出列

含义
id SELECT标识符
select_type SELECT类型
table 输出行的表
partitions 匹配的分区
type 连接类型
possible_keys 可供选择的索引
key 实际选择的指数
key_len 所选键的长度
ref 列与索引进行比较
rows 估计要检查的行
filtered 按表条件过滤的行的百分比
Extra 附加信息

 

  • idSELECT标识符。这是SELECT查询中的序号 。NULL如果行引用其他行的联合结果,则该值可以是。在这种情况下,该 table列显示一个值 ,表示该行引用值为和的行的 并集 。 <unionM,N>idMN
  • select_type类型SELECT,可以是下表中显示的任何类型。
    select_type 值 含义
    SIMPLE 简单SELECT(不使用 UNION或子查询)
    PRIMARY 最 SELECT
    UNION 第二次或以后的SELECT陈述 UNION
    DEPENDENT UNION 中的第二个或更晚的SELECT语句 UNION,取决于外部查询
    UNION RESULT UNION的结果。
    SUBQUERY 子查询
    DEPENDENT SUBQUERY 在子查询中,依赖于外部查询
    DERIVED 派生表
    UNCACHEABLE SUBQUERY 无法缓存结果的子查询,必须为外部查询的每一行重新计算
    UNCACHEABLE UNION UNION 属于不可缓存的子查询的第二个或后一个选择(请参阅参考资料 UNCACHEABLE SUBQUERY

    DEPENDENT通常表示使用相关子查询。请参见 第13.2.10.7节“相关子查询”

    DEPENDENT SUBQUERY评估与评估不同UNCACHEABLE SUBQUERY。因为DEPENDENT SUBQUERY,子查询仅针对来自其外部上下文的变量的每组不同值重新评估一次。对于 UNCACHEABLE SUBQUERY,子查询将针对外部上下文的每一行重新进行评估。

    子查询的可缓存性与查询缓存中查询结果的缓存不同(在第8.10.3.1节“查询缓存如何操作”中对此进行了描述 )。查询执行期间发生子查询缓存,而查询缓存仅在查询执行完成后用于存储结果。

  • table输出行引用的表的名称。这也可以是以下值之一:
    • <unionM,N>:行指的是id值为M和的行 的 并集 N
    • <derivedN>:该行是指用于与该行的派生表结果id的值 N。例如,派生表可能来自FROM子句中的子查询 。
  • partitions查询将匹配记录的分区。仅当使用PARTITIONS关键字时,才会显示此列 。该值适用NULL于非分区表。请参见 第19.3.4节“获取有关分区的信息”
  • type连接类型。有关不同类型的说明,请参阅 EXPLAIN 连接类型
  • possible_keyspossible_keys列指示MySQL可以从中选择查找此表中的行的索引。请注意,此列完全独立于输出中显示的表的顺序 EXPLAIN。这意味着某些键possible_keys可能无法在生成中使用生成的表顺序。如果此列是NULL,则没有相关索引。在这种情况下,您可以通过检查WHERE子句以检查它是否引用适合索引的某些列或列来提高查询性能 。如果是,请创建适当的索引并EXPLAIN再次检查查询。请参见第13.1.7节“ALTER TABLE语法”。要查看表有哪些索引,请使用。 SHOW INDEX FROM tbl_name
  • keykey列指示MySQL实际决定使用的密钥(索引)。如果MySQL决定使用其中一个possible_keys 索引来查找行,那么该索引将被列为键值。可能key会命名值中不存在的索引 possible_keys。如果没有possible_keys索引适合查找行,则会发生这种情况,但查询选择的所有列都是其他索引的列。也就是说,命名索引覆盖了所选列,因此虽然它不用于确定要检索的行,但索引扫描比数据行扫描更有效。因为InnoDB,即使查询还选择主键,辅助索引也可能覆盖所选列,因为InnoDB主键值与每个辅助索引一起存储。如果 keyNULL,MySQL没有找到用于更有效地执行查询的索引。要强制MySQL使用或忽略列出的索引 possible_keys列,使用 FORCE INDEXUSE INDEXIGNORE INDEX在您的查询。请参见第8.9.3节“索引提示”。对于MyISAMNDB 表,运行ANALYZE TABLE有助于优化器选择更好的索引。对于NDB表,这还可以提高分布式下推连接的性能。对于 MyISAM表格,myisamchk --analyze也是如此 ANALYZE TABLE。请参见 第7.6节“MyISAM表维护和崩溃恢复”
  • key_lenkey_len列指示MySQL决定使用的密钥的长度。该值 key_len使您可以确定MySQL实际使用的多部分密钥的多少部分。如果key列说 NULL,该len_len 列也说NULL。由于密钥存储格式,对于可能NULL 比列的列,密钥长度更大NOT NULL
  • refref列显示将哪些列或常量与列中指定的索引进行比较,以 key从表中选择行。
  • rowsrows列指示MySQL认为必须检查以执行查询的行数。对于InnoDB表格,此数字是估算值,可能并不总是准确的。
  • filteredfiltered列指示将按表条件过滤的表行的估计百分比。最大值为100,这意味着不会对行进行过滤。值从100开始减少表示过滤量增加。 rows显示检查的估计行数,rows× filtered表示将与下表连接的行数。例如,如果 rows为1000且 filtered为50.00(50%),则使用下表连接的行数为1000×50%= 500.如果使用,则显示此列 EXPLAIN EXTENDED
  • Extra此列包含有关MySQL如何解析查询的其他信息。有关不同值的说明,请参阅 EXPLAIN 附加信息

EXPLAIN加入类型

type列 EXPLAIN输出介绍如何联接表。以下列表描述了从最佳类型到最差类型的连接类型:

  • system该表只有一行(=系统表)。这是const连接类型的特例 。
  • const该表最多只有一个匹配行,在查询开头读取。因为只有一行,所以优化器的其余部分可以将此行中列的值视为常量。 const表非常快,因为它们只读一次。const将a PRIMARY KEY或 UNIQUE索引的所有部分与常量值进行比较时使用。在以下查询中,tbl_name可以用作const 表:
    SELECT * FROM tbl_name WHERE primary_key=1;
    
    SELECT * FROM tbl_name
      WHERE primary_key_part1=1 AND primary_key_part2=2;
  • eq_ref对于前面表格中的每个行组合,从该表中读取一行。除了 system和 const类型之外,这是最好的连接类型。当连接使用索引的所有部分且索引是 索引PRIMARY KEYUNIQUE NOT NULL索引时使用它。eq_ref可用于使用=运算符进行比较的索引列 。比较值可以是常量,也可以是使用在此表之前读取的表中的列的表达式。在以下示例中,MySQL可以使用 eq_ref联接来处理 ref_table
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;
  • ref对于前面表中的每个行组合,将从此表中读取具有匹配索引值的所有行。ref如果连接仅使用键的最左前缀或者键不是a PRIMARY KEY或 UNIQUE索引(换句话说,如果连接不能基于键值选择单行),则使用此方法。如果使用的密钥只匹配几行,这是一个很好的连接类型。ref可以用于使用=or <=> 运算符进行比较的索引列 。在以下示例中,MySQL可以使用 ref联接来处理 ref_table
    SELECT * FROM ref_table WHERE key_column=expr;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column=other_table.column;
    
    SELECT * FROM ref_table,other_table
      WHERE ref_table.key_column_part1=other_table.column
      AND ref_table.key_column_part2=1;
  • fulltext使用FULLTEXT 索引执行连接。
  • ref_or_null这种连接类型是这样的 ref,但除此之外,MySQL还会对包含NULL值的行进行额外搜索。此连接类型优化最常用于解析子查询。在以下示例中,MySQL可以使用 ref_or_null联接来处理ref_table
    SELECT * FROM ref_table
      WHERE key_column=expr OR key_column IS NULL;

    请参见第8.2.1.9节“IS NULL优化”

  • index_merge此连接类型表示使用了索引合并优化。在这种情况下,key输出行中的列包含使用的索引列表,并key_len包含所用索引 的最长关键部分的列表。有关更多信息,请参见 第8.2.1.3节“索引合并优化”
  • unique_subquery此类型替换 以下形式的eq_ref某些 IN子查询:
    value IN (SELECT primary_key FROM single_table WHERE some_expr)

    unique_subquery 只是一个索引查找功能,它可以完全替换子查询以提高效率。

  • index_subquery此连接类型类似于 unique_subquery。它替换IN子查询,但它适用于以下形式的子查询中的非唯一索引:
    value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range仅检索给定范围内的行,使用索引选择行。的key 输出行中的列指示使用哪个索引。将key_len包含已使用的时间最长的关键部分。该ref列 NULL适用于此类型。range当一个键柱使用任何的相比于恒定可使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE,或 IN()运算符:
    SELECT * FROM tbl_name
      WHERE key_column = 10;
    
    SELECT * FROM tbl_name
      WHERE key_column BETWEEN 10 and 20;
    
    SELECT * FROM tbl_name
      WHERE key_column IN (10,20,30);
    
    SELECT * FROM tbl_name
      WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
    
  • indexindex联接类型是一样的 ALL,只是索引树被扫描。这有两种方式:
    • 如果索引是查询的覆盖索引,并且可用于满足表中所需的所有数据,则仅扫描索引树。在这种情况下,Extra专栏说 Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快 。
    • 使用索引中的读取执行全表扫描,以按索引顺序查找数据行。 Uses index没有出现在 Extra列中。

    当查询仅使用属于单个索引的列时,MySQL可以使用此连接类型。

  • ALL对前面表格中的每个行组合进行全表扫描。如果表是第一个未标记的表 const,这通常不好的,并且在所有其他情况下通常 非常糟糕。通常,您可以ALL通过添加索引来避免 ,这些索引根据以前表中的常量值或列值从表中启用行检索。

解释额外信息

Extra列 EXPLAIN输出包含MySQL解决查询的额外信息。以下列表说明了此列中可能出现的值。如果你想使你的查询尽可能快,看出来Extra的数值Using filesortUsing temporary

  • Child of 'table' pushed join@1此表被引用为table可以下推到NDB内核的联接中的子代 。仅当启用了下推连接时,才适用于MySQL NDB Cluster 7.2及更高版本。有关ndb_join_pushdown更多信息和示例,请参阅 服务器系统变量的说明。
  • const row not found对于诸如此类的查询,该表为空。 SELECT ... FROM tbl_name
  • DistinctMySQL正在寻找不同的值,因此它在找到第一个匹配行后停止为当前行组合搜索更多行。
  • Full scan on NULL key当优化程序无法使用索引查找访问方法时,子查询优化会作为回退策略发生这种情况。
  • Impossible HAVINGHAVING子句始终为false,不能选择任何行。
  • Impossible WHEREWHERE子句始终为false,不能选择任何行。
  • Impossible WHERE noticed after reading const tablesMySQL已经读取了所有 const(和 system)表,并注意到该WHERE子句始终为false。
  • No matching min/max row没有行满足查询的条件,例如 。 SELECT MIN(...) FROM ... WHERE condition
  • no matching row in const table对于具有连接的查询,有一个空表或没有满足唯一索引条件的行的表。
  • No tables used查询没有FROM子句,或者有 FROM DUAL子句。
  • Not existsMySQL能够对LEFT JOIN 查询进行优化,并且在找到与LEFT JOIN标准匹配的行之后,不会检查此表中更多行以用于上一行组合。以下是可以通过以下方式优化的查询类型的示例:
    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
      WHERE t2.id IS NULL;

    假设t2.id定义为 NOT NULL。在这种情况下,MySQL 使用的值 扫描 t1并查找行 。如果MySQL找到匹配的行 ,则它知道 永远不会 ,并且不会扫描具有相同值的其余行。换句话说,对于每一行,MySQL只需要进行一次查找,而不管实际匹配的行数。 t2t1.idt2t2.idNULLt2idt1t2t2

  • Range checked for each record (index map: N)MySQL发现没有好的索引可以使用,但发现在前面的表的列值已知之后可能会使用某些索引。对于上表中的每个行组合,MySQL检查是否可以使用range或 index_merge访问方法来检索行。这不是很快,但比执行没有索引的连接更快。适用性标准如 第8.2.1.2节“范围优化”和 第8.2.1.3节“索引合并优化”中所述,除了前面的表的所有列值都是已知的并被认为是常量。索引从1开始编号,顺序SHOW INDEX与表中显示的顺序相同。索引映射值 N是一个位掩码值,指示哪些索引是候选。例如,值0x19(二进制11001)表示将考虑索引1,4和5。
  • Scanned N databases这表示服务器在处理INFORMATION_SCHEMA表查询时执行的目录扫描次数 ,如第8.2.3节“优化INFORMATION_SCHEMA查询”中所述。值N可以是0,1或all
  • Select tables optimized away优化器确定1)应该返回最多一行,以及2)为了产生该行,必须读取确定的行集。当在优化阶段(例如,通过读取索引行)读取要读取的行时,在查询执行期间不需要读取任何表。当查询被隐式分组时(包含聚合函数但没有GROUP BY子句),满足第一个条件 。当每个使用的索引执行一行查找时,满足第二个条件。读取的索引数决定了要读取的行数。请考虑以下隐式分组查询:
    SELECT MIN(c1), MIN(c2) FROM t1;

    假设MIN(c1)可以通过读取一个索引行MIN(c2) 来检索,并且可以通过从不同的索引读取一行来检索。即,对于每一列c1和 c2,存在其中列是索引的第一列的索引。在这种情况下,返回一行,通过读取两个确定行来生成。

    Extra如果要读取的行不是确定性的,则不会出现 此值。考虑这个查询:

    SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

    假设这(c1, c2)是一个覆盖索引。使用此索引,c1 <= 10必须扫描所有行以查找最小值 c2。相比之下,请考虑以下查询:

    SELECT MIN(c2) FROM t1 WHERE c1 = 10;

    在这种情况下,第一个索引行c1 = 10包含最小值c2 。必须只读取一行才能生成返回的行。

    对于保持每个表的精确行数(例如MyISAM但不是 InnoDB)的存储引擎,对于缺少该子句或始终为true且没有 子句的查询,Extra 可能会出现此值。(这是隐式分组查询的实例,其中存储引擎会影响是否可以读取确定数量的行。) COUNT(*)WHEREGROUP BY

  • Skip_open_table, Open_frm_only, Open_full_table这些值表示适用于INFORMATION_SCHEMA 表查询的文件打开优化,如 第8.2.3节“优化INFORMATION_SCHEMA查询”中所述
    • Skip_open_table:表文件不需要打开。通过扫描数据库目录,该信息已在查询中可用。
    • Open_frm_only:只.frm需要打开表的 文件。
    • Open_full_table:未经优化的信息查找。的.frm, .MYD和 .MYI文件必须被打开。
  • unique row not found对于诸如的查询,没有行满足 索引或表的条件。 SELECT ... FROM tbl_nameUNIQUEPRIMARY KEY
  • Using filesortMySQL必须执行额外的传递以找出如何按排序顺序检索行。排序是通过根据连接类型遍历所有行并将排序键和指针存储到与该WHERE子句匹配的所有行的行来完成的。然后对键进行排序,并按排序顺序检索行。请参见 第8.2.1.10节“ORDER BY Optimization”
  • Using index仅使用索引树中的信息从表中检索列信息,而不必执行额外的搜索以读取实际行。当查询仅使用属于单个索引的列时,可以使用此策略。对于InnoDB具有用户定义的聚簇索引的表,即使列中Using index不存在 该索引,也可以使用该索引Extra。这样的话,如果 type是 index和 keyPRIMARY
  • Using index for group-byUsing index表访问方法类似,Using index for group-by 表示MySQL找到了一个索引,可用于检索GROUP BY或 DISTINCT查询的所有列,而无需对实际表进行任何额外的磁盘访问。此外,索引以最有效的方式使用,因此对于每个组,只读取少数索引条目。有关详细信息,请参见 第8.2.1.11节“GROUP BY优化”
  • Using join buffer将早期联接中的表分成几部分读入连接缓冲区,然后从缓冲区中使用它们的行来执行与当前表的连接。
  • Using sort_union(...)Using union(...)Using intersect(...)这些指示特定算法显示如何为index_merge连接类型合并索引扫描 。请参见第8.2.1.3节“索引合并优化”
  • Using temporary要解析查询,MySQL需要创建一个临时表来保存结果。如果查询包含以不同方式列出列的GROUP BY和 ORDER BY子句,则通常会发生这种情况。
  • Using whereWHERE子句用于限制匹配哪些行针对下一个表或发送到客户端。除非您特意打算从表中获取或检查所有行,否则如果Extra值不是 Using where并且表连接类型为ALL或者 ,则 查询中可能出现错误index。即使您正在为WHERE 子句的所有部分使用索引,您也可以查看Using where列是否可以NULL
  • Using where with pushed condition此产品适用于NDB 表。这意味着NDB Cluster正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。在这种情况下,条件被“ 下推 ”到集群的数据节点,并在所有数据节点上同时进行评估。这消除了通过网络发送不匹配行的需要,并且可以在可以但不使用条件下推的情况下将这种查询加速5到10倍。有关更多信息,请参阅 第8.2.1.4节“发动机状态下推优化”

EXPLAIN输出解释

通过获取输出rows 列中值的乘积,可以很好地指示连接的好坏程度EXPLAIN。这应该大致告诉你MySQL必须检查多少行才能执行查询。如果使用max_join_size系统变量限制查询,则 此行产品还用于确定SELECT 要执行的多表语句和要中止的多个表语句。请参见 第5.1.1节“配置服务器”

以下示例显示如何根据提供的信息逐步优化多表连接 EXPLAIN

假设您有SELECT此处显示的 语句,并且您计划使用EXPLAIN以下方法检查它 :

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于此示例,请进行以下假设:

  • 被比较的列已声明如下。
    字段 数据类型
    tt ActualPC CHAR(10)
    tt AssignedPC CHAR(10)
    tt ClientID CHAR(10)
    et EMPLOYID CHAR(15)
    do CUSTNMBR CHAR(15)
  • 表格具有以下索引。
    指数
    tt ActualPC
    tt AssignedPC
    tt ClientID
    et EMPLOYID (首要的关键)
    do CUSTNMBR (首要的关键)
  • tt.ActualPC值不是均匀分布的。

最初,在执行任何优化之前,该 EXPLAIN语句将生成以下信息:

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

因为type是 ALL针对每个表,所以此输出表明MySQL正在生成所有表的笛卡尔积; 也就是说,每一行的组合。这需要相当长的时间,因为必须检查每个表中行数的乘积。对于手头的情况,该产品为74×2135×74×3872 = 45,268,558,720行。如果表格更大,你只能想象需要多长时间。

这里的一个问题是MySQL可以更有效地使用列上的索引,如果它们被声明为相同的类型和大小。在这种情况下,VARCHAR与 CHAR被认为是相同的,如果它们被声明为相同的大小。 tt.ActualPC被声明为 CHAR(10)et.EMPLOYID 是CHAR(15),所以有一个长度不匹配。

要修复列长度之间的这种差异,请使用 从10个字符ALTER TABLE延长 ActualPC到15个字符:

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和 et.EMPLOYID都是 VARCHAR(15)EXPLAIN再次执行该 语句会产生以下结果:

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这并不完美,但要好得多:rows值的乘积 减少了74倍。这个版本在几秒钟内执行。

可以进行第二次更改以消除tt.AssignedPC = et_1.EMPLOYIDtt.ClientID = do.CUSTNMBR比较的列长度不匹配:

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

在修改之后, EXPLAIN生成此处显示的输出:

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

此时,查询几乎尽可能地优化。剩下的问题是,默认情况下,MySQL假定tt.ActualPC 列中的值均匀分布,而tt表不是这种情况。幸运的是,很容易告诉MySQL分析密钥分发:

mysql> ANALYZE TABLE tt;

使用附加索引信息,连接是完美的并 EXPLAIN产生以下结果:

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

rows输出中 的列 EXPLAIN是来自MySQL连接优化器的有根据的猜测。通过将rows产品与查询返回的实际行数进行比较,检查数字是否与事实 相符。如果数字完全不同,则可以通过STRAIGHT_JOIN在 SELECT语句中使用并尝试在FROM子句中以不同顺序列出表来 获得更好的性能 。

在某些情况下,可以执行在EXPLAIN SELECT与子查询一起使用时修改数据的语句; 有关更多信息,请参见第13.2.10.8节“派生表”