Significance of using Invisible Primary key (GIPK) with MySQL 8.0

Introduction

Looking to optimize your database’s performance and efficiency? Generated Invisible Primary Keys (GIPK) might just be the solution you’re looking for. In this article, we’ll explore how GIPK can simplify the process of adding primary keys to your database, enhancing your data integrity and improving your database’s overall functionality. So, keep reading to learn more and take the first step towards a better database today

Problem Statement

• While deploying MySQL, usage of Primary Key becomes inevitable.
• Primary key is an integral part of any relational database.
• In a table with a huge set of records, if a Primary key is not present, performing bulk writes will create a mess in the system. The result might be a huge replication delay.

Why GIPK?

• Adopting a primary key straight away, might not be a preferable option.
• Invoking a new primary key might require an application or code level review and testing, etc.
• While performing a change in the database system, the fear towards schema compatibility with respect to application, might be a blocker in creating new primary keys.
• There can be more such reasons leading to the avoidance of a primary key, GIPK will help us overcome these hurdles, with only a little effort.

Generated Invisible Primary Keys (GIPK)

It eliminates the need to maintain a separate column for the primary key in a table. While using GIPK, an invisible column with a primary key will be automatically created, if that particular table was created without a primary key.

What are Generated Invisible Primary Keys ?

It is an invisible primary key for a table that is created without a primary key column.
This was introduced in MySQL version 8.0.30.

How Generated Invisible Primary Keys Works ?

• Similar to invisible columns, Generated Invisible Primary Key (GIPK) creates an invisible primary key with the default column name ‘my_row_id’.
• ‘sql_generate_invisible_primary_key’ is a dynamic global variable, while set as ‘1’ it will automatically add a generated invisible primary key column to any table created.
• The variable ‘sql_generate_invisible_primary_key’ will have a default value of ‘0’.

Advantages:

• Improved performance: Database can skip the overhead of updating the primary key during an insert or update.
• Data Integrity: The invisible primary key ensures data integrity, even though it is not visible in the table structure.
• Ensures no two records have the same identifier in cluster environments. Primary key is mandatory in clustered environments.

Limitations:

• GIPK is available in newer versions of MySQL only, i.e 8.0.30 onwards, tools and applications using lower versions of MySQL do not support invisible primary keys.
• Only InnoDB tables support GIPK.
• Understanding the relationships between tables and data can be complex if the primary key is not visible.

Working Sample:

Set ‘sql_generate_invisible_primary_key’ server variable as 1.

1. SET sql_generate_invisible_primary_key= 1;

Check if Generated Invisible Primary Keys is enabled.

1. SELECT @@sql_generate_invisible_primary_key;

Note:
‘1’ defines enabled.
‘0’ defines disabled.

Creating a table without primary key constraint.

1. CREATE TABLE test1 ( name varchar(20), age int ) ;

View CREATE TABLE to check GIPK column.

1. SHOW CREATE TABLE test1 \G ;

Automatically generated primary key column is created as ‘my_row_id’.

Inserting record into table.

1. INSERT INTO test1 ( name, age) VALUES (‘abc’,10),(‘abd’,22) ;
2. SELECT * FROM test1 ;

Now the record is inserted with GIPK.
By default the primary key is invisible.
To view the inserted primary key, an alter table command is used.

1. ALTER TABLE test1 ALTER COLUMN my_row_id SET VISIBLE ;

Summary: 
GIPK is an invisible primary key that is automatically created for tables without a primary key column. It improves database performance, ensures data integrity, and prevents replication delays. However, it is only available in newer versions of MySQL and is limited to InnoDB tables.

At Mafiree Database Support Services, we provide expert assistance in managing your database, ensuring optimal performance and maximum uptime. If you need help with your database management needs, don’t hesitate to contact us at sales@mafiree.com.