快捷導(dǎo)航

mysql 超大數(shù)據(jù)/表管理技巧

更新時間：2013年03月18日 00:17:02 作者：

在實際應(yīng)用中經(jīng)過存儲、優(yōu)化可以做到在超過9千萬數(shù)據(jù)中的查詢響應(yīng)速度控制在1到20毫秒?？瓷先ナ莻€不錯的成績，不過優(yōu)化這條路沒有終點，當(dāng)我們的系統(tǒng)有超過幾百人、上千人同時使用時，仍然會顯的力不從心

如果你對長篇大論沒有興趣，也可以直接看看結(jié)果，或許你對結(jié)果感興趣。在實際應(yīng)用中經(jīng)過存儲、優(yōu)化可以做到在超過9千萬數(shù)據(jù)中的查詢響應(yīng)速度控制在1到20毫秒?？瓷先ナ莻€不錯的成績，不過優(yōu)化這條路沒有終點，當(dāng)我們的系統(tǒng)有超過幾百人、上千人同時使用時，仍然會顯的力不從心。

    分區(qū)存儲
    優(yōu)化查詢
    改進分區(qū)
    模糊搜索
    持續(xù)改進的方案

正文：

分區(qū)存儲
對于超大的數(shù)據(jù)來說，分區(qū)存儲是一個不錯的選擇，或者說這是一個必選項。對于本例來說，數(shù)據(jù)記錄來源不同，首先可以根據(jù)來源來劃分這些數(shù)據(jù)。但是僅僅這樣還不夠，因為每個來源的分區(qū)的數(shù)據(jù)都可能超過千萬。這對數(shù)據(jù)的存儲和查詢還是太大了。MySQL5.x以后已經(jīng)比較好的支持了數(shù)據(jù)分區(qū)以及子分區(qū)。因此數(shù)據(jù)就采用分區(qū)+子分區(qū)來存儲。

下面是基本的數(shù)據(jù)結(jié)構(gòu)定義：

復(fù)制代碼代碼如下:

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(32) DEFAULT NULL,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) DEFAULT NULL,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′,
        PRIMARY KEY (`id`,`src`)
        ) ENGINE=MyISAM AUTO_INCREMENT=95660181 DEFAULT CHARSET=gbk
        /*!50500 PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY HASH (id)
        SUBPARTITIONS 5
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM) */

    對于擁有分區(qū)及子分區(qū)的數(shù)據(jù)表，分區(qū)條件（包括子分區(qū)條件）中使用的數(shù)據(jù)列，都應(yīng)該定義在primary key 或者 unique key中。詳細的分區(qū)定義格式，可以參考MySQL的文檔。上面的結(jié)構(gòu)是第一稿的存儲方式（后文還將進行修改）。采用load data infile的方式加載，用時30分鐘加載8千萬記錄。感覺還是挺快的（bulk_insert_buffer_size=8m）。
    基本查詢優(yōu)化
    數(shù)據(jù)裝載完畢后，我們測試了一個查詢：

復(fù)制代碼代碼如下:

        mysql> explain select * from tmp_sampledata where id=9562468\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ref
        possible_keys: PRIMARY
        key: PRIMARY
        key_len: 8
        ref: const
        rows: 8
        Extra:
        1 row in set (0.00 sec)

這是毋庸置疑的，通過id進行查詢是使用了主鍵，查詢速度會很快。但是這樣的做法幾乎沒有意義。因為對于終端用戶來說，不可能知曉任何的資料的id的。假如需要按照username來進行查詢的話：

復(fù)制代碼代碼如下:

        mysql> explain select * from tmp_sampledata where username = ‘yourusername'\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 74352359
        Extra: Using where
        1 row in set (0.00 sec)

        mysql> explain select * from tmp_sampledata where src between 1 and 7 and username = ‘yourusername'\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata
        type: ALL
        possible_keys: NULL
        key: NULL
        key_len: NULL
        ref: NULL
        rows: 74352359
        Extra: Using where
        1 row in set (0.00 sec)

