MySQL性能优化21个最佳实践 和使用索引

21 MySQL 性能优化最佳实践 使用索引的数据库操作日益成为整个应用程序的性能瓶颈，尤其是Web 应用程序。当谈到数据库性能时，这不仅仅是数据库管理员需要担心的事情，也是我们程序员需要警惕的事情。在设计数据库表结构和操作数据库时（尤其是查表时的SQL语句），需要关注数据操作的性能。这里我们不会过多谈论优化SQL语句，我们只关注MySQL这个Web应用最多的数据库。我希望以下优化技巧对您有用。

  1. 优化查询以实现查询缓存

大多数 MySQL 服务器都启用了查询缓存。这是提高性能最有效的方法之一，由 MySQL 数据库引擎处理。如果多次执行相同的查询，则这些查询结果会被缓存，以便后续相同的查询不需要操作表，而是直接访问缓存的结果。

这里的主要问题是程序员很容易忽视这件事。因为我们的一些查询语句使得MySQL没有使用缓存。请看下面的例子：

上面两条SQL语句的区别在于CURDATE()。 MySQL 的查询缓存对此功能不起作用。因此，像 NOW() 和 RAND() 这样的 SQL 函数或其他类似函数不允许查询缓存，因为这些函数的返回结果是不稳定的。所以你所要做的就是用一个变量替换 MySQL 函数来启用缓存。

  2. 解释 SELECT 查询

使用 EXPLAIN 关键字了解 MySQL 如何处理 SQL 语句。这可以帮助分析查询语句或表结构中的性能瓶颈。

EXPLAIN查询结果还告诉你主索引键是如何使用的，数据表是如何搜索和排序的......等等。 ETC。

选择一个 SELECT 语句（建议用于具有多个表连接的最复杂的语句）并将 EXPLAIN 关键字添加到开头。您可以为此使用 phpmyadmin。然后会出现一个表格。在下面的例子中，我们忘记添加group_id索引并创建了表连接：

将索引添加到group_id字段后：

我们可以看到之前的结果显示搜索了7883行，然后一种是表 9 和第 16 行中的 Searched。如果我们查看行列，我们可以发现潜在的性能问题。

3. 当只有一行数据时，请使用 LIMIT 1。

有时在查询表时，您已经知道结果将只是一个结果，但因为您可能需要调用游标或值得检查返回的记录数量。

在这种情况下，添加 LIMIT 1 可以提高性能。这样，MySQL数据库引擎在找到一条记录后就停止搜索，而不是继续搜索下一条与该记录匹配的数据。

下面的例子只是为了看看是否有“中国”用户。显然，后者会比前者更有效。 （请注意，第一个元素是Select *，第二个元素是Select 1）

4. 为搜索字段创建索引

索引并不一定意味着主键或唯一字段。如果表中有一个字段是您经常要搜索的，请为其创建索引。

上图显示了搜索字符串“last_name LIKE ‘a%’”。一个已索引，另一个未索引。性能大约差 4 倍。

此外，您需要知道哪些搜索不能与普通索引一起使用。例如，如果您需要搜索大型文章中的单个单词，例如“WHERE post_content LIKE '%apple%'”，则索引可能没有意义。如果您的应用程序有许多 JOIN 查询，并且您需要确保 Join 字段在两个表中都建立了索引。这样，MySQL内部就启动了一种机制来优化Join SQL语句。

此外，用于连接的字段必须属于同一类型。例如：如果将 DECIMAL 字段与 INT 字段连接起来，MySQL 就无法使用它们的索引。这些 STRING 类型还必须具有相同的字符集。 （两个表的字符集可能不同）

   6.切勿ORDER BY RAND()

是否要打断返回的数据行？你随机选择一个数据吗？我真的不知道是谁发明了这个用法，但是很多新手都喜欢这样使用。但你真的不明白这有多么可怕的性能问题。

如果你确实想混合返回的数据行，可以使用N方法来实现。如果使用这个，数据库的性能只会呈指数下降。这里的问题是：MySQL需要执行RAND()函数（该函数非常消耗CPU），即记录每行记录的行，然后对它们进行排序。即使使用 Limit 1 也无济于事（因为需要排序）

