Indexes can be classified into 3 major categories:
1> Clustered Vs Non-Clustered
2> Primary Key/UK/Normal Index/Full Text Index
3> B Tree/Hash Index/R Tree
Clustered Index :: The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated. Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation. By default, with InnoDB, the primary index is a clustered index.
Non Clustered Index :: In InnoDB, the records in non-clustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key. A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index. Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in the InnoDB table. The fast index creation feature of the InnoDB Plugin makes both CREATE INDEX and DROP INDEX statements much faster for InnoDB secondary indexes.
PK :: A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If the primary key is not declared as NOT NULL, then MySQL declares them implicitly (and silently). A table can have only one PRIMARY KEY.
UK :: A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if we try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that contain NULL.
Normal :: If it’s not primary or unique, it doesn’t constrain values inserted into the table, but it does allow them to be looked up more efficiently.
Full Text Index :: It is a more specialized form of indexing that allows full text search. Think of it as (essentially) creating an “index” for each “word” in the specified column. Up to 5.5 versions, this index is supported for MyISAM engine only but from 5.6 it supports both MyISAM and InnoDB engines. The special kind of index that holds the search index in the MySQL full-text search mechanism. The MySQL feature for finding words, phrases, Boolean combinations of words, and so on within table data, in a faster, more convenient, and more flexible way than using the SQL LIKE operator or writing your own application-level search algorithm.
B Tree :: A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM. Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.
Hash :: They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible. It is available for MEMORY tables. Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.
R Tree :: A tree data structure used for spatial indexing multi-dimensional information such as geographical coordinates, rectangles or polygons.
3 ways to use indexes:
---------------------------
a> Using index to find rows :: =, Between, >= , <= , IN
b> Using index to sort data :: ORDER BY ASC, DESC
c> Using index to read data :: Use index to read the data, hence avoiding to read the row data itself. Read only index column data.
Points to Note:
a) Firstly, indexes consume adequate amount of disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file grows at a rather significant rate than a data file. In the case of large table size, the index file could reach the operating system’s maximum file size.
b) Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, there is a high performance price to pay.
c) Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.
References:
http://dev.mysql.com/doc/refman/5.7/en/glossary.html#
https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
https://www.percona.com/blog/2009/09/12/3-ways-mysql-uses-indexes/
http://www.treselle.com/blog/mysql-indexes-basicstypes-and-features/
1> Clustered Vs Non-Clustered
2> Primary Key/UK/Normal Index/Full Text Index
3> B Tree/Hash Index/R Tree
Clustered Index :: The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated. Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation. By default, with InnoDB, the primary index is a clustered index.
Non Clustered Index :: In InnoDB, the records in non-clustered indexes (also called secondary indexes) contain the primary key columns for the row that are not in the secondary index. InnoDB uses this primary key value to search for the row in the clustered index. If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key. A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index. Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in the InnoDB table. The fast index creation feature of the InnoDB Plugin makes both CREATE INDEX and DROP INDEX statements much faster for InnoDB secondary indexes.
PK :: A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If the primary key is not declared as NOT NULL, then MySQL declares them implicitly (and silently). A table can have only one PRIMARY KEY.
UK :: A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if we try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that contain NULL.
Normal :: If it’s not primary or unique, it doesn’t constrain values inserted into the table, but it does allow them to be looked up more efficiently.
Full Text Index :: It is a more specialized form of indexing that allows full text search. Think of it as (essentially) creating an “index” for each “word” in the specified column. Up to 5.5 versions, this index is supported for MyISAM engine only but from 5.6 it supports both MyISAM and InnoDB engines. The special kind of index that holds the search index in the MySQL full-text search mechanism. The MySQL feature for finding words, phrases, Boolean combinations of words, and so on within table data, in a faster, more convenient, and more flexible way than using the SQL LIKE operator or writing your own application-level search algorithm.
B Tree :: A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM. Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.
Hash :: They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible. It is available for MEMORY tables. Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.
R Tree :: A tree data structure used for spatial indexing multi-dimensional information such as geographical coordinates, rectangles or polygons.
3 ways to use indexes:
---------------------------
a> Using index to find rows :: =, Between, >= , <= , IN
b> Using index to sort data :: ORDER BY ASC, DESC
c> Using index to read data :: Use index to read the data, hence avoiding to read the row data itself. Read only index column data.
Points to Note:
a) Firstly, indexes consume adequate amount of disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file grows at a rather significant rate than a data file. In the case of large table size, the index file could reach the operating system’s maximum file size.
b) Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, there is a high performance price to pay.
c) Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.
References:
http://dev.mysql.com/doc/refman/5.7/en/glossary.html#
https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
https://www.percona.com/blog/2009/09/12/3-ways-mysql-uses-indexes/
http://www.treselle.com/blog/mysql-indexes-basicstypes-and-features/
No comments:
Post a Comment