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

This article mainly introduces what are the newly added functions of MySQL 8.0. the content of the article is carefully selected and edited by the author, which is of certain pertinence and is of great significance to everyone's reference. let's work with the author to understand what the newly added features of MySQL 8.0 are.

I. functions added in MySQL 8.0

1. New system dictionary table

Integrates transaction data dictionaries that store information about database objects, and all metadata is stored using the InnoDB engine

2. Support DDL atomic operation

The DDL of the InnoDB table supports transaction integrity. Either succeed or roll back. Write the DDL operation rollback log to the data dictionary data dictionary table mysql.innodb_ddl_log for rollback operations.

3. Security and user management

The caching_sha2_password authentication plug-in is added and is the default authentication plug-in. Enhanced performance and security

Permissions support role

New password history feature to restrict the reuse of previous passwords

4. Support resource management

Supports the creation and management of resource groups, and allows threads running on the cloud server to be assigned to specific groups so that threads can execute according to the resources available to the resource group

5. Innodb enhancement

Self-adding column optimization, fix MySQL's bug#199, this bug causes MySQL to take the maximum self-increment on the table as the maximum value when DB restarts, and the next allocation is to allocate max (id) + 1. If the data is deleted from the archived table or other schema, the DB system restarts, and the self-increment may be reused.

Add INFORMATION_SCHEMA.INNODB_CACHED_INDEXES to view the number of index pages cached in the InnoDB buffer pool for each index

InnoDB temporary tables will be created in the 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 setting innodb_undo_tablespaces to 0 is no longer allowed. The minimum value of 2 ensures that the rollback segment is always created in the undo tablespace, not in the system tablespace

Support for ALTER TABLESPACE... RENAME TO syntax

Add innodb_dedicated_server to allow InnoDB to automatically configure innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_method based on the amount of memory detected on the CVM

New INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view

A new dynamic configuration item, innodb_deadlock_detect, has been added to disable deadlock checking, because in highly concurrent systems, deadlock checking can greatly slow down the database when a large number of threads wait for the same lock

Support to use the innodb_directories option to move or restore tablespace files to a new location when the cloud server is offline

6. MySQL 8.0 provides better support for document databases and JSON

7. Optimization

Invisible index, start to support invisible index (feel like Oracle again), in the process of optimizing SQL, the index can be set to invisible, and the optimizer will not take advantage of invisible index.

Support descending index, you can define DESC on the index, before, the index can be scanned in reverse order, but the performance is affected, and the descending index can be completed efficiently

8. Support RANK (), LAG (), NTILE () and other functions

9. Regular expressions are enhanced by providing functions such as REGEXP_LIKE (), EGEXP_INSTR (), REGEXP_REPLACE (), REGEXP_SUBSTR (), etc.

10. Add a backup lock to allow DML during online backup and prevent operations that may lead to inconsistent snapshots. Backup locks are supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax

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

12. Profile enhancement

MySQL version 8.0 supports online modification of global parameter persistence. By adding the PERSIST keyword, you can persist the adjustment to a new configuration file, and restart db again can also be applied to the latest parameters. For the command to modify parameters with the PERSIST keyword, the MySQL system generates a mysqld-auto.cnf file containing data in json format, such as executing:

Set PERSIST expire_logs_days=10; # memory and json files are modified, and restart is still in effect

Set GLOBAL expire_logs_days=10; # only modified memory and lost reboot

A file containing the following mysqld-auto.cnf is generated in the data directory:

{"mysql_server": {"expire_logs_days": "10"}}

When my.cnf and mysqld-auto.cnf exist at the same time, the latter has a high priority.

13. Histogram

MySQL version 8.0 began to support long-awaited histograms. The optimizer will use the column_statistics data to determine the distribution of the values of the fields and 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 to dynamically adjust some parameters, which is helpful to improve statement performance.

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 to keep it consistent with max_connections, and enhance the connection processing capacity brought by burst traffic.

Modify event_scheduler to default to ON, which was previously turned off by default.

Adjust the default value of max_allowed_packet from 4m to 64m.

Adjust the default value of bin_log,log_slave_updates to on.

Adjust the expiration time of expire_logs_days to 30 days, and the old version is 7 days. Check this parameter in the production environment to prevent space tension caused by too much binlog.

Adjust innodb_undo_log_truncate to ON by default

Adjust the innodb_undo_tablespaces default value to 2

Adjust the default value of innodb_max_dirty_pages_pct_lwm 10

Adjust the innodb_max_dirty_pages_pct default to 90

The default value of the new innodb_autoinc_lock_mode is 2

16. InnoDB performance improvement

Abolish buffer pool mutex and split the original mutex into multiple ones to improve concurrency

Splitting the two mutex, LOCK_thd_list and LOCK_thd_remove, can improve thread linking efficiency by about 5%.

17. Row caching

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

18. Improve scanning performance

Improve the performance of InnoDB range queries, which can improve the performance of full table queries and range queries by 5-20%.

19. Cost model

The InnoDB buffer can estimate how many tables and indexes are in the cache, which allows the optimizer to know whether the data can be stored in memory or must be stored on disk when choosing the access method.

20. Refactoring SQL parser

Improve the SQL parser. The old parser has serious limitations because of its syntax complexity and top-down analysis, which makes it difficult to maintain and expand.

II. Obsolete features in MySQL8.0

Obsolete validate_password plugin

Discard ALTER TABLESPACE and DROP TABLESPACE ENGINE clauses

Replacement of waste JSON_MERGE ()-> JSON_MERGE_PRESERVE ()

Discard have_query_cache system variabl

Third, the function that MySQL8.0 has been removed

The query cache function is removed and the related system variables are also removed

Mysql_install_db is replaced by mysqld-- initialize or-- initialize-insecure

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

Remove four tables under INFORMATION_SCHEMA: GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS

InnoDB no longer supports compressed temporary tables.

The 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

Server options 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 variables 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

State variables 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 function of remove

JSON_APPEND ()-> JSON_ARRAY_APPEND ()

ENCODE ()

DECODE ()

DES_ENCRYPT ()

DES_DECRYPT ()

Client options for remove:

-- ssl--ssl-verify-server-cert is deleted and replaced with-- ssl-mode=VERIFY_IDENTITY | REQUIRED | DISABLED

-- secure-auth

After reading the above about the newly added features of MySQL 8.0, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

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.