|< Day Day Up >|
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:
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.
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.
|< Day Day Up >|