InnoDB Indexing Features

The InnoDB storage engine has its own set of index-related internal structures, caches, and features. This section briefly examines these capabilities; Chapter 12 discusses how to configure, monitor, and tune these capabilities.

Index Structure and Storage

The index structure diagrams displayed earlier in the chapter are still applicable for InnoDB. However, InnoDB includes a number of additional index storage and performance concepts:

• InnoDB actually stores each row's data in an internal structure known as a clustered index. The table is physically stored in the order of this index.

• When a primary key exists for your table, the clustered index is equivalent to the primary key, so the rows are stored physically in the order of the primary key.

• In the absence of a primary key, MySQL itself looks for another unique index to assign as the clustered index. The first unique index that consists of non-null columns will be chosen.

• If there are no qualified indexes, InnoDB generates its own internal index, along with a 6-byte unique row identifier known as the rowID. This approach leaves the rows physically stored in the order in which they were inserted. Note that you cannot see this index via the SHOW INDEX command or through the MySQL Administrator.

• All other indexes for the table include the value of the primary key along with their other index details. This value is then used to point back to the clustered index, which in turn yields the data.

Buffer Pool

The InnoDB buffer pool caches indexes as well as the underlying data, unlike MyISAM's key cache, which only stores index key information in a memory buffer.

Administrators have considerable control over the buffer pool and other InnoDB memory structures, including defining its capacity via the innodb_buffer_pool_size setting, whether it takes advantage of extended memory on Windows-based servers, and how frequently the log buffer is synchronized to disk. All of these, and many other InnoDB-specific topics, are explored later in Chapter 12.

Memory Pool

The memory pool is a RAM-based cache that holds information about internal MySQL structures as well as data dictionary information. Administrators can define the initial size of this cache via the innodb_additional_mem_pool_size server variable.

Adaptive Hash Index

As you saw earlier in this chapter, hash indexes provide extremely fast access to information, especially when combined with memory-resident data. InnoDB features an adaptive hash index algorithm, which examines access patterns for your data. If InnoDB detects the right kind of pattern, it automatically constructs a hash index. Aside from allocating enough memory to InnoDB for caching, there's nothing you need to do to enable this functionality; it builds the index without administrative intervention.

To determine if your server is benefiting from an adaptive hash index, simply run SHOW INNODB STATUS and locate the section containing the relevant details:

 ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 397, seg size 399, is empty Ibuf for space 0: size 1, free list len 397, seg size 399, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 1, node heap has 1 buffer(s) 1880.28 hash searches/s, 81.23 non-hash searches/s 

In this example, these indexes are aiding a high percentage of searches.

Automatic Foreign Key Index Generation

The benefits of foreign key constraints were discussed earlier in the book. To make these constraints possible, it's important that the proper indexes are in place. As of version 4.1.2, InnoDB now automatically generates these indexes when you specify your foreign constraints.