20多个MySQL最佳实践与优化

本文地址：https://code.tutsplus.com/tutorials/top-20-mysql-best-practices--net-7855

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

在开始之前，请注意，您可以在Envato Market上找到大量有用的MySQL脚本和实用程序。

Envato市场上的MySQL脚本和实用程序

1.优化查询对于查询缓存

大多数MySQL服务器都启用了查询缓存。这是提高性能的最有效方法之一，由数据库引擎静静地处理。当多次执行相同的查询时，结果将从缓存中获取，这相当快。

主要的问题是，程序员很容易和隐藏，我们大多数人往往忽视它。我们所做的一些事情实际上可以阻止查询缓存执行任务。

1 2 3 4 五 6

// 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.解释你的SELECT查询

使用EXPLAIN关键字可以让你了解MySQL正在做什么来执行你的查询。这可以帮助您发现查询或表结构的瓶颈和其他问题。

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

采取一个SELECT查询（最好是一个复杂的，与连接），并在其前面添加关键字EXPLAIN。你可以使用这个phpmyadmin。它会显示在一个不错的表格中的结果。例如，假设我忘记将索引添加到我执行连接的列中：

将索引添加到group_id字段后：

现在不是扫描7883行，而是只扫描2个表中的9行和16行。一个好的经验法则是将“行”列下的所有数字相乘，并且查询性能将与结果数量成比例。

3.获取唯一行时限制1

有时当你在查询你的桌子时，你已经知道你正在寻找一行。您可能正在获取唯一记录，或者您可能只是检查是否存在满足WHERE子句的任意数量的记录。

在这种情况下，将LIMIT 1添加到查询中可以提高性能。这样数据库引擎就会在找到1后停止扫描记录，而不是通过整个表或索引。

01 02 03 04 05 06 07 08 09 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.索引和使用相同的列类型的联接

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

而且，连接的列也必须是相同的类型。例如，如果您将DECIMAL列加入到另一个表的INT列中，则MySQL将无法使用至少一个索引。即使字符编码也需要是字符串类型列的相同类型。

1 2 3 4 五 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.不要按兰德（）

这是一开始听起来很酷的技巧之一，许多新手程序员都陷入了这个陷阱。一旦你开始在查询中使用它，你可能不会意识到你可以创建什么样的可怕的瓶颈。

如果你真的需要结果中的随机行，有更好的方法来做到这一点。当然，这需要额外的代码，但是随着数据的增长，您将会避免一个瓶颈，而这个瓶颈会呈指数级地恶化。问题是，在排序它之前，MySQL将不得不对表中的每一行执行RAND（）操作（这需要处理能力），并给你一行。

01 02 03 04 05 06 07 08 09 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 *

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

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

01 02 03 04 05 06 07 08 09 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引擎本身也有幕后操作，在内部使用主键字段。哪个变得更重要，数据库设置就越复杂。（集群，分区等）。

规则的一个可能的例外是“关联表”，用于2个表之间的多对多关联类型。例如，“posts_tags”表包含两列：post_id，tag_id，用于名为“post”和“tags”的两个表之间的关系。这些表可以有一个包含两个id字段的PRIMARY键。

9.在VARCHAR上使用ENUM

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

如果您有一个只包含几种不同类型值的字段，请使用ENUM而不是VARCHAR。例如，它可能是一个名为“status”的列，只包含“active”，“inactive”，“pending”，“expired”等值。

甚至有一种方法可以从MySQL本身获得关于如何重组表的一个“建议”。当你有一个VARCHAR字段，它实际上可以建议你改变列的类型为ENUM。这使用PROCEDURE ANALYZE（）调用完成。这使我们能够：

10.使用PROCEDURE ANALYZE（）获取建议

PROCEDURE ANALYZE（）将让MySQL分析表结构和表中的实际数据，为您提供一些建议。只有在表格中存在实际数据时才有用，因为这在决策中扮演着重要的角色。

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

您也可以通过单击phpmyadmin中的“建议表结构”链接来运行该表。

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

11.如果可以的话，使用NOT NULL

除非你有一个非常具体的理由来使用NULL值，你应该总是把你的列设置为NOT NULL。

首先，问问自己空字符串值与空值是否有区别（对于INT字段：0与NULL）。如果没有理由同时拥有，则不需要NULL字段。（你知道Oracle认为NULL和空字符串是相同的吗？）

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

从MySQL文档：

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

12.准备好的陈述

出于性能和安全原因，使用预准备语句有多个好处。

Prepared Statements将默认过滤与其绑定的变量，这对保护您的应用程序免受SQL注入攻击非常有用。你当然也可以手动过滤你的变量，但是这些方法更容易被程序员误解和遗忘。这在使用某种框架或ORM时不是什么问题。

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