下面的例子是随机选择一条记录

7. 避免 SELECT *

数据库读取的数据越多，查询会变慢。另外，如果数据库服务器和WEB服务器是两个独立的服务器，也会增加网络传输负载。

所以你要养成一个好习惯，该拿什么就拿什么。

  8. 始终为每个表设置一个标识符

对于数据库中的每个表，设置一个标识符作为主键，最好是 INT 类型（推荐 UNSIGNED），并设置自增标志 AUTO_INCREMENT。

即使用户表有一个名为“电子邮件”的主键字段，它也不应该是主键。使用 VARCHAR 类型作为主键会降低性能。此外，您必须在程序中使用表标识符来构建数据结构。

另外，MySQL数据引擎下有一些操作需要使用主键。在这些情况下，主键的性能和设置就变得非常重要，比如集群、分区……

这里只有一种情况。例外的是“关联表”的“外键”，这意味着该表的主键由几个唯一表的主键组成。这种情况称为“外键”。例如：我有一个包含学生 ID 的“学生表”和一个包含课程 ID 的“课程表”。那么“成绩表”就是一个“关联表”，将学生表和课程表关联起来。在积分表中，学生ID和课程ID称为“外键”，共同构成主键。

9. 使用 ENUM 代替 VARCHAR

ENUM 类型非常快速且紧凑。它实际上有一个 TINYINT ，但它显示为一个字符串。这样，使用字段创建选项列表就变得相当完美了。

如果您有“性别”、“国家/地区”、“种族”、“身份”或“类别”等字段，并且您知道这些字段的值是有限且固定的，则应该使用 ENUM 而不是不是 VARCHAR。

MySQL 还有一个“建议”（参见#10）告诉你如何重新组织你的表结构。如果您有 VARCHAR 字段，此建议建议将其更改为 ENUM 类型。您可以使用 PROCEDURE ANALYSE() 来获取相关建议。

  10. 请求 PROCEDURE ANALYSE() 的建议。

PROCEDURE ANALYSE()帮助MySQL分析字段及其实际数据，并提供有用的建议。仅当您的电子表格中有实际数据时，这些建议才有用，因为一些重大决策需要数据。

例如，如果您创建一个 INT 字段作为主键，但没有太多数据，PROCEDURE ANALYSE() 建议将该字段的类型更改为 MEDIUMINT。或者，如果您使用 VARCHAR 字段，您可能会收到将其更改为 ENUM 的建议，因为数据不多。这些建议是可能的，因为没有足够的数据，所以决策不够准确。

在 phpmyadmin 应用程序中，您可以在查看表格时通过单击“建议表格结构”来查看这些建议。

请记住，这些只是建议。这些建议仅在表中数据越来越多时使用。将会是准确的。永远记住，最终决定权在你手中。

  11. 尽可能使用 NOT NULL。

除非您有特定原因使用 NULL 值，否则请始终保留非 NULL 字段。这看起来可能有点争议，请阅读一下。

首先问问自己“Empty”和“NULL”（如果是INT则0和NULL）有什么区别？如果您觉得它们之间没有区别，那么就不要使用 NULL。 （你知道吗？在Oracle中，NULL和空字符串是一样的！）

不要以为NULL不需要空间，它需要额外的空间，相比之下程序会更复杂。当然，这并不意味着不能使用NULL。现实情况非常复杂，仍然存在需要使用 NULL 值的情况。

    12. 准备语句

准备语句与存储过程类似。这些是在后台运行的 SQL 语句的集合。无论是性能还是安全问题，使用准备好的语句都有很多好处。

Prepared语句可以检查一些绑定变量，这可以保护你的程序免受“SQL注入”攻击。当然，您也可以手动检查这些变量。但人工检查很容易出现问题，而程序员经常忘记。如果我们使用一些框架或者ORM，这个问题会好一些。

在性能方面，当多次使用同一查询时，这可以具有显着的性能优势。您可以为这些准备好的语句提供一些参数，MySQL 只会解析它们一次。

虽然最新版本的MySQL在传输prepared statements时使用的是二进制格式，但这使得网络传输非常高效。

