MySQL 5.7 新特性详解 已翻译 100%

冒牌导演 投递于 2015/10/22 09:45 (共 54 段, 翻译完成于 01-19)
阅读 38743
收藏 185
19
加载中

We proudly announce General Availability (GA) of MySQL 5.7. Download now! MySQL 5.7.9 is an extremely exciting new version of the world’s most popular open source database that is 3x faster than MySQL 5.6, while also improving usability, manageability, and security. Some key enhancements include:

  1. Performance & Scalability: Improved InnoDB scalability and temporary table performance, enabling faster online and bulk load operations, and more.

  2. JSON Support: With the newly added JSON support in MySQL, you can now combine the flexibility of NoSQL with the strength of a relational database.

  3. Replication improvements for increased availability and performance. They include multi-source replication, multi-threaded slave enhancements, online GTIDs, and enhanced semi-sync replication.

  4. Performance Schema delivering much better insights. We’ve added numerous new monitoring capabilities, reduced the footprint and overhead, and significantly improved ease of use with the new SYS Schema.

  5. Security: We are fulfilling “secure by default” requirements and many new MySQL 5.7 features will help users keep their database secure.

  6. Optimizer: We have rewritten large parts of the parser, optimizer, and cost model. This has improved maintainability, extendability, and performance.

  7. GIS: Completely new in MySQL 5.7 and including InnoDB spatial indexes, use of Boost.Geometry, along with increased completeness and standard compliance.

已有 1 人翻译此段
我来翻译

The above represents some of the highlights and I encourage you to further drill into the complete series of Milestone blog posts—5.7.1, 5.7.2, 5.7.3, 5.7.4, 5.7.5, 5.7.6, 5.7.7, 5.7.8 —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. We have implemented 365 worklogs, added 1007 MTR tests, and fixed 2812 5.7-specific bugs in 5.7. It might all be a bit overwhelming, so this “little” guide might be useful.

A big THANK YOU to everyone in the development team that made this happen! By development I mean everyone: requirements, design, coding, reviewing, testing, bug fixing, documentation, release, and lab support.  A real team effort. A big THANK YOU to the overall MySQL community providing bugs, feedback, and contributions! A world wide effort.

已有 1 人翻译此段
我来翻译

Adding JSON Support to MySQL

With the newly added JSON support in MySQL, you can now combine the flexibility of NoSQL with the strength of a relational database.

