MySQL优化技术——20条建议

最新推荐文章于 2024-08-31 10:36:21 发布

多纤果冻

最新推荐文章于 2024-08-31 10:36:21 发布

阅读量2w

点赞数 8

分类专栏： sql优化

sql优化专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL优化

数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA(数据库管理员)不得不担心这些性能问题。作为程序员，我们需要通过正确地构造表、编写优化的查询和更好的代码来完成我们的工作。在本文中，我将列出一些针对程序员的MySQL优化技术。

1.优化查询缓存的查询

大多数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'");

第一行中查询缓存不能工作的原因是CURDATE()函数的使用。这适用于所有非确定性函数，如Now()和RAND()等.由于函数的返回结果可能会改变，MySQL决定禁用该查询的查询缓存。我们所需要做的就是在查询之前增加一行PHP，以防止这种情况发生。

2.解释您的选择查询

使用解释关键字可以让您深入了解MySQL在执行查询时所做的事情。这可以帮助您发现查询或表结构的瓶颈和其他问题。

解释查询的结果将显示正在使用哪些索引，如何扫描和排序表等。

接受一个SELECT查询(最好是带有联接的复杂查询)，并在前面添加关键字EXPLAIN。您只需使用phpmyadmin就可以了。它会在一张漂亮的桌子上向你展示结果。例如，假设我忘记向列添加索引，而我对该列执行联接：

在将索引添加到group_id字段后：

现在，它将只扫描2个表中的9行和16行，而不是扫描7883行。一个好的经验法则是将“行”列下的所有数字乘以，您的查询性能将在一定程度上与结果数字成正比。

3.获得唯一行时的限制1

有时候，当您查询您的表时，您已经知道您只需要一行。您可能正在获取唯一的记录，也可能只是检查满足WHERE子句的任何数量的记录的存在。

在这种情况下，向查询添加限制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) {

    // ...

}

4.索引搜索字段

索引不仅仅用于主键或唯一键。如果表中有要搜索的列，则几乎应该始终对它们进行索引。

正如您所看到的，此规则也适用于部分字符串搜索，如“姓氏类似于‘a%’”。当从字符串的开头进行搜索时，MySQL能够利用该列上的索引。

您还应该了解哪些类型的搜索不能使用常规索引。例如，在搜索一个单词时(例如，“如果POST_Content像‘%Apple%’一样”)，您将不会从正常的索引中看到任何好处。你最好用MySQL全文搜索或者建立你自己的索引解决方案。

5.为Joins索引并使用相同的列类型

如果应用程序包含许多联接查询，则需要确保在两个表上都对您所连接的列进行索引。这会影响MySQL内部优化联接操作的方式。

此外，被连接的列需要相同的类型。例如，如果将一个十进制列加入到另一个表中的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

6.不要由RAND()命令

这是一开始听起来很酷的技巧之一，很多新手程序员都落入了这个陷阱。您可能没有意识到，一旦在查询中开始使用这种瓶颈，就会造成什么样的瓶颈。

如果你真的需要从你的结果随机行，有更好的方法来做它。当然，这需要额外的代码，但您将防止随着数据的增长，瓶颈会呈指数级恶化。问题是，MySQL必须对表中的每一行执行RAND()操作(这需要处理能力)，然后再对其进行排序，只给您1行。

// 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");

因此，您选择一个小于结果数的随机数，并将其用作限制子句中的偏移量。

7.避免选择*

从表中读取的数据越多，查询就会变得越慢。它会增加磁盘操作所需的时间。此外，当数据库服务器与Web服务器分离时，由于必须在服务器之间传输数据，您将有更长的网络延迟。

在执行SELECT时，总是指定需要哪些列是一个好习惯。

// 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列，它是主键，AUTO_INTION和INT的风格之一。也最好没有签名，因为值不能是负值。

即使您有一个具有唯一用户名字段的User表，也不要将其作为您的主键。作为主键的VARCHAR字段速度较慢。通过在内部引用所有具有id的用户，您的代码中将有一个更好的结构。

还有MySQL引擎本身所做的幕后操作，这些操作在内部使用主键字段。更重要的是，数据库设置越复杂。(集群、分区等)。

该规则的一个可能的例外是“关联表”，用于两个表之间的多到多类型的关联。例如，包含两个列的“post_tag”表：post_id、tag_id，用于两个名为“post”和“tag”的表之间的关系。这些表可以具有包含两个id字段的主键。

9.在VARCHAR上使用ENUM

ENUM类型列非常快速和紧凑。在内部，它们像TINYINT一样存储，但是它们可以包含和显示字符串值。这使他们成为某些领域的完美候选人。

If you have a field, which will contain only a few different kinds of values, use ENUM instead of VARCHAR. For example, it could be a column named "status", and only contain values such as "active", "inactive", "pending", "expired" etc...

甚至还有一种方法可以从MySQL本身获得关于如何重构表的“建议”。当您有一个VARCHAR字段时，它实际上可以建议您将该列类型改为ENUM。这是使用过程Analysis()调用完成的。这使我们想到：

10.用过程分析()获得建议

程序分析()将让MySQL分析表中的列、结构和实际数据，为您提供某些建议。只有当您的表中有实际数据时，它才是有用的，因为这在决策过程中起着很大的作用。

例如，如果您为主键创建了一个int字段，但是没有太多行，它可能建议您使用MEDIUMINT。或者，如果您使用的是VARCHAR字段，如果只有很少的唯一值，则可能会收到将其转换为ENUM的建议。

