MySQL具體解釋（16）-----------海量資料建議

標籤：sam   char   ble   text   地址   定義   計算   欄位   file   

以下是一部分比較重要的建議：
1、選擇正確的儲存引擎
以 MySQL為例。包含有兩個儲存引擎 MyISAM 和 InnoDB，每一個引擎都有利有弊。
MyISAM 適合於一些須要大量查詢的應用，但其對於有大量寫操作並非非常好。甚至你僅僅是須要update一個欄位，整個表都會被鎖起來。而別的進程，就算是讀進程都無法操作直到讀操作完畢。

另外，MyISAM 對於 SELECT COUNT(*) 這類的計算是超快無比的。
InnoDB 的趨勢會是一個很複雜的儲存引擎。對於一些小的應用。它會比 MyISAM 還慢。

可是它支援“行鎖” 。於是在寫操作比較多的時候。會更優秀。而且，他還支援很多其它的進階應用程式，比方：事務。

2、最佳化欄位的資料類型
記住一個原則，越小的列會越快。對於大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。

所以。把你的資料變得緊湊會對這樣的情況很有協助。由於這降低了對硬碟的訪問。
假設一個表僅僅會有幾列罷了（比方說字典表。配置表），那麼，我們就沒有理由使用 INT 來做主鍵。使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。假設你不須要記錄時間，使用 DATE 要比 DATETIME 好得多。當然，你也須要留夠足夠的擴充空間。

3、為搜尋欄位加入索引
索引並不一定就是給主鍵或是唯一的欄位。假設在你的表中，有某個欄位你總要會經經常使用來做搜尋，那麼最好是為其建立索引。除非你要搜尋的欄位是大的文字欄位。那應該建立全文索引。
4、避免使用Select *從資料庫裡讀出越多的資料。那麼查詢就會變得越慢。

而且。假設你的資料庫server和WEBserver是兩台獨立的server的話，這還會添加網路傳輸的負載。即使你要查詢資料表的全部欄位，也盡量不要用*萬用字元，善用內建提供的欄位排除定義或許能給帶來很多其它的便利。
5、使用 ENUM 而不是 VARCHAR
ENUM 類型是很快和緊湊的。在實際上，其儲存的是 TINYINT，但其外表上顯示為字串。

這樣一來。用這個欄位來做一些選項列表變得相當的完美。比如，性別、民族、部門和狀態之類的這些欄位的取值是有限並且固定的，那麼，你應該使用 ENUM 而不是 VARCHAR。
6、儘可能的使用 NOT NULL
除非你有一個非常特別的原因去使用 NULL 值，你應該總是讓你的欄位保持 NOT NULL。 NULL事實上須要額外的空間，而且，在你進行比較的時候，你的程式會更複雜。

當然，這裡並非說你就不能使用NULL了，現實情況是非常複雜的，依舊會有些情況下。你須要使用NULL值。
7、固定長度的表會更快
假設表中的全部欄位都是“固定長度”的，整個表會被覺得是 “static” 或 “fixed-length”。

比如，表中沒有例如以下類型的欄位： VARCHAR，TEXT，BLOB。

僅僅要你包含了當中一個這些欄位，那麼這個表就不是“固定長度靜態表”了，這樣，MySQL 引擎會用還有一種方法來處理。
固定長度的表會提高效能，由於MySQL搜尋得會更快一些，由於這些固定的長度是非常easy計算下一個資料的位移量的，所以讀取的自然也會非常快。而假設欄位不是定長的，那麼，每一次要找下一條的話，須要程式找到主鍵。
而且。固定長度的表也更easy被緩衝和重建。

只是，唯一的副作用是，固定長度的欄位會浪費一些空間，由於定長的欄位不管你用不用。他都是要分配那麼多的空間。

使用“垂直切割”技術，你能夠切割你的表成為兩個一個是定長的。一個則是不定長的。
8、垂直切割“垂直切割”是一種把資料庫中的表按列變成幾張表的方法，這樣能夠減少表的複雜度和欄位的數目，從而達到最佳化的目的。
比如：在User表中有一個欄位是家庭地址。這個欄位是可選欄位。相比起，並且你在資料庫操作的時候除了個人資訊外，你並不須要常常讀取或是改寫這個欄位。那麼，為什麼不把他放到另外一張表中呢？ 這樣會讓你的表有更好的效能。大家想想是不是，大量的時候，我對於使用者表來說，僅僅實使用者ID，username，口令。使用者角色等會被常常使用。

小一點的表總是會有好的效能。
另外，你須要注意的是，這些被分出去的欄位所形成的表，你不會常常性地去Join他們，不然的話，這種效能會比不切割時還要差。並且，會是極數級的下降。

9、EXPLAIN 你的 SELECT 查詢。
使用 EXPLAIN keyword能夠讓你知道MySQL是怎樣處理你的SQL語句的。這能夠幫你分析你的查詢語句或是表結構的效能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被怎樣利用的，你的資料表是怎樣被搜尋和排序的……等等，等等。
通常我們能夠對照較複雜的尤其是涉及到多表的SELECT語句，把keywordEXPLAIN加到前面。

 

插入大量資料的最佳化：

1。

使用多行插入取代單行插入操作。比單條插入快非常多。另外。加大bulk_insert_buffer_size的值。比如設定為64M（在MYISAM引擎下）

　　　　insert into t values(),(),();

2.使用LOAD DATA INFILE .......INTO TABLE ..比插入相同多行的insert語句快

3.對使用InnoDB儲存引擎的表，你能夠在一個事務中完畢insert操作，這樣InoDB將在事務，結束時重新整理改變。而不是每一條insert語句後都重新整理改變。相同的操作能夠應用到update。

4.假設是非空表，使用alter table table_name disable keys，然後load data infile,匯入完資料在運行：

alter table table_name enable keys. 假設是空表。就不須要這個操作。由於myisam表在空表中匯入資料時，是先匯入資料然後建立indexs。

 

執行個體例

如以下一段代碼，關閉事務commit，等待更新完畢後再一次性提交，能夠將原來10幾個小時的工作變成10幾分鐘。這裡讀的是一個700多萬行的檔案。更新記錄約300多萬條。

 

my $db_handle = DBI->connect("DBI:mysql:database=$database;host=$host", $db_user, $db_pass, {‘RaiseError‘ => 1,AutoCommit => 0})|| die "Could not connect to database: $DBI::errstr";    eval {        while( !eof($fd) )        {            $CloudID = <$fd> ;chomp $CloudID;            $crc_code = <$fd> ;chomp $crc_code;            my $sql = "call `room_match`.`crcWriteCode`($CloudID,‘$crc_code‘);" ;            my $affect_rows = $db_handle->do($sql);        }        $db_handle->commit();    };

開始的時候是第一次都運行sql，這樣速度極慢！

設定 autocommit = 0，再 commit後，速度極大提升。

MySQL具體解釋（16）-----------海量資料建議