MySQL 8.0 new Features

I. Features added in MySQL 8.0

1. New System Dictionary Table

Consolidates transactional data dictionaries that store information about database objects, all of which are stored with the InnoDB engine

2. Support for DDL atomic operation

The DDL of the InnoDB table supports transactional integrity, either successfully or rollback, and the DDL operations rollback log is written to the Data Dictionary dictionary table mysql.innodb_ddl_log for rollback operations

3. Security and User management

• Added Caching_sha2_password authentication plugin, and is the default authentication plugin. Enhanced performance and security

• Permissions Support Role

• New password history feature to limit reuse of previous passwords
  

4. Support Resource Management

Support for creating and managing resource groups and allowing threads running by the server to be assigned to specific groups so that threads are executed based on resources available to the resource group

5. InnoDB Enhancement

• Self-increment column optimization, fix mysql bug#199, this bug causes when the DB restarts, MySQL will be the maximum value of self-increment on the table as the maximum, the next allocation is to allocate Max (ID) +1, if it is an archive table or other mode delete data, the DB system restarts, self-increment may be reused

• Added Information_schema. Innodb_cached_indexes, view the number of index pages per index cache in the INNODB buffer pool

• InnoDB temporary tables are created in shared temporary tablespace IBTMP1

• For select ... For Share and select ... For UPDATE statement, INNODB supports nowait and skip LOCKED

• The minimum value for Innodb_undo_tablespaces is 2, and innodb_undo_tablespaces is no longer allowed to be set to 0. The minimum value of 2 ensures that the rollback segment is always created in the undo tablespace instead of being created in the system table space

• Support for ALTER tablespace ... RENAME to Syntax

• Add Innodb_dedicated_server to allow InnoDB to automatically configure the amount of memory detected on the server Innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_ Method

• Added Information_schema. Innodb_tablespaces_brief View

• A new dynamic configuration item, Innodb_deadlock_detect, is added to disable deadlock checking because in high-concurrency systems, when a large number of threads are waiting for the same lock, deadlock checking slows down the database significantly

• Supports the use of the innodb_directories option to move or restore a tablespace file to a new location when the server is offline
  

6. MySQL 8.0 better supports document database and JSON

7. Optimization

• An invisible index that starts to support invisible index (which feels like Oracle again), can be indexed as invisible during SQL optimization, and the optimizer will not take advantage of an invisible index

• Descending indexes are supported, and DESC can be defined on an index, before the index can be reversed, but it affects performance, and descending indexes can be done efficiently

8, support rank (), LAG (), NTILE () and other functions

9, regular expression enhancement, provides regexp_like (), Egexp_instr (), Regexp_replace (), Regexp_substr () and other functions

10. Add a backup lock to allow DML during online backup while preventing operations that may cause snapshot inconsistencies. Backup lock supported by lock INSTANCE for backup and unlock INSTANCE syntax

11. Character set default character set changed from Latin1 to UTF8MB4

12. configuration file Enhancement

MySQL version 8.0 supports online modification of global parameter persistence, by adding the PERSIST keyword, you can persist the adjustment to the new configuration file, and restart the DB again to apply the latest parameters. For the Add PERSIST keyword modify parameter command, the MySQL system generates a MYSQLD-AUTO.CNF file that contains JSON-formatted data, such as execution:

Set PERSIST expire_logs_days=10; #内存和json文件都修改, restart is also in effect

Set GLOBAL expire_logs_days=10; #只修改内存, reboot lost

The system generates a MYSQLD-AUTO.CNF file with the following content in the data directory:

{"Mysql_server": {"expire_logs_days": "10"}}

When My.cnf and mysqld-auto.cnf are present at the same time, the latter has a high priority.

13. Histogram

MySQL version 8.0 began to support the long-awaited histogram. The optimizer uses Column_statistics data to determine the distribution of the value of the field and to get a more accurate execution plan.

You can use ANALYZE TABLE table_name [UPDATE histogram on col_name with N BUCKETS | Drop histogram on clo_name] to collect or delete histogram information

14, support session level Set_var dynamic adjustment of some parameters, is conducive to improve the performance of the statement.

Select/*+ Set_var (sort_buffer_size = 16M) */ID from Test order ID;

Insert/*+ Set_var (foreign_key_checks=off) */into Test (name) values (1);

15. Adjustment of default parameters

Adjust the default value of Back_log, maintain and max_connections consistent, enhance the connection processing capacity caused by burst traffic.

Modify Event_scheduler default to On, which is turned off by default.

Adjust the default value for Max_allowed_packet from 4M to 64M.

Adjust the Bin_log,log_slave_updates default value to ON.

