使用16+秒的简单mysql查询

Question

使用16秒以上的简单sql查询。这是桌子。

CREATE TABLE IF NOT EXISTS `udr` (
  `userid` int(11) NOT NULL DEFAULT '0',
  `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upbytes` int(11) NOT NULL DEFAULT '0',
  `downbytes` int(11) NOT NULL DEFAULT '0',
  `traffictype` int(11) NOT NULL DEFAULT '1',
  KEY `userid` (`userid`),
  KEY `time` (`time`),
  KEY `traffictype` (`traffictype`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

有1600万条记录。(原来有1.41亿人，我修剪了一下，看看是不是问题所在)

所讨论的查询是(类似)

select time,upbytes,downbytes
 from udr
  where userid = 315533 and 
        time between '2014-01-01 14:35:28' and '2014-01-02 14:35:28'

我认为问题是时间部分，所以我去掉了时间条件，然后试着遵循

select time,upbytes,downbytes from udr where userid = 315533
...
10282 rows in set (19.42 sec)

不过，查询需要16秒以上的时间。

这是我的关键配置参数

key_buffer    = 32M
max_allowed_packet  = 16M
thread_stack    = 192K
thread_cache_size       = 8
query_cache_limit = 1M
query_cache_size        = 16M

在这件事上没有什么想法了。

谢谢

mysql> explain select * from udr where userid = '315533';
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows  | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
|  1 | SIMPLE      | udr   | ref  | userid        | userid | 4       | const | 12738 |       |
+----+-------------+-------+------+---------------+--------+---------+-------+-------+-------+
1 row in set (0.02 sec)

碘顶报告沉重的磁盘io。我怀疑mysql正在检索内存中的所有记录。

Answer 1

SELECT time,upbytes,downbytes
    FROM udr
    WHERE userid = 315533 AND
    time BETWEEN '2014-01-01 14:35:28' AND '2014-01-02 14:35:28'

在这个查询中，您在两个列( time和userid )上进行搜索。这两个列都有索引，但是MySQL一次只能使用其中的一个。

MySQL将选择使用time-index或userid索引。如果选择userid，则必须检索12738行，这将导致查询时间过长。然后，MySQL将搜索检索到的数据，以筛选出time-column。

解决方案是在这两个列上添加一个索引：

ALTER TABLE udr ADD KEY (`userid`, `time`);

这样，MySQL就可以在userid和time上进行搜索，而无需首先检索数据。

注意:创建新索引可能需要几分钟时间，请耐心等待。

Answer 2

尝试在time和userid中建立索引，userid.you可以很容易地使用phpmyadmin或sqlyog建立索引。

Answer 3

但是，仅使用userid的查询仍然花费了几乎相同的时间。

你不能再做更多了。你的硬件规格是什么？我的心声比你的一般，而且比你的高：

query_cache_size =  64 MiB
key_buffer =  128 MiB
max_allowed_packet  =  16M
thread_stack    =  294 Kb
thread_cache_size       = 128
query_cache_limit =  128 KiB

我建议使用MariaDB / Percona代替标准的MySQL，然后用MYISAM再试一次。他们和Aria一起进步了很多..。