Server層，選項(xiàng)持久化

mysql> show variables like '%max_connections%';+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| max_connections | 512 |

| mysqlx_max_connections | 100 |

+------------------------+-------+

2 rows in set (0.00sec)

mysql> set persist max_connections=8000;

Query OK,0 rows affected (0.00sec)

mysql> show variables like '%max_connections%';+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| max_connections | 8000 |

| mysqlx_max_connections | 100 |

+------------------------+-------+

2 rows in set (0.00sec)

[root@mydb1~]# cat /app/mysqldata/3306/data/mysqld-auto.cnf

{"Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "8000" , "Metadata" : { "Timestamp" : 1570677819639469 , "User" : "dba_user" , "Host" : "localhost"} } } }

[root@mydb1~]# cat /app/mysqldata/3306/my.cnf | grepmax_connections

max_connections= 512查看配置文件，仍然是之前的配置max_connections= 512mysql>restart;

mysql> show variables like '%max_connections%';+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| max_connections | 8000 |

| mysqlx_max_connections | 100 |

+------------------------+-------+

2 rows in set (0.01 sec)

set persist更改運(yùn)行時(shí)變量值，將變量設(shè)置寫入mysqld-auto.cnf數(shù)據(jù)目錄中指定的選項(xiàng)文件 。數(shù)據(jù)庫(kù)啟動(dòng)時(shí)，會(huì)首先讀取其它配置文件my.cnf，最后才讀取mysqld-auto.cnf文件。

不建議手動(dòng)修改該文件，其有可能導(dǎo)致數(shù)據(jù)庫(kù)在啟動(dòng)過(guò)程中因解析錯(cuò)誤而失敗。如果出現(xiàn)這種情況，可手動(dòng)刪除mysqld-auto.cnf文件或?qū)ersisted_globals_load變量設(shè)置為off來(lái)避免該文件的加載

對(duì)于已經(jīng)持久化了變量，可通過(guò)reset persist命令取消持久

注意，其只是清空mysqld-auto.cnf和performance_schema.persisted_variables中的內(nèi)容，對(duì)于已經(jīng)修改了的變量的值，不會(huì)產(chǎn)生任何影響。

但是對(duì)于read only 的參數(shù)，修改參數(shù)后需要重啟才能生效

修改read only的變量需要額外的特權(quán):

SYSTEM_VARIABLES_ADMIN

PERSIST_RO_VARIABLES_ADMIN

mysql> show variables like 'innodb_log_file_size';+----------------------+------------+

| Variable_name | Value |

+----------------------+------------+

| innodb_log_file_size | 1147483648 |

+----------------------+------------+

1 row in set (0.00sec)

mysql> set persist innodb_log_file_size=2147483648;

ERROR1238 (HY000): Variable 'innodb_log_file_size'is a read only variable

mysql> set persist_only innodb_log_file_size=2147483648;

Query OK,0 rows affected (0.00sec)

mysql>restart;

Query OK,0 rows affected (0.00sec)

mysql> show variables like 'innodb_log_file_size';+----------------------+------------+

| Variable_name | Value |

+----------------------+------------+

| innodb_log_file_size | 2147483648 |

+----------------------+------------+

1 row in set (0.00sec)

[root@mydb1~]# cat /app/mysqldata/3306/data/mysqld-auto.cnf

{"Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "8000" , "Metadata" : { "Timestamp" : 1570677819639469 , "User" : "dba_user" , "Host" : "localhost" } } , "mysql_server_static_options" : { "innodb_log_file_size" : { "Value" : "2147483648" , "Metadata" : { "Timestamp" : 1570679652100887 , "User" : "dba_user" , "Host" : "localhost"} } } } }

mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'\G*************************** 1. row ***************************VARIABLE_NAME: innodb_log_file_size

VARIABLE_SOURCE: PERSISTED

VARIABLE_PATH:/app/mysqldata/3306/data/mysqld-auto.cnf

MIN_VALUE:4194304MAX_VALUE:18446744073709551615SET_TIME:2019-10-10 11:54:12.100887SET_USER: dba_user

SET_HOST: localhost*************************** 2. row ***************************VARIABLE_NAME: max_connections

VARIABLE_SOURCE: PERSISTED

VARIABLE_PATH:/app/mysqldata/3306/data/mysqld-auto.cnf

MIN_VALUE:1MAX_VALUE:100000SET_TIME:2019-10-10 11:23:39.639469SET_USER: dba_user

SET_HOST: localhost2 rows in set (0.01 sec)

數(shù)據(jù)字典

(1)新版本之前的數(shù)據(jù)字典

數(shù)據(jù)字典是數(shù)據(jù)庫(kù)重要的組成部分之一，那么什么是數(shù)據(jù)字典？數(shù)據(jù)字典包含哪些內(nèi)容呢？數(shù)據(jù)字典是對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)、庫(kù)對(duì)象、表對(duì)象等的元信息的集合。在MySQL中，數(shù)據(jù)字典信息內(nèi)容就包括表結(jié)構(gòu)、數(shù)據(jù)庫(kù)名或表名、字段的數(shù)據(jù)類型、視圖、索引、表字段信息、存儲(chǔ)過(guò)程、觸發(fā)器等內(nèi)容。MySQL INFORMATION_SCHEMA庫(kù)提供了對(duì)數(shù)據(jù)局元數(shù)據(jù)、統(tǒng)計(jì)信息、以及有關(guān)MySQL server的訪問(wèn)信息(例如：數(shù)據(jù)庫(kù)名或表名，字段的數(shù)據(jù)類型和訪問(wèn)權(quán)限等)。該庫(kù)中保存的信息也可以稱為MySQL的數(shù)據(jù)字典。

