Reprinted from:
Http://www.chinaxing.org/articles/Database/2015/10/23/2015-10-22-mysql-5.7.html
Percona a test, in the case of MySQL5.7 innodb_page_size adjustment to 4k, can be improved than 16k performance of more than 70%; https://www.percona.com/blog/2016/08/10/ small-innodb_page_size-performance-boost-ssd/
MySQL 5.7 GA new features table of Content
- Performance
- Ease of operation
- SQL Optimizer elevation
- InnoDB Enhancement
- Performace Schema
- Buffer Pool Dump/load
- Replication
- Reference
MySQL 5.7 is an enlarged recruit, ease of use, performance, usability, security, etc. have a great improvement.
Performance
More than 3 times times performance improvement. Including:
- Temporary table performance
- Index builds faster when bulk data is imported
- Improved read and write performance of InnoDB
- InnoDB's Parallel Brush disk O (n^2), O (n), formerly single-threaded, allows the use of multicore
- Connection handling capability improving Connect/disconnect performance
Ease of operation
- GTID Online Setup enabling Global Transaction Identifiers without downtime in MySQL 5.7.6
- Online modification
innodb_buffer_pool_size
Resizing the InnoDB Buffer Pool online
- Automatically truncate the undo log to prevent the undo log from being too large
- Rename an index online
- Expand
VARCHAR
the length of the type column online
- Modify copy Filter online
CHANGE REPLICATION FILTER
Making MySQL Slave Replication filter Dynamic
- Change the master of slave online without having to stop copying changes
CHANGE MASTER
master without stopping slave altogether
SQL Optimizer elevation
- Parser overriding SQL parser refactoring
- Hierarchical SQL Optimizer
- Add GIS implementations
- Query Rewrite plugin framework, users can write their own query Rewrite plug-in Write yourself a query Rewrite Plugin; https://yq.aliyun.com/articles/53686
- Subquery processing normalization: View and from statements 2 ways to get subquery to use a unified approach
- A method of performing a row expression by using interval scanning improves theperformance of "in Queries" Range Access:now in an inpredicate
- "UNION All" no longer creates temporary tables, reducing disk space and disk IO operations. State of the UNION
- Compresses unsorted fields in the sort cache, using sort caches more efficiently, reducing disk IO
- Can be executed
EXPLAIN FOR CONNECTION
to view the execution of the query in the case, the role of the run-time profile.
- The JSON EXPLAIN increases the output of query overhead, the number of data sets processed, and other information that makes it easier for users to make decisions about execution plans. MySQL EXPLAIN explained
- The optimizer's hint syntax is increased, and can be directly followed by a key word like SELECT,/*+ */In the form of comments
- Added an index-level hint
- Add a hint to control the execution policy of a subquery
- Optimizer's new cost evaluation model MySQL Optimizer costs model Project
InnoDB Enhancement
- Page Compression increase transparent page compression, for the support of sparse files on the system, compressed page content, and the use of sparse file system features only save compressed content, save disk space usage.
Performace Schema
The schema for monitoring system performance can be redesigned, including metadata Locking, transactions, Memory Usage, Stored Programs, Prepared statements,mysql Performace Schema
Buffer Pool Dump/load
Boost Bufferpool dump and load, support only dump each Bufferpool the hottest per n (default 25%) page, load runs in the background, does not affect user query execution MySQL dumping and reloading the InnoDB Buffer Pool
Replication
- Mutli-source Replication MySQL multi-source Replication allows multiple master configurations in the same slave
- Multi-threaded slaves configuring slave for intra-database parallelization support slave The SQL execution thread concurrently applies the Binlog event to the same database and schema (as long as there is no intersection), and the previous version is a concurrent application that supports only multiple libraries.
- Semi-sync Master can wait for n slave to confirm Binlog Log received, before it is only waiting for 1, this benefit is increased the reliability of the persistence Lossless semi-synchronous Replication on MySQL 5.7.2
- GTID mode of replication Client Auto-sensing GTID, ensuring gtid_executed and gtid_purged are not lost enabling Global Transaction Identifiers without downtime in MySQL 5.7.6
- No-blocking
SHOW SLAVE STATUS
- Mysqlbinlog support rewrite-DB can overwrite db in rowevent according to the instructions
- Add more replication-related state variables to the performance Schema Table
- Binlogging xa-prepared transactions full support for XA transactions in replication
Reference
What ' s New in MySQL 5.7 GA
MySQL 5.7 GA new features