mysql 超年夜數據/表治理技能。本站提示廣大學習愛好者：（mysql 超年夜數據/表治理技能）文章只能為提供參考，不一定能成為您想要的結果。以下是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毫秒。聽上去是個不錯的成就。然則當我們的體系有跨越幾百小我同時應用時，依然顯的力有未逮。也許往後還無機會能更優化這個存儲與查詢。讓我漸漸等待吧。
