mysql explain 字段意义

type

解释

"system":          "这是const连接类型的一种特例, 该表仅有一行数据(=系统表).",
"const":           `const用于使用常数值比较PRIMARY KEY时, 当查询的表仅有一行时, 使用system. 例:SELECT * FROM tbl WHERE col = 1.`,
"eq_ref":          `除const类型外最好的可能实现的连接类型. 它用在一个索引的所有部分被连接使用并且索引是UNIQUE或PRIMARY KEY, 对于每个索引键, 表中只有一条记录与之匹配. 例: 'SELECT * FROM RefTbl, tbl WHERE RefTbl.col=tbl.col;'.`,
"ref":             `连接不能基于关键字选择单个行, 可能查找到多个符合条件的行. 叫做ref是因为索引要跟某个参考值相比较. 这个参考值或者是一个数, 或者是来自一个表里的多表查询的结果值. 例:'SELECT * FROM tbl WHERE idx_col=expr;'.`,
"fulltext":        "查询时使用 FULLTEXT 索引.",
"ref_or_null":     "如同ref, 但是MySQL必须在初次查找的结果里找出null条目, 然后进行二次查找.",
"index_merge":     `表示使用了索引合并优化方法. 在这种情况下. key列包含了使用的索引的清单, key_len包含了使用的索引的最长的关键元素. 详情请见 8.2.1.4, “Index Merge Optimization”.`,
"unique_subquery": `在某些IN查询中使用此种类型,而不是常规的ref:'value IN (SELECT PrimaryKey FROM SingleTable WHERE SomeExpr)'.`,
"index_subquery":  "在某些IN查询中使用此种类型, 与 unique_subquery 类似, 但是查询的是非唯一索引性索引.",
"range":           `只检索给定范围的行, 使用一个索引来选择行. key列显示使用了哪个索引. key_len包含所使用索引的最长关键元素.`,
"index":           "全表扫描, 只是扫描表的时候按照索引次序进行而不是行. 主要优点就是避免了排序, 但是开销仍然非常大.",
"ALL":             `最坏的情况, 从头到尾全表扫描.`,

预计复杂度

"ALL":             "O(n)",
"index":           "O(n)",
"range":           "O(log n)+",
"index_subquery":  "O(log n)+",
"unique_subquery": "O(log n)+",
"index_merge":     "O(log n)+",
"ref_or_null":     "O(log n)+",
"fulltext":        "O(log n)+",
"ref":             "O(log n)",
"eq_ref":          "O(log n)",
"const":           "O(1)",
"system":          "O(1)",

Extra

解释