在MySQL8.0之前，MySQL的數(shù)據(jù)字典信息，并沒(méi)有全部存放在系統(tǒng)數(shù)據(jù)庫(kù)表中，部分?jǐn)?shù)據(jù)庫(kù)數(shù)據(jù)字典信息存放于文件中，其余的數(shù)據(jù)字典信息存放于數(shù)據(jù)字典庫(kù)中(INFORMATION_SCHEMA,mysql,sys)。例如表結(jié)構(gòu)信息存放在.frm文件中,數(shù)據(jù)庫(kù)表字段信息存放于INFORMATION_SCHEMA下的COLUMNS表中。早期，5.6版本之前，MyISAM是MySQL的默認(rèn)存儲(chǔ)引擎，而作為MyISAM存儲(chǔ)引擎，它是沒(méi)有數(shù)據(jù)字典的。只有表結(jié)構(gòu)信息記錄在.frm文件中。MySQL5.6版本之后，將InnoDB存儲(chǔ)引擎作為默認(rèn)的存儲(chǔ)引擎。在InnoDB存儲(chǔ)引擎中，添加了一些數(shù)據(jù)字典文件用于存放數(shù)據(jù)字典元信息，例如：.opt文件，記錄了每個(gè)庫(kù)的一些基本信息，包括庫(kù)的字符集等信息，.TRN，.TRG文件用于存放觸發(fā)器的信息內(nèi)容。

(2)新版本數(shù)據(jù)字典的改進(jìn)

最新的MySQL 8.0 發(fā)布之后，對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)字典方面做了較大的改進(jìn)。

首先是，將所有原先存放于數(shù)據(jù)字典文件中的信息，全部存放到數(shù)據(jù)庫(kù)系統(tǒng)表中，即將之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了，不再通過(guò)文件的方式存儲(chǔ)數(shù)據(jù)字典信息。

其次是對(duì)INFORMATION_SCHEM，mysql，sys系統(tǒng)庫(kù)中的存儲(chǔ)引擎做了改進(jìn)，原先使用MyISAM存儲(chǔ)引擎的數(shù)據(jù)字典表都改為使用InnoDB存儲(chǔ)引擎存放。從不支持事務(wù)的MyISAM存儲(chǔ)引擎轉(zhuǎn)變到支持事務(wù)的InnoDB存儲(chǔ)引擎，為原子DDL的實(shí)現(xiàn)，提供了可能性。

新數(shù)據(jù)字典帶來(lái)的影響

(1)INFORMATION_SCHEMA性能提升

8.0中對(duì)數(shù)據(jù)字典進(jìn)行改進(jìn)之后，很大程度上提高了對(duì)INFORMATIONS_SCHEMA的查詢性能，通過(guò)可以通過(guò)查表快速的獲得想要查詢的數(shù)據(jù)，原因是：

數(shù)據(jù)庫(kù)在查詢INFORMATION_SCHEMA的表時(shí)，不再一定需要?jiǎng)?chuàng)建一張臨時(shí)表，可以直接查詢數(shù)據(jù)字典表。在之前版本中，數(shù)據(jù)字典信息不一定是存放于表中，所以在獲取數(shù)據(jù)字典信息時(shí)候，不僅僅是查表操作。例如讀取數(shù)據(jù)庫(kù)表結(jié)構(gòu)信息，底層其實(shí)是讀取.frm文件來(lái)獲得，是一個(gè)文件打開讀取的操作。而在新版本中，數(shù)據(jù)字典信息都可以通過(guò)直接查表的方式獲取，替代那些獲取信息慢的方式。

