数据库性能优化技巧之：变长表、定长表

什么是定长表和变长表？

包含任何varchar、text等变长字段的数据表，即为变长表，反之则为定长表。

表设计思路：

在设计表结构时如果能够使用定长数据类型尽量用定长的，因为定长表的查询、检索、更新速度都很快。必要时可以把部分关键的、承担频繁访问的表拆分，例如定长数据一个表，非定长数据一个表。因此规划数据结构时需要进行全局考虑；进行表结构设计时，应当做到恰到好处，反复推敲，从而实现最优的数据存储体系。

定长表、变长表各自的优缺点：

A.对于变长表，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保持性能。而定长表就没有这个问题；
B.如果表中有可变长的字段，将它们转换为定长字段能够改进性能，因为定长记录易于处理。但在试图这样做之前，应该考虑下列问题：
C.使用定长列涉及某种折衷。它们更快，但占用的空间更多。char(n) 类型列的每个值总要占用n 个字节(即使空串也是如此)，因为在表中存储时，值的长度不够将在右边补空格；
D.而varchar(n)类型的列所占空间较少，因为只给它们分配存储每个值所需要的空间，每个值再加一个字节用于记录其长度。因此，如果在char和varchar类型之间进行选择，需要对时间与空间作出折衷；
E.有时不能使用定长类型，即使想这样做也不行。例如对于比255字符更长的串，没有定长类型。

举例说明：

例如您需要有一个存储用户登录信息和其他资料的表。需要有以下域：
uid, username, password, gender(性别), regdate(注册时间), nickname(昵称), site(个人网站地址), address(地址)

从域的内容分析，以下域长度都会在255以下，而且长度相对稳定，那就将其分离出来，建做一个定长表：mysite_user，结构如下：
CREATE TABLE `mysite_user` (
`uid` mediumint(8) unsigned NOT NULL auto_increment,
`username` char(15) NOT NULL default '',
`password` char(32) NOT NULL default '',
`gender` tinyint(1) NOT NULL default '0',
`regdate` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`uid`),
UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM;

然后将其他长度无法预料、并且有可能超过255长度的域建做另外一个表：mysite_userprofile，这是一个变长表。结构如下：
CREATE TABLE `mysite_userprofile` (
`uid` mediumint(8) unsigned NOT NULL default '0',
`nickname` varchar(30) NOT NULL default '',
`site` varchar(75) NOT NULL default '',
`address` varchar(512) NOT NULL default '',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM;

用户登录验证查询是非常频繁的，当用户数多了以后，表的设计更加影响查询速度。用户登录的时候只查询mysite_user表，按照我们的设计这个表是定长，所以一定程度会加快查询速度。
而在需要调用用户全部信息的时候，可以用左连接将两个表的数据拼在一起，这样就和没分表一样使用了。例如：
SELECT u.*, up.* FROM mysite_user AS u 
LEFT JOIN mysite_userprofile AS up ON up.uid=u.uid
WHERE u.uid='888';

来源于：http://www.2ky.cn/h/59/7135.htm，转载要求注明来源！

参考文献：《数据库系统实现》第二版