MySQL詳解（16）-----------海量資料建議，mysql-----------

MySQL詳解（16）-----------海量資料建議，mysql-----------

下面是一部分比較重要的建議：
1、選擇正確的儲存引擎
以 MySQL為例，包括有兩個儲存引擎 MyISAM 和 InnoDB，每個引擎都有利有弊。
MyISAM 適合於一些需要大量查詢的應用，但其對於有大量寫操作並不是很好。甚至你只是需要update一個欄位，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到讀操作完成。另外，MyISAM 對於 SELECT COUNT(*) 這類的計算是超快無比的。
InnoDB 的趨勢會是一個非常複雜的儲存引擎，對於一些小的應用，它會比 MyISAM 還慢。但是它支援“行鎖” ，於是在寫操作比較多的時候，會更優秀。並且，他還支援更多的進階應用程式，比如：事務。
2、最佳化欄位的資料類型
記住一個原則，越小的列會越快。對於大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。所以，把你的資料變得緊湊會對這種情況非常有協助，因為這減少了對硬碟的訪問。
如果一個表只會有幾列罷了（比如說字典表，配置表），那麼，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果你不需要記錄時間，使用 DATE 要比 DATETIME 好得多。當然，你也需要留夠足夠的擴充空間。
3、為搜尋欄位添加索引
索引並不一定就是給主鍵或是唯一的欄位。如果在你的表中，有某個欄位你總要會經常用來做搜尋，那麼最好是為其建立索引，除非你要搜尋的欄位是大的文字欄位，那應該建立全文索引。
4、避免使用Select *從資料庫裡讀出越多的資料，那麼查詢就會變得越慢。並且，如果你的資料庫伺服器和WEB伺服器是兩台獨立的伺服器的話，這還會增加網路傳輸的負載。即使你要查詢資料表的所有欄位，也盡量不要用*萬用字元，善用內建提供的欄位排除定義也許能給帶來更多的便利。
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搜尋得會更快一些，因為這些固定的長度是很容易計算下一個資料的位移量的，所以讀取的自然也會很快。而如果欄位不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。
並且，固定長度的表也更容易被緩衝和重建。不過，唯一的副作用是，固定長度的欄位會浪費一些空間，因為定長的欄位無論你用不用，他都是要分配那麼多的空間。
使用“垂直分割”技術，你可以分割你的表成為兩個一個是定長的，一個則是不定長的。
8、垂直分割“垂直分割”是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和欄位的數目，從而達到最佳化的目的。
例如：在User表中有一個欄位是家庭地址，這個欄位是可選欄位，相比起，而且你在資料庫操作的時候除了個人資訊外，你並不需要經常讀取或是改寫這個欄位。那麼，為什麼不把他放到另外一張表中呢？ 這樣會讓你的表有更好的效能，大家想想是不是，大量的時候，我對於使用者表來說，只有使用者ID，使用者名稱，口令，使用者角色等會被經常使用。小一點的表總是會有好的效能。
另外，你需要注意的是，這些被分出去的欄位所形成的表，你不會經常性地去Join他們，不然的話，這樣的效能會比不分割時還要差，而且，會是極數級的下降。
9、EXPLAIN 你的 SELECT 查詢；
使用 EXPLAIN 關鍵字可以讓你知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的效能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜尋和排序的……等等，等等。
通常我們可以對比較複雜的尤其是涉及到多表的SELECT語句，把關鍵字EXPLAIN加到前面。

 

插入大量資料的最佳化：

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後，速度極大提升。

著作權聲明：歡迎轉轉載，希望轉載的同時添加原文地址，謝謝合作，學習快樂！