In this article, I want to discuss a couple of pretty new features in MySQL 8.0 — and an older one. Maybe these are minor features you are not aware of, and maybe not so relevant, to be honest. But it is worth providing a quick overview, showing how they work, and how they could be useful in some cases.
All refer to the invisibility of something:
- Invisible columns
- Generated invisible primary keys
- Invisible indexes
Let’s take a look.
Invisible columns
The invisible columns feature has been deployed since version 8.0.23. What is an invisible column? It’s basically a regular column of a table with its own name and data type. It is treated and updated as any other regular column, with the only difference being that it is invisible to the application. In other words, it can be accessed only in the case it is explicitly addressed in your SELECT; otherwise, it is like a non-existing column.
The definition looks strange, but everything should be more clear, providing a real use case for this feature.
Suppose you have SELECT * queries in your application code. As an experienced database developer, you should know that these kinds of queries should not exist in any production code. The typical problem is when you need to change the schema of the table, adding or removing a column or, even worse, adding a new column in the middle of others. The position of the fields fetched into your application variables could completely break the application or trigger unexpected misbehaviors. That’s the reason why you need to avoid using SELECT * in applications like the plague.
Here come the invisible columns. In such a situation, if you need to avoid changing your application code to match the new table schema, you can add the new column as an invisible one, and it won’t be returned to the client since it is not explicitly addressed by your query. So, no failures or strange behaviors for your application.
You need to use the INVISIBLE keyword in the column definition. When you need to turn a column to visible, you need to use the VISIBLE keyword instead. Let’s see an example.
Create a table for our articles and insert a few rows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, article TEXT, PRIMARY KEY(id)); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO articles(article) VALUES("This is first article"),("This is second article"),("This is third article"); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM articles; +----+---------------------+------------------------+ | id | ts | article | +----+---------------------+------------------------+ | 1 | 2023-07-28 13:15:03 | This is first article | | 2 | 2023-07-28 13:15:03 | This is second article | | 3 | 2023-07-28 13:15:03 | This is third article | +----+---------------------+------------------------+ |
At some point, we decide the new field title must be added to the table after the ts column. In order to avoid the invalidation of our application due to the SELECT * and the newly added column in the middle, we have to create the title column as INVISIBLE.
1 2 3 | mysql> ALTER TABLE articles ADD COLUMN title VARCHAR(200) INVISIBLE AFTER ts; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Let’s provide some values to the new column:
1 | mysql> UPDATE articles SET title='Title 1' WHERE id=1; UPDATE articles SET title='Title 2' WHERE id=2; UPDATE articles SET title='Title 3' WHERE id=3; |
See the table schema now:
1 2 3 4 5 6 7 | CREATE TABLE `articles` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `title` varchar(200) DEFAULT NULL /*!80023 INVISIBLE */, `article` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
You see, the column is correctly flagged with the INVISIBLE keyword.
And try now the SELECT * again:
1 2 3 4 5 6 7 8 | mysql> SELECT * FROM articles; +----+---------------------+------------------------+ | id | ts | article | +----+---------------------+------------------------+ | 1 | 2023-07-28 13:15:03 | This is first article | | 2 | 2023-07-28 13:15:03 | This is second article | | 3 | 2023-07-28 13:15:03 | This is third article | +----+---------------------+------------------------+ |
You see, the column is not returned. This permits the query not to fail after the schema was changed.
If you would like to see the titles, you have to address the field explicitly:
1 2 3 4 5 6 7 8 | mysql> SELECT id, ts, title, article FROM articles; +----+---------------------+---------+------------------------+ | id | ts | title | article | +----+---------------------+---------+------------------------+ | 1 | 2023-07-28 13:15:03 | Title 1 | This is first article | | 2 | 2023-07-28 13:15:03 | Title 2 | This is second article | | 3 | 2023-07-28 13:15:03 | Title 3 | This is third article | +----+---------------------+---------+------------------------+ |
You can turn the column to VISIBLE with the following DDL:
1 | mysql> ALTER TABLE articles MODIFY COLUMN title varchar(200) VISIBLE; |
Remember, the invisible columns are treated as any other regular one, so you can read and update them anytime. Metadata regarding invisibility is available on the information_schema, and the INVISIBLE/VISIBLE keyword is preserved in the binlog, so all changes are correctly replicated.
For further details, you can have a look at the documentation:
https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html
Generated Invisible Primary Keys
This feature has been deployed in MySQL 8.0.30; it’s more recent. The Generated Invisible Primary Key (GIPK) is a kind of special invisible column, and it applies only to InnoDB tables.
You know you can create InnoDB tables without an explicit Primary Key. This is not a best practice at all, and we strongly encourage you to always create an explicit PK in your tables. You probably also know InnoDB creates a hidden Primary Key if you don’t provide one, but the new feature provided by GIPK makes it possible for the Primary Key to become usable and finally visible. Instead, the implicitly created hidden PK, which is a long-time feature, can become neither usable nor visible.
Ultimately, the feature is useful to force inexperienced users to have InnoDB tables with explicit PK, even if invisible.
Let’s see how it works.
This feature is disabled by default, so MySQL will continue behaving as in the past. To enable GIPK, you have to set the following dynamic system variable (it has both global and session scope):
1 | mysql> SET [PERSIST] sql_generate_invisible_primary_key=ON; |
Let’s now create a table without the explicit PK:
1 2 | mysql> CREATE TABLE customer(name VARCHAR(50)); Query OK, 0 rows affected (0.03 sec) |
Check the schema:
1 2 3 4 5 6 7 8 | mysql> SHOW CREATE TABLE customerG *************************** 1. row *************************** Table: customer Create Table: CREATE TABLE `customer` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
The invisible Primary Key named my_row_id has been automatically created.
A few notes:
- The name of the GIPK is always my_row_id. You cannot have a column with the same name in the table
- The GIPK data type is always BIGINT UNSIGNED using AUTO_INCREMENT
Interesting things are that you can use in your queries the Primary Key and see it if explicitly addressed, as described for invisible columns.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | mysql> INSERT INTO customer VALUES('Tim'),('Rob'),('Bob'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT my_row_id, name FROM customer; +-----------+------+ | my_row_id | name | +-----------+------+ | 1 | Tim | | 2 | Rob | | 3 | Bob | +-----------+------+ 3 rows in set (0.00 sec) mysql> SELECT my_row_id, name FROM customer WHERE my_row_id=2; +-----------+------+ | my_row_id | name | +-----------+------+ | 2 | Rob | +-----------+------+ 1 row in set (0.00 sec) |
And obviously, if you issue SELECT *, the Primary Key is not returned:
1 2 3 4 5 6 | mysql> SELECT * FROM customer WHERE my_row_id=2; +------+ | name | +------+ | Rob | +------+ |
At some point, you can eventually decide to make it visible and change the name as well if you like:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> ALTER TABLE customer MODIFY `my_row_id` bigint unsigned not null auto_increment VISIBLE; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE customerG *************************** 1. row *************************** Table: customer Create Table: CREATE TABLE `customer` ( `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`my_row_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
For further details, you can have a look at the documentation:
https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html
Invisible indexes
To complete the overview of invisible things, let’s discuss Invisible indexes as well. This is the oldest invisibility feature, introduced in the very first release of MySQL 8.0.
The simple idea is that you can make an index invisible to the optimizer in order to test the performance of a query if that index did not exist. Anyway, when the index is invisible, it gets updated when you execute any DML statement against the table (INSERT, UPDATE, DELETE, REPLACE).
You can use the following statements to set an index invisible and visible again:
1 2 | ALTER TABLE mytable ALTER INDEX my_idx INVISIBLE; ALTER TABLE mytable ALTER INDEX my_idx VISIBLE; |
An invisible index makes it possible to test the execution plan of a query without considering it. The great advantage is that you don’t need to drop the index. Remember, the index drop is almost instantaneous, but rebuilding an index is not. Rebuilding an index could require a lot of time and overloading the server, depending on the size of the table. As an alternative, you can also use the IGNORE INDEX() index hint, but in this case, you could be forced to add index hints on many queries in your application code. Setting an index as invisible will permit you to start testing your queries in a very short time. And you can step back it to visible at any time easily, without losing any updates.
Notes:
- The PRIMARY Key cannot be invisible
- A UNIQUE index can be invisible, but uniqueness checks are executed as usual
- Information about index invisibility is available in the information_schema
- Index invisibility is correctly replicated
For further details, you can have a look at the documentation:
https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
Conclusions
From my perspective, you shouldn’t use invisible columns since, as a best practice, you shouldn’t deploy SELECT * queries in any application. Anyway, in some urgent cases, the feature may be useful to fix issues on the fly. But then remember to fix your code and turn the invisible column to visible. Definitively better.
More or less, it’s the same for GIPK. As long as you remember to provide an explicit Primary Key to your tables, you don’t need this feature. Anyway, it could be useful just to make it possible for a table that was created without the PK can have a proper one that can be used and become visible at ease.
What about invisible indexes? It’s a very simple feature but useful for running tests, particularly when multiple indexes could be used, and you’re not sure the optimizer is choosing the best possible execution plan.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
There is also the ability to add the primary key on a replica and have replication work even if the source does not have the primary key.
As described on https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html
use
REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATEas part of aCHANGE REPLICATION SOURCE TOstatement.