Check out the new features in MySQL 5.6, 5.7, and 8.0!

I believe many people are already familiar with the history of MySQL, so I won’t go into details here. The following is a summary of the milestone events in its development process only from the perspective of product characteristics.

Recommended: "mysql Video Tutorial"

In 1995, MySQL 1.0 was released for internal use only.

In 1996, MySQL 3.11.1 was released, skipping the MySQL 2.x version.

In 1999, MySQL AB was established. In the same year, MySQL 3.23 was released, which integrated the Berkeley DB storage engine. The engine was developed by Sleepycat and supports transactions. In the process of integrating the engine, the source code was modified, laying the foundation for the subsequent pluggable storage engine architecture.

In 2000, ISAM was upgraded to the MyISAM storage engine. In the same year, MySQL was open source based on the GPL agreement.

In 2002, MySQL 4.0 was released, integrating the later famous InnoDB storage engine. Developed by Innobase, this engine supports transactions and row-level locks, and is suitable for high-concurrency scenarios such as OLTP.

In 2005, MySQL 5.0 was released and began to support features such as cursors, stored procedures, triggers, views, and XA transactions. In the same year, Oracle acquired Innobase.

In 2008, Sun acquired MySQL AB for US$1 billion. In the same year, MySQL 5.1 was released, which began to support features such as timers (Event scheduler), partitioning, and row-based replication.

In 2009, Oracle acquired Sun Microsystems for US$7.4 billion.

In 2010, MySQL 5.5 was released, which includes the following important features and updates.

● InnoDB replaces MyISAM as the default storage engine for MySQL.

● Multi-core expansion can make full use of multi-core CPUs.

● InnoDB performance improvements include support for rapid index creation, table compression, I/O subsystem performance improvements, the PURGE operation is separated from the main thread, and the Buffer Pool can be split into multiple Instances.

● Semi-synchronous replication.

● Introduced the utf8mb4 character set, which can be used to store emoji expressions.

● Introduce metadata locks.

● Partition table enhancement, two new partition types: RANGE COLUMNS and LIST COLUMNS.

● MySQL Enterprise Edition introduces thread pool.

● The number of IO read and write threads can be configured (innodb_read_io_threads, innodb_write_io_threads). Prior to this, the number was 1 and was not configurable.

● Introduced the innodb_io_capacity option to control the number of dirty page refreshes.

In 2013, MySQL 5.6 was released, which includes the following important features and updates.

● GTID copy.

● Lossless copy.

● Delayed replication.

● Parallel replication based on library level.

● mysqlbinlog can remotely back up binlog.

● TIME, DATETIME and TIMESTAMP have been reconstructed to support fractional seconds. The space requirement of DATETIME is also reduced from the previous 8 bytes to 5 bytes.

● Online DDL. ALTER operations no longer block DML.

● Transportable tablespaces.

● Persistence of statistical information. Avoid differences in the execution plan of the same SQL between the master and slave or after the database is restarted.

● Full text index.

● InnoDB Memcached plugin.

● EXPLAIN can be used to view the execution plans of DML operations such as DELETE, INSERT, REPLACE, UPDATE, etc. Before this, only SELECT operations were supported.

● Enhancements to partition tables, including increasing the maximum number of available partitions to 8192, supporting data exchange between partitioned and non-partitioned tables, and explicitly specifying partitions during operation.

● The total size limit of Redo Log has been extended from the previous 4G to 512G.

● Undo Log can be saved in an independent table space. Because it is random IO, it is more suitable to be placed in SSD. But automatic reclamation of space is still not supported.

● The status of the buffer pool can be dumped and loaded to avoid the long warm-up time required after the database is restarted.

● InnoDB internal performance improvements include splitting the kernel mutex, introducing independent refresh threads, and setting up multiple purge threads.

● Optimizer performance has been improved, features such as ICP, MRR, and BKA have been introduced, and subqueries have been optimized.

It can be said that MySQL 5.6 is a milestone version in the history of MySQL, and it is also the most widely used version in production.

In 2015, MySQL 5.7 was released, which includes the following important features and updates.

● Group Replication

● InnoDB Cluster

● Multi-source replication

● Enhanced semi-synchronization (AFTER_SYNC)

● Based on WRITESET of parallel replication.

● Enable GTID replication online.

● Set copy filtering rules online.

● Modify the size of the Buffer pool online.

● Within the same length of coded bytes, modifying the size of VARCHAR only requires modifying the metadata of the table, without creating a temporary table.

● You can set the memory allocation strategy of NUMA architecture (innodb_numa_interleave).

● Transparent Page Compression.

● Automatic recycling of UNDO table space.

● Refactoring and enhancement of query optimizer.

● You can view the execution plan (EXPLAIN FOR CONNECTION) of the currently executing SQL.

● Introduced the Query Rewrite Plugin, which can rewrite queries on the server side.

● EXPLAIN FORMAT=JSON will display cost information, so that the advantages and disadvantages of the two execution plans can be intuitively compared.

● Introduced virtual columns, similar to function indexes in Oracle.

● New instances no longer create test database and anonymous users by default.

● Introducing the ALTER USER command, which can be used to modify user passwords, password expiration policies, and lock users.

● The field that stores passwords in the mysql.user table is changed from password to authentication_string.

● Table space encryption.

● Performance Schema is optimized and its memory usage is reduced.

● Performance Schema introduces many instrumentations. Commonly used ones include Memory usage instrumentation, which can be used to check the memory usage of MySQL, Metadata Locking Instrumentation, which can be used to check the holding status of MDL, and Stage Progress instrumentation, which can be used to check the progress of Online DDL.

