procedure analyse

在项目初期，设计数据表时，可能无法根据需求准确的设计出符合实际情况的字段，可以根据需求先选择较为合适的字段类型，在项目上线一段时间，有了一定数据量后，可以用procedure analyse( )来分析表结构，此时可以根据数据的实际情况来修改字段的数据类型。

procedure analyse语法如下：

1
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

详细说明见MySQL官方说明 9.4.2.4 Using PROCEDURE ANALYSE
在mysql控制台中使用procedure analyse( )语句：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT * FROM sanitytj.caseinfo procedure analyse()\G;
*************************** 1. row ***************************
             Field_name: sanitytj.subcaseinfo.id
              Min_value: 11150
              Max_value: 1884301
             Min_length: 5
             Max_length: 7
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 947737.3191
                    Std: 1091140.9483
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL

结果显示mysql建议将id字段改为MEDIUMINT(7)，并设置成无符号。id最初设置的是INT类型，为什么mysql建议改为MEDIUMINT？从MySQL官网查到关于Integer类型的描述：

Type Storage Minimum Value Maximum Value
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

根据procedure analyse得到的信息显示id最大值为1884301，远远小于无符号INT类型的范围，也远没有达到MEDIUMINT的最大值，所以按照MySQL的建议将id改为MEDIUMINT(7)，能满足当前存储需求，还节省了空间。

再来看另外一列categoryMySQL的优化建议

1
2
3
4
5
6
7
8
9
10
11
*************************** 2. row ***************************
             Field_name: sanitytj.subcaseinfo.category
              Min_value: AP(UIA)
              Max_value: Local(UIA)
             Min_length: 7
             Max_length: 10
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 7.7703
                    Std: NULL
      Optimal_fieldtype: ENUM('AP(UIA)','Local(UIA)') NOT NULL

category存储固定分类，共有两个分类AP(UIA),Local(UIA)，mysql给出优化建议，使用ENUM类型存储，ENUM类型有什么特点？

  • ENUM类型实际保存的是TINYINT，表现形式是字符串，如果字段的值是有限个且固定，可以考虑使用ENUM代替VARCHAR
  • ENUM类型的列总有1个默认值，如果该列没有指定NOT NULL，则默认值为null，如果指定NOT NULL，则默认值为第一个枚举值
  • ENUM列如果插入了不正确的值，会将其设置为保留的枚举值0，对于字符串值，将显示空字符串，如果sql中set值不正确，该值将被忽略。

根据procedure analyse的优化建议，可以使表结构更合理，节省更多空间。