MySQL 8.0 has a rich set of features. One of the feature which interests DBA’s more is invisible index
What is an index in MySQL ?
- The indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.
What is invisible index ?
- The invisible index is ability to mark an index as unavailable for use by optimizer.
- In MySQL 5.7 and other previous versions the indexes are visible by a default.
- To control the index visibility for a new index ,use a visible or invisible key words as a part of the index creation.
How to add a invisible index on existing table ?
Syntax :
alter table table-name alter index index-name invisible (key-word)
Example :
mysql> alter table mydbops_test alter index idx_age invisible;
Query OK, 0 rows affected (2.43 sec)
Records: 0 Duplicates: 0 Warnings:0
Table Structure :
mysql> show create table mydbops_test\G
*************************** 1. row ***************************
Table: mydbops_test Create Table: CREATE TABLE `mydbops_test`
( `name` varchar(50) DEFAULT NULL,
`age` int(10) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
KEY `idx_name` (`name`),
KEY `idx_age` (`age`) /*!80000 INVISIBLE */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
Where it can be used ?
The invisible index can be used on below cases.
Case 1 : (Dropping an unused index )
→ The indexes are great when the optimizer can find an optimal execution plan to execute them.
→ In heavy indexes on tables it will hurt the performance of modifications like (insert, update) queries.And they can also hurt the performance of reads as the optimizer needs to evaluate them for plan selection.
→ In MySQL 5.7 sys schema was introduced,using this sys schema we can find un used index easily.
→ Here the main drawback is difficult to judge cases where there are redundant indexes, and you expect that dropping an index should result in one of the other candidates being selected.
mysql> select * from sys.schema_unused_indexes where object_name='City';
+---------------+-------------+----------------+
| object_schema | object_name | index_name |
+---------------+-------------+----------------+
| world | City | CountryCode |
| world | City | idx_District |
| world | City | idx_combo |
| world | City | idx_Population |
+---------------+-------------+----------------+
Example Query :
SELECT District, SUM(Population) FROM City WHERE District like ‘%York%’ and Population BETWEEN 670000 AND 700000 GROUP BY District;
Explain Plan :
+----+-------------+-------+------------+-------+------------------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | City | NULL | index | idx_District,idx_combo | idx_District | 20 | NULL | 40 | 11.23 | Using where |
+----+-------------+-------+------------+-------+------------------------+--------------+---------+------+------+----------+-------------+
→ The query is using index (idx_District) ,but the (idx_District) index shows as un-used index.
+---------------+-------------+----------------+
| object_schema | object_name | index_name |
+---------------+-------------+----------------+
| world | City | CountryCode |
| world | City | idx_District |
+---------------+-------------+----------------+
→ If this index was dropped my query performance will be degraded.
→ In this case we can use invisible index.When an index goes unseen, the optimizer can’t use it.
→ The index is still actively maintained,kept up-to-date as data is modified, but no queries will be permitted to make use this index.
→ In this case the idx_District index will not use so i mark an index as unavailable for use by the Optimizer..
Case 2 : (Evaluating the Optimizer behaviour)
→ You have many indexes, but you are not sure which one is not in use. You can change an index to invisible to note if there is any performance degradation. If it will affect the query performance, you can change it back immediately.
→ In some cases, the only one query can use that specific index for example (fulltext index),in that case invisible index could be a great solution.
Example :
In this case i am not sure which index will be used by application queries .
Example Query :
select a.ID,a.Name,a.Population,b.Language,b.IsOfficial from City as a,CountryLanguage as b where b.CountryCode=a.ID and b.Percentage=0.9 and b.Language=’English’;
Table Structure :
mysql> show create table CountryLanguage\G
*************************** 1. row ***************************
Table: CountryLanguage Create Table: CREATE TABLE `CountryLanguage` (`ID` int(11) NOT NULL AUTO_INCREMENT,
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`),
KEY `idx_Percentage` (`Percentage`),
KEY `idx_Language` (`Language`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
+----+-------------+-------+------------+--------+----------------------------------+--------------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+----------------------------------+--------------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | b | NULL | ref | PRIMARY,CountryCode,idx_Language | idx_Language | 30 | const | 6201 | 10.00 | Using where |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | world.b.CountryCode | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+----------------------------------+--------------+---------+---------------------+------+----------+-------------+
Then i try to disable the (idx_Language) index,after disable this index it will use (idx_Percentage) index the number of rows scanning also reduced.
mysql> alter table CountryLanguage alter index idx_Language invisible
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
Explain Plan :
mysql> explain select a.ID,a.Name,a.Population,b.Language,b.IsOfficial from City as a,CountryLanguage as b where b.CountryCode=a.ID and Percentage=0.9 and Language=’English’;
+----+-------------+-------+------------+--------+-------------------------------------------------+----------------+---------+---------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------------------+----------------+---------+---------------------+------+----------+-----------------------+
| 1 | SIMPLE | b | NULL | ref | PRIMARY,CountryCode,idx_Percentage,idx_Language | idx_Percentage | 4 | const | 10 | 6.10 | Using index condition |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | world.b.CountryCode | 1 | 100.00 | Using where |
+----+-------------+-------+------------+--------+-------------------------------------------------+----------------+---------+---------------------+------+----------+-----------------------+
2 rows in set, 0 warnings (0.00 sec)
Using Force Index :
→ In this case i have hide the combo index and try to force the index to optimizer.
Example :
mysql> explain SELECT District, SUM(Population) FROM City force index (idx_combo) WHERE District like '%York%' and Population BETWEEN 670000 AND 700000 GROUP BY District;
ERROR 1176 (42000): Key 'idx_combo' doesn't exist in table 'City'
It throws the error,this index is not visible by the optimizer.
Note :
- The primary key index can’t be made invisible.
- If there is no primary key in a table but it have a UNIQUE key. In this case the first UNIQUE key is treated as primary key so we can’t be made invisible
→ In MySQL 8.0.3 they have implemented selected sessions to activate the index and measure the effect of this invisible index.
→ In this case we have to set a optimizer_switch to make the index visible, for session level.
Example :
mysql> set session optimizer_switch = 'use_invisible_indexes=on';
Query OK, 0 rows affected (4.28 sec)
Find Invisible indexes using information Schema :
→ The information about whether an index is visible or invisible is available from the information_schema.statistics table IS_VISIBLE ( Column ) or you can use show indexes.
mysql> select table_schema,table_name,index_name,is_visible from information_schema.statistics where table_name='City';
+--------------+------------+----------------+------------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | IS_VISIBLE |
+--------------+------------+----------------+------------+
| world | City | CountryCode | YES |
| world | City | idx_combo | NO |
| world | City | idx_combo | NO |
| world | City | idx_District | YES |
| world | City | idx_Population | YES |
| world | City | PRIMARY | YES |
+--------------+------------+----------------+------------+
Conclusion :
→ The invisible indexes are a great new feature in MySQL 8.0.It will more help for MySQL users.In MySQL 5.7 we are using performance schema to find out the unused indexes and removed from table.
→ In MySQL 8.0 the invisible index will be more helpful to check the query performance and index usage.
Credits : Photo by Robert McLay on Unsplash
%20vs%20systemctl%20stop%20(2).png)
.png)
.svg)
