Characteristics and functions of invisible index in MySQL8.0

I don't know if you have any understanding of the similar articles on the characteristics and functions of invisible index in MySQL8.0. Today, I'm here to give you a brief introduction. If you are interested, let's take a look at the text. I believe you will gain something after reading the characteristics and functions of invisible index in MySQL8.0.

Characteristics of invisible index

All indexes created by default are visible, and you need to specify the invisible parameter if you need to create an invisible index.

Invisible index index cannot be used by default. You need to enable the parameter optimizer_switch='use_invisible_indexes=on' to use it.

Force index cannot be used, and an error will be reported.

The visible and invisible properties of an index can be converted to each other, which is fast even if the table is large

Primary key cannot be changed to invisible

Invisible index action

In the production environment, often a table is very large, we want to test the execution efficiency of a SQL without using an index, if you delete this index directly, it may be more expensive, but change the index to invisible mode, and then test it, the time is very fast.

Mysql > create index idx_emp_no on t_group (emp_no) invisible;Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show index from t_group +- -+ | Table | Non_unique | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-- -+ -+ | t_group | 1 | idx_emp_no | 1 | emp_no | A | 10 | NULL | NULL | | BTREE | NO | | NULL | +-- | -+-+ 1 row in set (0.08 sec) mysql > desc select * from t_group where emp_no=31112 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ | 1 | SIMPLE | t_group | NULL | ALL | NULL | 10 | 10.00 | Using where | +-+ | -+-+ 1 row in set 1 warning (0.00 sec) mysql > desc select * from t_group force index (idx_emp_no) where emp_no=31112 ERROR 1176 (42000): Key 'idx_emp_no' doesn't exist in table' t_group'mysql > desc select / * + set_var (optimizer_switch='use_invisible_indexes=on') * / * from t_group where emp_no=31112 +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | SIMPLE | t_group | NULL | ref | idx_emp_no | idx_emp_no | 4 | const | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec)

After reading this article on the characteristics and functions of invisible index in MySQL8.0, what do you think? If you want to know more about it, you can continue to follow our industry information section.