Use DMS to maintain tables - ApsaraDB RDS

Data Management Service (DMS) lets you run standard MariaDB table maintenance statements directly from the console. This topic covers four operations: optimizing, checking, repairing, and analyzing tables.

Prerequisites

Before you begin, make sure you have:

An ApsaraDB RDS for MariaDB instance
Access to DMS for your instance

Optimize a table

Run OPTIMIZE TABLE <table_name> to reclaim unused space and defragment the table's data and index files.

Run this command when:

You have performed a large number of INSERT, UPDATE, or DELETE operations on a table
You have deleted a significant portion of table data, leaving fragmented free space

Check a table

Run CHECK TABLE <table_name> to scan the table for errors. Use the options below to control how thorough the check is.

Option	What it does
`QUICK`	Does not scan rows. Only checks that internal links are structurally valid.
`FAST`	Checks only tables that were not closed properly.
`CHANGED`	Checks only tables that have changed since the last check or were not closed properly.
`MEDIUM`	Scans rows to verify that deleted links are valid, then calculates and verifies a key checksum.
`EXTENDED`	Does a full key lookup for every row. Guarantees 100% consistency, but takes the longest time.

Note For most cases, running CHECK TABLE without options (equivalent to MEDIUM) is sufficient. Options from fastest to slowest: QUICK > FAST > CHANGED > MEDIUM > EXTENDED.

The command returns a result set with four columns: Table, Op, Msg_type, and Msg_text. A Msg_text value of OK confirms the table passed the check.

Repair a table

Run REPAIR TABLE <table_name> to fix a corrupted table.

Important

Back up the table before running REPAIR TABLE. The operation can cause data loss under some circumstances.

Use the options below to control the repair scope.

Option	Command	What it does
General	`REPAIR TABLE <table_name>`	Performs a simple repair of both the data file and the index file.
`QUICK`	`REPAIR TABLE <table_name> QUICK`	Repairs only the index file. Does not fix data file corruption.
`EXTENDED`	`REPAIR TABLE <table_name> EXTENDED`	Repairs both files by recovering and reindexing each row individually. Most thorough option.

Note Execution speed from fastest to slowest: General > QUICK > EXTENDED.

Analyze a table

Run ANALYZE TABLE <table_name> to update the table's key distribution statistics. The query optimizer uses these statistics to select efficient execution plans.