invisible index特點(diǎn)
默認(rèn)創(chuàng)建的索引都是visible，如果需要invisible索引創(chuàng)建的時(shí)候需要指定invisible參數(shù)。
默認(rèn)無(wú)法使用invisible index索引，需要開(kāi)啟參數(shù)optimizer_switch='use_invisible_indexes=on'才能使用；
使用force index也無(wú)法使用，會(huì)報(bào)錯(cuò)；
索引的visible和invisible屬性可以相互轉(zhuǎn)換，即使表很大，這個(gè)過(guò)程也是很快的；
primary key不能改成invisible;
invisible index作用
在生產(chǎn)環(huán)境中，往往一個(gè)表是非常大的，我們想要測(cè)試一條SQL不使用某個(gè)索引的執(zhí)行效率，如果直接刪除這個(gè)索引，可能代價(jià)比較大，但是把索引改成不可見(jiàn)模式，再去測(cè)試，時(shí)間是很快的。
mysql>createindexidx_emp_noont_group(emp_no)invisible;Query OK,0rowsaffected(0.22sec)Records:0Duplicates:0Warnings:0mysql>showindexfromt_group;+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+|Table|Non_unique|Key_name|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|+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+1rowinset(0.08sec)mysql>descselect*fromt_groupwhereemp_no=31112;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|1|SIMPLE|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|10.00|Usingwhere|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1rowinset,1warning(0.00sec)mysql>descselect*fromt_groupforceindex(idx_emp_no)whereemp_no=31112;ERROR1176(42000):Key'idx_emp_no'doesn't exist in table 't_group'
mysql> desc select /*+ set_var(optimizer_switch='use_invisible_indexes=on')*/*fromt_groupwhereemp_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|+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+1rowinset,1warning(0.00sec)
========================================================
mysql> alter table city alter index idx_name visible;
Query OK, 0 rows affected (0.04 sec)
Records: 0? Duplicates: 0? Warnings: 0
mysql> desc select * from city where Name ='Qandahar'\G;
*************************** 1. row ***************************
? ? ? ? ? id: 1
? select_type: SIMPLE
? ? ? ? table: city
? partitions: NULL
? ? ? ? type: ref
possible_keys: idx_name
? ? ? ? ? key: idx_name
? ? ? key_len: 35
? ? ? ? ? ref: const
? ? ? ? rows: 1
? ? filtered: 100.00
? ? ? ? Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> alter table city alter index idx_name invisible;
Query OK, 0 rows affected (0.05 sec)
Records: 0? Duplicates: 0? Warnings: 0
mysql> desc select * from city where Name ='Qandahar'\G;
*************************** 1. row ***************************
? ? ? ? ? id: 1
? select_type: SIMPLE
? ? ? ? table: city
? partitions: NULL
? ? ? ? type: ALL
possible_keys: NULL
? ? ? ? ? key: NULL
? ? ? key_len: NULL
? ? ? ? ? ref: NULL
? ? ? ? rows: 1
? ? filtered: 100.00
? ? ? ? Extra: Using where
1 row in set, 1 warning (0.00 sec)
參考鏈接
