mysql數(shù)據(jù)庫(kù)海量數(shù)據(jù)優(yōu)化方法總結(jié)

下面是一部分比較重要的建議：
1、選擇正確的存儲(chǔ)引擎
以 MySQL為例，包括有兩個(gè)存儲(chǔ)引擎 MyISAM 和 InnoDB，每個(gè)引擎都有利有弊。
MyISAM 適合于一些需要大量查詢的應(yīng)用，但其對(duì)于有大量寫操作并不是很好。甚至你只是需要update一個(gè)字段，整個(gè)表都會(huì)被鎖起來(lái)，而別的進(jìn)程，就算是讀進(jìn)程都無(wú)法操作直到讀操作完成。另外，MyISAM 對(duì)于 SELECT COUNT(*) 這類的計(jì)算是超快無(wú)比的。
InnoDB 的趨勢(shì)會(huì)是一個(gè)非常復(fù)雜的存儲(chǔ)引擎，對(duì)于一些小的應(yīng)用，它會(huì)比 MyISAM 還慢。但是它支持“行鎖” ，于是在寫操作比較多的時(shí)候，會(huì)更優(yōu)秀。并且，他還支持更多的高級(jí)應(yīng)用，比如：事務(wù)。

2、優(yōu)化字段的數(shù)據(jù)類型
記住一個(gè)原則，越小的列會(huì)越快。對(duì)于大多數(shù)的數(shù)據(jù)庫(kù)引擎來(lái)說(shuō)，硬盤操作可能是最重大的瓶頸。所以，把你的數(shù)據(jù)變得緊湊會(huì)對(duì)這種情況非常有幫助，因?yàn)檫@減少了對(duì)硬盤的訪問(wèn)。
如果一個(gè)表只會(huì)有幾列罷了（比如說(shuō)字典表，配置表），那么，我們就沒(méi)有理由使用 INT 來(lái)做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會(huì)更經(jīng)濟(jì)一些。如果你不需要記錄時(shí)間，使用 DATE 要比 DATETIME 好得多。當(dāng)然，你也需要留夠足夠的擴(kuò)展空間。
3、為搜索字段添加索引
索引并不一定就是給主鍵或是唯一的字段。如果在你的表中，有某個(gè)字段你總要會(huì)經(jīng)常用來(lái)做搜索，那么最好是為其建立索引，除非你要搜索的字段是大的文本字段，那應(yīng)該建立全文索引。
4、避免使用Select *從數(shù)據(jù)庫(kù)里讀出越多的數(shù)據(jù)，那么查詢就會(huì)變得越慢。并且，如果你的數(shù)據(jù)庫(kù)服務(wù)器和WEB服務(wù)器是兩臺(tái)獨(dú)立的服務(wù)器的話，這還會(huì)增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。即使你要查詢數(shù)據(jù)表的所有字段，也盡量不要用*通配符，善用內(nèi)置提供的字段排除定義也許能給帶來(lái)更多的便利。
5、使用 ENUM 而不是 VARCHAR
ENUM 類型是非?？旌途o湊的。在實(shí)際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來(lái)，用這個(gè)字段來(lái)做一些選項(xiàng)列表變得相當(dāng)?shù)耐昝?。例如，性別、民族、部門和狀態(tài)之類的這些字段的取值是有限而且固定的，那么，你應(yīng)該使用 ENUM 而不是 VARCHAR。
6、盡可能的使用 NOT NULL
除非你有一個(gè)很特別的原因去使用 NULL 值，你應(yīng)該總是讓你的字段保持 NOT NULL。 NULL其實(shí)需要額外的空間，并且，在你進(jìn)行比較的時(shí)候，你的程序會(huì)更復(fù)雜。 當(dāng)然，這里并不是說(shuō)你就不能使用NULL了，現(xiàn)實(shí)情況是很復(fù)雜的，依然會(huì)有些情況下，你需要使用NULL值。
7、固定長(zhǎng)度的表會(huì)更快
如果表中的所有字段都是“固定長(zhǎng)度”的，整個(gè)表會(huì)被認(rèn)為是 “static” 或 “fixed-length”。 例如，表中沒(méi)有如下類型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一個(gè)這些字段，那么這個(gè)表就不是“固定長(zhǎng)度靜態(tài)表”了，這樣，MySQL 引擎會(huì)用另一種方法來(lái)處理。
固定長(zhǎng)度的表會(huì)提高性能，因?yàn)镸ySQL搜尋得會(huì)更快一些，因?yàn)檫@些固定的長(zhǎng)度是很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移量的，所以讀取的自然也會(huì)很快。而如果字段不是定長(zhǎng)的，那么，每一次要找下一條的話，需要程序找到主鍵。
并且，固定長(zhǎng)度的表也更容易被緩存和重建。不過(guò)，唯一的副作用是，固定長(zhǎng)度的字段會(huì)浪費(fèi)一些空間，因?yàn)槎ㄩL(zhǎng)的字段無(wú)論你用不用，他都是要分配那么多的空間。
使用“垂直分割”技術(shù)，你可以分割你的表成為兩個(gè)一個(gè)是定長(zhǎng)的，一個(gè)則是不定長(zhǎng)的。
8、垂直分割“垂直分割”是一種把數(shù)據(jù)庫(kù)中的表按列變成幾張表的方法，這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。
例如：在User表中有一個(gè)字段是家庭地址，這個(gè)字段是可選字段，相比起，而且你在數(shù)據(jù)庫(kù)操作的時(shí)候除了個(gè)人信息外，你并不需要經(jīng)常讀取或是改寫這個(gè)字段。那么，為什么不把他放到另外一張表中呢？ 這樣會(huì)讓你的表有更好的性能，大家想想是不是，大量的時(shí)候，我對(duì)于用戶表來(lái)說(shuō)，只有用戶ID，用戶名，口令，用戶角色等會(huì)被經(jīng)常使用。小一點(diǎn)的表總是會(huì)有好的性能。
另外，你需要注意的是，這些被分出去的字段所形成的表，你不會(huì)經(jīng)常性地去Join他們，不然的話，這樣的性能會(huì)比不分割時(shí)還要差，而且，會(huì)是極數(shù)級(jí)的下降。
9、EXPLAIN 你的 SELECT 查詢；
使用 EXPLAIN 關(guān)鍵字可以讓你知道MySQL是如何處理你的SQL語(yǔ)句的。這可以幫你分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN 的查詢結(jié)果還會(huì)告訴你你的索引主鍵被如何利用的，你的數(shù)據(jù)表是如何被搜索和排序的……等等，等等。
通常我們可以對(duì)比較復(fù)雜的尤其是涉及到多表的SELECT語(yǔ)句，把關(guān)鍵字EXPLAIN加到前面。