對(duì)存儲(chǔ)引擎的改進(jìn)之后，在查詢INFORMATIONS_SCHEMA表時(shí)，如果表上有索引，優(yōu)化器會(huì)合理的利用索引。

對(duì)于INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通過(guò)緩存的方式，以提高查詢的性能。可以通過(guò)設(shè)置information_schema_stats_expiry參數(shù)設(shè)置緩存數(shù)據(jù)的過(guò)期時(shí)間，默認(rèn)是86400秒。查詢這兩張表的數(shù)據(jù)的時(shí)候，首先是到緩存中進(jìn)行查詢，緩存中沒(méi)有緩存數(shù)據(jù)，或者緩存數(shù)據(jù)過(guò)期了，查詢會(huì)從存儲(chǔ)引擎中獲取最新的數(shù)據(jù)。如果需要獲取最新的數(shù)據(jù)，可以通過(guò)設(shè)置information_schema_stats_expiry參數(shù)為0或者ANALYZE TABLE操作。

(2)原子DDL

MySQL8.0開始支持原子DDL操作，一個(gè)原子DDL操作，具體的操作內(nèi)容包括：數(shù)據(jù)字典更新，存儲(chǔ)引擎層的操作，在binlog中記錄DDL操作。并且這些操作都是原子性的，表示中間過(guò)程出現(xiàn)錯(cuò)誤的時(shí)候，是可以完整回退的。這在之前版本的DDL操作中是不支持的。之前數(shù)據(jù)庫(kù)版本中一直沒(méi)有支持原子DDL的特性，是有原因的，因?yàn)樵谠缙诘臄?shù)據(jù)庫(kù)版本中，數(shù)據(jù)庫(kù)元信息存放于元信息文件中、非事務(wù)性表中以及特定存儲(chǔ)引擎的數(shù)據(jù)字典中。這些都無(wú)法保證DDL操作內(nèi)容在一個(gè)事務(wù)當(dāng)中，無(wú)法保證原子性。具體的原子DDL，后續(xù)會(huì)有專門的文章。

(3)innodb_read_only對(duì)所有存儲(chǔ)引擎生效

在8.0之前版本中，innodb_read_only參數(shù)可以阻止對(duì)InnoDB存儲(chǔ)引擎表的create和drop等更新操作。但是在MySQL8.0中，開啟innodb_read_only參數(shù)阻止了所有存儲(chǔ)引擎的這些操作。create或者drop表的操作都需要更新數(shù)據(jù)字典表，8.0中這個(gè)數(shù)據(jù)字典表都改為了InnoDB存儲(chǔ)引擎，所以對(duì)于數(shù)據(jù)字典表的更新會(huì)失敗，從而導(dǎo)致各存儲(chǔ)引擎create和drop表失敗。同樣的像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name這種操作也會(huì)失敗，因?yàn)檫@些操作都要去更新數(shù)據(jù)字典表。

(4)mysqldump mysqlpump導(dǎo)出的內(nèi)容影響

MySQL8.0之后，在使用mysqldump和mysqlpump導(dǎo)出數(shù)據(jù)時(shí)候，與之前有了一些不同，主要是以下幾點(diǎn)：

之前版本的mysqldump和mysqlpump可以導(dǎo)出mysql系統(tǒng)庫(kù)中的所有表的內(nèi)容，8.0之后，只能導(dǎo)出mysql系統(tǒng)庫(kù)中沒(méi)有數(shù)據(jù)的數(shù)據(jù)字典表。

之前版本當(dāng)使用 --all-databases 參數(shù)導(dǎo)出數(shù)據(jù)的時(shí)候，不加 --routines和 --events選項(xiàng)也可以導(dǎo)出觸發(fā)器、存儲(chǔ)過(guò)程等信息，因?yàn)檫@些信息都存放于proc和event表中，導(dǎo)出所有表即可導(dǎo)出這些信息。但是在8.0中，proc表和event表都不再使用，并且定義觸發(fā)器、存儲(chǔ)過(guò)程的數(shù)據(jù)字典表不會(huì)被導(dǎo)出，所以在8.0中使用mysqldump、mysqlpump導(dǎo)出數(shù)據(jù)的時(shí)候，如果需要導(dǎo)出觸發(fā)器、存儲(chǔ)過(guò)程等內(nèi)容，一定需要加上 --routines和 --events選項(xiàng)。

