请教一个优化

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)

这个查询该如何优化呢？ 表的索引是可以修改的，但结构不能修改。

Edengundam

1. 在apchistory表上给contextid加一个索引.
2. 请对表contexthistory执行:
select count(*) from contexthistory WHERE StopEventTime > 20080531000059
AND StopEventTime < 20080601000059;
并告诉我结果.

dutboy

我已经给contextid建立了一个索引，花了9个小时。

下面是执行结果：
mysql> select count(*) from contexthistory WHERE StopEventTime > 20080531000059
    -> AND StopEventTime < 20080601000059;
+----------+
| count(*) |
+----------+
|   322791 |
+----------+
1 row in set (16.69 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)

为射门执行计划好的反而执行起来慢呢？？？？

Edengundam

那天看选择率就知道第一个性能不好=.=
alarm选择率太高了, 强制用只会导致I/O更多

你要提示他用force index (pcb.a.ContextID)或者idxID

要把contexthistory作为主表.

yueliangdao0608

It's not a good idea to force index,I think.