MySQL 8 new features: Invisible Indexes

background

Indexes are a double-edged sword. While they increase query speed, they also slow down DML operations. After all, index maintenance requires a certain cost. Therefore, for the index, add what should be added and delete the useless ones. The former is addition and the latter is subtraction. But in actual work, everyone seems to be more keen on the former and rarely does the latter. The reason lies in the latter, which is difficult. The difficulty is not the operation itself, but how to confirm that an index is useless.

How to identify useless indexes

Before invisible indexes appeared, you could use sys.schema_unused_indexes to identify unused indexes. In MySQL 5.6, even without the sys library, you can query through the base table of the view.

mysql> show create table sys.schema_unused_indexes\G
*************************** 1. row ***************************
        View: schema_unused_indexes
    Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_unused_indexes` (
`object_schema`,`object_name`,`index_name`) AS select `t`.`OBJECT_SCHEMA` AS `object_schema`,`t`.`OBJECT_NAME` AS `object_name`,`t`.`INDEX_NAME` AS `index_name` from (`performance_schema`.`table_io_waits_summary_by_index_usage` `t` join `information_schema`.`STATISTICS` `s` on(((`t`.`OBJECT_SCHEMA` = convert(`s`.`TABLE_SCHEMA` using utf8mb4)) and (`t`.`OBJECT_NAME` = convert(`s`.`TABLE_NAME` using utf8mb4)) and (convert(`t`.`INDEX_NAME` using utf8) = `s`.`INDEX_NAME`)))) where ((`t`.`INDEX_NAME` is not null) and (`t`.`COUNT_STAR` = 0) and (`t`.`OBJECT_SCHEMA` <> 'mysql') and (`t`.`INDEX_NAME` <> 'PRIMARY') and (`s`.`NON_UNIQUE` = 1) and (`s`.`SEQ_IN_INDEX` = 1)) order by `t`.`OBJECT_SCHEMA`,`t`.`OBJECT_NAME`character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.00 sec)

But this approach also has shortcomings.

1. If the instance is restarted, the data in performance_schema will be cleared.

2. What if the index is deleted based on the above query and the query performance suddenly deteriorates?

The emergence of invisible indexes can effectively make up for the above shortcomings. Setting the index to invisible will cause the optimizer to automatically ignore the index when selecting an execution plan, even if FORCE INDEX is used.

Of course, this is determined by the use_invisible_indexes option in the optimizer_switch variable, which defaults to off. If you want to see the difference in the execution plan of a query before and after index adjustment, you can adjust the value of use_invisible_indexes at the session level, such as,

mysql> show create table slowtech.t1\G
*************************** 1. row ***************************
   Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `name` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_name` (`name`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from slowtech.t1 where name='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Common operations on invisible indexes

create table t1(id int primary key,name varchar(10),index idx_name (name) invisible);
alter table t1 alter index idx_name visible;
alter table t1 alter index idx_name invisible;

How to see which indexes are invisible

mysql> select table_schema,table_name,index_name,column_name,is_visible from information_schema.statistics where is_visible='no';
+--------------+------------+------------+-------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+--------------+------------+------------+-------------+------------+
| slowtech | t1 | idx_name | name | NO |
+--------------+------------+------------+-------------+------------+
1 row in set (0.00 sec)

Notice

1. The primary key index cannot be set to invisible.

Summarize

The above is the new feature of MySQL 8, Invisible Indexes, which I introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time!

You may also be interested in:

MySQL 8.0 new features: support for atomic DDL statements
Detailed explanation of the underlying implementation of descending index, a new feature of MySQL 8
MySQL 8.0 New Features: Hash Join
A brief discussion on the pitfalls and solutions of the new features of MySQL 8.0 (summary)
MySQL 8 new features: Descending index details
MySQL 8 new features: how to modify persistent global variables
MySQL 8 new features: detailed explanation of persistence of auto-increment primary key
Analysis of the new features of MySQL 8.0 - transactional data dictionary and atomic DDL

<<: A brief introduction to Linux performance monitoring commands free

>>: Detailed explanation of Linux file permissions and group modification commands