MySQL 8.0 New Features

MySQL 8.0 is released with lot of new features, we will discuss those in short, please visit official website for details of the features

Features can be divided in 3 types, in this post will discuss features added in MySQL 8.0, including latest 8.0.14 (22nd Dec 2018)

• Features added in 8.0
• Depreciated
• Completely removed

Features added in 8.0

Data Dictionary

Previously data dictionary was stored in metadata files and non-transactional tables, but here MySQL 8.0 incorporates a transactional data dictionary that stores information about database

Atomic DDL

An atomic DDL combines the data dictionary objects, storage engine operations and binary log writes associated with DDL operation in a single atomic transaction.

Security and Account Management

• The grant table is now InnoDB (transactional) table.
• A new caching_sha2_password authentication plugin is available and is default in MySQL 8.0. Like sha256_password plugin, caching_sha2_password implements SHA-256 password hashing, but uses caching to address latency issue at connect time.
• MySQL 8.0 supports roles i.e. collection of privileges
• MySQL can now maintain password history, enabling restrictions on reuse of passwords.

Resource Management

• MySQL now supports creation and management of resource groups and permits assigning threads running within the server to particular groups so that threads execute according to resource available to group

InnoDB environment

• The current maximum auto increment counter value is written to redo log, each time the value changes and saved to engines private system table on checkpoint.
• A server restarts no longer cancels effect of AUTO_INCREMENT = n table option
• New dynamic variable innodb_deadlock_detect may be used to disable deadlock operations
• INFORMATION_SCHEMA.INNODB_CACHED_INDEXES table reports number of index pages cached in InnoDB buffer pool for each index
• InnoDB temporary tables are now created in shared temporary tablespace ibtmp1.
• ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REORGANIZE PARTITION and REBUILD PARTITION ALTER TABLE options are supported by native partitioning in place APIs.
• InnoDB storage engine now uses mysql data dictionary instead of own storage engine specified data dictionary.
• The mysql system tables and data dictionary tables are now created in single InnoDB tablespace file named mysql.ibd in MySQL data directory

MySQL 8.0 changes to Undo Tablespace

• Undo logs re no longer created in system tablespace. As of 8.0.14 additional undo tablespace can be created in a chosen location at run time using “create undo tablespace” command.
• Default value for variables that affect buffer pool pre-flushing and flushing behavior were modified
• innodb_max_dirty_pages_pct_lwm = 10 – previously it was 0 (disable), setting value 10 enables pre-flushing where % of dirty pages exceeds 10%
• innodb_max_dirty_pages_pct = 90 –previously it was 75
• Renaming a general tablespace is supported by “alter tablespace ..rename’ command.
• The innodb_dedicated_server variable which is disabled by default, can be used to have InnoDB automatically configure following options
• innodb_buffer_pool_size
• innodb_log_file_size
• innodb_flush_method
• The new table under information schema INFORMATION_SCHEMA.INNODB_TABLESAPCE_BRIEF
• Tablespace files can be moved or restored to new location while server is offline using innodb_directories option.
• The innodb_log_buffer_size variable is now dynamic, which permits on the fly changes to its value
• The ADD DATAFILE clause in CREATE TABLESPACE command is optional it will automatically create datafile with unique name

Character Set Support

The default character set is now utf8mb4 instead of latin1, this new character set support lot of new Unicode’s.

JSON and NoSQL

• Today’s world is internet world and internet is document based medium. Regular transactional relation database lacks ability to match up with webs unstructured data and document central model, this lack of feature was major factor in rise of NoSQL databases such as MongoDB.
• Release of MySQL 8.0 introduces a NoSQL document store so that single database can be used for both relational and document databases
• In addition to introduction to NoSQL, MySQL 8.0 brings lot of improvements to JSON handling, few function are mentioned below
• JSON aggregate functions
•             JSON_ARRAYAGG()
•             JSON_OBJECTAGG()
• JSON pretty function
•             JSON_PRETTY()
• JSON storage function
•             JSON_STORAGE_SIZE()
•             JSON_STORAGE_FREE()
• JSON merge function
•             JSON_MERGE_PRESERVE()
• JSON table function
•            JSON_TABLE()

Data Type Support

MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all

Optimizer

MySQL 8.0 now support invisible indexes and descending indexes

MySQL 8.0 adds hinds for INDEX_MERGE and NO_INDEX_MERGE, this allows the user to control index merge behavior for an individual query without changing optimizer switch.

Common Table Expression

MySQL 8.0 supports common table expression both recursive and non recursive, these expressions enable use of named temporary result sets, implemented by permitting a WITH clause preceding SELECT statement and certain other statements.

Window Functions

MySQL 8.0 supports SQL window functions similar to grouped aggregate functions.

Window functions perform some calculation on set of rows e.g COUNT or SUM. There are two kinds of window functions
SQL aggregate functions used as window functions

COUNT, SUM, AVG, MIN, MAX, BIT_OR, BIT_AND, BIT XOR, STDDEV_OOP, STDEV_SAMP, VAR_OOP and VAR_SAMP

Specialized window functions

RANK DENSE_RANK, PERCENT_RANK, NTILE, ROW_NUMBER, FIRST_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD and LAG
Lateral Derived Table

A derived table now preceded by LATERAL keyword to specify that it is permitted to refer to columns of preceding tables in the same FROM clause.

Internal Temporary Tables

The TempTable storage engine replaces the MEMORY storage engine as default engine for in-memory internal temporary tables. TempTable storage engine provides efficient storage for VARCHAR and VARBINARY column

Error Logging

Error logging was rewritten to use the mysql component architecture traditional error logging is implemented using built-in components as logging using system log is implemented as as loadable component. In addition a loadable JSON log writer is available related to parameter log_error_services

Replication

Replication now supports binary logging of partial updates to JSON documents using a compact binary format, saving space in log over logging complete JSON documents

Connection Management

MySQL 8.0 server allows TCP/IP port to be configured specifically for administrative connections

Plugins

Plugins must be written in C++ not C

Please feel free to ask questions and give feedback