优化MySQL语句的十个建议

上一篇 / 下一篇 2012-05-15 09:48:28 / 个人分类：数据库

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

　　为什么那个清单很糟糕51Testing软件测试网W1X} g#S

　　1、他的力气没使对地方51Testing软件测试网'X?,?p.dD4u

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

G)Vm5YiG;i0`0　　2、很好的问题，但是很糟糕的解决方法

+C3i7a [Z"xr0　　 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)
);

h8d@8H[*q0　　3、有点匪夷所思……

.k/krh%l%|+K{1a0　　他的许多建议都是让人非常吃惊的，譬如“移除不必要的括号”。你这样写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、建立基准，建立基准，建立基准！51Testing软件测试网}2A;VTx!n

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

　　2、性能测试，性能测试，性能测试！51Testing软件测试网V$f7iWk3o:h\

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

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

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

R&b.gj$m4q03、减小你的schema

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

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

hgc6pY m0　　4、拆分你的表

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

'irzf2M L aJ MFc0

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关系的数量将会受到整形数据的系统最大值的限制。

ye5BL@051Testing软件测试网 h2@#iBb(s/}

CREATE TABLE posts_tags (
post_id int UNSIGNED NOT NULL,
tag_id int UNSIGNED NOT NULL,
PRIMARY KEY(post_id, tag_id)
);

　　6、学习索引51Testing软件测试网 p3R8\bb7P

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

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

　　7、SQL不是C

`+EV cM OCV0　　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特定的快捷键51Testing软件测试网(m,xO&BdO

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

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

　　10、到这里为止吧

Os.|)pF4z Bf'f V0　　最后，如果你关心MySQL性能优化的话，请阅读Peter Zaitsev的关于MySQL性能的博客，他写了许多关于数据库管理和优化的博客。

收藏举报

TAG:

查看全部评论

bob123654

不要让那些真正对你好的人，慢慢的从你的生活中消失，无论爱情还是友情，都需要用心经营。

优化MySQL语句的十个建议

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