Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge about "what features MYSQL8 adds compared with MYSQL 5.7". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

I. New system dictionary table

Integrated transaction data dictionary storing information about database objects, all metadata stored with InnoDB engine

II. Security and User Management

Added caching_sha2_password authentication plug-in, and is the default authentication plug-in. Performance and safety enhancements

Permission support role

Added password history feature to restrict reuse of previous passwords

Third, innodb enhancement

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

InnoDB temporary tables are created in the shared temporary table space ibtmp1

For SELECT... FOR SHARE AND SELECT... For UPDATE statements, 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. A minimum value of 2 ensures that rollback segments are always created in the undo table space, not the system table space

Support ALTER TABLESPACE... RENAME TO Syntax

New INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view

Added dynamic config item innodb_deadlock_detect to disable deadlock checking, because deadlock checking slows down the database significantly when a large number of threads are waiting for the same lock in a highly concurrent system

Support for using innodb_directories option to move or restore tablespace files to a new location when server is offline

Add innodb_dedicated_server, so that InnoDB automatically configures innodb_buffer_pool_size, innodb_log_file_size, innodb_flush_method according to the amount of memory detected on the server. When innodb_dedicated_server is enabled, InnoDB automatically configures the following options based on the amount of memory detected on the server:

innodb_dedicated_server: Automatically configure buffer pool size

MySQL 8.0 better supports document databases and JSON

Invisible index, invisible index is supported, index can be set invisible during SQL optimization, optimizer will not use invisible index

Descending index is supported. DESC can be defined for index. Before, index can be scanned in reverse order, but it affects performance. Descending index can be done efficiently.

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

Regular expression enhancement, providing REGEXP_LIKE(), EGEXP_INSTR(), REGEXP_REPLACE(), REGEXP_SUBSTR() and other functions

Added backup locks to allow DML during online backups while preventing operations that could cause snapshot inconsistencies. Backup lock supported by LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE syntax

Default character set changed from latin1 to utf8mb4

V. Configuration file enhancement

MySQL version 8.0 supports online modification of global parameter persistence. By adding PERIST keyword, you can persist the adjustment to the new configuration file. Restart db again and apply it to the latest parameters. For commands that add the PERIST keyword to modify parameters, MySQL generates a mysqld-auto.cnf file that contains data in json format, such as:

set PERIST binlog_expire_logs_seconds = 604800 ; #Memory and json files are modified, restart still takes effect

set GLOBAL binlog_expire_logs_seconds = 604800 ; #Modify memory only, restart lost

The system generates a mysqld-auto.cnf file in the data directory that contains the following:

{ "mysql_server": {" binlog_expire_logs_seconds ": "604800" } }

When my.cnf and mysqld-auto.cnf exist simultaneously, the latter has high priority.

VI. Histogram

MySQL version 8.0 begins to support long-awaited histograms. The optimizer uses the column_statistics data to determine the distribution of field values and get a more accurate execution plan.

ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS| DROP HISTOGRAM ON clo_name] to collect or delete histogram information

Session level SET_VAR is supported to dynamically adjust some parameters, which is conducive to improving statement performance.

Refer to select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1); VII. InnoDB performance improvement

Abolish buffer pool mutex, split the original mutex into multiple, improve concurrency split LOCK_thd_list and LOCK_thd_remove these two mutex, can improve thread link efficiency about 5%.

1. Line cache

MySQL 8.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 appropriate size to store the required data. The performance of continuous scanning of large volumes of data will benefit from a larger record buffer.

2. Improve scanning performance

Improved performance of InnoDB range queries by 5-20% for full-table and range queries.

3. Cost model

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

"MYSQL8 compared to MYSQL 5.7 added what features" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.