之前版本中 --routines選項(xiàng)導(dǎo)出的時(shí)候，備份賬戶需要有proc表的SELECT權(quán)限，在8.0中需要對(duì)所有表的SELECT權(quán)限

之前版本中，導(dǎo)出觸發(fā)器、存儲(chǔ)過(guò)程可以同時(shí)導(dǎo)出觸發(fā)器、存儲(chǔ)過(guò)程的創(chuàng)建和修改的時(shí)間戳，8.0中不再支持。

(5)新數(shù)據(jù)字典的局限性

MySQL8.0數(shù)據(jù)字典的改進(jìn)有很多方便的特性，例如帶來(lái)了原子DDL，提升了INFORMATION_SCHEMA的查詢性能等，但是它并不是完美的，新版數(shù)據(jù)字典還是存在一些局限性：

通過(guò)手動(dòng)mkdir的方式在數(shù)據(jù)目錄下創(chuàng)建庫(kù)目錄，這種方式是不會(huì)被數(shù)據(jù)庫(kù)所識(shí)別到。DDL操作會(huì)花費(fèi)更長(zhǎng)的時(shí)間，因?yàn)橹暗腄DL操作是直接對(duì).frm文件進(jìn)行更改操作，只要寫一個(gè)文件，現(xiàn)在是需要更新數(shù)據(jù)字典表，代表著需要將數(shù)據(jù)寫到存儲(chǔ)引擎、read log、undo log中

auto-inc持久化

自增主鍵沒(méi)有持久化是個(gè)比較早的bug，歷史悠久且臭名昭著。

首先，直觀的重現(xiàn)下。

mysql> create table t1(id intauto_increment primary key);

Query OK,0 rows affected (0.01sec)

mysql> insert into t1 values(null),(null),(null);

Query OK,3 rows affected (0.01sec)

Records:3 Duplicates: 0 Warnings: 0mysql> select *from t1;+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+rowsin set (0.00sec)

mysql> delete from t1 where id=3;

Query OK,1 row affected (0.36sec)

mysql> insert into t1 values(null);

Query OK,1 row affected (0.35sec)

mysql> select *from t1;+----+

| id |

+----+

| 1 |

| 2 |

| 4 |

+----+rowsin set (0.01 sec)

雖然id為3的記錄刪除了，但再次插入null值時(shí)，并沒(méi)有重用被刪除的3，而是分配了4。

刪除id為4的記錄，重啟數(shù)據(jù)庫(kù)，重新插入一個(gè)null值。

mysql> delete from t1 where id=4;

# service mysqld restart

mysql> insert into t1 values(null);

Query OK,1 row affected (0.00sec)

mysql> select *from t1;+----+

| id |

+----+

| 1 |

| 2 |

| 3 |

+----+rowsin set (0.00 sec)

可以看到，新插入的null值分配的是3，按照重啟前的操作邏輯，此處應(yīng)該分配5啊。

這就是自增主鍵沒(méi)有持久化的bug。究其原因，在于自增主鍵的分配，是由InnoDB數(shù)據(jù)字典內(nèi)部一個(gè)計(jì)數(shù)器來(lái)決定的，而該計(jì)數(shù)器只在內(nèi)存中維護(hù)，并不會(huì)持久化到磁盤中。當(dāng)數(shù)據(jù)庫(kù)重啟時(shí)，該計(jì)數(shù)器會(huì)通過(guò)下面這種方式初始化。

SELECT?MAX(ai_col)FROM?table_nameFOR?UPDATE;

MySQL 8.0的解決思路

將自增主鍵的計(jì)數(shù)器持久化到redo log中。每次計(jì)數(shù)器發(fā)生改變，都會(huì)將其寫入到redo log中。如果數(shù)據(jù)庫(kù)發(fā)生重啟，InnoDB會(huì)根據(jù)redo log中的計(jì)數(shù)器信息來(lái)初始化其內(nèi)存值。為了盡量減小對(duì)系統(tǒng)性能的影響，計(jì)數(shù)器寫入到redo log中，并不會(huì)馬上刷新。

窗口函數(shù)、通用表達(dá)式

對(duì)于窗口函數(shù)，比如row_number()，rank()，dense_rank()，NTILE()，PERCENT_RANK()等等，在MSSQL和Oracle以及PostgreSQL，使用的語(yǔ)法和表達(dá)的邏輯，基本上完全一致。?在MySQL 8.0之后就放心的用吧。

測(cè)試case，簡(jiǎn)單模擬一個(gè)訂單表，字段分別是訂單號(hào)，用戶編號(hào)，金額，創(chuàng)建時(shí)間

