MySQL虽然出了多个分支，但它仍然是世界上最受欢迎的关系数据库。但它最初的安装以及至部署到生产环境之时，可能不是最优化的状态。许多人就用它的默认值在跑，没有深入的进行研究。我在本文中，和你讨论这些MySQL的优化技巧，并将它们与后面MySQL的新特性结合在一起来讲解。
配置优化
首先，这一部分是最容易被人们忽略的。性能是每个MySQL最应该做的事。虽然MySQL 5.7之后配置默认值比以前有更合理，但是即使如些还是有很多优化的空间。
我们假设你正在一个linux主机，无论是阿里云，腾讯云还是AWS还是其它布拉布拉，MySQL安装后的配置文件都在/etc/mysql/my.cnf中。也有可能你的MySQL配置文件内容不多，可能还加载了其它配置文件，需要仔细观察一下。比如/etc/mysql/mysql.conf.d/mysqld等名称。
配置文件
你需要有一个熟悉的命令行工具，比如vi或emacs。如果你没有接触过它，那么也可以用其它方式。你可以在本地使用Vagrant box来编辑，比如把原文件复制到安全的文件夹中编辑。比如：
cp /etc/mysql/my.cnf /home/vagrant/Code
使用编辑器做正则编辑，编辑完毕后将原来的配置文件备份，再复制回原目录。当然你也可直接编辑它，如果你有十足把握的情况下来操作：
sudo vim /etc/mysql/my.cnf
注意：上面的路径是我当前Linux系统的真实路径，你的配置文件有可能在/etc/mysql/mysql.conf.d/mysqld.conf 上。
手动微调
下面我们手动调整上面的my.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
该缓冲区用来在内存中缓存数据和索引的存储区域。用来把经常访问的数据保存在内存中。当运行VPS或云服务器时，数据库常常成为瓶颈，因此将内存分多一些给应用程序是有意义的，一般情况下是，我们通常分给它内存的50%-70%。MySQL文档中提供了一个缓冲池大小调整指南。
innodb_log_file_size
这个参数告诉MySQL二进制日志文件的大小。
那么问题来了，MySQL的二进制日志是大还是小合适？简单来讲，MySQL在清理日志前需要存储很多数据。
注意在这种情况下，二进制日志并不是系统错误日志或者开发者会用到这些内容，而是check point —— 检查点时间。因为在MySQL中，写入日志是在后台执行的，多少会影响前台性能。日志设置的大些意味着更好的性能，因为创建的新检查点和更小的检查点较小，但是发生崩溃时需要更长的恢复时间，需要将更多的内容重新回写到数据库。
innodb_flush_method
为了避免重复刷新，你可以把 O_DIRECT设置为true，以避免双缓冲。我们应该一直这样做，除非你的系统 I/O性能非常低。在现在大多数的托管服务器，云主机上，大家多半会选择SSD固态硬盘，所以设置为true后，系统 I/O 性能会提高很多。
另外，Percona 也提供了4个修补工具帮助我们自动找到余下的性能问题。请注意，如果我们没有进行上述手动调整的情况下运行这个，这几个修补工具的检测结果取决于用户首选项和应用的当前环境。
下面我们分别来使用Percona提供的这几个修补优化工具。
变量检查器（Variable Inspector）
我们在Ubuntu上安装Variable Inspector。我们使用如下命令：
wget https://repo.percona.com/apt/p ... .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
如果是其它操作系统 ，请参考 https://www.percona.com/downlo … TEST/
安装成功后运行这个工具包，使用如下命令：
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.
可以看到，这些内容并非最关键，它们并不需要修复。我们唯一可以添加的是用来复制和进行快照的二进制记录。
注意：最新版本的MySQL的bin log大小默认为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 Tunner
MySQL Tunner是用来用较长时间监控MySQL数据库，比如每周运行一次，我们可以根据它产生日志的内容来做优化调整。
MySQL Tunner用Perl开发，可以直接下载运行：
[size=15]wget https://raw.githubusercontent. ... er.pl chmod +x mysqltuner.pl [/size]
使用./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)
可以看到，它给了我们一些运行和校正信息。
这个工具应该每周跑一次，因为MySQL正在运行，数据和配置发生变化，或服务器的启动，都需要再运行校正。放在Cronjob里来做这件事是个好主意，还可以让它定期给你发送结果。
值得我们注意的是，每次配置改变后，都需要重启MySQL服务器才能生效。
sudo service mysql restart
索引
接下来，我们把重点放在索引上——这是很多业余数据库管理员的主要痛点！尤其那立即使用ORM而未真正暴露使用原始SQL的人。
注意：在本文中键（Key）和索引（Index）会互换使用。
MySQL索引与书籍中的索引类似，都是让用户能轻松的查找到正确的页面。如果没有索引，就必须浏览整本书，搜索包含该关键字的页面。
正像我们想象的一样，通过索引搜索比通过搜索每个页面更快。因此，使用索引会加快数据库的SELECT查询。
索引被创建和存储后才能使用，因此，数据库在更新和插入操作时速度会变慢，索引的存储也会占用一些磁盘空间。
但是你无需过多担心，如果是正确地创建数据表索引，则不会出现Update、Insert时的速度延迟问题。
所以创建正确的索引是相当的重要。那么，我们怎样找出哪些字段需要添加索引以及创建什么样的类型。
唯一/主索引
主索引-Privary Indexes是数据主索引的默认方式。
比如在用户帐号表里，可能是UserId或UserName，甚至是邮箱地址。主索引是唯一的，它指的是在一组数据中不能被重复的索引值。
例如，如果想让用户选择了一个指定的userName，其他人将不能再使用，那么在username字段中添加一个’unique’唯一索引就解决了这个问题。
如果其他人也想插入一个已经存在的用户名，MySQL就会返回错误信息。
告诉我们该用户名已经存在。如下代码：
...
ALTER TABLE `users`
ADD UNIQUE INDEX `username` (`username`);
...
主键和唯一键通常在表创建时定义，通过Alter的更改方式来定义唯一索引。
主键和唯一键都可以在一个字段或多个字段上创建。比如，如果你想让每个国
家和地区只能创建一个用户名，则可以同时在这两个字段上创建唯一索引。
如下代码：
...
ALTER TABLE `users`
ADD UNIQUE INDEX `usercountry` (`username`, `country`),
...
唯一索引会放在我们经常处理的字段上。因此，如果用户帐号频繁地被查询，而且数据库中有许多用户帐号，那么这是一个很好的例子。
常规索引
常规索引方便查询。当我们需要快速查找特定的字段或字段组合的数据时，这些索引将非常有用，这些数据也不需要是唯一的。
如下代码：
...
ALTER TABLE `users`
ADD INDEX `usercountry` (`username`, `country`),
...
上面的索引添加后将使搜索每个国家的用户名时速度更快。索引也有助于排序和分组（Group By）的查询速度。
全文索引
FULLTEXT全文索引用来进行全文搜索。只有InnoDB和MyISAM存储引擎才能支持FULLTEXT全文索引，且仅支持char,varchar和text类型的字段，我想这些已经够了。
这些索引对一些需要运行文字搜索时会非常有用。在一堆文字中找到关键字是FULLTEXT的特色。如果你的应用会让用户进行搜索操作，比如帖子，评论等都可以使用全文索引。
降序索引
从MySQL 8.x开始，开始支持降序索引这一新特性。因此这不是一个特殊类型，而是一个变化。
当我们需要在表中取得最后添加的数据时，或以降序的方式优先录入数据，这个新特性就派上用场了。如下代码：
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查询前，它会以非常深入的进行处理，分析使用的索引，显示命中和未命中的比率。我们会注意到需要处理多少条记录才能取得自己要找到的结果。
如下代码：
EXPLAIN SELECT City.Name FROM City
JOIN Country ON (City.CountryCode = Country.Code)
WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
可以进一步扩展：
EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'
辅助工具： Percona for Duplicate Indexs
Percona工具包有一个检测重复索引的工作。这在使用第三方CMS时可以派上用场。可以检查某张表是否是意外地添加比的所需索引更多的索引。
比如WordPress中的wp_posts表中就存在重复的索引 ：
pt-duplicate-key-checker h=localhost,u=homestead,p=secret
# ########################################################################
# homestead.wp_posts
# ########################################################################
# Key type_status_date ends with a prefix of the clustered index
# Key definitions:
# KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
# PRIMARY KEY (`ID`),
# Column types:
# `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default 'post'
# `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default 'publish'
# `post_date` datetime not null default '0000-00-00 00:00:00'
# `id` bigint(20) unsigned not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);
可以看到在最后一行，它建议怎样去掉多余的重复索引。
辅助工具：Percona查找未使用的索引
Peercona还可以帮你检查没有用的过的索引。如果你在记录慢速查询，则可以运行该工具，并检查这些记录的查询是否正在使用数据表中的索引。
pt-index-usage /var/log/mysql/mysql-slow.log
关于此工具的用户，可以参阅：https://www.percona.com/doc/pe … .html
发现瓶颈
这一部分我们来说如何检测和监控数据库中的瓶颈。
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes = 1
可以把上面一段语句放在MySQL配置中，用来监控大于1秒的慢查询，以及没有使用索引的核销。
一旦发现mysql-slow.log有内容，你就可以用前面提到的pt-index-usage或pt-query-digest工具来分析它的索引用法。
如下用法：
pt-query-digest /var/log/mysql/mysql-slow.log
# 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz
# Current date: Thu Feb 13 22:39:29 2014
# Hostname: *
# Files: mysql-slow.log
# Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________
# Time range: 2014-02-13 22:23:52 to 22:23:59
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3ms 267us 406us 343us 403us 39us 348us
# Lock time 827us 88us 125us 103us 119us 12us 98us
# Rows sent 36 1 15 4.50 14.52 4.18 3.89
# Rows examine 87 4 30 10.88 28.75 7.37 7.70
# Query size 2.15k 153 296 245.11 284.79 48.90 258.32
# ==== ================== ============= ===== ====== ===== ===============
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article
# 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item
# 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item
# 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category
# 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category
# 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article
# ==== ================== ============= ===== ====== ===== ===============
# Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______
# Scores: V/M = 0.00
# Time range: all events occurred at 2014-02-13 22:23:52
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 37 3
# Exec time 40 1ms 352us 406us 375us 403us 22us 366us
# Lock time 42 351us 103us 125us 117us 119us 9us 119us
# Rows sent 25 9 1 4 3 3.89 1.37 3.89
# Rows examine 24 21 5 8 7 7.70 1.29 7.70
# Query size 47 1.02k 261 262 261.25 258.32 0 258.32
# String:
# Hosts localhost
# Users *
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS LIKE 'blog_article'/G
# SHOW CREATE TABLE `blog_article`/G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10
如果你想手工分析这些日志，也可以这样做——但首先要将日志导出为“可分析”的格式。
使用如下方式完成：
mysqldumpslow /var/log/mysql/mysql-slow.log
还有其他的参数能够进一步过滤数据，并确保只有重要的内容导出。
例如：按平均执行时间排序前10个查询。
mysqldumpslow -t 1- -s at /var/log/mysql/localhost-slow.log
还有其它参数，你还可以延伸阅读到 https://dev.mysql.com/doc/refm … .html
小结
在这篇全面的MySQL优化文章中，我们看到了使MySQL跑得更快的各种技术。包括处理配置文件，参数优化，通过索引来优化查询，如何改善数据瓶颈。
然而，这大部分都是理论性的内容，对于在真实应用中使用这些技术的真实用例，还需要举一反三。我们还会持续推出一系列的MySQL性能专题。
我是否还少写或错过别的技巧和提示？ 欢迎评论留言。
作者：Bruno Skvorc
编译：养乐多
地址：https://www.sitepoint.com/opti … tion/
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/257028.html