MySQL语句的优化

核心提示：567CREATE 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)

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

1
2
3
4
5
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也不是功能语言也不是面向对象的）。你不要想象对数据进行操作，而是要想象有一组数据，以及它们之间的关系。经常使用子查询时会出现错误的用法。

1
2
3
4
5
6
SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post
FROM authors a
因为这个子查询是耦合的，子查询要使用外部查询的信息，我们应该使用join来代替。

1
2
3
4
5
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的表现可能很糟糕。