create table order_info

(

order_idintprimary key,

user_no varchar(10),

amountint,

create_date datetime

);

insert into order_info values (1,'u0001',100,'2018-1-1');

insert into order_info values (2,'u0001',300,'2018-1-2');

insert into order_info values (3,'u0001',300,'2018-1-2');

insert into order_info values (4,'u0001',800,'2018-1-10');

insert into order_info values (5,'u0001',900,'2018-1-20');

insert into order_info values (6,'u0002',500,'2018-1-5');

insert into order_info values (7,'u0002',600,'2018-1-6');

insert into order_info values (8,'u0002',300,'2018-1-10');

insert into order_info values (9,'u0002',800,'2018-1-16');

insert into order_info values (10,'u0002',800,'2018-1-22');

采用新的窗口函數(shù)的方法，就是使用row_number() over (partition by user_no order by create_date desc) as row_num 給原始記錄編一個(gè)號(hào)，然后取第一個(gè)編號(hào)的數(shù)據(jù)，自然就是“用戶的最新的一條訂單”，實(shí)現(xiàn)邏輯上清晰了很多，代碼也簡(jiǎn)潔，可讀了很多。

需要注意的是，MySQL中的使用窗口函數(shù)的時(shí)候，是不允許使用*的，必須顯式指定每一個(gè)字段。

row_number()

(分組)排序編號(hào)，正如上面的例子， row_number()over(partition by user_no order by create_date desc) as row_num，按照用戶分組，按照create_date排序，對(duì)已有數(shù)據(jù)生成一個(gè)編號(hào)。

當(dāng)然也可以不分組，對(duì)整體進(jìn)行排序。任何一個(gè)窗口函數(shù)，都可以分組統(tǒng)計(jì)或者不分組統(tǒng)計(jì)(也即可以不要partition by ***都可以，看你的需求了)

rank()

類似于 row_number()，也是排序功能，但是rank()有什么不一樣？新的事物的出現(xiàn)必然是為了解決潛在的問(wèn)題。

如果再往測(cè)試表中寫入一條數(shù)據(jù)：insert into order_info values (11,'u0002',800,'2018-1-22');

對(duì)于測(cè)試表中的U002用戶來(lái)說(shuō)，有兩條create_date完全一樣的數(shù)據(jù)(假設(shè)有這樣的數(shù)據(jù))，那么在row_number()編號(hào)的時(shí)候，這兩條數(shù)據(jù)卻被編了兩個(gè)不同的號(hào)

理論上講，這兩條的數(shù)據(jù)的排名是并列最新的。因此rank()就是為了解決這個(gè)問(wèn)題的，也即：排序條件一樣的情況下，其編號(hào)也一樣。

dense_rank()

dense_rank()的出現(xiàn)是為了解決rank()編號(hào)存在的問(wèn)題的，

rank()編號(hào)的時(shí)候存在跳號(hào)的問(wèn)題，如果有兩個(gè)并列第1，那么下一個(gè)名次的編號(hào)就是3，結(jié)果就是沒(méi)有編號(hào)為2的數(shù)據(jù)。

如果不想跳號(hào)，可以使用dense_rank()替代。

avg，sum等聚合函數(shù)在窗口函數(shù)中的的增強(qiáng)

可以在聚合函數(shù)中使用窗口功能，比如sum(amount)over(partition by user_no order by create_date) as sum_amont，達(dá)到一個(gè)累積計(jì)算sum的功能

這種需求在沒(méi)有窗口函數(shù)的情況下，用純sql寫起來(lái)，也夠蛋疼的了，就不舉例了。

NTILE(N) 將數(shù)據(jù)按照某些排序分成N組

舉個(gè)簡(jiǎn)單的例子，按照分?jǐn)?shù)線的倒序排列，將學(xué)生成績(jī)分成上中下3組，可以得到哪個(gè)程序數(shù)據(jù)上中下三個(gè)組中哪一部分，就可以使用NTILE(3) 來(lái)實(shí)現(xiàn)。這種需求倒是用的不是非常多。

如下還是使用上面的表，按照時(shí)間將user_no = 'u0002'的訂單按照時(shí)間的緯度，劃分為3組，看每一行數(shù)據(jù)數(shù)據(jù)哪一組。

first_value(column_name) and last_value(column_name)

first_value和last_value基本上見名知意了，就是取某一組數(shù)據(jù)，按照某種方式排序的，最早的和最新的某一個(gè)字段的值。

看結(jié)果體會(huì)一下。

nth_value(column_name,n)

