mysql使用笔记

Explain

Extra 内容解释

类型	说明
Using filesort	MySQL 有两种方式可以生成有序的结果：通过排序操作或者使用索引。当 Extra 中出现了 Using filesort 说明使用了前者，但注意虽然 filesort 但并不是一定就是用了磁盘文件来进行排序，也可能是在内存里完成的。当出现排序，可以通过添加合适的索引来改进性能，用索引来为查询结果排序。
Using temporary	用临时表保存中间结果，常用于 GROUP BY 操作中，一般看到它说明查询需要优化了。
Using index	使用索引覆盖的情况
Using index condition	发生索引条件下推（Index Condition Pushdown）的情况
Using where	全表扫描的时候，MySQL 服务层应用 where 条件过滤数据；使用索引访问数据，但是 where 子句中有除了改索引包含的字段之外的条件
Using join buffer	当关联查询使用 Block Nested Loop 算法或者 Batched Key Access 算法，会把对驱动表查询的结果集放到 join buffer 中
distinct	优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作
LooseScan	表示使用了 Semi-join LooseScan 策略优化子查询
FirstMatch	表示使用了 Semi-join FirstMatch 策略优化子查询
Start temporary、End temporary	表示使用了 Semi-join DuplicateWeedout 策略优化子查询

Using Where

当 EXPLAIN 结果的 Extra 字段显示 Using where 时，MySQL 对已被索引过滤的数据进行进一步筛选的执行过程如下：

1、索引扫描阶段

确定使用的索引：MySQL 优化器会根据查询语句和表的索引情况，选择合适的索引来使用。如果查询中存在多个索引，优化器会评估每个索引的成本，选择成本最低的索引。
利用索引定位数据：根据所选的索引，MySQL 存储引擎会在索引结构（如 B+ 树）中查找满足部分查询条件的数据。例如，对于查询 SELECT * FROM users WHERE age > 20 AND gender = 'male';，如果有联合索引 (age, gender)，存储引擎会先通过索引找到 age > 20 的所有索引项。

2、数据行获取阶段

回表操作（如果需要）：如果查询所需的列不在索引中，存储引擎需要根据索引项中记录的行指针，回到数据文件中获取完整的数据行。例如，上述查询中如果索引只包含 age 和 gender 列，而查询需要返回所有列（SELECT *），就需要进行回表操作。
批量获取数据行：存储引擎会批量地从数据文件中读取满足索引条件的数据行，将这些数据行返回给 MySQL 服务器层。

3、服务器层过滤阶段

应用 WHERE 条件：MySQL 服务器层接收到存储引擎返回的数据行后，会对这些数据行逐行应用 WHERE 子句中的剩余条件进行过滤。在上述例子中，存储引擎返回了 age > 20 的所有数据行，服务器层会进一步检查每一行的 gender 是否为 'male'，将不满足条件的行过滤掉。
使用过滤算法：服务器层会使用高效的过滤算法来快速判断每一行是否满足条件。例如，对于简单的等值比较、范围比较等条件，会直接进行比较判断；对于复杂的条件，可能会使用表达式计算和逻辑判断来确定是否保留该行。

4、结果集返回阶段

返回最终结果：经过服务器层过滤后，剩下的数据行构成了最终的查询结果集。MySQL 会将这些结果集返回给客户端。

示例分析

假设有以下表结构和查询

CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10, 2),
    INDEX idx_category_price (category_id, price)
);

SELECT * FROM products WHERE category_id = 10 AND price > 100;

索引扫描：存储引擎使用 idx_category_price 索引，找到 category_id = 10 的所有索引项。
回表操作：由于查询需要返回所有列（SELECT *），存储引擎根据索引项中的行指针，回到数据文件中获取这些数据行。
服务器层过滤：服务器层对返回的数据行逐行检查 price > 100 的条件，将不满足条件的行过滤掉。
结果返回：将满足 category_id = 10 且 price > 100 的数据行作为最终结果返回给客户端。

步骤解析

这几个步骤并不是所有使用索引的查询都会完整经历，不同的查询场景和索引使用情况会导致某些步骤被跳过，以下是详细分析：

索引扫描

这是使用索引进行查询时基本都会有的步骤。当查询涉及到索引列时，存储引擎会根据索引结构（如 B+ 树）去查找满足部分查询条件的索引项。例如，在有索引的情况下，无论是等值查询（如 category_id = 10）还是范围查询（如 category_id > 10），存储引擎都会利用索引的有序性来快速定位可能符合条件的索引记录。所以，只要查询使用了索引，就会进行索引扫描。

回表操作

回表操作是否会发生取决于查询所需的列是否都包含在索引中。

需要回表的情况：如果查询使用的是二级索引（非主键索引），并且查询语句中要求返回的列包含了不在该索引中的列（如 SELECT *），那么存储引擎在通过索引找到索引项后，需要根据索引项中记录的行指针回到数据文件中获取完整的数据行，就会进行回表操作。就像前面例子中使用 idx_category_price 索引，但查询要求返回所有列，就需要回表。
无需回表的情况：当查询使用的索引是覆盖索引时，即查询所需的列都包含在索引中，就不需要回表。例如，有索引 (category_id, price)，查询语句为 SELECT category_id, price FROM table WHERE category_id = 10 AND price > 100;，此时可以直接从索引中获取所需数据，无需回表。

