Here are new features added into the newer version of MySQL from its predecessor
Data dictionary
Now there is a data dictionary which stores the db object details.
Atomic DLL
Now there is enhancement of grouping updates, writes and binary log writes into a single atomic transaction.
Security and account management
There are many changes in security and accounts management. Here are short description about them
MySQL system tables storage engine is now innodb instead of myisam previously.
A new password protection hashing plugin is there as default called caching_sha2_password which implements SHA-256 password hashing.
MySQL now supports roles based access which is a group of privileges.
Password changes history is now maintained for checking previous password is not used again.
Now MySQL can be used with Federal Information Processing Standards support.
Access control grants can be given to named pipes for successful communication on windows OS.
Resource Management
Now we can group resources and assigned threads to those resources in a server and can manage vCPUs and multi core CPU.
InnoDB enhancement
There are many innodb enhancements which are as following
auto_increment when updated it will be persisted even if server get restarted.
An auto increment value after roll back transaction will not be assigned again.
if auto increment value is set to some higher value in update. Newly inserts will get values start from new value of update. Now innodb writes corruption flag both in memory and in redo log.
Innodb Memcached can now do multiple get requests.
Two new variables for deadlock are innodb_deadlock_detect and innodb_locak_wait_timeout to avoid dead locks and automate them.
information_schema.innodb_cached_indexes for index pages cached.
Innodb encryption of table space even its logs
Support added for nowait and skip locked for select for share and update operations.
ADD,DROP,COALESCE,REORGANIZE and REBUILD PARTITIONS are now supported for ALTER TABLE.
Innodb now uses the MySQL data dictionary.
System table and data dictionaries are now created in single tablespace.
undo table space operations are now added in new version of mysql
Modified behavior for flushing and reflushing of buffer pool.
Now we can do muti rows inserts in parallel in innodb tables.
Now we can alter tablespace.
zlib library version is upgraded.
Serialized Dictionary Information is added for all innodb tables.
Innodb now supports atomic DDL jobs.
Tablespaces can be copied to new location when server is not running.
redo logging is optimized for threads to handle dirty pages. A dedicated thread is added for writing buffer and innodb_log_wait_for_flush_spin_hwm, innodb_log_spin_cpu_abs_lwm, innodb_log_spin_cpu_pct_hwm varaibles added for configuring the threads.
Innodb log buffer size variable can now be set at run time.
Undo logging from large to small object data is now supported.
Now we can store large binary blob objects in temptable.
Innodb general tablespaces now support encryption feature.
Now temporary tables can be created in session and they will be stored in session temporary tablesspaces.
Now the log file size is controllable by the variable innodb_dedicated_server.
Now ADD DATAFILE of CREATE TABLESPACE is optional.
When the temptable exceeds memory limit it will be stored to disk.
Character set support
Now the default character set is utf8mb4 instead of latin1.
JSON Enhancements
There are several JSON updates in MySQL 8.x
Added inline path operator ->>
Two new json aggregate functions JSON_ARRAYAGG() and JSON_OBJECTAGG()
JSON_PRETTY() function is added and sort by json value performance is improved.
Added support for partial in place update of json fields data.
Utility functions added like JSON_STORAGE_SIZE(), JSON_STORAGE_FREE(). JSON_STORAGE_SIZE()
Added ranges in xpath like $[1 to 5]
Json merge functions optimized and work like union.
implemented last duplicate key win with java script parser.
New JSON_TABLE() function
Data type support
Now following datatypes default value is supported. BLOB,TEXT, GEOMETRY, JSON
Optimizer
There are also query optimizer enhancements.
Invisible indexes are no more used .
DESC indexes are now supported
Functional indexes are now supported
Constants in where condition are no more used in optimization for making join query simplify like 0=1
Common table expressions
Now supports common table expression recursive and none recursive both.
Windows functions
MySQL now supports windows function like RANK(), LAG(), NTILE()
Lateral derived tables
Now LATERAL keyword permits derived table column to inherit same properties as parent table columns.
Regular expressions support
Henry Spencer regular expression library is now supported.
Internal temporary tables
TempTable storage engine replaces the memory table engine.
Logging
Error logging is reimplemented
Backup lock
Backup logging is enhanced to avoid inconsistent snapshots.
Replication
For json replication logging is done in compressed binary format to save space.
Connection management
TCP/IP port to be configured for administrative purpose.
Plugins
Before both C and C++ can be used for plugin but now one only C++ plugins are supported.
Comments