那這個查詢就沒法用了。根本就沒人能等待一個上億表的全表搜索！這是我們就考慮是否給username創(chuàng)建一個索引，這樣肯定會提高查詢速度：

create index idx_username on tmp_sampledata(username);

這個創(chuàng)建索引的時間很久，似乎超過了數(shù)據(jù)裝載時間，不過好歹建好了。

復(fù)制代碼代碼如下:

        mysql> explain select * from tmp_sampledata2 where username = ‘yourusername'\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tmp_sampledata2
        type: ref
        possible_keys: idx_username
        key: idx_username
        key_len: 66
        ref: const
        rows: 80
        Extra: Using where
        1 row in set (0.00 sec)

和預(yù)期的一樣，這個查詢使用了索引，查詢速度在可接受范圍內(nèi)。
但是這帶來了另外一個問題：創(chuàng)建索引需要而外的空間！！當(dāng)我們對username和email都創(chuàng)建索引時，空間的使用大幅度的提升！這同樣不是我們期望看到的（無奈的選擇？）。

除了使用索引，并保證其在查詢中能使用到此索引外，分區(qū)的關(guān)鍵字段是一個很重要的優(yōu)化因素，比如下面的這個例子：

復(fù)制代碼代碼如下:

        mysql> explain select id from tsampledata where username='abcdef'\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 80
        Extra: Using where
        1 row in set (0.00 sec)

        mysql> explain select id from tsampledata where username='abcdef' and src in (2,3,4,5)\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 40
        Extra: Using where
        1 row in set (0.01 sec)

        mysql> explain select id from tsampledata where username='abcdef' and src in (2)\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 10
        Extra: Using where
        1 row in set (0.00 sec)

        mysql> explain select id from tsampledata where username='abcdef' and src in (2,3)\G
        *************************** 1. row ***************************
        id: 1
        select_type: SIMPLE
        table: tsampledata
        type: ref
        possible_keys: idx_sampledata_username
        key: idx_sampledata_username
        key_len: 66
        ref: const
        rows: 20
        Extra: Using where
        1 row in set (0.00 sec)

同一個查詢語句在根據(jù)是否針對分區(qū)限定做查詢時，查詢成本相差很大：

        where username='abcdef'                                                    rows: 80
        where username='abcdef' and src in (2,3,4,5)            rows: 40
        where username='abcdef' and src in (2)                        rows: 10
        where username='abcdef' and src in (2,3)                    rows: 20

    從分析中看出，當(dāng)根據(jù)src（分區(qū)表的分區(qū)字段）進行查詢限定時，被影響的數(shù)目（rows）在發(fā)生著變化。rows:80代表著需要對8個分區(qū)進行搜索。
    改進數(shù)據(jù)存儲：另一種分區(qū)格式
    既然在統(tǒng)計應(yīng)用中，最多用的是通過username, email進行數(shù)據(jù)查詢，那么在表存儲時，應(yīng)該考慮使用username,email進行分區(qū)，而不是通過id。因此重新創(chuàng)建分區(qū)表，導(dǎo)入數(shù)據(jù)：

