MySQL Learning 2--mysql Index

Here are a few articles on the web that introduce MySQL index:

Examples of MySQL index use:

Http://www.cnblogs.com/yjl49/archive/2012/02/08/2371926.html

Http://www.cnblogs.com/dreamhome/archive/2013/04/16/3025304.html

Understanding mysql--Indexing and Optimization: (highly recommended, explained in depth)

Http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

MySQL 5.6 Reference Manual

Http://dev.mysql.com/doc/refman/5.6/en/preface.html

MySQL index using a fool tutorial

Http://www.php100.com/html/webkaifa/database/Mysql/2013/0316/12223.html

InnoDB Index (recommended)

Http://blog.sina.com.cn/s/blog_9e55238601017ddu.html

One, from the perspective of the index method (or index algorithm)

There are four main types of indexes in MySQL: B-tree index, Hash Index, Full-text Index and R-tree index, and the following is an approximate analysis of the basic implementation and storage structure of these four kinds of indexes.

1. B-tree Index
The B-tree index is the most frequently used index type in a MySQL database, and all storage engines except the Archive storage engine support B-tree indexes. Not only in MySQL, but in many other database management systems, the B-tree index is also the most important index type, mainly because the storage structure of the B-tree index has a very good performance in data retrieval of the database. The B-tree index of the INNODB storage engine actually uses the storage structure b+tree, which is a small modification on the basis of the B-tree data structure, which, in addition to the information about the index key stored on each leaf node, stores a pointer to the leaf node The pointer information of the neighboring leaf node, which is mainly to speed up the efficiency of retrieving multiple neighboring leaf node.

Reference article: http://blog.sina.com.cn/s/blog_9e55238601017ddu.html

In the INNODB storage engine, there are two different forms of indexes, one is the primary key index (Primary key) in Cluster form, and the other is a common B-tree index that is basically the same as other storage engines (such as the MyISAM Storage engine), which The Innodb storage engine is known as secondary Index. Both indexes are exactly the same in both root Node and Branch Nodes. And the Leaf Nodes there is a difference. In Primary key, Leaf Nodes holds the actual data of the table, not only the data of the primary key field, but also the data of the other fields, and the entire data is arranged in an orderly manner with primary key values. and secondary index and other ordinary B-tree index is not much different, but in the leaf Nodes the index key to store the relevant information, but also stored the INNODB primary key value.

2. Hash Index
Hash index is not used much in MySQL, it is mainly used by memory storage engine, and the hash index is the default index type in memory storage engine.

Because of the particularity of the hash index structure, the retrieval efficiency is very high, the index retrieval can be positioned at once, and the BTree index needs to be accessed from the root node to the node point, so that the hash index is much more efficient than the B-tree index.

But the Hash index itself due to the fact that the particularity also brought a lot of limitations and drawbacks, mainly have the following:
1). Hash index can only satisfy "=", "in" and "<=>" query, can not use range query;
2). Hash index can not be exploited to avoid the sorting operation of data;
Because the hash index is stored in the hash after the hash value, and the size of the hash value is not necessarily the same as the key value before the hash operation, so the database can not use the index data to avoid any and sorting operations;
3). Hash index cannot use partial index key query;
For the composite index, the hash index in the calculation of the hash value when the combination index key merge and then calculate the hash value together, rather than calculate the hash value alone, so when we through the combination index of the previous or several index key query, the hash index can not be exploited;
4). Hash index at any time can not avoid the table sweep surface;
Because of the possibility of having the same hash value in different index keys, even if we just take the number of records that satisfy some hash key value, we can not directly complete the query directly from the hash index, or get the corresponding result by the corresponding comparison by accessing the actual data in the table.
5). If the hash index encounters a large number of hash values equal, the performance will not necessarily be higher than the B-tree index;

3. Full-text Index
The Full-text index, which is what we often call full-text indexing, is supported only by the MyISAM storage engine in MySQL, and not all data types support full-text indexing. Currently, only columns of the three data types, Char,varchar and TEXT, can be full-text indexed.

The InnoDB engine can implement full-text indexing through plug-ins.

4. R-tree Index

The R-tree index is probably an index type that we rarely see in other databases, primarily to solve the problem of spatial data retrieval.
In MySQL, a data type GEOMETRY for storing spatial information is supported, and is based on the OpenGIS specification. In previous versions of MySQL5.0.16, only the MyISAM storage engine supported that data type, but starting with the MySQL5.0.16 version, the Bdb,innodb,ndbcluster and Archive storage engines also began to support that data type. Of course, although a variety of storage engines are starting to support the GEOMETRY data type, only the MyISAM storage engine supports R-tree indexes.

Two, clustered indexes and nonclustered indexes

