代码之家 › 专栏 › 技术社区 › Art Peterson

mysql优化-显示10条最新记录,同时标识重复行

sql-order-by group-by optimization mysql

Art Peterson · 技术社区 · 15 年前

我是MySQL的新手,我已经为这个问题纠结了好几天了。我需要改进/优化这个查询,使它运行得更快-现在它需要5秒多的时间。

以下是查询:

SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.*  
FROM announcements as a  
INNER JOIN stores as s  
ON a.username=s.username
    WHERE s.username is not null AND s.state='NC' 
GROUP BY a.announcement_id
ORDER BY a.dt DESC LIMIT 0,10

商店表包括:商店ID、用户名、名称、州、市、邮编等…

公告表包括:announcement_id、msg、dt、username

stores表有大约10000条记录,announcements表有大约500000条记录。

我试图用英语完成的是——显示10个最近的商店公告,但让这变得复杂的是,商店可以在stores表中有多个具有相同用户id的条目(每个位置一行)。所以,如果一家连锁店,比如说“Chipotle”发送了一个公告,我只想在他们的公告中显示一行,旁边写着“这家店有多个位置”。这就是为什么我要使用count(*)和group by,所以如果 count(*) > 1 我知道有很多地方可以发布公告。

where条件可以是任何州、市或邮政编码。使用sql_no__缓存是因为公告经常更新,所以很少得到相同的结果,这有意义吗?

我真的很感激你能给我一些建议,让我把事情做得更好。我对索引知之甚少,但我确实为两个表中的“username”字段创建了索引。你可以在这里把我撕成碎片,我知道我一定错过了什么。

更新——

DESC商店;

Field       Type            Null    Key     Default         Extra  
store_id    int(11)         NO      PRI     NULL            auto_increment  
username    varchar(20)     NO      MUL     NULL       
name        varchar(100)    NO              NULL       
street      varchar(100)    NO              NULL       
city        varchar(50)     NO              NULL       
state       varchar(2)      NO              NULL       
zip         varchar(15)     NO              NULL

说明公告;

Field              Type           Null      Key     Default     Extra
dt                 datetime       NO                NULL     
username           varchar(20)    NO        MUL     NULL     
msg                varchar(200)   NO                NULL     
announcement_id    int(11)        NO        PRI     NULL        auto_increment

解释输出;

id  select_type     table   type    possible_keys   key       key_len     ref         rows     Extra
1   SIMPLE          a       index   username        PRIMARY   47          NULL        315001   Using temporary; Using filesort
1   SIMPLE          b       ref     username        username  62          a.username  1        Using where

3 回复 | 直到 15 年前

John M 15 年前

试试这样的:

SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.*   
FROM announcements as a   
INNER JOIN 
(
  SELECT username, COUNT(username) as multiple FROM stores
  WHERE username IS NOT NULL AND state = 'NC'
  GROUP BY username
 )  as s 
ON a.username=s.username 
ORDER BY a.dt DESC LIMIT 10

Ian Clelland 15 年前

如果您在dt列上进行排序,但该列上没有索引,那么每次运行查询时,mysql都必须对该列上的所有结果行进行排序(缓慢、昂贵)

尝试在announcements.dt上添加一个索引——MySQL可以使用该索引按顺序访问行,并避免随后的排序步骤。

grateful.dev 15 年前

更改联接中表的顺序,mysql从第一个表中读取行,然后在第二个表中查找匹配的行。如果总是按stores表中的字段筛选结果,那么stores表应该是联接中的前导表,这样它就不会从announcements表中挑选和排序不必要的行。
在您粘贴的explain输出中,似乎只有一个商店与查询匹配,切换表的顺序将导致它只在announcements表中查找特定的商店。
在dt列上添加索引(使用unixtime的索引整数列更好)
如果可能的话-为每个用户名创建一个整数userid并使用该列加入(在该列上也添加一个on索引)
不确定mysql是否还有问题,但是用count(1)替换count(*)可能会有帮助。