MySQL性能调优和优化技巧（如何优化数据库（）） _如何优化MySQL数据库

介绍MySQL 是一种流行的开源数据库应用程序，它以有意义且易于访问的方式存储和构建数据。对于大型应用程序，庞大的数据量可能会导致性能问题。
如何优化MySQL数据库？本指南提供了一些关于如何提高 MySQL 数据库性能的调整技巧。
先决条件

安装并运行 MySQL 的 Linux 系统，Centos或Ubuntu
现有数据库
操作系统和数据库的管理员凭据

系统 MySQL 性能调优MySQL性能调优和优化技巧的依据：在系统级别，你将调整硬件和软件选项以提高 MySQL 性能。
1.平衡四大硬件资源

文章图片
存储花点时间评估你的存储。如果你使用的是传统硬盘驱动器 (HDD)，你可以升级到固态驱动器 (SSD) 以提高性能。
使用类似的工具iotop或SAR从SYSSTAT包来监控你的磁盘输入/输出率。如果磁盘使用率远高于其他资源的使用率，请考虑添加更多存储或升级到更快的存储。
处理器处理器通常被认为是衡量系统速度的指标。使用Linux top命令详细了解你的资源是如何使用的。注意 MySQL 进程及其所需的处理器使用百分比。

文章图片
处理器的升级成本更高，但如果你的 CPU 是瓶颈，则可能需要升级。
内存内存表示 MySQL数据库存储服务器中的 RAM 总量。你可以调整内存缓存（稍后会详细介绍）以提高性能。如果你没有足够的内存，或者如果现有的内存没有优化，则最终可能会损害性能而不是提高性能。
与其他瓶颈一样，如果你的服务器内存不断不足，你可以通过添加更多内存进行升级。如果内存不足，你的服务器将缓存数据存储（如硬盘驱动器）以充当内存。数据库缓存会降低你的性能。
网络这是重要的监控网络流量，以确保你有足够的基础设施来管理负载。
网络过载会导致延迟、丢包，甚至服务器中断。确保你有足够的网络带宽来容纳正常级别的数据库流量。
2. 使用 InnoDB，而不是 MyISAM
MySQL性能调优和优化技巧：MyISAM是用于某些 MySQL 数据库的较旧的数据库样式。这是一种效率较低的数据库设计。较新的InnoDB支持更多高级功能并具有内置优化机制。
InnoDB 使用聚集索引并将数据保存在页面中，这些页面存储在连续的物理块中。如果页面的值太大，InnoDB 会将其移动到另一个位置，然后索引该值。此功能有助于将相关数据保存在存储设备上的同一位置，这意味着物理硬盘驱动器访问数据所需的时间更少。
3. 使用最新版本的 MySQL
MySQL数据库性能优化的方法：对于旧的和遗留的数据库，使用最新版本并不总是可行的。但只要有可能，你应该检查正在使用的 MySQL 版本并升级到最新版本。
正在进行的开发的一部分包括性能增强。较新版本的 MySQL 可能会使一些常见的性能调整变得过时。一般而言，与脚本和配置文件相比，使用原生 MySQL 性能增强总是更好。
软件 MySQL 性能调优SQL 性能调优是最大化关系数据库查询速度的过程。该任务通常涉及多种工具和技术。
这些方法包括：

调整 MySQL 配置文件。
编写更高效的数据库查询。
构建数据库以更有效地检索数据。

注意：在调整配置设置时，最好进行小的增量调整。重大调整可能会使另一个值负担过重并降低性能。此外，建议你一次进行一项更改，然后进行测试。当你一次只更改一个变量时，跟踪错误或错误配置会更容易。
4. 考虑使用自动性能改进工具
如何优化MySQL数据库？与大多数软件一样，并非所有工具都适用于所有版本的 MySQL。我们将检查三个实用程序来评估你的 MySQL 数据库并推荐更改以提高性能。
第一个是Tuning-primer。这个工具有点旧，专为 MySQL 5.5 – 5.7 设计。它可以分析你的数据库并建议设置以提高性能。例如，如果感觉你的系统无法足够快地处理查询以保持缓存清晰，它可能会建议你提高query_cache_size参数。
对大多数现代 SQL 数据库有用的第二个调优工具是MySQLTuner。该脚本 ( mysqltuner.pl ) 是用 Perl 编写的。与tuning-primer 一样，它会分析你的数据库配置，寻找瓶颈和低效率。输出显示指标和建议：