Currently, SOLIDDB and InnoDB are the only storage engines that support clustered indexes. InnoDB is clustered according to the primary key, and if no primary key is defined, InnoDB will try to replace it with a unique non-empty index. Without such an index, INNODB defines the hidden primary key and aggregates it on top.

Reference article: http://lobert.iteye.com/blog/1673540

The logical order of the key values in the clustered index determines the physical order of the corresponding rows in the table.
The clustered index determines the physical order of the data in the table. A clustered index is similar to a phone book, which arranges data by last name. Because a clustered index specifies the order in which data is physically stored in a table, a table can contain only one clustered index. However, the index can contain multiple columns (combined indexes), just as the phone book is organized by last name and first name.
Clustered indexes are particularly effective for columns that are frequently searched for range values. When you use a clustered index to find the row that contains the first value, you can ensure that the rows that contain the subsequent index values are physically adjacent. For example, if a query executed by an application frequently retrieves records from a range of dates, using a clustered index can quickly find the row that contains the start date, and then retrieve all adjacent rows in the table until the end date is reached. This helps improve the performance of such queries. Similarly, if a column is often used to sort data retrieved from a table, you can save costs by aggregating (physically sorting) the table on that column, avoiding sorting each time the column is queried.

When the index value is unique, it is also efficient to use a clustered index to find a particular row. For example, the fastest way to find a specific employee with a unique employee ID column emp_id is to create a clustered index or PRIMARY KEY constraint on the emp_id column.

Reference article: http://www.cnblogs.com/aspnethot/articles/1504082.html

Nonclustered indexes

The logical order of the indexes in a nonclustered index is different from the physical storage order of the disk upstream. The data is stored in one place, and the index is stored in another place, with the index pointing to where the data is stored.

Items in a nonclustered index are stored in the order of the index key values, and the information in the table is stored in a different order (this can be specified by a clustered index). For nonclustered indexes, you can create a nonclustered index for each column that is commonly used when looking up data in a nonclustered index on a table.

What is the description of the clustered index versus the nonclustered index in the database?

The index is described in the form of a binary tree, so we can differentiate between aggregation and nonclustered indexes: the leaf node of the clustered index is the final data node, and the leaf section of the nonclustered index is still an index node, but it has a pointer to the final data.

Reference article: http://jingyan.baidu.com/article/e73e26c0f1e82d24acb6a75d.html

III. single-column and federated Indexes (multicolumn indexes)

Single-column index: Refers to indexing on a field.

The following is a detailed explanation of the federated index

Federated indexes, also called composite indexes: indexes can be built not only on a single column, but also through multiple columns, which are called federated indexes.

For composite index, official website: http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

Recommended article: Http://blog.sina.com.cn/s/blog_9e55238601017ddu.html

The two URLs are very detailed.

The combined index adheres to the leftmost prefix principle.

The leftmost prefix principle:
For example, a federated index is established in A,b,c three columns in table user.
① full column matching: SELECT * from user where a =? and B =? and C =?;
A full-column match, as the name implies, puts the value of the indexed three columns, which can be used exactly to the exact index, even if the order is different. The MySQL query optimizer automatically adjusts the order of where statements (rather than INNODB) to fit the index structure
② leftmost prefix match: SELECT * from user where a =? and b =?;
Such a statement does not provide a full column value, but because the index is continuously matched from the car, it is also possible to use an index built using the a,b,c three columns.
③ uses an exact index match, and one of the intermediate conditions is not provided. Select * from user where a =? and C =?;
In this case, both A and C are in the index column, but because B does not exist, the connection to the leftmost prefix cannot be matched. In this case there are generally two solutions, if there are a large number of queries in this way, you might consider establishing a federated index on columns A and C. Another way is by pits, that is, if the value on column B is not much (for example, an enumeration, or a simple bit type), by optimizing SQL to select * from user where a =? and b in (?,?,?......) and C =? In a way that can improve some of the performance.
④ query is not used to index the first column of select * from user where B =? and C =?;
This condition does not conform to the leftmost prefix and the index cannot be used.
⑤ Match String prefix condition select * from user where a =? and b=? and c like ' abc% ';
This condition conforms to the leftmost prefix and can be indexed, but cannot be used if the wildcard character does not appear at the end of love.
⑥ Range Query select * from user where a >? and B =? and C =?;
The index can be used in this case, but the indexes of columns B and C cannot be used, and if the range query is not the leftmost prefix or if there are two range columns in the query condition.
⑦ condition with function or expression select * from user where a =? and B =? and left (c,2) = ' ba '
Although and C like ' ba% '; the effect is consistent, but because the function is used, the index cannot be used.
For SQL that uses expressions, such as SELECT * from user where a =? and B =? and c-1 =?;
The index cannot be used.

For single-row and multi-column indexes, many scholars have also done a lot of testing, can refer to the following link:

Tens data tables, single-row indexes and multi-column index performance comparisons

MySQL Learning 2--mysql Index