MySQL 8.0: 9 new features to look into
MySQL is arguably the most popular Open Source DataBase Management System; release 8.0 DMR brings many improvements, some already anticipated by popular forks like MariaDB and Percona, and some not. Let’s take a look at what the new features are.
As of the time of writing this article MySQL 8 is not yet final! The release of MySQL 8 is in DMR status (Development Milestone Release). That means that it is not safe to use it in production until the final version is released. So, beware! Also, accounting the complexity of some changes, MySQL 8 won’t probably be backward compatible with previous versions.
MySQL 8 New features
- Transactional Data Dictionary: will now be kept in InnoDB tables instead of disks. Storing dictionary on disk was a good choice back when MySQL was born, but the same can’t be told today. This change will improve performance and increase the resiliency of the database.
- Persistent runtime configuration: SET PERSIST allows runtime configuration to be kept and survive a restart. In previous versions changes made using SET GLOBAL would be discarded upon restart.
- AUTO_INCREMENT counter persistence over restart: auto-increment counters values will now be stored in the redo log allowing retrieval upon restart.
- Optimizer hints: this is an alternative (better solution) to the optimizer_switch variable. In previous versions of MySQL, to control the optimizer behaviour, optimizer_switch had to be called before each query (when switching) since it would affect every subsequent query. With hints the optimizer behaviour can be controlled on a per-query basis.
- SQL Roles: roles are named collections of privileges (INSERT, SELECT, etc.) that allows easier permissions management. Roles can be created, dropped and granted to users.
- Invisible indexes: an invisible index is not used by the optimizer and looks invisible. Invisible indexes, are however maintained in the background so that they can be switched back on at any time. If you think an index isn’t useful you can try to make it invisible, monitor performance changes and decide whether it is or not without entirely dropping it.
- Character sets and collations: with this release, the new default character set will be set to utf8mb4 and the default collation will be utf8mb4_800_ci_ai.
- Bit-wise operation on BINARY types: up until now bitwise operation could not be used on BINARY types and would also produce BIGINT(64) results. This allows operations performed on IPV6 and UUID stored in binary types (or more generically, binary types) to return VARBINARY.
- UUID functions: following the previous feature, UUIDs can now be stored in VARBINARY(16) format instead of CHAR(36); this has a potential huge impact on required space to store them. Three new functions to manipulate UUIDs have also been introduced: BIN_TO_UUID(), UUID_TO_BIN(), IS_UUID().