|
system： 表仅有一行，是const类型的一个特例
- mysql> explain select * from (select * from test2 where id = 1000)a;
- +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
- | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
- | 2 | DERIVED | test2 | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
- +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
因为子查询只有一行数据，模拟了单表只有一行数据，此时type为system
const： 确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快
- mysql> explain select * from test where id =1 ;
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 8 | const | 1 | NULL |
- +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- 1 row in set (0.00 sec)
eq_ref： 对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key
- mysql> explain select * from test,test2 where test.com_key=test2.com_key;
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
- | 1 | SIMPLE | test2 | ALL | IDX(com_key) | NULL | NULL | NULL | 67993 | NULL |
- | 1 | SIMPLE | test | eq_ref | IDX(com_key) | IDX(com_key) | 194 | test.test2.com_key | 1 | NULL |
- +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
