优化 MySQL 语句的十个建议

Jaslabs的Justin Silverton列出了十条有关优化MySQL查询的语句，我不得不对此发表言论，因为这个清单非常非常糟糕。另外一个Mike也同样意识到了。所以在这个博客中，我要做两件事情，第一，指出为什么这个清单很糟糕，第二，列出我的清单，希望我的比较好些。继续看吧，无畏的读者们!

(译者注：作者借这个题目反讽另一篇同名的文章)

为什么那个清单很糟糕

1.他的力气没使对地方

我们要遵循的一个准则就是如果你要优化代码时，应该先找出瓶颈在哪。然而Silverton先生的力气没有用对地方。我认为60％的优化是基于清楚 理解SQL和数据库基础的。你需要知道join和子查询的区别，列索引，以及如何将数据规范化等等。另外的35％的优化是需要清楚数据库选择时的性能表 现，例如COUNT(*)可能很快也可能很慢，要看你选用什么数据库引擎。还有一些其他要考虑的因素，例如数据库在什么时候不用缓存，什么时候存在硬盘上 而不存在内存中，什么时候数据库创建临时表等等。剩下的5％就很少会有人碰到了，但Silverton先生恰好在这上面花了大量的时间。我从来就没用过 SQL_SAMLL_RESULT。

2.很好的问题，但是很糟糕的解决方法

Silverton先生提出了一些很好的问题。MySQL针对长度可变的列如TEXT或BLOB，将会使用动态行格式(dynamic row format)，这意味着排序将在硬盘上进行。我们的方法不是要回避这些数据类型，而是将这些数据类型从原来的表中分离开，放入另外一个表中。下面的 schema可以说明这个想法：

CREATE TABLE posts (

id int UNSIGNED NOT NULL AUTO_INCREMENT,

author_id int UNSIGNED NOT NULL,

created timestamp NOT NULL,

PRIMARY KEY(id)

);

CREATE TABLE posts_data (

post_id int UNSIGNED NOT NULL.

body text,

PRIMARY KEY(post_id)

);

3. 有点匪夷所思……

他的许多建议都是让人非常吃惊的，譬如“移除不必要的括号”。你这样写SELECT * FROM posts WHERE (author_id = 5 AND published = 1)，还是这样写SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比较好的DBMS都会自动进行识别做出处理。这种细节就好像C语言中是i++快些还是++i快些。真的，如果你把精力都花在这上面了，那就不用写代码了。

我的列表

看看我的列表是不是更好吧。我先从最普遍的开始。

1.建立基准，建立基准，建立基准!

如果需要做决定的话，我们需要数据说话。什么样的查询是最糟的?瓶颈在哪?我什么情况下会写出糟糕的查询?基准测试可以让你模拟高压情况，然后借助 性能测评工具，可以让你发现数据库配置中的错误。这样的工具有supersmack, ab, SysBench。这些工具可以直接测试你的数据库(譬如supersmack)，或者模拟网络流量(譬如ab)。

2.性能测试，性能测试，性能测试!

那么，当你能够建立一些高压情况之后，你需要找出配置中的错误。这就是性能测评工具可以帮你做的了。它可以帮你发现配置中的瓶颈，不论是在内存中，CPU中，网络中，硬盘I/O，或者是以上皆有。

你要做的第一件事就是开启慢查询日志(slow query log)，装上mtop。这样你就能获取那些恶意的入侵者的信息了。有需要运行10秒的查询语句正在破坏你的应用程序吗?这些家伙会展示给你看他的查询语句是怎么写的。

在你发现那些很慢的查询语句后，你需要用MySQL自带的工具，如EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它们会告诉你资源都消耗在哪了，查询语句的缺陷在哪，譬如一个有三次join子查询的查询语句是否在内存中进行排序，还是在硬盘 上进行。当然你也应该使用测评工具如top，procinfo，vmstat等等获取更多系统性能信息。

3.减小你的schema

在你开始写查询语句之前，你需要设计schema。记住将一个表装入内存所需要的空间大概是行数*一行的大小。除非你觉得世界上的每个人都会在你的 网站注册2兆8000亿次的话，否则你不需要采用BITINT作为你的user_id。同样的，如果一个文本列是固定大小的话(譬如US邮编，通常 是”XXXXX-XXXX”的形式)，采用VARCHAR的话会给每行增加多余的字节。

有些人对数据库规范化不以为意，他们说这样会形成相当复杂的schema。然而适当的规范化会减少化冗余数据。(适当的规范化)就意味着牺牲少许性 能，换取整体上更少的footprint，这种性能换取内存在计算机科学中是很常见的。最好的方法是IMO，就是开始先规范化，之后如果性能需要的话，再 反规范化。你的数据库将会更逻辑化，你也不用过早的进行优化。(译者注，这一段我不是很理解，可能翻译错了，欢迎纠正。)

4.拆分你的表

通常有些表只有一些列你是经常需要更新的。例如对于一个博客，你需要在许多不同地方显示标题(如最近的文章列表)，只在某个特定页显示概要或者全文。水平垂直拆分是很有帮助的：

CREATE TABLE posts (

id int UNSIGNED NOT NULL AUTO_INCREMENT,

author_id int UNSIGNED NOT NULL,

title var char( 128 ),

created timestamp NOT NULL,

PRIMARY KEY(id)

);

CREATE TABLE posts_data (

post_id int UNSIGNED NOT NULL,

teaser text,

body text,

PRIMARY KEY(post_id)

);

上面的schema是对读数据进行的优化。经常要访问的数据存在一个表中，那些不经常访问的数据放在另一个。被拆分后，不经常访问的数据占据更少的 内存。你也可以优化写数据，经常更新的数据放在一个表，不经常更新的放在另一个表。这可以使缓存更高效，因为MySQL不需要让没有更新过的数据移出缓 存。