插入大量數(shù)據(jù)的優(yōu)化：

1。使用多行插入代替單行插入操作。比單條插入快很多；另外，加大bulk_insert_buffer_size的值，例如設(shè)置為64M（在MYISAM引擎下）

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

2.使用LOAD DATA INFILE .......INTO TABLE ..比插入同樣多行的insert語(yǔ)句快

3.對(duì)使用InnoDB存儲(chǔ)引擎的表，你可以在一個(gè)事務(wù)中完成insert操作，這樣InoDB將在事務(wù)，結(jié)束時(shí)刷新改變。而不是每一條insert語(yǔ)句后都刷新改變。同樣的操作可以應(yīng)用到update。

4.如果是非空表，使用alter table table_name disable keys，然后load data infile,導(dǎo)入完數(shù)據(jù)在執(zhí)行：

alter table table_name enable keys. 如果是空表，就不需要這個(gè)操作，因?yàn)閙yisam表在空表中導(dǎo)入數(shù)據(jù)時(shí)，是先導(dǎo)入數(shù)據(jù)然后建立indexs。

例如下面一段代碼，關(guān)閉事務(wù)commit，等待更新完成后再一次性提交，可以將原來(lái)10幾個(gè)小時(shí)的工作變成10幾分鐘。這里讀的是一個(gè)700多萬(wàn)行的文件，更新記錄約300多萬(wàn)條。

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();
    };
開始的時(shí)候是第一次都執(zhí)行sql，這樣速度極慢！設(shè)置 autocommit = 0，再 commit后，速度極大提升。