优化 MySQL 语句的十个建议（2）

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性能的博客，他写了许多关于数据库管理和优化的博客。