技术老铁们,工作累了,我们就一起来放松一下!老张我呢是个金庸迷,在金庸小说中,降龙十八掌无愧巅峰外功,它的威力之大可想而知。而今儿,老张要给大家介绍18招式,来优化我们的 MySQL 数据库,让它跑起来更快,更稳定!


之前老有学生问我,张老师该如何优化我们的 MySQL 数据库呢?这个问题太泛泛了,不是很具体!因为数据库的优化要从多个角度去考虑,通过不同的维度模型去排查问题。老师整理了下思路,大概可以从18个角度,大致四个方向去给大家一些建议。


第一掌----亢龙有悔

要想保证数据库能够高效,稳定地运行在服务器上面,我们首先要保证有充足的内存,只有内存足够大了,我们才能缓存住那些我们经常访问的热数据,一些 update 语句的操作当然也可以在内存中优先完成。但是我们要考虑内存使用黄金分割法则,由于不同业务的存在,对内存的需求当然也就不一样了。

举个列子来说,用户经常访问的热数据,对于内存的分配就要尽可能达到达到数据库内存的 70-80% 左右。众所周知,我们知道 MySQL 数据库内存主要靠 innodb_buffer_pool,redo log buffer,double write buffer,binlog cache 等组成。如果服务器上面只跑着 MySQL 一个应用,那大概 innodb_buffer_pool 可以分配到物理内存的 50-80% 左右。

TIPS:我们要根据实际物理内存的大小,具体是什么业务类型,去考虑数据库内存的分配。


第二掌----飞龙在天

要优化 MySQL 数据库,首先要很了解对手,随着版本的升级,MySQL 用到的 CPU 核数就越多,自从 MySQL 5.6 之后可以使用到 64 个核。MySQL 连接特点的是这样,每个连接对应一个线程,每个 sql/ 查询只能使用到一个 cpu 核心,所以需要越多的 CPU,并且更快的 CPU。这样才能有利于提高数据库性能,提高我们数据库的并发能力!

TIPS:使用多核 CPU。


第三掌----见龙在田

众所周知,IO 对数据库来说,一直都是瓶颈,并且有可能将来一段时间还会是。所以对存储介质的要求就非常高,对于 IO 系统比较高的情况下,建议我们要使用更快的存储设备 SSD 固态硬盘可提高上百倍的数据读写性能或者是 PCIE-SSD 固态硬盘可提高上千倍的数据读写能力。像现在的一些电商网站,在搞店庆或者促销活动的时候,都需要借助此设备,来满足大量用户的影响请求。

TIPS:建议上高转速硬件设备,SSD 或者 PCIE-SSD


第四掌----鸿渐于陆

自从 web2.0 时代开启,基本所有的,我们使用的软件都是基于 linux 平台自主研发的。我们知道,MySQL 数据库也是跑在 linux 操作系统上面的。在官方建议估计最推荐的是 Solaris,但从实际生产中的角度来看 CentOS 和 REHL 都是不错的选择,个人建议推荐使用 CentOS, 如果非要使用 REHL,建议 版本为6以后的,这里就不推荐使用在 windows 下跑 MySQL 数据库了,虽然随着 MySQL 版本提升,对 windows 有了相关的优化,但是对于高并发,高负载的环境来说,依旧不建议使用。

TIPS:推荐使用 CentOS,或者 REHL 操作系统类型


第五掌----潜龙勿用

操作系统层面的优化,我们要考虑个可能大家会比较忽略的问题,首先就是 swappiness 的问题。swappiness 的值大小对如何使用 swap 分区有着密切的联系。有两个极限值,一个为 0,另一个为 100,查看可执行 cat /proc/sys/vm/swappiness。

0 代表:最大限度地使用物理内存,然后才是 swap 分区,这种行为有可能导致系统内存溢出,从而导致mysql被意外kill掉。不建议这样去设置。

100则为:积极地使用使用 swap 分区,并且把内存上面的数据及时搬到 swap 分区里。

TIPS:这里比较建议使用默认 60 就可以。


第六掌----利涉大川

与 swappiness 对应的,另一个操作系统层面的优化,还有一个小细节点就是 IO 调度。这里有 cfq,noop 和 deadline,系统默认使用 cfq,这里老师建议使用 deadline。查看方法:

cat /sys/block/sda/queue/scheduler/ 

TIPS:deadline 可以调整读写时间,避免写完没有被读取的饿死场景。


第七掌----突如其来

Oracle 11g 之后多了一个 result_cache,来缓存数据结果集。MySQL 里面通过 innodb_buffer_pool 里面有个 query cache 来缓存静态结果集。我们都希望热数据都保存在内存里面,我们读取数据快速便捷,数据库的缓存率也很高!但数据库中的 query cache 里面的数据一但发生更改,此缓存区毫无意义,就会变成鸡肋。而且如果开启 Query Cache,更新与写入都要去检查 query cache 反而增加了写入的开销。

TIPS:建议关闭 query cache


第八掌----震惊百里

