Mysql查詢優化之利用中間表方法優化count()統計大數據量總數問題

在上一篇博文我們提到,分頁有三種方法。其中,第三種是我們最常用的。然而,在實際應用過程中我們會發現,select count(*) from tname 語句在統計某表內記錄總數時,如果表內數據量達到一定規模(比如100W條),這個語句就會執行得非常慢。有什麼辦法可以加快統計出表內記錄總數呢?

這裡,我們需要借助一個中間表來記錄資料庫內各表記錄總數。然後,在我們需要知道某表的記錄總數來計算分頁數時直接查詢中間表獲取目的表的記錄總數即可。無需把目的表全部查詢一次然後逐一統計。

這裡有人要問瞭,這個中間表哪兒來的呢?嘿嘿,無需再用一個文件來定期更新!資料庫已經為我們提供瞭一個很好的工具啦!那就是——觸發器。

觸發器是一種特殊的存儲過程。一般的存儲過程是通過存儲過程名直接調用,而觸發器主要是通過事件(增、刪、改)進行觸發而被執行的。其在表中數據發生變化時自動強制執行。所以,我們隻需要為每個需要監聽的表創建一個觸發器,使得該表有增、刪操作時,自動對rowsCount中間表裡相應的記錄進行修改,即可同步更新中間表對各表的記錄。

這裡大致講一下觸發器的創建:我用的資料庫桌面工具是SQLyog。

在需要監聽的表上右鍵,選擇“創建觸發器”,工具會自動生成一些通用的代碼如下:

    CREATE
    TRIGGER `資料庫名`.`觸發器名` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `資料庫名`.`<Table Name>`
    FOR EACH ROW

    BEGIN

    事件發生後執行的代碼
    END

監聽器有兩種:事前執行與事後執行。分別對應上面的BEFORE/AFTER.

事件類型有三種:插入、修改、刪除

監聽對象為:資料庫名.表名

事件發生後代碼:觸發器的主體部分。用於響應監聽對象發生所監聽的事件前/後所執行的sql操作。比如:修改某中間表中的數據來記錄監聽表的變化。

實例:為admin表創建監聽器,在admin表有數據插入後,激活觸發器執行,更新pagecount中間表中,tablename為admin的那條記錄的total屬性,因為插入瞭一條記錄,所以total+1.

    CREATE
    TRIGGER `counter` AFTER INSERT ON `admin` 
    FOR EACH ROW    
    BEGIN
    UPDATE pagecount SET total=total+1 WHERE tablename = 'admin';
    END;

You May Also Like