数据库Mysql优化技巧常用方法
首页 »
PHP » 数据库Mysql优化技巧常用方法
Mysql数据库优化技巧有很多，这里摘抄了一些许多程序员常用的Mysql优化方法，供参考及备忘。
1. MySQL优化WHERE子句
下面以SELECT语句为例，不过DELETE和UPDATE语句中的WHERE子句优化和这里SELECT 语句的Mysql优化技巧一样，下面只列出了一部分的MYSQL 优化方法实例。
减少括号嵌套：
WHERE ((a AND b) AND c OR (((a AND b) AND (c AND d))))
优化-> WHERE (a AND b AND c) OR (a AND b AND c AND d)
常量重叠，去除不必要的常量：
(a<b AND b=c) AND a=5 AND (5=5)
优化-> b>5 AND b=c AND a=5
如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合 并。所有常数的表在查询中比其它表先读出。常数表为：1. 空表或只有1行的表; 2. 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引 部分使用常数表达式并且索引部分被定义为NOT NULL。
2. Mysql 优化避免SELECT *命令
从表中读取越多的数据，查询会变得更慢。始终指定你需要的列，这是一个非常良好的习惯。
-
- $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
-
- $d = mysql_fetch_assoc($r);
-
- echo "Welcome {$d['username']}";
-
- // 优化如下:
-
- $r = mysql_query("SELECT username FROM user WHERE user_id = 1");
-
- $d = mysql_fetch_assoc($r);
-
- echo "Welcome {$d['username']}";
3. MySQL 如何优化DISTINCT 语句
在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。例如，下面的两个数据库查询是等效的：
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
由于这个等效性，适用于GROUP BY查询的优化技巧和方法也适用于有DISTINCT子句的查询。
4. 优化MySQL查询缓存
MySQL查询可以启用高速查询缓存。这是提高数据库性能的有效Mysql优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多，小小的Mysql优化技巧。
-
- // query cache does NOT work
-
- $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
-
- // query cache works!
-
- $today = date("Y-m-d");
-
- $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
-
- // query cache does NOT work
-
- $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
-
- // query cache works!
-
- $today = date("Y-m-d");
-
- $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
5. Mysql 优化：用EXPLAIN优化SELECT查询
使用EXPLAIN关键字是另一个MySQL优化技巧，了解MySQL正在进行什么样的数据库查询操作。
实现一个SELECT查询（最好是比较复杂的一个，带joins方式的），在里面添加上你的关键词解释，在这里我们可以使用phpMyAdmin， 他会告诉你表中的结果。举例来说，假如当我在执行joins时，正忘记往一个数据库索引中添加列，EXPLAIN能帮助我找到问题的所在。
MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。下面看看ORDER BY 优化技巧和方法。
即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数，就可以使用索引。在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然行使用索引来找到匹配WHERE子句。如：
对不同的关键字使用ORDER BY：SELECT * FROM t1 ORDER BY key1, key2；
对关键字的非连续元素使用ORDER BY：SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；
混合ASC和DESC：SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
用于查询行的关键字与ORDER BY中所使用的不相同：SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
联接许多表，并且ORDER BY中的列并不是全部来自第1个 用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联 接类型的第1个表）。
有不同的ORDER BY和GROUP BY表达式。
使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索 引情况即如此。
通过EXPLAIN SELECT …ORDER BY，可以检查MySQL是否可以使用索引来解决查询。
文件排序优化不仅用于记录排序关键字和行的位置，并且还记录数据库查询需要的列。这样可以避免两次读取行。
7. Mysql 优化：使用LIMIT 1取得唯一行
有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的 WHERE子句。增加一个LIMIT 1会令你的查询更加有效，这个方法是Mysql优化很有效的一个技巧。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。
-
- $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'")
-
- $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
8. MySQL如何优化 LIMIT
使用LIMIT row_count而不使用HAVING时，MySQL将 以不同方式处理数据库查询。
如果你用LIMIT只选择一些行，当MySQL选 择做完整的表扫描时，它将在一些情况下使用索引，涉及到Mysql索引优化。
如果你使用LIMIT row_count与ORDER BY，MySQL一旦找到了排序结果的第一个row_count行， 将结束排序而不是排序整个表。如果使用索引，将很快。如果必须进行文件排序（filesort），必须选择所有匹配查询没有LIMIT子 句的行，并且在确定已经找到第1个row_count行 前，必须对它们的大部分进行排序。在任何一种情况下，一旦找到了行，则不需要再排序结果的其它部分，并且MySQL不再进行排 序。
当结合LIMIT row_count和DISTINCT时，MySQL一旦找到row_count个 唯一的行，它将停止。
在一些情况下，GROUP BY能通过顺序读取键(或在键上做排序)来解决，然后计算摘要直到关键字的值 改变。在这种情况下，LIMIT row_count将不计算任何不必要的GROUP BY值。
只要MySQL已经发送了需要的行数到客户，它将放弃查询，除非你正使用SQL_CALC_FOUND_ROWS。
LIMIT 0将总是快速返回一个空集合。这对检查数据库查询的有效性是有用的。当使用MySQL API时，它也可以用来得到结果列的列类型。(该mysql优化技巧在MySQL Monitor中不工作，只显示Empty set；应使用SHOW COLUMNS或DESCRIBE）。
当服务器使用临时表来进行查询时，使用LIMIT row_count子句来计算需要多少空间。
9. Mysql 优化：保证连接的索引是相同的类型
如果应用程序中包含多个连接查询，你需要确保你链接的列在两边的数据库表上都被索引。这会影响MySQL如何优化内部联接操作。此外，加入的列，必须是同一类型。例如，你加入一个DECIMAL列，而同时加入另一个表中的int列，MySQL将无法使用其中至少一个 指标。这种方法即使字符编码必须同为字符串类型。
- // 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
10. Mysql 优化避免使用BY RAND()命令
若需要随机显示结果，MySQL可能 会为表中每一个独立的行执行数据库BY RAND()命令（这会消耗处理器的处理能力，不利于Mysql数据库优化），这种方法然后给你仅返回一行。
-
- $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
-
- // Mysql 优化如下:
-
- $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");
11. Mysql 优化变量类型：将IP地址存储为无符号整型
程序员经常在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储，没有考虑到Mysql优化的问题。当你有一个 INT类型时，你只占用4个字 节的空间，这是一个固定大小的领域。确定所操作的数据库列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。
12. MySQL如何优化LEFT JOIN和RIGHT JOIN
在MySQL中，LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接数据库Mysql优化器更快地工作，因为检查的表交换更少。
SELECT *
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
MySQL可以进行下面方法的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接。
例如，在下面的查询中如果t2.column1为NULL，WHERE 子句将为false：
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
因此，可以安全地将查询转换为普通联接：
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
这样可以更快，因为如果可以使查询更佳，MySQL可以在表t1之前使用表t2。 为了强制使用数据库表顺序，使用STRAIGHT_JOIN。
13. MySQL如何优化嵌套Join
在MySQL数据库中，CROSS JOIN语法上等价于INNER JOIN (它们可以彼此代替。在标准SQL中，它们不等价。INNER JOIN结合ON子句使用；CROSS JOIN 用于其它地方。
总的来说，在只包含内部联接操作的联接表达式中可以忽略括号。删除括号并将操作组合到左侧后，联接表达式：
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
ON t1.a=t2.a
转换为表达式：
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL
但是这两个表达式不等效。不能忽视左外联接操作的右操作数和右联接操作的左操作数中的括号。也就是不能忽视外联接操作中的内表达式中的括号。可以忽视其它 操作数中的括号(外部表的操作数)。
对于任何表t1、t2、t3和 属性t2.b和t3.b的任何条件P，下面的表达式：
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
等价于表达式
t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b)
MySQL数据库对于只包含内联接(而非外联接)的联接表达式，可以删除括号。 你可以移除括号并从左到右评估(或实际上，你可以按任何顺序评估表)。对外联接却不是这样。去除括号可能会更改结果。对外联接和内联接的结合，也不是这样。去除括号可能会更改结果。
满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如 果有)。在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。数据库Mysql索引优化是数据库优化技巧常用的重要方法。
为GROUP BY使用数据库索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性，并且索引按顺序保存其关键字(例如，这是B-树索 引，而不是HASH索引)。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、 为该部分指定的条件，以及选择的累积函数。有两种方法通过索引访问执行GROUP BY查询，组合操作结合所有范围判断式使用(如果有)。第2个方法首先执行范围扫描，然后组合结果元组。
相关项目
