MySQL 8.0. The Latest Features | Thorough Overview

Many developers have criticized MySQL for insufficient JSON functionality. However, everything is different now because JSON has undergone some major enhancements.

Let’s start with the new utility functions, JSON_STORAGE_SIZE( ) and JSON_STORAGE_FREE(). Another addition is JSON_PRETTY().

That’s not it, because ->> has also been added, which is the equivalent for JSON_UNQUOTE(JSON_EXTRACT()). Some new aggregation functions, such as JSON_OBJECTAGG() and JSON_ARRAYAGG() have also been integrated into MySQL 8.0.  Even better, now we have a JSON_TABLE() function for JSON documents. It accepts the JSON data and returns it as a relational table, with specifiable columns and casting.

Earlier versions of MySQL worked on storing the dictionary on disk. Now, it has taken a step further and stores a dictionary in InnoDB tables. This improvement’s main reason is to upgrade the performance of the database.

This is one of the most anticipated upgrades within MySQL 8.0. Now, the document store has become exactly what developers expected it to be.  It combines the best of two languages, SQL and NoSQL. This way, developers can take advantage of the top-notch benefits of both.

Well, it can handle all sorts of work, from CRUD commands to merging several tables into one – all is possible now.  It follows the ACID properties, so you can learn it effortlessly if you are familiar with the MySQL environment.

4. Window Functions and CTEs (Common Table Expressions)

There have been various Window functions added into MySQL 8.0.4. Here are some of them:

In addition to that, WITH queries, also known as CTEs (Common Table Expressions), have been added to MySQL 8. It is a volatile result set within a single statement. The CTE can be called again with that statement.

The latest MySQL 8.0 upgrade allows the Optimizer Cost Model to view cached data (actual amount located in memory). Thus, you can always check how much of it is left on the disk.

In addition to that, Optimizer hints are also available for the optimizer_switch variable. Although it was available in previous versions, the Oracle team has improved it significantly. Before, you had to call the variable after each subsequent query. Now, you can control optimizer hints per query.

MySQL has opened a new path for better functionality for developers. Those new to the DBMS can still catch up and learn new features for better database performance. In their turn, the experienced developers can do more with the improved options.

MySQL 8.0. The Latest Features | Thorough Overview

1. JSON Enhancements

Many developers have criticized MySQL for insufficient JSON functionality. However, everything is different now because JSON has undergone some major enhancements.
Let’s start with the new utility functions, JSON_STORAGE_SIZE( ) and JSON_STORAGE_FREE(). Another addition is JSON_PRETTY().
That’s not it, because ->> has also been added, which is the equivalent for JSON_UNQUOTE(JSON_EXTRACT()). Some new aggregation functions, such as JSON_OBJECTAGG() and JSON_ARRAYAGG() have also been integrated into MySQL 8.0.  Even better, now we have a JSON_TABLE() function for JSON documents. It accepts the JSON data and returns it as a relational table, with specifiable columns and casting.

2. Data Dictionary

Earlier versions of MySQL worked on storing the dictionary on disk. Now, it has taken a step further and stores a dictionary in InnoDB tables. This improvement’s main reason is to upgrade the performance of the database.

3. Document Store

This is one of the most anticipated upgrades within MySQL 8.0. Now, the document store has become exactly what developers expected it to be.  It combines the best of two languages, SQL and NoSQL. This way, developers can take advantage of the top-notch benefits of both.

How does the MySQL document store work?

Well, it can handle all sorts of work, from CRUD commands to merging several tables into one – all is possible now.  It follows the ACID properties, so you can learn it effortlessly if you are familiar with the MySQL environment.

4. Window Functions and CTEs (Common Table Expressions)

There have been various Window functions added into MySQL 8.0.4. Here are some of them:
CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER
In addition to that, WITH queries, also known as CTEs (Common Table Expressions), have been added to MySQL 8. It is a volatile result set within a single statement. The CTE can be called again with that statement.

5. Improved Optimizer Model and Hints

The latest MySQL 8.0 upgrade allows the Optimizer Cost Model to view cached data (actual amount located in memory). Thus, you can always check how much of it is left on the disk.
In addition to that, Optimizer hints are also available for the optimizer_switch variable. Although it was available in previous versions, the Oracle team has improved it significantly. Before, you had to call the variable after each subsequent query. Now, you can control optimizer hints per query.
MySQL has opened a new path for better functionality for developers. Those new to the DBMS can still catch up and learn new features for better database performance. In their turn, the experienced developers can do more with the improved options.

MySQL 8.0. The Latest Features. Thorough Overview. JSON Enhancements. MySQL document store. Data dictionary. Windows functions and CTE's.

Best New Features of MySQL 8.0

Firewall security with advanced protection

You can easily secure your infrastructure using stateful firewalls at no extra cost. Define inbound and outbound rules to deny unnecessary traffic. Once configured, you can assign as many cloud servers as you need.

Load balancers make it stress-free to scale requests by automatically distributing traffic through your infrastructure. It handles use cases such as TLS termination or creates an internet-facing entry point to route traffic to the Hetzner cloud network.

Combine AMD EPYC 2nd Gen and Intel® Xeon® Gold processors with high-speed NVMe SSDs to benefit from high-performance hardware. You can select between local storage and cloud storage (NVMe SSD RAID or Ceph). You can also take advantage of redundant 10 Gigabit network links.

Network communication feature allows servers to communicate over private networks and establish complex network topologies. Ideal in the sense of running Kubernetes clusters or database servers that can not be made publicly reachable.

