17611538698
webmaster@21cto.com

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

作者 Admin 分类 资讯 11月18日
1509294383superhero-534120_1280.jpg

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/


评论