Shulou(Shulou.com)06/01 Report--

MySQL 5.6

1)。 Support for GTID replication

2)。 Support for lossless replication

3)。 Support for delayed replication

4)。 Support for parallel replication based on library level

5) .mysqlbinlog command supports remote backup of binlog

6)。 TIME, DATETIME and TIMESTAMP are refactored to support fractional seconds. The space requirement of DATETIME is also reduced from the previous 8 bytes to

5 bytes

7)。 Online DDL is supported. The ALTER operation no longer blocks DML.

8)。 Support for transportable tablespaces (transportable tablespaces)

9)。 Support the persistence of statistics. Avoid differences in the execution plan of the same SQL between masters and slaves or after database restart

10)。 Support for full-text indexing

11)。 Support for InnoDB Memcached plugin

12). EXPLAIN can be used to view the execution plan of DML operations such as DELETE,INSERT,REPLACE,UPDATE. Previously, only SELECT operations were supported.

13)。 Enhancements to partitioned tables, including an increase in the maximum number of available partitions to 8192, support for data exchange between partitioned and non-partitioned tables, and explicitly specify partitions during operation

14). The limit on the total size of Redo Log extends from 4G to 512G.

15). Undo Log can be saved in a separate tablespace, and because it is a random IO, it is more suitable for SSD. But it still does not support automatic space recycling.

16)。 Support the status of dump and load Buffer pool when MySQL is online and closed to avoid long warm-up time after database restart

17). Performance improvement within InnoDB, including splitting kernel mutex and introducing independent refresh threads, which can set up multiple purge threads

18)。 The performance of the optimizer is improved, and features such as ICP,MRR,BKA are introduced to optimize the subquery.

MySQL5.7

1)。 Support for group replication and InnoDB Cluster

2)。 Support for multi-source replication

3) support enhanced semi-synchronization (AFTER_SYNC)

4)。 Supports table-level (LOGICAL_CLOCK)-based parallel replication

5)。 GTID replication can be enabled online

6)。 Support online setting of replication filtering rules

7)。 Support to modify the size of Buffer pool online

8)。 Within the encoded bytes of the same length, you only need to modify the metadata of the table to modify the size of the VARCHAR, without the need to create a temporary table

9)。 Supports memory allocation policies (innodb_numa_interleave) that can be set for NUMA schemas

10)。 Support for transparent page compression (Transparent Page Compression)

11)。 Support automatic collection of UNDO tablespaces

12)。 Support the enhancement and refactoring of query optimizer

13)。 You can view the execution plan (EXPLAIN FOR CONNECTION) of the SQL currently being executed by session

14)。 The query rewriting plug-in (Query Rewrite Plugin) is introduced, which can rewrite the query on the server.

15). EXPLAIN FORMAT=JSON will display cost information, which can visually compare the advantages and disadvantages of the two execution plans

16)。 Introduced virtual columns, similar to functional indexes in Oracle

17)。 New instances no longer create test databases and anonymous users by default

18)。 Introduce ALTER USER command, which can be used to change user's password, password expiration policy, and lock users, etc.

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

20)。 Support for tablespace encryption

21)。 Optimized Performance Schema with reduced memory usage

Performance Schema introduces a large number of instrumentation. Commonly used is Memory usage instrumentation, which can be used to view

Memory usage of MySQL, Metadata Locking Instrumentation, which can be used to view MDL holdings.

Stage Progress instrumentation, which can be used to check the progress of Online DDL

23)。 Same trigger event (INSERT,DELETE,UPDATE) and same trigger time (BEFORE,AFTER). Multiple triggers are allowed to be created.

Until then, only one trigger is allowed

24). InnoDB natively supports partitioned tables, which was previously implemented through the ha_partition interface

25)。 Partitioned tables support transportable tablespace features.

26). The integration of SYS database simplifies the management of MySQL and the location of abnormal problems.

27). Natively supports JSON types and introduces a number of JSON functions

28). A new logical backup tool mysqlpump is introduced to support multi-threaded backup at the table level.

29). A new client tool, mysqlsh, is introduced, which supports three languages: JavaScript and Python and SQL. Two kinds of API:X DevAPI

AdminAPI, where the former can operate MySQL as a document database, and the latter is used to manage InnoDB Cluster

