Mysql optimize innodb optimization details

If you delete a mysql table in a large scope, it is better to optimize the table, which will be faster. I have written a similar article for the myisam storage engine. Please refer to: instance description optimize table is very important when optimizing mysql

1. Delete the data and try to optimize it.

Mysql> delete FROM 'test' WHERE d_id> 397136;
Query OK, 306356 rows affected (19.48 sec)
 
Mysql> optimize table test;
+ ------------ + ---------- + ------------------------------------------------------------------- +
| Table | Op | Msg_type | Msg_text |
+ ------------ + ---------- + ------------------------------------------------------------------- +
| Test1.test | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| Test1.test | optimize | status | OK |
+ ------------ + ---------- + ------------------------------------------------------------------- +
2 rows in set (0.35 sec)
When optimize is executed, it is very slow and may be stuck. Msg_text indicates the innodb engine and does not support optimize. I found the following content on the official website:
Table does not support optimize, doing recreate + analyze instead.
It is because the table that you are using is InnoDB.
You can optimize the InnoDB tables by using this.
Alter table table. name ENGINE = 'innodb ';

Generally, if myisam is converted to innodb, the alter table. name engine = 'innodb' is used for conversion. This can also be used for optimization.

2. alter optimize data

Mysql> alter table test engine = 'innodb ';
Query OK, 384781 rows affected (19.88 sec)
Records: 384781 Duplicates: 0 Warnings: 0
If alter is slow, optimize my. cnf, [mysqld], add the following content, and reload.
Innodb_buffer_pool_size = 1G
Innodb_file_io_threads = 4
The general configuration of innodb_file_io_threads is smaller than the number of equal CPU cores. Innodb_buffer_pool_size is less than or equal to 1/2 of the physical memory. In principle, it is enough.

3. Comparison after Optimization

Mysql> use information_schema
 
Mysql> select concat (round (sum (data_length/1024/1024), 2), 'mb') as data_length_MB,
-> Concat (round (sum (index_length/1024/1024), 2), 'mb') as index_length_MB
-> From tables where
-> Table_schema = 'test1'
-> And table_name = 'test ';
+ ---------------- + ----------------- +
| Data_length_MB | index_length_MB |
+ ---------------- + ----------------- +
| 20.55 MB | 27.55 MB | // before optimization
+ ---------------- + ----------------- +
1 row in set (0.01 sec)
 
Mysql> select concat (round (sum (data_length/1024/1024), 2), 'mb') as data_length_MB,
-> Concat (round (sum (index_length/1024/1024), 2), 'mb') as index_length_MB
-> From tables where
-> Table_schema = 'test1'
-> And table_name = 'test ';
+ ---------------- + ----------------- +
| Data_length_MB | index_length_MB |
+ ---------------- + ----------------- +
| 20.55 MB | 16.55 MB | // After optimization
+ ---------------- + ----------------- +
1 row in set (0.00 sec)
Data_length_MB, data in the first column; index_length_MB; and index in the second column