一、my.ini或my.cnf的全局参数

 一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。

#最大连接数
max_connections=3000
#最大用户连接数
max_user_connections=2980

如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝。

back_log=300

指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。

wait_timeout=300

指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。

interactive_timeout=300

此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能。

innodb_thread_concurrency=64

 innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。

innodb_buffer_pool_size=40G

行锁锁定时间，默认50s，根据公司业务定，没有标准值。

innodb_lock_wait_timeout=10

#连接级别参数 一个connection消耗4M内存
sort_buffer_size=4M
join_buffer_size=4M

#redo log直接写入磁盘
innodb_flush_log_at_trx_commit=1
#binlog直接同步到磁盘
sync_binlog=1

二、mysql8.0新特性详解

1、新增降序索引

create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));#c2为降序排列
insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);

explain select * from t1 order by c1,c2 desc;
explain select * from t1 order by c1 desc,c2;
explain select * from t1 order by c1 desc,c2 desc;
explain select * from t1 order by c1,c2;

 第一条sql就是用到降序索引，第二条sql是反向扫描索引，第三、四条sql没有按照索引相同或相反方向排序，所以是文件排序

2、group by不再隐式排序

mysql8.0之前的版本都会根据group by的字段做排序，mysql8.0则不会， 排序需要自己指定排序列

3、增加隐藏索引

有时候我们想删除索引，而又不确定索引是不是真的没地方用到了，可以先设置索引不可见

create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);

explain select * from t2 where c1=1;
explain select * from t2 where c2=1;

 不可见索引字段c2没用到索引

set session optimizer_switch="use_invisible_indexes=on";#在会话级别设置查询优化器可以看到隐藏索引

 再次执行使用c2列作为条件查询就可以用到索引

explain select * from t2 where c2=1;

 4、新增函数索引

create table t3(c1 varchar(10),c2 varchar(10));
create index idx_c1 on t3(c1);     #创建普通索引
create index func_idx on t3((UPPER(c2)));  #创建一个大写的函数索引

explain select * from t3 where upper(c1)='TEST';
explain select * from t3 where upper(c2)='ZHUGE';  #使用了函数索引

 5、innodb存储引擎select for update跳过锁等待

 

6、新增innodb_dedicated_server自适应参数

mysql开启此参数会检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会占用其它应用的内存资源

show variables like '%innodb_dedicated_server%';   #默认是OFF关闭，修改为ON打开

7、死锁检查控制

show variables like '%innodb_deadlock_detect%';  #默认是打开的

8、undo文件不再使用系统表空间

默认创建2个UNDO表空间，不再使用系统表空间。

9、 binlog日志过期时间精确到秒

之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

10、窗口函数(Window Functions)：也称分析函数

# 创建一张账户余额表
CREATE TABLE `account_channel` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',
  `channel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '账户渠道',
  `balance` int DEFAULT NULL COMMENT '余额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

# 插入一些示例数据
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('1', 'test', 'wx', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('2', 'test', 'alipay', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('3', 'test', 'yinhang', '300');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('4', 'test1', 'wx', '200');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('5', 'test1', 'alipay', '100');
INSERT INTO `test`.`account_channel` (`id`, `name`, `channel`, `balance`) VALUES ('6', 'test2', 'wx', '500');

现在表数据是这样的

 

一般情况下查询是这样的

 

用窗口函数查询 

 

11、专用窗口函数

• 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
• 分布函数：PERCENT_RANK()、CUME_DIST()
• 前后函数：LAG()、LEAD()
• 头尾函数：FIRST_VALUE()、LAST_VALUE()
• 其它函数：NTH_VALUE()、NTILE()

12、默认字符集由latin1变为utf8mb4

在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4。

13、MyISAM系统表全部换成InnoDB表

将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。

14、元数据存储变动

MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。可以看见下图test库文件夹里已经没有了frm文件。

15、自增变量持久化

自增id情况下，比如当前最大id是8，这时候把id为1的记录id值改成9，再次新增数据时，mysql8.0之前会报错，mysql8.0才解决这个问题

16、DDL原子化

InnoDB表的DDL支持事务完整性，要么成功要么回滚。

比如drop table t1,t2;语句，t2表不存在，在mysql8.0之前就把t1表删除了，mysql8.0会回滚，t1表还在

17、参数修改持久化

 MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。

set persist innodb_lock_wait_timeout=25;#系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld-auto.cnf 同时存在时，后者具有更高优先级。