当然，在某些情况下我们应该避免使用准备好的语句，因为它们不支持查询缓存。但他们说5.1版本之后就支持了。

要在 PHP 中使用准备好的语句，请查看您的手册：mysqli 扩展或使用数据库抽象层，例如 PDO。

  13. 无缓冲查询

一般情况下，当您在脚本中执行 SQL 语句时，程序将停止，直到返回SQL语句，然后程序将继续运行。您可以使用无缓冲查询来更改行为。

mysql_unbuffered_query() 向 MySQL 发送 SQL 语句，而不像 mysql_query() 那样自动检索和缓存结果。这可以节省大量内存，特别是对于生成大量结果的查询，并且您不必等待所有结果返回。你只需要返回第一行数据就可以立即开始工作。查询结果已经到了。

但是，这有一定的局限性。因为要么必须读取所有行，要么必须调用 mysql_free_result() 在下一个查询之前清除结果。此外，mysql_num_rows() 或 mysql_data_seek() 将不起作用。因此，您应该仔细考虑是否使用无缓冲查询。

  14. 将 IP 地址保存为 NOT SIGNED INT。如果使用整数来存储，它只需要4个字节，并且可以有固定长度的字段。更重要的是，这在你的查询中具有优势，特别是当你需要使用WHERE条件时，例如：ip1和ip2之间的IP。

我们需要使用UNSIGNED INT，因为IP地址使用完整的32位无符号整数。

查询时，可以使用INET_ATON()将字符串IP地址转换为整数，使用INET_NTOA()将整数转换为字符串IP地址。 PHP中也有这样的函数：ip2long()和long2ip()。

  15. 固定长度表速度更快

如果表中所有字段都是“固定长度”，那么整个表就被认为是“静态”或“固定长度”。例如，表没有以下类型的字段：VARCHAR、TEXT、BLOB。只要包含这些字段之一，该表就不再是“定长静态表”，并且由 MySQL 引擎进行不同的处理。

固定长度表可以提高性能，因为 MySQL 的搜索速度更快。由于这些固定的长度可以更容易计算出下一个数据的偏移量，读取自然会更快。而如果该字段不是固定长度，那么每次要查找下一个字段时，程序都必须查找主键。

固定长度的表也更容易缓存和重建。然而，唯一的副作用是固定长度字段浪费了一些空间，因为无论您使用与否，固定长度字段都会占用大量空间。

使用“垂直分割”技术（参见下一点），您可以将表格分为两部分，一个固定长度，一个可变长度。

   16.垂直分区

“垂直分区”是一种将数据库表按列划分为多个表的方法，可以降低表的复杂度和字段数量，从而达到优化目标。 （以前在银行做项目，看到一张表有100多个字段，吓人）

1。示例：用户表有一个字段是家庭地址。该字段是可选的。与 、 相比，在管理数据库时，除个人数据外，不需要经常读取或重写该字段。那么为什么不把它放在另一个表中呢？这使您的桌面性能更好。试想，通常用户表只有我的用户ID、用户名和密码。 、用户角色等会经常使用。较小的表总是会表现更好。

2。示例：您有一个名为“last_login”的字段，每次用户登录时都会更新该字段。但是，每次更新都会导致表的查询缓存被清除。因此，你可以把这个字段放在另一个表中，这样就不会影响用户ID、用户名和用户角色的连续读取，因为查询缓存对性能的提升有很大帮助。

此外，请注意不要频繁连接由这些单独字段组成的表。否则的话，性能会比不拆分的情况更差，这是一种极端的下降。

  17. 破解大型 DELETE 或 INSERT 语句

如果您需要在在线站点上执行大型 DELETE 或 INSERT 查询，则需要非常小心，该操作不会导致整个站点瘫痪。合适的。因为这两个操作都会锁定表，如果表被锁定，其他操作就无法进入。

Apache 将有许多子进程或线程。因此它的工作效率非常高，我们的服务器不需要太多的子进程、线程和数据库引用。这会占用大量的服务器资源，尤其是内存。

如果锁表一定时间，比如说30秒，那么对于一个高流量的网站来说，这30秒内积累的访问进程/线程数、数据库链接数、打开文件数不仅会导致WEB服务崩溃，但整个服务器立即崩溃也会导致其停止。

