mysql数据库性能优化须知

数据库  /  管理员 发布于 2年前   399

当谈到数据库性能优化时，最重要的事情就是选择正确的。

你应该决定你的应用程序是需要关系型数据库还是非关系型数据库。

即使在一种类型中，你也将有多种选择。 与关系数据库一样，你可能会发现 Oracle、MySQL、SQL Server 和 PostgreSQL。 

另一方面，非关系型数据库引入了 MongoDB、Cassandra 和 CouchDB。

你可能希望我建议使用非关系数据库以获得更快的读 / 写性能。 

但是，通过一些改进和调整，您可以推动关系数据库超出其已知限制。 

因此，在本文中，我将向您介绍一些技巧，以使您的 MySQL 数据库更快。

如果您特别想知道为什么要使用 MySQL，那么答案很简单，因为它是免费的、开源的，并且在 PHP 社区中非常受欢迎，而 Oracle 因价格昂贵而未被广泛使用。 其他选项不如 MySQL 受欢迎。

- MySQL 服务器配置：

好吧，首先你应该知道配置文件的位置，具体取决于你的操作系统。

在 Linux 系统中，它位于 “/etc/mysql/my.cnf” 中。现在是时候选择你的引擎 InnoDB 和 MyISAM 了。为了让选择更容易，你应该知道 InnoDB 成为 MySQL 5.5 的默认引擎，因为它支持 “行级锁定、外键和事务”，而 MyISAM 不支持任何提到的特性，这使得它在现代很少有用应用程序。

选择正确的引擎后，是时候在 my.cnf 文件中设置一些配置变量了。

max_connection 变量：

max_connection 变量表示应用程序允许的连接数。

默认值为 151 连接，但是，如果你收到错误消息 “MySQL 错误，连接太多...”，你可以轻松增加此数量 

最大连接数 = 170

innodb_buffer_pool_size 变量：

为了加快速度，MySQL 会将数据缓存在你的服务器内存中，这个变量告诉 MySQL 它可以使用多少 GigaBytes。

如果你在数据库中保存大 blob，此变量非常有用。你可以将其设置为服务器内存的 80–90%。

因此，如果你的服务器内存为 16GB，则可以将其设置为 14GB。

innodb_buffer_pool_size = 14GB

innodb_io_capacity 变量：

这个变量告诉 MySQL 它可以使用多少输入 / 输出操作，它取决于你的磁盘。

例如，单个 7200 RPM 驱动器限制为 200 I/O，而企业 SSD 磁盘限制为 50,000 I/O。

你可以在操作系统上通过命令行轻松找到输入 / 输出值，并将变量设置为可用 I/O 的 90%，因此 MySQL 永远不会使用太多 I/O 操作。

innodb_io_capacity = 21000

query_cache_limit 和 query_cache_size 变量:

MySQL 也支持内存中缓存数据，但是我们不能依赖它来做缓存系统，因为每次你的程序向数据库表写入数据的时候，MySQL 将会重建整个表的查询缓存。

因此如果你的程序有很高的负载，MySQL 缓存将完全无用，这种情况下，两个变量最好设置为 0，节省 MySQL 缓存的开销，相反，你可以使用类似 Redis 的东西来管理缓存。

query_cache_limit = 0
query_cache_size = 0

慢查询日志:

慢查询日志将会显示你的哪些查询超过你定义的阈值，而不需要猜测哪个查询更慢。

首先，你必须在你的配置文件中启用 slow_query_log 。在 Linux 服务器中，打开 「/etc/mysql/my.cnf」或者你系统上同等的文件。

并添加:

slow_query_log = 1
long_query_time = 1

那么，这两个选项将启用慢查询日志，并记录任何需要超过一秒的查询。

如果你喜欢在一个表中而不是在一个文件中查看日志，你可以添加：

log_output = 'TABLE'

然后你可以在「slow_log」表中找到你的日志。你可以在那里看到所有执行超过一秒的慢查询的信息。

这些信息包括查询的确切执行时间和受影响的行数，以及哪个用户执行的它。

查询优化 

在你得到所有的慢查询后，你需要一种方式来优化它们，使它们更快。

因此，你可以在查询语句前面加上「explain」关键词，从而获得相关查询的详情信息，例如：explain select * from users where active=1;

「解释」关键字可以帮助你定义查询命中哪些索引以及为获取数据而查询的行数。

此信息可以告诉你是否需要创建更多索引或重组数据库表。

非规范化和约束：

非规范化是通过添加冗余数据或将它们分组来提高读取性能的过程。

例如，如果你有一个「产品」表和一个「类别」表，并且每次查询「产品」表时，你还需要获取每个产品的「类别名称」。

在这种情况下，你可以使用「join」来检索「category_name」。

但是，这意味着每次用户打开产品页面时，都会执行一个复杂的连接查询。

因此，你可以考虑在「产品」表中添加「类别名称」。尽管有冗余数据，但读取性能的提高还是值得的。

非规范化方法可能会导致「产品」表中的「类别名称」过时。

所以你需要定义一个「外键」约束，但是你需要知道一个「外键」会使写入性能稍微变慢，因为 MySQL 需要在写入数据之前检查约束。

所以做出最佳选择始终是你的任务。

转：

https://codeburst.io/database-performance-optimization-8d8407808b5b