|
(0.09 sec) Records: 599 Duplicates: 0 Warnings: 0 (root@localhost) [sakila]> select first_name,count(*) from customer group by first_name -> having count(*)>1 limit 2; +------------+----------+ | first_name | count(*) | +------------+----------+ | JAMIE | 2 | | JESSIE | 2 | +------------+----------+ 2 rows in set (0.00 sec) (root@localhost) [sakila]> explain select first_name from customer where first_name='JESSIE'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer type: ref possible_keys: idx_fisrt_last_name key: idx_fisrt_last_name key_len: 137 ref: const rows: 2 Extra: Using where; Using index 1 row in set (0.00 sec) (root@localhost) [sakila]> alter table customer drop index idx_fisrt_last_name; Query OK, 599 rows affected (0.03 sec) Records: 599 Duplicates: 0 Warnings: 0 --下面演示出现在join是ref的示例 (root@localhost) [sakila]> explain select b.*,a.* from payment a inner join -> customer b on a.customer_id=b.customer_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.b.customer_id rows: 13 Extra: NULL 2 rows in set (0.01 sec) 5、eq_ref 类似于ref，其差别在于使用的索引为唯一索引，对于每个索引键值，表中只有一条记录与之匹配。 多见于主键扫描或者索引唯一扫描。 (root@localhost) [sakila]> explain select * from film a join film_text b -> on a.film_id=b.film_id; +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ | 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 1000 | NULL | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 2 | sakila.b.film_id | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+ (root@localhost) [sakila]> explain select title from film where film_id=5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | film | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 6、const、system： 当MySQL对查询某部分进行优化，这个匹配的行的其他列值可以转换为一个常量来处理。 如将主键或者唯一索引置于where列表中，MySQL就能将该查询转换为一个常量 (root@localhost) [sakila]> create table t1(id int,ename varchar(20) unique); Query OK, 0 rows affected (0.05 sec) (root@localhost) [sakila]> insert into t1 values(1,'robin'),(2,'jack'),(3,'henry'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 (root@localhost) [sakila]> explain select * from (select * from t1 where ename='robin')x; +----+-------------+------------+--------+---------------+-------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------- |