● The same trigger event (INSERT, DELETE, UPDATE) and the same trigger time (BEFORE, AFTER) allow the creation of multiple triggers. Previously, only one trigger was allowed to be created.

● InnoDB natively supports partition tables. Before this, it was implemented through the ha_partition interface.

● Partitioned tables support the transportable table space feature.

● Integrated SYS database, simplifying MySQL management and locating abnormal problems.

● Natively supports JSON type and introduces many JSON functions.

● Introduced a new logical backup tool - mysqlpump, which supports table-level multi-threaded backup.

● Introduced a new client tool-mysqlsh, which supports three languages: JavaScript, Python and SQL. Two APIs: X DevAPI and AdminAPI. The former can operate MySQL as a document database, and the latter is used to manage InnoDB Cluster.

● mysql_install_db is replaced by mysqld --initialize, which is used to initialize the instance.

● Native support for systemd.

● The super_read_only option is introduced.

● You can set the timeout for the SELECT operation (max_execution_time).

● The MySQL instance can be shut down through the SHUTDOWN command.

● The innodb_deadlock_detect option is introduced. In high concurrency scenarios, this option can be used to turn off deadlock detection.

● Optimizer Hints are introduced, which can control the behavior of the optimizer at the statement level, such as whether to enable ICP, MRR, etc. Before this, there were only Index Hints.

● GIS enhancements, including using Boost.Geometry to replace the previous GIS algorithm, InnoDB begins to support spatial indexes.

In 2018, MySQL 8.0 was released, which includes the following important features and updates.

● Introduced native InnoDB-based data dictionary. The data dictionary table is located in the mysql library and is invisible to users. Like other system tables in the mysql library, it is stored in the mysql.ibd file in the data directory. It is no longer placed in the mysql directory.

● Atomic DDL.

● INFORMATION_SCHEMA has been reconstructed, in which some tables have been reconstructed into views based on data dictionary. Before that, they were temporary tables.

● PERFORMANCE_SCHEMA query performance has been improved, and multiple indexes have been built in.

● Invisible index.

● Descending index.

● Histogram.

● Common table expressions.

● Window functions.

● Role.

● Resource Groups can be used to control the priority of threads and the resources they can use. Currently, the only resource that can be managed is the CPU.

● The innodb_dedicated_server option is introduced, which can dynamically set innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method based on the server's memory.

● Quickly add columns (ALGORITHM=INSTANT).

● Partial updates of JSON fields (JSON Partial Updates).

● Persistence of auto-incremented primary keys.

● Persistible global variables (SET PERSIST).

● The default character set is changed from latin1 to utf8mb4.

● UNDO table space is enabled by default and supports online adjustment of the quantity (innodb_undo_tablespaces). In MySQL 5.7, it is not enabled by default. If you want to enable it, you can only set it during initialization.

● Backup lock.

● Redo Log optimization includes allowing multiple user threads to write to the log buffer concurrently and dynamically modify the size of innodb_log_buffer_size.

● The default authentication plug-in is changed from mysql_native_password to caching_sha2_password.

● The default memory temporary table is changed from the MEMORY engine to the TempTable engine. Compared with the former, the latter supports variable-length storage of VARCHAR, VARBINARY and other variable-length fields. Starting from MySQL 8.0.13, the TempTable engine supports BLOB fields.

● Grant no longer creates users implicitly.

● The NOWAIT and SKIP LOCKED options are introduced in the SELECT ... FOR SHARE and SELECT ... FOR UPDATE statements to solve the problem of hot rows in e-commerce scenarios.

● Regular expression enhancement, 4 new related functions have been added, REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), REGEXP_SUBSTR().

● The query optimizer will consider whether the data is in the Buffer Pool when formulating the execution plan. Before this, it was assumed that the data was on disk.

● The ha_partition interface is removed from the code layer. If you want to use partition tables, you can only use the InnoDB storage engine.

● More fine-grained permissions have been introduced to replace SUPER permissions. Granting SUPER permissions will now prompt a warning.

● GROUP BY statement no longer implicitly sorts.

● The table space encryption feature introduced in MySQL 5.7 can encrypt Redo Log and Undo Log.

● The innodb_locks and innodb_lock_waits tables in information_schema are removed and replaced by the data_locks and data_lock_waits tables in performance_schema.

● Introduce the performance_schema.variables_info table to record the source and modification of parameters.

● Added statistics on client error information (performance_schema.events_errors_summary_xxx).

● The response time distribution of queries can be counted (call sys.ps_statement_avg_latency_histogram()).

● Supports direct modification of column names (ALTER TABLE ... RENAME COLUMN old_name TO new_name).

● The user password can set the retry policy (Reuse Policy).

● Remove the PASSWORD() function. This means that the user password cannot be modified through the "SET PASSWORD ... = PASSWORD('auth_string')" command.

● The Query Cache module has been removed from the code layer, so Query Cache related variables and operations are no longer supported.

● BLOB, TEXT, GEOMETRY and JSON fields allow setting default values.

● You can restart the MySQL instance through the RESTART command.

It should be noted that the release mentioned above generally refers to the GA version.

Finally, take a look at the table below. The table gives the release times of the recent major versions, and as of the publication of this book, its latest minor version and its release time. .

https://en.wikipedia.org/wiki/MySQL#Release_history

From the data in the table,

1. A major version will be released approximately every 3 years.

2. The product support cycle is generally 8 years.

3. I thought MySQL 5.5 was an antique, but the official version is still being updated.

Original address: https://www.cnblogs.com/ivictor/p/9807284.html

The above is the detailed content of Check out the new features in MySQL 5.6, 5.7, and 8.0!. For more information, please follow other related articles on the PHP Chinese website!