從排序的第n行還是返回nth_value字段中的值，這個(gè)函數(shù)用的不多，要表達(dá)的這種邏輯，說(shuō)實(shí)話，很難用語(yǔ)言表達(dá)出來(lái)，看個(gè)例子體會(huì)一下就行。

n = 3

n = 4

cume_dist

在某種排序條件下，小于等于當(dāng)前行值的行數(shù)/總行數(shù)，得到的是數(shù)據(jù)在某一個(gè)緯度的分布百分比情況。

比如如下示例

第1行數(shù)據(jù)的日期(create_date)是2018-01-05 00:00:00，小于等于2018-01-05 00:00:00的數(shù)據(jù)是1行，計(jì)算方式是：1/6 = 0.166666666

第2行數(shù)據(jù)的日期(create_date)是2018-01-06 00:00:00，小于等于2018-01-06 00:00:00的數(shù)據(jù)是2行，計(jì)算方式是：2/6 = 0.333333333

依次類推

第4行數(shù)據(jù)的日期(create_date)是2018-01-16 00:00:00，小于等于2018-01-16 00:00:00的數(shù)據(jù)是4行，計(jì)算方式是：4/6 = 0.6666666666

第一行數(shù)據(jù)的0.6666666666 意味著，小于第四行日期(create_date)的數(shù)據(jù)占了符合條件數(shù)據(jù)的66.66666666666%

percent_rank()

同樣是數(shù)據(jù)分布的計(jì)算方式，只不過(guò)算法變成了：當(dāng)前RANK值-1/總行數(shù)-1 。

具體算法不細(xì)說(shuō)，這個(gè)實(shí)際中用的也不多。

lag以及l(fā)ead

lag(column,n)獲取當(dāng)前數(shù)據(jù)行按照某種排序規(guī)則的上n行數(shù)據(jù)的某個(gè)字段，lead(column,n)獲取當(dāng)前數(shù)據(jù)行按照某種排序規(guī)則的下n行數(shù)據(jù)的某個(gè)字段，確實(shí)很拗口。

舉個(gè)實(shí)際例子，按照時(shí)間排序，獲取當(dāng)前訂單的上一筆訂單發(fā)生時(shí)間和下一筆訂單發(fā)生時(shí)間，(可以計(jì)算訂單的時(shí)間上的間隔度或者說(shuō)買買買的頻繁程度)

selectorder_id,

user_no,

amount,

create_date,

lag(create_date,1) over (partition by user_no order by create_date asc) 'last_transaction_time',

lead(create_date,1) over (partition by user_no order by create_date asc) 'next_transaction_time'from order_info ;

CTE 通用表表達(dá)式

CTE有兩種用法，非遞歸的CTE和遞歸的CTE。

非遞歸的CTE可以用來(lái)增加代碼的可讀性，增加邏輯的結(jié)構(gòu)化表達(dá)。

平時(shí)我們比較痛恨一句sql幾十行甚至上上百行，根本不知道其要表達(dá)什么，難以理解，對(duì)于這種SQL，可以使用CTE分段解決，

比如邏輯塊A做成一個(gè)CTE，邏輯塊B做成一個(gè)CTE，然后在邏輯塊A和邏輯塊B的基礎(chǔ)上繼續(xù)進(jìn)行查詢，這樣與直接一句代碼實(shí)現(xiàn)整個(gè)查詢，邏輯上就變得相對(duì)清晰直觀。

舉個(gè)簡(jiǎn)單的例子，當(dāng)然這里也不足以說(shuō)明問(wèn)題，比如還是第一個(gè)需求，查詢每個(gè)用戶的最新一條訂單

第一步是對(duì)用戶的訂單按照時(shí)間排序編號(hào)，做成一個(gè)CTE，第二步對(duì)上面的CTE查詢，取行號(hào)等于1的數(shù)據(jù)。

另外一種是遞歸的CTE，遞歸的話，應(yīng)用的場(chǎng)景也比較多，比如查詢大部門下的子部門，每一個(gè)子部門下面的子部門等等，就需要使用遞歸的方式。

這里不做細(xì)節(jié)演示，僅演示一種遞歸的用法，用遞歸的方式生成連續(xù)日期。

當(dāng)然遞歸不會(huì)無(wú)限下去，不同的數(shù)據(jù)庫(kù)有不同的遞歸限制，MySQL 8.0中默認(rèn)限制的最大遞歸次數(shù)是1000。

超過(guò)最大低估次數(shù)會(huì)報(bào)錯(cuò)：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

由參數(shù)@@cte_max_recursion_depth決定。

