同时使用WHERE，GROUP BY，ORDER BY时，如何建合理的索引？

Question

CREATE TABLE `product` (
  `id` int(10) NOT NULL auto_increment,
  `tid` bigint(15) default NULL,
  `cid` mediumint(8) default NULL,
  `pid` mediumint(8) default NULL,
  `rid` mediumint(8) default NULL,
  `pubtime` int(10) default NULL,
  `dateline` int(10) default NULL,
  `count` mediumint(8) default '0',
  `rate` double default NULL,
  `title` varchar(32) default NULL,
  `score` tinyint(2) default NULL,
  `price` double default NULL,
  `pic` varchar(127) default NULL,
  `tags` varchar(64) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `cid_tid` USING BTREE (`cid`,`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
cid为商品的分类ID
pid为商品分类的父级父类ID
rid为最顶尖分类ID
tid为商品ID
rate为商品的评价分频繁使用的查询语句如下：SELECT * FROM product WHERE rid=xxx GROUP BY tid ORDER BY rate DESC LIMIT 0, 20;目前表数据大概有40W，表的特点是rid为最顶级分类只有6个值，同一个商品被分配到多个类目下，所以用GROUP BY tid去重。尝试使用索引(rid,tid)或者(rid,tid,rate)，查询速度会快一些，在600ms左右。请问这个表结构或者索引方面该如何优化？

Answer 1

优化不能光看一句SQL，一个index的。这个要慢慢各方面调的我每次问DBA，他们都这么回答我

Answer 2

看看其它有什么地方更有效。单存index解决不了很多问题。

Answer 3

create index xxx on product (rid,tid)

Answer 4

这个试过了，速度有所提升。不过即使从limit从0开始都需要600ms左右。

Answer 5

贴出
show index from ..
explain select ... 
以供分析

Answer 6

去掉ORDER BY rate DESC LIMIT 0, 20　速度有无提高

Answer 7

分析一下的SQL语句，主要浪费时间是在order by 和group by 语句上，特别是order by 语句。
针对order by 语句优化：考虑到表已经存在40W条记录，你可以建立几张新表，以rate字段分片存储，例如
满分为100分，那第一张表可以存储rate分值为0-30分的记录,第二张表可以存储rate分值30-60的记录....
以此类推。
先把rate值排好序，按分值存储在多个表中，减少表中记录数，并且每个表已经按rate值
排完序，这样可以直接优化order by 语句。
group by 语句同理。
总而言之，就是用空间换时间，对大表进行分片存储，达到查询时间的优化目的。
分片存储的代价，必须通过触发器或者程序来对多个表的添加，删除操作进行验证执行。

Answer 8

去掉ORDER BY之后速度明显提升，20ms左右。同样测试去掉GROUP BY，把索引换成(rid,rate)，也是只有20ms左右。所以我觉得可能还有优化空间

Answer 9

没注意不能用贴图，现在贴出文本形式：mysql> SHOW INDEX FROM product;EXPLAIN SELECT * FROM product WHERE rid=1000 GROUP BY tid ORDER BY rate DESC LIMIT 0, 20;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| product |          0 | PRIMARY  |            1 | id          | A         |      308726 |     NULL | NULL   |      | BTREE      |         | 
| product |          0 | cid_tid  |            1 | cid         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| product |          0 | cid_tid  |            2 | tid         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         | 
| product |          1 | rid_tid  |            1 | rid         | A         |           6 |     NULL | NULL   | YES  | BTREE      |         | 
| product |          1 | rid_tid  |            2 | tid         | A         |      308726 |     NULL | NULL   | YES  | BTREE      |         | 
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
 
+----+-------------+---------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
| id | select_type | table   | type | possible_keys | key     | key_len | ref   | rows  | Extra                                        |
+----+-------------+---------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | product | ref  | rid_tid       | rid_tid | 4       | const | 83274 | Using where; Using temporary; Using filesort | 
+----+-------------+---------+------+---------------+---------+---------+-------+-------+----------------------------------------------+
1 row in set (0.00 sec)

Answer 10

在RID、TID、RATE上建立复合索引，并强制使用此索引试试
CREATE INDEX id1 ON product (RID、TID、RATE);
SELECT * FROM product force index (id1) WHERE rid=xxx GROUP BY tid ORDER BY rate DESC LIMIT 0, 20

Answer 11

这个在一开始就试过了，索引为(rid,tid)和(rid,tid,rate)效率一样，都在700ms。加了force无啥变化。

Answer 12

建立(rid,tid,rate）索引后，SELECT TID FROM product force index (id1) WHERE rid=xxx 
GROUP BY TID ORDER BY rate DESC LIMIT 0, 20
　速度如何

Answer 13

只查tid，速度提升了不少，150ms左右。但是再多查一个字段，速度又慢下来了。这个什么原因呢？

Answer 14

即使是主键id字段都不行。除了当前使用到的索引(rid,tid,rate)中的字段外，其他多任意一个字段速度就慢下来

Answer 15

呵呵，你的SQL语句
SELECT * FROM product force index (id1) WHERE rid=xxx GROUP BY tid ORDER BY rate DESC LIMIT 0, 20
不是标准的SQL语句　优化只针对具体的SQL语句，假设加入ID
ID,rid,tid,rate复合索引

Answer 16

这个表的大部分字段都需要select出来，不可能索引把这些字段都加上吧。

Answer 17

create index xxxx on product(rid,tid,rate);另外，从实际数据分布上看，(rid,tid）没有重复的，如果这样语句中可以不用 GROUP BY tid 
此时，直接创建索引
create index yyy on product(rid,rate);
然后
SELECT * FROM product 
WHERE rid=1000 
ORDER BY rate DESC LIMIT 0, 20;

Answer 18

没办法，只能根据具体的SQL语句来进行优化

Answer 19

请问LZ，tid和id之间有什么关系。我没太看懂这个业务逻辑。
按照tid分组，如果同一tid可能对应多个id的话，你SELECT语句GROUP BY tid，但是没有对rate进行集约函数（sum/count/avg/...)统计，mysql会“随机”取任意一条数据的rate（其实是根据索引出来的先后）。所以，我完全没有理解这句语句的含义。IMHO，如果你多造点测试数据的话，或许会发现这个SQL根本就是个BUG。如果这句SQL不是个BUG，显然你的表结构违反了范式，而且看不出这样设计的明显理由

Answer 20

很高兴您的回答。
tid表示商品的唯一ID，同一个商品能够被分到不同的分类cid，所以有(cid，tid)的唯一索引，rate表示他在各自的分类下的受喜欢程度。rid表示所在分类的最顶尖分类。也就是当我在最顶尖分类的显示时候不想把重复的tid都搜索出来，至于违反不违反范式这个真不太懂，我只是需要这样的业务实现。当然如果有好的表的设计改进办法当然好，只是我不太清楚怎么弄。

Answer 21

商品和Product什么区别？？？？