【随手记】MySQL单表访问方法

在MySQL查询优化器中，单表访问方法（Access Method）指的是查询时数据库如何从一个表中访问所需的数据。不同的访问方法适用于不同的查询场景，主要包括 const、ref、ref_or_null、range、index 和 all。这些方法从效率上依次递减，具体区别如下：

1. const

• 定义：const 访问方法用于当查询的某个列是主键或者唯一索引，并且查询条件是等值查询时。
• 特点：MySQL 只需要读取一次即可确定结果，并且结果通常在优化阶段即已确定。因为主键或唯一索引是唯一的，所以只会返回一行结果。
• 使用场景：查询使用了主键或唯一索引的等值查询。
• 效率：非常高效，是单表访问中最快的方法。

示例：

SELECT * FROM employees WHERE id = 1;  -- id 是主键

2. ref

• 定义：ref 是指 MySQL 使用了非唯一索引，并且查询条件是等值匹配。不同于 const，ref 可能会返回多行结果，因为非唯一索引允许多个记录具有相同的索引值。
• 特点：查询条件中使用了非唯一索引的等值查询。MySQL 通过索引查找符合条件的多行记录，然后再通过聚簇索引访问实际的行数据。
• 使用场景：当查询条件使用非唯一索引或者多列组合索引的最左列时，使用 ref 访问方法。
• 效率：效率高，但不如 const，因为它可能需要扫描多个匹配的记录。

示例：

SELECT * FROM employees WHERE department_id = 5;  -- department_id 是非唯一索引

3. ref_or_null

• 定义：ref_or_null 访问方法是 ref 的扩展，除了查找等值匹配的记录外，还会查找 NULL 值的记录。
• 特点：用于当查询条件既包含等值匹配，又需要包含 NULL 的情况。MySQL 会先查找匹配的索引记录，然后查找 NULL 值的记录。
• 使用场景：适用于某列既有具体值也允许 NULL 的查询。
• 效率：效率较高，但比 ref 稍差，因为它需要额外查找 NULL 值。

示例：

SELECT * FROM employees WHERE department_id = 5 OR department_id IS NULL;

4. range

• 定义：range 访问方法用于索引列上的范围查询。MySQL 使用索引扫描某个范围内的记录，例如使用 <, >, BETWEEN, IN 等操作符。
• 特点：MySQL 通过索引确定符合条件的记录范围，然后再查找该范围内的数据行。相比 ref，range 需要访问更多的记录，因为它不是精确匹配，而是扫描一个范围。
• 使用场景：适用于范围查询的场景，如 >，<，BETWEEN，IN 等条件。
• 效率：效率中等，视范围的大小而定。

示例：

SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;

5. index

• 定义：index 访问方法意味着 MySQL 需要全索引扫描，即扫描索引中的所有记录，而不查找表中的实际数据行。索引中的所有列都能够满足查询需求，因此不需要回表。
• 特点：索引的所有信息都能满足查询，尤其是在覆盖索引的情况下，MySQL 可以只扫描索引，不需要访问表中的数据行。
• 使用场景：常用于索引列能够满足查询的情况，不需要访问实际的行数据。
• 效率：相对较低，因为索引扫描仍然涉及大量数据读取。

示例：

SELECT name, salary FROM employees USE INDEX(salary_index);

6. all

• 定义：all 访问方法是指 MySQL 对表进行全表扫描，即逐行读取表中的每一行数据以找到匹配的记录。这是所有访问方法中最慢的一种，因为它要读取表的所有记录。
• 特点：all 通常出现在没有使用索引或查询条件没有涉及任何索引的情况下。
• 使用场景：适用于没有索引的表，或者查询条件不符合任何索引时。
• 效率：效率最低，通常应该尽量避免全表扫描，除非表非常小或者没有合适的索引。

示例：

SELECT * FROM employees WHERE name = 'Alice';  -- 没有为 name 创建索引

总结：

• const 是效率最高的访问方法，适用于主键或唯一索引的等值查询。
• ref 适用于非唯一索引的等值查询，效率也很高。
• ref_or_null 用于查询非唯一索引且包含 NULL 值的场景。
• range 适用于范围查询，性能较好，但依赖于范围的大小。
• index 执行全索引扫描，不需要回表，但涉及大量索引扫描。
• all 是效率最低的全表扫描，应该尽量避免。

合理设计索引，选择合适的访问方法，可以显著提升数据库查询性能。