Mysql-Explain(七):输出列-ref、rows

简介

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消耗。

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值