3G Eden
RSS|
数据库操作是当今 Web 应用程序中的主要瓶颈。 不仅是 DBA（数据库管理员）需要为各种性能问题操心，程序员为做出准确的结构化表，优化查询性能和编写更优代码，也要费尽心思。 在本文中，我列出了一些针对程序员的 MySQL 优化技术。
在我们开始学习之前，我补充一点：你可以在 Envato Market 上找到大量的 MySQL 脚本和实用程序。  结 |
1.优化查询的查询缓存大部分MySQL服务器都有查询缓存功能。这是提高性能的最有效的方法之一，这是由数据库引擎私下处理的。当同一个查询被多次执行，结果会直接从缓存里提取，这样速度就很快。 主要的问题是，这对程序员来说太简单了，不容易看到，我们很多人都容易忽略。我们实际上是可以组织查询缓存执行任务的。
查询缓存在第一行不执行的原因在于CURDTE()功能的使用。这适用于所有的非确定性功能，就像NOW()和RAND()等等。。。因为功能返回的结果是可变的。MySQL决定禁用查询器的查询缓存。我们所需要做的是通过添加一额外一行PHP，在查询前阻止它发生。 |
||
2. EXPLAIN你的选择查询使用EXPLAIN关键词可以帮助了解MySQL是怎样运行你的查询的。这有助于发现瓶颈和查询或表结构的其它问题。 EXPLAIN的查询结果会展示哪一个索引被使用过，表示怎样扫描和储存的，等等。。。 选择一个SELECT查询（一个有连接的复杂查询会更好），在它的前面添加关键词EXPLAIN，这样就可以直接使用数据库了。结果会以一个漂亮的表来展示。例如，就好比我执行连接时忘了添加一栏的索引：
现在它只会从表2里面扫描9和16行，而非扫描7883行。经验法则是乘以所有“行”那一栏的数字，你的查询性能会跟结果数字成比例的。 |
3. 获取唯一行时使用LIMIT 1
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
// do I have any users from Alabama? // what NOT to do:$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");if (mysql_num_rows($r) > 0) { // ...} // much better:$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");if (mysql_num_rows($r) > 0) { // ...} |
4. 索引搜索字段索引不仅仅是为了主键或唯一键。如果你会在你的表中按照任何列搜索，你就都应该索引它们。
正如你所看到的，这个规则也适用于如 "last_name LIKE 'a%'"的部分字符串搜索。当从字符串的开头搜索时，MySQL就可以使用那一列的索引。 你也应该明白什么样搜索可以不使用有规律的索引。例如，当搜索一个单词时（例如，"WHERE post_content LIKE '%apple%'"），你将不会看到普通索引的好处。你最好使用 mysql 全文搜索或者构建你自己的索引解决方案。
|
5. 索引并对连接使用同样的字段类型
|
|
1
2
3
4
5
6
7
8
|
// looking for companies in my state$r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); // both state columns should be indexed// and they both should be the same type and character encoding// or MySQL might do full table scans |
6. 不要ORDER BY RAND()
起初这是一个听起来挺酷的技巧, 让许多菜鸟程序员陷入了这个陷阱。但你可能不知道，一旦你开始在查询中使用它，你创建了非常可怕的查询瓶颈。
如果你真的需要对结果随机排序, 这有一个更好的方法。补充一些额外代码,你将可以防止当数据成指数级增长时造成的瓶颈。关键问题是，MySQL必须在排序之前对表中的每一行执行RAND()操作（这需要处理能力），并且仅仅给出一行。
|
1
2
3
4
5
6
7
8
9
10
11
|
// what NOT to do:$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); // much better: $r = mysql_query("SELECT count(*) FROM user");$d = mysql_fetch_row($r);$rand = mt_rand(0,$d[0] - 1); $r = mysql_query("SELECT username FROM user LIMIT $rand, 1"); |
所以挑选一个小于结果数的随机数，并将其用作LIMIT子句中的偏移量。
7. 避免使用SELECT *
|
|
1
2
3
4
5
6
7
8
9
10
11
|
// not preferred$r = mysql_query("SELECT * FROM user WHERE user_id = 1");$d = mysql_fetch_assoc($r);echo "Welcome {$d['username']}"; // better:$r = mysql_query("SELECT username FROM user WHERE user_id = 1");$d = mysql_fetch_assoc($r);echo "Welcome {$d['username']}"; // the differences are more significant with bigger result sets |
8. 几乎总是有一个id字段
在每个以id列为PRIMARY KEY的数据表中，优先选择AUTO_INCREMENT或者INT。 也可以优选使用UNSIGNED，因为该值不能为负的。
即使你拥有一个具有唯一用户名字段的用户表，也不要将其作为主键。 VARCHAR字段作为主键（检索）速度较慢。通过内部ID引用所有的用户数据，你的代码中将更加结构化。
有些后台操作是由MySQL引擎本身完成的，它在内部使用主键字段。当数据库设置越复杂（集群，分区等...），这就变得更加重要了。
这个规则的一个可能的例外是“关联表”，用于两个表之间的多对多类型的关联。例如，“posts_tags”表中包含两列：post_id，tag_id，用于保存表名为“post”和“tags”的两个表之间的关系。这些表可以具有包含两个id字段的PRIMARY键。
9. 相比VARCHAR优先使用ENUMENUM枚举类型是非常快速和紧凑的。在内部它们像TINYINT一样存储，但它们可以包含和显示字符串值。这使他们成为某些领域的完美候选。 如果有一个字段只包含几种不同的值，请使用ENUM而不是VARCHAR。例如，它可以是名为“status”的列，并且只包含诸如“active”，“inactive”，“pending”，“expired”等的值... |
10. 使用PROCEDURE ANALYSE()获取建议
|
11. 如果可以的话使用NOT NULL
|
12. 预处理语句
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
// create a prepared statementif ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { // bind parameters $stmt->bind_param("s", $state); // execute $stmt->execute(); // bind result variables $stmt->bind_result($username); // fetch value $stmt->fetch(); printf("%s is from %s\n", $username, $state); $stmt->close();} |
13. 无缓冲查询
|
14. 使用 UNSIGNED INT 存储IP地址
|
|
1
|
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id"; |
15. 固定长度（静态）的表会更快
|
16. 垂直分区
|
17. 拆分大型DELETE或INSERT语句
|
|
1
2
3
4
5
6
7
8
9
|
while (1) { mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) { // done deleting break; } // you can even pause a bit usleep(50000);} |
18. 越小的列越快
|
20. 使用对象关系映射器（ORM, Object Relational Mapper）
|
21. 小心使用持久连接
|
