编程知识 cdmana.com

[51 learning workshop arrangement] detailed explanation of MySQL 8.0 Technology

MySQL8.0  brief introduction

MySQL 5.7 To 8.0,Oracle The official jump Major Version Version number , What followed was that MySQL 8.0 A lot of major updates have been made on , On the way to enterprise database , new Data Dictionary Design , Support Atomic DDL, New version upgrade strategy , Strengthen security and account management ,InnoDB Enhancements, etc , At present, the small version has release To 8.0.16, New features are still coming out .

MySQL8.0  Version update

1. The data dictionary

MySQL 8.0 Abandoned Server Layer Defined FRM Files and other non transactional tables , A set of InnoDB Come on Save data dictionary , Support transaction features .

2. Atomic DDL

stay Data Dictionary On the basis of supporting transaction features ,8.0 Added one DDL log Dictionary table , To coordinate in

DDL In the process , For the data dictionary , Modification of file system and transaction system , Be atomic .

3. upgrade

from 8.0.16 Start , For system table modification , Abandoned mysql_upgrade Tools , When the system restarts , upgrade .

4. Security and account management

Account number , from 8.0 Start , Support role Convenient management of permissions , And add multiple system permissions , They have been added respectively ROLE_EDGES,GLOBAL_GRANTS Two system tables ;mysql schema The following tables are related to permissions and users Change to InnoDB engine , Support transaction features , Ensure the atomicity of account management statements .

Certification ,caching_sha2_password As the default Authentication plugin, To improve safety , But be careful not to be with 8.0 Previous client For compatibility .

Link encryption , If it's compiled OpenSSL 1.1.1 And above ,MySQL 8.0 SSL Will support to TLSv1.3 edition .

System account number , stay 8.0.16 newly added SYSTEM_USER jurisdiction , Used to distinguish System account or ordinary account , It can be Manage by category .

5. Auto increment Persistence

stay InnoDB In the engine , Added a system table private to the engine innodb_dynamic_metadata, Keep it since you add value In this watch , Make changes to each table auto increment When it's worth it , All use redo log To protect , Doing it heckpoint When , Persist to this system table , Make sure that after the next restart ,auto increment Be able to recover from persistence Come back , And it is not affected by the transaction context rollback .

6. Deadlock detection

In the case of high concurrency ,InnoDB In the engine for Transaction lock Deadlock detection for , It's going to affect performance scale An important factor in ,8.0 A parameter is provided after innodb_deadlock_detect:https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_deadlock_detect, Deadlock detection to turn the engine on or off , Where the business is able to identify the relevant risks , Turn off deadlock detection , It can greatly improve the concurrent ability .

7. A temporary table

stay InnoDB In the engine , Temporary tables created by users will be unified to ibtmp In the temporary table space of the file ; For the temporary memory table generated during the system operation ,8.0 After the launch of the new TempTable engine , Support blob Field , It is better than memory ngine.

8. Lock

SELECT ... FOR SHARE and SELECT ... FOR UPDATE Added NOWAIT and SKIP LOCKED grammar , Reduce long and unnecessary blocking .

9. Instant add column

InnoDB Solved the long-term problems DBA Add fields to copy The whole table data problem . Now you can quickly add fields , Just modify the data dictionary , Instead of modifying the record itself in the table .

10. Parallel queries

InnoDB Currently supported in clustered index Parallel query on , Provide innodb_parallel_read_threads,https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_parallel_read_threads, Parameter control session Within the degree of parallelism .

11. Redo Optimize

Redo The writing of has always been InnoDB The bottleneck in the case of high concurrency ,8.0 Start :

  • User threads can be concurrent copy redo Log to log buffer in

  • User threads can put dirty block Add to the dirty block list

  • Independent write threads complete redo The persistence of

12. Json To strengthen

stay Json On ,8.0 More sex , See MySQL 8.0 Of document.

13. Partial update on lob

InnoDB Continue to optimize partial update on lob data, For those that modify only a few bytes lob Field , Can be big Reduce undo data, And improve efficiency .

14. Optimizers are object related

1) Support invisible index, Convenient for users and DBA debugging statement.

2)descending indexes, Improve the efficiency of descending scanning .

3)Common table expressions, Support with Grammar complete .

4)Window functions, Add a lot of window functions

5)Regular expression, Redesigned support for regular expressions .

版权声明
本文为[51 learning workshop]所创,转载请带上原文链接,感谢

Scroll to Top