Mysql8.0升级，数据查询效率上涨400%解决高并发难题

 

概述：目前绝大多数网络应用依然再使用Mysql5.7，不过在随着应用覆盖率及应用人群提升，特别是春节等期间等引流活动加持下数据库都撑不过第一轮高并发的压力。目前Mysql8.0的升级版瞬间将同等配置下的数据并发阈值提升到400%。

# 周年庆正版采购有优惠，点击咨询 # # 31款JAVA开发必备控件和工具 #

在Mysql8.0发布的4年后，把Mysql5.7升级至Mysql8.0.19已经是必要之举，截止目前最后一次文件更新是2020年1约12日，关于Mysql8.0的离线文档下载链接点这里。

在Mysql8.0发布之后，阿里云和腾讯云等国内云服务供应商纷纷升级云数据库架构，腾讯云发布了MySQL性能基准测试对比：5.7 VS 8.0 ，以下内容来自于腾讯云社区团队的实测对比。

 

在Oracle MySQL团队的推动下，MySQL 8.0发生了巨大的变化和修改。

物理文件已更改。例如，.frm， .TRG，.TRN和 .par 不再存在。添加了大量的新特性，如通用表表达式(Common Table Expressions CTE)，窗口函数（Window Functions），不可见索引（ Invisible Indexes），正则表达式（regexp） -MySQL8.0现在已经完全支持Unicode，且具有多字节安全特性。数据字典也发生了变化。它现在与一个事务性数据字典合并，该字典存储有关数据库对象的信息。与以前的版本不同，字典数据存储在元数据文件和非事务表中。

安全性得到了改进，caching_sha2_password认证方式取代了之前的mysql_native_password认证方式，成为默认的身份验证方式。它提供了更强大的灵活性，而且也加强了安全性，即它要求必须使用安全连接或通过RSA密钥对实现的支持密码交换的未加密链接。

本次实测中使用了虚拟机，以下为虚拟机的配置参数和Mysql8.0的相关对比。

 

· 存储：gp2（SSD存储，最小100 IOPS，最大16000 IOPS）

· 虚拟CPU：4

· 内存：16GiB

· 系统：centos 7.6

· MySQL5.7版本：MySQLCommunity Server (GPL) 5.7.24

· MySQL8.0版本：MySQLCommunity Server - GPL 8.0.14

在这个基准测试中，我也针对一些参数项的取值进行了配置，它们是：

· innodb_max_dirty_pages_pct= 90 ##这是MySQL 8.0中的默认值。

· innodb_max_dirty_pages_pct_lwm= 10 ##这是MySQL 8.0中的默认值

· innodb_flush_neighbors=0

· innodb_buffer_pool_instances=8

· innodb_buffer_pool_size=8GiB

 

 

Commands and Scripts Used使用的命令和脚本 对于此任务，sysbench用于测试和负载模拟这两个环境。以下测试中使用的命令和脚本：

 

sb-prepare.sh #!/bin/bash host=$1#host192.168.10.110port=3306user='sysbench'password='MysqP@55w0rd'table_size=500000rate=20ps_mode='disable'sysbench/usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --threads=1--max-requests=0 --time=3600 --mysql-host=$host --mysql-user=$user--mysql-password=$password --mysql-port=$port --tables=10 --report-interval=1--skip-trx=on --table-size=$table_size --rate=$rate --db-ps-mode=$ps_modeprepare

运行sh

#!/usr/bin/envbash2     host=$1port=3306user="sysbench"password="MysqP@55w0rd"table_size=100000tables=10rate=20ps_mode='disable'threads=1events=0time=5trx=100path=$PWD  counter=1   echo "thread,cpu" >${host}-cpu.csv   for i in 16 32 64 128 256 512 1024 2048; 
do  threads=$i  mysql -h $host -e"SHOW GLOBAL STATUS" >> $host-global-status.logtmpfile=$path/${host}-tmp${threads}touch $tmpfile/bin/bashcpu-checker.sh $tmpfile $host $threads &  /usr/share/sysbench/oltp_read_write.lua--db-driver=mysql --events=$events --threads=$threads --time=$time--mysql-host=$host --mysql-user=$user --mysql-password=$password--mysql-port=$port --report-interval=1 --skip-trx=on --tables=$tables--table-size=$table_size --rate=$rate --delete_inserts=$trx --order_ranges=$trx--range_selects=on --range-size=$trx --simple_ranges=$trx --db-ps-mode=$ps_mode--mysql-ignore-errors=all run | tee -a $host-sysbench.log   echo"${i},"`cat ${tmpfile} | sort -nr | head -1` >> ${host}-cpu.csvunlink ${tmpfile}   mysql -h $host -e"SHOW GLOBAL STATUS" >> $host-global-status.logdone   python $path/innodb-ops-parser.py $host  mysql -h $host -e "SHOW GLOBALVARIABLES" >> $host-global-vars.log

因此，脚本只是准备sbtestschema并填充表和记录。然后，它使用

/usr/share/sysbench/oltp_read_write.lua脚本执行读/写负载测试。该脚本转储全局状态和MySQL变量，收集CPU利用率，并解析由脚本innodb-ops-parser.py处理的InnoDB行操作。脚本根据基准测试期间收集的转储日志生成* .csv文件，我在这里使用Excel电子表格从* .csv文件生成图表。请检查 github中提交的代码。

现在，让我们继续处理图表结果！

 

InnoDB行操作

 

 

 

基本上在这里，我只提取了InnoDB行操作，它执行查找（读取），删除，插入和更新。当线程数量增加时，MySQL 8.0明显优于MySQL 5.7！在这两个版本中都没有针对配置项进行任何个性化变更，只有我统一配置的参数项。所以这两个版本中的配置几乎都使用默认值。

