mysql 超大資料/表管理技巧

如果你對長篇大論沒有興趣，也可以直接看看結果，或許你對結果感興趣。在實際應用中經過儲存、最佳化可以做到在超過9千萬資料中的查詢響應速度控制在1到20毫秒。看上去是個不錯的成績，不過最佳化這條路沒有終點，當我們的系統有超過幾百人、上千人同時使用時，仍然會顯的力不從心。

目錄：

分區儲存
最佳化查詢
改進分區
模糊搜尋
持續改進的方案

本文：

分區儲存
對於超大的資料來說，分區儲存是一個不錯的選擇，或者說這是一個必選項。對於本例來說，資料記錄來源不同，首先可以根據來源來劃分這些資料。但是僅僅這樣還不夠，因為每個來源的分區的資料都可能超過千萬。這對資料的儲存和查詢還是太大了。MySQL5.x以後已經比較好的支援了資料分區以及子分區。因此資料就採用分區+子分區來儲存。

下面是基本的資料結構定義：

複製代碼 代碼如下: 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) */

對於擁有分區及子分區的資料表，分區條件（包括子分區條件）中使用的資料列，都應該定義在primary key 或者 unique key中。詳細的分區定義格式，可以參考MySQL的文檔。上面的結構是第一稿的儲存方式（後文還將進行修改）。採用load data infile的方式載入，用時30分鐘載入8千萬記錄。感覺還是挺快的（bulk_insert_buffer_size=8m）。
基本查詢最佳化
資料裝載完畢後，我們測試了一個查詢：

複製代碼 代碼如下: 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來進行查詢的話：

複製代碼 代碼如下: 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建立一個索引，這樣肯定會提高查詢速度：

create index idx_username on tmp_sampledata(username);

這個建立索引的時間很久，似乎超過了資料裝載時間，不過好歹建好了。

複製代碼 代碼如下: 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)

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

除了使用索引，並保證其在查詢中能使用到此索引外，分區的關鍵字段是一個很重要的最佳化因素，比如下面的這個例子：

複製代碼 代碼如下: 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)

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

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

從分析中看出，當根據src（分區表的分區欄位）進行查詢限定時，被影響的數目（rows）在發生著變化。rows:80代表著需要對8個分區進行搜尋。
改進資料存放區：另一種分區格式
既然在統計應用中，最多用的是通過username, email進行資料查詢，那麼在表格儲存體時，應該考慮使用username,email進行分區，而不是通過id。因此重新建立分區表，匯入資料：

複製代碼 代碼如下: 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)?;

這個定義沒什麼問題，按照預期，它將根據primary key來進行資料表分區。但是這有一個非常非常嚴重的效能問題：資料在load data infile的時候，同時對資料進行索引建立。這大大延長了資料裝載時間，同樣是不可忍受的情況。上面這個例子，如果建表時啟用了 primary key 或者 unique key, 在我的測試系統上，load data infile執行了超過12小時。而下面這個：

複製代碼 代碼如下: 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)?;

資料裝載僅僅用了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.上
測試查詢中的模糊搜尋
對於建立好索引的大資料表，一般般的針對性的查詢，應該可以滿足需要。但是有些查詢可能不能通過索引來發揮效率，比如查詢以 163.com 結尾的郵箱:

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

即便資料針對 email 建立有索引，上面的查詢是用不到那個索引的。如果我們使用的是 oracle，那麼還可以建立一個反向索引，但是mysql不支援反向索引。所以如果發生類似的查詢，只有兩種方案可以：
通過資料冗餘，把需要的欄位反轉一遍另外儲存，並建立一個索引
這樣上面的那個查詢可以通過 where email like ‘moc.361%' 來完成，但是這個成本（儲存、更新）太高昂了
通過全文檢索索引fulltext來實現。不過mysql同樣在分區表上不支援fulltext(或許等待以後的版本吧。)
自己做分詞fulltext
沒有最終方案

建立一個不含任何索引、鍵的分區表；
匯入資料；
建立索引；

因為建立索引要花很久時間，此處做了個小小調整，提高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

實際應用中，此表可能不需要這麼多索引的，都建立一遍，只是為了展示一下建立的速度而已。
實際應用中的效果
儲存的問題暫時解決到這裡了，接下來經過了一系列的伺服器參數調整以及查詢的最佳化，我只能做到在這個超過9千萬資料中的查詢響應速度控制在1到20毫秒。聽上去是個不錯的成績。但是當我們的系統有超過幾百個人同時使用時，仍然顯的力不從心。或許日後還有機會能更最佳化這個儲存與查詢。讓我慢慢期待吧。