MySQL優化之資料庫結構：資料對象優化

使用PROCEDURE ANALYSE函數優化表的資料類型

表需要使用何種資料類型，是需要根據應用來判斷的。在MySQL中，可以使用函數PROCEDURE ANALYSE()對目前應用的表進行分析，該函數可以對資料庫中列的資料類型提出優化建議，使用者可以根據應用的實際情況斟酌考慮是否實施優化。

以下是函數PROCEDURE ANALYSE()的使用方法：

SELECT * FROM tbl_name PROCEDURE ANALYSE(); SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256); 輸出的每一列資訊都會對資料表中的列的資料類型提出優化建議。以上第二個語句告訴PROCEDURE ANALYSE()不要為那些包含的值多于16個或者256位元組的ENUM類型提出建議。如果沒有這樣的限制，輸出資訊可能很長；ENUM定義通常很難閱讀。

根據PROCEDURE ANALYSE()函數的輸出資訊，使用者可能會發現，一些表中的字段可以修改為更加高效的資料類型。如果決定改變某個字段的類型，則需要使用ALTER TABLE語句。

mysql> desc statistics_news_category_history;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(10)     | NO   | PRI | NULL    | auto_increment | 
| news_category | varchar(20) | YES  |     | NULL    |                | 
| news_num      | int(10)     | YES  |     | NULL    |                | 
| news_date     | varchar(10) | YES  |     | NULL    |                | 
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)           

mysql> select * from statistics_news_category_history procedure analyse(9,256)\G;
*************************** 1. row ***************************
             Field_name: datacrawldb.statistics_news_category_history.id
              Min_value: 26
              Max_value: 30
             Min_length: 2
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 28.0000
                    Std: 1.4142
      Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: datacrawldb.statistics_news_category_history.news_category
              Min_value: bigdata
              Max_value: test
             Min_length: 4
             Max_length: 10
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 6.6000
                    Std: NULL
      Optimal_fieldtype: ENUM('bigdata','cloud','datacenter','storage','test') NOT NULL
*************************** 3. row ***************************
             Field_name: datacrawldb.statistics_news_category_history.news_num
              Min_value: 1
              Max_value: 33
             Min_length: 1
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.4000
                    Std: 11.9766
      Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL
*************************** 4. row ***************************
             Field_name: datacrawldb.statistics_news_category_history.news_date
              Min_value: 2014-02-17
              Max_value: 2014-02-17
             Min_length: 10
             Max_length: 10
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 10.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('2014-02-17') NOT NULL
4 rows in set (0.00 sec)           

優化小技巧

1) 使用正确合适的類型，不要将數字存儲為字元串。

2) 盡可能地使用最有效(最小)的資料類型。MySQL有很多節省磁盤空間和記憶體的專業化類型。

3) 盡可能使用較小的整數類型使表更小。例如，MEDIUMINT經常比INT好一些，因為MEDIUMINT列使用的空間要少25%。

4) 如果可能，聲明列為NOT NULL。它使任何事情更快而且每列可以節省一位。注意如果在應用程式中确實需要NULL，應該毫無疑問使用它，隻是避免預設地在所有列上有它。

5) 對于MyISAM表，如果沒有任何變長列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的記錄格式。這比較快但是不幸地可能會浪費一些空間。即使你已經用CREATE選項讓 VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定長度的行。

6) 使用sample character set，例如latin1。盡量少使用utf-8，因為utf-8占用的空間是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。