MySQL Tips

转载

[b]1.你的表的行长度是固定的吗？[/b]

长度固定的好处就是便于检索，一个起码的常识，如果能用 char，绝不用 varchar，我需要强调一点是，必须是[b]所有[/b]字段都是固定长度的，那么行的长度才能固定、才能有速度上的差别。如果你以前并没有在这方面没有足够重视，那么最好现在检查一下你现在正在使用的那些表。

在 phpMyAdmin 里直接看到一个“固定”的字样：

[img][file=85][/img]

如果用 SQL 语句看，方法如下：

[quote]
SHOW TABLE STATUS LIKE "表名";
[/quote]

如果是固定长度，则 Row_format 字段应该是“Fixed”

当发现有不是定长的表，问题在哪里？看看有没有 text 之类的字段，它们跟 varchar 是一个概念，如果是一个读写极为频繁、行数很多的表，应该以做关联的形式把这类字段放在一个新表里。如果 tinytext 字段，甚至可以直接改成 char(255) 继续用，那会浪费一些空间，但空间的价格跟速度的价格相比，微不足道。

[b]2.存储密码（或者说 md5），用 binary(16) 而不是 char(32)[/b]

一个 PHP 程序员第一次往数据库里存密码估计都是明码方式的，之后才知道 md5，而第一次存 md5 密码肯定都用的是 char(32) 或者 varchar(32)，我甚至忽视了这个问题，很久后才意识到，应该用 binary(16)

比方说一个表只有 id、user、password 三个字段，你要创建个新用户，最终的 SQL 可能是这样：

[quote]
INSERT INTO members SET user = "platinum", password = [color=green]"[/color]202cb962ac59075b964b07152d234b70[color=green]"[/color]
[/quote]

现在则应该这样：

[quote]
INSERT INTO members SET user = "platinum", password = [color=green]0x[/color]202cb962ac59075b964b07152d234b70
[/quote]

就是说，你应该把一个 md5 串看成是 16 个 HEX 十六进制数，而非长度为 32 的英文数字混淆的字符串。在这里，好处仅仅是节省 16 个字节而已，但是以后，你会碰到需要用 guid 之类（而非自增长的整数 id）做主键的情况（举个例子，假设你写了个爬虫抓取整个 web，为了标识那无数的网址你只能用 md5 把它们缩短后作为主键），这时候效率会更明显，要知道，以二进制存储的时候，不需要考虑大小写、不需要考虑字符集，而且更适合被索引 hash。

[b]3.搞明白 MySQL 从 4.1 开始有的字符集特性[/b]

凭我的感觉，中国的 PHP 从业人员里只有不到 10% 或者更少的人能理解 MySQL 字符集的概念。很多人还是像对 MySQL 3.23 那般直来直去的操作，而奇怪的是他们通常还能得到正确的结果，因而把错误延续下去，可谓假做真时真亦假。如果你没有耐心去[url=http://jjgod.3322.org/2005/07/31/a-mysql-41-story/]研究细节[/url]，至少应该如下的规则操作：

在设计表的时候，只要是有汉字并且需要你选择字符集的字段，都选成 utf8_general_ci（纯英文数字的字段我用的 ascii_general_ci）
连接数据库（mysql_connect）之后，按你实际需要输入的编码，加一条 query“SET NAMES gbk”或者“SET NAMES utf8”，之后你就照常使用吧
如果哪天你需要另一种编码，在 SELECT 前“SET NAMES utf8/gbk”

即插入和读取的时候，你可以自由的分别选择所用的编码，而存储的是 UTF-8。

比方说我现在正在做的一个东西就是如此，后台是 GBK（IE 里的 GB2312 实际是 GBK）的，而做这部分的人习惯于 MySQL 4.0 以前的操作，于是我让他们连接前只要多发一个“SET NAMES gbk”就可以了，而显示的时候由于一些原因，需要以 UTF-8 显示，那我就“SET NAMES utf8”，相比 mysql 3/4.0，大家总共就只需要多两个 query 就可以相安无事了。不需要在 PHP 里再 iconv 什么的。

另外还有一个小差别，因为在有了字符集概念后，每个汉字都是一个字符，而非 2 到 3 byte 的字符串，因此一个 utf8_general_ci 的 char(30) 能装 30 个汉字，而不是以前的 15 个或者 10 个。在不同的 MySQL 下试下一个 query 就明白了

[quote]
SELECT LENGTH("长度"), CHAR_LENGTH("长度")
[/quote]

[hr]

MySQL 还有很多新特性，都应该是试验一下。比方说老版本的 MySQL 里要用 LEFT JOIN 代替子查询，导致我一直不屑于，结果我偶然把一条让我头疼的、执行时间需要 5 秒的 SQL 改用子查询的方式试了一下，很震惊，只花了千分之八秒……连个 memcache 还花了千分之六秒呢，可见如果刨除连接的开销，实际的查询时间缩短了多少？