MySQL8.0 GA版本發(fā)布了，展現(xiàn)了眾多新特性，本系列譯文將整理為3篇，為大家介紹升級(jí)的部分新特性。

本文為第1篇，重點(diǎn)為大家介紹SQL、JSON上展現(xiàn)的新特性，其他特性的介紹將陸續(xù)更新，敬請(qǐng)關(guān)注。

非常高興的向大家宣布MySQL 8.0 GA版本發(fā)布，MySQL 8.0是一個(gè)得到全面增強(qiáng)且極具吸引力的新版本。不限于下面幾點(diǎn)：

We proudly announce General Availability of MySQL 8.0. Download now! MySQL 8.0 is an extremely exciting new version of the world’s most popular open source database with improvements across the board. Some key enhancements include:

1、SQL方面：窗口函數(shù)，公共表達(dá)式，NOWAIT, SKIP LOCKED, 降序索引，分組，正則表達(dá)式，字符集，CBO優(yōu)化模式，直方圖

1、SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.

2、對(duì)JSON的支持：擴(kuò)充語(yǔ)法，新函數(shù)，排序增強(qiáng)，JSON列部分更新?；贘SON表的特性，可以調(diào)用SQL語(yǔ)句處理JSON數(shù)據(jù)。

2、JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.

3、對(duì)地理信息系統(tǒng)的支持—空間引用系統(tǒng)（SRS），包括SRS空間數(shù)據(jù)類(lèi)型，空間索引，空間函數(shù)

3、GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.

4、可靠性：DDL語(yǔ)句支持原子性和崩潰安全恢復(fù)（元信息數(shù)據(jù)被存在了一個(gè)基于InnoDB的單獨(dú)事務(wù)性數(shù)據(jù)字典中）。

4、Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary. Powered by InnoDB!

5、可觀察性：對(duì)P_S,I_S,配置參數(shù)，錯(cuò)誤日志的記錄有顯著增強(qiáng)

5、Observability Significant enhancements to Performance Schema, Information Schema, Configuration Variables, and Error Logging.

6、可管理性：遠(yuǎn)程管理，Undo表空間管理，快速DDL

6、Manageability Remote management, Undo tablespace management, and new instant DDL.

7、安全性：OpenSSL的改進(jìn)，新的默認(rèn)驗(yàn)證方式，SQL角色權(quán)限，分解super權(quán)限，密碼強(qiáng)度提升等等

7、Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, and more.

8、性能：InnoDB在讀/寫(xiě)負(fù)載，高IO負(fù)載，熱數(shù)據(jù)高并發(fā)競(jìng)爭(zhēng)等場(chǎng)景表現(xiàn)更好。新增的資源組特性給用戶在特定負(fù)載和特定硬件情況下將用戶線程映射到指定的CPU上的可選項(xiàng)

8、Performance InnoDB is significantly better at Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads. Added Resource Group feature to give users an option optimize for specific workloads on specific hardware by mapping user threads to CPUs.

以上是8.0版本的部分亮點(diǎn)，我（原文作者）推薦您仔細(xì)閱讀GA版本前幾個(gè)版本的發(fā)布信息，甚至這些特性和實(shí)現(xiàn)方法的的項(xiàng)目日志?；蛘吣梢赃x擇直接在Github上閱讀源碼。

The above represents some of the highlights and I encourage you to further drill into the complete series of Milestone blog posts—8.0.0, 8.0.1, 8.0.2, 8.0.3, and 8.0.4 —and even further down in to the individual worklogs with their specifications and implementation details. Or perhaps you prefer to just look at the source code at github.com/mysql.   

面向開(kāi)發(fā)人員的特性  

 SQL

1、窗口函數(shù)

MySQL 8.0帶來(lái)了標(biāo)準(zhǔn)SQL的窗口函數(shù)功能，窗口函數(shù)與分組聚合函數(shù)相類(lèi)似的是都提供了對(duì)一組行數(shù)據(jù)的統(tǒng)計(jì)計(jì)算。但與分組聚合函數(shù)將多行合并成一行不同是窗口函數(shù)會(huì)在結(jié)果結(jié)果集中展現(xiàn)每一行的聚合。

