explain output columns 列名	json name 使用format=json时输出中显示的效属性名称	meaning含义	详细解释
id	select_id	The SELECT identifier select标识符	这是查询中 SELECT 的顺序号。 id 号每个号码，表示一趟独立的查询 , 一个 sql 的查询趟数越少越好如果该行引用其他行的并集结果，则值可以是 NULL 。在这种情况下， table 列显示一个类似于的值，以指示该行引用具有 id 的 M 和 N 值的行并集。可以查看执行顺序。多表联查，id值越大，表查询越先执行。 id值相同，看输出顺序
select_type	None 无	The SELECT type SELECT 类型	查询类型，说明单表查询、关联查询、子查询等信息
table	table_name	The table for the output row 输出行所引用的表	表名。 explain输出的每条记录都对应着都某个单表的访问方法。 M,N>该行指的是具有 M 和 N 的 id 值的行的并集 N>指的是具有 id 值为 N 的行的派生表结果。例如，派生表可能由 FROM 子句中的子查询生成 N>指的是具有 id 值为 N 的行的物化子查询的结果
partitions	partitions	The matching partitions 匹配分区	显示分区，对于非分区表值为null
type	access_type	The join type 连接类型	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types 描述了如何连接表。以下连接类型按照最好到最差排列： system 该表只有一行（=系统表）。const连接类型的特殊情况 const 该表最多一行匹配，该行在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器其余部分视为常数。const 表非常快，因为它们只读取一次。 eq_ref当索引的所有部分都被连接使用且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，使用此类型。通常发生在连表查询中，关联的条件是某一张表的主键或者UNIQUE唯一非空索引。 ref ref 可用于使用 = 或操作符比较的索引列。如果连接不能根据键值选择单行，则使用 ref 。满足索引最左匹配原则，且不走主键或者唯一非空索引则为ref，非聚簇索引查询一般都为ref。 fulltext 使用fulltext索引进行连接操作 ref_or_null 类似于 ref ，但增加了 MySQL 对包含 NULL 值的行的额外搜索。这种连接类型优化最常用于解决子查询。在ref基础上增加null值查询则为这个。 index_merge 此连接类型表示使用了索引合并优化。一般是单表多个索引条件的查询，将多个搜索结果合并为一个，统一回表降低查询代价。 unique_subquery这种类型替换了以下形式的某些 IN 子查询中的 eq_ref。unique_subquery 是一个索引查找函数，它完全替换子查询以提高效率。 index_subquery 此连接类型类似于 unique_subquery 。它替换 IN 子查询，但适用于以下形式的子查询中的非唯一索引： range 仅检索给定范围内的行，使用索引选择行. key_len 包含使用的最长键部分。 ref 列是针对此类型的 NULL 当键列使用任何 = 、 <> 、 > 、 >= 、 < 、、 BETWEEN 、 LIKE 或 IN() 运算符与常量进行比较时，可以使用 range index index 连接类型与 ALL类似，扫描索引树 ALL 全表扫描，尽量避免
possible_keys	possible_keys	The possible indexes to choose 可能的索引选择	表示 MySQL 可以从其中选择以查找此表中行的索引
key	key	The index actually chosen 实际选择的索引	表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用 possible_keys 索引之一来查找行，则该索引被列为键值。
key_len	key_length	The length of the chosen key 所选索引的长度	表示 MySQL 决定使用的键的长度（即：字节数）。帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
ref	ref	The columns compared to the index 列与索引的比较	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	rows	Estimate of rows to be examined 估计要检查的行数	表示 MySQL 认为必须检查的行数以执行查询。对于 InnoDB 表，这个数字是一个估计值，可能并不总是准确。值越小越好
filtered	filtered	Percentage of rows filtered by table condition 行过滤比例	表示由表条件过滤的表行估计百分比
Extra	None 无	Additional information 附加信息	https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information 查询type为const时为null distinct MySQL 正在寻找不同的值，因此它在找到第一个匹配的行之后，就会停止为当前行组合搜索更多行。 Full scan on NULL key这发生在子查询优化中，作为当优化器无法使用索引查找访问方法时的后备策略。 Impossible HAVING HAVING 子句始终为假，无法选择任何行。 Impossible WHERE WHERE 子句始终为假，无法选择任何行。 Using filesortMySQL 必须进行额外遍历来确定如何以排序顺序检索行。排序是通过遍历所有行（根据连接类型）并存储所有匹配 WHERE 子句的行的排序键和行指针来完成的。然后对这些键进行排序，并按排序顺序检索行。请参阅第 8.2.1.14 节，“ORDER BY 优化”。 using index 该列信息仅通过索引树中的信息从表中检索，无需额外查找实际行。当查询仅使用单个索引中的列时，可以使用此策略。 using index condition 表通过访问索引元组并首先测试它们来确定是否读取完整的表行。这样，索引信息被用来延迟（“推下”）读取完整的表行，除非这是必要的。 Using index for group-by 与 Using index 表访问方法类似， Using index for group-by 表示 MySQL 找到一个可以用来检索 GROUP BY 或 DISTINCT 查询的所有列的索引，而无需对实际表进行任何额外的磁盘访问 Using MRR使用多范围读取优化策略读取 Using temporary为了解决查询，MySQL 需要创建一个临时表来存储结果。这通常发生在查询包含 GROUP BY 和 ORDER BY 子句，这些子句以不同的方式列出列时。 Using where 一个 WHERE 子句用于限制与下一个表匹配的行或发送给客户端的行。除非你明确打算检索或检查表中的所有行，否则如果 Extra 值不是 Using where ，并且表连接类型是 ALL 或 index ，你的查询可能有问题。

