The world’s most popular open-source database got an improvement with the announcement of the improved MySQL 8.0 back in late 2016. Any database-driven web applications rely on MySQL to function properly and perform. Some popular applications include WordPress, Joomla, Drupal and our very own, Ottomatik.
Also, popular websites including YouTube, Twitter, Facebook, and Flickr are some of that use MySQL extensively.
In this post, we will look at all the new functionalities that came with MySQL 8.0. Going through all the iterations and the latest updates of MySQL would take more than one article. Today, we look at the general functionalities and improvements of MySQL 8.0 across the board.
What You Get As A Developer
MySQL 8.0 has many features that a lot of developers were eagerly waiting for as we’ll see below. The areas of improvement include GIS, regular expressions, SQL, and JSON. UTF8MB4 also features as MySQL 8.0’s default character set enabling developers to store Emojis.
MySQL 8.0 SQL Functionalities
Below are the improved SQL functionalities in MySQL 8.0.
Windowing
Windowing has been among the coveted feature in MySQL. This function boasts the similarity of the grouped aggregate functions. Windowing allows you to perform some calculation on sets of rows using functions COUNT or SUM.
However, the beauty of windowing is that unlike grouped aggregate which collapses the set of rows into a single row, the window function aggregates each row in the set.
There are two windowing functions – the SQL aggregate function that supports windowing and the specialized window function. Each variation has its own set of functions that a developer can deploy as we see below:
SQL aggregate functions – COUNT, SUM, AVG, MIN, MAX, BIT_OR, BIT_AND, BIT_XOR, STDDEV_POP (and the synonyms STD, STDDEV), STDDEV_SAMP, VAR_POP (and the synonym VARIANCE) and VAR_SAMP
Specialized window functions – RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD, and LAG.
This support for analytic functions has long been requested by MySQL users although they have a long history on SQL.
Common Table Expressions (CTEs)
Another frequently requested feature is the ability to get a temporary result set from a particular query that only lasts as long as a larger query is executing. Common table expressions allow developers to simplify and maintain complex queries by increasing readability. To reduce the complexity of a query, CTEs facilitate the deconstruction of complex queries into simple blocks that can be used multiple times.
CTEs are useful when you want to reference a derived table severally in one query or as a way of creating a view in the database. CTEs also allow you to perform the same calculation repeatedly across compound query components.
Simply put, CTEs make it possible to perform advanced tasks with less code. CTEs are also sometimes known as WITH queries.
NOWAIT And SKIP LOCKED
In SQL, chances are that you will come across a locking clause once in a while. MySQL 8.0 comes with commands to help you navigate an instance where a table or data set is locked. In the event that a user comes across a locked row as a result of an UPDATE or SELECT … FOR UPDATE, the normal occurrence would be that the user would have to wait for the release of the locked row.
With MySQL 8.0 a NOWAIT locking clause, if a row is locked, the query will automatically return and terminate with an error rather than wait for the row to be released which could take longer than you want.
Alternatively, if you do not require the results for all rows, you can use the SKIP LOCKED clause. SKIP LOCKED does not wait to acquire a row lock on the listed tables. Instead, it ignores the rows that are locked and returns the results for the rows that are “unlocked” or free.
Descending Indexes
Unlike with previous MySQL releases, MySQL 8.0 comes with support for descending indexes. With descending indexes, DBA’s no longer have to sort results and they enjoy faster query executions.
Before MySQL 8.0, the only way to create indexes was in ascending order. The optimizer for MySQL 5.7 would then do a backward scan of the ascending index to give a descending index. However, the backward index scan is far less efficient and executes slower than a forward index scan.
With the availability of descending indexes for InnoDB in MySQL 8.0, entries can now be stored in descending order for the optimizer to leverage when a descending order query is run. The result is significantly faster execution times.
Grouping Function
MySQL server has had support for GROUP BY extension ROLLUP for a long time. However, with the initial GROUP BY, distinguishing whether a NULL represents a super-aggregate value or a regular grouped value was impossible.
In MySQL 8, the GROUPING function enables DBAs to differentiate between NULLs produced by ROLLUP and those produced from grouped data. When you perform a ROLLUP on a column, if a NULL is generated, the GROUPING function will return the value 1. Otherwise, the function will return a value of 0.
Apart from differentiating the NULLs, you can also use the GROUPING function to pass multiple columns as arguments to one GROUPING function. The GROUPING functions together with a having condition can also be used to retrieve either super-aggregate or aggregate rows only and to present those in a nice way.
Optimizer Hints
The MySQL optimizer switch allows control of different optimizer modes but it has a disadvantage. The problem with the optimizer switch is that it requires an extra query execution step to change its value.
Also, an optimizer switch affects the entire query. So what happens when you want to change the behavior of a specific table or query block? Optimizer hints allow you to have more control over the optimizer’s behavior.
You can use optimizer hints multiple times in the same comment. Hints follow certain SQL keywords namely – SELECT, UPDATE, INSERT, REPLACE, or DELETE.
JSON Functionalities
Sorting and grouping JSON values are also improved with MySQL 8 and that is not the sole added functionality. Other JSON functionalities that ship with MySQL 8 include:
New Syntax for ranges – For instance, JSON path expression ranges has an extended syntax in MySQL 8, with the new syntax being a subset of the standard SQL syntax. To put it into perspective, SELECT JSON_EXTRACT (‘[1,2,3,4,5]’, ‘$[1 to 3]’) returns [2,3,4].
Table functions for JSON – table functions for JSON make working with JSON data easy thanks to the deployment of SQL apparatus. It is now easy to create a relational view of JSON data using the function JSON_TABLE().
By using the table function, you get an evaluation of JSON data mapped into relational rows and columns. Once a result is returned, you can use SQL (join, project, and aggregate) to query it as you would any standard relational table.
Aggregation – combining JSON documents in multiple rows into arrays or objects was an uphill task. With MySQL 8, users can leverage the functions JSON_ARRAYAGG() and JSON_OBJECTAGG() to generate arrays and objects respectively.
Merge – the merge function (JSON_MERGE_PATCH() for JSON assigns the same meaning to a query as it would in JavaScript. The function removes duplicates and gives precedence to the second document. A good example is JSON_MERGE(‘{“a”:1,”b”:2}’,’{“a”:3,”c”:4}’) whose result is {”a”:3,”b”:2,”c”:4}.
For better functionality and performance, MySQL 8 feature improved sorting for JSON thanks to the use of variable length sort keys. The improvement in performance depends on the use case but it is up to 18 times faster.
Last, MySQL 8 allows you to update JSON documents partially. You can do this using the functions JSON_REMOVE(), JSON_SET(), and JSON_REPLACE(). These functions tell the engine that there is no need to write the full document.
GIS Support
Another coveted function that ships with this version of MySQL are geography support. It has support for approximately 5000 spatial reference systems and can calculate distances between two points on the earth correctly. In other words, it understands coordinates – longitude and latitude.
When it comes to datatypes, MySQL 8.0 improves the manipulation of IPV6 and UUID and extends bit-wise operations on binary to reduce the loss of bits.
Dev Ops Features
MySQL 8 also delivers some nifty DevOps features that have many users will enjoy. For DevOps, it’s all about availability, performance, security, manageability, and observability. MySQL InnoDB Custer and MySQL Group Replication brings high availability.
Reliability
Reliability in version 8 has been improved because of three factors. The first is the storage of meta-data such as users, privileges, and data dictionary tables in InnoDB, a proven transactional storage engine.
MySQL 8.0 also eliminates the potential for inconsistency by using a single data dictionary. Before, there were two data dictionaries, one for the server layer and the other for InnoDB layer. The two could un-sync causing inconsistencies.
The last feature that has improved reliability is assured crash safe DDL at an atomic level. What this means is that statements are either fully executed or they don’t get executed at all. This reduces the chance s of data-drift as a result of masters and slaves (nodes) getting out of sync.
Observability
This has been improved by information and performance schemas speedup. In the new implementation of information schema, tables feature as simplified views of data dictionary tables stored in InnoDB. The new implementation is superior to the previous one with up to 100 times speed up.
Performance schema queries have also been sped up by the addition of more than 100 indexes on performance schema tables.
There is also a new table, variables_info that stores information about configuration variables. The information stored in the table includes the name of the variable, the minimum and maximum values, the origin of the current value, the identity of the user who made the change, and when the change was made.
To help keep tabs on your database’s performance, you also have access to client error reports. Available statistics are separated in 5 tables namely summary per count, summary per user, summary per host, global count, and summary per thread. Each error messages reports the total number of errors raised, how many errors the SQL exception handler handled, and the time stamps for first and last seen.
A user with the right privileges can SELECT from the client error report tables or TRUNCATE to reset the statistics.
To view query response times better, users also have statement latency histograms which provide performance schema histograms of statements latency and calculates histogram percentiles to indicate service quality.
In instances where one transaction is locking a row and another is trying to access the same row, there are data locking dependency graphs in MySQL 8.0. The dependency graphs let users know which data is locked, the user that owns the lock, and the user that is waiting for the lock to be released.
Manageability
Under manageability, index visibility is another nifty feature in MySQL 8.0 is the ability to toggle the visibility of indexes to either visible or invisible. When you toggle an index to be invisible, the optimizer ignores it when making a query execution plan. However, the index is still available in the background and making it visible again is easy.
The feature that allows toggling indexes from visible to invisible and vice versa allows DBA’s and Dev Ops to determine whether they can drop an index or not. By monitoring the performance of a query after turning an index to invisible, you can decide to remove the index if the query does not suffer performance wise.
This is one of the features that users have been hoping to see for a long time.
Other manageability functionalities that ships with MySQL 8.0 include:
- Flexible undo tablespace management which affords users total control over undo tablespaces.
- SET PERSIST for global variables which allow already configured GLOBAL and DYNAMIC server variables to survive a server restart. Additionally, the functionality also facilitates server management without the need to edit configuration files.
- Remote management of the server over an SQL connection with the SQL RESTART command.
- Rename tablespace and rename column with the functions ALTER TABLESPACE t1 RENAME TO t2 and ALTER TABLE … RENAME COLUMN old_name TO new_name respectively. For the rename column syntax, the new one is an improvement over the old syntax (ALTER TABLE <table_name> CHANGE …) where you had to re-specify all column attributes. Also, with the old syntax, not all column information would be available to the application trying to rename it.
Security
In the past, the default authentication plugin was mysql_native_password. MySQL 8.0 has a new plugin which boasts better security (SHA2 algorithm) and faster performance (caching). The new default authentication plugin is caching_sha2_password. As a result, MySQL server team recommends the use of TLS/SSL for all network communications.
There is also support for the popularly requested OpenSSL in the community edition of MySQL 8.0. The new version also restricts the reuse of passwords with a password rotation policy. Additionally, brute force attacks on consecutive failed login attempts will be slowed by a delay in the authentication.
Performance
MySQL 8.0 improves performance for read/write, IO, and high contention (hot spot) workloads. Now, you can scale Read/Write workloads without affecting performance thanks to improved hardware deployment. In the new version, InnoDB writes to the REDO log without experiencing locks in user threads. Instead, flushing and writing on the REDO log are now dedicated to threads in the background, making the REDO process an event-driven one.
With the phasing out of the global lock fil_system_mutex, the new version of MySQL facilitates optimal usage of every storage device.
Another performance improving feature with the new version is the versions ability to deal better with high contention loads. When there is a high contention to access a certain row, secondary transactions are held in a queue. The new version of MySQL improves on the transactions per second, the mean latency, and the 95th percentile latency.
For the end user, this results in efficient hardware use thanks to the reduced need for spare capacity and the systems higher average loads.
Finalizing the performance features is the ability to map users/system threads and CPUs. By splitting the workloads across different CPUs, DBAs can enjoy better performance and system efficiency.
Summary
There are even more features and performance improvements of MySQL 8.0 than we have covered in this post. The bottom line is that for you as a DBA or Dev Op, you get more control over the whole system with MYSQL 8.0.
You can enjoy and deploy these features by migrating your server to the new version. Before doing so, make sure to use the Upgrade checker to evaluate the compatibility of your existing 5.7 server with the new 8.0.
