如何优化 MySQL：索引、慢查询、配置
MySQL 仍然是世界上最流行的关系数据库，然而，它仍然是最未优化的——许多人将其保留为默认值，而懒得进一步研究。 在这篇文章中，我们将看看我们之前介绍过的一些 MySQL 优化技巧，并将它们与之后出现的新事物结合起来。
配置优化
第一个——也是被跳过最多的！ – 性能升级每个 MySQL 用户都应该做的是调整配置。 5.7（当前版本）的默认设置比其前身好得多，但在这些默认设置之上进行改进仍然很容易。
我们假设您使用的是基于 Linux 的主机或像我们的 Homestead Improved 这样的好 Vagrant box，因此您的配置文件将位于 /etc/mysql/my.cnf. 您的安装实际上可能会将辅助配置文件加载到该配置文件中，因此请调查一下 – 如果 my.cnf 文件内容不多，文件 /etc/mysql/mysql.conf.d/mysqld.cnf 可能。
编辑配置
您需要熟悉使用命令行。 即使您还没有接触过它，现在也是最好的时机。
如果您在 Vagrant 框上进行本地编辑，则可以通过将文件复制到共享文件夹中来将文件复制到主文件系统中 cp /etc/mysql/my.cnf /home/vagrant/Code 并使用常规文本编辑器对其进行编辑，然后在完成后将其复制回原位。 否则，通过执行来使用像 vim 这样的简单文本编辑器 sudo vim /etc/mysql/my.cnf.
注意：修改上面的路径以匹配配置文件的真实位置——它可能实际上在 /etc/mysql/mysql.conf.d/mysqld.cnf
手动调整
以下手动调整应该开箱即用。 根据这些提示，将其添加到配置文件中 [mysqld] 部分：
innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size – 缓冲池是内存中缓存数据和索引的存储区。 它用于将经常访问的数据保存在内存中，当您运行专用或虚拟服务器时，数据库通常会成为瓶颈，为您的应用程序的这一部分提供最大的 RAM 是有意义的。 因此，我们将所有 RAM 的 50-70% 分配给它。 MySQL 文档中提供了缓冲池大小调整指南。 日志文件大小在这里得到了很好的解释，但简而言之，它是在擦除日志之前要在日志中存储多少数据。 请注意，在这种情况下，日志不是错误日志或您可能习惯的东西，而是指示检查点时间，因为对于 MySQL，写入发生在后台，但仍会影响前台性能。 大日志文件意味着更好的性能，因为创建的新检查点更少和更小，但在崩溃的情况下恢复时间更长（更多的东西需要重新写入数据库）。innodb_flush_log_at_trx_commit 在这里解释并指出日志文件发生了什么。 设置为 1 是最安全的设置，因为日志会在每次事务后刷新到磁盘。 对于 0 或 2，它的 ACID 更少，但性能更高。 这种情况下的差异不足以抵消设置为 1 带来的稳定性优势。innodb_flush_method – 最重要的是关于冲洗，这被设置为 O_DIRECT 以避免双缓冲。 应该始终这样做，除非 I/O 系统性能非常低。 在像 DigitalOcean droplets 这样的大多数托管服务器上，您将拥有 SSD，因此 I/O 系统将是高性能的。
Percona 的另一个工具可以帮助我们自动找到剩余的问题。 请注意，如果我们在没有进行上述手动调整的情况下运行它，则 4 个修复中只有 1 个会被手动识别，因为其他 3 个取决于用户偏好和应用程序的环境。
变量检查器
在 Ubuntu 上安装变量检查器：
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit
对于其他系统，请按照说明进行操作。
然后，运行工具包：
pt-variable-advisor h=localhost,u=homestead,p=secret
您应该看到与此不同的输出：
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.
# NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB.
# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.
# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.
# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.
这些都不是关键的，它们不需要修复。 我们唯一可以添加的是用于复制和快照目的的二进制日志记录。
注意：binlog 大小在新版本中默认为 1G，不会被 PT 记录。
max_binlog_size = 1G
log_bin = /var/log/mysql/mysql-bin.log
server-id=master-01
binlog-format="ROW"
- 这
max_binlog_size 设置决定了二进制日志的大小。 这些是记录您的事务和查询并创建检查点的日志。 如果事务大于最大值，则日志在保存到磁盘时可能大于最大值——否则，MySQL 会将它们保持在该限制。 这 log_bin 选项完全启用二进制日志记录。 没有它，就没有快照或复制。 请注意，这可能会非常占用磁盘空间。 服务器 ID 是激活二进制日志记录时的必要选项，因此日志知道它们来自哪个服务器（用于复制），格式就是日志的写入方式。
正如您所看到的，新的 MySQL 具有合理的默认值，可以让产品接近生产就绪。 当然，每个应用程序都是不同的，并且有额外的自定义调整适用。
MySQL调谐器
Tuner 将以更长的时间间隔监视数据库（在实时应用程序上每周运行一次左右）并根据在日志中看到的内容提出更改建议。
只需下载即可安装它：
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
运行它 ./mysqltuner.pl 将要求您提供数据库的管理员用户名和密码，并从快速扫描中输出信息。 例如，这是我的 InnoDB 部分：
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/11.2M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 256.0M * 2/1.0G should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 96.65% (19146 hits/ 19809 total)
[!!] InnoDB Write Log efficiency: 83.88% (640 hits/ 763 total)
[OK] InnoDB log waits: 0.00% (0 waits / 123 writes)
同样，重要的是要注意该工具应该每周运行一次，大约在服务器运行时运行一次。 一旦更改配置值并重新启动服务器，它应该从那时开始运行一周。 最好设置一个 cronjob 来为您执行此操作并定期向您发送结果。
确保在每次配置更改后重新启动 mysql 服务器：
sudo service mysql restart
索引
接下来，让我们关注索引——许多爱好者数据库管理员的主要痛点！ 尤其是那些立即跳入 ORM 并因此从未真正接触过原始 SQL 的人。
注意：术语键和索引可以互换使用。
您可以将 MySQL 索引与书中的索引进行比较，这样您就可以轻松找到包含您要查找的主题的正确页面。 如果没有任何索引，您将不得不翻阅整本书来搜索包含该主题的页面。
可以想象，通过索引搜索比浏览每一页要快得多。 因此，向数据库添加索引通常会加快选择查询的速度。 但是，还必须创建和存储索引。 所以更新和插入查询会更慢，并且会花费更多的磁盘空间。 一般来说，如果您正确地为表建立了索引，您将不会注意到更新和插入的区别，因此建议在正确的位置添加索引。
仅包含几行的表并不能真正从索引中受益。 您可以想象搜索 5 页并不比首先转到索引、获取页码然后打开该特定页面慢多少。
那么我们如何找出要添加哪些索引，存在哪些类型的索引呢？
唯一/主要索引
主索引是数据的主要索引，是默认的寻址方式。 对于用户帐户，这可能是用户 ID 或用户名，甚至是主电子邮件地址。 主索引是唯一的。 唯一索引是一组数据中不能重复的索引。
例如，如果一个用户选择了一个特定的用户名，其他人就不能使用它。 添加一个“唯一”索引到 username 列解决了这个问题。 如果其他人试图插入一个用户名已经存在的行，MySQL 会报错。
...
ALTER TABLE `users`
ADD UNIQUE INDEX `username` (`username`);
...
主键/索引通常在创建表时定义，而唯一索引是在事后通过更改表来定义的。
主键和唯一键都可以在单个列或多个列上同时创建。 例如，如果你想确保每个国家只能定义一个用户名，你可以在这两列上创建一个唯一索引，如下所示：
...
ALTER TABLE `users`
ADD UNIQUE INDEX `usercountry` (`username`, `country`),
...
唯一索引被放置在您将经常处理的列中。 因此，如果频繁请求用户帐户并且您在数据库中有很多用户帐户，那是一个很好的用例。
常规索引
常规索引便于查找。 当您需要按特定列或列组合快速查找数据时，它们非常有用，但该数据不需要是唯一的。
...
ALTER TABLE `users`
ADD INDEX `usercountry` (`username`, `country`),
...
上面的内容可以更快地搜索每个国家/地区的用户名。
索引还有助于提高排序和分组速度。
全文索引
FULLTEXT 索引用于全文搜索。 只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且只支持 CHAR、VARCHAR 和 TEXT 列。
这些索引对于您可能需要进行的所有文本搜索都非常有用。 在文本正文中查找单词是 FULLTEXT 的专长。 如果您经常允许在您的应用程序中搜索它们，请在帖子、评论、描述、评论等上使用它们。
降序索引
不是特殊类型，而是变种。 从 8+ 版本开始，MySQL 支持降序索引，这意味着它可以按降序存储索引。 当您有大量的表，这些表经常首先需要最后添加的数据，或者以这种方式对条目进行优先级排序时，这会派上用场。 按降序排序总是可行的，但会带来很小的性能损失。 这进一步加快了速度。
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
在处理写入数据库的日志、从后到前加载的帖子和评论等类似内容时，请考虑将 DESC 应用于索引。
辅助工具：说明
在查看优化查询时，EXPLAIN 工具将是无价的。 为一个简单的查询加上前缀 EXPLAIN 将以非常深入的方式处理它，分析正在使用的索引，并向您展示命中率和未命中率。 您会注意到它必须处理多少行才能获得您要查找的结果。
EXPLAIN SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
您可以进一步扩展它 EXTENDED:
EXPLAIN SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
通过阅读这篇优秀而详细的文章，了解如何使用它并应用这些发现。
辅助工具：用于重复索引的 Percona
之前安装的 Percona Toolkit 也有一个检测重复索引的工具，在使用第三方 CMS 或只是检查您是否不小心添加了比需要更多的索引时可以派上用场。 例如，默认的 WordPress 安装在 wp_posts 桌子：
pt-duplicate-key-checker...
