代码之家 › 专栏 › 技术社区 › Gajendra Bang

MySQL查询优化

optimization mysql

Gajendra Bang · 技术社区 · 14 年前

SELECT
    b.categoryid,
    SUM(viewcount) AS cnt,
    categoryname
FROM
    bookvisit AS bv
    INNER JOIN book AS b ON b.isbn = bv.isbn
    LEFT JOIN category AS c ON b.categoryid = c.categoryid
WHERE
    b.categoryid IS NOT NULL AND
    b.categoryid <> 0 
GROUP BY
    b.categoryid 
ORDER BY
    cnt DESC,
    bv.isbn
LIMIT 0, 4

我有三张桌子- 书籍(包含书籍信息) 预约(预约信息) 类别(类别主控形状)

我需要的是流行的类别,上面的查询可以使用两个eq-ref,但它有一个使用临时;也使用文件排序

有什么帮助吗?

2 回复 | 直到 14 年前

Quassnoi 14 年前

AS @ JJY分段故障 也就是说,在 MySQL 您将不得不编写一个服务来缓存shapshot表中的总和(您可以对其进行索引)。

以下是您如何做到这一点,并且仍然能够获得准确的统计数据:

创建快照表:
```
category  cnt
```
用一个 PRIMARY KEY 在 category .
创建一个名为 snapshot_time :
```
taken
```

及时用查询填写此表:

UPDATE  snapshot_time
SET     taken = NOW()

INSERT  
INTO    snapshot
SELECT  b.category, COUNT(*) AS new_cnt,
        (
        SELECT  taken
        FROM    snapshot_time
        ) AS new_taken
FROM    bookvisit bv
JOIN    book b
ON      b.isbn = bv.isbn
WHERE   bv.visit_time <=
        (
        SELECT  taken
        FROM    snapshot_time
        )
ON DUPLICATE KEY UPDATE
SET     cnt = new_cnt,
        snapshot_taken = new_taken

创建以下索引:

snapshot (cnt)
bookvisit (visit_time)
book (category)

运行此查询:

SELECT  category,
        cnt +
        (
        SELECT  COUNT(*)
        FROM    bookvisit bv
        JOIN    book b
        ON      b.isbn = bv.isbn
        WHERE   bv.visit_time > 
                (
                SELECT  taken
                FROM    shapshot_time
                )
                AND b.category = s.category
        ) AS total
FROM    snapshot
WHERE   cnt >=
        (
        SELECT  cnt
        FROM    snapshot
        ORDER BY
                cnt DESC
        LIMIT 4
        )
        -
        (
        SELECT  COUNT(*)
        FROM    bookvisit
        WHERE   bv.visit_time > 
                (
                SELECT  taken
                FROM    shapshot_time
                )
        )
ORDER BY
        total DESC
LIMIT 4

查询将返回准确的访问计数。

主要的想法是您只需要扫描 bookvisit 在缓存统计信息后收集的。

更重要的是:您甚至不必扫描缓存统计信息中的所有记录。由于访问次数只会增加,所以只能扫描可能进入前四个的结果。

如果 4th 记录已 1,000,000 快照中的页面视图,以及 1,000 获取快照后发生的页面视图,只能从快照中选择记录 cnt >= 999,000 . 其他记录理论上不能达到这个极限,因为它需要 1K 页面视图。

唯一的问题是,您可以删除书籍或更改它们的类别。在这种情况下,您只需要重新计算统计数据或返回到原始方法。

dj_segfault 14 年前

根据您昨天的评论,我认为问题在于您正在对计算(聚合)列进行排序,因此它不能使用索引。

唯一的方法是添加一个category.viewCount列,在更新book.viewCount时更新该列。它不会占用太多的额外空间。这种解决方案的缺点是:

当一本书的类别发生变化时(我认为这很少见),您需要从旧类别中减去book.viewCount并将其添加到新类别中。
类别表上的锁争用可能是性能问题

如果这是我,并且性能是必需的,那么我将编写一个单独的服务,在启动时将当前的统计信息读取到内存中,并让系统向它发送更新,然后查询它以获取最新的统计信息。这样可以保存锁定和写入类别表。

当然,如果数字不一定是100%最新的,那么您可以在晚上运行查询并缓存当天的结果。