MySQL 8 is incorporated with numerous features which excites all enthusiastic MySQL Database Architects / DBAs / Database Engineers / DBA Managers, I am attracted to few of them which will make my day-to-day MySQL 8 DBA life even more interesting:
MySQL Data Dictionary
In the past MySQL data dictionary was stored in metadata files and nontransactional tables but from MySQL 8 we have transactional data dictionary to store the information about database objects. The major difference between MySQL system tables and data dictionary tables is that system tables contain auxiliary data such as time zone and help information, whereas data dictionary tables contain data required to execute SQL queries. The dictionary data is now protected by the same commit, rollback, and crash-recovery capabilities that protect user data stored in InnoDB tables. This make MySQL data dictionary based object management more interesting.
P.S – InnoDB storage engine continues to use its own storage engine-specific data dictionary.
MySQL 8 supports Roles
MySQL 8 supports roles, They are collections of privileges which are granted to and revoked from user accounts. A much waited feature IMHO, Especially when I am working for customers who are serious about building secured privileges / policies across MySQL users, Eureka I have answer for all those folks now 🙂 !!!
InnoDB AUTO_INCREMENT
MySQL 5.7 and earlier, the auto-increment counter is stored only in main memory, not on disk. To initialize an auto-increment counter after a server restart, InnoDB would execute the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. From MySQL 8 this behaviour has changed, The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts. On a server restart following a normal shutdown, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary system table. On a server restart during crash recovery, InnoDB initializes the in-memory auto-increment counter using the current maximum auto-increment value stored in the data dictionary system table and scans the redo log for auto-increment counter values written since the last checkpoint. This feature will increase performance of MySQL operations with several schema objects built with AUTO_INCREMENT counter.
Deadlock detection disabling
We can now dynamically set innodb_deadlock_detect in MySQL 8 which is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. After carefully evaluating the transaction behaviour of your application you can disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs. I will be personally very careful using this feature, Good but too powerful too !
INNODB_CACHED_INDEXES
The INFORMATION_SCHEMA table, INNODB_CACHED_INDEXES, reports the number of index pages cached in the InnoDB buffer pool for each index. This really helps to measure index efficiency more proactively and I am sure this will help me a lot going forward !
Invisible indexes
MySQL 8 now support invisible index, These indexes are not used by optimizer at all but maintained normally like any other indexes, This enables Database Architects / DBAs / Database Engineers / DBA Managers to benchmark MySQL performance on what if any index is removed ? I personally love this feature and will be using this most often in real-life as a MySQL 8 DBA to decide the fate of indexes, Great feature to have !
Much better descending indexes
Yes, We have descending indexes in MySQL 5.7 but they are scanned reverse order which indeed causes performance bottleneck. MySQL 8 solved this problem, Now descending indexes can be scanned in forward order, which is much more performance conscious and optimal . The 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. I was waiting for this feature for several years and this will make sort / search of both ASC/DESC based queries equally efficient, Must use feature in MySQL 8 to make your application more index efficient.
