010--Explain实战详解：ref分析

最新推荐文章于 2024-11-26 09:41:13 发布

原创最新推荐文章于 2024-11-26 09:41:13 发布 · 2.3k 阅读

0 ·

CC 4.0 BY-SA版权

本文深入解析MySQL中的索引使用情况，特别是ref列的作用及其影响因素。通过具体案例展示了不同查询条件下索引的选择与利用方式，帮助读者理解如何优化查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.ref理解

ref:显示索引的哪一列被使用了，有时候会是一个常量：表示哪些列或常量被用于用于查找索引列上的值

2.ref分析

库.表.字段
常量

3.key-len案例

mysql> explain select * from t1,t2 where t1.id = t2.id and t1.id='1';
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys         | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | const | PRIMARY,index_id_name | PRIMARY | 4       | const |    1 |       |
|  1 | SIMPLE      | t2    | const | PRIMARY               | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-----------------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)

mysql> explain SELECT * from t1,t2 where t1.id = t2.id and t1.`name` = 'downeyjr_1';
+----+-------------+-------+--------+-----------------------+---------+---------+--------------+------+-------------+
| id | select_type | table | type   | possible_keys         | key     | key_len | ref          | rows | Extra       |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------+------+-------------+
|  1 | SIMPLE      | t2    | ALL    | PRIMARY               | NULL    | NULL    | NULL         |    1 |             |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,index_id_name | PRIMARY | 4       | mysql1.t2.id |    1 | Using where |
+----+-------------+-------+--------+-----------------------+---------+---------+--------------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain SELECT * from t1,t3 where t1.id = t3.id and t3.age='10';
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------------+
| id | select_type | table | type  | possible_keys         | key           | key_len | ref  | rows | Extra                          |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,index_id_name | index_id_name | 37      | NULL |    3 | Using index                    |
|  1 | SIMPLE      | t3    | ALL   | PRIMARY               | NULL          | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+-------+-----------------------+---------------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)