Oracle on Oct 19 2015 announced the general availability of MySQL 5.7, the latest version of the world’s most popular open source database. The new version delivers greater performance, scalability and manageability, plus enhanced NoSQL capabilities with JSON support and MySQL Router, which makes it easy to connect applications to multiple MySQL databases. MySQL 5.7 is the best release ever of the world’s most popular open source database and provides a new, advanced feature set designed to enable those who are building the next generation of web-based and embedded applications and services.
Key enhancements in MySQL 5.7, which deliver greater performance and scalability, include:
- Enhanced Speed: In benchmark tests using SysBench Read-only Point-Selects, at 1,024 connections, MySQL 5.7 delivered 1,600,000 queries per second (QPS) — 3x faster than MySQL 5.6.
- Optimized InnoDB: New capabilities include increased performance and concurrency, enhanced on-line operations, spatial indexes, native partitioning and more.
– Now you can resize InnoDB buffer pool online. Since MySQL 5.7 innodb_buffer_pool_size is a dynamic variable which provides the ability to resize buffer pool without restarting MySQL server.
– From MySQL 5.7, online ALTER TABLE also supports RENAME INDEX clause to rename an index. This change will take in place without table copy operation.
– InnoDB supports Transportable Tablespace feature for partitioned InnoDB tables. I wrote a blog post on Transportable Tablespace that you will find useful.
– Innochecksum utility is enhanced with new options. I also wrote a recent blog post on this same topic.
– As of MySQL 5.7, InnoDB supports “spatial indexes” and it also supports online DDL operation to add spatial indexes i.e. ALTER TABLE .. ALGORITHM=INPLACE.
Improved InnoDB buffer pool dump/reload operations. A new system variable, innodb_buffer_pool_dump_pct allows you to specify percentage of most recently used pages in each buffer pool to read out and dump.
- More Robust Replication: Enhancements to MySQL’s replication features include multi-source replication, enhanced Global Transaction Identifiers (GTIDs), and improved multi-threaded slaves for better scalability and availability.
– With the new CHANGE REPLICATION FILTER command now you can modify replication filters rules without bouncing MySQL servers.
– Since MySQL 5.7 you can perform CHANGE MASTER TO without stopping the slave via the STOP SLAVE command.
– There is now a different method for parallel replication. With new implementation the slave can apply transaction in parallel with single database/schema too.
– Global Transaction Identifiers (GTID) is a feature that automatically tracks the replication position in replication stream.
- Enhanced Optimizer: A new MySQL optimizer dynamic cost model provides better query performance and greater user control.
– EXPLAIN FOR CONNECTION will let you run explain statements for already running queries. This may yield important information towards query optimization.
– The optimizer avoids the creatation temporary table for result of UNION ALL queries and this will help to reduce disk I/O and disk space when UNION yields large result set.
– JSON format for EXPLAIN is enhanced in version 5.7 by printing total query cost which makes it easier to see the difference between the good and bad execution plans.
– MySQL 5.7 now supports generated columns also known as virtual columns as new feature.
- Triggers: MySQL 5.7 now supports multiple triggers per table for trigger event (DML) and timing (BEFORE,AFTER) i.e. multiple triggers are permitted now for each event e.g. multiple triggers on INSERT action.
Primary manageability enhancements within MySQL 5.7 include:
- New Native JSON Data Type and JSON Functions: Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index management on the JSON Documents using generated columns.
- Performance Schema: Enables instrumentation for memory, transactions, stored routines, prepared statements, replication and locks.
- MySQL SYS Schema: Provides helper objects that answer common performance, health, usage and monitoring questions.
- Improved Security: Delivers easier and safer instance initialization, setup and management.
- Expanded Geographic Information System (GIS) Support for Mobile Applications: Provides spatial index support in InnoDB, GeoJSON and GeoHash support.