mysql exists慢_mysql中exists执行过慢的问题

执行计划：

select类别不一样，执行计划也是不一样的，结果也不一样了

mysql> explain SELECT COUNT(1) from SPACE_DAILY a

-> where exists (select 1

->             from SPACE_DAILY b

->            where b.ts_name = a.ts_name

->              and b.inst_name = 'test')

->   and a.inst_name = 'test';

+----+--------------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

| id | select_type        | table | type  | possible_keys | key              | key_len | ref  | rows  | Extra                    |

+----+--------------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

|  1 | PRIMARY            | a     | index | NULL          | SPACE_DAILY_IDX1 | 132     | NULL | 82974 | Using where; Using index |

|  2 | DEPENDENT SUBQUERY | b     | index | NULL          | SPACE_DAILY_IDX1 | 132     | NULL | 82974 | Using where; Using index |

+----+--------------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

2 rows in set (0.00 sec)

mysql> explain select count(*) from SPACE_DAILY a where a.inst_name = 'test';

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows  | Extra                    |

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

|  1 | SIMPLE      | a     | index | NULL          | SPACE_DAILY_IDX1 | 132     | NULL | 82974 | Using where; Using index |

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+--------------------------+

1 row in set (0.00 sec)

mysql> explain select count(1) from SPACE_DAILY a,SPACE_DAILY b

-> where a.inst_name = 'test'

->    and b.inst_name = 'test'

->    and a.ts_name = b.ts_name;

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+---------------------------------------------+

| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows  | Extra                                       |

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+---------------------------------------------+

|  1 | SIMPLE      | a     | index | NULL          | SPACE_DAILY_IDX1 | 132     | NULL | 82974 | Using where; Using index                    |

|  1 | SIMPLE      | b     | index | NULL          | SPACE_DAILY_IDX1 | 132     | NULL | 82974 | Using where; Using index; Using join buffer |

+----+-------------+-------+-------+---------------+------------------+---------+------+-------+---------------------------------------------+

2 rows in set (0.00 sec)

作者: shiri512003

发布时间: 2009-07-24