分析诊断工具之五：Procedure Analyse优化表结构

一、Procedure Analyse

PROCEDURE ANALYSE() ，在优化表结构时可以辅助参考分析语句。通过分析select查询结果对现有的表的每一列给出优化的建议。

利用此语句，MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。

【只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。】 

例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。 

我们在设计表时有时候总会思考到底某个字段选用什么类型呢。其实我们在后期调优时也可以使用 procedure analyse();分析表结构看看mysql给我们的字段建议，综合实际情况调整一些字段的类型(这个已经很琐碎了，一般应用都到不了这么细，并且我们鉴于经验设计的数据库基本都能满足应用。

 

PROCEDURE ANALYSE的语法如下：

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

max_elements:指定每列非重复值的最大值，当超过这个值的时候，MySQL不会推荐enum类型。（默认值256）
max_memory （默认值8192）analyse()为每列找出所有非重复值所采用的最大内存大小。
执行返回中的Optimal_fieldtype列是mysql建议采用的列。

样例程序

mysql> DESC user_account;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| USERID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| USERNAME | varchar(10) | NO | | NULL | |
| PASSSWORD | varchar(30) | NO | | NULL | |
| GROUPNAME | varchar(10) | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from user_account PROCEDURE ANALYSE(1)\G;
*************************** 1. row ***************************
Field_name: ibatis.user_account.USERID
Min_value: 1
Max_value: 103
Min_length: 1
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 51.7500
Std: 50.2562
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 2. row ***************************
Field_name: ibatis.user_account.USERNAME
Min_value: dfsa
Max_value: LMEADORS
.........................................................

从第一行输出我们可以看到analyze分析ibatis.user_account.USERID列最小值1，最大值103，最小长度1，最大长度3...，并给出了改字段的优化建议：建议将该字段的数据类型改成TINYINT(3) UNSIGNED NOT NULL。

总结
从上面这个例子我们可以看出analyze能根据目前表中的数据情况给出优化建议。当数据库在生产环境运行一定时间以后，开发或是DBA能参考analyze的分析结果来对表结构做出一定的优化。