Mysql數據表的優化方法總結

Mysql中除了對數據庫及語句程序進行優化之外數據表優化也是非常的重要了，我們這裡來為各位介紹Mysql數據表的優化方法吧。

優化表的數據類型

表需要使用何種數據類型，是需要根據應用來判斷的。雖然應用設計的時候需要考慮字段的長度留有一定的冗余，但是不推薦讓很多字段都留有大量的冗余，這樣即浪費存儲也浪費內存。

我們可以使用PROCEDURE ANALYSE()對當前已有應用的表類型的判斷，該函數可以對數據表中的列的數據類型提出優化建議，可以根據應用的實際情況酌情考慮是否實施優化。語法：

 代碼如下 復制代碼    SELECT * FROM tbl_name PROCEDURE ANALYSE();
   SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

輸出的每一列信息都會對數據表中的列的數據類型提出優化建議。第二個例子告訴PROCEDURE ANALYSE()不要為那些包含的值多於16個或者256字節的ENUM類型提出建議。如果沒有這樣的限制，輸出信息可能很長；ENUM定義通常很難閱讀。

在對字段類型進行優化時，可以根據統計信息並結合應用的實際情況對其進行優化。
通過拆分，提高表的訪問效率

這裡我們所說的拆分，主要是針對Myisam類型的表，拆分的方法可以分成兩種情況：

1) 縱向拆分：

縱向拆分是只按照應用訪問的頻度，將表中經常訪問的字段和不經常訪問的字段拆分成兩個表，經常訪問的字段盡量是定長的，這樣可以有效的提高表的查詢和更新的效率。

2) 橫向拆分：

橫向拆分是指按照應用的情況，有目的的將數據橫向拆分成幾個表或者通過分區分到多個分區中，這樣可以有效的避免Myisam表的讀取和更新導致的鎖問題。

規范化

數據庫德規范化設計強調數據的獨立性，數據應該盡可能少地冗余，因為存在過多的冗余數據，這就意味著要占用了更多的物理空間，同時也對數據的維護和一致性檢查帶來了問題。

但是對於查詢操作很多的應用，一次查詢可能需要訪問多表進行，如果通過冗余紀錄在相同表中，更新的代價增加不多，但是查詢操作效率可以有明顯提高，這種情況就可以考慮通過冗余數據來提高效率。
使用冗余統計表

使用create temporary table語法，它是基於session的表，表的數據保存在內存裡面，當session斷掉後，表自然消除。

對於大表的統計分析，如果統計的數據量不大，利用insert, select將數據移到臨時表中比直接在大表上做統計要效率更高。

選擇更合適的表類型

1、如果應用出現比較嚴重的鎖沖突，請考慮是否更改存儲引擎到innodb，行鎖機制可以有效的減少鎖沖突的出現。
2、如果應用查詢操作很多，且對事務完整性要求不嚴格，則可以考慮使用Myisam存儲引擎。

優化Mysql數據庫

1、創建索引

對於查詢占主要的應用來說，索引顯得尤為重要。很多時候性能問題很簡單的就是因為我們忘了添加索引而造成的，或者說沒有添加更為有效的索引導致。如果不加索引的話，那麼查找任何哪怕只是一條特定的數據都會進行一次全表掃描，如果一張表的數據量很大而符合條件的結果又很少，那麼不加索引會引起致命的性能下降。但是也不是什麼情況都非得建索引不可，比如性別可能就只有兩個值，建索引不僅沒什麼優勢，還會影響到更新速度，這被稱為過度索引。

2、復合索引

比如有一條語句是這樣的：select * from users where area='beijing' and age=22;
如果我們是在area和age上分別創建單個索引的話，由於mysql查詢每次只能使用一個索引，所以雖然這樣已經相對不做索引時全表掃描提高了很多效率，但是如果在area、age兩列上創建復合索引的話將帶來更高的效率。如果我們創建了(area, age, salary)的復合索引，那麼其實相當於創建了(area,age,salary)、(area,age)、(area)三個索引，這被稱為最佳左前綴特性。因此我們在創建復合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

3、索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。

4、使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

5、排序的引問題

mysql查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。

6、like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

7、不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01';

8、不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

mysql千萬級數據大表該如何優化

1.數據的容量：1-3年內會大概多少條數據，每條數據大概多少字節；
2.數據項：是否有大字段，那些字段的值是否經常被更新；
3.數據查詢SQL條件：哪些數據項的列名稱經常出現在WHERE、GROUP BY、ORDER BY子句中等；
4.數據更新類SQL條件：有多少列經常出現UPDATE或DELETE 的WHERE子句中；
5.SQL量的統計比，如：SELECT：UPDATE+DELETE：INSERT=多少？
6.預計大表及相關聯的SQL，每天總的執行量在何數量級？
7.表中的數據：更新為主的業務 還是 查詢為主的業務
8.打算采用什麼數據庫物理服務器，以及數據庫服務器架構？
9.並發如何？
10.存儲引擎選擇InnoDB還是MyISAM？

大致明白以上10個問題，至於如何設計此類的大表，應該什麼都清楚了！

至於優化若是指創建好的表，不能變動表結構的話，那建議InnoDB引擎，多利用點內存，減輕磁盤IO負載，因為IO往往是數據庫服務器的瓶頸。

另外對優化索引結構去解決性能問題的話，建議優先考慮修改類SQL語句，使他們更快些，不得已只靠索引組織結構的方式，當然此話前提是，
索引已經創建的非常好，若是讀為主，可以考慮打開query_cache，

以及調整一些參數值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size 。

1、分庫分表

很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優化途徑，現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然後根據mid分發記錄到這些表中，牛逼的代碼大概是這樣子：

mysql分庫分表的實現過程可參考：http://www.111cn.net/database/mysql/103264.htm