YiluPHP
这家伙很懒，什么都没有留下...
获赞
32

经验 MySQL优化：SQL优化

浏览数 201897
  1. 对象关键性语句，要使用索引进行查询
    写完SQL语句后，使用explain分析一下这条语句有没有使用到索引

  2. 分页查询的优化
    MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要的，所以n越大，性能会越差。
    一般的分页做法，测试耗时 10.961s
    SELECT * FROM v_history_data  LIMIT 5000000, 10

    优化后，测试耗时 1.943s
    SELECT * FROM v_history_data INNER JOIN (SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid)

    优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

  3. 使用连接（JOIN）来代替子查询(Sub-Queries)
    MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
    DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
    使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
    SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
    如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
    SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL
    连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

  4. 使用联合(UNION)来代替手动创建的临时表
    MySQL 从 4.0 的版本开始支持 UNION 查询，它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。
    SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author
    UNION
    SELECT Name, Supplier FROM product

  5. 使用事务
    尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN 关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
    BEGIN;
    INSERT INTO salesinfo SET CustomerID=14;
    UPDATE inventory SET Quantity=11
    WHERE item='book';
    COMMIT;
    事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

  6. 锁定表
    尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。 其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
    LOCK TABLE inventory WRITE
    SELECT Quantity FROM inve

  7. 优化的查询语句
    绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。首先，最好是在相同类型的字段间进行比较的操作。在MySQL 3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和 VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。其次，在建有索引的字段上尽量不要使用函数进行操作。
    例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
    SELECT * FROM order WHERE YEAR(OrderDate)<2001;
    SELECT * FROM order WHERE OrderDate<"2001-01-01";
    同样的情形也会发生在对数值型字段进行计算的时候：
    SELECT * FROM inventory WHERE Amount/7<24;
    SELECT * FROM inventory WHERE Amount<24*7;
    上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。第三，在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。例如下面的查询将会比较表中的每一条记录。
    SELECT * FROM books WHERE name like "MySQL%"
    但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
    SELECT * FROM books WHERE name>="MySQL"and name<"MySQM"

    最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

  8. 优化你的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'");

  9. 用EXPLAIN使你的SELECT查询更加清晰
    使用EXPLAIN关键字是另一个MySQL优化技巧，可以让你了解MySQL正在进行什么样的查询操作，这可以帮助你发现瓶颈的所在，并显示出查询或表结构在哪里出了问题。
    EXPLAIN查询的结果，可以告诉你那些索引正在被引用，表是如何被扫描和排序的等等。
    实现一个SELECT查询（最好是比较复杂的一个，带joins方式的），在里面添加上你的关键词解释，在这里我们可以使用phpMyAdmin，他会告诉你表中的结果。举例来说，假如当我在执行joins时，正忘记往一个索引中添加列，EXPLAIN能帮助我找到问题的所在。

  10. 利用LIMIT 1取得唯一行
    有时，当你要查询一张表是，你知道自己只需要看一行。你可能会去的一条十分独特的记录，或者只是刚好检查了任何存在的记录数，他们都满足了你的WHERE子句。
    在这种情况下，增加一个LIMIT 1会令你的查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。
    // 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) {
    // ...
    }

  11. 索引中的检索字段
    索引不仅是主键或唯一键。如果你想搜索表中的任何列，你应该一直指向索引。

  12. 保证连接的索引是相同的类型
    如果应用程序中包含多个连接查询，你需要确保你链接的列在两边的表上都被索引。这会影响MySQL如何优化内部联接操作。
    此外，加入的列，必须是同一类型。例如，你加入一个DECIMAL列，而同时加入另一个表中的int列，MySQL将无法使用其中至少一个指标。即使字符编码必须同为字符串类型。
    1. // looking for companies in my state
    2. $r = mysql_query("SELECT company_name FROM users
    3. LEFT JOIN companies ON (users.state = companies.state)
    4. WHERE users.id = $user_id");
    5.
    6. // both state columns should be indexed
    7. // and they both should be the same type and character encoding
    8. // or MySQL might do full table scans

  13. 不要使用BY RAND()命令
    这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
    如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。
    1. // what NOT to do:
    2. $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
    3. // much better:
    4. $r = mysql_query("SELECT count(*) FROM user");
    5. $d = mysql_fetch_row($r);
    6. $rand = mt_rand(0,$d[0] - 1);
    7.
    8. $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

  14. 尽量避免SELECT *命令
    从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
    始终指定你需要的列，这是一个非常良好的习惯。
    1. // not preferred
    2. $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
    3. $d = mysql_fetch_assoc($r);
    4. echo "Welcome {$d['username']}";
    5. // better:
    6. $r = mysql_query("SELECT username FROM user WHERE user_id = 1");
    7. $d = mysql_fetch_assoc($r);
    8. echo "Welcome {$d['username']}";
    9. // the differences are more significant with bigger result sets

  15. 从PROCEDURE ANALYSE()中获得建议
    PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。


    MySQL数据库十大优化技巧

  16. 准备好的语句
    准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
    准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。
    1. // create a prepared statement
    2. if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
    3. // bind parameters
    4. $stmt->bind_param("s", $state);
    5. // execute
    6. $stmt->execute();
    7. // bind result variables
    8. $stmt->bind_result($username);
    9. // fetch value
    10. $stmt->fetch();
    11. printf("%s is from %s\n", $username, $state);
    12. $stmt->close();
    13. }

  17. 将IP地址存储为无符号整型
    许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
    你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。
    1. $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
我来说说