You can manually back up the server using the snapshot function. You can use a snapshot to restore a server from a saved image, use a saved image to create a new cloud server, or transfer images to another project.

Volumes provide highly available and reliable SSD storage to cloud servers. Each storage capacity can be expanded up to 10TB at any time and connected to Hetzner cloud servers.

Get instant access to a range of features to help you manage your cloud infrastructure. It also provides REST-API and CLI tools for developers. Complete documentation and programming examples make it easy to get started and get the most out of your instance.


Traffic controlling with higher bandwidth

With the 20TB of storage, the project will have a lot of bandwidth no matter which Hetzner Cloud package you choose. However, if you wish, you can pay an additional fee of 1.19 euros per month per TB.

Cloud point is hosted in the data centers of the Hetzner Online company in Nuremberg and Falkenstein (Germany), Helsinki (Finland), Ashburn (Virginia, USA). All data centers are operating following strict European data protection regulations.

A backup is an automatically created copy of your server to keep your data safe. There can be produced more than seven copies at a time.

Allocate floating IPs as needed. For use with redundant servers or high-availability server clusters.

Choose from various operating systems, like Ubuntu, Debian, Fedora, etc.

Distributed Denial of Service Protection

Hetzner Online uses the latest hardware appliances and advanced perimeter security technology to secure your Hetzner cloud servers. It also provides unparalleled protection against large-scale Distributed Denial of services occurrences and is free of cost.

Cloud General Data Protection Regulation (GDPR) compliant. You can generate a Data Processing Agreement (DPA) online that complies with Article 28 of the General Data Protection Regulation (GDPR). You can also enter the correct European location for your DPA.

Firewall security with advanced protection
You can easily secure your infrastructure using stateful firewalls at no extra cost. Define inbound and outbound rules to deny unnecessary traffic. Once configured, you can assign as many cloud servers as you need.

Load balancer to distribute traffic
Load balancers make it stress-free to scale requests by automatically distributing traffic through your infrastructure. It handles use cases such as TLS termination or creates an internet-facing entry point to route traffic to the Hetzner cloud network.

Optimized performance
Combine AMD EPYC 2nd Gen and Intel® Xeon® Gold processors with high-speed NVMe SSDs to benefit from high-performance hardware. You can select between local storage and cloud storage (NVMe SSD RAID or Ceph). You can also take advantage of redundant 10 Gigabit network links.

Network communication
Network communication feature allows servers to communicate over private networks and establish complex network topologies. Ideal in the sense of running Kubernetes clusters or database servers that can not be made publicly reachable.

Snapshot functionality
You can manually back up the server using the snapshot function. You can use a snapshot to restore a server from a saved image, use a saved image to create a new cloud server, or transfer images to another project.

Increased storage сapacity
Volumes provide highly available and reliable SSD storage to cloud servers. Each storage capacity can be expanded up to 10TB at any time and connected to Hetzner cloud servers.

API and Documentation
Get instant access to a range of features to help you manage your cloud infrastructure. It also provides REST-API and CLI tools for developers. Complete documentation and programming examples make it easy to get started and get the most out of your instance.

Traffic controlling with higher bandwidth
With the 20TB of storage, the project will have a lot of bandwidth no matter which Hetzner Cloud package you choose. However, if you wish, you can pay an additional fee of 1.19 euros per month per TB.

Location
Cloud point is hosted in the data centers of the Hetzner Online company in Nuremberg and Falkenstein (Germany), Helsinki (Finland), Ashburn (Virginia, USA). All data centers are operating following strict European data protection regulations.

Automatic backups
A backup is an automatically created copy of your server to keep your data safe. There can be produced more than seven copies at a time.

Floating IPS
Allocate floating IPs as needed. For use with redundant servers or high-availability server clusters.

OS
Choose from various operating systems, like Ubuntu, Debian, Fedora, etc.

Distributed Denial of Service Protection
Hetzner Online uses the latest hardware appliances and advanced perimeter security technology to secure your Hetzner cloud servers. It also provides unparalleled protection against large-scale Distributed Denial of services occurrences and is free of cost.

Data security and safety
Cloud General Data Protection Regulation (GDPR) compliant. You can generate a Data Processing Agreement (DPA) online that complies with Article 28 of the General Data Protection Regulation (GDPR). You can also enter the correct European location for your DPA.

Hetzner Cloud Review. New Features 2022. Load Balancer. Snapshot Functionality. Optimized Performance. Automatic Backups

Hetzner Cloud Review | The New Features 2022

MySQL is the most popular Database Management System that has upgraded over time, every upgrade coming with many exciting features. The same refers to MySQL 8.0. 
MySQL 8.0 features set is something every developer should be aware of. We created an overall article about the new features of MySQL 8.0 for those, who’ve missed the update or would like to learn more details.

Firewall security with advanced protection
You can easily secure your infrastructure using stateful firewalls at no extra cost. Define inbound and outbound rules to deny unnecessary traffic. Once configured, you can assign as many cloud servers as you need.
Load balancer to distribute traffic
Load balancers make it stress-free to scale requests by automatically distributing traffic through your infrastructure. It handles use cases such as ...

Check some deployment facilitation tips and tech howto's. Learn how to integrate popular technologies and their key features. Learn something new with TryDirect

Dashboard

TryDirect blog

Best New Features of MySQL 8.0

Hetzner Cloud Review | The New Features 2022

Load balancer to distribute traffic