同样的sql语句,为什么从mysql5.5升级到mysql8.0后,高并发下效率会降低呢
原先是springboot1.5.22+mysql5.5 现在是springboot2.6.10+mysql8.0
mysql配置文件已经做了优化处理了.连接池用的druid连接池
以下是我现在的mysql配置和druid连接池配置
#数据库配置
spring.datasource.url=jdbc:mysql://192.168.160.123:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
db.type=MySQL
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=600
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.druid.WebStatFilter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
-----------------------------------------------------------------
mysql的my.cnf配置参数如下
[client]
port=3306
socket=/var/opt/mitec/mysql/mysql.sock
[mysqld]
log-bin=mysql-bin
datadir=/var/opt/mitec/mysql
socket=/var/opt/mitec/mysql/mysql.sock
lower_case_table_names = 1
server-id=1
skip_ssl
character-set-server = utf8
character-set-filesystem = utf8
init_connect='SET NAMES utf8'
default-time_zone = '+8:00'
default-storage-engine=INNODB
log-error=/var/opt/mitec/mysql/mi_error.log
general_log=OFF
general_log_file=/var/opt/mitec/mysql/mysql.log
server-id= 1
expire_logs_days = 30
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 16M
innodb_log_file_size = 64M
max_connections = 15000
slow_query_log = 0
binlog_format=mixed
lower_case_table_names = 1
back_log = 600
open_files_limit = 65535
binlog_cache_size = 1M
max_heap_table_size = 16M
tmp_table_size = 16M
read_rnd_buffer_size = 8M
table_open_cache = 2048
max_allowed_packet = 32M
read_buffer_size = 6M
read_rnd_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size=64
key_buffer_size = 384M
sort_buffer_size = 6M
wait_timeout = 38400
innodb_file_per_table = 0
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
collation-server=utf8_general_ci
default_authentication_plugin=mysql_native_password
[mysqld_safe]
MySQL8和MySQL5.7在单条主键更新的场景下几乎无差别。（平均慢0.08毫秒我觉得这个可以视为无差别）之前30%的慢是因为操作系统版本有差异，现在几乎一致是因为两个操作系统都是一致的。至于为什么会反馈有的时候会慢3倍，我觉得主要是因为数据库整体运行状态。如果数据库处于亚健康的会，产生的问题会是连锁的。而CPU超过50%的时候数据库就是亚健康状态的。我之前的模拟都是两个静态数据库的前提条件下，正式环境如果SQL质量过关大部分数据库应该是近似空载状态，那么是可以接近于我这个压测的状态。（压测为虚拟机本地磁盘，生产环境数据库必须是SSD）。所以归根结蒂，还是要提高SQL质量，让数据库处于健康状态才能有更好的并发。
参考：http://blog.itpub.net/637517/viewspace-2865297/
建议你去了解他们的各自性能优劣；
参考：
https://zhuanlan.zhihu.com/p/356456604
是谁告诉你版本越高效率也越高了
高版本是在高硬件性能的前提下设计出来的，你硬件不变，把软件从低版本换成高版本，效率必然降低
相应的是功能增多了
如果多增加的功能你用不上，那就别升级