MySQL 8.0 delivers SQL window functions. Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g. COUNT or SUM. But where a grouped aggregate collapses this set of rows into a single row, a window function will perform the aggregation for each row in the result set.

窗口函數(shù)有兩種使用方式，首先是常規(guī)的SQL聚合功能函數(shù)和特殊的窗口函數(shù)。

常規(guī)的聚合功能函數(shù)如：COUNT,SUM等函數(shù)。而窗口函數(shù)專(zhuān)有的則是RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEADand LAG等函數(shù)。

Window functions come in two flavors: SQL aggregate functions used as window functions and specialized window functions. This is the set of aggregate functions in MySQL that support windowing: COUNT, SUM, AVG, MIN, MAX, BIT_OR, BIT_AND, BIT_XOR, STDDEV_POP (and its synonyms STD, STDDEV), STDDEV_SAMP, VAR_POP (and its synonym VARIANCE) and VAR_SAMP. The set of specialized window functions are: RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEADand LAG

對(duì)窗口函數(shù)的支持上，是用戶呼聲比較頻繁。窗口函數(shù)早在SQL2003規(guī)范中就成為了標(biāo)準(zhǔn)SQL的一部分。

Support for window functions (a.k.a. analytic functions) is a frequent user request. Window functions have long been part of standard SQL (SQL 2003). See blog post by Dag Wanvik here as well as blog post by Guilhem Bichot here.

2、公用表表達(dá)式（CTE）

MySQL 8.0 帶來(lái)了支持遞歸的公用表表達(dá)式的功能。非遞歸的公用表表達(dá)式由于允許由from子句派生的臨時(shí)表的原因可以被多次引用，因而被解釋為改進(jìn)型的派生表（from子句中的臨時(shí)表）。

而遞歸的公用表表達(dá)式則由一組原始數(shù)據(jù)，經(jīng)過(guò)處理后得到新的一組數(shù)據(jù)，再被帶入處理得到更多的新數(shù)據(jù)，循環(huán)往復(fù)直到再也無(wú)法產(chǎn)生更多新數(shù)據(jù)為止。公用表達(dá)式也是一個(gè)用戶呼聲頻繁的SQL功能。

MySQL 8.0 delivers [Recursive] Common Table Expressions (CTEs). Non-recursive CTEs can be explained as “improved derived tables” as it allow the derived table to be referenced more than once. A recursive CTE is a set of rows which is built iteratively: from an initial set of rows, a process derives new rows, which grow the set, and those new rows are fed into the process again, producing more rows, and so on, until the process produces no more rows. CTE is a commonly requested SQL feature, see for example feature request 16244 and 32174 . See blog posts by Guilhem Bichot here, here, here, and here.

3、新的NOWAIT、SKIP LOCKED選項(xiàng)

MySQL 8.0 給SQL的上鎖子句帶來(lái)了NOWAIT和SKIP LOCKED兩個(gè)可選項(xiàng)。在原來(lái)的版本中，當(dāng)行數(shù)據(jù)被UPDATE或者SELECT ... FOR UPDATE語(yǔ)句上鎖后，其他的事務(wù)需要等待鎖釋放才能訪問(wèn)這行數(shù)據(jù)。

但在某些場(chǎng)景下，有馬上獲得（不等待鎖）數(shù)據(jù)的需求。使用NOWAIT參數(shù)后如果請(qǐng)求的數(shù)據(jù)中包括了被鎖住的行，將馬上會(huì)收到查詢失敗的報(bào)錯(cuò)信息。使用SKIP LOCKED參數(shù)后，返回的數(shù)據(jù)將會(huì)跳過(guò)被鎖住的行。

