数据库查询 BETWEEN、timestamp RANGE， GROUP BY 一起用，求优化

CREATE TABLE test (
  id int(1) unsigned NOT NULL,
  pid int(1) unsigned NOT NULL,
  days tinyint(1) unsigned NOT NULL,
  age tinyint(1) unsigned NOT NULL,
  price decimal(9,2) unsigned NOT NULL DEFAULT '0.00',
  status tinyint(1) unsigned NOT NULL DEFAULT '0',
  mtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary kye (id,pid,days,age)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

其中， id, pid, days, age 这 4 个字段是联合主键（至少应该唯一），如果优化不方便，也可以程序里实现这逻辑

表数据量大概是 1000 万，对这个表最常用的查询语句

SELECT id,days
  FROM test
 WHERE days BETWEEN 100 AND 150 AND mtime < (NOW() + INTERVAL 1 DAY)
 GROUP BY id;

求优化方案，现在这条语句每次都使用 Using temporary; Using filesort 导致查询速度很慢

6 条回复 • 2016-08-04 13:37:29 +08:00

palfortime

2016-08-01 20:09:12 +08:00

id 字面意思应该就是唯一的。。为什么还要联合？ int(1)不是指只能存一位，它还是能存 int 范围里的数。
你应该描述一下数据在 id ， pid ， days ， mtime 的分布，新增数据时，这几个值会是怎样。

fuxkcsdn

2016-08-02 00:30:34 +08:00 via iPhone

@palfortime
实际的数据库不是这些字段名，我只是随意取个名而已。
数据类型，表结构都和实际数据库一样的，示例数据如下

INSERT INTO test(id, pid, days, age, price, status) VALUES
(1, 1, 20, 30, 65.23, 1),
(1, 2, 21, 30, 100.30, 1),
(1, 6, 33, 54, 121.33, 0),
(1, 2, 34, 33, 111.22, 2),
(3, 454, 43, 64, 22.98, 1);

palfortime

2016-08-02 01:39:22 +08:00

@fuxkcsdn
mtime < (NOW() + INTERVAL 1 DAY) 这个没有意义吧，一定为 true 。
能说一下 days 的取值范围吗？它在这个范围里分布均匀吗？假如 days 取值范围足够大，而且均匀，可以根据它来建索引。不过对于 1000 万条数据， days 就算能取值 0~10000 ，每条索引也会对应 1000 条记录，速度也不快。
你的 sql 有两个字段是范围比较，肯定会有 Using filesort ，可以的话，你描述一下你的业务吧。要根据业务重构那条 sql ，让它尽可能用上索引，你现在的 sql 是完全没有用上索引的。

fuxkcsdn

2016-08-02 02:07:41 +08:00 via Android

@palfortime
mtime < (NOW() - INTERVAL 1 DAY)
这里应该是减一天，发帖时写错了…
days 的取值范围在 1 到 180

业务需求是获取 days 区间内已超时的大类产品

比如 days 在 1 到 15 ，且最后修改时间是 10 分钟前的大类产品，取得大类产品 id 和对应的 days ，然后将其添加到第一优先队列去抓取数据

SELECT id,age,days
FROM test
WHERE days BETWEEN 1 AND 15 AND mtime < (NOW() - INTERVAL 10 MINUTES)
GROUP BY id,age

days 在 16 到 40 间，最后修改时间是半小时前的则添加到第二队列

之所以只需要大类产品 id 是因为抓取数据时大类产品下会包含对应的子产品信息，所以队列任务里只要大类产品信息，至于 age 字段，则是标识大类产品的一个查询字段，根据该字段会得到不同的报价(所以我上面的 SQL 里也忘了要根据这个字段 GROUP BY 了)
age 字段的取值范围在 1 到 20

palfortime

2016-08-02 21:20:28 +08:00

@fuxkcsdn
按你这样子说，应该加 mtime 的索引，查询时加上 mtime 的下限，例如， mtime>(Now() - INTERVAL 1 DAYS)，下限取值依赖于你们的业务。假如每次查询出来的记录达到万级的话，加 mtime 索引也没有什么用。
假如 mtime 会经常被更新的话，加索引会导致写速度变慢。
索引：
假如多读少写可以
mtime, id, age, days, 这样子查询时，不用在寻址读数据
假如多写就只加：
mtime

更好的实现方式是，不要用 mysql 做队列，例如，用 redis 按小时来记录最近更新的大类产品，弄个定时任务来按小时扫。

fuxkcsdn

2016-08-04 13:37:29 +08:00

@palfortime
现在把队列分成 2 部分了
一部分需要 group by 的查询独立在一张表里，精简掉 group by
另一部分大量数据的查询则根据业务需求放到 redis 里

数据库里都是精确查询了，索引只加在 id,pid,days,age 上

然后我现在有个疑问
CREATE TABLE test (....
PRIMARY KEY (id,days,pid,age),
KEY (days),
KEY (pid),
KEY (age)
) ENGINE=....
因为这几个字段一经写入就不会再更新（ PRIMARY KY 字段顺序是根据对这长表最常用的查询语句，做的最后调整的结果）

我多添加的这几个 INDEX 是否有必要？？
是否有必要添加 KEY (id) 这个索引？？

UPDATE 语句只会更新 price 、 status 和 mtime 字段
INSERT 语句只有在增加产品的时候才会执行，而且只执行一次