Rearrangement of records in a table with clustered indexing

Question

Assumption : In case of clustered indexing, the order of the records on the disk is the same as that of the clustered indices.

Problem : Is the data rearranged each time on the disk when a new record is inserted? Isn't that a big hit on the performance in performing data moves on the disk.

Improvisation By MySQL : Each index page has a reserved space i.e. 1/16 of the space in the index page reserved for future updates.

My question: What happens when this space(reserved space) gets exhausted and a new record is waiting to be written? Does this rearrange all the data after that index page to fit in this new record? Isn't that a big hit on the performance? If yes, what are the possible workarounds?

Some additional reference : Does this directly map to the way an operating system handles internal fragmentation and the file system tries to have all the blocks corresponding to the same file stored as close as possible on the disk to save on the disk seek time? If possible, is there an explanation if the 2 are related and how?

I find the answer on the post (SQL query slow because of clustered index) by Gary Mcgill very helpful in regard to the same.

Answer 1

Yes, using a clustered index, and inserting something in-between hurts big time. That's why one has to be careful to use clustered indexes only when inserting in a linear manner, so for AUTO_INCREMENT IDs, and other columns, where it is uncommon for rows to get inserted 'before' the last one...

An interesting case I had with this is when the ID field was constructed with a two char prefix and a sequential number... The number part in itself would have been a good candidate for this, but with the two letter prefix, inserting ZZ1234 and AA1235 caused an issue.

Possible workaround: restructure the indexes not to use clustered, when the situation is not suitable.

Does this directly map to the way an operating system handles internal fragmentation and the file system tries to have all the blocks corresponding to the same file stored as close as possible on the disk to save on the disk seek time?

I don't think that these are directly connected. In some DBMSs, it is possible to give the DBMS full control over a block device, and there is no OS layer affecting the layout of the data on the disk/SSD/whatever...