请教一条几百万数据的mysql查询

Question

在几百万数据里查询出适合条件的记录
而且还有关联表，以下是我写的语句，效率太差，想请教高手，还可以怎么改？
SELECT p.author,p.tid, p.authorid,p.dateline, p.message, p.support, p.oppose,t.subject FROM cdb_posts p join cdb_threads t ON p.tid = t.tid AND p.dateline>'$saturday' AND p.dateline<'$friday' AND first=0 AND p.support > 10 group by p.tid ORDER BY p.support DESC LIMIT 20

Answer 1

中，$saturday 是指上周六的时间秒数， $friday 是指这周五的时间秒数，就是查出从上周六到这周五这个时间段内的数据，并按p.tid 分类，使得每种分类只要出现一条记录就可以了，关联另一张表查出分类的另一字段，最后按support降序排列。

Answer 2

EXPLAIN 你的SQL语句SHOW INDEX FROM 你的表名，贴结果

Answer 3

SELECT p.author,p.tid, p.authorid,p.dateline, p.message, p.support, p.oppose,t.subject 
FROM cdb_posts p join cdb_threads t ON p.tid = t.tid 
AND p.dateline>'$saturday' 
AND p.dateline<'$friday' 
AND first=0 
AND p.support > 10 
group by p.tid 
ORDER BY p.support 
DESC LIMIT 201. first 来源于哪个表？
2. 为什么要用 group by p.tid  ?
3。建议贴出以下信息
 show index from cdb_posts;
 show index from cdb_threads;
 explain SELECT p.author,p.tid, p.authorid,p.dateline, p.message, p.support, p.oppose,t.subject 
FROM cdb_posts p join cdb_threads t ON p.tid = t.tid 
AND p.dateline>'$saturday' 
AND p.dateline<'$friday' 
AND first=0 
AND p.support > 10 
group by p.tid 
ORDER BY p.support 
DESC LIMIT 20 ;4. 建议描述一想你想实现的功能，及表中数据结构。

Answer 4

对了，我刚才试了一下，发现p.tid 没有创建索引，dateline 有创建了索引，然后要是把group by p.tid 去掉的话，那查询就快很多，而加了这个分组就变多了快一百倍了，是不是应该也给tid 建一个索引，这样会快吗？

Answer 5

TID是连接字段，一般应该加索引

Answer 6

回复ACMAIN_CHM ：
这个数据库是discuz的数据库，里面的索引还是默认的，
1、first 来源于cdb_posts 
2、我想按p.tid 分组，这样才能实现每种主题贴里只显示一条回复。
Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  
cdb_posts 0 PRIMARY 1 pid A 58 NULL NULL   BTREE   
cdb_posts 1 fid 1 fid A NULL NULL NULL   BTREE   
cdb_posts 1 authorid 1 authorid A NULL NULL NULL   BTREE   
cdb_posts 1 dateline 1 dateline A NULL NULL NULL   BTREE   
cdb_posts 1 invisible 1 invisible A NULL NULL NULL   BTREE   
cdb_posts 1 displayorder 1 tid A NULL NULL NULL   BTREE   
cdb_posts 1 displayorder 2 invisible A NULL NULL NULL   BTREE   
cdb_posts 1 displayorder 3 dateline A NULL NULL NULL   BTREE   
cdb_posts 1 first 1 tid A NULL NULL NULL   BTREE   
cdb_posts 1 first 2 first A NULL NULL NULL   BTREE   Table  Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  
cdb_threads 0 PRIMARY 1 tid A 23 NULL NULL   BTREE   
cdb_threads 1 digest 1 digest A NULL NULL NULL   BTREE   
cdb_threads 1 sortid 1 sortid A NULL NULL NULL   BTREE   
cdb_threads 1 displayorder 1 fid A NULL NULL NULL   BTREE   
cdb_threads 1 displayorder 2 displayorder A NULL NULL NULL   BTREE   
cdb_threads 1 displayorder 3 lastpost A NULL NULL NULL   BTREE   
cdb_threads 1 typeid 1 fid A NULL NULL NULL   BTREE   
cdb_threads 1 typeid 2 typeid A NULL NULL NULL   BTREE   
cdb_threads 1 typeid 3 displayorder A NULL NULL NULL   BTREE   
cdb_threads 1 typeid 4 lastpost A NULL NULL NULL   BTREE   这是执行explain 后的显示 不过，里面的数据现在很少，因为这是我自己的数据库，不是论坛里的
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE p index dateline,displayorder,first first 4 NULL 58 Using where; Using temporary; Using filesort 
1 SIMPLE t eq_ref PRIMARY PRIMARY 3 discuz.p.tid 1   我要实现的功能：
 查出从上周六到这周五这个时间段内的数据，并按p.tid 分类，使得每种分类只要出现一条记录就可以了，并且，这条记录还不能是第一贴，（first=0）关联另一张表查出分类的另一字段，主题贴标题，最后按support降序排列。

Answer 7

对了，再请教一下，改成用left join 和现在这样直接join 有影响吗，
现在的时间比较好还是用between $saturday and $friday 好？

Answer 8

left join 会略慢一点点，影响不大。
between 和或者直接 > < ,没什么差别。

