Mysql 8.0新特性详解
参数修改持久化
set persist innodb_lock_wait_timeout=25;
#这条命令会在安装目录生成一个json文件/var/lib/mysql/mysqld-auto.cnf
#通过mysqld --help --verbose 找到mysql加载配置文件
#Default options are read from the following files in the given order:/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
ln -s /var/lib/mysql/mysqld-auto.cnf /etc/mysql/mysql.conf.d/mysqld-auto.cnf
#重启mysql服务生效
systemctl restart mysql
新增降序索引
示例语句：create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
增加隐藏索引
#创建示例
create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);
#让隐藏索引生效
set session optimizer_switch="use_invisible_indexes=on"; ----在会话级别设置查询优化器可以看到隐藏索引
#改变隐藏状态语句示例
alter table t2 alter index idx_c2 visible;
alter table t2 alter index idx_c2 invisible;
新增函数索引
示例语句： create index func_idx on t3((UPPER(c2))); --创建一个大写的函数索引
依然遵循索引原则。
函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。
innodb存储引擎select for update跳过锁等待
在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。
应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。
select * from t1 for update skip locked; --查询立即返回，过滤掉了第二行记录
新增innodb_dedicated_server自适应参数
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。
解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序。
show variables like '%innodb_dedicated_server%';
死锁检查控制
死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。
但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。
show variables like '%innodb_deadlock_detect%'; --默认是打开的
undo文件不再使用系统表空间
默认创建2个UNDO表空间，不再使用系统表空间。
binlog日志过期时间精确到秒
在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数。
在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。
窗口函数(Window Functions)：也称分析函数
从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。
窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。
可以节省了一些程序中的操作，还是自己的程序好控制些。
专用窗口函数：
- 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
- 分布函数：PERCENT_RANK()、CUME_DIST()
- 前后函数：LAG()、LEAD()
- 头尾函数：FIRST_VALUE()、LAST_VALUE()
- 其它函数：NTH_VALUE()、NTILE()
select name,channel,balance,sum(balance) over(partition by name order by balance) as sum_balance from account_channel;
select name,channel,balance,first_value(balance) over(order by balance) as first1 from account_channel;
默认字符集由latin1变为utf8mb4
MyISAM系统表全部换成InnoDB表
元数据存储变动
MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。
自增变量持久化
在8.0之前的版本，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(bugs.mysql.com/bug.php?id=…
DDL原子化
# MySQL 5.7
drop table t1,t2; //删除表报错不会回滚，t1表会被删除
ERROR 1051 (42S02): Unknown table 'test.t2'
# MySQL 8.0
mysql> drop table t1,t2; //删除表报错会回滚，t1表依然还在
ERROR 1051 (42S02): Unknown table 'test.t2'
Mysql全局优化总结
从上图可以看出SQL及索引的优化效果是最好的，而且成本最低，所以工作中我们要在这块花更多时间。
补充一点配置文件my.ini或my.cnf的全局参数
[mysqld]
#一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
#如果3000个用户同时连上mysql，最小需要内存3000*256KB=750M，最大需要内存3000*64MB=192G。
max_connections=105
#允许用户连接的最大数量，剩余连接数用作DBA管理
max_user_connections=100
#MySQL能够暂存的连接数量。如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝。
back_log=20
#指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
wait_timeout=300
#指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时
interactive_timeout=1800
#此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能。
innodb_thread_concurrency=64
#innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。
#SET PERSIST innodb_buffer_pool_size = 2*1024*1024*1024;
innodb_buffer_pool_size=2G
#行锁锁定时间，默认50s，根据公司业务定，没有标准值。
innodb_lock_wait_timeout=10
#redo log 的写入磁盘策略
innodb_flush_log_at_trx_commit=1
#binlog写入磁盘策略
sync_binlog=1
#每个需要排序的线程分配该大小的一个缓冲区。增加该值可以加速ORDER BY 或 GROUP BY操作。
#并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(4M)=2G。
sort_buffer_size=4M