您还可以通过单击phpmyadmin中的“提议表结构”链接来运行此操作，该链接位于您的一个表视图中。

记住，这些只是建议。如果你的桌子要变大，它们甚至可能不是正确的建议。这个决定最终是你的。

11.如果可以使用NOTNULL

除非您有非常具体的理由使用NULL值，否则应该始终将列设置为NOTNULL。

首先，问问自己，空字符串值和空值(对于int字段：0和NULL)之间是否有什么区别。如果没有理由同时使用这两个字段，则不需要空字段。(您知道Oracle认为空字符串和空字符串是相同的吗？)

空列需要额外的空间，它们可以增加比较语句的复杂性。只要你能避开他们就行了。但是，我知道有些人可能有非常具体的理由使用空值，这并不总是一件坏事。

来自MySQL文档：

“空列需要行中的额外空间来记录它们的值是否为空。对于MyISAM表，每个空列需要额外增加一位，舍入到最近的字节。“

12.准备好的陈述

由于性能和安全方面的原因，使用准备好的语句有多种好处。

准备好的语句将过滤默认情况下绑定到它们的变量，这对于保护应用程序免受SQL注入攻击非常有用。当然，您也可以手动过滤变量，但是这些方法更容易被程序员人为错误和遗忘。在使用某种框架或ORM时，这不是什么问题。

由于我们的重点是业绩，我也要提到这方面的好处。当在应用程序中多次使用相同的查询时，这些好处更显着。您可以为同一个准备好的语句分配不同的值，但是MySQL只需要解析一次。

另外，MySQL的最新版本以本机二进制形式传输准备好的语句，这些语句效率更高，也有助于减少网络延迟。

曾经有一段时间，许多程序员出于一个重要的原因，故意避免准备好的语句。它们没有被MySQL查询缓存。但是在5.1版前后的某个时候，查询缓存也是受支持的。

若要在PHP中使用已准备好的语句，请签出mysqli扩展或者使用数据库抽象层，如PDO.

// create a prepared statement

if ($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.非缓冲查询

通常，当您从脚本执行查询时，它将等待该查询的执行完成，然后才能继续执行。您可以通过使用未缓冲的查询来更改这一点。

PHP文档中对MySQL_unBuffed_query()职能：

“MySQL_unBuffed_query()将SQL查询发送到MySQL，而不像MySQL_Query()那样自动获取和缓冲结果行。这将节省大量的内存，因为SQL查询会产生较大的结果集，而且您可以在检索到第一行之后立即开始处理结果集，因为您不必等到执行完整的SQL查询之后才开始处理结果集。“

然而，它也有一定的局限性。您必须要么读取所有行，要么调用mysql_free_test()然后再执行另一个查询。此外，您也不允许使用mysql_num_row()或mysql_data_find()在结果集上。

14.将ip地址存储为无符号int

许多程序员将创建一个VARCHAR(15)字段，而不知道他们实际上可以将IP地址存储为整数值。使用int时，只剩下4个字节的空间，而不是一个固定大小的字段。

您必须确保您的列是无符号int，因为IP地址使用32位无符号整数的整个范围。

在查询中，可以使用iNet_Aton()若要将和IP转换为整数，以及INET_NTOA()反之亦然。PHP中也有类似的函数ip2long()和长2ip().

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

15.固定长度(静态)表更快。

当表中的每一列都是“固定长度”时，也会考虑表。“静态”或“固定长度”。非固定长度列类型的示例有：VARCHAR、Text、BLOB.如果只包含其中一种类型的列，表就不再是固定长度的，必须由MySQL引擎以不同的方式处理。

固定长度的表可以提高性能，因为MySQL引擎查找记录的速度更快。当它想读取表中的特定行时，它可以快速计算它的位置。如果行大小不是固定的，则每次需要查找时，都必须参考主键索引。

它们在崩溃后也更容易缓存和重建。但它们也可以占用更多的空间。例如，如果您将一个VARCHAR(20)字段转换为Char(20)字段，那么它总是占用20字节的空间，不管它在什么地方。

通过使用“垂直分区”技术，可以将可变长度列分隔到单独的表中。这使我们想到：

16.垂直划分

垂直分区是出于优化的原因，以垂直方式拆分表结构的行为。

例1：您可能有一个包含家庭地址的User表，这些地址不会经常被读取。您可以选择拆分表并将地址信息存储在单独的表中。这样，主用户表就会缩小。如您所知，较小的表现得更快。

例2：您的表中有一个“LEST_LOGIN”字段。每次用户登录到网站时，它都会更新。但是，表上的每次更新都会导致表的查询缓存被刷新。您可以将该字段放入另一个表中，以将对用户表的更新保持在最低限度。

但是，您还需要确保在分区之后不需要经常加入这两个表，否则性能实际上可能会下降。

17.拆分大删除或插入查询

如果您需要在一个活动的网站上执行一个大的删除或插入查询，您需要小心不要干扰网络流量。当执行这样的大型查询时，它可以锁定您的表并使您的Web应用程序停止。

Apache运行许多并行进程/线程。因此，当脚本尽快完成执行时，它的工作效率最高，因此服务器不会同时经历太多的打开连接和进程，这些连接和进程占用了资源，特别是内存。

如果您在高流量的网站上锁定表的任何长时间(比如30秒或更长时间)，则会导致进程和查询堆积，这可能需要很长时间才能清除Web服务器，甚至使其崩溃。

如果您有某种需要删除大量行的维护脚本，只需使用Limit子句在较小的批中执行它，以避免这种拥塞。

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);

}