"Using temporary":                   "表示MySQL在对查询结果排序时使用临时表. 常见于排序order by和分组查询group by.",
"Using filesort":                    "MySQL会对结果使用一个外部索引排序,而不是从表里按照索引次序读到相关内容. 可能在内存或者磁盘上进行排序. MySQL中无法利用索引完成的排序操作称为'文件排序'.",
"Using index condition":             "在5.6版本后加入的新特性(Index Condition Pushdown)。Using index condition 会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行。",
"Range checked for each record":     "MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。",
"Using where with pushed condition": "这是一个仅仅在NDBCluster存储引擎中才会出现的信息,打开condition pushdown优化功能才可能被使用。",
"Using MRR":                         "使用了 MRR Optimization IO 层面进行了优化,减少 IO 方面的开销。",
"Skip_open_table":                   "Tables are read using the Multi-Range Read optimization strategy.",
"Open_frm_only":                     "Table files do not need to be opened. The information is already available from the data dictionary.",
"Open_full_table":                   "Unoptimized information lookup. Table information must be read from the data dictionary and by reading table files.",
"Scanned":                           "This indicates how many directory scans the server performs when processing a query for INFORMATION_SCHEMA tables.",
"Using index for group-by":          "Similar to the Using index table access method, Using index for group-by indicates that MySQL found an index that can be used to retrieve all columns of a GROUP BY or DISTINCT query without any extra disk access to the actual table. Additionally, the index is used in the most efficient way so that for each group, only a few index entries are read.",
"Start temporary":                   "This indicates temporary table use for the semi-join Duplicate Weedout strategy.Start",
"End temporary":                     "This indicates temporary table use for the semi-join Duplicate Weedout strategy.End",
"FirstMatch":                        "The semi-join FirstMatch join shortcutting strategy is used for tbl_name.",
"Materialize":                       "Materialized subquery",
"Start materialize":                 "Materialized subquery Start",
"End materialize":                   "Materialized subquery End",
"unique row not found":              "For a query such as SELECT ... FROM tbl_name, no rows satisfy the condition for a UNIQUE index or PRIMARY KEY on the table.",
// "Scan":                                                "",
// "Impossible ON condition":                             "",
// "Ft_hints:":                                           "",
// "Backward index scan":                                 "",
// "Recursive":                                           "",
// "Table function:":                                     "",
"Index dive skipped due to FORCE":                     "This item applies to NDB tables only. It means that MySQL Cluster is using the Condition Pushdown optimization to improve the efficiency of a direct comparison between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the cluster's data nodes and is evaluated on all data nodes simultaneously. This eliminates the need to send nonmatching rows over the network, and can speed up such queries by a factor of 5 to 10 times over cases where Condition Pushdown could be but is not used.",
"Impossible WHERE noticed after reading const tables": "查询了所有const(和system)表, 但发现WHERE查询条件不起作用.",
"Using where":                              "WHERE条件用于筛选出与下一个表匹配的数据然后返回给客户端. 除非故意做的全表扫描, 否则连接类型是ALL或者是index, 且在Extra列的值中没有Using Where, 则该查询可能是有问题的.",
"Using join buffer":                        "从已有连接中找被读入缓存的数据, 并且通过缓存来完成与当前表的连接.",
"Using index":                              "只需通过索引就可以从表中获取列的信息, 无需额外去读取真实的行数据. 如果查询使用的列值仅仅是一个简单索引的部分值, 则会使用这种策略来优化查询.",
"const row not found":                      "空表做类似 SELECT ... FROM tbl_name 的查询操作.",
"Distinct":                                 "MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.",
"Full scan on NULL key":                    "子查询中的一种优化方式, 常见于无法通过索引访问null值.",
"Impossible HAVING":                        "HAVING条件过滤没有效果, 返回已有查询的结果集.",
"Impossible WHERE":                         "WHERE条件过滤没有效果, 最终是全表扫描.",
"LooseScan":                                "使用半连接LooseScan策略.",
"No matching min/max row":                  "没有行满足查询的条件, 如 SELECT MIN(...) FROM ... WHERE condition.",
"no matching row in const table":           "对于连接查询, 列未满足唯一索引的条件或表为空.",
"No matching rows after partition pruning": "对于DELETE 或 UPDATE, 优化器在分区之后, 未发现任何要删除或更新的内容. 类似查询 Impossible WHERE.",
"No tables used":                           "查询没有FROM子句, 或者有一个 FROM DUAL子句.",
"Not exists":                               "MySQL能够对LEFT JOIN查询进行优化, 并且在查找到符合LEFT JOIN条件的行后, 则不再查找更多的行.",
"Plan isn't ready yet":                     "This value occurs with EXPLAIN FOR CONNECTION when the optimizer has not finished creating the execution plan for the statement executing in the named connection. If execution plan output comprises multiple lines, any or all of them could have this Extra value, depending on the progress of the optimizer in determining the full execution plan.",
"Using intersect":                          "开启了index merge,即:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并,使用的算法为:index_merge_intersection",
"Using union":                              "开启了index merge,即:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并,使用的算法为:index_merge_union",
"Using sort_union":                         "开启了index merge,即:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并,使用的算法为:index_merge_sort_union",

