02-优化表的数据类型
表需要使用何种数据类型是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费磁盘存储空间，同时在应用程序操作时也浪费物理内存。
在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语句。
下面分析一下表duck_cust的数据类型是否需要优化。
（1）首先创建测试表duck_cust，duck_cust表中记录了客户的一些基本信息：
drop table duck_cust;
CREATE TABLE duck_cust(
cust_num MEDIUMINT AUTO_INCREMENT, --客户编号
cust_title TINYINT, --客户标题号
cust_last CHAR(20) NOT NULL, --客户姓氏
cust_first CHAR(15) NOT NULL, --客户名
cust_suffix ENUM('Jr.', 'II', 'III','IV', 'V', 'M.D.','PhD'), --附加码
cust_add1 CHAR(30) NOT NULL, --客户地址
cust_add2 CHAR(10), --客户地址
cust_city CHAR(18) NOT NULL, --客户所在城市
cust_state CHAR(2) NOT NULL, --客户所在州
cust_zip1 CHAR(5)NOT NULL, --客户邮编
cust_zip2 CHAR(4), --客户邮编
cust_duckname CHAR(25) NOT NULL, --客户名称
cust_duckbday DATE, --客户生日
PRIMARY KEY (cust_num)
)TYPE=MyISAM;
（2）然后生成一些测试数据：
INSERT INTO duck_cust VALUES(NULL, 1, 'Irishlord', 'Red', 'III', '1022 N.E. Sea of Rye', 'A207', 'Seacouver', 'WA', '98601', '3464', 'Netrek Rules', '1967:10:21');
INSERT INTO duck_cust VALUES(NULL, 4, 'Thegreat', 'Vicki', 0, '2004 Singleton Dr.', 0, 'Freedom', 'KS', '67209', '4321', 'Frida Kahlo de Tomayo', '1948:03:21');
INSERT INTO duck_cust VALUES(NULL, 9, 'Montgomery', 'Chantel', 0, '1567 Terra Cotta Way', 0, 'Chicago', 'IL', '89129', '4444', 'Bianca', '1971:07:29');
INSERT INTO duck_cust VALUES(NULL, 7, 'Robert', 'David', 'Sr.', '20113 Open Road Highway', '#6', 'Blacktop', 'AZ', '00606', '1952', 'Harley', '1949:08:00');
INSERT INTO duck_cust VALUES(NULL, 5, 'Kazui', 'Wonko', 'PhD', '42 Cube Farm Lane', 'Gatehouse', 'Vlimpt', 'CA', '45362', 0, 'Fitzwhistle', '1961:12:04');
INSERT INTO duck_cust VALUES(NULL, 6, 'Gashlycrumb', 'Karen', 0, '3113 Picket Fence Lane', 0, 'Fedora', 'VT', '41927', '5698', 'Tess D''urberville', '1948:08:19');
这时，查看一下表结构：
desc duck_cust;
+--------------+--------------------------------+------+------+--------+------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------------+------+------+--------+------------+
| cust_num | mediumint(9) | | MUL | 0 | |
| cust_title | tinyint(4) | YES | | NULL | |
| cust_last | char(20) | | | | |
| cust_first | char(15) | | | | |
| cust_suffix |enum('Jr.','II','III','IV','V','M.D.','PhD')| YES | | NULL | |
| cust_add1 | char(30) | | | | |
| cust_add2 | char(10) | YES | | NULL | |
| cust_city | char(18) | | | | |
| cust_state | char(2) | | | | |
| cust_zip1 | char(5) | | | | |
| cust_zip2 | char(4) | YES | | NULL | |
| cust_duckname| char(25) | | | | |
| cust_duckbday| date | YES | | NULL | |
+--------------+---------------------------------+------+------+--------+-----------+
（3）使用PROCEDURE ANALYSE()函数确定要优化的列：
mysql> SELECT * FROM duck_cust PROCEDURE ANALYSE()\G;
1. row
Field_name: sakila.duck_cust.cust_num
Min_value: 1
Max_value: 6
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 3.5000
Std: 1.7078
Optimal_fieldtype: ENUM('1','2','3','4','5','6') NOT NULL
2. row
…
从结果中可以看到 test.duck_cust.cust_num 列的 Min_length、Max_length、Avg_value_or_avg_length，根据这些统计值，可以对列进行优化，例如，插入的数据最大长度和最小长度都是 1，所以，可以优化字段 cust_num 为 mediumint(2)；同时，上面的结果也给出了优化建议“Optimal_fieldtype: ENUM('1','2','3','4','5','6') NOT NULL”。
看到这个建议读者可能会觉得很奇怪，怎么给出了枚举类型，而不是我们预期的整型？因为这时分析的测试表记录数太少，使得cust_name的唯一值太少，因此函数觉得用枚举类型会更合理。如果是对一个大表进行分析，提出的建议会更准确。
根据给出的统计信息和优化建议，可以使用如下语句进行字段类型的更改：
mysql> alter table duck_cust modify cust_num mediumint(2);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0