create table t1 ( i int, j int, k int, index i_idx (i) invisible ) engine = innodb; create index j_idx on t1 (j) invisible; alter table t1 add index k_idx (k) invisible; 修改已經存在的索引的可見性：
alter table t1 alter index i_idx invisible; alter table t1 alter index i_idx visible; 可以通過information_schema.statistics、show index查看索引的可見性：
>select index_name, is_visible -> from information_schema.statistics -> where table_schema = 'abce' and table_name = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | NO | +------------+------------+ 1 row in set (0.00 sec) >show index from t1; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | t1 | 1 | i_idx | 1 | i | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) 不可見索引可以用來測試移除索引后對查詢性能的影響。 畢竟對于大表，刪除和重建索引是非常昂貴的操作。 系統變量optimizer_switch中的use_invisible_indexes標志控制了優化器是否使用不可見索引來構建執行計劃。 如果use_invisible_indexes=off(默認設置)，優化器會忽略不可見索引；如果設置為on，索引仍然不可見，但是優化器在生成執行計劃的時候會考慮不可見索引。
化器在生成執行計劃的時候會考慮不可見索引。
mysql>show variables like '%optimizer_switch%' index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on | 返回行數：[1]，耗時：14 ms.
