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/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

如果是其它操作系统 ，请参考 https://www.percona.com/downloads/percona-toolkit/LATEST/

安装成功后运行这个工具包，使用如下命令：

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开发，可以直接下载运行：

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

[--] 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/percona-toolkit/LATEST/pt-index-usage.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/refman/5.7/en/mysqldumpslow.html

小结

在这篇全面的MySQL优化文章中，我们看到了使MySQL跑得更快的各种技术。包括处理配置文件，参数优化，通过索引来优化查询，如何改善数据瓶颈。

然而，这大部分都是理论性的内容，对于在真实应用中使用这些技术的真实用例，还需要举一反三。我们还会持续推出一系列的MySQL性能专题。

我是否还少写或错过别的技巧和提示？ 欢迎评论留言。