This blog post talks about one of the important MySQL configuration, sql_generate_invisible_primary_key, available in MySQL 8.0.30. This will save you from performance bottlenecks due to tables without Primary Key.
If you are handling applications without Primary Keys and you cannot change your application to adjust the table definition to have Primary Keys, this MySQL option is your golden bullet. This blog about MySQL configuration option, sql_generate_invisible_primary_key, is surely for you.
Find tables without Primary Keys
First things first, do you have tables without Primary Key or Unique Key in your database? If not, congratulations, you don’t need to read further. You can find it using the following query:
SELECT tbls.table_schema AS database_name,
tbls.table_name, tbls.table_rows, tblcnstrnt.constraint_type
FROM information_schema.tables tbls
LEFT JOIN information_schema.table_constraints tblcnstrnt ON tbls.table_schema = tblcnstrnt.table_schema
AND tbls.table_name = tblcnstrnt.table_name
AND (tblcnstrnt.constraint_type = 'Primary Key'
OR tblcnstrnt.constraint_type = 'UNIQUE')
WHERE tblcnstrnt.constraint_type IS NULL
AND tbls.table_schema not in('mysql', 'information_schema','performance_schema','sys')
ORDER BY tbls.table_schema,
tbls.table_name;The Problem without Primary Keys
Tables without Primary Keys can spell trouble. They often lead to performance issues and operational difficulties. Duplicate records, slow queries, and complex data management become frequent challenges for database administrators.
For InnoDB tables, when no Primary Key is defined, the first unique not null key is used. If none is available, InnoDB will create a hidden Primary Key (6 bytes) which cannot be utilized by the optimizer for purposes of improving query execution.
The problem with such hidden keys is that you don’t have any control over them, and worse, this key is global to all tables without Primary Keys. That can create contention problems if you perform multiple simultaneous writes on these tables.
A large amount of tables that don’t have a Primary Keys can create performance issues as they will all share that global hidden PK index increment.
Is this something new? Nope, this is well known to the MySQL community since ages. You may refer to some of the blog posts:
http://www.percona.com/blog/2013/10/18/innodb-scalability-issues-tables-without-primary-keys/
http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/
http://bugs.mysql.com/bug.php?id=69223
http://www.fromdual.com/disadvantages-of-explicitly-not-using-innodb-primary-keys
Get Primary Keys in 1 minute: sql_generate_invisible_primary_key
In MySQL 8.0.30, a significant improvement arrived in the form of sql_generate_invisible_primary_key. It introduces a simple yet game-changing feature: generated invisible Primary Keys. This feature dynamically generates Primary Keys for tables that lack them. The 1 minute (or less) solution is to dynamically change the configuration as follows:
SET GLOBAL sql_generate_invisible_primary_key = ONGet Primary Keys without any application or table changes
One of the key advantages of this feature is its invisibility to your applications. Your existing applications will continue to function seamlessly without any modifications. It means you can enjoy all the benefits of Primary Keys without impacting your ongoing operations.
The Advantages of Generated Primary Keys
- Improved Query Performance: Queries on tables with Primary Keys are significantly faster. Now, you can enjoy similar performance for tables without Primary Keys.
- Usage of tools requiring Primary Keys, example: Percona Tools.
- Simplified Data Management: Managing data becomes easier with well-defined Primary Keys, as they streamline updates and deletions.
- Faster Data Replication: Replicated architecture can benefit from the generated Primary Keys.
Replication Challenges – Solved in MySQL 8.0.32
In MySQL 8.0.32, the generated Primary Key feature was further enhanced. You can now use REQUIRE_TABLE_PRIMARY_KEY_CHECK=GENERATE to ensure that the generated Primary Key is properly replicated to the replica.
TL;DR for Generated Invisible Primary Keys
- Enabling is as simple as SET GLOBAL sql_generate_invisible_primary_key=ON. If you have tables without PK, enabling this feature should help improving performance. (Always test the production changes)
- Configuration sql_generate_invisible_primary_key is not replicated. Hence this had specific questions for the replicated environments. From 8.0.32, GIPK has additional functionality of having “REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE” option in a “CHANGE REPLICATION SOURCE TO” statement. This automatically adds the GIPKs for the tables replicated without Primary Keys.
- GIPKs work with row-based replication of CREATE TABLE … SELECT. The binary log includes GIPK definitions, ensuring correct replication. Statement-based replication of CREATE TABLE … SELECT is not supported with sql_generate_invisible_primary_key = ON.
- When creating you can exclude generated invisible Primary Key columns and values using –skip-generated-invisible-primary-key option of mysqldump command.
- Similarly it is possible to exclude importing the dump having GIPKs using mysqlpump’s –skip-generated-invisible-primary-key option.
Conclusion
Databases without Primary Keys can be a headache, but MySQL has come to the rescue with its generated invisible Primary Keys. You can unlock faster query performance, improved data integrity, and streamlined data management. Best of all, your applications remain unaffected by these changes. So, if you’re looking to optimize your MySQL performance and reliability, it’s time to enable sql_generate_invisible_primary_key.