极客笔记MySQL中的eq_ref和ref类型在explain中代表什么意思？
在MySQL中，执行查询语句时可以使用explain命令来查看查询的执行计划，它会显示一个表格，其中包含了查询语句中的每个步骤以及它们的执行顺序、优化等级等信息。在这个表格中，你可能会注意到两个常见的类型：eq_ref和ref，它们在查询性能和优化方面有着重要的意义。
阅读更多：MySQL 教程
eq_ref
eq_ref代表等值连接，它是最优秀的类型之一。当使用eq_ref时，MySQL使用了索引（如主键、唯一键等）来查询数据，从而能够减少搜索整个表格的开销。这种类型通常出现在如下的情况下：
- 在主键或唯一索引列上做了比较操作，例如
where id='123'； - 在连接查询中使用了主键或唯一索引作为连接条件，例如
join users on orders.user_id = users.id。
下面是一个使用eq_ref的例子，考虑以下两张表格orders和users：
- orders表格，列id为主键：
| id | user_id | amount |
|---|---|---|
| 1 | 100 | 10.00 |
| 2 | 101 | 20.00 |
| 3 | 101 | 30.00 |
- users表格，列id为主键：
| id | name |
|---|---|
| 100 | Alice |
| 101 | Bob |
现在要查询所有用户的订单数量，可以使用以下查询语句：
select users.name, count(*) as order_count
from orders join users on orders.user_id = users.id
group by users.name
执行explain命令后，可以发现该查询使用了eq_ref连接类型：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using index | |
| 1 | SIMPLE | orders | ref | user_id | user_id | 5 | const | 3 | 100.00 | Using index |
可以发现，它使用了users表格的主键来作为连接条件，在关联查询的同时，MySQL也使用了users表格的主键来过滤数据，直接返回结果，大大提高了查询性能。
ref
ref代表全索引扫描或索引扫描，当MySQL无法使用索引进行等值连接时，就会使用ref类型。一般情况下，ref是使用普通索引进行查询，但是结果集中的数据不是单独存在一个索引中的，而是查询多条索引记录直到满足所有条件。这种类型通常出现在如下的情况下：
- 在非主键或非唯一索引列上做了比较操作，例如
where name='bob'； - 在连接查询中使用了非主键或非唯一索引作为连接条件，例如
join users on orders.user_id = users.company_id。
下面是一个使用ref的例子，考虑以下两张表格students和classes：
- students表格，列id为主键：
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
| 2 | Bob | 22 |
| 3 | Jack | 23 |
- classes表格，列student_id为普通索引：
| id | student_id | name |
|---|---|---|
| 1 | 1 | Math |
| 2 | 2 | English |
| 3 | 2 | History |
现在要查询所有选修了Math的学生的名字和年龄，可以使用以下查询语句：
select students.name, students.age
from students join classes on students.id = classes.student_id
where classes.name = 'Math'
执行explain命令后，可以发现该查询使用了ref连接类型：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | classes | ref | PRIMARY,name | name | 768 | const | 1 | 100.00 | Using index | |
| 1 | SIMPLE | students | ref | PRIMARY | PRIMARY | 4 | test.classes.student_id | 1 | 100.00 | NULL |
可以发现，它使用了索引来过滤数据，但是需要查询多次，直到遍历所有满足条件的索引，因此效率没有eq_ref高。虽然ref的效率低于eq_ref，但是也比起全表扫描等操作高效得多。
总结
在MySQL中，eq_ref和ref连接类型在执行查询语句时有着重要的作用，eq_ref使用索引进行等值连接查询，效率高；ref使用普通索引进行查询，效率较低，但比全表扫描效率高。正确地选择和使用合适的连接类型，可以提高查询性能和优化查询计划。