做网站框架搭建的人,百度门店推广,视频网站开发策划书,帝国cms 制作网站地图文章目录 Mysql全局优化总结配置文件my.ini或my.cnf的全局参数最大连接数允许用户连接的最大数量MySQL能够暂存的连接数量JDBC连接空闲等待时长client连接空闲等待时长innodb线程并发数innodb存储引擎buffer pool缓存大小行锁锁定时间redo log写入策略binlog写入磁盘机制排序线… 文章目录 Mysql全局优化总结配置文件my.ini或my.cnf的全局参数最大连接数允许用户连接的最大数量MySQL能够暂存的连接数量JDBC连接空闲等待时长client连接空闲等待时长innodb线程并发数innodb存储引擎buffer pool缓存大小行锁锁定时间redo log写入策略binlog写入磁盘机制排序线程缓冲区分配大小表关联缓存的大小 Mysql 8.0新特性详解1、新增降序索引2、group by 不再隐式排序3、增加隐藏索引4、新增函数索引5、innodb存储引擎select for update跳过锁等待6、新增innodb_dedicated_server自适应参数7、死锁检查控制8、undo文件不再使用系统表空间9、 binlog日志过期时间精确到秒10、窗口函数(Window Functions)也称分析函数专用窗口函数 11、默认字符集由latin1变为utf8mb412、MyISAM系统表全部换成InnoDB表13、元数据存储变动14、自增变量持久化15、DDL原子化16、参数修改持久化 Mysql全局优化总结 从上图可以看出SQL及索引的优化效果是最好的而且成本最低所以工作中我们要在这块花更多时间。
配置文件my.ini或my.cnf的全局参数
假设服务器配置为 CPU32核 内存64G DISK2T SSD 下面参数都是服务端参数默认在配置文件的 [mysqld] 标签下
最大连接数
max_connections3000连接的创建和销毁都需要系统资源比如内存、文件句柄业务说的支持多少并发指的是每秒请求数也就是QPS。 一个连接最少占用内存是256K最大是64M如果一个连接的请求数据超过64MB比如排序就会申请临时空间放到硬盘上。 如果3000个用户同时连上mysql最小需要内存3000256KB750M最大需要内存300064MB192G。 如果innodb_buffer_pool_size是40GB给操作系统分配4G给连接使用的最大内存不到20G如果连接过多使用的内存超过20G将会产生磁盘SWAP此时将会影响性能。 连接数过高不一定带来吞吐量的提高而且可能占用更多的系统资源。
允许用户连接的最大数量
max_user_connections2980指允许用户连接的最大数量剩余连接数用作DBA管理。
MySQL能够暂存的连接数量
back_log300如果MySQL的连接数达到max_connections时新的请求将会被存在堆栈中等待某一连接释放资源该堆栈数量即back_log如果等待连接的数量超过back_log将被拒绝。
JDBC连接空闲等待时长
wait_timeout300指的是app应用通过jdbc连接mysql进行操作完毕后空闲300秒后断开默认是28800单位秒即8个小时。
client连接空闲等待时长
interactive_timeout300指的是mysql client连接mysql进行操作完毕后空闲300秒后断开默认是28800单位秒即8个小时。
innodb线程并发数
innodb_thread_concurrency64此参数用来设置innodb线程的并发数默认值为0表示不被限制若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍如果超过配置并发数则需要排队这个值不宜太大不然可能会导致线程之间锁争用严重影响性能。
innodb存储引擎buffer pool缓存大小
innodb_buffer_pool_size40Ginnodb存储引擎buffer pool缓存大小一般为物理内存的60%-70%。
行锁锁定时间
innodb_lock_wait_timeout10行锁锁定时间默认50s根据公司业务定没有标准值。
redo log写入策略
innodb_flush_log_at_trx_commit1设置为0表示每次事务提交时都只是把 redo log 留在 redo log buffer 中数据库宕机可能会丢失数据。 设置为1(默认值)表示每次事务提交时都将 redo log 直接持久化到磁盘数据最安全不会因为数据库宕机丢失数据但是效率稍微差一点线上系统推荐这个设置。 设置为2表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里这种情况如果数据库宕机是不会丢失数据的但是操作系统如果宕机了page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。 参看上期
binlog写入磁盘机制
sync_binlog1binlog写入磁盘机制主要通过 sync_binlog 参数控制默认值是 0。 为0的时候表示每次提交事务都只 write 到page cache由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升但是机器宕机page cache里面的 binlog 会丢失。 也可以设置为1表示每次提交事务都会执行 fsync 写入磁盘这种方式最安全。 还有一种折中方式可以设置为N(N1)表示每次提交事务都write 到page cache但累积N个事务后才 fsync 写入磁盘这种如果机器宕机会丢失N个事务的binlog。 参看上期
排序线程缓冲区分配大小
sort_buffer_size4M每个需要排序的线程分配该大小的一个缓冲区。增加该值可以加速ORDER BY 或 GROUP BY操作。 sort_buffer_size是一个connection级的参数在每个connectionsession第一次需要使用这个buffer的时候一次性分配设置的内存。 sort_buffer_size并不是越大越好由于是connection级的参数过大的设置高并发可能会耗尽系统的内存资源。例如500个连接将会消耗500*sort_buffer_size(4M)2G。
表关联缓存的大小
join_buffer_size4M用于表关联缓存的大小和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。
Mysql 8.0新特性详解 建议使用8.0.17及之后的版本更新的内容比较多。
1、新增降序索引
MySQL在语法上很早就已经支持降序索引但实际上创建的仍然是升序索引。 如下MySQL 5.7 所示c2字段降序但是从show create table看c2仍然是升序。 MySQL 8.0可以看到c2字段降序。只有Innodb存储引擎支持降序索引。
# MySQL 5.7演示
mysql create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.04 sec)mysql insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);
Query OK, 5 rows affected (0.02 sec)mysql show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE t1 (c1 int(11) DEFAULT NULL,c2 int(11) DEFAULT NULL,KEY idx_c1_c2 (c1,c2) --注意这里c2字段是升序
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)mysql explain select * from t1 order by c1,c2 desc; --5.7也会使用索引但是Extra字段里有filesort文件排序
---------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
---------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)# MySQL 8.0演示
mysql create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
Query OK, 0 rows affected (0.02 sec)mysql insert into t1 (c1,c2) values(1, 10),(2,50),(3,50),(4,100),(5,80);
Query OK, 5 rows affected (0.02 sec)mysql show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE t1 (c1 int DEFAULT NULL,c2 int DEFAULT NULL,KEY idx_c1_c2 (c1,c2 DESC) --注意这里的区别降序索引生效了
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)mysql explain select * from t1 order by c1,c2 desc; --Extra字段里没有filesort文件排序充分利用了降序索引
-----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index |
-----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain select * from t1 order by c1 desc,c2; --Extra字段里有Backward index scan意思是反向扫描索引;
--------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Backward index scan; Using index |
--------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain select * from t1 order by c1 desc,c2 desc; --Extra字段里有filesort文件排序排序必须按照每个字段定义的排序或按相反顺序才能充分利用索引
---------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
---------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain select * from t1 order by c1,c2; --Extra字段里有filesort文件排序排序必须按照每个字段定义的排序或按相反顺序才能充分利用索引
---------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 1 | 100.00 | Using index; Using filesort |
---------------------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)2、group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序如需要排序必须显式加上order by 子句。
# MySQL 5.7演示
mysql select count(*),c2 from t1 group by c2;
----------------
| count(*) | c2 |
----------------
| 1 | 10 |
| 2 | 50 |
| 1 | 80 |
| 1 | 100 |
----------------
4 rows in set (0.00 sec)# MySQL 8.0演示
mysql select count(*),c2 from t1 group by c2; --8.0版本group by不再默认排序
----------------
| count(*) | c2 |
----------------
| 1 | 10 |
| 2 | 50 |
| 1 | 100 |
| 1 | 80 |
----------------
4 rows in set (0.00 sec)mysql select count(*),c2 from t1 group by c2 order by c2; --8.0版本group by不再默认排序需要自己加order by
----------------
| count(*) | c2 |
----------------
| 1 | 10 |
| 2 | 50 |
| 1 | 80 |
| 1 | 100 |
----------------
4 rows in set (0.00 sec)3、增加隐藏索引
使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见但是数据库后台还是会维护隐藏索引的在查询时优化器不使用该索引即使用force index优化器也不会使用该索引同时优化器也不会报索引不存在的错误因为索引仍然真实存在必要时也可以把隐藏索引快速恢复成可见。注意主键不能设置为 invisible。 软删除就可以使用隐藏索引比如我们觉得某个索引没用了删除后发现这个索引在某些时候还是有用的于是又得把这个索引加回来如果表数据量很大的话这种操作耗费时间是很多的成本很高这时我们可以将索引先设置为隐藏索引等到真的确认索引没用了再删除。
# 创建t2表里面的c2字段为隐藏索引
mysql create table t2(c1 int, c2 int, index idx_c1(c1), index idx_c2(c2) invisible);
Query OK, 0 rows affected (0.02 sec)mysql show index from t2\G
*************************** 1. row ***************************Table: t2Non_unique: 1Key_name: idx_c1Seq_in_index: 1Column_name: c1Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t2Non_unique: 1Key_name: idx_c2Seq_in_index: 1Column_name: c2Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: NO --隐藏索引不可见Expression: NULL
2 rows in set (0.00 sec)mysql explain select * from t2 where c11;
--------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t2 | NULL | ref | idx_c1 | idx_c1 | 5 | const | 1 | 100.00 | NULL |
--------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain select * from t2 where c21; --隐藏索引c2不会被使用
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql select optimizer_switch\G --查看各种参数
*************************** 1. row ***************************
optimizer_switch: index_mergeon,index_merge_unionon,index_merge_sort_unionon,index_merge_intersectionon,engine_condition_pushdownon,index_condition_pushdownon,mrron,mrr_cost_basedon,block_nested_loopon,batched_key_accessoff,materializationon,semijoinon,loosescanon,firstmatchon,duplicateweedouton,subquery_materialization_cost_basedon,use_index_extensionson,condition_fanout_filteron,derived_mergeon,use_invisible_indexesoff,skip_scanon,hash_joinon
1 row in set (0.00 sec)mysql set session optimizer_switchuse_invisible_indexeson; ----在会话级别设置查询优化器可以看到隐藏索引
Query OK, 0 rows affected (0.00 sec)mysql select optimizer_switch\G
*************************** 1. row ***************************
optimizer_switch: index_mergeon,index_merge_unionon,index_merge_sort_unionon,index_merge_intersectionon,engine_condition_pushdownon,index_condition_pushdownon,mrron,mrr_cost_basedon,block_nested_loopon,batched_key_accessoff,materializationon,semijoinon,loosescanon,firstmatchon,duplicateweedouton,subquery_materialization_cost_basedon,use_index_extensionson,condition_fanout_filteron,derived_mergeon,use_invisible_indexeson,skip_scanon,hash_joinon
1 row in set (0.00 sec)mysql explain select * from t2 where c21;
--------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
--------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t2 | NULL | ref | idx_c2 | idx_c2 | 5 | const | 1 | 100.00 | NULL |
--------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql alter table t2 alter index idx_c2 visible;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql alter table t2 alter index idx_c2 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 04、新增函数索引
之前我们知道如果在查询中加入了函数索引不生效所以MySQL 8引入了函数索引MySQL 8.0.13开始支持在索引中使用函数(表达式)的值。 函数索引基于虚拟列功能实现在MySQL中相当于新增了一个列这个列会根据你的函数来进行计算结果然后使用函数索引的时候就会用这个计算后的列作为索引。
mysql create table t3(c1 varchar(10),c2 varchar(10));
Query OK, 0 rows affected (0.02 sec)mysql create index idx_c1 on t3(c1); --创建普通索引
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql create index func_idx on t3((UPPER(c2))); --创建一个大写的函数索引
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql show index from t3\G
*************************** 1. row ***************************Table: t3Non_unique: 1Key_name: idx_c1Seq_in_index: 1Column_name: c1Collation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: t3Non_unique: 1Key_name: func_idxSeq_in_index: 1Column_name: NULLCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: upper(c2) --函数表达式
2 rows in set (0.00 sec)mysql explain select * from t3 where upper(c1)ZHUGE;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql explain select * from t3 where upper(c2)ZHUGE; --使用了函数索引
----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | t3 | NULL | ref | func_idx | func_idx | 43 | const | 1 | 100.00 | NULL |
----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)5、innodb存储引擎select for update跳过锁等待
对于select … for share(8.0新增加查询共享锁的语法)或 select … for update 在语句后面添加NOWAIT、SKIP LOCKED语法可以跳过锁等待或者跳过锁定。 在5.7及之前的版本select…for update如果获取不到锁会一直等待直到innodb_lock_wait_timeout超时。 在8.0版本通过添加nowaitskip locked语法能够立即返回。如果查询的行已经加锁那么nowait会立即报错返回而skip locked也会立即返回只是返回的结果中不包含被锁定的行。 应用场景比如查询余票记录如果某些记录已经被锁定用skip locked可以跳过被锁定的记录只返回没有锁定的记录提高系统性能。
# 先打开一个session1:
mysql select * from t1;
------------
| c1 | c2 |
------------
| 1 | 10 |
| 2 | 50 |
| 3 | 50 |
| 4 | 100 |
| 5 | 80 |
------------
5 rows in set (0.00 sec)mysql begin;
Query OK, 0 rows affected (0.00 sec)mysql update t1 set c2 60 where c1 2; --锁定第二条记录
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0# 另外一个session2:
mysql select * from t1 where c1 2 for update; --等待超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql select * from t1 where c1 2 for update nowait; --查询立即返回
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.mysql select * from t1 for update skip locked; --查询立即返回过滤掉了第二行记录
------------
| c1 | c2 |
------------
| 1 | 10 |
| 3 | 50 |
| 4 | 100 |
| 5 | 80 |
------------
4 rows in set (0.00 sec)6、新增innodb_dedicated_server自适应参数
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_sizeinnodb_log_file_size等参数会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题前提是服务器是专用来给MySQL数据库的如果还有其他软件或者资源或者多实例MySQL使用不建议开启该参数不然会影响其它程序。
mysql show variables like %innodb_dedicated_server%; --默认是OFF关闭修改为ON打开
--------------------------------
| Variable_name | Value |
--------------------------------
| innodb_dedicated_server | OFF |
--------------------------------
1 row in set (0.02 sec)7、死锁检查控制
MySQL 8.0 MySQL 5.7.15增加了一个新的动态变量 innodb_deadlock_detect用于控制系统是否执行 InnoDB 死锁检查默认是打开的。死锁检测会耗费数据库性能的对于高并发的系统我们可以关闭死锁检测功能提高系统性能。但是我们要确保系统极少情况会发生死锁同时要将锁等待超时参数调小一点以防出现死锁等待过久的情况。
mysql show variables like %innodb_deadlock_detect%; --默认是打开的
-------------------------------
| Variable_name | Value |
-------------------------------
| innodb_deadlock_detect | ON |
-------------------------------
1 row in set, 1 warning (0.01 sec)8、undo文件不再使用系统表空间
默认创建2个UNDO表空间不再使用系统表空间。
9、 binlog日志过期时间精确到秒
之前是天并且参数名称发生变化. 在8.0版本之前binlog日志过期时间设置都是设置expire_logs_days参数而在8.0版本中MySQL默认使用binlog_expire_logs_seconds参数。
10、窗口函数(Window Functions)也称分析函数
从 MySQL 8.0 开始新增了一个叫窗口函数的概念它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似在聚合函数后面加上over()就变成窗口函数了在括号里可以加上partition by等分组关键字指定如何分组窗口函数即便分组也不会将多行查询结果合并为一行而是将结果放回多行当中即窗口函数不需要再使用 GROUP BY。
# 创建一张账户余额表
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)
) ENGINEInnoDB# 插入一些示例数据
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (1, zhuge, wx, 100);
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (2, zhuge, alipay, 200);
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (3, zhuge, yinhang, 300);
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (4, lilei, wx, 200);
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (5, lilei, alipay, 100);
INSERT INTO test.account_channel (id, name, channel, balance) VALUES (6, hanmeimei, wx, 500);mysql select * from account_channel;
---------------------------------
| id | name | channel | balance |
---------------------------------
| 1 | zhuge | wx | 100 |
| 2 | zhuge | alipay | 200 |
| 3 | zhuge | yinhang | 300 |
| 4 | lilei | wx | 200 |
| 5 | lilei | alipay | 100 |
| 6 | hanmeimei | wx | 500 |
---------------------------------
6 rows in set (0.00 sec)mysql select name,sum(balance) from account_channel group by name;
-------------------------
| name | sum(balance) |
-------------------------
| zhuge | 600 |
| lilei | 300 |
| hanmeimei | 500 |
-------------------------
3 rows in set (0.00 sec)# 在聚合函数后面加上over()就变成分析函数了后面可以不用再加group by制定分组因为在over里已经用partition关键字指明了如何分组计算这种可以保留原有表数据的结构不会像分组聚合函数那样每组只返回一条数据
mysql select name,channel,balance,sum(balance) over(partition by name) as sum_balance from account_channel;
------------------------------------------
| name | channel | balance | sum_balance |
------------------------------------------
| hanmeimei | wx | 500 | 500 |
| lilei | wx | 200 | 300 |
| lilei | alipay | 100 | 300 |
| zhuge | wx | 100 | 600 |
| zhuge | alipay | 200 | 600 |
| zhuge | yinhang | 300 | 600 |
------------------------------------------
6 rows in set (0.00 sec)mysql select name,channel,balance,sum(balance) over(partition by name order by balance) as sum_balance from account_channel;
------------------------------------------
| name | channel | balance | sum_balance |
------------------------------------------
| hanmeimei | wx | 500 | 500 |
| lilei | alipay | 100 | 100 |
| lilei | wx | 200 | 300 |
| zhuge | wx | 100 | 100 |
| zhuge | alipay | 200 | 300 |
| zhuge | yinhang | 300 | 600 |
------------------------------------------
6 rows in set (0.00 sec)# over()里如果不加条件则默认使用整个表的数据做运算
mysql select name,channel,balance,sum(balance) over() as sum_balance from account_channel;
------------------------------------------
| name | channel | balance | sum_balance |
------------------------------------------
| zhuge | wx | 100 | 1400 |
| zhuge | alipay | 200 | 1400 |
| zhuge | yinhang | 300 | 1400 |
| lilei | wx | 200 | 1400 |
| lilei | alipay | 100 | 1400 |
| hanmeimei | wx | 500 | 1400 |
------------------------------------------
6 rows in set (0.00 sec)mysql select name,channel,balance,avg(balance) over(partition by name) as avg_balance from account_channel;
------------------------------------------
| name | channel | balance | avg_balance |
------------------------------------------
| hanmeimei | wx | 500 | 500.0000 |
| lilei | wx | 200 | 150.0000 |
| lilei | alipay | 100 | 150.0000 |
| zhuge | wx | 100 | 200.0000 |
| zhuge | alipay | 200 | 200.0000 |
| zhuge | yinhang | 300 | 200.0000 |
------------------------------------------
6 rows in set (0.00 sec)专用窗口函数
序号函数ROW_NUMBER()、RANK()、DENSE_RANK()分布函数PERCENT_RANK()、CUME_DIST()前后函数LAG()、LEAD()头尾函数FIRST_VALUE()、LAST_VALUE()其它函数NTH_VALUE()、NTILE()
# 按照balance字段排序展示序号
mysql select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel;
------------------------------------------
| name | channel | balance | row_number1 |
------------------------------------------
| zhuge | wx | 100 | 1 |
| lilei | alipay | 100 | 2 |
| zhuge | alipay | 200 | 3 |
| lilei | wx | 200 | 4 |
| zhuge | yinhang | 300 | 5 |
| hanmeimei | wx | 500 | 6 |
------------------------------------------
6 rows in set (0.00 sec)# 按照balance字段排序first_value()选出排第一的余额
mysql select name,channel,balance,first_value(balance) over(order by balance) as first1 from account_channel;
-------------------------------------
| name | channel | balance | first1 |
-------------------------------------
| zhuge | wx | 100 | 100 |
| lilei | alipay | 100 | 100 |
| zhuge | alipay | 200 | 100 |
| lilei | wx | 200 | 100 |
| zhuge | yinhang | 300 | 100 |
| hanmeimei | wx | 500 | 100 |
-------------------------------------
6 rows in set (0.01 sec)11、默认字符集由latin1变为utf8mb4
在8.0版本之前默认字符集为latin1utf8指向的是utf8mb38.0版本默认字符集为utf8mb4utf8默认指向的也是utf8mb4。
12、MyISAM系统表全部换成InnoDB表
将系统表(mysql)和数据字典表全部改为InnoDB存储引擎默认的MySQL实例将不包含MyISAM表除非手动创建MyISAM表。
13、元数据存储变动
MySQL 8.0删除了之前版本的元数据文件例如表结构.frm等文件全部集中放入mysql.ibd文件里。可以看见下图test库文件夹里已经没有了frm文件。
14、自增变量持久化
在8.0之前的版本自增主键AUTO_INCREMENT的值如果大于max(primary key)1在MySQL重启后会重置AUTO_INCREMENTmax(primary key)1这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id199)一直到8.0才被解决8.0版本将会对AUTO_INCREMENT值进行持久化MySQL重启后该值将不会改变。
# MySQL 5.7演示
mysql create table t(id int auto_increment primary key,c1 varchar(20));
Query OK, 0 rows affected (0.03 sec)mysql insert into t(c1) values(zhuge1),(zhuge2),(zhuge3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from t;
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
| 3 | zhuge3 |
------------
3 rows in set (0.00 sec)mysql delete from t where id 3;
Query OK, 1 row affected (0.01 sec)mysql select * from t;
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
------------
2 rows in set (0.00 sec)mysql exit;
Bye# 重启MySQL服务并重新连接MySQL
mysql insert into t(c1) values(zhuge4);
Query OK, 1 row affected (0.01 sec)mysql select * from t;
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
| 3 | zhuge4 |
------------
3 rows in set (0.00 sec)mysql update t set id 5 where c1 zhuge1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from t;
------------
| id | c1 |
------------
| 2 | zhuge2 |
| 3 | zhuge4 |
| 5 | zhuge1 |
------------
3 rows in set (0.00 sec)mysql insert into t(c1) values(zhuge5);
Query OK, 1 row affected (0.01 sec)mysql select * from t;
------------
| id | c1 |
------------
| 2 | zhuge2 |
| 3 | zhuge4 |
| 4 | zhuge5 |
| 5 | zhuge1 |
------------
4 rows in set (0.00 sec)mysql insert into t(c1) values(zhuge6);
ERROR 1062 (23000): Duplicate entry 5 for key PRIMARY# MySQL 8.0演示
mysql create table t(id int auto_increment primary key,c1 varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql insert into t(c1) values(zhuge1),(zhuge2),(zhuge3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql select * from t;
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
| 3 | zhuge3 |
------------
3 rows in set (0.00 sec)mysql delete from t where id 3;
Query OK, 1 row affected (0.01 sec)mysql select * from t;
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
------------
2 rows in set (0.00 sec)mysql exit;
Bye
[rootlocalhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL... SUCCESS! # 重新连接MySQL
mysql insert into t(c1) values(zhuge4);
Query OK, 1 row affected (0.00 sec)mysql select * from t; --生成的id为4不是3
------------
| id | c1 |
------------
| 1 | zhuge1 |
| 2 | zhuge2 |
| 4 | zhuge4 |
------------
3 rows in set (0.00 sec)mysql update t set id 5 where c1 zhuge1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from t;
------------
| id | c1 |
------------
| 2 | zhuge2 |
| 4 | zhuge4 |
| 5 | zhuge1 |
------------
3 rows in set (0.00 sec)mysql insert into t(c1) values(zhuge5);
Query OK, 1 row affected (0.00 sec)mysql select * from t;
------------
| id | c1 |
------------
| 2 | zhuge2 |
| 4 | zhuge4 |
| 5 | zhuge1 |
| 6 | zhuge5 |
------------
4 rows in set (0.00 sec)15、DDL原子化
InnoDB表的DDL支持事务完整性要么成功要么回滚。 MySQL 8.0 开始支持原子 DDL 操作其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括更新数据字典存储引擎层的操作在 binlog 中记录 DDL 操作。支持与表相关的 DDL数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。支持的其它 DDL 存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER 语句。支持账户管理相关的 DDL用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等。
# MySQL 5.7
mysql show tables;
----------------
| Tables_in_test |
----------------
| account |
| actor |
| employee |
| film |
| film_actor |
| leaf_id |
| t1 |
| test_innodb |
| test_myisam |
| test_order_id |
----------------
10 rows in set (0.01 sec)mysql drop table t1,t2; //删除表报错不会回滚t1表会被删除
ERROR 1051 (42S02): Unknown table test.t2
mysql show tables;
----------------
| Tables_in_test |
----------------
| account |
| actor |
| employee |
| film |
| film_actor |
| leaf_id |
| test_innodb |
| test_myisam |
| test_order_id |
----------------
9 rows in set (0.00 sec)# MySQL 8.0
mysql show tables;
----------------
| Tables_in_test |
----------------
| account |
| actor |
| employee |
| film |
| film_actor |
| leaf_id |
| t1 |
| test_innodb |
| test_myisam |
| test_order_id |
----------------
10 rows in set (0.00 sec)mysql drop table t1,t2; //删除表报错会回滚t1表依然还在
ERROR 1051 (42S02): Unknown table test.t2
mysql show tables;
----------------
| Tables_in_test |
----------------
| account |
| actor |
| employee |
| film |
| film_actor |
| leaf_id |
| t1 |
| test_innodb |
| test_myisam |
| test_order_id |
----------------
10 rows in set (0.00 sec)16、参数修改持久化
MySQL 8.0版本支持在线修改全局参数并持久化通过加上PERSIST关键字可以将修改的参数持久化到新的配置文件mysqld-auto.cnf中重启MySQL时可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。
mysql set persist innodb_lock_wait_timeout25;
--系统会在数据目录下生成一个包含json格式的mysqld-auto.cnf 的文件。
--格式化后如下所示当my.cnf 和mysqld-auto.cnf 同时存在时后者具有更高优先级。{Version: 1,mysql_server: {innodb_lock_wait_timeout: {Value: 25,Metadata: {Timestamp: 1675290252103863,User: root,Host: localhost}}}
}