Answer 9

一般来讲，INNER　JOIN速度最快，用BETWEEN　AND代码精简一些

Answer 10

AND p.dateline>'$saturday' AND p.dateline<'$friday' 
AND p.first=0 
AND p.support > 10 
group by p.tid 
ORDER BY p.support 这里主要涉及 dateline,first,support,tid 四列数据的索引安排。
如果创建索引，要看你的数据分布。比如你估计全表数据会有多少条？
p.dateline>'$saturday' AND p.dateline<'$friday'  会有多少条？
p.first=0 的会有多少条？
p.support > 10 的会有多少条？
同一p.tid 下平均会有多少条记录？从目前你的情况来看是用了 (tid,first)这个索引，但一般假设中应该是想办法先用 (dateline) 索引。但具体要了解上面几个问题后才能回答。

Answer 11

如果可以，上传你所使用的表、数据及要求结果，导出为TXT文件,上传到www.access911.net/csdn
，用WINRAR压缩
才能测试

Answer 12

对对对，确实是这样的，我也觉得应该用dateline做索引，但是，就是不知道应该怎么改才能让它变在这样的效果。
我估算了一下，全表大概671W记录
在那个时间段内的就不会很多
大部分都是first = 0
support > 10 一般不会很多
同一p.tid下的平均记录应该也不会太多，目前最多的差不多1k

Answer 13

cdb_posts 表中记录总数 6,710,000 就算 10,000,000 条记录.
在那个时间段内的就不会很多 ? 不会很多，那就算10 条？！
大部分都是first = 0， 99%的记录都是 first=0 ?
support > 10 一般不会很多, 不会很多是多少 10M 记录中一共最多 50 条support > 10 ?!每个 tid 下估计有记录 1000 条左右？ 也就是说表中应该有 10M/1K = 10,000 个不同的 tid ?

Answer 14

呵呵，因为这些数据本来就是会变的，所以没办法说的太详细，不好意思，
我重写一个：
在那个时间段内的大概四五万条
90% 都是first = 0 
support > 10 大概占总数的10%
同一p.tid下的平均记录应该也不会太多，目前最多的差不多1k 

Answer 15

cdb_posts 表中记录总数 6,710,000 就算 8,000,000
where p.dateline>'$saturday' AND p.dateline<'$friday' , count(*) = 50,000
where first = 0 , count(*) = 90% * 8,000,000 = 7,200,000
where support > 10 , count(*) = 10% * 8,000,000 = 800,000tid , count(*) = 1,000
select distinct tid  = 8,000,000/1,000 = 10,000 个不同的 tid, 取其中 support 最大的 20 个每种分类只要出现一条记录就可以了? 随机取一条？还是取其中 support 最大的一条？
粗步仅以上述信息来看，建议索引 (support,dateline) ,假设suppport 为integer型，估计取值范围 0-40, 这样通过索引可以将数据减少到 10%*50,000 = 5000 条
或者(tid,support,dateline)

Answer 16

如果效率允许的话，那当然最好取support最大的，后面那个方法我没怎么看懂，你的意思就是说添加(support,dateline)这两列的索引吗？或者再加上(tid),
对于这句查询语句是不是没办法再改了，只能通过索引来优化了是吧。对了，还有我试了一下，要是把group by 去掉，那查询的记录数只要7W多条就可以了，加上分组后就变成了整表查询了，这个跟tid有没有建索引有关系吗？因为我现在不方便修改数据结构，所以想尽量通过修改查询语句来实现，实在不行，那我想就只能先不分组的查询，然后在程序里实现分组筛选工作，麻烦也只能麻烦一点了。

Answer 17

也就是说，不能再创建新的INDEX，只能利用现有的INDEX ？

Answer 18

办法是有，比如你可以SELECT p.author,p.tid, p.authorid,p.dateline, p.message, p.support, p.oppose,t.subject 
from (select tid,author,tid, authorid,dateline, message, support, oppose
from cdb_posts
where p.dateline>'$saturday' AND p.dateline<'$friday')p , cdb_threads t
where p.tid = t.tid 
AND p.first=0 
and p.support > 10 
group by p.tid 
ORDER BY p.support 
DESC LIMIT 20先试一下效果如何。优化要很有耐心，在多种方案中进行选择。　你可以先通过子查询来人为的确定筛选的先后顺序。没有你具体的数据，很难进行分析。

Answer 19

我试了一下，这句好象效果还是差不多，那接下来是不是应该改成这样：
SELECT p.author,p.tid, p.authorid,p.dateline, p.message, p.support, p.oppose,t.subject 
from (select tid,author,tid, authorid,dateline, message, support, oppose
    from cdb_posts p
    where p.support > 10 )p , cdb_threads t
where p.tid = t.tid 
AND p.first=0 
and p.dateline>'$saturday' AND p.dateline<'$friday'group by p.tid 
ORDER BY p.support 
DESC LIMIT 20
然后再把别的条件放到子查询中，再看一下，哪个的效果最好是不是?
原理是不是应该把记录数最少的条件当做子查询？这样查询的效率才最高？

Answer 20

<a href="#">耐心也要有方法才行</a>