30). Mysql_install_db is replaced by mysqld-- initialize, which is used to initialize the instance

31). Native support for systemd

32). Introduced the super_read_only option

33). You can set the timeout for SELECT operations (max_execution_time)

34). You can shut down the MySQL instance through the SHUTDOWN command.

35). The innodb_deadlock_detect option has been introduced, which can be used to turn off deadlock detection in high concurrency scenarios

36). Optimizer Hints was introduced to control the behavior of the optimizer at the statement level, such as whether to turn on ICP,MRR, etc. Before that, only Index Hints

37). Enhancements to GIS, including the use of Boost.Geometry instead of the previous GIS algorithm, and InnoDB began to support spatial indexing

MySQL8.0

1)。 A native, InnoDB-based data dictionary is introduced. The data dictionary table is located in the mysql library and is not visible to users, just like other system tables in the mysql library

Save it in the mysql.ibd file in the data directory. No longer placed in the mysql directory

2)。 Refactored INFORMATION_SCHEMA, where some of the tables have been refactored into views based on data dictionaries, which were previously temporary tables

3). PERFORMANCE_SCHEMA query performance improvement, which has multiple indexes built in

4)。 Support for invisible indexes (Invisible index)

5)。 Support for descending indexes

6)。 The optimizer adds histogram function to compare Oracle

7)。 Support for common table expressions (Common table expressions)

8)。 The window function (Window functions) is supported.

9)。 Support for role (Role) features, compared to Oracle

10)。 Support resource groups (Resource Groups), which can be used to control the priority of threads and the resources they can use. Currently, the only resources that can be managed are CPU

11)。 The innodb_dedicated_server option is introduced to dynamically set innodb_buffer_pool_size based on the memory of the server

Innodb_log_file_size and innodb_flush_method

12)。 Support second plus field (Instant add column) function

13) partial update of the JSON field (JSON Partial Updates)

14)。 Support the persistence of self-increasing primary keys

15)。 Support for persistable global variables (SET PERSIST)

16)。 The default character set is changed from latin1 to utf8mb4

17)。 UNDO tablespaces are enabled by default, and online quantity adjustment (innodb_undo_tablespaces) is supported. In MySQL 5.7, it is not enabled by default

To turn it on, it can only be set during initialization.

18)。 Backup locks are supported

19). Optimization of Redo Log, including allowing multiple user threads to write to log buffer concurrently, and dynamically modifying the size of innodb_log_buffer_size

20)。 The default authentication plug-in is changed from mysql_native_password to caching_sha2_password

21)。 The default memory temporary table is changed from the MEMORY engine to the TempTable engine, and the latter supports variable length storage of VARCHAR compared to the former.

Variable length fields such as VARBINARY. Starting with MySQL 8.0.13, the TempTable engine supports BLOB fields

22) .Grant no longer implicitly creates users

23). SELECT. FOR SHARE and SELECT... NOWAIT and SKIP LOCKED options are introduced into the FOR UPDATE statement to solve the problem of hot lines in e-commerce scenarios.

24)。 Regular expression enhancement, adding 4 related functions, REGEXP_INSTR (), REGEXP_LIKE (), REGEXP_REPLACE ()

REGEXP_SUBSTR ()

25)。 When making an execution plan, the query optimizer considers whether the data is in the Buffer Pool. Before that, it was assumed that the data was on disk.

26). 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

27). More fine-grained permissions have been introduced to replace SUPER permissions, and now granting SUPER permissions prompts warning

28). GROUP BY statements are no longer implicitly sorted

29). The innodb_locks and innodb_lock_ tables in information_schema are removed and replaced by those in performance_schema

Data_locks and data_lock_ tables

30). The performance_schema.variables_ info table is introduced to record the source and modification of the parameters.

31). Added statistics for client error messages (performance_schema.events_errors_summary_xxx)

32). Statistical response time distribution of queries (call sys.ps_statement_avg_latency_histogram ())

33). Direct modification of column names (ALTER TABLE... RENAME COLUMN old_name TO new_name)

34). User password can be set retry policy (Reuse Policy)

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

36). The code layer removes the Query Cache module, so Query Cache-related variables and operations are no longer supported

37). BLOB, TEXT, GEOMETRY, and JSON fields allow you to set default values

38). Restart the MySQL instance through the RESTART command

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.