服务器层过滤

服务器层过滤是否会发生取决于索引是否能完全满足查询条件。

需要服务器层过滤的情况：如果索引只能满足部分查询条件，那么存储引擎返回的数据行中可能包含不满足其他条件的记录，这些记录需要在服务器层进行进一步过滤。例如，联合索引 (category_id, price)，查询 WHERE category_id = 10 AND price > 100，索引可以快速定位 category_id = 10 的记录，但 price > 100 这个条件在索引扫描时可能无法完全筛选，就需要在服务器层对返回的数据行进行检查和过滤。
无需服务器层过滤的情况：当索引能完全满足查询条件，并且不需要额外的过滤操作时，就不会有服务器层过滤步骤。比如使用覆盖索引，并且查询条件和索引列完全匹配，如 SELECT category_id, price FROM table WHERE category_id = 10 AND price = 200; 且索引为 (category_id, price)，存储引擎可以直接从索引中获取满足条件的数据，无需在服务器层进行过滤。

结果返回

这是查询执行的最后一步，无论前面的步骤如何，最终都会将满足查询条件的数据行作为结果返回给客户端。只是在不同情况下，返回的结果集大小和获取结果的效率可能会有所不同。

综上所述，这几个步骤是使用索引查询时常见的流程，但具体到每个查询，会根据索引的使用情况、查询条件以及查询所需的列等因素，部分步骤可能会被跳过。

通过这种方式，MySQL 完成了对已被索引过滤的数据的进一步筛选过程。

Using index condition

查找使用了索引，但是需要回表查询

索引条件下推

索引条件下推（Index Condition Pushdown，ICP）是 MySQL 5.6 版本添加的用于优化数据查询的技术，旨在减少存储引擎与 MySQL 服务器之间的数据交互，提升查询效率，以下是具体介绍：

原理机制：在不使用该优化时，存储引擎先通过索引检索到数据，再返回给 MySQL 服务器，由服务器判断数据是否符合条件；而使用索引条件下推优化时，若存在某些被索引列的判断条件，MySQL 服务器会将这部分条件传递给存储引擎。存储引擎先判断索引是否符合这些条件，只有符合时才将数据检索出来返回给服务器。例如，对于一张 people 表，字段有 name、address、first_name，索引为 (name,address,first_name)，执行查询SELECT * FROM person WHERE name = "1" AND address LIKE "%222" ，使用该优化后，可依据索引中存储的数据判断当前索引对应的数据是否符合条件，只有符合的数据才会查询出整行数据。
适用场景和限制：适用于 range、ref、eq_ref、ref_or_null 等需要整表扫描的情况，以及 InnoDB 和 MyISAM 引擎的查询（5.7 版本后可用于分区表查询，5.6 版本不行）。对于 InnoDB 引擎仅适用于二级索引；引用子查询的条件、调用存储过程的条件、触发条件不能下推。
对查询过程的影响
- 不使用时：先读取索引信息，再根据索引读取整行数据，最后通过 where 条件判断数据是否符合，符合则返回。
- 使用时：先获取索引信息，检查索引中存储的列信息是否符合索引条件，符合才读取整行数据，接着用剩余条件判断是否符合要求，符合则返回。
在 EXPLAIN 分析中的体现 ：使用索引条件下推时，在执行计划中 Extra 列会显示Using index condition 。
开启与关闭：该优化默认开启。可通过SET optimizer_switch = 'index_condition_pushdown = off';关闭，通过SET optimizer_switch = 'index_condition_pushdown = on';开启。

Using Where 和 Using Index同时出现

在 MySQL 的EXPLAIN执行计划中，Using where和Using index同时出现，通常基于以下原因：

索引覆盖查询但条件需额外过滤：当查询所需的所有列都包含在索引中，满足Using index（索引覆盖）情况。然而，WHERE子句中的条件不能完全通过索引来筛选。例如，有一张products表，包含id、name、category、price字段，创建联合索引idx_category_price (category, price) ，执行查询SELECT category, price FROM products WHERE category = 'Electronics' AND price > 100，索引可覆盖查询列，但price > 100的范围条件在索引扫描时不能完全确定，存储引擎按category = 'Electronics'筛选后，服务器层还需对price > 100进一步过滤，就会同时出现这两个标识。
部分条件可索引筛选，部分需服务器层处理：WHERE子句中有多个条件，部分能借助索引筛选，部分不能。如上述products表，若查询为SELECT category, price FROM products WHERE category = 'Electronics' AND name LIKE '%Pro'，category = 'Electronics'可通过idx_category_price索引筛选，但name LIKE '%Pro'无法通过该索引处理，存储引擎先利用索引找到category符合的记录，服务器层再对name条件过滤，此时也会同时出现Using where和Using index。