MySQL 数据库,基本上运维都会接触到,而对于数据库的性能优化,想必是大家比较关注的,下面分享一些调优技巧。
在系统层面,调整硬件和软件选项以提高 MySQL 性能。
如果使用的是传统的 HDD,可以升级到 SSD。
使用 sysstat 工具包中的 iotop、sar 监控输入/输出速率,如果磁盘使用率远远高于其他资源,请考虑添加更多存储或升级到更快的存储。
通常认识处理器是衡量系统速度的指标,使用 top 命令可以查看关于 CPU 详细的使用信息,注意 MySQL 进程及其CPU 使用率。
top - 10:41:15 up 205 days, 23:03, 1 user, load average: 1.07, 1.02, 0.98
Tasks: 424 total, 1 running, 423 sleeping, 0 stopped, 0 zombie
%Cpu(s): 8.1 us, 0.5 sy, 0.0 ni, 91.1 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 13145304+total, 59450800 free, 53483316 used, 18518920 buff/cache
KiB Swap: 16777212 total, 16546300 free, 230912 used. 76746272 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7133 mysql 20 0 103.1g 45.5G 10148 S 205.6 36.3 94658:39 mysqld
6754 mongod 20 0 4504956 3.2g 11872 S 64.8 2.6 1916:34 mongod
29632 root 20 0 36.5g 506904 6884 S 0.0 0.4 154:00.13 JAVA
15795 gdm 20 0 5776296 199816 15260 S 0.0 0.2 72:39.84 gnome-shell
31684 root 20 0 353572 98864 1308 S 1.9 0.1 386:24.16 redis-server
31686 root 20 0 320804 98496 1108 S 1.9 0.1 288:01.69 redis-server
31688 root 20 0 326948 97988 1108 S 0.0 0.1 288:37.66 redis-server
16576 gdm 20 0 701592 60952 856 S 3.7 0.0 2688:28 gsd-color
20199 root 20 0 292132 39696 1212 S 0.0 0.0 483:14.41 redis-server
6437 root 20 0 84744 39352 39100 S 0.0 0.0 1:16.05 systemd-journal
17429 root 20 0 123500 30252 4668 S 0.0 0.0 4476:21 node_exporter
12977 root 20 0 411672 25976 21820 S 0.0 0.0 8:10.97 rsyslogd
12480 polkitd 20 0 620076 8848 2552 S 0.0 0.0 9:42.49 polkitd
30362 root 20 0 350468 6728 4636 S 0.0 0.0 0:00.01 abrt-dbus
13071 root 20 0 435300 5552 3940 S 0.0 0.0 2:36.75 X
30333 root 20 0 116212 4728 3680 S 0.0 0.0 0:00.02 sshd
12491 root 20 0 519268 3872 1984 S 0.0 0.0 63:00.71 udisksd
16584 gdm 20 0 1069144 3688 1248 S 0.0 0.0 0:31.78 gsd-media-keys
16544 root 20 0 398376 3612 1124 S 0.0 0.0 0:34.98 boltd
CPU 升级成本更高,但是如果已经成为瓶颈了,可能还是需要升级的。
内存表示 MySQL 服务器中的内存总量。可以调整内存缓存(下面细说),以提高性能。但是如果没有足够的内存,或者现有内存没有经过优化,那就不要乱来
与其他资源一样,如果服务器不断耗尽内存,可以通过添加内存升级。如果内存不足,服务器将缓存数据存储到硬盘上,降低性能。
监控网络流量,确保基础架构没问题。
网络过载可能导致延迟,数据包丢失甚至服务中断。
确保有足够的网络带宽来容纳正常水平的数据库流量。
Myisam 是 MySQL 5.1 及之前的版本使用的存储引擎,不支持事务以及行级锁,而且一个毫无疑问的缺陷是崩溃后无法安全恢复,效率也较低。InnoDB 是 MySQL 5.5 版本以后的默认存储引擎,InnoDB 是为 Mysql 处理巨大数据量时的最大性能设计。它的 CPU 效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大的网站或是应用中 Innodb 是倍受青睐的。
InnoDB 使用聚集索引并将数据保留在页面中,这些页面存储在连续的物理块中。 如果一个页面的值太大,InnoDB会将其移动到另一个位置,然后索引该值。 此功能有助于将相关数据保存在存储设备上的同一位置,这意味着物理硬盘驱动器需要较少的时间来访问数据。
如果由于历史遗留问题,只能用旧版本的 MySQL,那没办法。但是只要有可能,都用最新版的 MySQL,因为新版性能更好,高级特效更多。
这些方法涉及调整 MySQL 配置文件,编写更高效的 SQL 语句以及创建索引等等
注意:调整时,一次只进行一个更改,进行测试,然后进行下一个。如果一次调整很多,出现性能下降或故障,排查问题不好下手。
和大多数工具一样, 不一定适用所有的 MySQL 版本。下面提供几个工具来评估 MySQL 数据库及提供更改建议。
MySQL Tuning Primer 这个工具使用于 MySQL >= 5.5 <= 5.7, 它可以分析数据库并建议设置以提高性能。 例如,如果感觉系统处理查询较慢,则建议提高 query_cache_size 参数。
MySQLTuner-perl 使用 Perl 编写的脚本,可快速查看 MySQL 安装并进行调整以提高性能和稳定性。 检索当前配置变量和状态数据,并以简短的格式显示一些基本的性能建议。
该脚本适用于 MySQL 多个版本。
日志文件建议是列表中的第一条建议,在底部,则可以看到有关提高MySQL性能的一般建议。
查询是用于搜索数据库中与特定值匹配的数据的编码请求。有些查询需要很长时间才能返回结果。如果您的环境依赖于自动查询,它们可能会影响性能,检查并及时终止堆积的 MySQL 进程。
查询经常使用类似于以下的结构:
SELECT … WHERE
这些查询涉及评估,过滤和检索结果。 可以通过为相关表添加索引,加快查询速度。
避免在谓词查询中使用函数,如:
SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'
UPPER 创建一个函数,这个函数必须在 SELECT 运行期间进行操作,这会使查询正在做的工作加倍,尽量避免。
通配符%表示零个或多个字符。 如果查询如下所示:
SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'
表达式 "%123" 包含所有以 123 结尾的值。% 符号代表任何类型或数量的前面字符,包括任何字符。 这样必须执行全表扫描,尽量避免
一种常见的表达方式是使用 SELECT *扫描所有数据库列。 如果指定所需的列,则查询将不需要扫描不相关的列。
按指定的列对结果进行排序。可用于一次按两列排序。这些应该以相同的顺序(升序或降序)进行排序。如果尝试按不同顺序对不同的列进行排序,则会降低性能。可以将其与索引结合使用以加快排序速度。
尽可能尝试使用内部联接。 外部联接将查看指定列之外的其他数据。 如果您需要这些数据,那很好,但是包含不必要的数据会浪费性能。 查询有时包含UNION和DISTINCT命令。 就像外部联接一样,可以在必要时使用这些表达式。 但是,它们增加了数据库的其他排序和读取。 如果您不需要它们,最好找到一个更有效的表达方式。
将 EXPLAIN 表达式附加到查询的开头将读取并评估查询。 如果表达式效率低下或结构混乱,EXPLAIN 可以帮助您找到它们。 然后,您可以调整查询的措词,以避免意外的表扫描或其他性能影响。
此配置涉及更改 my.cnf 文件。 请谨慎操作,并一次进行较小的更改。
现在,您应该知道如何提高 MySQL 性能和调整数据库。
查找瓶颈(硬件和软件)、执行更多工作所需的查询,并考虑使用自动化工具和EXPLAIN 函数来评估数据库。