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

在几百万数据里查询出适合条件的记录
而且还有关联表，以下是我写的语句，效率太差，想请教高手，还可以怎么改？
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

35 个解决方案

#1

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

#2

表的索引情况贴出来看看

#3

在索引情况上我也不清楚，好象就只有主键唯一索引，应该怎么改，这句里有哪些是比较耗资源的条件？

#4

引用 3 楼 jichangh1983 的回复:

在索引情况上我也不清楚，好象就只有主键唯一索引，应该怎么改，这句里有哪些是比较耗资源的条件？

EXPLAIN 你的SQL语句

SHOW INDEX FROM 你的表名，贴结果

#5

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

1. 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. 建议描述一想你想实现的功能，及表中数据结构。

#6

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

#7

不一定的，不过你可以先加索引试一下。
具体如何合理的创建索引需要很多信息。

建议你提供一个
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. 建议描述一想你想实现的功能，及表中数据结构。

#8

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

#9

回复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降序排列。

#10

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

#11

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

#12

这样啊，谢谢ACMAIN_CHM ！

#13

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

#14

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) 索引。但具体要了解上面几个问题后才能回答。

#15

学习了，谢谢！
我写的这个语句，在这样的环境下，还能不能再优化啊，现在这句在线测试时反应太慢了，肯定不能上线，但不知道应该怎么改才行?
拜托两位大牛帮忙想个办法，非常感谢！

#16

如果可以，上传你所使用的表、数据及要求结果，导出为TXT文件,

上传到www.access911.net/csdn
，用WINRAR压缩
才能测试

#17

引用

我写的这个语句，在这样的环境下，还能不能再优化啊，现在这句在线测试时反应太慢了，肯定不能上线，但不知道应该怎么改才行?

已经说得很清楚了。我不可能猜出你这个表中的数据分布！

引用

比如你估计全表数据会有多少条？
p.dateline>'$saturday' AND p.dateline <'$friday'  会有多少条？
p.first=0 的会有多少条？
p.support > 10 的会有多少条？
同一p.tid 下平均会有多少条记录？

你希望我来猜？

#18

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

#19

引用

我估算了一下，全表大概671W记录
在那个时间段内的就不会很多
大部分都是first = 0
support > 10 一般不会很多
同一p.tid下的平均记录应该也不会太多，目前最多的差不多1k

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 ?

#20

引用 16 楼 WWWWA 的回复:

如果可以，上传你所使用的表、数据及要求结果，导出为TXT文件, 

上传到www.access911.net/csdn 
，用WINRAR压缩 
才能测试

不好意思，我这样公司的数据库，不方便上传，请见谅！

#21

引用 19 楼 ACMAIN_CHM 的回复:

引用我估算了一下，全表大概671W记录 
在那个时间段内的就不会很多 
大部分都是first = 0 
support > 10 一般不会很多 
同一p.tid下的平均记录应该也不会太多，目前最多的差不多1k 

cdb_posts 表中记录总数 6,710,000 就算 10,000,000 条记录. 
在那个时间段内的就不会很多 ? 不会很多，那就算10 条？！ 
大部分都是first = 0， 99%的记录都是 first=0 ? 
support > 10 一般不会很多, 不会很多是多少 10M 记录中一…

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

#22

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,000

tid , 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)

#23

引用 22 楼 ACMAIN_CHM 的回复:

每种分类只要出现一条记录就可以了? 随机取一条？还是取其中 support 最大的一条？ 

粗步仅以上述信息来看，建议索引 (support,dateline) ,假设suppport 为integer型，估计取值范围 0-40, 这样通过索引可以将数据减少到 10%*50,000 = 5000 条 
或者(tid,support,dateline) 

如果效率允许的话，那当然最好取support最大的，

后面那个方法我没怎么看懂，你的意思就是说添加(support,dateline)这两列的索引吗？或者再加上(tid),
对于这句查询语句是不是没办法再改了，只能通过索引来优化了是吧。

对了，还有我试了一下，要是把group by 去掉，那查询的记录数只要7W多条就可以了，加上分组后就变成了整表查询了，这个跟tid有没有建索引有关系吗？因为我现在不方便修改数据结构，所以想尽量通过修改查询语句来实现，实在不行，那我想就只能先不分组的查询，然后在程序里实现分组筛选工作，麻烦也只能麻烦一点了。

#24

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

#25

是啊，所以相当麻烦，有办法吗？没有的话，那我就结贴给分了，真的很感谢你们的帮忙，我学了很多。

#26

办法是有，比如你可以

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

先试一下效果如何。

优化要很有耐心，在多种方案中进行选择。　你可以先通过子查询来人为的确定筛选的先后顺序。没有你具体的数据，很难进行分析。

#27

哦，是这样啊，
确实啊，我的优化知道太差了，
我先试一下，不过，这种方法我学习了，谢谢！

#28

引用 26 楼 ACMAIN_CHM 的回复:

SQL code
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

我试了一下，这句好象效果还是差不多，那接下来是不是应该改成这样：
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
然后再把别的条件放到子查询中，再看一下，哪个的效果最好是不是?
原理是不是应该把记录数最少的条件当做子查询？这样查询的效率才最高？

#29

其它几个条件，对查询没什么大的影响，应用无法利用上索引。

#30

引用 29 楼 ACMAIN_CHM 的回复:

其它几个条件，对查询没什么大的影响，应用无法利用上索引。

这里说的无法利用上索引是针对那句子查询说的吗？
据我理解，子查询的效率不高，所以平时写查询时尽量不要用子查询，
不过，像这样记录比较大的话，那就要看一下，是子查询耗时间还是多搜索一些记录耗时间多对吧？
而像现在的情况，两个效果都不好的话，还有别的办法吗？

#31

优化要很有 耐心，在多种方案中进行选择。

#32

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

#33

引用 31 楼 qizhicong2 的回复:

优化要很有 耐心

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

，在多种方案中进行选择。

#34

用的是什么语言阿

#35

学习。