MySql表性能优化

Question

我有一张具有以下结构的桌子

CREATE TABLE rel_score (
  user_id bigint(20) NOT NULL DEFAULT '0',
  score_date date NOT NULL,
  rel_score decimal(4,2) DEFAULT NULL,
  doc_count int(8) NOT NULL
  total_doc_count int(8) NOT NULL
  PRIMARY KEY (user_id,score_date),
  KEY SCORE_DT_IDX (score_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PACK_KEYS=1

从2000年1月1日到今天，该表将存储应用程序中每个用户的rel_score值。我估计记录的总数将超过7亿。我用6个月的数据(大约3000万行)填充了表，查询响应时间约为8分钟。这是我的疑问，

select 
  user_id, max(rel_score) as max_rel_score
from
  rel_score
where score_date between '2012-01-01' and '2012-06-30'
group by user_id
order by max_rel_score desc;

我尝试使用以下技术优化查询，

1. score_date列上的分区
2. 在score_date列上添加索引

查询响应时间略有改善，仅略少于8分钟。

如何提高响应时间？表的设计是否合适？

此外，由于允许用户查询整个数据范围，所以我不能将旧数据移到存档中。

Answer 1

如果在score_date的同一级别上对表进行分区，则不会减少查询响应时间。

尝试创建另一个只包含日期年份的attribut，将其转换为整数，在此attribut上对表进行分区(您将得到13个分区)，并重新执行查询以查看。

Answer 2

您的主要索引应该能很好地覆盖表。如果您没有它，我建议在rel_score(user_id, score_date, rel_score)上建立一个索引。对于您的查询，这是一个“覆盖”索引，这意味着索引包含查询中的所有列，因此引擎不必访问数据页(只有索引)。

下面的版本也可以很好地利用这个索引(尽管我更喜欢您的查询版本)：

select u.user_id,
       (select max(rel_score)
        from rel_score r2
        where r2.user_id = r.user_id and 
              r2.score_date between '2012-01-01' and '2012-06-30'
      ) as rel_score
from (select distinct user_id
      from rel_score
      where score_date between '2012-01-01' and '2012-06-30'
     ) u
order by rel_score desc;

这个查询的思想是用一个简单的索引查找来代替聚合。MySQL中的聚合是一种缓慢的操作--它在其他数据库中运行得更好，因此不需要这样的技巧。