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

最新推荐文章于 2020-12-04 11:10:39 发布

铮亮不锈

最新推荐文章于 2020-12-04 11:10:39 发布

阅读量583

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/shi_yi_fei/article/details/59627271

版权

数据库专栏收录该内容

25 篇文章 0 订阅

订阅专栏

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

show create table webservicelog \G
*************************** 1. row ***************************
       Table: webservicelog
Create 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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_value_or_avg_length: 19.0000
                    Std: NULL
      Optimal_fieldtype: CHAR(19) NOT NULL
7 rows in set (0.02 sec)

可以看到主要针对表中的一些字段进行了优化，首先是id字段，系统认为应该设置为smallint(5)类型，后续id字段还会再大量增加，这个建议不合适。

然后是 fromto这个字段，该字段本来就是定义的tinyint(1)啊，为什么还要优化成enum类型呢？没必要吧

总之，这些只是建议，不一定适用，要分析后再决定是否采用。