最新版本的MySQL以本地二进制形式传输准备好的语句，这些语句更高效，还可以帮助减少网络延迟。

有一段时间，许多程序员习惯于避免准备好陈述，这只是一个重要原因。他们没有被MySQL查询缓存缓存。但是从5.1版本开始，也支持查询缓存。

要在PHP中使用准备好的语句，请检查mysqli扩展名或使用PDO等数据库抽象层。

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19

// 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_unbuffered_query（）函数：

“mysql_unbuffered_query（）将SQL查询查询发送到MySQL，而不会像mysql_query（）那样自动获取和缓冲结果行。这样可以节省大量的内存，使用SQL查询生成大的结果集，您可以开始处理结果集在第一行被取回之后立即执行，因为您不必等待完整的SQL查询执行完毕。

但是，它具有一定的局限性。您必须读取所有行或调用mysql_free_result（）才能执行另一个查询。此外，您不允许在结果集上使用mysql_num_rows（）或mysql_data_seek（）。

14.将 IP地址存储为UNSIGNED INT

许多程序员将创建一个VARCHAR（15）字段，而不会意识到它们实际上可以将IP地址存储为整数值。有了INT，你只需要下降到4个字节的空间，而且有一个固定大小的字段。

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

在你的查询中，你可以使用INET_ATON（）将IP转换为一个整数，而INET_NTOA（）则可以使用INET_NTOA（）。PHP中也有类似的函数，称为ip2long（）和long2ip（）。

1	$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：您可能有一个包含家庭地址的用户表，这些表不经常读取。您可以选择拆分表格并将地址信息存储在单独的表格中。这样你的主要用户表就会缩小。如你所知，较小的表格执行速度更快。

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

但是你也需要确保在分区之后你不需要经常加入这两个表，否则你的性能可能会下降。

17.拆分Big DELETE或INSERT查询

如果您需要在实时网站上执行大的DELETE或INSERT查询，则需要注意不要打扰网络流量。当执行这样一个大的查询时，它可以锁定你的表，并停止你的web应用程序。

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

如果您最终锁定表的时间过长（如30秒或更长时间），则在高流量的网站上，您将导致进程和查询堆积，这可能需要很长时间才能清除，甚至导致网页崩溃服务器。

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

1 2 3 4 五 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.小列更快

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

MySQL文档具有所有数据类型的存储要求列表。

如果一个表预计只有很少的行，那么没有理由将主键设置为INT，而不是MEDIUMINT，SMALLINT，甚至在某些情况下是TINYINT。如果您不需要时间组件，请使用DATE而不是DATETIME。

只要确保你留下合理的空间来增长，或者你最终可能会像Slashdot一样。

19.选择正确的存储引擎

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

MyISAM适用于读取繁重的应用程序，但在写入很多时不能很好地扩展。即使您更新一行的一个字段，整个表也会被锁定，并且在查询完成之前，其他任何进程都不能读取。MyISAM在计算SELECT COUNT（*）类型的查询时速度非常快。

InnoDB往往是一个更复杂的存储引擎，对于大多数小型应用程序，它可能比MyISAM慢。但它支持基于行的锁定，这种锁定更好。它还支持一些更高级的功能，如交易。

• MyISAM存储引擎
• InnoDB存储引擎

广告

20.使用对象关系映射器

通过使用ORM（对象关系映射器），您可以获得一定的性能优势。ORM所能做的一切，都可以手动编码。但这可能意味着太多的额外工作，需要高水平的专业知识。

ORM对于“懒加载”非常有用。这意味着它们只能在需要的时候获取值。但是，您需要小心，否则最终可能会创建许多可以降低性能的迷你查询。

ORM还可以将查询分批处理到事务中，这比将单个查询发送到数据库要快得多。

目前我最喜欢的PHP的ORM是教义。我写了一篇关于如何使用CodeIgniter安装Doctrine的文章。

21.小心持久连接

持久连接意味着减少重新连接到MySQL的开销。当创建一个持久连接时，即使脚本运行完成，它仍然保持打开状态。由于Apache重用了它的子进程，所以下次进程运行一个新脚本时，它将重用相同的MySQL连接。

• PHP中的mysql_pconnect（）

理论上听起来不错。但是从我个人的经验（以及其他许多方面）来看，这个功能是不值得的。存在连接限制，内存问题等严重问题。

Apache运行非常平行，并创建了许多子进程。这是持续性连接在这种环境下不能很好地工作的主要原因。在考虑使用mysql_pconnect（）函数之前，请咨询您的系统管理员。