mysql表结构优化语句procedure analyse();

show create table webservicelog /G*************************** 1. row ***************************       Table: webservicelogCreate Table: CREATE TABLE `webservicelog` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `fromto` tinyint(1) NOT NULL DEFAULT '0',  `biztype` tinyint(2) NOT NULL DEFAULT '0',  `bizcode` varchar(32) NOT NULL DEFAULT '',  `result` tinyint(1) unsigned NOT NULL DEFAULT '0',  `errmsg` varchar(256) NOT NULL DEFAULT '',  `oprtime` datetime NOT NULL,  KEY `id` (`id`),  KEY `biz` (`biztype`,`bizcode`),  KEY `Operatetime` (`oprtime`)) ENGINE=InnoDB AUTO_INCREMENT=21136 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(`oprtime`))(PARTITION p201610 VALUES LESS THAN (736634) ENGINE = InnoDB, PARTITION p201611 VALUES LESS THAN (736664) ENGINE = InnoDB, PARTITION p201612 VALUES LESS THAN (736695) ENGINE = InnoDB, PARTITION p201701 VALUES LESS THAN (736726) ENGINE = InnoDB, PARTITION p201702 VALUES LESS THAN (736754) ENGINE = InnoDB, PARTITION p201703 VALUES LESS THAN (3652119) ENGINE = InnoDB) */1 row in set (0.00 sec)mysql> select * from webservicelog procedure analyse() /G*************************** 1. row ***************************             Field_name: test.webservicelog.id              Min_value: 1              Max_value: 21135             Min_length: 1             Max_length: 5       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 15563.8073                    Std: 3224.0990      Optimal_fieldtype: SMALLINT(5) UNSIGNED NOT NULL*************************** 2. row ***************************             Field_name: test.webservicelog.fromto              Min_value: 1              Max_value: 1             Min_length: 1             Max_length: 1       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 1.0000                    Std: 0.0000      Optimal_fieldtype: ENUM('1') NOT NULL*************************** 3. row ***************************             Field_name: test.webservicelog.biztype              Min_value: 1              Max_value: 1             Min_length: 1             Max_length: 1       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 1.0000                    Std: 0.0000      Optimal_fieldtype: ENUM('1') NOT NULL*************************** 4. row ***************************             Field_name: test.webservicelog.bizcode              Min_value: areyouok              Max_value: areyouok9999             Min_length: 8             Max_length: 12       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 11.7789                    Std: NULL      Optimal_fieldtype: CHAR(12) NOT NULL*************************** 5. row ***************************             Field_name: test.webservicelog.result              Min_value: 1              Max_value: 1             Min_length: 1             Max_length: 1       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 1.0000                    Std: 0.0000      Optimal_fieldtype: ENUM('1') NOT NULL*************************** 6. row ***************************             Field_name: test.webservicelog.errmsg              Min_value: 111              Max_value: 111             Min_length: 3             Max_length: 3       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 3.0000                    Std: NULL      Optimal_fieldtype: ENUM('111') NOT NULL*************************** 7. row ***************************             Field_name: test.webservicelog.oprtime              Min_value: 2016-10-02 01:01:01              Max_value: 2044-02-25 22:00:00             Min_length: 19             Max_length: 19       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 19.0000                    Std: NULL      Optimal_fieldtype: CHAR(19) NOT NULL7 rows in set (0.02 sec)
可以看到主要针对表中的一些字段进行了优化，首先是id字段，系统认为应该设置为smallint(5)类型，后续id字段还会再大量增加，这个建议不合适。
然后是 fromto这个字段， 该字段本来就是定义的tinyint(1)啊，为什么还要优化成enum类型呢？没必要吧
总之，这些只是建议，不一定适用，要分析后再决定是否采用。