2.3重点🌟

type列（连接类型，判断索引使用情况）

结果值从最好到最坏依次是： system > const > eq_ref> ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。

range效率不高

index和all都是全表扫描

extra （额外信息，看SQL执行情况）

using index 覆盖索引查询的数据都有索引

using index condition 索引下推

using where 所有过滤动作都由server层处理，没有索引（需要优化）

using MRR

using join buffer （BAK|BNL）

using union（indexs）使用联合索引，多个查询条件都是索引列会发生索引合并

usring temporaray 使用了临时表，尽量使用索引，而非临时表，例如group by、union、distnct语句会使用到临时表

using filesort 通常出现在order by语句，查询之后需要额外进行排序。可以将条件与排序字段组成联合索引，由于有联合索引，排序字段本身就是有序的，不需要额外排序。

总结：避免临时表，尽量覆盖索引，像排序和函数什么的尽量避免

rows * filtered

rows列与filtered列通常与type列一起来看。

通常希望 rows * filtered的值越小越好，rows尽量小，filtered尽量高。

只要rows小就是好

rows:越小说明需要检查的行越少，即扫描的越少，语句越精准

filtered:越大说明扫描的行都被保留了，说明效率很高。

两者的值越小，说明性能越高。

异常情况：rows大，filtered低，说明有过滤，但是初始扫描行数多了，需要优化索引，联合索引最左侧选择性不高就可能出现这个问题。

2.4MySQL执行优化

is null优化

MySQL 可以对 col_name IS NULL 执行与用于 col_name = constant_value 相同的优化。

is null或者 null都会进行优化

注意：Oracle不会对NULL进行索引

Optimizing Subqueries with Materialization物化优化子查询

物化通过将子查询结果作为临时表（通常在内存中）生成来加速查询执行。MySQL 第一次需要子查询结果时，会将该结果物化到临时表中。任何后续需要该结果时，MySQL 都会再次引用临时表。优化器可能会使用哈希索引来对表进行索引，以使查找快速且成本低。索引包含唯一值，以消除重复并使表更小。

Index Merge索引合并优化

索引合并访问方法通过多次 range 扫描检索行，并将结果合并为一个。此访问方法仅合并单个表中的索引扫描，而不是跨多个表的扫描。合并可以产生其底层扫描的并集、交集或交集的并集。

如果您的查询包含复杂的 WHERE 子句，并且具有深层 AND / OR 嵌套，并且 MySQL 没有选择最佳计划，请尝试使用以下恒等变换来分配术语

