MySQL优化技术——20条建议

 

MySQL优化

 

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

 

MySQL scripts and utilities on Envato Market

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

}

18.较小的列更快

对于数据库引擎,磁盘可能是最重要的瓶颈。保持更小和更紧凑的东西通常在性能上是有帮助的,以减少磁盘传输的数量。

MySQL文档有一个列表存储要求对于所有数据类型。

如果表的行数非常少,则没有理由使主键成为int,而不是MEDIUMINT、SMALLINT,甚至在某些情况下是TINYINT。如果不需要时间组件,请使用日期而不是日期。

你一定要留出合理的空间来成长,否则你可能会像斯拉什多.

19.选择正确的存储引擎

MySQL中的两个主要存储引擎是MyISAM和InnoDB。每个人都有自己的优点和缺点。

MyISAM很适合阅读量大的应用程序,但是当有大量的写操作时,它的扩展就不太好了。即使您正在更新一行中的一个字段,整个表也会被锁定,在该查询完成之前,任何其他进程都无法从中读取。MyISAM在计算SELECT(*)类型查询方面非常快速。

InnoDB往往是一个更复杂的存储引擎,在大多数小型应用程序中可能比MyISAM慢。但是它支持基于行的锁定,这样可以更好地扩展。它还支持一些更高级的特性,如事务。

 

 

20.使用对象关系映射器

通过使用ORM(ObjectRelationalMapper),您可以获得某些性能好处。ORM所能做的一切,也可以手动编码。但这可能意味着过多的额外工作,需要高水平的专业知识。

奥姆的是伟大的“懒散装载”。这意味着它们只能在需要时获取值。但是,您需要小心处理它们,否则您可能会创建到许多可以降低性能的迷你查询。

ORM还可以将您的查询批量处理到事务中,这些事务的操作比向数据库发送单独的查询要快得多。

目前,我最喜欢的PHP ORM是学说。我写了一篇关于如何使用CodeIgniter安装原则.

21.小心持久的连接

持久连接的目的是减少重新创建到MySQL连接的开销。创建持久连接时,即使脚本完成运行,它也将保持打开状态。由于Apache重用它的子进程,所以下次该进程为新脚本运行时,它将重用相同的MySQL连接。

理论上听起来不错。但根据我个人的经验(以及其他许多方面),这些特性最终证明不值得麻烦。在连接限制、内存问题等方面,您可能会遇到严重的问题。

Apache运行非常并行,并创建许多子进程。这是持久连接在此环境中不能很好工作的主要原因。在考虑使用MySQL_pCONNECT()函数之前,请咨询系统管理员。

作者布拉克·古泽尔

文章:https://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855

相关推荐
©️2020 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页