MySQL 8: How to clean unused indices in a database

Introduction

As databases grow and evolve, the accumulation of unused and redundant indices can become a real concern for MySQL administrators. Not only do they take up valuable space, but they can also degrade performance. In MySQL 8, cleaning up these unused indices is a crucial maintenance task to ensure the efficiency and speed of your database operations. In this tutorial, we’ll explore the methods to identify and remove unused indices from your MySQL 8 database, from basic to advanced techniques, with code examples and expected outputs.

Understanding Indices in MySQL 8

Before diving into the cleanup process, it’s important to have a solid understanding of indices in MySQL. Indices are used to speed up the retrieval of rows from a database table and can be a powerful tool when used correctly. However, poorly chosen indices may never be used by the MySQL optimizer and thus become dead weight.

Finding Unused Indices

The first step in cleaning indices is to identify them. In MySQL 8, you can do this by examining the sys schema, which provides helper views and procedures to make this task easier. Let’s start with a straightforward query to find unused indices.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;

This query checks for unused indices across all schemas in your MySQL database. The output will show the schema name, table name, and index name of the indices that haven’t been used since the last server restart.

Removing Unused Indices

Once an unused index has been identified, the next step is to remove it. This can be achieved using a simple DROP INDEX statement:

ALTER TABLE your_table_name
DROP INDEX your_index_name;

The above command will remove the index named your_index_name from the table your_table_name. Be aware that dropping an index cannot be reversed and should be performed during a period of low activity.

Considerations Before Dropping Indices

Before dropping any index, assess its potential future use. This involves reviewing the queries that run against your database and understanding the indexing strategies. It’s advisable to track index usage over an extended period before making any permanent changes.

In addition to the sys.schema_unused_indexes view, MySQL 8 introduced the sys.schema_index_statistics and sys.schema_table_statistics views, which can provide valuable insights into index usage. Use these views to gather more detailed statistics.

Advanced Techniques for Cleaning Indices

In a more complex database setup, you might want to perform a deeper analysis before removing any indices. You can do this by monitoring index usage and using MySQL’s Performance Schema to track index statistics over time.

Monitoring Index Usage with Performance Schema

MySQL’s Performance Schema is a feature that monitors server events and collects performance data. First, ensure that the Performance Schema is enabled:

SHOW VARIABLES LIKE 'performance_schema';

If it’s not enabled, you can turn it on by modifying your MySQL configuration file and restarting the service. Once enabled, you can gather index usage data with the following:

SELECT * 
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA='your_db_name';

This will give you detailed information on how each index in your database has been used. Look for indices with a low or zero COUNT_STAR value over an extended period, as these are good candidates for removal.

Automating Index Cleanup with Scripts

Automating the process of cleaning up unused indices can save you time and reduce the risk of human error. Below is a basic script that identifies and removes unused indices automatically:

-- Script to remove unused indices
START TRANSACTION;

SET @schema_name = 'your_schema_name';

SELECT CONCAT('ALTER TABLE ', table_name, '
DROP INDEX ', index_name, ';')
INTO @ddl
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @schema_name
AND INDEX_NAME NOT IN ('PRIMARY')
AND NOT EXISTS(SELECT 1 FROM sys.schema_index_statistics
                WHERE table_schema = @schema_name
                AND table_name = table_name
                AND index_name = index_name);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

COMMIT;

Note that the above script should be thoroughly tested in a development environment first and used with great caution in production. Always ensure you have a full backup before running any cleanup script.

Conclusion

In conclusion, managing unused indices in MySQL 8 is essential for maintaining database performance and efficient storage use. By leveraging MySQL tools like the sys schema and Performance Schema, alongside thorough analysis and careful planning, you can effectively identify and remove unnecessary indices from your database, thus streamlining operations and improving overall performance.