mysql innodb 最大_mysql innodb 表过大问题

Solution One：

To shrinking a shared InnoDB tablespace is:

1. Backup *all* InnoDB tables with mysqldump.

2. Drop all of the InnoDB tables.

3. Physically delete the ibdata1 file at the filesystem interface.

4. Restart MySQL Server, which recreates a new, small tablespace file.

5. Restore all your InnoDB tables, which expands the tablespace file as needed.

Solution Two：

To shrink an .ibd file it"s enough to runALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I thinkOPTIMIZE TABLEdoes exactly the same internally.ALTER TABLEhowever is preferred because you can usept-online-schema-changeto avoid blocking the table.

To get better understanding how data use the tablespace I would recommend InnoDB toolshttps://github.com/jeremycole/innodb_ruby

For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:

# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate

Start Page ╭────────────────────────────────────────────────────────────────╮

0 │███▄▂░░ │

╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):

Page Type Pages Ratio

█ System 3 42.86%

█ Index 15 1 14.29%

█ Index 16 1 14.29%

░ Free space 2 28.57%