mysql 8.0 – gabi.dev

This is the announcement blogpost and this is my overview taken from medium:

The one where MySQL 8.0 lands on Google Cloud SQL

Ada Doglace and Lily Grace (lilygrams). Photo by Anthony Ferrara.

There are many things that makes me happy. Puppies (see picture), food, wine and databases… (not particularly in that order). And things that makes me even happier such as a well designed schema and proper usage of ORM (Object Relational Mapping).

MySQL was the database I used to love to hate. It grew on me and the fact that long strides were made to make it more consistent and more modern kept me away from using other open source databases on a daily basis for my projects. And with grand expectation and admiration I am proud of the Cloud SQL team for achieving this milestone:

Today we are making available MySQL 8.0 on Cloud SQL. It’s not just a new version inside our managed databases portfolio, but it also comes with all Cloud SQL capabilities such as Automatic Storage Increase, High Availability, Cross-region Replication and PITR (point in time recovery). And best yet: It’s not a beta, it’s a GA launch! See the announcement.

You can start using today from scratch or migrate an existing MySQL database to Cloud SQL; as a way to minimize downtime you can use the External Replication feature from your Google Cloud Console or via the gcloud command-line tool.

MySQL 8.0 has a huge list of new features, and you can do a range of new operations and querying. You can even do silly things like Fibonacci sequence using SQL (it is true!), traverse a Binary Tree or even do the old fizz-buzz:

WITH RECURSIVE fizz_buzz (sequence, modulo_3, modulo_5) AS (
  SELECT 1, CAST('' AS CHAR(4)), CAST('' AS CHAR(5))
  UNION ALL
  SELECT sequence + 1,
         IF(MOD(sequence + 1, 3) = 0, 'Fizz', ''),
         IF(MOD(sequence + 1, 5) = 0, 'Buzz', '')
  FROM fizz_buzz
  WHERE sequence < 100
)
SELECT
       IF(CONCAT(modulo_3, modulo_5) = '', sequence, CONCAT(modulo_3, modulo_5)) AS fizzbuzz
FROM fizz_buzz;

On a more serious note, and enterprise worthy, you can now avoid sub-queries (or other N+1 problems), have better access control, and use Window Functions. There are plenty of new stuff, and this is a small list of talks on MySQL 8.0 I gave since the preview versions in 2017. One of my favorites is the following:

phpDay about MySQL 8.0 Features

At Google we define anyone that uses a keyboard to work on a technical aspect of a product a Technical Practitioner. It is a wider definition, however more inclusive to the DBAs, DevOps and SysAdmins alike.

My particular goal has always been to help the day-to-day practitioner do things they wouldn’t imagine their database was capable of. This is why I have the Office Hours. This is why I do those talks and should do more blog posts.

Schedule time with me

If you want to learn more about MySQL 8.0 I ask you to read my website: gabi.dev, where I posted several things on MySQL.

Happy Launch day!

This post will be updated as soon more information comes along.

This developer version wasn’t released yet, when it does, use at your own risk.

Oracle released the development version of MySQL 8.0.0-dmr on September 12th of 2016. Since then, the team have been working on the 8.0.1 development milestone. You can find the partial change list here.

The objective here is try to explain how this will have any real world impact for you from 8.0.1. Please remember though, that any changes made to this version will not be final until the General Availability (date not currently set).

These topics are aimed at the Software Engineering side and not DBA and this is why Replication, for instance, is not covered here.

Changes

Charset and Collation

MySQL 8.0 was defined as to have utf8mb4 as the default CHARACTER SET and utf8mb4_general_ci as the default COLLATION. 8.0.1 will change the default COLLATION to utf8mb4_0900_ai_ci.

Let’s analyse the name utf8mb4_0900_ai_ci:

0900 refers to Unicode Collation Algorithm version.
ai refers to accent insensitive.
ci refers to case insensitive.

How does that impact you? It means that by default, new tables will have that collation and will be able to handle more characters than Basic Multilingual Plane (more emoji! 🤦🏼‍♀️), plus it will be accent and case insensitive. If you want case and accent sensitive you will need to use utf8mb4_0900_as_cs.

If you wish to know more about the reasons for utf8 now being utf8mb4 you should read this post on MySQL official blog: Sushi = Beer ?! An introduction of UTF8 support in MySQL 8.0

Language Specific Charsets

There are cases where language takes precedence over the default general collation. For this you will need to use, for example in the instance of German phone book order, utf8mb4_de_pb_0900_as_cs.

Optimization

Descending Index

This particularly is one of my most desired features. Finally being implemented on this version, the ALTER TABLE ADD INDEX ix_column (column DESC) won’t be parsed as ASC anymore.

Since InnoDB uses BTREE indexes, when running a query that uses it in the case of single columns it doesn’t matter if the index is ASC or DESC. The index is used for DESC anyway.

However when working with multi-column indexes this will matter and having a descending index will actually increase your performance. Example:

A generic table users:

Field	Type	Null	Key	Default	Extra
id	int(10) unsigned	NO	PRI		auto_increment
first_name	varchar(127)	NO
last_name	varchar(128)	NO
email	varchar(255)	NO
created_at	timestamp	NO		CURRENT_TIMESTAMP
updated_at	timestamp	NO		CURRENT_TIMESTAMP	on update CURRENT_TIMESTAMP

The following query will have better performance if run with DESC index on updated_at time column:

SELECT *
FROM `users`
ORDER BY updated_at DESC, first_name ASC
LIMIT 100;

For that we need the following index:

ALTER TABLE `users`
ADD INDEX `ix_updated_at_first_name`
(updated_at DESC, first_name ASC);

Before that, the query plan would do a full scan on the table and not use the index at all. After the index is created it searches on index, and not the table:

Before	After

The query above is just a simplistic example and doesn’t filter anywhere the index, this is why it causes to do a full index scan.

Parser

I personally never made use for \N to indicate NULL in any query itself, but I have used it in CSV or TSV files.
This behaviour won’t be supported anymore.
This change won’t impact file import or export through LOAD DATA INFILE or SELECT ... INTO OUTFILE.

Functionalities

In-place operations

In MySQL 5.7 the Generated Columns feature was added. However ALTER TABLE in tables containing one would be a COPY operation (which is slower since it has to copy all data again of the table). Now it can be INPLACE as long as the column(s) being modified is not in a generated column. What this actually means is: the metadata for the column will be changed in real time, without the need to internally create a new table and copy data.

JSON

Features

Two new functions are added to aggregate JSON values: JSON_ARRAYAGG() and JSON_OBJECTAGG().

JSON_ARRAYAGG() takes a column or expression as an argument and aggregates the result in a single JSON array.
With JSON_OBJECTAGG(id, col) you can use two columns which will be interpreted as key and value and returns a single JSON object.

Performance

Performance on JSON columns when used with ORDER BY was improved. Before MySQL would allocate 1K of memory to a sort key, making it fixed length. The extra padding was removed in this version.

Error Handling

The indexing of JSON fields is only possible through Generated Columns. If you tried to index a JSON field before, would get the following as an error:

JSON column '%s' cannot be used in key specification.

The error message has now been made clearer:

JSON column '%s' supports indexing only via generated columns on a specified JSON path.

Bugs

More than 100 bugs were fixed on this version. It ranges from InnoDB through Replication and even compilation bugs. The list is too big to be tacked on to this article, but the complete (and yet partial) change list is available here.