關(guān)于CTE的限制，跟其他數(shù)據(jù)庫(kù)并無(wú)太大差異，比如CTE內(nèi)部的查詢結(jié)果都要有字段名稱，不允許連續(xù)對(duì)一個(gè)CTE多次查詢等等，相信熟悉CTE的老司機(jī)都很清楚。

窗口函數(shù)和CTE的增加，簡(jiǎn)化了SQL代碼的編寫和邏輯的實(shí)現(xiàn)，并不是說(shuō)沒(méi)有這些新的特性，這些功能都無(wú)法實(shí)現(xiàn)，只是新特性的增加，可以用更優(yōu)雅和可讀性的方式來(lái)寫SQL。

不過(guò)這都是在MySQL 8.0中實(shí)現(xiàn)的新功能，在8.0之前，還是老老實(shí)實(shí)按照較為復(fù)雜的方式實(shí)現(xiàn)吧。

Primary key restrict

參數(shù)sql_require_primary_key控制

普通表和臨時(shí)表都會(huì)被限制

刪除當(dāng)前主鍵報(bào)錯(cuò)(除非同時(shí)新建主鍵)，即便已有NOT NULL UK也報(bào)錯(cuò)

導(dǎo)入無(wú)主鍵表報(bào)錯(cuò)

并行復(fù)制writeset機(jī)制

5.7的并行復(fù)制效率，取決于事務(wù)在主庫(kù)上的并發(fā)度。如果主庫(kù)上并發(fā)度不高，或者有大事務(wù)，則從庫(kù)延遲依然比較嚴(yán)重

8.0的writeset模式完美解決了這個(gè)難題：即便在主庫(kù)是串行提交，但只要事務(wù)間不沖突，在從庫(kù)依然可以并行回放

MySQL 8.0 中引入?yún)?shù)?binlog_transaction_depandency_tracking?用于控制如何決定事務(wù)的依賴關(guān)系。該值有三個(gè)選項(xiàng)：默認(rèn)的?COMMIT_ORDERE表示繼續(xù)使用5.7中的基于組提交的方式?jīng)Q定事務(wù)的依賴關(guān)系；WRITESET?表示使用寫集合來(lái)決定事務(wù)的依賴關(guān)系，基于主鍵的并發(fā)策略，可以并發(fā)的執(zhí)行同一個(gè)session內(nèi)的事務(wù)；還有一個(gè)選項(xiàng)?WRITESET_SESSION?表示使用 WriteSet 來(lái)決定事務(wù)的依賴關(guān)系，基于主鍵的并發(fā)策略，但是同一個(gè)Session內(nèi)的事務(wù)不會(huì)有相同的 last_committed 值，不可以并發(fā)執(zhí)行同一個(gè)session內(nèi)的事務(wù)。

WriteSet 是通過(guò)檢測(cè)兩個(gè)事務(wù)是否更新了相同的記錄來(lái)判斷事務(wù)能否并行回放的，因此需要在運(yùn)行時(shí)保存已經(jīng)提交的事務(wù)信息以記錄歷史事務(wù)更新了哪些行。記錄歷史事務(wù)的參數(shù)為 binlog_transaction_dependency_history_size. 該值越大可以記錄更多的已經(jīng)提交的事務(wù)信息，不過(guò)需要注意的是，這個(gè)值并非指事務(wù)大小，而是指追蹤的事務(wù)更新信息的數(shù)量。

從 MySQL Hight Availability 的測(cè)試中可以看到，開啟了基于 WriteSet 的事務(wù)依賴后，對(duì)Slave上RelayLog回放速度提升顯著。Slave上的 RelayLog 回放速度將不再依賴于 Master 上提交時(shí)的并行程度，使得Slave上可以發(fā)揮其最大的吞吐能力， 這個(gè)特性在Slave上復(fù)制停止一段時(shí)間后恢復(fù)復(fù)制時(shí)尤其有效。

這個(gè)特性使得 Slave 上可能擁有比 Master 上更大的吞吐量，同時(shí)可能在保證事務(wù)依賴關(guān)系的情況下，在 Slave 上產(chǎn)生 Master 上沒(méi)有產(chǎn)生過(guò)的提交場(chǎng)景，事務(wù)的提交順序可能會(huì)在 Slave 上發(fā)生改變。 雖然在5.7 的并行復(fù)制中就可能發(fā)生這種情況，不過(guò)在8.0中由于 Slave 上更高的并發(fā)能力，會(huì)使該場(chǎng)景更加常見。 通常情況下這不是什么大問(wèn)題，不過(guò)如果在 Slave 上做基于 Binlog 的增量備份，可能就需要保證在 Slave 上與Master 上一致的提交順序，這種情況下可以開啟?slave_preserve_commit_order?這是一個(gè) 5.7 就引入的參數(shù)，可以保證 Slave 上并行回放的線程按 RelayLog 中寫入的順序 Commit。

