15 个有用的 MySQL/MariaDB 性能调优和优化技巧

MySQL是一个功能强大的开源关系数据库管理系统，简称RDBMS。它于 1995 年（20 年前）发布。它使用结构化查询语言，这可能是管理数据库内容的最流行选择。最新的 MySQL 版本是5.6.25 ，于2015 年 5 月 29 日发布。

关于 MySQL 的一个有趣的事实是，它的名字来自Michael Widenius（MySQL 的创建者）的女儿My。尽管关于 MySQL 有很多有趣的事实，但本文旨在向您展示一些有用的实践，以帮助您管理 MySQL 服务器。

2009 年4 月，MySQL 项目被 Oracle 收购。因此，一个名为MariaDB的 MySQL 社区分支应运而生。创建该分支的主要原因是让该项目在通用公共许可证下保持免费。

如今，MySQL和MariaDB是用于WordPress、Joomla、Magento等 Web 应用程序最常用的 RDBMS 之一（如果不是最常用的话）。

本文将向您展示一些基本但有用的技巧，帮助您优化和微调 MySQL/MariaDB 的性能。请记住，本文假设您已经安装了 MySQL 或 MariaDB。如果您仍想知道如何在您的系统上安装它们，您可以按照我们这里的详尽指南进行操作：

1. 在 RHEL/CentOS 7 上安装 LAMP
2. 在 Fedora 22 上安装 LAMP
3. 在 Ubuntu 15.04 上设置 LAMP
4. 在 Debian 8 上安装 MariaDB
5. 在 Gentoo Linux 上安装 MariaDB
6. 在 Arch Linux 上安装 MariaDB

重要提示：在开始之前，请不要盲目接受这些建议。每个 MySQL 设置都是独一无二的，在进行任何更改之前，需要进行额外的思考。

你需要知道的事情：

1. MySQL/MariaDB 配置文件位于/etc/my.cnf。每次修改此文件时，都需要重新启动 MySQL 服务以使新的更改生效。
2. 撰写本文时使用了MySQL 5.6版本作为模板。

1. 启用 InnoDB file-per-table

首先，需要说明的是，InnoDB是一种存储引擎。MySQL 和 MariaDB 使用 InnoDB 作为默认存储引擎。过去，MySQL 习惯将数据库表和索引保存在系统表空间中。这种方法适用于仅用于数据库处理且其存储磁盘不用于任何其他目的的服务器。

InnoDB 提供了更灵活的方法，每个数据库信息都保存在.ibd数据文件中。每个.ibd文件代表其自己的表空间。这样，诸如“ TRUNCATE ”之类的数据库操作可以更快地完成，并且您还可以在删除或截断数据库表时回收未使用的空间。

此配置的另一个好处是您可以将一些数据库表保存在单独的存储设备中。这可以大大改善磁盘上的I/O负载。

在 MySQL 5.6 及更高版本中，innodb_file_per_table默认启用。您可以在/etc/my.cnf文件中看到它。该指令如下所示：

innodb_file_per_table=1

2. 将 MySQL 数据库数据存储在单独的分区上

注意：此设置仅适用于 MySQL，不适用于 MariaDB。

有时，操作系统的读写操作会降低 MySQL 服务器的性能，尤其是当它们位于同一块硬盘上时。因此，我建议为 MySQL 服务使用单独的硬盘（最好是 SSD）。

要完成此操作，您需要将新驱动器连接到您的计算机/服务器。出于本文的目的，我假设驱动器位于/dev/sdb下。

下一步是准备新的驱动器：

# fdisk /dev/sdb

现在按“ n ”创建新分区。接下来按“ p ”使新分区成为主分区。之后，设置分区号（1-4 ） 。之后，您将选择分区大小。在这里按回车键。下一步，您需要配置分区的大小。

如果您希望使用整个磁盘，请再次按 Enter 键。否则，您可以手动设置新分区的大小。准备就绪后，按“ w ”写入更改。现在我们需要为新分区创建一个文件系统。这可以轻松完成：

# mkfs.ext4 /dev/sdb1

现在我们将新分区挂载到文件夹中。我将文件夹命名为“ ssd ”，并在根目录中创建：

# mkdir /ssd/

我们准备在新文件夹中挂载刚刚创建的新分区：

# mount /dev/sdb1  /ssd/

您可以在/etc/fstab文件中添加以下行来在启动时执行挂载。

/dev/sdb1 /ssd ext3 defaults 0 0

现在您已准备好将 MySQL 移动到新磁盘。首先使用以下命令停止 MySQL 服务：

# service mysqld stop

我建议您也停止 Apache/nginx，以防止任何在数据库中写入的尝试：

# service httpd stop
# service nginx stop

现在将整个 MySQL 目录复制到新驱动器中：

# cp /var/lib/mysql /ssd/ -Rp

这可能需要一段时间，具体取决于您的 MySQL 数据库的站点。此过程完成后，重命名 MySQL 目录：

# mv /var/lib/mysql /var/lib/mysql-backup

接下来我们将创建一个符号链接。

# ln -s /ssd/mysql /var/lib/mysql

现在您可以启动 MySQL 和 Web 服务了：

# service mysqld start
# service httpd start
# service nginx start

此时，您的 MySQL 数据库将可以从新驱动器访问。