MySQL 大量刪除資料的問題

Posted on June 9, 2009 by

    url_detail_history 資料表
    +--------------+---------------------+------+
    | Field        | Type                | Null |
    +--------------+---------------------+------+
    | id           | bigint(11) unsigned | NO   |  -> 有 Auto_increment
    | url_id       | int(11)             | NO   |
    | visitor_ip   | varchar(15)         | NO   |
    | visitor_fqdn | varchar(100)        | NO   |
    | visitor_time | timestamp           | NO   |  -> CURRENT_TIMESTAMP
    +--------------+---------------------+------+

現在裡面有許多筆資料，可以用

SELECT COUNT(id), url_id FROM url_detail_history GROUP BY url_id

1	SELECT COUNT(id), url_id FROM url_detail_history GROUP BY url_id

得知每個 url_id 有幾筆資料。

我前幾天寫錯了一個地方，導致資料表裡面長了大概一百萬筆資料。由於我 SQL 很弱，所以想知道有沒有辦法直接用 SQL 語法執行刪除，條件是：每個 url_id 只要總數超過 N 筆資料就刪除到只剩下 N 筆為止，當然， id 越大代表資料越新，必須能從小的 id 開始刪起。

我有寫一段程式來跑但是效能不彰，系統 loading 飆到 100 多，拉牛上樹慘不忍睹..(以下是虛擬碼)


/* 先取出符合條件的資料 */

SELECT count(id), url_id FROM url_detail_history GROUP BY url_id HAVING count(id) > 1500

用此法個別取出 $url_id ，然後

foreach( $url_id ) {
     do {
            $id= SELECT min(id) FROM table WHERE url_id = $url_id
            DELETE FROM table WHERE id = $id
            $count = "SELECT COUNT(id), url_id FROM url_detail_history WHERE url_id = $url_id
        } while( $count < 1501)
}

/* 先取出符合條件的資料 */

SELECT count(id), url_id FROM url_detail_history GROUP BY url_id HAVING count(id) > 1500

用此法個別取出 $url_id ，然後

foreach( $url_id ) {

do {

$id= SELECT min(id) FROM table WHERE url_id = $url_id

DELETE FROM table WHERE id = $id

$count = "SELECT COUNT(id), url_id FROM url_detail_history WHERE url_id = $url_id

} while( $count < 1501)

}

這樣作法效能很差，不想這麼暴力解這個問題… 😥

Posted in DB

2 comments on “MySQL 大量刪除資料的問題”

kong0107 says:

January 19, 2010 at 19:55

在下想到一法能用一個SQL指令做到您的需求：
DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id 1500

tbl就是您的url_detail_history
此法受限到Multiple-table DELETE的支援度
詳細說明我晚點會發在我的blog上
以上，獻醜了。

Reply
kong0107 says:

January 19, 2010 at 21:09

啊，大於和小於的顯示好像有狀況….
DELETE FROM tbl USING tbl INNER JOIN ( SELECT id, count(*) AS place FROM ( SELECT t1.id FROM tbl AS t1, tbl AS t2 WHERE t1.url_id = t2.url_id AND t1.id <= t2.id ) AS t3 GROUP BY id ) AS t4 WHERE tbl.id = t4.id AND t4.place > 1500

Reply