文章图片
在输出的顶部，你可以看到 MySQLTuner 工具和你的数据库的版本。
该脚本适用于 MySQL 8.x。日志文件建议是列表中的第一个，但如果你滚动到底部，你可以看到提高 MySQL 性能的一般建议。

文章图片
你可能已经拥有的第三个实用程序是phpMyAdmin Advisor。与其他两个实用程序一样，它会评估你的数据库并建议调整。如果你已经在使用 phpMyAdmin，Advisor 是一个可以在 GUI 中使用的有用工具。
注意：查看我们的顶级 SQL 查询优化工具列表，并使用我们对每个工具的深入分析来找到最适合你的任务的工具。
5.优化查询
查询是在数据库中搜索与特定值匹配的数据的编码请求。有一些查询运算符，就其本质而言，需要很长时间才能运行。SQL 性能调优技术有助于优化查询以获得更好的运行时间。
检测执行时间很短的查询是性能调优的主要任务之一。通常在大型数据集上实现的查询速度慢且占用数据库。因此，这些表不可用于任何其他任务。
注意：考虑查看数据仓库架构，它将生产数据库与分析数据库分开。
例如，OLTP 数据库需要快速事务和有效的查询处理。运行低效的查询会阻止使用数据库并停止信息更新。
如果你的环境依赖于自动查询（例如触发器），则它们可能会影响性能。检查并终止可能会及时堆积的MySQL 进程。
6. 在适当的地方使用索引
许多数据库查询使用类似于以下的结构：

SELECT … WHERE

MySQL性能调优和优化技巧：这些查询涉及评估、过滤和检索结果。你可以通过为相关表添加一小组索引来重构这些。查询可以直接指向索引以加快查询速度。
7. 谓词中的函数
避免在查询谓词中使用函数。例如：

SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'Copy

该UPPER符号创建了一个函数，该函数必须在操作期间进行SELECT操作。这会使查询所做的工作加倍，如果可能，你应该避免它。
8. 避免在谓词中使用 % 通配符
在搜索文本数据时，通配符有助于进行更广泛的搜索。例如，要选择所有以ch开头的名称，请在名称列上创建一个索引并运行：

SELECT * FROM person WHERE name LIKE "ch%"

查询扫描索引，使查询成本低：

文章图片
但是，在开头使用通配符搜索名称会显着增加查询成本，因为索引扫描不适用于字符串的结尾：

文章图片
搜索开头的通配符不应用索引。相反，全表扫描单独搜索每一行，增加了过程中的查询成本。在示例查询中，在末尾使用通配符有助于降低由于遍历较少的表行而导致的查询成本。
注意：查看我们的MySQL 命令备忘单，其中包含索引命令。
搜索字符串结尾的一种方法是反转字符串，索引反转的字符串并查看起始字符。将通配符放在末尾现在会搜索反转字符串的开头，从而提高搜索效率。
9.在SELECT函数中指定列
MySQL数据库性能优化的方法：分析和探索性查询的常用表达式是SELECT *. 选择超出你需要的数量会导致不必要的性能损失和冗余。如果你指定所需的列，你的查询将不需要扫描不相关的列。
如果需要所有列，则没有其他方法可以解决此问题。但是，大多数业务需求不需要数据集中所有可用的列。考虑改为选择特定的列。
总之，避免使用：

SELECT * FROM table

相反，请尝试：

SELECT column1, column2 FROM table

10. 适当使用 ORDER BY
该ORDER BY表达式按指定的列对结果进行排序。它可用于一次按两列排序。这些应该以相同的顺序排序，升序或降序。
如果你尝试以不同的顺序对不同的列进行排序，则会降低性能。你可以将其与索引结合使用以加快排序。
11. GROUP BY 而不是 SELECT DISTINCT
该SELECT DISTINCT试图摆脱重复值的查询时就派上用场了。但是，语句需要大量的处理能力。
如何优化MySQL数据库？尽可能避免使用SELECT DISTINCT，因为它非常低效且有时令人困惑。例如，如果一个表列出了具有以下结构的客户信息：