8.0.14后新特性

雙密碼機(jī)制

從MySQL8.0.14開始，允許用戶賬戶擁有雙密碼，指定為主密碼和輔助密碼

mysql>create user root@'%' identified by '123456';

mysql>grant all privileges on *.* to root@'%';

創(chuàng)建新密碼

mysql>alter user root@'%' identified by 'root'RETAIN CURRENT PASSWORD;

丟棄舊密碼:

mysql>alter user root@'%' DISCARD OLD PASSWORD;

Binary log?加密機(jī)制，屬于安全機(jī)制方面

redo & undo 日志加密

log_slow_extra

mysql> ?SET GLOBAL log_slow_extra=1;

log_slow_extra 慢日志參數(shù)，提供了更詳細(xì)的內(nèi)容

admin_port

使用的端口默認(rèn)為33062, 由admin_port來(lái)設(shè)置

mysql> show variables like 'admin_%';+---------------+---------------+

| Variable_name | Value |

+---------------+---------------+

| admin_address | 192.168.1.101 |

| admin_port | 33062 |

+---------------+---------------+

2 rows in set (0.00 sec)

默認(rèn)字符集由latin1變?yōu)閡tf8mb4

在8.0版本之前，默認(rèn)字符集為latin1，utf8指向的是utf8mb3，8.0版本默認(rèn)字符集為utf8mb4，utf8默認(rèn)指向的也是utf8mb4

group by 不再隱式排序

mysql 8.0 對(duì)于group by 字段不再隱式排序，如需要排序，必須顯式加上order by 子句

# 表結(jié)構(gòu)

mysql>show create table tb1\G*************************** 1. row ***************************Table: tb1

Create Table: CREATE TABLE `tb1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`group_own`int(11) DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC1 row in set (0.00sec)

# 表數(shù)據(jù)

mysql> select *from tb1;+----+------+-----------+

| id | name | group_own |

+----+------+-----------+

| 1 | 1 | 0 |

| 2 | 2 | 0 |

| 3 | 3 | 0 |

| 4 | 4 | 0 |

| 5 | 5 | 5 |

| 8 | 8 | 1 |

| 10 | 10 | 5 |

+----+------+-----------+

7 rows in set (0.00sec)

# MySQL5.7mysql> select count(id), group_own from tb1 group by group_own;+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 1 | 1 |

| 2 | 5 |

+-----------+-----------+

3 rows in set (0.00sec)

# MySQL8.0.11mysql> select count(id), group_own from tb1 group by group_own;+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 2 | 5 |

| 1 | 1 |

+-----------+-----------+

3 rows in set (0.00sec)

# MySQL8.0.11顯式地加上order by進(jìn)行排序

mysql> select count(id), group_own from tb1 group by group_own order by group_own;+-----------+-----------+

| count(id) | group_own |

+-----------+-----------+

| 4 | 0 |

| 1 | 1 |

| 2 | 5 |

+-----------+-----------+

3 rows in set (0.00 sec)

JSON特性增強(qiáng)

MySQL 8 大幅改進(jìn)了對(duì) JSON 的支持，添加了基于路徑查詢參數(shù)從 JSON 字段中抽取數(shù)據(jù)的 JSON_EXTRACT() 函數(shù)，以及用于將數(shù)據(jù)分別組合到 JSON 數(shù)組和對(duì)象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函數(shù)。

在主從復(fù)制中，新增參數(shù) binlog_row_value_options，控制JSON數(shù)據(jù)的傳輸方式，允許對(duì)于Json類型部分修改，在binlog中只記錄修改的部分，減少json大數(shù)據(jù)在只有少量修改的情況下，對(duì)資源的占用。

8.0幾個(gè)特殊參數(shù)

log_error_verbosity=3innodb_print_ddl_logs=1binlog_expire_logs_seconds=86400innodb-undo-tablespaces=10innodb-undo-directory=undolog

轉(zhuǎn)自

MySQL8.0新特性實(shí)驗(yàn)1 - AllenHU320 - 博客園

https://www.cnblogs.com/allenhu320/p/11551010.html

總結(jié)

以上是生活随笔為你收集整理的mysql8.0默认引擎是什么_MySQL8.0新特性【转】的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。