Search

New things in MySQL 8

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.

1 view

Call

T: +44 7825018241

Follow me

 

© 2019 by GoplarDB 

All Rights Reserved

Powered by: Goplar LTD

  • LinkedIn Social Icon
  • Twitter Clean