对于磁盘阵列,我们再熟悉不过了,但是对于阵列卡的 cache 策略,我们又该如何选择呢。首先对于qps,tps,业务高的系统,一定要配置阵列卡,配 cache 模块,和 BBU 模块(用于提供后备电量)。

cache 策略有两种,一种为:write through(WT);另一种为:write back;

个人强烈建议使用 write back(WB)。WT含义,数据直接写入磁盘,WB含义:数据先写阵列卡的cache,再由cache写入磁盘,这样对于写入的性能有所提高。并且对于加速redo log ,binlog, data file都有好处。

TIPS:强烈建议阵列卡的 cache 策略使用 write back。


第九掌----或跃在渊

前面也涉及到了,尽可能大的给 innodb_buffer_pool 分配空间,在服务器只跑数据库一个应用前提下大概为物理内存 50-80%。

TIPS:建议应用与数据库分开部署在服务器上面,后期好排查问题。


第十掌----双龙取水

MySQL 数据库的一些核心参数,我们要在心里铭记。比如双一的含义,直接影响日志的刷新机制。影响redo log buffer 的刷新机制

innodb_flush_log_at_trx_commit  = 1(最安全)

innodb_flush_log_at_trx_commit  = 2 (性能一般)

innodb_flush_log_at_trx_commit  = 0 (性能最好)。

影响binlog cache的刷新机制~sync_binlog=0,当事务提交之后,MySQL 不做 fsync 之类的磁盘同步指令刷新 binlog_cache 中的信息到磁盘,而让 Filesystem 自行决定什么时候来做同步,或者 cache满了之后才同步到磁盘。sync_binlog=n当每进行 n 次事务提交之后,MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘。为了确保安全性,我们可以将sync_binlog=1。为了获得最佳性能我们可以将sync_binlog=0。

TIPS:对于不同业务的公司,保障的点不一样,所有我们要考虑好,是业务最重要,还是数据最重要!然后分别去设置不同的参数value


第十一掌----鱼跃于渊

MySQL 数据库区别于其他数据库最主要就是插件式存储引擎,最为著名就是 myisam 还有 innodb。它们都有各自的特点,这里强烈建议使用 innodb 存储引擎表,无论是对于事务的支持,还是在线 DDL 语句快速操作,它都是目前最优秀的存储引擎!MySQL 5.5 之后默认使用的存储引擎都是 innodb

TIPS:生产环境中,如果还有 myisam 这种存储引擎的表,建议全部做 myisam-->innodb 存储引擎的转换!不过 MySQL 5.7之后,系统表也都是 innodb 了!


第十二掌----时乘六龙

文件系统强烈推荐使用 xfs,不要再使用 ext3,ext4 之类的,因为 xfs 这种文件系统也是 B-tree 结构最接近于数据库的树状结构。


第十三掌----密云不雨

生产环境中,经常会出现对大表进行 delete,或者 update 这类的操作。数据碎片随之产生,我们要经常去整理主要业务表的碎片,让查询检索更快。可以通过 pt-ioprofile 监控与磁盘交互最为紧密的表,然后通过 alter table 或者导入导出数据的方法对表进行碎片整理。尽可能回收表空间


第十四掌----损则有孚

利用天兔(lepus)或者 zabbix 做好对数据库的监控。监控事项可以从服务器的状态,内存的使用情况,cpu的负载。数据库中每秒的增删改查信息,架构中的延迟和复制状态信息去作为监控的核心点。


第十五掌----龙战于野

配合开发人员合理地设计表结构,秉着越简单越好的原则,去选择合适字段的数据类型。对于ipv4,时间类型的字段,我们完全可以通过整型int来存取!通过函数转换就可以了!

ip涉及到两个函数:inet_aton和inet_ntoa

时间类型的两个函数:from_unixtime和unix_timestamp

第十六掌----事务隔离级别的选择

mysql数据库中,有四种事务隔离级别。它们分别是Read Uncommitted(RU),Read Committed(RC),Repeatable Read(RR)Serializable(SR)。对于交易类型系统的网站,对于事务要求比较高,我们建议使用RR这种隔离级别。


第十七掌----羝羊触藩

更改文件句柄  ulimit –n 默认1024 太小

进程数限制  ulimit –u   根据不同版本来决定

禁掉NUMA  numctl –interleave=all


第十八掌----神龙摆尾

做过数据库的同学们,可以经常会遇到too many connections这样的问题,对于这样的问题,我们一定要做好配置数据库内部并发的情况。innodb_thread_concurrency 这个参数来决定innodb的并发情况。默认的大小是0。在mysql5.7版本中,增加了thread pool,连接复用的存在,可以取默认值就ok。但是5.7之前的版本,就需要考量一下取值了,个人建议mysql5.6版本中设置为36。mysql5.6之前可以8-32。


降龙十八掌已经打完,希望对于数据库爱好者,从事数据库工作中的同学来说有帮助。让我们每天学习一点点,把自己的内功练得越来越深厚,打出属于自己的武功。让我们的数据库飞起来!