復(fù)制代碼代碼如下:

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL,
        `username` varchar(32) NOT NULL DEFAULT ”,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) NOT NULL DEFAULT ”,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′,
        primary KEY (`src`,`username`,`email`, `id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=gbk
        PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY KEY (username,email)
        SUBPARTITIONS 10
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

這個定義沒什么問題，按照預(yù)期，它將根據(jù)primary key來進行數(shù)據(jù)表分區(qū)。但是這有一個非常非常嚴重的性能問題：數(shù)據(jù)在load data infile的時候，同時對數(shù)據(jù)進行索引創(chuàng)建。這大大延長了數(shù)據(jù)裝載時間，同樣是不可忍受的情況。上面這個例子，如果建表時啟用了 primary key 或者 unique key, 在我的測試系統(tǒng)上，load data infile執(zhí)行了超過12小時。而下面這個：

復(fù)制代碼代碼如下:

        CREATE TABLE `tmp_sampledata` (
        `id` bigint(20) unsigned NOT NULL,
        `username` varchar(32) NOT NULL DEFAULT ”,
        `passwd` varchar(32) DEFAULT NULL,
        `email` varchar(64) NOT NULL DEFAULT ”,
        `nickname` varchar(32) DEFAULT NULL,
        `siteid` varchar(32) DEFAULT NULL,
        `src` smallint(6) NOT NULL DEFAULT '0′
        ) ENGINE=MyISAM DEFAULT CHARSET=gbk
        PARTITION BY LIST COLUMNS(src)
        SUBPARTITION BY KEY (username,email)
        SUBPARTITIONS 10
        (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
        PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
        PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
        PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
        PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
        PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
        PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
        PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

    數(shù)據(jù)裝載僅僅用了5分鐘：
    mysql> load data infile ‘cvsfile.txt' into table tmp_sampledata fields terminated by ‘\t' escaped by ”;
    Query OK, 74352359 rows affected, 65535 warnings (5 min 23.67 sec)
    Records: 74352359 Deleted: 0 Skipped: 0 Warnings: 51267046

    So，所有的問題，又回到了2.上
    測試查詢中的模糊搜索
    對于創(chuàng)建好索引的大數(shù)據(jù)表，一般般的針對性的查詢，應(yīng)該可以滿足需要。但是有些查詢可能不能通過索引來發(fā)揮效率，比如查詢以 163.com 結(jié)尾的郵箱:

select … from … where email like ‘%163.com'

    即便數(shù)據(jù)針對 email 建立有索引，上面的查詢是用不到那個索引的。如果我們使用的是 oracle，那么還可以建立一個反向索引，但是mysql不支持反向索引。所以如果發(fā)生類似的查詢，只有兩種方案可以：
        通過數(shù)據(jù)冗余，把需要的字段反轉(zhuǎn)一遍另外保存，并創(chuàng)建一個索引
        這樣上面的那個查詢可以通過 where email like ‘moc.361%' 來完成，但是這個成本（存儲、更新）太高昂了
        通過全文檢索fulltext來實現(xiàn)。不過mysql同樣在分區(qū)表上不支持fulltext(或許等待以后的版本吧。)
        自己做分詞fulltext
    沒有最終方案

            創(chuàng)建一個不含任何索引、鍵的分區(qū)表；
            導(dǎo)入數(shù)據(jù)；
            創(chuàng)建索引；

因為創(chuàng)建索引要花很久時間，此處做了個小小調(diào)整，提高myisam索引的排序空間為1G（默認是8m）：

mysql> set myisam_sort_buffer_size=1048576000;
Query OK, 0 rows affected (0.00 sec)

        mysql> create index idx_username_src on tmp_sampledata (username,src);
        Query OK, 74352359 rows affected (7 min 13.11 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

        mysql> create index idx_email_src on tmp_sampledata (email,src);
        Query OK, 74352359 rows affected (10 min 48.30 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

        mysql> create index idx_src_username_email on tmp_sampledata(src,username,email);
        Query OK, 74352359 rows affected (16 min 5.35 sec)
        Records: 74352359 Duplicates: 0 Warnings: 0

    實際應(yīng)用中，此表可能不需要這么多索引的，都建立一遍，只是為了展示一下創(chuàng)建的速度而已。
    實際應(yīng)用中的效果
    存儲的問題暫時解決到這里了，接下來經(jīng)過了一系列的服務(wù)器參數(shù)調(diào)整以及查詢的優(yōu)化，我只能做到在這個超過9千萬數(shù)據(jù)中的查詢響應(yīng)速度控制在1到20毫秒。聽上去是個不錯的成績。但是當(dāng)我們的系統(tǒng)有超過幾百個人同時使用時，仍然顯的力不從心。或許日后還有機會能更優(yōu)化這個存儲與查詢。讓我慢慢期待吧。