MySQL优化之数据库结构：数据对象优化

rrt44

使用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语句。

[python] view plaincopy
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)  

[python] view plaincopy
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等。