SELECT title.id,title.title,title.hit,title.rep,title.redate FROM bbsmenu menu,bbstitle title WHERE title.user_id=? OR (menu.user_id=? AND menu.pid=title.id) GROUP BY title.id LIMIT 0,15关于两张表的说明
bbstitle 表有38万的数据,字段id、user_id已经建立索引。
bbsmenu 表有几百条数据。字段user_id已经建立索引。问题是:
查询异常的慢,而且会影响到其他操作数据库的功能。
bbstitle 表有38万的数据,字段id、user_id已经建立索引。
bbsmenu 表有几百条数据。字段user_id已经建立索引。问题是:
查询异常的慢,而且会影响到其他操作数据库的功能。
刚才测试了一个这个语句,还是发现很慢。之后,就把GROUP BY title.id 去掉,发现很快就查询出来。查询在0.0N秒。但是,如果不加GROUP BY title.id,那么就有很多重复的数据出来。
如果仅仅是查询bbstitle,那就不需要GROUP BY title.id啦。可是,还需要查询这个表bbsmenu。
有考虑过把两张表拆分来查询,但是,还有一点,在查询两张表之后,结果集需要按时间排序。想来想去,
好像还是要添加GROUP BY title.id。
show index from bbstitle;
文本形式如下:
id select_type table type possible_keys key key_len ref rows Extra
------ ----------- ------ ------ --------------- ------ ------- ------ ------ -------------------------------
1 SIMPLE title ALL PRIMARY,user_id (NULL) (NULL) (NULL) 39317 Using temporary; Using filesort
1 SIMPLE menu ALL user_id,pid (NULL) (NULL) (NULL) 36 Using where; Using join buffer
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
bbsmenu 1 user_id 1 user_id A (NULL) (NULL) (NULL) YES BTREE
bbsmenu 1 pid 1 pid A (NULL) (NULL) (NULL) YES BTREE
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-------- ---------- --------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
bbstitle 0 PRIMARY 1 id A 47539 (NULL) (NULL) BTREE
bbstitle 1 BoardID_2 1 BoardID_2 A (NULL) 333 (NULL) YES BTREE
bbstitle 1 titleType 1 titleType A (NULL) (NULL) (NULL) YES BTREE
bbstitle 1 deleted 1 deleted A (NULL) (NULL) (NULL) YES BTREE
bbstitle 1 BoardID_1 1 BoardID_1 A (NULL) 333 (NULL) YES BTREE
bbstitle 1 user_id 1 user_id A (NULL) 333 (NULL) YES BTREE
------ ----------- ------ ------ --------------- ------ ------- ------ ------ -------------------------------
1 SIMPLE title ALL PRIMARY,user_id (NULL) (NULL) (NULL) 62589 Using temporary; Using filesort
1 SIMPLE menu ALL user_id,pid (NULL) (NULL) (NULL) 36 Using where; Using join buffer Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
bbsmenu 1 user_id 1 user_id A (NULL) (NULL) (NULL) YES BTREE
bbsmenu 1 pid 1 pid A (NULL) (NULL) (NULL) YES BTREE Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
-------- ---------- --------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
bbstitle 0 PRIMARY 1 id A 68459 (NULL) (NULL) BTREE
bbstitle 1 BoardID_2 1 BoardID_2 A (NULL) 333 (NULL) YES BTREE
bbstitle 1 titleType 1 titleType A (NULL) (NULL) (NULL) YES BTREE
bbstitle 1 deleted 1 deleted A (NULL) (NULL) (NULL) YES BTREE
bbstitle 1 BoardID_1 1 BoardID_1 A (NULL) 333 (NULL) YES BTREE
bbstitle 1 user_id 1 user_id A (NULL) 333 (NULL) YES BTREE
type字段如果是index,那么这个语句应该是需要优化.
我想问一下,如果是联合查询,一个是req或以上,一个是index或ALL,那么,这个语句是否需要优化
如:
EXPLAIN EXTENDED SELECT title.id,title.title,title.hit,title.rep,title.redate FROM bbstitle title,userpost upost WHERE upost.id=title.id
id select_type table type possible_keys key key_len ref rows filtered Extra
------ ----------- ------ ------ ------------- ------- ------- ------------- ------ -------- -----------
1 SIMPLE upost index id id 5 (NULL) 10 100.00 Using index
1 SIMPLE title eq_ref PRIMARY PRIMARY 4 agri.upost.id 1 100.00
SELECT title.id,title.title,title.hit,title.rep,title.redate FROM bbstitle title WHERE title.user_id=? LIMIT 0,15
union
SELECT title.id,title.title,title.hit,title.rep,title.redate FROM bbsmenu menu,bbstitle title WHERE
(menu.user_id=? AND menu.pid=title.id) LIMIT 0,15
) t2 LIMIT 0,15
其次从你贴出的explain分析中,可以看出两个表都是全表扫描,这查询速度自然不快。另外虽然你的表中创建了很多索引,但是从key这一列为NUll,mysql并没有采用哪一个索引。请检查索引的有效性,索引的字段是否有大量的重复性数据。通常情况下,字段的唯一性越高,索引越有效
正经的全表扫描。
e.CategoryCode as MCC,e.CustomerRate,e.CustomerLimitMoney,e.BankRate,e.BankLimitMoney,
if(e.BankLimitMoney is null ,(DataItem11*e.BankRate/100),if(e.BankLimitMoney=0,(DataItem11*e.BankRate/100),if((DataItem11*e.BankRate/100)<e.BankLimitMoney,(DataItem11*e.BankRate/100),e.BankLimitMoney))) as BankFee,
if(e.CustomerLimitMoney is null ,(DataItem11*e.CustomerRate/100),if(e.CustomerLimitMoney=0,(DataItem11*e.CustomerRate/100),if((DataItem11*e.CustomerRate/100)<e.CustomerLimitMoney,(DataItem11*e.CustomerRate/100),e.CustomerLimitMoney))) as CustomerFee
from dbl_datafromunionpay c left join view_poscodeinfo_customer d on c.DataItem04=d.POSCode
, dbl_customercategorycode e where e.categorycode=SubSTR(c.DataItem02,8,4)
and (
( e.ValidStartDate<=concat(substr(c.DataItem01,1,4),'-',substr(c.DataItem01,5,2),'-',substr(c.DataItem01,7,2))
and e.ValidEndDate>=concat(substr(c.DataItem01,1,4),'-',substr(c.DataItem01,5,2),'-',substr(c.DataItem01,7,2))
and e.ValidEndDate is not null) OR ( e.ValidStartDate<=concat(substr(c.DataItem01,1,4),'-',substr(c.DataItem01,5,2),'-',substr(c.DataItem01,7,2))
and e.ValidEndDate is null)
)