Index of the purpose is to facilitate quick search, index of a book is the catalogue, can make us to quickly locate to search content; Existing database data is recorded, so the purpose of the index is easy to find some records,

Index type (common database of books about index categories) :
?? (1) the only index: do not allow any two rows have the same value index
???? USES the primary key of the index is the only candidate keys and indexes, for the Lord and the candidate keys can be determined only a tuple, namely a table does not exist to the same primary key and candidate key, in MySQL, when you create a primary key and candidate key, MySQL will be established for each index, after all, to satisfy the uniqueness, still in the update data when testing whether the primary key or a candidate key already exists, the index is fast inspection standard,

?? (2). The primary key index: the only index can be thought of as a special, only the primary key is used to establish the index

?? (3). A single index: any of a single data item set up index
???? If you have the following table (country, city, personNumber), if we want to query the number of a country, we shall be based on national index, so that a single data item set up index is a single index,
?? (4). A composite index: set up multiple data items index
???? If have the following table (country, city, personNumber), if we want to query the number of a city, we should be in country, city index, multiple index data items, we shall be specified in the order of the sort of countries should prioritize here, second city,

?? (5). The cluster index: the primary key is used to establish the index, the physical store order is consistent with the primary key order, because the data is only a physical store order, so a table with only a clustering index,
???? In MySQL, select primary key will automatically after the primary key to create an index, the index can maintain the uniqueness of the primary key, the leaf node contains a primary key value, while the leaf node points to a complete record of the

?? 6. The clustering index (secondary indexes, auxiliary index) : in addition to the cluster index, all the rest of the index are clustering index
???? Why do the clustering index is secondary indexes? Emphasis on a word, can be expected if the WHERE condition is not according to the primary key index, then we will need to be established based on the primary key index search, so establish the index of the leaf node contains the record's primary key, then use a primary key to find the complete record in the clustering index, can say conducted two B + Tree search rather than a

?? All landowners. Coverage index: an index contains (cover) to query the value of the field, pay attention to the index to the specific queries about
???? If have the following table (country, city, personNumber), if we want to query the number of a city, covering the index refers to is you want to query the column can be set up in an index, such as (the number of countries, cities,) as a composite index, the search for a certain country all cities use index can be completed, in fact, this also is equivalent to complete the function of the cluster index

2. How to quickly find the record?

????? When it comes to search algorithm, the most simple traversal is arranged, in small amount of data (PS: if the data volume smaller I think there won't be so quick database development), this method also can yet be regarded as a kind of good method, after all, there is no corresponding data structure of maintenance, you can insert to update the data faster, in the books of learning algorithms, data structure, the total is also play an important role, disordered arrangement of data is a Buddha is a data structure, regardless of whether it is, a good algorithm based on a suitable data structure examples abound, heap heap sort based on the data structure; Binary search based on a build good binary search tree, hash lookup based on hash table... So in order to quickly find the need to record in the database also needs to choose the appropriate data structures,

3. What kind of data structure for as index?


The following will introduce a few kinds of quickly find records in the database data structure:

Ⅰ. B + Tree index (MySQL, SQL Server, Oracle)
B + Tree
Above for a three order B + Tree, its digital we can think that using the ID set up index of single, if you need to use the following SQL statement to query:

?? SELECT * FROM STUDENTS WHERE ID=1

?? The query statements only need three times to find can find leaf node ID is 1, find the actual orderdate deposited with ID 1 (all attributes of the students) physical address, and then find which data,

Advantages of B + Tree index
?? (1). All values match: refers to the matching with all the columns in the index, the assumption in (surname, name, date of birth) three items to establish a composite index, you can find name is zhang SAN, date of birth in the 2000-12-12
?? (2). The most left prefix matching: suppose to (surname, name, date of birth) three items to establish a composite index, you can find all zhang person
?? (3). The matching prefix: suppose you have name for Stuart, si ma, we can also find the prefix is part of the first column, such as find all begin with department of people's last name
?? (4). Matching range values: you can find all between li and zhang family name, note range queries only in the first column of the prioritization of composite index, (assuming the names sorted by pinyin)
?? (5). An exact match a column after column and range in front of the match: can find lee and date of birth in the 2000-12-12 person or name for zhang SAN and date of birth in the 2000-12-12 people, pay attention to the scope of the first range queries the back of the column can no longer use the index query
?? 6. Only access index of the query: query access index, without access to the data line, (at this point should be thought of in the index covering index)

B + Tree index shortcomings
?? (1). If not in accordance with the index of the left column lookup, then cannot use indexes, such as cannot find called dragon, also cannot find people born after 2000-12-12 s, of course also cannot find the name in the dragon at the end of the people (note the difference between for and contain)
?? (2). Can't skip in the index columns: unable to find lee and people born after the 2000-12-12
?? (3). If the query includes a column in the scope of the query, all is in the right hand column have been using the index optimization query

Ⅱ. B Tree index
B Tree

Ⅲ. Hash index (MySQL, Oracle)

A hash index
Hash index advantages
?? . Quick query: participation index field as long as after the Hash operation to quickly locate the records, time complexity is about 1

Hash index shortcomings
?? (1). The hash index contains only row pointer and the hash values, so can't use in the index values to avoid line read
?? (2). The hash index data is not stored in order according to the index value, so also cannot be used for sorting and range queries
?? (3). The hash index also does not support the part of the index columns query, because the hash index is always used to hash index columns of all data calculation,
?? (4). The hash index only support equivalence comparison query, such as=IN (), & lt;=& gt;
?? 5. If the hash collision is more, some indexes of maintenance operation cost will be higher