Are clustered index created and stored separately than the actual data in MySQL and if so, then why can we not have more than one clustered index. All we need to do is create another index and store it in memory.
8
-
AFAIK most databases only allow a single clustered index, because the index is based on the way the data is physically stored somewhere, and there is usually a single copy of that data.Tim Biegeleisen– Tim Biegeleisen2017-12-08 11:30:23 +00:00Commented Dec 8, 2017 at 11:30
-
@TimBiegeleisen : Thank you for the clarification. So, does sql creates a copy of data in disk and stores it as index in memory as well incase of clustered index as well. Like sql does in case of non clustered index, it creates an index with b-tree but leaf nodes only contains pointer to actual dataprem– prem2017-12-08 13:34:24 +00:00Commented Dec 8, 2017 at 13:34
-
The answer below looks good to me.Tim Biegeleisen– Tim Biegeleisen2017-12-08 13:36:04 +00:00Commented Dec 8, 2017 at 13:36
-
@prem, as the records are stored in order of the cluster-indexed fields, no b-tree is necessary. Repeatedly dividing the list in half during the lookup has the same effect - and unlike a b-tree, it's always perfectly balanced.Joshua R.– Joshua R.2017-12-08 19:17:56 +00:00Commented Dec 8, 2017 at 19:17
-
@JoshuaRubin -- No. A BTree, or something like it, is necessary.Rick James– Rick James2017-12-09 18:22:13 +00:00Commented Dec 9, 2017 at 18:22
|
Show 3 more comments
2 Answers
A clustered index is, at least partially, the way the table is physically sorted and stored, i.e. the row order on disk. That's why you can only have one. But because it reflects the physical layout of the rows, it's potentially more compact and performant than a typical index.
UPDATE: As @RickJames excellently points out below, in InnoDB (MySQL's default engine since 5.5.5), a lookup is typically a two-stage process. One b-tree relates a secondary key to a primary key, a second b-tree relates the primary key to the location of a data record. If retrieving data on primary key, only the second lookup is necessary. In that sense, a b-tree lookup is always necessary.
Additionally, according to the MySQL documentation:
Typically, the clustered index is synonymous with the primary key. 1
And the reason it's considered "clustered" and not just a primary key is because InnoDB attempts to order the data records according to primary key and leaves room for future records to be inserted in the correct location in its data pages 2.
Because of that, not only is a query on a InnoDB primary key one fewer b-tree lookup than a secondary index, but the primary key b-tree can be significantly smaller because of the physically ordering of the data on disk.
It stands to reason even if there were a mechanism to make a secondary index that pointed directly to a data record (like an index MyISAM), it wouldn't perform as well as InnoDB's primary/clustered index.
So, it's fundamentally the (at least partial) physical ordering of data records by primary key which prevents you from getting the same performance from a secondary index.
Comments
MySQL's InnoDB does the following for its PRIMARY KEY: The data records are in PK order, stored together in a B+Tree structure. This allows for rapid point-queries and range scans. That is, the value at the 'bottom' of the tree has all the columns of the table.
InnoDB's secondary keys are also in a B+Tree, but the bottom values are the PK columns. Hence, a second lookup is needed to fetch a row(s) by a secondary key.
Note that a secondary key could contain all the columns of the table, thereby acting like a second clustered index. The drawback is that any modification to the table would necessarily involve changes to both BTrees.
MyISAM, in contrast, throws the data into a file (the .MYD) and has every index in its own BTree in the .MYI file. The bottom of each BTree is a pointer (row number or byte offset) into the .MYD. The PK is not implemented any differently than a secondary key.
(Note: FULLTEXT and SPATIAL indexes are not covered by the above discussion.)
