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

This article mainly explains "what are the new features of MySQL 8". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the new features of MySQL 8"?

Authorization is shown in the Innodb table, which used to be MyISAM, which will enjoy the following benefits:

There may be some successes and some failures when changing multiple users at the same time. No atomicity

Now there is atomicity. Either they succeed or they all fail. 13.1.1

The default authentication plug-in is caching_sha2_password for better performance and more secure 6.4.1.3

Support for roles 6.2.10

Resource Manager 8.12.5

Innodb enhancement

Self-increasing field modification to join redo persistence

Persisted if the modified value is greater than the self-increment

Innodb_deadlock_detect can turn off deadlock detection

INFORMATION_SCHEMA.INNODB_CACHED_INDEXES can view cached data

Support for for update nowait operation

Innodb uses mysql's data dictionary 14

Temporary tables are stored in the ibtmp1 directory

Partition tables use native and support online DDL

Mysql system tables and variables are now put into innodb tablespace called mysql.ibd

Innodb_undo_log_truncate is enabled by default

Innodb_rollback_segments parameter meaning, from global to each undo tablespace

The default value of the innodb_max_dirty_pages_pct_lwm parameter is changed to 10

Innodb_max_dirty_pages_pct increased from 75 to 90

The default setting of innodb_auto_lock_mode is 2, and the self-increasing lock decreases.

The innodb_dedicated_server parameter is added. The default is OFF, which is used to automatically configure some parameters, including:

Innodb_buffer_pool_size

Innodb_log_file_size

Innodb_flush_method

The INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF view can view tablespace types

Innodb supports atomic DDL

SDI joins Serialized dictionary information (SDI), other data except temporary files and UNDO tablespaces

The files contain SDI data, which is an easy table metadata that can be used directly from the tablespace if the data dictionary is corrupted

Extract metadata, which is a compressed format JSON

Redo record optimization

Start an exclusive redo writer thread

User threads write dirty data to flush list using relaxed order

New parameters are added

Innodb_log_wait_for_flush_spin_hwm: define the maximum average log write time innodb_log_spin_cpu_abs_lwm: define the minimum total CPU usage innodb_log_spin_cpu_pct_hwm: define the maximum total CPU usage

These three parameters need to be further studied.

Innodb_log_buffer_size: reclaimed memory can be modified dynamically

Undo logs support 8.0.12 for small LOB data

Instantly add field support. The algorithm is ALGORITHM=INSTANT, and the INSTANT algorithm is preferred.

A new temptable storage engine is added to the parameter internal_tmp_mem_storage_engine parameter. The allowed values include

Temptable defaults to the previous memory. 8.0.13 blob data types are supported

Turn off the parameter innodb_buffer_pool_in_core_file

8.0.13 temporary tables created by users and internal temporary tables are stored in temporary tablespaces when session is disconnected

Ibtmp1 will be released, but the disk space will not be returned to the operating system. The parameter innodb_temp_tablespaces_dir will be added.

Set to # innodb_tmp when the session is disconnected, the space will be truncated and then released to the pool.

Operating system.

Innodb_dedicated_server can dynamically assign some parameters 8.0.14

Innodb_buffer_pool_size

Innodb_log_file_size

Innodb_flush_method

Innodb_parallel_read_threads parameter parallel clustered index scan is used to optimize check table performance

If the temptable_max_ram memory size is exceeded, temporary files or innodb tables will be used. By default, mmap will be used for mapping files and temptable_use_mmap will be used for control. The new temporary table engine temptable and the old memory as a comparison between the old or the tmp_table_size or max_heap_table_size

Innodb_spin_wait_pause_multiplier parameter is added

Innodb_parallel_read_threads parameters control concurrent execution

Character set change

The default character set uses utf8mb4 instead of latin1, and contains the new check character set utf8mb4_ja_0900_as_cs

Optimizer Enhancement

Invisible index

Descending index

Functional index

Type return detection

For example, c is a TINYINT UNSIGNED that occupies a maximum of 1 byte under the condition of using c

= 255 converted to clocked 255

8.0.16 semi-join supports exists subquery

8.0.17 reverse half connection supports not in/not EXISTS

WITH AS syntax support

Windowing function

RANK (), LAG (), NTILE ()

Configure the management port

If max_connections is reached, the connection management port is set for management.

Need to configure admin_address to IP value, which is not enabled by default

Need to configure admin_port port default 33062

There is no connection limit

SERVICE_CONNECTION_ADMIN permission is required

8.0.17 innodb archiving support

8.0.17 innodb Clone plug-in

The upper limit of hostname support ranges from 60 to 255.

Thank you for your reading, the above is the content of "what are the new features of MySQL 8". After the study of this article, I believe you have a deeper understanding of what the new features of MySQL 8 have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.