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 類型是非?？旌途o湊的。在實際上，其保存的是 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后，速度極大提升。