Mysql-Explain(七):输出列-ref、rows
Mysql-Explain(一):explain简介和输出列解释
Mysql-Explain(二):explain实验数据准备
Mysql-Explain(三):输出列-id
Mysql-Explain(四):输出列-select_type
Mysql-Explain(五):输出列-type
Mysql-Explain(六):输出列-possiable_keys、key、key_len
Mysql-Explain(七):输出列-ref、rows
Mysql-Explain(八):输出列-extra
简介
ref | 哪些列或者常量被用做索引列上的值 |
rows | 根据表的统计信息和索引的使用情况,大致估算查询结果所需要读取记录的行数 |
演示
-
ref:哪些列或者常量被用做索引列上的值
mysql> explain select * from student where school_id = 1; +----+-------------+---------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ref | ik_schoolId_majorId | ik_schoolId_majorId | 5 | const | 1960 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.07 sec)
school_id =1,1是常量,ref=const
mysql> explain select * from student left join school on student.school_id = school.id; +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+---------+----------+-------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 1994142 | 100.00 | NULL | | 1 | SIMPLE | school | NULL | eq_ref | PRIMARY | PRIMARY | 4 | mydb.student.school_id | 1 | 100.00 | NULL | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+---------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
上面例子ref的值是mydb.student.school_id,因为表school被关联查询的时候,使用了主键索引,而值则使用了驱动表的mydb.student.school_id列。
-
rows:根据表的统计信息和索引的使用情况,大致估算查询结果所需要读取记录的行数;filtered:表示示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例
mysql> explain select * from student where school_id < 4 and major_id = 3; +----+-------------+---------+------------+------+--------------------------------+------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+--------------------------------+------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | student | NULL | ref | ik_schoolId_majorId,ik_majorId | ik_majorId | 5 | const | 4076 | 0.77 | Using where | +----+-------------+---------+------------+------+--------------------------------+------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.07 sec)
上面explain的分析结果显示,在存储引擎层面上大概需要扫描4076行记录,使用的索引是ik_majorId,所以数据在server层还需要对shcool_id条件进行过滤,过滤后的匹配比例是0.77。在我们平时的sql优化中rows一般是越小越好,代表所需扫描的表记录少,而filtered是越大越好,表明扫描的无用数据少,没有过多的多余的IO消耗。