The use of InnoDB can also have a direct effect on the size of disk space with the choice of primary key and how that primary key is used. Secondary indexes always have the primary key appended to each secondary index record. Therefore, it is important to use as small a primary key data type as possible for InnoDB tables. There is an exception when a greater disk footprint can be of longer term performance benefit. In cases of extreme table size such as hundreds of gigabytes , an ordered primary key that is not a sequential key might produce more sequential disk activity if all queries use the primary key order.
Although the fill factor causes a greater data size, the overall time of a highly concurrent system that retrieves large numbers of rows by the primary key order can result in more even disk performance and overall query performance. This is very rare example that highlights that detailed monitoring and suitable production volume testing is necessary to look at long term benefits in overall performance.
Your choice of a natural primary key over a surrogate primary key has a direct influence of your default page fill factor. When the primary key is a natural key, InnoDB tries to minimize reorganization of the data with page splitting when inserting new data. Generally InnoDB will fill data pages only to 50 percent initially.
This results in a naturally larger disk footprint, and when the data volume exceeds the allocated memory to the InnoDB Buffer Pool, packing more data into 16K data pages might provide performance improvements.
Chapter 3 provides an example of the disk size that occurs due to the sequential and natural fill factors. InnoDB uses the primary key value within a secondary index, not a pointer to the primary key.
A copy of the applicable primary key is appended to each index record. When your database table has a primary key length of 40 bytes, and you have 15 indexes, the index size can be dramatically reduced by introducing a shorter primary key.
This primary key value implementation has performance benefits combined with the use of internal InnoDB primary key hash. Save my name, email, and website in this browser for the next time I comment. Toggle navigation. DML Impact Adding indexes to a table affects the performance of writes.
Duplicate Indexes One of the easiest techniques for index optimization is to remove duplicate indexes. Search related threads. Remove From My Forums. Answered by:. Archived Forums.
Sign in to vote. Thanks, Jack. Saturday, March 4, AM. Adding a new row to a table involves several steps. First, the database must find a place to store the row. For a regular heap table—which has no particular row order—the database can take any table block that has enough free space. This is a very simple and quick process, mostly executed in main memory. All the database has to do afterwards is to add the new entry to the respective data block.
If there are indexes on the table, the database must make sure the new entry is also found via these indexes. For this reason it has to add the new entry to each and every index on that table. The number of indexes is therefore a multiplier for the cost of an insert statement. I make my living from training , other SQL related services and selling my book.
Moreover, adding an entry to an index is much more expensive than inserting one into a heap structure because the database has to keep the index order and tree balance. That means the new entry cannot be written to any block—it belongs to a specific leaf node. Although the database uses the index tree itself to find the correct leaf node, it still has to read a few index blocks for the tree traversal.
Once the correct leaf node has been identified, the database confirms that there is enough free space left in this node. If not, the database splits the leaf node and distributes the entries between the old and a new node. This process also affects the reference in the corresponding branch node as that must be duplicated as well. Needless to say, the branch node can run out of space as well so it might have to be split too.
In the worst case, the database has to split all nodes up to the root node. This is the only case in which the tree gains an additional layer and grows in depth. The index maintenance is, after all, the most expensive part of the insert operation.
0コメント