In this blog post, we’ll look at some of the basic housekeeping steps for MySQL indexes.
We all know that indexes can be the difference between a high-performance database and a bad/slow/painful query ride. It’s a critical part that needs deserves some housekeeping once in a while. So, what should you check? In no particular order, here are some things to look at:
1. Unused indexes
With sys schema, is pretty easy to find unused indexes: use the schema_unused_indexes view.
mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-------------+
| object_schema | object_name | index_name |
+---------------+-----------------+-------------+
| world | City | CountryCode |
| world | CountryLanguage | CountryCode |
+---------------+-----------------+-------------+
2 rows in set (0.01 sec)
This view is based on the performance_schema.table_io_waits_summary_by_index_usage table, which will require enabling the Performance Schema, the events_waits_current consumer and the wait/io/table/sql/handler instrument. PRIMARY (key) indexes are ignored.
If you don’t have them enabled, just execute these queries:
update performance_schema.setup_consumers set enabled = 'yes' where name = 'events_waits_current';
update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/io/table/sql/handler';
Quoting the documentation:
“To trust whether the data from this view is representative of your workload, you should ensure that the server has been up for a representative amount of time before using it.”
And by representative amount, I mean representative:
- Do you have a weekly job? Wait at least one week
- Do you have monthly reports? Wait at least one month
- Don’t rush!
Once you’ve found unused indexes, remove them.
2. Duplicated indexes
You have two options here:
- pt-duplicate-key-checker
- the schema_redundant_indexes view from sys_schema
The pt-duplicate-key-checker is part of Percona Toolkit. The basic usage is pretty straightforward:
[root@e51d333b1fbe mysql-sys]# pt-duplicate-key-checker
# ########################################################################
# world.CountryLanguage
# ########################################################################
# CountryCode is a left-prefix of PRIMARY
# Key definitions:
# KEY `CountryCode` (`CountryCode`),
# PRIMARY KEY (`CountryCode`,`Language`),
# Column types:
# `countrycode` char(3) not null default ''
# `language` char(30) not null default ''
# To remove this duplicate index, execute:
ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 2952
# Total Duplicate Indexes 1
# Total Indexes 37
Now, the schema_redundant_indexes view is also easy to use once you have sys schema installed. The difference is that it is based on the information_schema.statistics table:
mysql> select * from schema_redundant_indexesG
*************************** 1. row ***************************
table_schema: world
table_name: CountryLanguage
redundant_index_name: CountryCode
redundant_index_columns: CountryCode
redundant_index_non_unique: 1
dominant_index_name: PRIMARY
dominant_index_columns: CountryCode,Language
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `world`.`CountryLanguage` DROP INDEX `CountryCode`
1 row in set (0.00 sec)
Again, once you find the redundant index, remove it.
3. Potentially missing indexes
The statements summary tables from the performance schema have several interesting fields. For our case, two of them are pretty important: NO_INDEX_USED (means that the statement performed a table scan without using an index) and NO_GOOD_INDEX_USED (“1” if the server found no good index to use for the statement, “0” otherwise).
Sys schema has one view that is based on the performance_schema.events_statements_summary_by_digest table, and is useful for this purpose: statements_with_full_table_scans, which lists all normalized statements that have done a table scan.
For example:
mysql> select * from world.CountryLanguage where isOfficial = 'F';
55a208785be7a5beca68b147c58fe634 -
746 rows in set (0.00 sec)
mysql> select * from statements_with_full_table_scansG
*************************** 1. row ***************************
query: SELECT * FROM `world` . `Count ... guage` WHERE `isOfficial` = ?
db: world
exec_count: 1
total_latency: 739.87 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 746
rows_examined: 984
rows_sent_avg: 746
rows_examined_avg: 984
first_seen: 2016-09-05 19:51:31
last_seen: 2016-09-05 19:51:31
digest: aa637cf0867616c591251fac39e23261
1 row in set (0.01 sec)
The above query doesn’t use an index because there was no good index to use, and thus was reported. See the explain output:
mysql> explain select * from world.CountryLanguage where isOfficial = 'F'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
Note that the “query” field reports the query digest (more like a fingerprint) instead of the actual query.
In this case, the CountryLanguage table is missing an index over the “isOfficial” field. It is your job to decide whether it is worth it to add the index or not.
4. Multiple column indexes order
It was explained before that Multiple Column index beats Index Merge in all cases when such index can be used, even when sometimes you might have to use index hints to make it work.
But when using them, don’t forget that the order matters. MySQL will only use a multi-column index if at least one value is specified for the first column in the index.
For example, consider this table:
mysql> show create table CountryLanguageG
*************************** 1. row ***************************
Table: CountryLanguage
Create Table: CREATE TABLE `CountryLanguage` (
`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 (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
A query against the field “Language” won’t use an index:
mysql> explain select * from CountryLanguage where Language = 'English'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 984
Extra: Using where
Simply because it is not the leftmost prefix for the Primary Key. If we add the “CountryCode” field, now the index will be used:
mysql> explain select * from CountryLanguage where Language = 'English' and CountryCode = 'CAN'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: CountryLanguage
type: const
possible_keys: PRIMARY,CountryCode
key: PRIMARY
key_len: 33
ref: const,const
rows: 1
Extra: NULL
Now, you’ll have to also consider the selectivity of the fields involved. Which is the preferred order?
In this case, the “Language” field has a higher selectivity than “CountryCode”:
mysql> select count(distinct CountryCode)/count(*), count(distinct Language)/count(*) from CountryLanguage;
+--------------------------------------+-----------------------------------+
| count(distinct CountryCode)/count(*) | count(distinct Language)/count(*) |
+--------------------------------------+-----------------------------------+
| 0.2368 | 0.4644 |
+--------------------------------------+-----------------------------------+
So in this case, if we create a multi-column index, the preferred order will be (Language, CountryCode).
Placing the most selective columns first is a good idea when there is no sorting or grouping to consider, and thus the purpose of the index is only to optimize where lookups. You might need to choose the column order, so that it’s as selective as possible for the queries that you’ll run most.
Now, is this good enough? Not really. What about special cases where the table doesn’t have an even distribution? When a single value is present way more times than all the others? In that case, no index will be good enough. Be careful not to assume that average-case performance is representative of special-case performance. Special cases can wreck performance for the whole application.
In conclusion, we depend heavily on proper indexes. Give them some love and care once in a while, and the database will be very grateful.
All the examples were done with the following MySQL and Sys Schema version:
mysql> select * from sys.version;
+-------------+-----------------+
| sys_version | mysql_version |
+-------------+-----------------+
| 1.5.1 | 5.6.31-77.0-log |
+-------------+-----------------+
via MySQL Performance Blog
Basic Housekeeping for MySQL Indexes