ID	name	姓	地址	城市	状态	压缩
0	约翰	史密斯	花街652号	天使	那	90017
1	约翰	史密斯	海洋大道1215号	天使	那	90802
2	玛莎	马修斯	3104山顶大道	天使	那	90019
3	玛莎	琼斯	威尼斯大道2712号	天使	那	90019

运行以下查询返回四个结果：

SELECT DISTINCT name, address FROM person

文章图片
该语句似乎应该返回不同名称的列表及其地址。相反，查询着眼于双方的名称和地址栏。虽然有两对同名的客户，但他们的地址不同。
要过滤掉重复的名称并返回地址，请尝试使用以下GROUP BY语句：

SELECT name, address FROM person GROUP BY name

结果返回第一个不同的名称以及地址，使语句不那么模糊。要按唯一地址分组，GROUP BY参数将更改为 address 并DISTINCT更快地返回与语句相同的结果。
总之，避免使用：

SELECT DISTINCT column1, column2 FROM table

相反，请尝试使用：

SELECT column1, column2 FROM table GROUP BY column1

12. JOIN, WHERE, UNION, DISTINCT
MySQL性能调优和优化技巧：尽可能尝试使用内部联接。外连接查看指定列之外的附加数据。如果你需要该数据，那很好，但是包含不需要的数据会浪费性能。
使用INNER JOIN是连接表的标准方法。大多数数据库引擎也接受使用WHERE。例如，以下两个查询输出相同的结果：

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

对比：

SELECT * FROM table1, table2 WHERE table1.id = table2.id

理论上，它们也具有相同的运行时间。
使用或查询的选择取决于数据库引擎。虽然大多数引擎对于这两种方法具有相同的运行时间，但在某些数据库系统中，一种运行速度比另一种更快。JOINWHERE
注意：了解有关MySQL JOINS以及如何使用它们的更多信息。
的和命令有时包括在查询。与外连接一样，如果需要，可以使用这些表达式。但是，它们增加了对数据库的额外排序和读取。如果你不需要它们，最好找到更有效的表达式。UNIONDISTINCT
13. 使用 EXPLAIN 函数
现代 MySQL 数据库包括一个EXPLAIN函数。
MySQL数据库性能优化的方法：将EXPLAIN表达式附加到查询的开头将读取和评估查询。如果有低效的表达式或令人困惑的结构，EXPLAIN可以帮助你找到它们。然后，你可以调整查询的措辞，以避免无意的表扫描或其他性能下降。
14. MySQL 服务器配置
如何优化MySQL数据库？此配置涉及对/etc/mysql/my.cnf文件进行更改。谨慎行事，一次做一些小的改动。

文章图片
query_cache_size– 指定等待运行的 MySQL 查询的缓存大小。建议从 10MB 左右的小值开始，然后增加到不超过 100-200MB。如果缓存查询过多，你可能会遇到“等待缓存锁定”的级联查询。如果你的查询不断备份，更好的方法是使用EXPLAIN评估每个查询并找到提高它们效率的方法。
max_connection– 指允许进入数据库的连接数。如果你收到引用“连接过多”的错误，增加此值可能会有所帮助。
innodb_buffer_pool_size – 此设置将系统内存分配为数据库的数据缓存。如果你有大量数据，请增加此值。记下运行其他系统资源所需的 RAM。
innodb_io_capacity – 此变量设置存储设备的输入/输出速率。这与存储驱动器的类型和速度直接相关。5400-rpm HDD 的容量比高端 SSD 或 Intel Optane低得多。你可以调整此值以更好地匹配你的硬件。
结论你现在应该知道一些MySQL性能调优和优化技巧，以及如何提高 MySQL 性能并调整你的数据库。
寻找瓶颈（硬件和软件）、做比需要更多工作的查询，并考虑使用自动化工具和EXPLAIN功能来评估你的数据库。
【MySQL性能调优和优化技巧（如何优化数据库（））】优化 MySQL 表有助于在专用存储服务器中重新排序信息，以提高数据输入和输出速度。查看我们关于如何优化 MySQL 表的指南。