所以如果你有一个很大的流程并且你确定要拆分它，那么使用 LIMIT 条件是一个好方法。下面是一个示例：

  18. 列越小，速度越快

硬盘操作可能是大多数数据库引擎最重要的瓶颈。因此数据压缩在这种情况下非常有用，因为它减少了硬盘访问。

有关所有数据类型，请参阅 MySQL 存储要求文档。

如果一个表只有几列（比如字典表、配置表），没有理由使用INT作为主键。使用 MEDIUMINT、SMALLINT 或更小的 TINYINT 会更经济。如果您不需要跟踪时间，DATE 比 DATETIME 好得多。

当然，你也需要留有足够的扩展空间，否则以后这样做你会死得很惨。请参阅 Slashdot 的示例（2009 年 11 月 6 日），一个简单的 ALTER TABLE 语句花费了 3 个小时以上，因为有 1600 万条记录。

  19. 选择正确的存储引擎

MySQL中有两种存储引擎，MyISAM和InnoDB。每个引擎都有优点和缺点。酷壳之前的文章《MySQL: InnoDB 还是 MyISAM?》讨论过这个。

MyISAM 适合一些需要大量查询的应用，但对于很多写操作来说不太好。即使你只需要更新一个字段，整个表也会被锁定，其他进程，甚至读进程，在读操作完成之前都无法工作。此外，MyISAM 对于 SELECT COUNT(*) 等计算速度非常快。

InnoDB 正在成为一种非常复杂的存储引擎，对于一些较小的应用程序来说，它会比 MyISAM 慢。另一个原因是它支持“队列锁定”，所以写操作多一些就更好了。它还支持更高级的应用程序，例如交易。

以下是MySQL手册

target=”_blank”MyISAM存储引擎

InnoDB存储引擎

20.使用对象关系映射器（对象关系映射器（Object Relational Mapper）（对象关系映射器）（对象关系映射器) 关系映射器) ) )，可以实现可靠的性能提升。 ORM 能做的一切都可以手动编写。然而，这需要高水平的专家。

ORM 最重要的是“延迟加载”，即仅在需要获取值时才发生。但您还需要小心此机制的副作用，因为它可能会通过创建大量小查询来降低性能。

ORM还可以将SQL语句打包成事务，这比单独执行它们要快得多。

目前我最喜欢的 PHP ORM：Doctrine。

  21.小心“永久链接”

“永久链接”的目的是减少MySQL引用重建的次数。一旦创建链接，它就会永远保持连接，即使在数据库操作完成后也是如此。此外，因为我们的 Apache 开始重用其子进程 - 这意味着下一个 HTTP 请求将重用 Apache 子进程并使用相同的 MySQL 连接。

PHP 手册：mysql_pconnect()

理论上，这听起来很棒。但根据我个人（以及大多数人）的经验，此功能会导致更多问题。因为你只有有限数量的链接、内存问题、文件管理器等。有

此外，Apache 在高度并行的环境中运行，并且会产生很多很多进程。这就是为什么这种“永久链接”机制不能很好地发挥作用。在决定“永久链接”之前，您需要仔细考虑整个系统的架构。

补充：mysql强制建立索引并禁用索引

1。 mysql强制使用索引：强制索引（索引名称或主键PRI）

例如：

select * from table强制索引（PRI）限制2;（强制使用主键）

select * from table force index(ziduan1_index) limit 2;(强制使用索引“ziduan1_index”)

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引“PRI和ziduan1_index”) MySQL禁用index ：忽略索引（索引名称或主键 PRI）

例如：

select * from table 忽略索引（PRI）限制 2； (使用禁止的主键)

select * from tableignoreindex(ziduan1_index) limit 2;(禁止使用索引“ziduan1_index”)

select * from tableignoreindex(PRI,ziduan1_index)limit 2;(禁止) 。使用索引“PRI,ziduan1_index”）

作者：Gawa Siege Lion
链接：https://juejin.im/post/5a532a7af265da3e2d3347a0❙版权归作者所有。商业转载请联系作者获得许可。非商业转载请注明来源。