MySQL执行计划中`type`列的不同类型及性能影响

在 MySQL 执行计划里，type 列描述了 MySQL 如何访问表中的数据，它体现了表的访问类型。type 列的不同取值反映了不同的查询性能，从最优到最差有多种类型，下面为你详细说明各类型及其性能影响。

1. system

• 含义：这是最理想的访问类型，表明表中仅有一行记录，是 const 类型的特殊情况。通常，这类表是系统表或者极小的表。
• 性能影响：由于表中只有一行数据，查询时无需进行搜索，性能极高，查询速度接近常量时间。
• 示例：如果有一个存储全局配置信息的表，且该表只有一行记录，那么对其查询时可能出现 system 类型。

2. const

• 含义：通过索引能一次性找到匹配的记录，一般用于主键或者唯一索引的等值查询。MySQL 把这种查询优化成常量处理，因为根据索引能直接定位到唯一行。
• 性能影响：性能非常好，查询效率高，因为只需要进行一次索引查找操作，时间复杂度接近 O (1)。
• 示例：

SELECT * FROM users WHERE id = 1;

假设 id 是 users 表的主键，执行该查询时 type 可能为 const。

3. eq_ref

• 含义：在多表连接查询中，对于前一个表的每一行，后一个表通过索引仅查找一条记录。通常用于主键或者唯一索引的连接查询。
• 性能影响：性能也较为出色，因为每处理前一个表的一行数据，只需在后一个表的索引中进行一次查找操作，时间复杂度接近线性。
• 示例：

 

SELECT * 
FROM orders 
JOIN users ON orders.user_id = users.id;

若 users.id 是主键，orders.user_id 有索引，且在连接查询时就可能出现 eq_ref 类型。

4. ref

• 含义：使用非唯一索引或者索引前缀进行查找，返回匹配某个单独值的所有行。MySQL 会扫描索引中所有匹配的行。
• 性能影响：性能较好，但比 eq_ref 和 const 稍差。因为可能会返回多行记录，需要扫描部分索引数据，时间复杂度取决于匹配的行数。
• 示例：

SELECT * FROM users WHERE department_id = 2;

若 department_id 列有非唯一索引，执行该查询时 type 可能为 ref。

5. fulltext

• 含义：使用全文索引进行查询，用于在文本列上执行全文搜索。
• 性能影响：性能取决于全文索引的大小和查询的复杂度。对于大规模文本数据的搜索，全文索引能显著提高查询速度，但在某些复杂的搜索场景下，性能可能会受到一定影响。
• 示例：

SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword' IN NATURAL LANGUAGE MODE);

如果 content 列有全文索引，执行该查询时 type 为 fulltext。

6. ref_or_null

• 含义：与 ref 类似，但额外考虑了 NULL 值的情况。它会查找索引中匹配某个值或者为 NULL 的所有行。
• 性能影响：性能和 ref 相近，但由于需要额外处理 NULL 值，可能会稍微慢一些。
• 示例：

SELECT * FROM users WHERE email = 'example@example.com' OR email IS NULL;

若 email 列有索引，执行该查询时 type 可能为 ref_or_null。

7. index_merge

• 含义：表示使用了索引合并优化，MySQL 会同时使用多个索引来满足查询条件，然后将这些索引的结果合并。
• 性能影响：在某些情况下能提高查询性能，特别是当不同的索引可以分别筛选出部分数据时。但索引合并操作本身也有一定开销，如果索引过多或者数据分布不合理，性能可能会下降。
• 示例：

SELECT * FROM users WHERE age > 20 AND gender = 'male';

若 age 和 gender 列分别有索引，执行该查询时可能会使用 index_merge。

8. unique_subquery

• 含义：用于 IN 子查询，子查询使用唯一索引。MySQL 会将子查询优化为一个常量，提高查询效率。
• 性能影响：性能较好，因为子查询可以利用唯一索引快速返回结果，避免了子查询的多次执行。
• 示例：

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE is_active = 1);

若 users.id 是主键，执行该查询时子查询可能为 unique_subquery 类型。

9. index_subquery

• 含义：和 unique_subquery 类似，不过子查询使用的是非唯一索引。
• 性能影响：性能比 unique_subquery 稍差，因为非唯一索引可能会返回多行记录，需要进行更多的比较操作。
• 示例：若 users 表的某个非唯一索引列用于子查询，执行查询时子查询可能为 index_subquery 类型。

10. range

• 含义：只检索给定范围的行，使用索引来选择行，常见于 WHERE 子句中的 BETWEEN、>、< 等操作。
• 性能影响：性能一般，MySQL 会扫描索引中指定范围的部分，扫描的行数取决于范围的大小。范围越大，性能越差。
• 示例：

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

若 age 列有索引，执行该查询时 type 可能为 range。

11. index

• 含义：全索引扫描，MySQL 会扫描整个索引树来获取数据。虽然使用了索引，但可能没有使用 WHERE 子句进行过滤，或者过滤条件没有使用索引。
• 性能影响：性能较差，因为需要扫描整个索引，当索引很大时，会消耗较多的时间和资源。不过比 ALL 类型要好，因为索引通常比数据行小，减少了磁盘 I/O。
• 示例：

SELECT id FROM users;

若 id 是索引列，执行该查询时 type 可能为 index。

12. ALL

• 含义：全表扫描，MySQL 会逐行扫描表中的所有数据来满足查询条件。
• 性能影响：性能最差，尤其是对于大表，会消耗大量的磁盘 I/O 和 CPU 资源，查询效率极低。
• 示例：

SELECT * FROM users;

若没有合适的索引，执行该查询时 type 为 ALL。

一般而言，我们应尽量让 type 列的值靠近 system、const 等高性能类型，避免出现 ALL 这种低性能类型。可以通过创建合适的索引、优化查询语句等方式来改善 type 类型，提高查询性能。