MySQL数据库高性能之全面优化指南!

发布时间:2018-01-10 18:37:23编辑:丝画阁阅读(193)

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性能专题。

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

关键字