(x AND y) OR z => (x OR z) AND (y OR z)

(x OR y) AND z => (x AND z) OR (y AND z)

order by优化

gorup by优化

满足 GROUP BY 子句的最通用方法是扫描整个表，创建一个新临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组和应用聚合函数（如果有）。在某些情况下，MySQL 能够做得更好，通过使用索引访问来避免创建临时表。

索引下推ICP 优化

在传统的查询执行过程中，数据库系统首先根据索引找到可能符合条件的记录位置，然后将这些记录加载到内存中，最后在内存中应用WHERE子句的过滤条件。

而索引下推技术则是在索引扫描阶段就应用部分或全部的WHERE子句条件，直接在索引层面上过滤数据，只将真正符合条件的记录加载到内存中。即讲过滤逻辑下推到‘存储引擎’进行，整理好后发给server层，减少交互次数。

ICP 可以减少存储引擎必须访问基本表的次数以及 MySQL 服务器必须访问存储引擎的次数。

3.MySQL8索引更新与说明

3.1支持降序索引 Descending Indexes

MySQL5.7版本只支持升序索引，降序查询需要filesort，从8.0开始，支持降序索引。

3.2函数索引

MySQL从 8.0.13 开始支持为表达式建立索引，称为函数索引（Functional Indexes）。

可以对表达式或函数的结果创建索引，而非仅限于列的原始值。

实现原理

函数索引底层通过"隐藏的虚拟生成"列实现。当创建索引时，MySQL 自动生成一个不可见的虚拟列，存储表达式的结果，并在此列上建索引。当创建索引时，MySQL 自动生成一个不可见的虚拟列，存储表达式的结果，并在此列上建索引。

适用场景

查询中常用表达式（如 UPPER(col)、SUBSTRING(col, 1, 3)）

JSON 字段提取（如 JSON_EXTRACT(doc, '$.key')）

3.3不可见索引（Invisible Indexes）

MySQL8可将索引标记为不可见（如 CREATE INDEX idx_col ON table(col) INVISIBLE），优化器忽略之，用于测试索引效果。

3.4多值索引（Multi-Valued Indexes）

MySQL8支持 JSON 数组的多值索引（如 CREATE INDEX idx_array ON table((CAST(json_col->>'$.array' AS UNSIGNED ARRAY)))）。用以提升JSON数据查询效率

资料引用

B站《猿人林克》MySQL系列
CSDN用户《贝尔摩德苦艾酒》MySQL栏目
书籍《高性能MySQL》
MySQL官网

收录于

关系型数据库

标签

#mysql #数据库

技术美术个人笔记（十六）——Houdini到UE的大地形文章目录关于地形白模制作关于PDG流程关于地形白模制作用heightfield noise和笔刷刷出合适的mask做山形叠加：用remap可快捷反转mask ，用来做平原地区的小型丘陵：接着用clip切平最低处，用distort制造侵蚀等，对部分地区用笔刷进行微调拓宽峡谷：用经过sweep打平的曲线以及裁切的各种多面体用heightfield_project投影制作河道和水池，用blur mask并remap的方法拓宽河道用distort、erode制造合适的弯曲和侵蚀，同理可挖出月牙形阅读详情

[MySQL]2-MySQL索引

1.索引🌟

1.1索引结构

B树

B+树

聚簇索引（一级索引）与非聚簇索引（二级索引）

回表操作

1.2索引碎片

清理索引碎片的方法

1.3索引匹配方式🌟

在数据列上使用函数或者计算会导致索引失效的原因

匹配列前缀

全值匹配

索引覆盖

最左匹配原则

匹配范围值

精确匹配某一列而范围匹配另外一列

1.4全文索引

1.5创建索引🌟

慎重建立索引

为长字符串建立前缀索引

联合索引的顺序

1.6索引优化🌟

建立索引

调整索引

谨慎使用索引统计

减少碎片

1.7索引创建原则

2.EXPLAIN

2.1explain output 执行计划输出解释

2.2概括