Each InnoDB table arranges the data on the disk in order to optimize queries with a primary key. This makes access a little slower but also much more secure. Data selection is relatively faster, but inserting and updating take longer. As such, InnoDB is best suited to large databases and in particular those that contain lots of relational data.
As mentioned above, with InnoDB, all the data in a transaction is treated as a single unit. Consequently, if some of the data is deleted, InnoDB will automatically delete all of the referenced data too. This makes it much easier for users to maintain the referential integrityof the database. However, this referential integrity can only be maintained if it is defined in advance. The same principle can be used to lock write access to data records.
The InnoDB table structure is saved in FRM files, user data, and indexes in a tablespace that is linked to the database. The tablespace can contain one or more files. Referential integrity applies here too. The tablespace can be configured across several directories, but this must be set up at the beginning and can’t be modified at a later date. It might be helpful to think of working with InnoDB tables as partitioning a hard drive – changing things later on can lead to data loss.