DB-유

Choosing the Right Indexes in MySQL: A Practical Guide

· mysql, performance, indexing

Most slow queries people bring me are not slow because of hardware or config. A table with a few million rows is scanned on every request, and the fix is one index. The opposite happens too: tables with twelve indexes, half redundant, so every INSERT pays for indexes no query uses.

This is the checklist I follow on MySQL 5.6/5.7 with InnoDB. Know your workload first: an index for a report that runs once a month is rarely worth slowing down every write.

1. Find the queries worth indexing

Turn on the slow log with a low threshold for a representative period:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;

After an hour, summarize it with pt-query-digest from Percona Toolkit:

pt-query-digest /var/lib/mysql/$(hostname)-slow.log --limit 20 > /tmp/digest.txt

The report ranks query fingerprints by total time. The first five entries are the ones to work on. On 5.7 you can skip the log and use the sys schema:

SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

2. Read EXPLAIN before adding anything

EXPLAIN SELECT * FROM orders WHERE customer_id = 4711 AND status = 'open' ORDER BY created_at DESC LIMIT 20;

The columns to check:

  • type: ALL is a full table scan, index a full index scan; ref, range and eq_ref are what you want.
  • key: the index chosen. NULL means none.
  • rows: estimated rows examined. Compare it with the rows the query returns.
  • Extra: Using filesort and Using temporary mean the index does not cover the ORDER BY or GROUP BY; Using index means the query was served entirely from the index (a covering index).

EXPLAIN FORMAT=JSON shows per-step cost estimates and is worth using when two indexes seem equally good.

3. Design the composite index

For the query above, the rule of thumb is: equality columns first, then the range or sort column. Both customer_id and status are equality predicates, created_at is the sort:

ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at);

With this index InnoDB walks directly to the (4711, 'open') range and reads it in created_at order, so Using filesort disappears. A few consequences of column order:

  • The index is usable for WHERE customer_id = ? alone (leftmost prefix), but not for WHERE status = ? alone.
  • Put the most selective equality column first only if all columns are always present in the WHERE; otherwise put the column that is always filtered on first.
  • A range condition (>, BETWEEN, LIKE 'abc%') stops index usage for the columns after it, so it goes last.

Check cardinality to judge selectivity:

SELECT COUNT(DISTINCT status), COUNT(DISTINCT customer_id), COUNT(*) FROM orders;

An index on a column with three distinct values (status) alone is nearly useless; the same column at the end of a composite index is fine.

4. Covering indexes, sparingly

If the query only selects a few columns, adding them to the index lets InnoDB skip the row lookup entirely:

ALTER TABLE orders ADD INDEX idx_cust_status_created_total (customer_id, status, created_at, total);

EXPLAIN then shows Using index. This is the biggest win available for hot read queries, but each extra column makes the index larger. I got carried away with these once and the table's index size ended up bigger than the data, so now it's only for the top two or three queries.

5. Drop what nobody uses

Redundant indexes are common: (customer_id) next to (customer_id, status) is pure overhead because the second one already serves every query the first could. Find them with:

pt-duplicate-key-checker --databases shop

And find indexes that have never been used since the last restart (5.6+ with Performance Schema enabled):

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

Give the server a few weeks of uptime before trusting that list, then drop with ALTER TABLE orders DROP INDEX idx_customer_id;.

Did it help?

Run the query with profiling before and after:

SET SESSION profiling = 1;   -- deprecated but still works on 5.7
SELECT ... ;
SHOW PROFILES;

Or, more reliably, compare Handler_read_* counters:

FLUSH STATUS;
SELECT ... ;
SHOW SESSION STATUS LIKE 'Handler_read%';

Handler_read_rnd_next in the tens of thousands means a scan; after the index it should drop to a few dozen and Handler_read_key / Handler_read_next take over.

Notes

  • Statistics can be stale. If EXPLAIN ignores an index that obviously fits, run ANALYZE TABLE orders; first. On 5.6+ set innodb_stats_persistent_sample_pages = 64 for large tables to get better estimates.
  • ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes when indexing long VARCHAR columns on older row formats: use a prefix index like (email(64)) or switch the table to ROW_FORMAT=DYNAMIC.
  • Functions on indexed columns disable the index. WHERE DATE(created_at) = '2017-11-30' scans; rewrite it as WHERE created_at >= '2017-11-30' AND created_at < '2017-12-01'.
  • Adding an index on 5.6+ is an online operation for InnoDB (ALGORITHM=INPLACE), but it still consumes I/O. On a busy master, use pt-online-schema-change or add it on a slave first and fail over.

Measure first, then index. Guessing is how you end up with a dozen indexes.