关于PHP+MYsql大量数据处理的问题

[number123456]

我现在有个数据库，里边主要有两个表，class,news
现在news表有2.3G，35W多条记录，我的查询大部分是以下形式，
select a.*,b.* from news as a inner join class as b
on a.classid=b.classid
期中news 表里还有个 node的字段，也要加进select里，也就是完整的是
select a.*,b.* from news as a inner join class as b
on a.classid=b.classid where classid=2 or node like '|2|%'

一个页面中至少有4个这样的查询语句，现在基本上打不开页面了，我在mysql里直接查询也要4分多钟，请问有什么办法能优化一下呢，可以从数据库或程序中都行。但是一个问题就是不能分表了，news 表己经不能变。

补充一下，news 表只设置了主键ID

[longjianghu]

用left join

[foolbirdflyfirst]

建一个联合索引(classid,node)试试

[number123456]

我建了一个联合索引，index news_classid on (classid,node,istop,isdel),可是基本上没有改善

[yybjroam05]

a.*,b.* 这个地方可以优化一下！用不到的就不要选

[foolbirdflyfirst]

可以试下将or node like '|2|%'这个条件拆分成一个语句试下
意思像这样

select a.*,c.* from news a 
left join (
  select * from news where node like '|2|%'
) b on a.class_id=b.class_id
left join class c on a.class_id=c.class_id 
where a.class_id=2 && a.isdel=0

[foolbirdflyfirst]

如果是where （classid=2 or node like '|2|%'） and isdel=0这个条件
sql应该类似这样

select a.*,c.* from (
  select * from news where node like '|2|%'
) a
left join news b on b.isdel_id=a.isdel_id
inner join class c on c.class_id=a.class_id 
where a.class_id=2 && a.isdel=0

[number123456]

我试了半天了，现在的情况是
原来的SQL=
select a.*,c.* from news a
inner join class c on a.classid=c.id
where a.isdel=0 and (a.classid=2 or a.node like '|2|%')
order by a.id desc
现将SQL改成如下
select ……
where a.isdel=0 and (a.classid=2)要快很多，因为不用like 了，但没办法实现需要的功能，所以不能去掉
select ……
where a.isdel=0 and a.node like '|2|%' 基本上和原来一样，没变化
索引，我做过己下改动
原始没有索引
第一次 news_index1 on news (isdel,classid,node)
第二次 news_index2 on news (classid,node)
均没有什么效果，
请大家帮帮我，看看都有什么方法能改善目前情况，

我总决得MYSQL处理30W多数据不应该连页面都打不开啊

[number123456]

嗯，明白了，索引这部分经过N次的尝试，基本上确定了哪些需要优化了，剩下的我会在PHP中再处理一下，谢谢以上所有帮助的人！！

[number123456]

有没有谁能帮我一下，实在急需，解决后另开贴曾分

[xuzuning]

1、like 是不是用索引的
2、索引对TEXT和BLOB类型无效

[cayleung]

35w的数据量不大

1、将表引擎换成InnoDB，node,classid加联合索引；
2、改MySql设置，innodb_buffer_pool_size改大一点；
3、那个like可以改用SUBSTRING切割然后再比较；

[number123456]

经过我坚持不懈的改进测试，
一、我设置了node,classid索引（node 因为是后边加通配符，可以使用索引，而且，我把node,classid加联合索引后，无效，所以我分开加的）经过测试，可以提高一半的速度，至少列表能打开了。
二、我创建了视图，基本上和原来的速度是一样（这个地方不了解视图能不能加索引，加索引有效果吗？）

[foolbirdflyfirst]

建议你多多分析explain信息，优化sql语句的过程就是分析explain信息的过程,因为项目是你自己的项目，业务只有你自己最清楚。
关于explain各列信息的详述，网上很多，可以多参考参考。
重点注意type列ref列和extra列.

[number123456]

楼上，
我有两个SQL
select * from news where node like '|2|%' order by id desc
和
select * from news where node like '|3|%' order by id desc
为什么两者相差20多秒，而且第二条有时候还打不开，
从explain来看，第一条涉及到2 W 8的数据，第二条才2 W，这是为什么呢？

[foolbirdflyfirst]

别老看rows列呀，其余列的信息呢？

[number123456]

这是我得到的，我发现一个问题
如果我加上排序，order by id desc就会在extra中出现Using filesort,如果不加就没有，这是为什么？

[foolbirdflyfirst]

extra列一旦出现using filesort，sql肯定要进行优化了，filesort简单来说就是遍历整表数据，使用快速排序(quicksort)算法对整列数据进行排序。而sql排序最优应该是按索引排序
拿这条语句来说
select * from news where node like '|3|%' order by id desc
虽然where后面用到了索引，但是此索引无法解决语句中的order by id,只好scan整表,所以是filesort,这个排序你是否可以考虑在业务端做呢？

mysql手册说得很清楚，在优化order by那一章
=======================================================
在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括：

· 对不同的关键字使用ORDER BY：

· SELECT * FROM t1 ORDER BY key1, key2；

· 对关键字的非连续元素使用ORDER BY：

· SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；

· 混合ASC和DESC：

· SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

· 用于查询行的关键字与ORDER BY中所使用的不相同：

· SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

[number123456]

那 foolbirdflyfirst 能告诉我一下以下情况，我应该怎么办吗？

select * from news where node like '|3|%' order by id desc
中的ID是为了显示最新的，但是还有按更新日期等其它排序的可能情，也就是说可能会出现
readtime,update,create这些情况，像这么复杂的排序的话，有什么办法能优化一下吗？因为业务需要，所以才会出现这么多排序

[foolbirdflyfirst]

这个还真不好说，要看你数据的索取量。
考虑你的业务吧，比如如果说like '|3|%' 这样的数据经常要提取,可以把建立个视图。
create view abc as select * from news where node like '|3|%'，然后order by什么什么的基本基于这个abc视图。

[skysbird]

好好学习学习