dutboy
请教一个优化
向各位请教个mysql查询优化。
有2个表：
DROP TABLE IF EXISTS apchistory;
CREATE TABLE apchistory (
ContextID bigint(20) NOT NULL default '0',
Label char(64) NOT NULL default '',
AlarmLevel char(40) NOT NULL default '',
PRIMARY KEY (ContextID,Label)
) TYPE=MyISAM;
有 10651729 条记录。
DROP TABLE IF EXISTS contexthistory;
CREATE TABLE contexthistory (
ContextID bigint(20) NOT NULL auto_increment,
StopEventTime char(16) NOT NULL default '',
dcqualityvalue double default NULL,
PRIMARY KEY (ContextID),
KEY HistoryIDIndex (HistoryID),
KEY StrategyID (StrategyID),
KEY StopEventTimeIndex (StopEventTime)
) TYPE=MyISAM;
有 1292043 条记录
使用的查询语句：
mysql> select count(distinct (contexthistory.contextid))
-> from contexthistory,apchistory
-> where contexthistory.contextid = apchistory.contextid
-> and alarmlevel > 0
-> and StopEventTime > 20080531000059
-> and StopEventTime < 20080601000059
-> and dcqualityvalue > 0;
+--------------------------------------------+
| count(distinct (contexthistory.contextid)) |
+--------------------------------------------+
| 124751 |
+--------------------------------------------+
1 row in set (1 min 37.73 sec)
耗时1分37秒
explain：
mysql> explain select count(distinct (contexthistory.contextid))
-> from contexthistory,apchistory
-> where contexthistory.contextid = apchistory.contextid
-> and alarmlevel > 0
-> and StopEventTime > 20080531000059
-> and StopEventTime < 20080601000059
-> and dcqualityvalue > 0;
+----+-------------+----------------+--------+----------------------------+---------+---------+--------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+--------+----------------------------+---------+---------+--------------------------+----------+-------------+
| 1 | SIMPLE | apchistory | ALL | PRIMARY | NULL | NULL | NULL | 10651729 | Using where |
| 1 | SIMPLE | contexthistory | eq_ref | PRIMARY,StopEventTimeIndex | PRIMARY | 8 | pcb.apchistory.ContextID | 1 | Using where |
+----+-------------+----------------+--------+----------------------------+---------+---------+--------------------------+----------+-------------+
2 rows in set (0.03 sec)
这个查询该如何优化呢？ 表的索引是可以修改的，但结构不能修改。
dutboy
真是被搞败，搞了半天后，explain如下：
mysql> explain select count(distinct(c.contextid)) from contexthistory c left join apchistory a force index (idxalarm) on c.contextid=a.contextid wher
e StopEventTime > 20080531000059 and StopEventTime < 20080601000059 and dcqualityvalue > 0 and a.alarmlevel in ('1','3');
+----+-------------+-------+--------+---------------+----------+---------+-----------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+----------+---------+-----------------+---------+-------------+
| 1 | SIMPLE | a | range | idxAlarm | idxAlarm | 40 | NULL | 4604840 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,idxm | PRIMARY | 8 | pcb.a.ContextID | 1 | Using where |
+----+-------------+-------+--------+---------------+----------+---------+-----------------+---------+-------------+
2 rows in set (0.00 sec)
执行起来，耗时10分8.66秒。
另一个查询：
mysql> explain select count(distinct(c.contextid)) from contexthistory c left join apchistory a on c.contextid=a.contextid where StopEventTime > 20080
531000059 and StopEventTime < 20080601000059 and dcqualityvalue > 0 and a.alarmlevel in ('1','3');
+----+-------------+-------+--------+------------------------+---------+---------+-----------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------+---------+---------+-----------------+----------+-------------+
| 1 | SIMPLE | a | ALL | PRIMARY,idxID,idxAlarm | NULL | NULL | NULL | 10651729 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY,idxm | PRIMARY | 8 | pcb.a.ContextID | 1 | Using where |
+----+-------------+-------+--------+------------------------+---------+---------+-----------------+----------+-------------+
2 rows in set (0.03 sec)
执行：
+------------------------------+
| count(distinct(c.contextid)) |
+------------------------------+
| 124751 |
+------------------------------+
1 row in set (1 min 53.13 sec)
为射门执行计划好的反而执行起来慢呢？？？？