有趣的是，MySQL团队关于新版本中读写性能的声明，这些图表指出了性能的显著提高，特别是在高负载服务器上。想一下MySQL 5.7和MySQL 8.0在InnoDB行操作上的区别，确实存在有很大的不同，特别是当线程数增加的时候。MySQL 8.0表明，无论工作负载如何，它都能高效地运行。

事务处理

如上图所示，MySQL 8.0的结果趋势显示出其处理事务所需的时间的巨大变化。纵轴数值越低，代表性能越好，意味着处理事务的速度更快。处理的事务统计表（第二张表）还显示出这两个版本处理事务的数量没有差异。这意味着，两个版本处理的事务数量几乎相同，但它们的完成速度不同。虽然MySQL 5.7在较低的负载下可以大量事务，但是实际的负载，特别是在生产中，可能会更高——尤其是在最繁忙的时期。

 

上面的图仍然显示的是两个版本能够处理的事务数量，只是将读和写分离开来。然而，图中实际上是存在异常值，而我没有将这些值包括在内，因为它们是这一小部分异常结果会扭曲图形。

MySQL 8.0体现出一个很大的改进，特别是对于读取。表现在写操作的效率上，特别是对于高工作负载的服务器。在8.0版本中，影响MySQL读取性能的重要新增支持是：可以按降序(或正向索引扫描)创建索引的能力。以前的版本只有升序或反向索引扫描，如果需要降序，MySQL必须执行filesort（如果需要filesort，需要检查max_length_for_sort_data的值）。当最有效的扫描顺序混合某些列的升序和其他列的降序时，降序索引还使优化器可以使用多列索引。有关详细信息，请参见此处。

CPU资源

在此基准测试中，我决定测试一些硬件资源，尤其是CPU利用率。

让我先解释一下如何在基准测试中获取CPU使用率。在对数据库进行基准测试时，sysbench测试结果中不包括在此过程中使用的硬件资源的统计信息。因此，我所做的是通过创建文件的方式来创建标识，通过SSH连接到目标主机，然后用Linux命令“top”收集数据并在测试结束前进行解析，然后再次收集。然后分析出mysqld进程占用最大的CPU使用量，最后删除该标识文件。你可以查看我在github上的代码。

让我们再次讨论图表结果，似乎表明MySQL 8.0消耗了大量的CPU，超过MySQL 5.7。然而，MySQL 8.0可能必须消耗额外的CPU在新的变量配置上。例如，这些变量可能会影响您的MySQL 8.0：

· innodb_log_spin_cpu_abs_lwm = 80

· innodb_log_spin_cpu_pct_hwm = 50

· innodb_log_wait_for_flush_spin_hwm = 400

· innodb_parallel_read_threads = 4

在此基准测试中，具有默认值的变量将保留其默认值。由于MySQL 8.0重新设计了InnoDB写入REDO日志的方式（这是一个改进），前三个变量可配置处理重做日志的使用的CPU资源。例如，变量innodb_log_spin_cpu_pct_hwm具有CPU亲和性，这意味着如果mysqld仅绑定到4个内核，它将忽略其他CPU内核。对于并行读取线程，在MySQL 8.0中添加了一个新变量，您可以调整要使用的线程数。

然而，我没有深入研究这个问题。可以通过利用MySQL8.0提供的特性来提高性能。

结论

MySQL 8.0中有许多改进，与MySQL 5.7相比，MySQL 8.0不仅在处理读负载时，而且在读写混合的高负载下的性能都取得了令人瞩目的进步。

在Mysql8.0 升级之后，如果开发人员和DBA期望获得更好的数据库管理体验，建议立即更新您的数据库管理工具，小编在这里为大家推荐：

1. 具备好看的UI和管理体验的Navicat15！Navicat的上一版本号是Nacicat12，Navicat和Mysql都采用了跨版本号的发布方式，实在是让小编感到很微妙！点此下载Navicat15。

Navicat for MySQL 是管理和开发 MySQL 或 MariaDB 的理想解决方案。它是一套单一的应用程序，能同时连接 MySQL 和 MariaDB 数据库，并与 Amazon RDS、Amazon Aurora、Oracle Cloud、Microsoft Azure、阿里云、腾讯云和华为云等云数据库兼容。这套全面的前端工具为数据库管理、开发和维护提供了一款直观而强大的图形界面。

2. 专业的研发与DBA首选管理工具dbForge Studio for MySQL，一个通用的GUI工具，用于管理，开发和管理MySQL和MariaDB数据库。它提供了内置的数据和模式比较和同步工具，数据库报告工具以及许多工具，可以简化数据库开发过程。点击这里下载最新版dbForge Studio for MySQL。

 

Mysql8.0发行时间表：

Changes in MySQL 8.0.20 (Not yet released, General Availability)

Changes in MySQL 8.0.19 (2020-01-13, General Availability)

Changes in MySQL 8.0.18 (2019-10-14, General Availability)

Changes in MySQL 8.0.17 (2019-07-22, General Availability)

Changes in MySQL 8.0.16 (2019-04-25, General Availability)

Changes in MySQL 8.0.15 (2019-02-01, General Availability)

Changes in MySQL 8.0.14 (2019-01-21, General Availability)

Changes in MySQL 8.0.13 (2018-10-22, General Availability)

Changes in MySQL 8.0.12 (2018-07-27, General Availability)

Changes in MySQL 8.0.11 (2018-04-19, General Availability)

Changes in MySQL 8.0.5 - 8.0.10 (Skipped version numbers)

Changes in MySQL 8.0.4 (2018-01-23, Release Candidate)

Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)

Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)

Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)

Changes in MySQL 8.0.0 (2016-09-12, Development Milestone)