How can I rebuild indexes and update stats in MySQL innoDB?
Why? One almost never needs to update the statistics. Rebuilding an index is even more rarely needed.
OPTIMIZE TABLE tbl;
will rebuild the indexes and do ANALYZE
; it takes time.
ANALYZE TABLE tbl;
is fast for InnoDB to rebuild the stats. With 5.6.6 it is even less needed.
(Note: The above applies to regular INDEXes
; InnoDB's FULLTEXT
does require periodic rebuilding.)
This is done with
ANALYZE TABLE table_name;
Read more about it here.
ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB. This statement works with MyISAM, BDB, InnoDB, and NDB tables.
To date (mysql 8.0.18) there is no suitable function inside mysql to re-create indexes.
Since mysql 8.0 myisam is slowly phasing into deprecated status, innodb is the current main storage engine.
In most practical cases innodb is the best choice and it's supposed to keep indexes working well.
In most practical cases innodb also does a good job, you do not need to recreate indexes. Almost always.
When it comes to large tables with hundreds of GB data amd rows and a lot of writing the situation changes, indexes can degrade in performance.
In my personal case I've seen performance drop from ~15 minutes for a count(*) using a secondary index to 4300 minutes after 2 months of writing to the table with linear time increase.
After recreating the index the performance goes back to 15 minutes.
To date we have two options to do that:
1) OPTIMIZE TABLE (or ALTER TABLE)
Innodb doesn't support optimization so in both cases the entire table will be read and re-created.
This means you need the storage for the temporary file and depending on the table a lot of time (I've cases where an optimize takes a week to complete).
This will compact the data and rebuild all indexes.
Despite not being officially recommended, I highly recommend the OPTIMIZE process on write-heavy tables up to 100GB in size.
2) ALTER TABLE DROP KEY -> ALTER TABLE ADD KEY
You manually drop the key by name, you manually create it again. In a production environment you'll want to create it first, then drop the old version.
The upside: this can be a lot faster than optimize. The downside: you need to manually create the syntax.
"SHOW CREATE TABLE" can be used to quickly see which indexes are available and how they are called.
Appendix:
1) To just update statistics you can use the already mentioned "ANALYZE TABLE".
2) If you experience performance degradation on write-heavy servers you might need to restart mysql. There are a couple of bugs in current mysql (8.0) that can cause significant slowdown without showing up in error log. Eventually those slowdowns lead to a server crash but it can take weeks or even months to build up to the crash, in this process the server gets slower and slower in responses.
3) If you wish to re-create a large table that takes weeks to complete or fails after hours due to internal data integrity problems you should do a CREATE TABLE LIKE, INSERT INTO SELECT *. then 'atomic RENAME' the tables.
4) If INSERT INTO SELECT * takes hours to days to complete on huge tables you can speed up the process by about 20-30 times using a multi-threaded approach. You "partition" the table into chunks and INSERT INTO SELECT * in parallel.