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

[liao1314]

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左右。请问这个表结构或者索引方面该如何优化？

[十年彩虹]

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

[liao1314]

引用 8 楼 wwwwb 的回复:

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

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

[liao1314]

引用 7 楼 ACMAIN_CHM 的回复:

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

感谢版主耐心的回答，请看如图

[liao1314]

引用 7 楼 ACMAIN_CHM 的回复:

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

没注意不能用贴图，现在贴出文本形式： 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)

[wwwwb]

在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

[liao1314]

引用 13 楼 wwwwb 的回复:

在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

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

[wwwwb]

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

[liao1314]

引用 15 楼 wwwwb 的回复:

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

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

[wwwwb]

但是再多查一个字段，速度又慢下来了: 什么字段，有无索引？

[liao1314]

引用 17 楼 wwwwb 的回复:

但是再多查一个字段，速度又慢下来了: 什么字段，有无索引？

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

[WWWWA]

呵呵，你的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复合索引

[liao1314]

引用 19 楼 WWWWA 的回复:

呵呵，你的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复合索引

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

[ACMAIN_CHM]

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;

[wwwwb]

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

[liao1314]

肯定是有重复的，不然我没必要加了。去掉之后速度快的不是一点点，哈哈。因为一个最低级分类下面可能会有上百个子分类。同一件商品可以分到多个分类下去，这个地方不知道怎么设计表更有利于现在这样的查询。

[wshlxvb]

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

[WWWWA]

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

[liao1314]

引用 24 楼 shine333 的回复:

请问LZ，tid和id之间有什么关系。我没太看懂这个业务逻辑。 按照tid分组，如果同一tid可能对应多个id的话，你SELECT语句GROUP BY tid，但是没有对rate进行集约函数（sum/count/avg/...)统计，mysql会“随机”取任意一条数据的rate（其实是根据索引出来的先后）。所以，我完全没有理解这句语句的含义。IMHO，如果你多造点测试数……

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

[shine333]

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

[shine333]

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