我有一个大的mysql表('d_operations'),有超过200万条记录(还有更多)。我编写了一个PHP网页,显示了一个图表,其中显示了一天中每半小时(0:00 0:30 1:00 1:30…)的操作次数。23:59)。它工作得很好,但需要太多的时间来获得结果,所以我想知道我的表和查询是否可以优化。
一天中每半小时,我执行一个select查询,询问MySQL在这段时间内完成的操作数量。这需要超过一分钟才能完成!
表模式:
mysql> describe d_operations;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| idx | int(11) unsigned | NO | PRI | NULL | auto_increment |
| system_id | int(11) | YES | | NULL | |
| dev_id | varchar(17) | YES | | NULL | |
| name | varchar(17) | YES | | NULL | |
| nond | smallint(6) | YES | | NULL | |
| is_new | smallint(6) | YES | | NULL | |
| tstamp | int(10) unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
我有一个auto_increment主键,这似乎对查询没有帮助。其余字段可以重复(一个设备可以在这段时间内执行多个操作,并且可以是具有相同戳记的行)。tstamp是UNIX时间戳
在PHP中我是这样做查询的:
for($i=$GLOBALS['init_hour'];$i<=($GLOBALS['end_hour']-1800);$i+=1800){
$n=$i+1800;
$sql="SELECT count(*) as num from d_operations where (tstamp >= $i and tstamp < $n);";
$r=mysqli_query($GLOBALS['con'],$sql);
$row = mysqli_fetch_row($r);
$values = ($i == $GLOBALS['init_hour']) ? $row[0] : $values.",".$row[0];
$GLOBALS['a_average'][$i]=$row[0];
}
在最坏的情况下,我在当天每半小时循环一次,即48次查询。
这是MySQL解释命令:
mysql> explain select count(*) as num from d_operations where (tstamp >= 1464739200 and tstamp < 1464825599);
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | d_operations | ALL | NULL | NULL | NULL | NULL | 2215384 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
有没有更有效的方法来做这件事?(表定义,MySQL查询优化…)
谢谢
正如Jon Stirling和Mark Baker建议的那样,解决方案非常简单,只需为邮票列创建一个索引:
ALTER TABLE d_operations ADD INDEX ts_index(tstamp);
谢谢!