Databases Reference
In-Depth Information
Figure 5-3. Clustered index data layout
Clustering data has some very important advantages:
• You can keep related data close together. For example, when implementing a
mailbox, you can cluster by
user_id
, so you can retrieve all of a single user's mes-
sages by fetching only a few pages from disk. If you didn't use clustering, each
message might require its own disk I/O.
• Data access is fast. A clustered index holds both the index and the data together
in one B-Tree, so retrieving rows from a clustered index is normally faster than a
comparable lookup in a nonclustered index.
• Queries that use covering indexes can use the primary key values contained at the
leaf node.
These benefits can boost performance tremendously if you design your tables and
queries to take advantage of them. However, clustered indexes also have disadvantages:
• Clustering gives the largest improvement for I/O-bound workloads. If the data fits
in memory the order in which it's accessed doesn't really matter, so clustering
doesn't give much benefit.
• Insert speeds depend heavily on insertion order. Inserting rows in primary key
order is the fastest way to load data into an InnoDB table. It might be a good idea