MySQL 8.0 delivers NOWAIT and SKIP LOCKED alternatives in the SQL locking clause. Normally, when a row is locked due to an UPDATE or a SELECT ... FOR UPDATE, any other transaction will have to wait to access that locked row. In some use cases there is a need to either return immediately if a row is locked or ignore locked rows. A locking clause using NOWAIT will never wait to acquire a row lock. Instead, the query will fail with an error. A locking clause using SKIP LOCKED will never wait to acquire a row lock on the listed tables. Instead, the locked rows are skipped and not read at all. NOWAIT and SKIP LOCKED are frequently requested SQL features. See for example feature request 49763 . We also want to say thank you to Kyle Oppenheim for his code contribution! See blog post by Martin Hansson here.

4、降序索引

MySQL 8.0 帶來(lái)了對(duì)降序索引的支持。在 8.0降序索引中，數(shù)據(jù)被倒序組織，正向查找。而在之前的版本中，雖然支持創(chuàng)建降序排列的索引，但其實(shí)現(xiàn)方式是通過(guò)創(chuàng)建常見(jiàn)的正序索引，然后進(jìn)行反向查找來(lái)實(shí)現(xiàn)的。

一方面，正序查找要比逆序查找更快；

另一方面，真正的降序索引在復(fù)合的order by語(yǔ)句（即有asc又有desc）中,可以提高索引利用率，消除filesort。

MySQL 8.0 delivers support for indexes in descending order. Values in such an index are arranged in descending order, and we scan it forward. Before 8.0, when a user create a descending index, we created an ascending index and scanned it backwards. One benefit is that forward index scans are faster than backward index scans. Another benefit of a real descending index is that it enables us to use indexes instead of filesort for an ORDER BY clause with mixed ASC/DESC sort key parts. Descending Indexes is a frequently requested SQL feature. See for example feature request 13375 . See blog post by Chaithra Gopalareddy here.

5、分組函數(shù) 

MySQL 8.0 帶來(lái)了GROUPING（）分組函數(shù)，這個(gè)功能可以把group by子句擴(kuò)展功能（如ROLLUP）產(chǎn)生的過(guò)聚合NULL值，通過(guò)0和1進(jìn)行區(qū)分，1為NULL，這樣就可以在having子句中對(duì)過(guò)聚合的無(wú)效值進(jìn)行過(guò)濾。

MySQL 8.0 delivers GROUPING(), SQL_FEATURE T433. The GROUPING() function distinguishes super-aggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP produce super-aggregate rows where the set of all values is represented by null. Using the GROUPING() function, you can distinguish a null representing the set of all values in a super-aggregate row from a NULL in a regular row. GROUPING is a frequently requested SQL feature. See feature requests 3156 and 46053. Thank you to Zoe Dong and Shane Adams for code contributions in feature request 46053 ! See blog post by Chaithra Gopalareddy here.

6、優(yōu)化器建議

在5.7版本中我們引入了新的優(yōu)化器建議的語(yǔ)法，借助這個(gè)新的語(yǔ)法，優(yōu)化器建議可以被用/*+ */包裹起來(lái)，直接放在SELECT | INSERT | REPLACE | UPDATE | DELETE關(guān)鍵字的后面。

在8.0的版本中我們又加入了新的姿勢(shì)：

In 5.7 we introduced a new hint syntax for optimizer hints. With the new syntax, hints can be specified directly after the SELECT | INSERT | REPLACE | UPDATE | DELETEkeywords in an SQL statement, enclosed in /*+ */ style comments. (See 5.7 blog post by Sergey Glukhov here). In MySQL 8.0 we complete the picture by fully utilizing this new style:

• 8.0版本增加了INDEX_MERGE和NO_INDEX_MERGE,允許用戶在單個(gè)查詢中控制是否使用索引合并特性。

MySQL 8.0 adds hints for INDEX_MERGE and NO_INDEX_MERGE. This allows the user to control index merge behavior for an individual query without changing the optimizer switch.

• 8.0版本增加了JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, 和 JOIN_SUFFIX，允許用戶控制join表關(guān)聯(lián)的順序。

MySQL 8.0 adds hints for JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, and JOIN_SUFFIX. This allows the user to control table order for the join execution.