Adjust the Expire_logs_days expiration time is 30 days, the old version is 7 days, when the production environment, check the parameter, to prevent excessive binlog caused space tension.

Adjust innodb_undo_log_truncate default to On

Adjust the innodb_undo_tablespaces default value to 2

Adjust the INNODB_MAX_DIRTY_PAGES_PCT_LWM default value of 10

Adjust the innodb_max_dirty_pages_pct default value to 90

New Innodb_autoinc_lock_mode default value is 2

16, InnoDB Performance improvement

The buffer pool mutex is abolished, splitting the original mutex into multiple, increasing the concurrency

Splitting the two mutexes of lock_thd_list and lock_thd_remove can increase the efficiency of line subroutines by about 5%.

17, Row cache

The MySQL8.0 optimizer can estimate the number of rows to be read, so it can provide the storage engine with a suitable size row buffer to store the required data. The performance of a large volume of continuous data scans will benefit from a larger record buffer

18. Improved scanning performance

Improve the performance of InnoDB range queries to improve the performance of full table queries and range query 5-20%.

19. Cost Model

The InnoDB buffer can estimate how many tables and indexes are in the buffer zone, which allows the optimizer to choose the way to know whether data can be stored in memory or must be stored on disk.

20. Refactoring SQL Analyzer

Improved SQL Analyzer. The old parser has serious limitations due to its syntactic complexity and top-down parsing, which makes it difficult to maintain and extend.

Ii. characteristics of abandonment in MySQL8.0

• Obsolete Validate_password plug-in

• Discard alter tablespace and drop tablespace engine clauses

• Abandoned Json_merge (), Json_merge_preserve () replaced

• Obsolete Have_query_cache System Variables
  

Third, the function that MySQL8.0 is removed

• The query cache function is removed and related system variables are removed

• mysql_install_db by mysqld--initialize or--initialize-insecure substitution

• The Innodb_locks and Innodb_lock_waits tables under Information_schema have been deleted. Replace with performance Schema data_locks and Data_lock_waits tables
  

• Four tables removed from Information_schema: Global_variables, Session_variables, Global_status, Session_status
  

• InnoDB no longer supports compressed temporary tables.
  

• PROCEDURE analyse () syntax is no longer supported
  

Renamed InnoDB Information Schema views

Old name New Name

Innodb_sys_columns Innodb_columns

Innodb_sys_datafiles Innodb_datafiles

Innodb_sys_fields Innodb_fields

Innodb_sys_foreign innodb_foreign

Innodb_sys_foreign_cols Innodb_foreign_cols

Innodb_sys_indexes innodb_indexes

Innodb_sys_tables Innodb_tables

Innodb_sys_tablespaces innodb_tablespaces

Innodb_sys_tablestats Innodb_tablestats

Innodb_sys_virtual innodb_virtual

The server option for remove:

--temp-pool

--ignore-builtin-innodb

--des-key-file

--log-warnings

--ignore-db-dir

Configuration options for Remove:

Innodb_file_format

Innodb_file_format_check

Innodb_file_format_max

Innodb_large_prefix

System variable for Remove

Information_schema_stats->information_schema_stats_expiry

Ignore_builtin_innodb

Innodb_support_xa

show_compatibility_56

Have_crypt

Date_format

Datetime_format

Time_format

Max_tmp_tables

Global.sql_log_bin (session.sql_log_bin reserved)

Log_warnings->log_error_verbosity

Multi_range_count

Secure_auth

Sync_frm

Tx_isolation-Transaction_isolation

Tx_read_only-Transaction_read_only

Ignore_db_dirs

Query_cache_limit

Query_cache_min_res_unit

Query_cache_size

Query_cache_type

Query_cache_wlock_invalidate

Innodb_undo_logs-->innodb_rollback_segments

The state variable of remove

Com_alter_db_upgrade

Slave_heartbeat_period

Slave_last_heartbeat

Slave_received_heartbeats

Slave_retried_transactions, slave_running

Qcache_free_blocks

Qcache_free_memory

Qcache_hits

Qcache_inserts

Qcache_lowmem_prunes

Qcache_not_cached

Qcache_queries_in_cache

Qcache_total_blocks

Innodb_available_undo_logs status

The Remove function

Json_append ()-Json_array_append ()

ENCODE ()

DECODE ()

Des_encrypt ()

Des_decrypt ()

Remove's client option:

--ssl--ssl-verify-server-cert is deleted, with--ssl-mode=verify_identity | REQUIRED | DISABLED replacement

--secure-auth

Reference: Https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-additions

MySQL 8.0 new Features