站长性能优化指引：优化MySQL语句的十个建议

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 varchar(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不需要让没有更新过的数据移出缓存.

5.不要过度使用artificial primary key

artificial primary key非常棒,因为他们使得schema更少的变化.如果我们将地理信息存在以美国邮编为基础的表中,如果邮编系统突然改变了,那我们就会有大麻烦了.另一方面,采用natural key有时候也很棒,譬如我们需要join多对多的关系表时,我们不应该这样：

CREATE TABLE posts_tags (

relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,

post_id int UNSIGNED NOT NULL,

tag_id int UNSIGNED NOT NULL,

PRIMARY KEY(relation_id),

UNIQUE INDEX(post_id, tag_id)

);

artificial key完全是多余的,而且post-tag关系的数量将会受到整形数据的系统最大值的限制.

CREATE TABLE posts_tags (

post_id int UNSIGNED NOT NULL,

tag_id int UNSIGNED NOT NULL,

PRIMARY KEY(post_id, tag_id)

);

6.学习索引

你选择的索引的好坏很重要,不好的话可能破坏数据库.对那些还没有在数据库学习很深入的人来说,索引可以看作是就是hash排序.例如如果我们用查询语句SELECT * FROM users WHERE last_name = ‘Goldstein’,而last_name没有索引的话,那么DBMS将会查询每一行,看看是否等于“Goldstein”.索引通常是B-tree(还有其他的类型),可以加快比较的速度.

你需要给你要select,group,order,join的列加上索引.显然每个索引所需的空间正比于表的行数,所以越多的索引将会占用更多的 内存.而且写数据时,索引也会有影响,因为每次写数据时都会更新对应的索引.你需要取一个平衡点,取决每个系统和实施代码的需要.

7.SQL不是C

C是经典的过程语言,对于一个程序员来说,C语言也是个陷阱,使你错误的以为SQL也是一种过程语言(当然SQL也不是功能语言也不是面向对象的).你不要想象对数据进行操作,而是要想象有一组数据,以及它们之间的关系.经常使用子查询时会出现错误的用法.

SELECT a.id,

(SELECT MAX(created)

FROM posts

WHERE author_id = a.id)

AS latest_post

FROM authors a

因为这个子查询是耦合的,子查询要使用外部查询的信息,我们应该使用join来代替.

SELECT a.id, MAX(p.created) AS latest_post

FROM authors a

INNER JOIN posts p

ON (a.id = p.author_id)

GROUP BY a.id

8.理解你的引擎

MySQL有两种存储引擎：MyISAM和InnoDB.它们分别有自己的性能特点和考虑因素.总体来讲,MyISAM适合读数据很多的情况,InnoDB适合写数据很多的情况,但也有很多情况下正好相反.最大的区别是它们如何处理COUNT函数.

MyISAM缓存有表meta-data,如行数.这就意味着,COUNT(*)对于一个结构很好的查询是不需要消耗多少资源的.然后对于 InnoDB来说,就没有这种缓存.举个例子,我们要对一个查询来分页,假设你有这样一个语句SELECT * FROM users LIMIT 5,10,而运行SELECT COUNT(*) FROM users LIMIT 5,10 时,对于MyISAM很快完成,而对InnoDB就需要和第一个语句相同的时间.MySQL有个SQL_CALC_FOUND_ROWS选项,可以告诉 InnoDB运行查询语句时就计算行数,之后再从SELECT FOUND_ROWS()来获取.这是MySQL特有的.但使用InnoDB有时候是非常必要的,你可以获得一些功能(如行锁定,stord procedure等).

9.MySQL特定的快捷键

MySQL提供了许多扩展,方便使用.譬如INSERT … SELECT, INSERT … ON DUPLICATE KEY UPDATE, 以及REPLACE.

我能用到它们时是毫不犹豫的,因为它们很方便,能在许多情况下发挥不错的效果.但是MySQL也有一些危险的关键字,应该少用.例如INSERT DELAYED,它告诉MySQL不需要立即插入数据(例如在写日志的时候).但问题是如果在很高数据量的情况下,插入可能会被无限期延迟,导致插入队列 爆满.你也可以使用MySQL的索引提示来指出哪些索引是需要使用的.MySQL大部分时间运行是不错的,但如果schema设计不好的话或语句写得不好 的话,MySQL的表现可能很糟糕.

10.到这里为止吧

最后,如果你关心MySQL性能优化的话,请阅读Peter Zaitsev的关于MySQL性能的博客,他写了许多关于数据库管理和优化的博客.

作者：20bits.com