MySQL 8 new features Invisible Indexes

Background

The index is a double-edged sword that slows down the operation of the DML while raising the query speed. After all, the maintenance of indexes requires a certain amount of cost. Therefore, for the index, to add the addition, delete useless. The former is addition, the latter is subtraction. But in practice, people seem to be more enthusiastic about the former than the latter. The reason lies in the latter, difficult. The difficulty is not the operation itself, but how to confirm that an index is useless.

How to confirm a useless index

Before an invisible index appears, you can use sys.schema_unused_indexes to determine the useless index. In MySQL 5.6, even without the SYS library, queries can be made from the base table of the view.

Mysql>ShowCreate TableSys.schema_unused_indexes\g*************************** 1. Row***************************                View: Schema_unused_indexesCreate View:CREATEAlgorithm=MERGE Definer=' Mysql.sys ' @ ' localhost ' SQL SECURITY INVOKERVIEW' 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:utf8mb4collation_connection:utf8mb4_0900_ai_ci1Rowinch Set,1Warning (0.00Sec

But there are also deficiencies in this approach, 1. If the instance restarts, the data in the Performance_schema is zeroed out. 2. If the index is deleted based on the query above, how does the query performance suddenly become worse? The presence of invisible indexes can effectively compensate for these deficiencies. Setting Index to invisible causes the optimizer to automatically ignore the index when it chooses the 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 is off by default. If you want to see the difference between a query's execution plan before and after an index adjustment, you can adjust the value of Use_invisible_indexes at the session level, for example,

Mysql>ShowCreate Tableslowtech.t1\g*************************** 1. Row***************************      Table: T1Create Table:CREATE TABLE' T1 ' (' ID ')int( One) not NULL, ' name 'varchar(Ten)DEFAULT NULL,  PRIMARY KEY(' id '),KEY' Idx_name ' (' name ')/*!80000 INVISIBLE*/) ENGINE=InnoDBDEFAULTCHARSET=UTF8MB4 COLLATE=Utf8mb4_0900_ai_ci1Rowinch Set(0.00sec) MySQL>ExplainSelect *  fromSlowtech.t1whereName='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 |Usingwhere |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---- ---------+1Rowinch Set,1Warning (0.00sec) MySQL> SetSession Optimizer_switch="Use_invisible_indexes= on"; Query OK,0Rows Affected (0.00sec) MySQL>ExplainSelect *  fromSlowtech.t1whereName='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|  +      |Const|    1 |  100.00 |UsingIndex |+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+--------- -+-------------+1Rowinch Set,1Warning (0.00Sec

Common operations for Invisible indexes

Create Table int Primary Key varchar (ten),index  idx_name (name) invisible); Alter Table Alter Index idx_name visible; Alter Table Alter index idx_name invisible;

How to see which indexes are not visible

Mysql> SelectTable_schema,table_name,index_name,column_name,is_visible fromInformation_schema.Statistics whereIs_visible='No';+--------------+------------+------------+-------------+------------+|Table_schema|table_name|Index_name|column_name|Is_visible|+--------------+------------+------------+-------------+------------+|Slowtech|T1|Idx_name|Name|NO|+--------------+------------+------------+-------------+------------+1Rowinch Set(0.00Sec

Attention

1. Primary key index cannot be set to invisible.

MySQL 8 new features Invisible Indexes