我现在有个数据库,里边主要有两个表,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
现在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
2 limit子句
3 inner join换left join
4 利用Explain获取的信息来实现mysql优化
1.建立索引,比如你的classid。
2.如果你的表类型是innodb那么请改成MyISAM
3.这样大的数据查询还是用存储过程吧,第一次有点慢,但以后会快很多的
4.重新架构数据库
top值是0-6,isdel值是0 or 1
我是不是也建立索引会好一点啊?
还有,我用的是MYSQL5
where classid=2 or node like '|2|%' classid是哪个表的?node又是哪个表的?这两个字段分别设置了索引了吗?没有就应该都设置为索引,因为条件连接用的是OR
应该以class为left join的左表与news外联.
id 1 1
select_type simple simple
table news class
type index eq_ref
possible_keys null primary
key primary primary
key_len 4 4
ref null qmw
rows 322904 1
extra Using where Using where请两位帮我解释一下各项数据可以吗?
意思像这样
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
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
原来的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多数据不应该连页面都打不开啊
2、索引对TEXT和BLOB类型无效
2、改MySql设置,innodb_buffer_pool_size改大一点;
3、那个like可以改用SUBSTRING切割然后再比较;
一、我设置了node,classid索引(node 因为是后边加通配符,可以使用索引,而且,我把node,classid加联合索引后,无效,所以我分开加的)经过测试,可以提高一半的速度,至少列表能打开了。
二、我创建了视图,基本上和原来的速度是一样(这个地方不了解视图能不能加索引,加索引有效果吗?)
关于explain各列信息的详述,网上很多,可以多参考参考。
重点注意type列ref列和extra列.
我有两个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,这是为什么呢?
这是我得到的,我发现一个问题
如果我加上排序,order by id desc就会在extra中出现Using filesort,如果不加就没有,这是为什么?
拿这条语句来说
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;
中的ID是为了显示最新的,但是还有按更新日期等其它排序的可能情,也就是说可能会出现
readtime,update,create这些情况,像这么复杂的排序的话,有什么办法能优化一下吗?因为业务需要,所以才会出现这么多排序
考虑你的业务吧,比如如果说like '|3|%' 这样的数据经常要提取,可以把建立个视图。
create view abc as select * from news where node like '|3|%',然后order by什么什么的基本基于这个abc视图。