淘小兔

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 = 256Minnodb_flush_log_at_trx_commit = 1 # may change to 2 or 0innodb_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.debsudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.debsudo apt-get updatesudo 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 = 1Glog_bin = /var/log/mysql/mysql-bin.logserver-id=master-01binlog-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 CityJOIN 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.loglong_query_time = 1log-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/

下载仅供下载体验和测试学习,不得商用和正当使用。

下载体验

请输入密码查看内容!

如何获取密码?

 

点击下载