《MYSQL教程mysql 超大數據/表管理技巧》要點：
本文介紹了MYSQL教程mysql 超大數據/表管理技巧，希望對您有用。如果有疑問，可以聯系我們。
如果你對長篇大論沒有興趣,也可以直接看看結果,或許你對結果感興趣.在實際應用中經過存儲、優化可以做到在超過9千萬數據中的查詢響應速度控制在1到20毫秒.看上去是個不錯的成績,不過優化這條路沒有終點,當我們的系統有超過幾百人、上千人同時使用時,仍然會顯的力不從心.MYSQL必讀
目錄：MYSQL必讀
??? 分區存儲
??? 優化查詢
??? 改進分區
??? 模糊搜索
??? 持續改進的方案MYSQL必讀
正文：MYSQL必讀
??? 分區存儲
??? 對于超大的數據來說,分區存儲是一個不錯的選擇,或者說這是一個必選項.對于本例來說,數據記錄來源不同,首先可以根據來源來劃分這些數據.但是僅僅這樣還不夠,因為每個來源的分區的數據都可能超過千萬.這對數據的存儲和查詢還是太大了.MySQL5.x以后已經比較好的支持了數據分區以及子分區.因此數據就采用分區+子分區來存儲.MYSQL必讀
??? 下面是基本的數據結構定義：MYSQL必讀
??? 對于擁有分區及子分區的數據表,分區條件(包括子分區條件)中使用的數據列,都應該定義在primary key 或者 unique key中.詳細的分區定義格式,可以參考MySQL的文檔.上面的結構是第一稿的存儲方式(后文還將進行修改).采用load data infile的方式加載,用時30分鐘加載8千萬記錄.感覺還是挺快的(bulk_insert_buffer_size=8m).
??? 基本查詢優化
??? 數據裝載完畢后,我們測試了一個查詢：MYSQL必讀
??? 這是毋庸置疑的,通過id進行查詢是使用了主鍵,查詢速度會很快.但是這樣的做法幾乎沒有意義.因為對于終端用戶來說,不可能知曉任何的資料的id的.假如需要按照username來進行查詢的話：MYSQL必讀
??????? 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)
MYSQL必讀
??? 那這個查詢就沒法用了.根本就沒人能等待一個上億表的全表搜索!這是我們就考慮是否給username創建一個索引,這樣肯定會提高查詢速度：MYSQL必讀
??????? create index idx_username on tmp_sampledata(username);MYSQL必讀
??? 這個創建索引的時間很久,似乎超過了數據裝載時間,不過好歹建好了.MYSQL必讀
??? 和預期的一樣,這個查詢使用了索引,查詢速度在可接受范圍內.
??? 但是這帶來了另外一個問題：創建索引需要而外的空間!!當我們對username和email都創建索引時,空間的使用大幅度的提升!這同樣不是我們期望看到的(無奈的選擇?).MYSQL必讀
??? 除了使用索引,并保證其在查詢中能使用到此索引外,分區的關鍵字段是一個很重要的優化因素,比如下面的這個例子：MYSQL必讀
??????? 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必讀
??????? 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必讀
??????? 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)
MYSQL必讀
??? 同一個查詢語句在根據是否針對分區限定做查詢時,查詢成本相差很大：MYSQL必讀
??????? 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: 20MYSQL必讀
??? 從分析中看出,當根據src(分區表的分區字段)進行查詢限定時,被影響的數目(rows)在發生著變化.rows:80代表著需要對8個分區進行搜索.
??? 改進數據存儲：另一種分區格式
??? 既然在統計應用中,最多用的是通過username, email進行數據查詢,那么在表存儲時,應該考慮使用username,email進行分區,而不是通過id.因此重新創建分區表,導入數據：MYSQL必讀
??? 這個定義沒什么問題,按照預期,它將根據primary key來進行數據表分區.但是這有一個非常非常嚴重的性能問題：數據在load data infile的時候,同時對數據進行索引創建.這大大延長了數據裝載時間,同樣是不可忍受的情況.上面這個例子,如果建表時啟用了 primary key 或者 unique key, 在我的測試系統上,load data infile執行了超過12小時.而下面這個：MYSQL必讀
??? So,所有的問題,又回到了2.上
??? 測試查詢中的模糊搜索
??? 對于創建好索引的大數據表,一般般的針對性的查詢,應該可以滿足需要.但是有些查詢可能不能通過索引來發揮效率,比如查詢以 163.com 結尾的郵箱:MYSQL必讀
??????? select … from … where email like ‘%163.com'MYSQL必讀
??? 即便數據針對 email 建立有索引,上面的查詢是用不到那個索引的.如果我們使用的是 oracle,那么還可以建立一個反向索引,但是mysql不支持反向索引.所以如果發生類似的查詢,只有兩種方案可以：
??????? 通過數據冗余,把需要的字段反轉一遍另外保存,并創建一個索引
??????? 這樣上面的那個查詢可以通過 where email like ‘moc.361%' 來完成,但是這個成本(存儲、更新)太高昂了
??????? 通過全文檢索fulltext來實現.不過mysql同樣在分區表上不支持fulltext(或許等待以后的版本吧.)
??????? 自己做分詞fulltext
??? 沒有最終方案MYSQL必讀
??????????? 創建一個不含任何索引、鍵的分區表；
??????????? 導入數據；
??????????? 創建索引；MYSQL必讀
??? 因為創建索引要花很久時間,此處做了個小小調整,提高myisam索引的排序空間為1G(默認是8m)：MYSQL必讀
??????? mysql> set myisam_sort_buffer_size=1048576000;
??????? Query OK, 0 rows affected (0.00 sec)MYSQL必讀
??????? 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: 0MYSQL必讀
??????? 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: 0MYSQL必讀
??????? 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: 0MYSQL必讀
??? 實際應用中,此表可能不需要這么多索引的,都建立一遍,只是為了展示一下創建的速度而已.
??? 實際應用中的效果
??? 存儲的問題暫時解決到這里了,接下來經過了一系列的服務器參數調整以及查詢的優化,我只能做到在這個超過9千萬數據中的查詢響應速度控制在1到20毫秒.聽上去是個不錯的成績.但是當我們的系統有超過幾百個人同時使用時,仍然顯的力不從心.或許日后還有機會能更優化這個存儲與查詢.讓我慢慢期待吧.MYSQL必讀
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5302.html
