Invisible Indexes in MySQL 8.0

2018-10-30 · mysql, indexing, performance

Dropping an index on a big production table makes me nervous, because it's a one-way door. If some monthly report turns out to depend on it, the rebuild takes hours and the report times out meanwhile. MySQL 8.0 finally gives me a way to test the decision first. An invisible index is maintained like any other, but the optimizer ignores it. Flip it invisible, watch the slow log for a while, and drop it only when you're sure. If something breaks, flip it back in milliseconds.

It's a small feature, but it changed how I do index cleanup on 8.0 servers. You need 8.0 (it doesn't exist in 5.7 or MariaDB 10.x) and ALTER privilege on the tables.

Find candidates

Start with indexes that have never been used since the last restart. The sys schema has a ready-made view:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'appdb';

This relies on Performance Schema counters, so the server must have been running under representative load for a while. An index unused for a day may still be needed on the first of the month, and I don't trust a view until it has seen one. Also list redundant indexes, where one index is a left prefix of another:

SELECT table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes
WHERE table_schema = 'appdb';

Hide it

ALTER TABLE orders ALTER INDEX idx_customer_status INVISIBLE;

This is a metadata-only change and returns immediately, regardless of table size. The index continues to be updated on every INSERT, UPDATE and DELETE, so there is no write-side benefit yet; the point is purely to see whether reads suffer.

You can also create a new index as invisible from the start, which is useful when you want to build it during a quiet window and enable it at a controlled moment:

ALTER TABLE orders ADD INDEX idx_created_total (created_at, total) INVISIBLE;

Watch for regressions

With the index hidden, queries that relied on it fall back to another plan. I look in two places.

First, EXPLAIN a few known queries against the table. The hidden index will not appear in possible_keys:

EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 8812 AND status = 'open';

Second, and more importantly, watch the slow query log over real traffic. Make sure it is on with a sensible threshold:

SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0.5;

Then, after a day or so:

mysqldumpslow -s c -t 20 /var/lib/mysql/db1-slow.log | grep -i orders

If nothing new shows up over a full business cycle, the index is a safe drop.

Undo it or drop it

If a query regressed, restore the index instantly:

ALTER TABLE orders ALTER INDEX idx_customer_status VISIBLE;

If it did not, drop it for real, and this time you do get the write-side saving and the disk back:

ALTER TABLE orders DROP INDEX idx_customer_status;

Using a hidden index in one session

Sometimes you want to keep an index invisible for the application but still use it for an ad hoc report or to compare plans. The optimizer switch use_invisible_indexes does exactly that, per session:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ... ;
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

This is also the trick for evaluating a new index before exposing it: create it invisible, run your test queries with the switch on, and compare EXPLAIN and Handler_read_* counters against the current plan.

Checking the state

Visibility of every index on the table:

SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.statistics
WHERE table_schema = 'appdb' AND table_name = 'orders'
GROUP BY INDEX_NAME, IS_VISIBLE;

SHOW CREATE TABLE orders also shows /*!80000 INVISIBLE */ after the index definition, which means dumps made with mysqldump preserve the state when loaded into 8.0 and silently ignore it on older servers.

To confirm the optimizer really is ignoring the index, EXPLAIN a query that would obviously use it and check that key is something else or NULL.

Notes

Primary keys cannot be made invisible. ALTER TABLE t ALTER INDEX PRIMARY INVISIBLE fails with ERROR 3522 (HY000): A primary key index cannot be invisible. Since 8.0.12 the same applies to a UNIQUE NOT NULL index that is being used as the implicit primary key when the table has no explicit one.

Unique constraints still hold. An invisible unique index still rejects duplicates, because the storage engine maintains it. Invisibility only affects the optimizer's plan choice, not constraint enforcement.

Foreign keys still need their index. InnoDB requires an index on foreign key columns and will not let you drop it, but it will let you make it invisible. Queries that join on the foreign key may then slow down, so watch those specifically.

FORCE INDEX and USE INDEX hints ignore invisible indexes unless use_invisible_indexes is on. If an application uses hints, you will see ERROR 1176 (42000): Key 'idx_customer_status' doesn't exist in table 'orders', which is a strong hint that the index is still needed.

"I think we can drop this" becomes an experiment with an undo button. I wish I'd had it years ago.