Shulou(Shulou.com)05/31 Report--

Editor to share with you the new features of MySQL 8 Invisible Indexes example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Background

Index is a double-edged sword, which will slow down the operation of DML while improving the query speed. After all, there is a certain cost to maintain the index. So, for the index, add what should be added and delete the useless. The former is addition and the latter is subtraction. But in practical work, people seem to be more keen on the former and seldom on 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 confirm the useless index

Before invisible indexes appear, you can use sys.schema_unused_indexes to determine useless indexes. In MySQL 5.6, queries can be made through the base table of the view, even if there is no sys library.

Mysql > show create table sys.schema_unused_indexes\ Graph * 1. Row * * View: schema_unused_indexes Create View: CREATE ALGORITHM=MERGE DEFINER= `mysql.sys` @ `localhost` SQL SECURITY INVOKER VIEW `sys.`sch ema_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 (`t`schema`.`table _ io_waits_summary_by_index_ usage`t`join `schema`.`STATISTICS``s`on (`t`.`OBJECT _ SCHEMA` = convert (`s`.`TABLE _ SCHEMA` using utf8mb4)) and (`t`.`OBJECT _ NAME`) and (convert (`t`.`INDEX _ NAME` using utf8) = `s`.INDEX _ NAME`)) where (`t`.INDEX _ NAME`is not null) and (`t`.COUNTSTAR`) 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: utf8mb4collation_connection: utf8mb4_0900_ai_ci1 row in set, 1 warning (0.00 sec)

But this approach also has its shortcomings.

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

two。 What if the query performance suddenly deteriorates if the index is deleted based on the above query?

The emergence of invisible index can effectively make up for the above shortcomings. Setting index to invisible causes the optimizer to automatically ignore the index when choosing 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\ gateway * 1. Row * * Table: t1Create 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_ci1 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 | 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 of 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 not visible

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)

Be careful

The primary key index cannot be set to invisible.

The above is all the content of the article "sample Analysis of Invisible Indexes with New Features of MySQL 8". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.