• 8.0版本增加了SET_VAR，該優(yōu)化器建議可以設(shè)定一個(gè)只在下一條語(yǔ)句中生效的的系統(tǒng)參數(shù)。

MySQL 8.0 adds a hint called SET_VAR. The SET_VAR hint will set the value for a given system variable for the next statement only. Thus the value will be reset to the previous value after the statement is over. See blog post by Sergey Glukhov here.

相對(duì)于之前的優(yōu)化器建議和優(yōu)化器特性開(kāi)關(guān)參數(shù)，我們更傾向于推薦新形式的優(yōu)化器建議模式，新形式的優(yōu)化器建議可以在不侵入SQL語(yǔ)句（指修改語(yǔ)句的非注釋的業(yè)務(wù)部分）的情況下，注入查詢語(yǔ)句的很多位置。與直接修改語(yǔ)句的優(yōu)化器建議相比，新形勢(shì)的優(yōu)化器建議在SQL語(yǔ)義上更加清晰。

We prefer the new style of optimizer hints as preferred over the old-style hints and setting of optimizer_switch values. By not being inter-mingled with SQL, the new hints can be injected in many places in a query string. They also have clearer semantics in being a hint (vs directive). 

 JSON

8.0版本追加了新的JSON函數(shù)，并可以提高在排序與分組JSON數(shù)據(jù)情況下的性能。

MySQL 8.0 adds new JSON functions and improves performance for sorting and grouping JSON values.

1、JSON path表達(dá)式中擴(kuò)展的范圍性語(yǔ)法

MySQL 8.0 擴(kuò)展了JSON path表達(dá)式中范圍性的語(yǔ)法，比如：SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');可以得出[2, 3, 4]的結(jié)果。

MySQL 8.0 extends the syntax for ranges in JSON path expressions. For example SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]'); results in [2, 3, 4]. 

The new syntax introduced is a subset of the SQL standard syntax, described in SQL:2016, 9.39 SQL/JSON path language: syntax and semantics. See also Bug#79052reported by Roland Bouman.

2、JSON表函數(shù) 

MySQL 8.0 增加了可以在JSON數(shù)據(jù)上使用SQL處理工具的JSON 表函數(shù)。JSON_TABLE()函數(shù)可以創(chuàng)建JSON數(shù)據(jù)的關(guān)系型視圖。可以將JSON數(shù)據(jù)估算到關(guān)系型的行列之中，用戶可以對(duì)此函數(shù)返回的數(shù)據(jù)按照常規(guī)關(guān)系型數(shù)據(jù)表的方式進(jìn)行SQL運(yùn)算。

MySQL 8.0 adds JSON table functions which enables the use of the SQL machinery for JSON data. JSON_TABLE() creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns. The user can query the result returned by the function as a regular relational table using SQL, e.g. join, project, and aggregate.

3、JSON 聚合函數(shù)

MySQL 8.0 增加了用于生成JSON陣列的聚合函數(shù)JSON_ARRAYAGG()，和用于生成JSON對(duì)象的JSON_OBJECTAGG()函數(shù)，令多行的JSON文檔組合成JSON陣列或者JSON對(duì)象成為可能。

MySQL 8.0 adds the aggregation functions JSON_ARRAYAGG() to generate JSON arrays and JSON_OBJECTAGG() to generate JSON objects . This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object. See blog post by Catalin Besleaga here.

4、JSON 合并函數(shù)

JSON_MERGE_PATCH() 函數(shù)可執(zhí)行JavaScript的語(yǔ)法，在合并時(shí)發(fā)生重復(fù)鍵值對(duì)時(shí)將會(huì)優(yōu)先選用第二個(gè)文檔的鍵值對(duì)，并刪除第一個(gè)文檔對(duì)應(yīng)的重復(fù)鍵值。

The JSON_MERGE_PATCH() function implements the semantics of JavaScript (and other scripting languages) specified by RFC7396, i.e. it removes duplicates by precedence of the second document. For example, JSON_MERGE('{"a":1,"b":2 }','{"a":3,"c":4 }');# returns {"a":3,"b":2,"c":4}.