已有 22 条评论
  1. FranKap

    Filitra Professional Viagra Brand From Amazon cialis Buy Azitromyicin Cialis 20 Prezzo

    FranKap 回复
  2. Austot

    Cialis Gunstig Mit Rezept Ritalin Canada online cialis Cialis 20 Mg Composizione Levitra Kaufen Berlin

    Austot 回复
  3. FranKap

    Buy Female Viagra Cheap Combine Cephalexin And Darvocet Mer viagra Cialis 5 Mg Quanto Costa In Farmacia Preisvergleich Cialis 20 Mg Buy Viagra No Rx

    FranKap 回复
  4. StepSwesk

    Viagra Pillole viagra Cialis Mejor Que Viagra Levitra 10 Mg Vendita

    StepSwesk 回复
  5. RebPlesum

    Propecia Generique Prix Amoxicillin Adult Dose Precio Cialis 10 Mg Farmacia buy cialis online Zithromax Dosage Cats Tamoxifen Nolvadex Healthy Man Radio Ads

    RebPlesum 回复
  6. Austot

    Costo Del Viagra In Farmacia Propecia E Impotencia Comprar Over Counter Med Comparable To Cialis levitra effet secondaire Viagra Pills Oline

    Austot 回复
  7. 1thx.com

    1thx.com Hey an importantprovide
    Are you in?
    https://drive.google.com/file/d/1_Z2Lbsf8ep9_af5kPsNd3RbZZnRLAbZm/preview

    1thx.com 回复
  8. CharlesCem

    buy tetracycline online without prescription

    CharlesCem 回复
  9. RandPsymn

    Buy Cheap Lasix Online Cialis 8 20 Mg viagra Propecia Soft Propecia

    RandPsymn 回复
  10. FranKap

    Levitra Online Generic buy orlistat online uk Comprar Cialis Farmacia Andorra

    FranKap 回复
  11. FranKap

    Amoxil Amoxicilline buy generic cialis online Acquisto Kamagra Paris Cafagote Il Cialis Forum

    FranKap 回复
  12. Austot

    Hydrazide No Rx Generic Viagra Super Force buy viagra online Cialis Viagra Milano

    Austot 回复
  13. Swovofsd5

    cialis vs viagra online pharmacy ed pills online

    Swovofsd5 回复
  14. StepSwesk

    Cialis Nachnahme Rechnung Propecia Cost No Prescription Prospecto Levitra viagra Zithromax Ingredients

    StepSwesk 回复
  15. FranKap

    Amoxicilline Molecule levitra coupon free trial Cheap Zentel Mail Order Carmarthenshire Donde Comprar Cialis En Espana Propecia Ereccion

    FranKap 回复
  16. StepSwesk

    Tomar Viagra Mujeres cialis Amoxicillin Small Intestine Overgrowth Syndrome

    StepSwesk 回复
  17. Austot

    Amoxicillin 500 Mg Capsule Udl Allergy To Amoxicillin And Clavulanate Potassium Propecia Arginina viagra vs cialis Lioresal Pas Cher 10mg Viagra Store In Usa Long Term Effects Of Prednisone

    Austot 回复
  18. FranKap

    Viagra Wonderful Special For Men Comprobar Con Propecia Donde Comprar Viagra En Sevilla cialis 5 mg Kamagra Sildenafil Citrate Tablets Levitra Generico Farmacias Del Ahorro

    FranKap 回复
  19. FranKap

    Bentyl Australia Delivered On Saturday Without Rx Cialis Tadalafil 20mg Generic Cialis Tadalafil 20mg cheapest cialis Priligy 30 O 60

    FranKap 回复
  20. RebPlesum

    Female Kamagra Tablets Seledruff Cialis Acheter Belgique viagra Viagra Zoll Kamagra Gold Side Effects

    RebPlesum 回复
  21. Ellcurfak

    Amoxicillin Reaction 2000 Mg Daily Relent cialis de 20mg Generisches Viagra Gut

    Ellcurfak 回复
  22. StepSwesk

    Acheter Xenical Orlistat En Ligne cialis 5mg Doxycycline Isotret Viagra Online Auf Rechnung How To Order Viagra From Canada

    StepSwesk 回复
发表新评论