026、Mysql优化

优化思路

mysql优化的金字塔模型

mysql优化可以从以下几个层面进行：

硬件角度优化
系统维度优化
数据库配置参数
设计过程

mysql的特点：

支持事务
实现高并发
应用、安装简单
维护成本低
管理便捷
性能卓越，服务稳定

优化思路：

发现问题的过程
锁定瓶颈点
优化方案的初定
方案的实施
做好记录，避免问题的再次发生

例如，打开网页缓慢，优化思路：

问清楚，这一现象是什么时候开始的。若刚发生，可能是业务高峰期。若持续了几天，则可能是历史问题。

锁定瓶颈的过程：

操作系统层：top、vmstat、sar

数据库层：

show engine innodb status \G;
查看slow log
show global status like '%cache%';
show full processlist
pt-ioprofile，操作系统直接执行

操作系统层

查看CPU使用情况

1,5,15分钟的CPU负载：

CPU使用率：

当cpu使用率在60%以上时，就需要关注了。

sy:系统本身占用，尽量不要超过5%，若超过，可能系统CPU存在一些瓶颈。

us：用户占用的CPU，若超过40%，可能存在问题。

查看IO使用情况

执行以下命令：

[root@localhost ~]# iostat -x -m
Linux 3.10.0-1127.el7.x86_64 (localhost.localdomain)    2021年05月17日  _x86_64_        (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.77    0.00    1.66    0.88    0.00   96.70

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.01     0.20    5.15    1.86     0.17     0.02    54.31     0.12   16.69   13.00   26.95   3.25   2.28
dm-0              0.00     0.00    3.93    1.22     0.14     0.01    61.09     0.13   25.34   16.27   54.58   4.13   2.13
dm-1              0.00     0.00    0.08    0.00     0.00     0.00    50.09     0.00    3.93    3.93    0.00   3.48   0.03
dm-2              0.00     0.00    0.57    0.14     0.02     0.00    57.35     0.00    4.60    4.48    5.08   3.36   0.24
--先看%util，代表当前设备的繁忙程度
--r/s     w/s，两个值加起来代表IOPS，请求数量。
--rMB/s    wMB/s，两个值加起来代表吞吐量。

有一种情况：IO很高%util，IOPS和吞吐量很低。原因是：IO的随机读写太多了，可能是SQL语句写的不好，导致全表扫描。

查看内存使用情况

主要查看是否用到swap空间：

[root@localhost ~]# vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0  79412   1080 199512    0    0    41     7  205  206  1  2 97  0  0
 0  0      0  79308   1080 199548    0    0     0     0  316  313  1  1 99  0  0
 --r下面的值若超过CPU核数，代表CPU遇到瓶颈，需要添加CPU
 --b代表IO的等待

对于swap空间使用比的参数：

[root@localhost ~]# cat /proc/sys/vm/swappiness 
30
--两个极端：0和100，0代表不使用swap，100代表不使用内存
[root@localhost ~]# cat /sys/block/sda/queue/scheduler 
noop [deadline] cfq
--以上三个参数，[]中的参数生效，首选deadline，其次noop
--IO调度一般选择deadline，即快速响应请求

因为一旦使用swap，表明内存不够使用，不仅会影响程序恨性能，程序早晚会崩溃，倒不如不让程序使用swap空间，所以该参数可以设置为0。

还可以使用以下命令查看cpu、内存、IO：

[root@localhost ~]# sar -u
Linux 3.10.0-1127.el7.x86_64 (localhost.localdomain)    07/29/2021      _x86_64_        (2 CPU)

09:03:36 AM       LINUX RESTART

09:10:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
09:20:01 AM     all      0.04      0.00      0.11      0.00      0.00     99.85
09:30:02 AM     all      0.02      0.00      0.04      0.00      0.00     99.94
09:40:01 AM     all      0.03      0.00      0.06      0.07      0.00     99.84
09:50:01 AM     all      0.03      0.00      0.05      0.00      0.00     99.92
10:00:01 AM     all      0.02      0.00      0.05      0.00      0.00     99.93
10:10:01 AM     all      0.06      0.00      0.08      0.04      0.00     99.82
10:20:01 AM     all      0.03      0.00      0.05      0.01      0.00     99.91
Average:        all      0.03      0.00      0.06      0.02      0.00     99.89
[root@localhost ~]# sar -r
Linux 3.10.0-1127.el7.x86_64 (localhost.localdomain)    07/29/2021      _x86_64_        (2 CPU)

09:03:36 AM       LINUX RESTART

09:10:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
09:20:01 AM    160504    854104     84.18      1080    146612   1996496     38.33    632332    120880         8
09:30:02 AM    160348    854260     84.20      1080    146784   1996496     38.33    632804    120512         8
09:40:01 AM    144768    869840     85.73      1080    151208   2030768     38.99    643332    121888        28
09:50:01 AM    143464    871144     85.86      1080    151368   2006776     38.53    643712    121864        12
10:00:01 AM    143440    871168     85.86      1080    151512   2006776     38.53    643860    121872         8
10:10:01 AM    125252    889356     87.66      1080    156304   2043668     39.23    654632    125172        52
10:20:01 AM    129648    884960     87.22      1080    156524   2014200     38.67    652084    124184         4
Average:       143918    870690     85.82      1080    151473   2013597     38.66    643251    122339        17
[root@localhost ~]# sar -d
Linux 3.10.0-1127.el7.x86_64 (localhost.localdomain)    07/29/2021      _x86_64_        (2 CPU)

09:03:36 AM       LINUX RESTART

09:10:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
09:20:01 AM    dev8-0      0.16      0.32      1.72     12.38      0.00      8.06      4.63      0.08
09:20:01 AM  dev253-0      0.13      0.32      1.72     15.52      0.00     10.16      5.80      0.08
09:20:01 AM  dev253-1      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
09:20:01 AM  dev253-2      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
……