Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
The gen_clust_index (clustered index) under the hood of InnoDB houses entries of primary keys along with rowids. What is interesting about the use of the gen_clust_index is the fact that any non-unique indexes you create will always have a corresponding rowid for the gen_clust_index of a table. Thus, there are always double index lookups, one for the secondary index and one for the gen_clust_index.
Any attempts to improve the layout of a table or primary key gets nullified because of the gen_clust_index, or at least marginal results at best.
Some people attempt to sort a MyISAM in PRIMARY KEY order. According to MySQL Database Design and Tuning, Page 236 Paragraph 7, under the subheading "Storing a Table in Index Order":
Granted, this works and works effectively FOR MyISAM. You could perform ALTER TABLE ... ORDER BY col1,col2,...,coln against InnoDB where the columns may or may not be that of the PRIMARY KEY. This will not produce faster results for InnoDB because ... that's right ... you must consult the gen_clust_index each time.
Some people can make the table's row format FIXED using
You could perform the following on an InnoDB table named mydb.mytb:
This will put the table in rowid order in the gen_clust_index. This may produce marginal results for InnoDB at best because ... that's right ... you must consult the gen_clust_index each time.
Now, let's get a little ridiculous. There is a NoSQL interface to query (SELECT only) MyISAM and InnoDB called the HandlerSocket (formerly called HANLDER) interface. This gives you access to data which lets you bypass all SQL, ACID, and MVCC protocols. Although it is possible, IMHO WAY TOO COMPLICATED TO CODE AND MAINTAIN. AFAIK there is nothing in print stating whether the HandlerSocket interface interacts with the gen_clust_index or not.
In summary, there are many ways to skin a cat. In this case, you cannot get a hold of the cat (the gen_clust_index). I guess this is why MyISAM continues to exist for its read performance, its pliability in table ordering, table row format, and the tools in support of it. InnoDB is will remain designed around its ACID-compliant nature until some brave soul takes the InnoDB source code and transforms it into something that has the best of both MyISAM and InnoDB.
The clustered index is perhaps the reason for InnoDB's concurrency performance on traditional spin drives.
Disk I/O is expensive. So reducing that is a huge benefit to improve concurrency.
If disk I/O starts becoming cheaper and less of a bottleneck (eg, as SSD technology becomes more stable), Oracle might decide to change how InnoDB indexes work. More likely it will stay the same, because the same technology will make 'the limitation of RAM' less of an issue.
Short answer: No.
InnoDB clusters via the primary key, and in the absence of a primary key, it picks the first unique index. In the absence of a unique index, it creates a hidden 6 byte key for clustering.
When you have the hidden 6 byte key, any secondary indexes refer to this key, rather than exact pointers to row locations (as in MyISAM), so you end up with a secondary key traversal, and then a primary key traversal to find your records.
To extrapolate a little from your question, I am assuming you are worried about memory fit with a tree, because to efficiently search, all root nodes should be in memory, since you always have to walk this path to find your leaf pages?
This is true, but one consolation is that commercial databases try and make their trees as fat as possible, rather than deep. Try running xtrabackup --stats on your data to see. For example:
There were 497839 leaf pages (~8GB), but only 416 pages above (6.5MB). I've ran this command a few times on production data, and it always surprises me when I have millions-billions of records, and only level 1-3 pages + leaf pages.