Distributed Proofreaders has a very large InnoDB-backed table that was created many years ago on a MySQL version that only supported the system tablespace (ibdata1). We’ve since upgraded to 5.7 which supports file-per-table tablespaces and we have innodb_file_per_table=ON.
With file-per-table tablespaces enabled, some table operations will move tables from the system tablespace to their own per-file tablespace and given the size of the table in question it was important to understand which ones would cause this to happen.
My research lead me to the Online DDL Operations doc which was the key. Any operation that says it Rebuilds Table will move an InnoDB table out of the system tablespace, regardless if In Place says “Yes”.
For example, the following will keep the table where it is:
- Creating, dropping, or renaming non-primary-key indexes
- Renaming a column
- Setting a column default value
- Dropping a column default value
And these will rebuild the table and move it to its own tablespace:
- Adding or dropping primary keys
- Adding or dropping a column
- Reordering columns
- Changing column types
- Converting a character set
The above are not an exhaustive list, see the Online DDL Operations documentation for your version of MySQL for the definitive list.
It’s important to know that OPTIMIZE TABLE will also move an InnoDB table out of the system tablespace.
If there’s an operation you want to perform that will rebuild the table but you want to keep the table in the system tablespace, you can temporarily set innodb_file_per_table=OFF (it’s a dynamic variable), do the operation, and then turn it back ON.
And for the curious, if you have a table already in its per-file tablespace and set innodb_file_per_table=OFF, making changes that will rebuild the table won’t move it to the system tablespace. It looks like you have to drop and recreate the table to do that.
Digital Ramblings 