MySQL8 Features | Getaround Tech

0
51
What's a good team process?


MySQL 8 was released in 2018 and is the next release after 5.7.
Since 2018 and as of today there was 27 minor versions bringing the last version to 8.0.27. It’s important because MySQL did bring a lot of feature enhancements in these minor versions as we’ll see in the next part.

Common Table Expressions (CTEs)

A Common Table Expression (also known as WITH query) is a named temporary result set.
It exists only in the scope of a single statement and can be later referred within that statement.
You create a CTE using a WITH query, then reference it within a SELECT, INSERT, UPDATE, or DELETE statement.

image

CTEs make a query more readable, allow to better organize long queries and better reflects human logic (like functions does). It’s particularly useful when you need to reference a derived table multiple times in a single query. There is also a specific category of CTEs called recursive CTEs that are allowed to reference themselves. These CTEs can solve problems that cannot be addressed with other queries.

This feature is available as of MySQL 8.0 and some edge cases have been handled in 8.0.19 (recursive SELECT  with LIMIT  clause).
Documentation is here.

Window Functions

PostgreSQL’s documentation does an excellent job of introducing the concept of Window Functions:

window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

Untitled

Here is an example that shows how to compare each employee’s salary with the average salary in his or her department:

image

MySQL comes with the following WINDOW functions:

Untitled

WINDOW functions probably deserves an article on their own. You can find one here.
This feature is available as of MySQL 8.0.
Documentation is here.

Expressions as Default Values

MySQL now supports use of “expressions” as default values. Expressions are distinguished by the use of parenthesis. BLOBTEXTGEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

image

This feature is available starting MySQL 8.0.13. Before 8.0.13, the only expression supported was CURRENT_TIMESTAMP.
Documentation is here.

Indexing key parts

MySQL now supports indexing expression values referencing other keys rather than column values or column prefixes. Using parts of a function key allows you to index values that are not directly stored in the table.

image

This feature is available starting MySQL 8.0.13. Prior to 8.0.13, you could achieve the same result by using virtual columns and indexing them but this is clearly way more straightforward.
Documentation is here.

Descending Indexes

MySQL now supports descending indexes (DESC). Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient.

Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

image

This feature is available as of MySQL 8.0.
Documentation is here.

Invisible indexes

MySQL now supports invisible indexes. An invisible index is not used by the optimizer, but is otherwise maintained normally.

It’s basically a toggle for indexes.

image

Hidden index can be used to quickly test the impact of index deletion or index creation on query performance without index deletion and reconstruction. If the index is needed, it is good to set it visible again. This is undoubtedly very useful in large table testing, because it consumes performance for index deletion and addition of large table, and even affects the normal operation of the table.

This feature is available as of MySQL 8.0.
Documentation is here.

EXPLAIN ANALYZE Statement

This statement provides expanded information about the execution of SELECT statements in TREE format. This includes startup cost, total cost, number of rows returned by iterator, and the number of loops executed.

image

The cost is an arbitrary unit but it is consistent between queries and usually a good proxy to answer the question “is this query faster than this other query?”.

This feature is available in 8.0.18.
Documentation is here.

What else?

MySQL 8 comes with tons of other features like:

  • Better UTF8 support
  • New “role” system allowing to give/remove permissions to groups of people
  • User comments and user attributes
  • JSON enhancements ( ->> operator, JSON_PRETTY(), merge function, aggregation functions, JSON schema validation draft 4 …)
  • Better regexp support

And of course performance and stability improvements.

The full list of changes is here.



Source link

Leave a reply

Please enter your comment!
Please enter your name here