We have developed a JSON datatype and a binary storage format that allows the server to efficiently store, retrieve and search JSON data (WL#8132) . This work enhances CREATE TABLE and ALTER TABLE so that they can create JSON columns, and it extends the Field class to allow INSERT to and SELECT from JSON typed fields.

We have introduced a set of server side, built-in JSON functions (WL#7909). This work lets users construct JSON data values from other relational data, extract relational data from JSON data values, introspect the structure of JSON values and text (validity, length, depth, keys), search within, and manipulate JSON data.

已有 1 人翻译此段
我来翻译

We have introduced the JSON comparator, similar to the DATE/TIME/DATETIME comparator, which allow comparisons of JSON scalars vs SQL constants, and JSON scalars vs JSON scalars (WL#8249). The comparator relies on the DOM support added in the scope of WL#7909. The comparator converts the SQL constant to a JSON scalar and then compares the values.

We have implemented ordering of scalar JSON values (WL#8539), i.e. a function that produces the sorting keys that the internal filesort function needs in order to properly sort JSON values. When ordering scalar JSON values with ORDER BY, they will be returned in the order defined by the JSON comparator in WL#8249.

已有 2 人翻译此段
我来翻译

We have implemented a expression analyzer for generated columns (WL#8170). This work allows our range and ref optimizers to find opportunities to use any indexes defined over generated columns. One intended use case for this feature is to allow the creation and automatic use of indexes on JSON Documents.

We have added inline JSON path expressions in SQL queries (WL#8607). MySQL now executes queries like:  SELECT … FROM t1 WHERE t1.json_field->”$.path.to[0].key”= 123;

Generated Columns & Indexable Virtual Columns

We first implemented Generated Columns (WL#411, WL#8114). The values for such columns, unlike a regular field’s value, aren’t set by the user but instead computed by the server when the row is created or updated, using the expression specified by the user as part of the table definition. Generated columns can either be materialized (stored) or non-materialized (virtual). See Evgeny Potemkin’s article “Generated Columns in MySQL 5.7.5“.

已有 1 人翻译此段
我来翻译

We then implemented support for the creation of secondary indexes on non-materialized virtual columns, as well as the usage of these indexes for fast computed-value retrieval and searches (WL#8149, WL#8227, WL#8481) .

The non-materialized virtual columns were designed in such way that they are not present in actual InnoDB index records, but their metadata is registered with InnoDB system tables and metadata caches. Virtual columns provide flexibility and space savings for the table, and more importantly, adding/dropping such columns does not require a table rebuild. These behaviors make it a much better choice for storing and processing non-relational data such as JSON. However, since the columns themselves are not materialized, a scan and search could be slower than on regular (materialized) columns. However, the virtual column value is materialized in the secondary index, thus making it much easier for value searches and processing. Thus this work greatly increases the practical value of virtual columns. With this work, creating an index on virtual generated columns also becomes an ONLINE operation.

已有 1 人翻译此段
我来翻译

Performance & Scalability

Performance and scalability is a priority for MySQL, learning from community feedback and taking into account trends and developments in computer hardware and its architecture. So far in 5.7 we have delivered impressive read-only (RO) scalability results with InnoDB and significantly sped up the connection handling in the server layer. We are also making good progress on InnoDB read-write (RW) scalability, improved internal operations (faster and more stable flushing/purging), and fast bulk data loads.

InnoDB Read-Only Scalability. We have improved the performance for Read-Only (RO) and Read-Mostly workloads. We have significantly improved how InnoDB handles RO transactions (WL#6047, WL#6899, WL#6906, WL#6578). We have also removed server layer contentions related to Meta Data Locking (MDL) and removed the use of THR_LOCK locks for InnoDB (WL#7304, WL#7305, WL#7306, WL#6671). After WL#6671, the LOCK_grant lock then became more visible as the next scalability bottleneck in some workloads; for example, the single table InnoDB POINT_SELECT Sysbench tests (see also Bug#72829). This has now been fixed by partitioning the LOCK_grant lock (see WL#8355). Finally we have  removed bottlenecks around LOCK_plugin and THR_LOCK_lock in workloads which involve the creation of internal memory based temporary tables; for example, like Sysbench’s SELECT_DISTINCT test for InnoDB. There is no real reason why we should acquire these locks for internal temporary tables, so we removed this unnecessary overhead (see WL#8356). See also Dimitri Kravtchuk’s articles “MySQL Performance: reaching 500K QPS with MySQL 5.7“,  “MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin“,  Sunny Bains’ article “Transaction life cycle improvements in 5.7.3“, and Jimmy Yang’s article “MySQL 5.7.3: Deep dive into 1mil QPS with InnoDB Memcached“.

已有 1 人翻译此段
我来翻译

InnoDB Read-Write Scalability. We have also improved the performance of Read-Write (RW) workloads. We have removed the “index lock contention” in InnoDB (WL#6363, WL#6326). The index lock that was used to protect the entire index tree structure is now replaced by more fine grained “block locks” in the tree. See Yasufumi Kinoshita’s article “MySQL-5.7 improves DML oriented workloads“.

InnoDB Faster & Parallel Flushing. We have reduced the number of pages scanned when doing flush list batches, speeding up page flushing (WL#7047). The time complexity of a scan is reduced from O(n*n) to O(n). We have also implemented parallel flushing by having multiple page_cleaner threads (WL#6642). This gives improved scalability and throughput on multi-core systems and avoids flushing becoming the bottleneck. Finally, we have improved the adaptive flushing algorithm and related mechanisms in order to achieve more consistent or smooth throughput (see WL#7868).

已有 1 人翻译此段
我来翻译

Speeding up Connection Handling. In some application scenarios (e.g. PHP applications) client connections have very short life spans, perhaps only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance. In 5.7 we have offloaded thread initialization and network initialization to a worker thread (WL#6606) and more than doubled MySQL’s ability to do high frequency connect/disconnect cycles, from 26K to 56K connect/disconnect cycles per second. See also Jon Olav Hauglid’s article “Improving connect/disconnect performance“.

Bulk Data Load Improvements.  Bulk Load for Create Index (WL#7277). This work implements bulk sorted index builds, thus making CREATE INDEX operations much faster. Prior to this work InnoDB looped through the base table and created one index record at a time for each record in the base table. After this work InnoDB reads many records from the base table, sorts the records using the index key, and then creates a chunked set of index records in one single bulk operation.

已有 1 人翻译此段
我来翻译

Online Operations

“Always ON” properties are essential to state of the art web solutions. It is important for DBAs or DevOps to be able to tune and extend their production systems without interrupting service. Thus, we continue to ensure that MySQL is leading in this area. In 5.7 we have delivered the following:

We have provided a way to enable GTIDs online (WL#7083), so that reads and writes are allowed during the procedure, servers do not need to synchronize, and no restarts are necessary. Prior to this work, the user had to stop all updates, synchronize all servers, and then restart all of them simultaneously. This previously meant that enabling GTIDs implied several minutes of planned downtime. See Sven Sandberg’s article “Enabling Global Transaction Identifiers Without Downtime in MySQL 5.7.6“.

已有 1 人翻译此段
我来翻译
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(41)

Likenttt
Likenttt
我们已经实施了 365 个worklogs,添加了 1007 个 MTR 测试,并且修复了 28125.7 个具体的 bug。 bug为啥是小数
h
holysky

引用来自“一剑倾城”的评论

一个小项目里用了一下5.7,一个嵌套了较多子查询的SQL,在5.6里可以1-2秒就出结果,放到5.7里等了几十分钟也无法出现结果,把所有能想到的配置都改了,还是没有效果,因为项目时间较紧,无奈放弃5.7换回5.6,等以后有机会再研究吧。
那你就不能改sql吗,回避问题不是好习惯
bluntFish
bluntFish
团队中遇到一个问题,一个历史表(一个模型到另一个模型的N:N的关系表),因为历史原因,虽然现在Java模型都使用Long型了,但后端数据库一直还是varchar字段,表里还有五行历史数据,存储的不是数值而是字符串。
在5.6、5.5版本的Mysql上执行如下代码正常:
int groupId = 8;
  sql = "delete from s_groupmodule where groupId=?";
  ps = conn.prepareStatement(sql);
  ps.setObject(1, groupId);
  ps.executeUpdate();

但切换到5.7的时候报错了,错误信息是:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value:
虽然知道怎么解决了, 但想知道5.7做了什么优化调整,才会出现这种只有在5.7里不能执行的情况 。
KangJellen
KangJellen
很好,学习了。。。谢谢分享。。。
MangoCool
MangoCool
mark
yho
yho
现在大家不是都要换到mariadb去了吗,mariadb版本我都搞不懂(企业版是啥意思),我该用哪个版本,还有不支持json吗,只支持dynamic_column吗
LinuxSuRen
LinuxSuRen
学习了。


http://surenpi.com
AnywayLee
AnywayLee
mark
游客
游客
5.7难用
AkataMoKa
AkataMoKa
有 json 数据使用场景的例子吗?
返回顶部
顶部