MySQL通用优化技巧

转自：https://www.cnblogs.com/digdeep/p/5167633.html

本文根据 DevOps华南运维圈@UCloud微信群的「运维在线」栏目的嘉宾分享整理而成。「运维在线」将邀请业界运维前线技术专家作为分享嘉宾，分享技术趋势和技术实战，为运维朋友提供各种踩坑、躲坑、绕坑新技能。

嘉宾介绍

叶金荣
Oracle MySQL ACE，国内最早的MySQL推广者。2006年创办国内首个MySQL专业技术网站 MySQL 中文网。资深MySQL专家，10余年MySQL经验，擅长MysQL性能优化、架构设计、故障排查。

内容提纲

  1. MySQL的特点；

  2. 硬件、系统优化；

  3. MySQL 配置优化；

  4. SCHEMA设计优化；

  5. SQL 优化；

  6. 其他优化。

MySQL 的特点

首先，需要明确的是。想要做好MySQL优化，需要先了解MySQL都有哪些特点：

简言之，MySQL一般用于互联网业务的数据持久化存储，并且用于保证数据的一致性、可靠性，而不是用于：

  • 复杂查询；

  • 复杂运算；

  • 大二进制存储。

等奇葩用途。

CPU的利用特点

看看MySQL不同版本对CPU多核的支持、利用情况：

建议：

  • 采用最新MySQL版本，以提升其CPU利用率；

  • 每个SQL足够简单，不要太过复杂；

  • 每个连接足够快速完成，不要“恋战”。

内存利用特点

内存利用、管理方面有什么特点呢？

建议：

  • 关闭query cache；

  • 采用InnoDB；

  • 采用Percona\MariaDB分支版本；

  • 简单KV数据用NOSQL存储，不使用MySQL。

磁盘的利用特点

最后看下磁盘I/O方面的特点：

建议：

  • 使用多盘提升整体I/O性能；

  • 多使用高速I/O设备；

  • 尽量加大内存，缓解I/O负载。

MySQL 优化

了解完MySQL各方面的特点后，我们可以开始进行优化工作了。

在开始之前，我们需要先明确几点：

  1. 为何而优化？领导指派\用户投诉\监控预警\没事找事？当前跑得好好的话，就没必要折腾神马优化没事找抽，即便想练手，也要悠着点，防止误操作；

  2. 优化的目标是什么，说白了，就是要解决什么瓶颈，切忌在过程中偏离初心；

  3. 计算投入产出比，比如为了让性能提升1%而投入1人月，基本上是非常不划算了，还不如去干点别的；

  4. 优化前做好现场信息采集，优化后再次采集做对比，确认优化成果（用来邀功啊，让老板看到你的成绩，年底加加薪什么的，最起码也能锻炼总结归纳文档能力吧）。

通常，我们进行MySQL优化工作的套路是这样的：

确认需求，先明确当前的运行状态，是否真的需要进行优化，别没事找事；

常见瓶颈：

  • 绝大多数瓶颈在于I/O子系统；

  • 若CPU很高，90%以上是因为索引不当；

  • 发生swap时，可能因为内存分配太小或过大；

  • iowait太高时，想办法从索引角度入手优化，以及提高I/O设备性能，增加内存，减少排序，减少SELECT一次性读取数据量。

常用优化策略

  1. 瞬间并发很高，采用thread pool；

  2. 频繁order by\group by，索引入手；

  3. 适当调整内存，不要太大或太小。一般ibp设置为50% ~ 70%为宜；

  4. iowait高，加内存，提高iops，减少数据读写。

制定方案时，重点解决发生频率高的问题（量变更容易引起质变）；回顾反馈，整理文档，回顾总结，从零散资料中总结出规律，预防风险再次出现。

一般采用下面几个瓶颈分析工具：

绝大多数情况下，有经验的工程师靠sysstat工具集中的就足够了，很多问题一看现象大概就能知道瓶颈何在。

在MySQL层面，有哪些确认瓶颈的手段呢？

硬件、系统优化

我们继续MySQL优化之旅。先来看看从硬件以及OS层面，都有哪些可以优化的。首先主要是BIOS中关于CPU和内存的参数调整，其次是RAID方面的优化。

再来看看几个参考配置图：

1、CPU选择最大性能模式，避免节能模式导致性能不足。

2、关闭NUMA，降低swap概率。


3、采用RAID-10，并且选择FORCE WB。

在OS层面，可以有几个优化手段：

  • 调整IO Scheduler

  • 使用XFS

  • 调整其他内核选项备

备注：

  1. vm.swappiness，降低发生swap的几率；

  2. vm.dirty_background_ratio & vm.dirty_ratio，避免瞬间大量I/O请求导致系统卡死。

从这个压测结果可以看到noop/deadline有明显优势。

这个io scheduler还可以在线修改的哦，还等神马？

echo deadline > /sys/block/sdc/queue/scheduler

在用PCIe SSD设备做测试时，XFS的IOPS能跑到ext4的4倍，表现非常好。

还有什么理由不用XFS呢？

xfs挂载参数：

/dev/sdc1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0

格式化参数不用特别指定，默认的即可。

MySQL配置优化

前面讲到，给MySQL分配的内存不要太大或太小，那么多少合适呢。

首先，要搞清楚MySQL的内存都由哪些部分组成：

  1. global buffers和oracle的SGA一个意思，就是全局一次分配，多个线程间共享。

  2. thread buffers和oracle的PGA一个意思，每个线程单独分配，线程间不能相互共享，因此不要分配过大，避免内存不够使用，发生OOM。

原则： 对这些选项调整时，不要照猫画虎随便调整，要先做到心里有数，了解其具体作用才动手。

看看innodb_flush_log_at_trx_commit分别为0、1、2的性能对比如：


如果再启用binlog后的对比：

最后，再加上sync_binlog选项不同设置的对比：

备注： 这3个测试结果图均来自Percona。

结论&建议：

  1. 想要保证数据安全，就设置 trx_commit =1 & sync_binlog = 1

  2. 在slave上或非关键场景，可以都改成0

SCHEMA设计优化

接下来看看MySQL的模式（SCHEMA）设计优化要点：

要点：

  1. 默认地，使用InnoDB引擎，别上MyISAM给自己找事；

  2. InnoDB必须要有自增（或类似自增）属性的主键；

  3. 不使用或少使用TEXT/BLOB列；

  4. NOT NULL主要是为了优化索引效率；

  5. 若无特殊需求，均可使用latin1字符集，否则用utf8\utf8mb4等大字符集保证通用性。

其他要点：

SQL优化

SQL优化层面有几个要点：


以及 COUNT(*)、大分页 的优化要点：

接下来，我们来看看EXPLAIN的结果中，有哪些关键信息要注意的。首先看下EXPLAIN结果的type列，都可以给我们什么信息：

再看看Extra列有哪些状态要引起重视：

MySQL的慢日志可用下面的工具来进行解析和管理：

pt-query-digest + Box Anemometer的案例，可以对slow log进行便捷管理。

关于JOIN优化有下面的几个关键点：

接下来看看哪些情况下，无法有效使用索引的：

再看看几个杀手级SQL的案例及其优化建议：

在平时，我们登入MySQL服务器后，如果觉得有问题，可以重点关注下面的一些线程状态：

其他优化

关于DBA的利器，常用percona-toolkit工具简介：

附：关于MariaDB及Percona分支版本的简介

posted @ 2018-09-29 19:54  西伯利亚狼520  阅读(120)  评论(0)    收藏  举报