Code前端首页关于Code前端联系我们

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

terry 2年前 (2023-09-26) 阅读数 105 #数据库
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❙版权归作者所有。商业转载请联系作者获得许可。非商业转载请注明来源。

版权声明

本文仅代表作者观点,不代表Code前端网立场。
本文系作者Code前端网发表,如需转载,请注明页面地址。

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

热门