In an optimization strategy, to be able to obtain an ideal optimization of MySQL, it is essential to have an understanding of the operation in the backend, to be able to make the right adjustments.

It's like when you bring your vehicle to the garage to check an abnormal sound. A competent mechanic will be able to give you good recommendations and do the right thing to correct the situation because he knows how the engine works, and it is the same with MySQL and optimization.

MySQL, like any other database, needs somewhere to store its schema names, table definitions, and other metadata; these are most commonly stored in a combination of different locations, including .FRL, .PAR, .OPT, .TRN, and .TRG files. This has gradually become a bottleneck in various contexts over time.

A visual example of files external to MySQL tables can be seen in the following...

In MySQL 8, there is one new feature that is well worth a special mention. Right on the MySQL server, you can now create roles, specify their privileges, and assign them to users. So, from now on, it will be an easy task for you because you will not need to remember which permissions a team X programmer needs, and whether a team QA must have Z privileges, and so on.

Let's take a concrete example:

To create a role, just run following command:

CREATE ROLE 'devops_developer', 'app_read', 'app_write';

Now give privileges to the role:

GRANT SELECT ON app_db.* TO 'app_read';

And, finally, share this role with a user:

GRANT 'app_read' TO 'read_username1'@'localhost', 'read_username2'@'localhost';

The other options available are as follows:

• Mandatory...

A new feature that Oracle has added to MySQL 8.0 is the creation and management of resource groups that can assign threads to particular groups so that these threads run according to the resources available to the group.

Group attributes allow you to control resources to vary resource consumption by group threads as needed. DBAs can adapt them to different workloads.

Currently, the use of the CPU is a manageable resource—yes, you read it right, manageable; this use is represented by the concept of virtual CPU or CPU cores. At start-up, an assessment is carried out by the MySQL server as to the number of available virtual processors. DBAs, depending on their privilege, can then associate them with resource groups and assign threads.

This means that you can assign a group 2 virtual CPU to application...

In MySQL 8.0, Oracle has improved or added features; let's look at these.

This step in MySQL is something many DBAs or developers are not aware of, because it is not something that we check everyday but it is important; we're talking about the maximum value of the auto-increment counter, which is registered in the redo log and changes each time during each checkpoint.

In addition to being saved in a series of private tables and visible only by MySQL, the auto-increment counter has been improved so it is more persistent during a restart of MySQL. This means that during a reboot, the new value will persist and not change as in previous releases.

Another improvement is the dynamic configuration of Innodb_deadlock_detect, which can be used to disable or enable this variable, which normally if it is active can affect the performance of MySQL in the case...

Oracle has been forced over time to adopt the new NoSQL trend because of the competition and also the fact that the concept of storage type document as JSON came to be much more practical at the management level; for some companies that have architectures such as big data, this became more of a constraint at the application level.

For these reasons, Oracle introduced for the first time in MySQL 5.7.8, the JSON data type, which is a type of specialized binary column, similar to a blob, but with additional features to optimize storage and recover the values of a JSON type column.

In the new version of MySQL 8.0, Oracle has added great features such as automatic validation, JSON document normalization, and automatic loading of values. There is also a restriction that must be taken into consideration, such as, for example, JSON columns that cannot...

You read that correctly—indexes can now be made invisible in MySQL 8.0!

From a performance or optimization point of view, this new MySQL 8.0 option is appreciated because you can essentially disable an index before deleting it. When you create an invisible index, it is always maintained normally, but the optimizer will not see it.

In other words, if you're not sure whether you need an index, you can mark it as invisible and the MySQL optimizer will not use it. After monitoring your server and monitoring performance, you can decide to reactivate it if you think that will result in improved performance.

As you probably already know, when the query optimizer can find an action plan to exploit them to their fullest, indexes can be of great assistance. They reduce execution time and improve the performance of your queries. However...

Like all new MySQL versions, the following features are considered obsolete in MySQL 8.0, and may be removed in a future new release. When alternatives are displayed, applications must be updated to use them.

Since MySQL 8.0.4, the validate_password plugin has been re-enabled for use in the server component framework, and although it is still available, there are plans for it to be removed from future versions of MySQL.

The ALTER TABLESPACE and DROP TABLESPACE ENGINE clause will be removed from future releases.

The function JSON_MERGE () is removed from MySQL 8.0.3. You should use JSON_MERGE_PRESERVE () instead.

It is important to note that the following items are obsolete and have been removed in MySQL 8.0. When messages are displayed for alternatives, applications must be updated to use them and the following is only a short list of all removed items. I invite you to visit MySQL website for more details:

• The information_schema_stats configuration has been replaced by information_schema_stats_expiry.
• InnoDB system views have been replaced:
  

• The query cache has been removed, including FLUSH QUERY CACHE and RESET QUERY CACHE.
• Two MySQL storage engines currently provide native partition support: InnoDB and NDB. Of these, only InnoDB is supported by MySQL 8.0. Any attempt to create partitioned tables in MySQL 8.0 using another storage engine will fail.
• In MySQL 8.0, the data_locks table and sys schema views contain separate schema name...

In this chapter, we have covered all the new exciting features of MySQL 8.0 and their functionalities, and reviewed deprecated and removed features. We have seen the main reasons why Oracle has gone so far with its development of the global data dictionary in MySQL 8.0.

We covered MySQL support roles and history; we covered one new feature on the MySQL server, in which you can now create roles, specify their privileges, and assign them to users. We learned that a new feature that Oracle has added to MySQL 8.0 is the creation and management of resource groups; we can assign threads to particular groups so that these threads run according to the resources available to the group. We also looked at enhancements in InnoDB and JSON, and we learned about invisible indexes from MySQL's optimizer. Lastly, we looked at features that have been deprecated and removed from MySQL...