优化MySQL语句的十个建议

标签: IT技术 程序员 MySQL | 发表时间:2012-04-28 03:07 | 作者:唐小娟
出处:http://blog.jobbole.com

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

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快些。真的,如果你把精力都花在这上面了,那就不用写代码了。

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   编译: 伯乐在线?- 唐小娟

【如需转载,请标注并保留原文链接、译文链接和译者等信息,谢谢合作!】

 

相关文章

相关 [优化 mysql 语句] 推荐:

优化MySQL语句的十个建议

- - 博客 - 伯乐在线
(译者注:作者借这个题目反讽另一篇同名的文章). Jaslabs的Justin Silverton列出了 十条有关优化MySQL查询的语句,我不得不对此发表言论,因为这个清单非常非常糟糕. 另外一个 Mike也同样意识到了. 所以在这个博客中,我要做两件事情,第一,指出为什么这个清单很糟糕,第二,列出我的清单,希望我的比较好些.

MYSQL查询SQL语句性能优化方法

- - 数据库 - ITeye博客
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,. Sql 代码 : select id from t where num is null;.

如何高效快速地优化MySQL、SQL语句(附源码)

- - 运维派
韩锋,宜信技术研发中心数据库架构师. 精通多种关系型数据库,曾任职于当当网、TOM在线等公司,曾任多家公司首席DBA、数据库架构师等职,多年一线数据库架构、设计、开发经验. 著有《SQL优化最佳实践》一书. 优化SQL,是DBA常见的工作之一. 如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题.

浅谈MySQL中优化sql语句查询常用的30种方法

- - 数据库 - ITeye博客
本篇文章是对MySQL中优化sql语句查询常用的30种方法进行了详细的分析介绍,需要的朋友参考下. 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

mysql语句执行顺序

- - SQL - 编程语言 - ITeye博客
首先,SELECT语句的基本语法如下:  .   LIMIT from, count; # Limiting row count on results 对于MySQL,除了第一行,其他部分都是可选的,有的DBMS要求FROM从句也是必须的.   SQL Select语句完整的执行顺序【从DBMS使用者角度】:  .

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.