【随手记】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是效率最低的全表扫描，应该尽量避免。
合理设计索引，选择合适的访问方法，可以显著提升数据库查询性能。