mysql 查询很慢，怎么回事。

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已经建立索引。

问题是：
查询异常的慢，而且会影响到其他操作数据库的功能。

20 个解决方案

#1

SELECT title.id,title.title,title.hit,title.rep,title.redate FROM bbsmenu menu,bbstitle title WHERE title.user_id=? GROUP BY title.id LIMIT 0,15
刚才测试了一个这个语句，还是发现很慢。之后，就把GROUP BY title.id 去掉，发现很快就查询出来。查询在0.0N秒。但是，如果不加GROUP BY title.id，那么就有很多重复的数据出来。
如果仅仅是查询bbstitle，那就不需要GROUP BY title.id啦。可是，还需要查询这个表bbsmenu。
有考虑过把两张表拆分来查询，但是，还有一点，在查询两张表之后，结果集需要按时间排序。想来想去，
好像还是要添加GROUP BY title.id。

#2

必要的索引没有创建。

另外这个逻辑有些看不明白 title.user_id=? OR (menu.user_id=? AND menu.pid=title.id)， 你想实现的功能是什么？你不会是在MYSQL中运行的这个语句吧  title.user_id= ? OR 应该会报错，这里应该是实际值。

贴出你的 explain 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

show index from bbsmenu ;
show index from bbstitle;

#3

引用 2 楼 acmain_chm 的回复:

必要的索引没有创建。

另外这个逻辑有些看不明白 title.user_id=? OR (menu.user_id=? AND menu.pid=title.id)， 你想实现的功能是什么？你不会是在MYSQL中运行的这个语句吧 title.user_id=? OR 应该会报错，这里应该是实际值。

贴出你的 explain SELECT title.id,title.title,titl……

带问号,可以给它一个值就行了.
我想实现的功能是,某用户发过的帖子或者参与过的帖子全部列出来.
哪个字段是必要的索引呢?

#4

explain 和 SHOW INDEX的结果贴出来以供分析。

#5

引用 2 楼 acmain_chm 的回复:

必要的索引没有创建。

另外这个逻辑有些看不明白 title.user_id=? OR (menu.user_id=? AND menu.pid=title.id)， 你想实现的功能是什么？你不会是在MYSQL中运行的这个语句吧 title.user_id=? OR 应该会报错，这里应该是实际值。

贴出你的 explain SELECT title.id,title.title,titl……

补充:

#6

我晕,图片显示不了.连接地址是正常的。怎么回事?

#7

看不到图，建议你在MYSQL的命令行工具中执行，然后直接把结果以文本的方式复制粘贴到论坛。

#8

文本的形式很难看.我这边多刷新几次就行了.或者是你查看图片连接地址都可以.
文本形式如下:
    

    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              

#9

引用 7 楼 acmain_chm 的回复:

看不到图，建议你在MYSQL的命令行工具中执行，然后直接把结果以文本的方式复制粘贴到论坛。

文本形式如下:

    id  select_type  table   type    possible_keys    key     key_len  ref       rows  Extra                          
------  -----------  ------  ------  ---------------  ------  -------  ------  ------  -------------------------------
     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              

#10

刚才去了解了一下explain指令.
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             

#11

你看执行计划是按照你想的那样从索引找数据更新吗

#12

引用 11 楼 rucypli 的回复:

你看执行计划是按照你想的那样从索引找数据更新吗

是的.

#13

group by 慢 title.id是主键吗？是的话如果表是innodb的话不需要排序的，innodb按主键聚集

#14

select * from (
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

#15

引用 14 楼 zhangminysu 的回复:

select * from (
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……

这个还是不行.

#16

首先在bbstitle，诸如titleType, deleted这种大量重复性的字段根本没必要创建索引，即使建了索引，mysql也会忽略，万一真的采用了这些索引，效率会变得很差。
其次从你贴出的explain分析中，可以看出两个表都是全表扫描，这查询速度自然不快。另外虽然你的表中创建了很多索引，但是从key这一列为NUll，mysql并没有采用哪一个索引。请检查索引的有效性，索引的字段是否有大量的重复性数据。通常情况下，字段的唯一性越高，索引越有效
正经的全表扫描。

#17

通配付应该用 like关键字，而不是用=来进行条件判断

#18

弱弱说一句，你要去除重复数据，可否用distinct,然后再加个order by

#19

SELECT c.*,d.CompanyName,d.CustomerID,d.Telephone,d.PayeeName,d.PayeeBankCard,d.PayeeIDNumer,
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)
       )

#20

上面这个语句谁看看能怎么优化呢，实在是太慢了

#21

• Retrofit 2.0 超能实践，完美支持Https传输