JSON_MERGE_PRESERVE()函數(shù)與5.7版本中的JSON_MERGE()含義相同，都是在合并的時(shí)候保留所有值。

The JSON_MERGE_PRESERVE() function has the semantics of JSON_MERGE() implemented in MySQL 5.7 which preserves all values, for example JSON_MERGE('{"a": 1,"b":2}','{"a":3,"c":4}'); # returns {"a":[1,3],"b":2,"c":4}.

5.7原來(lái)的JSON_MERGE() 函數(shù)在8.0版本中為減少merge操作的不明確性，而被棄用。

The existing JSON_MERGE() function is deprecated in MySQL 8.0 to remove ambiguity for the merge operation. See also proposal in Bug#81283 and blog post by Morgan Tocker here.

5、JSON 美化函數(shù)

8.0版本增加了可以接收J(rèn)SON原生數(shù)據(jù)類(lèi)型和用字符串形式表達(dá)的JSON，并返回一行縮進(jìn)的易讀的JSON格式化后的的字符串。

MySQL 8.0 adds a JSON_PRETTY() function in MySQL. The function accepts either a JSON native data-type or string representation of JSON and returns a JSON formatted string in a human-readable way with new lines and indentation.

6、JSON 文件大小函數(shù) 

8.0版本增加了和指定JSON對(duì)象空間占用相關(guān)的函數(shù)，JSON_STORAGE_SIZE() 可以用字節(jié)為單位返回JSON某個(gè)數(shù)據(jù)類(lèi)型的實(shí)際大小， JSON_STORAGE_FREE() 可以返回該JSON數(shù)據(jù)類(lèi)型的剩余空間（包括碎片和用來(lái)適應(yīng)更改后發(fā)生長(zhǎng)度變化的預(yù)備空間）

MySQL 8.0 adds JSON functions related to space usage for a given JSON object. The JSON_STORAGE_SIZE() returns the actual size in bytes for a JSON datatype. The JSON_STORAGE_FREE() returns the free space of a JSON binary type in bytes, including fragmentation and padding saved for inplace update.

7、JSON 改進(jìn)型的排序

8.0版本通過(guò)使用變長(zhǎng)的排序鍵提升了JSON排序分組的性能。在某些場(chǎng)景下，Preliminary 的壓測(cè)結(jié)果出現(xiàn)了1.2到18倍的提升。

MySQL 8.0 gives better performance for sorting/grouping JSON values by using variable length sort keys. Preliminary benchmarks shows from 1.2 to 18 times improvement in sorting, depending on use case.

8、JSON的部分更新

8.0版本增加了對(duì) JSON_REMOVE(), JSON_SET() 和 JSON_REPLACE() 函數(shù)的部分更新的支持。如果JSON文檔的某部分被更新，我們會(huì)將更改的詳情給到句柄。這樣存儲(chǔ)引擎和復(fù)制關(guān)系就不必寫(xiě)入整個(gè)JSON文檔。

在之前的復(fù)制環(huán)境中由于無(wú)法確保JSON文檔的排列(layout)在主從上完全一致，所以在基于行的復(fù)制情況下物理文件的差異并不能用來(lái)削減傳輸復(fù)制信息帶來(lái)的網(wǎng)絡(luò)IO消耗。

因此，8.0版本提供了在邏輯上區(qū)分差異的方法，可以在行復(fù)制的情況下傳輸并應(yīng)用到從庫(kù)上。

MySQL 8.0 adds support for partial update for the JSON_REMOVE(), JSON_SET() and JSON_REPLACE() functions. If only some parts of a JSON document are updated, we want to give information to the handler about what was changed, so that the storage engine and replication don’t need to write the full document. In a replicated environment, it cannot be guaranteed that the layout of a JSON document is exactly the same on the slave and the master, so the physical diffs cannot be used to reduce the network I/O for row-based replication. Thus, MySQL 8.0 provides logical diffs that row-based replication can send over the wire and reapply on the slave. See blog post by Knut Anders Hatlen here.