Introduction to the creation and differentiation of MySQL single-column indexes and composite indexes

MySQL single-column index is what we often see in MySQL database, the difference between MySQL single-column index and composite index may be a lot of people do not know very well, the following for you to analyze the main differences, for your reference to learn.

To visually compare the two, build a table:

CREATE TABLE Myindex ( I_testid INT not NULL auto_increment, Vc_name VARCHAR () not NULL, Vc_city VARCHAR () not NULL, I_age INT not NULL, I_schoolid INT not NULL, PRIMARY KEY (I_testid) );

In these 10,000 records, 7 of the 8 are distributed in 5 vc_name= "Erquan" records, but City,age,school combinations are different.

Look at this T-sql:

SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

First consider building a MySQL single-column index:

An index was established on the Vc_name column. When executing T-SQL, MYSQL quickly locks the target on the 5 records of Vc_name=erquan and takes it out to a middle result set. In this result set, the first rule out vc_city not equal to "Zhengzhou" record, and then exclude i_age not equal to 25 of the record, and finally filtered out the only qualified records.

Although the index is built on the vc_name, MySQL does not have to scan the whole table when querying, but the efficiency is improved, but there is a certain distance from our request. Similarly, the MySQL single-column indexes established separately in vc_city and i_age are similar in efficiency.

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build the Vc_name,vc_city,i_age into an index:

ALTER TABLE myindex ADD INDEX name_city_age (Vc_name (Ten), vc_city,i_age);

When building a table, the length of the vc_name is 50, why use 10 here? Because the length of the name does not typically exceed 10, this speeds up the index query, reduces the size of the index file, and increases the update speed of the INSERT.

When executing T-SQL, MySQL does not need to scan any records to find a unique record.

There must be someone to ask, if you set up a single-column index on the vc_name,vc_city,i_age, so that the table has 3 single-column index, query and the above combination index efficiency? Very different, far below our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index is actually equivalent to establishing a separate

(Vc_name,vc_city,i_age) (vc_name,vc_city) (Vc_name)

Such a three combination index! Why is there no such combination index as vc_city,i_age? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. It is not just that the combined index is used for queries that contain these three columns, and several of the following T-SQL is used:

SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou" SELECT * from Myindex whree vc_name= "Erquan"

And the next few are not used:

SELECT * from Myindex whree i_age=20 and vc_city= "Zhengzhou" SELECT * from Myindex whree vc_city= "Zhengzhou"

1. Why use an index

In the absence of an index, MySQL scans the entire table to find records that meet the SQL criteria, and its time overhead is positively correlated with the amount of data in the table. Jianjian indexes on certain words in a relational data table can greatly improve the query speed (of course, whether different fields are selective will cause the indexes of these fields to raise the query speed differently, and the index is not the more the better, because the index information needs to be updated when writing or deleting).

For MySQL's InnoDB storage engine, most types of index are stored in variant B+tree of the B-tree data structure (memory type tables also support hash type indexes). B-tree is a database or file system commonly used in a data structure, it is a kind of n-fork balance tree, this tree structure to ensure that the same layer of nodes to save the order of key, for a node, the left subtree of all keys are smaller than the key saved by the node, All keys saved by its right subtree are larger than the key saved by that node. In addition, in the implementation of the project, but also with the operating system of the local principle to do a lot of optimization, in short, b-tree of various characteristics or optimization techniques can guarantee: 1) query disk records, read the minimum number of times; 2) any insert and delete operations have little effect on the tree structure; 3) The rebalance operation of the tree itself is efficient.

2. mysql uses indexed scenarios

MySQL uses the index in the following scenario:
1) quickly find records that meet the Where condition
2) quickly identify candidate sets. If the Where condition uses more than one indexed field, MySQL takes precedence over the index that makes the candidate recordset the smallest, so that the non-qualifying record can be eliminated as quickly as possible.
3) If there are several fields in the table that make up a federated index, the leftmost prefix match field of the Federated index will also be automatically indexed to speed up the lookup when the record is found.
For example, if you create a union index that consists of 3 fields (c1, C2, C3) for a table, then (C1), (c1, C2), (c1, C2, C3) are indexed, (C2, C3) are not indexed, and (C1, C3) are actually only used to C1 indexes.
4) Indexes are used when multiple tables do join operations (if the fields participating in the join are indexed in these tables)
5) If a field is indexed, MySQL uses the index when the field's min () or MAX () is evaluated
6) When you do a sort or group operation on an indexed field, MySQL uses the index

3. Which SQL statements will actually take advantage of the index
From the MySQL official website document "Comparison of B-tree and Hash Indexes", the following types of SQL may actually be used for indexing:

1) B-tree can be used in SQL to compare columns to the expression, such as =,,, >=, <, <= and between operations

2) If the condition of the like statement is a constant string that does not begin with a wildcard, MySQL will also use the index
For example, select * from Tbl_name where key_col like ' patrick% ' or select * from Tbl_name where key_col as ' pat%_ck% ' can take advantage of the index while the Select * from Tbl_name where key_col like '%patrick% ' (starting with wildcards) and select * from Tbl_name WHERE key_col like Other_col (like condition is not a constant string) cannot Use the index.
For SQL statements that look like '%string% ', if the string after the wildcard is longer than 3, MySQL uses the turbo Boyer-moore algorithm algorithm to find it.

3) If the column named Col_name has been indexed, SQL that is shaped as "col_name is null" uses the index

4) for a federated index, the leftmost prefix match field in the SQL condition is used for the index, and the example refers to the description of the Federated index in section 2nd, article 3rd

5) If the Where condition in the SQL statement is not more than 1 conditions, MySQL will perform an index merge optimization to narrow the candidate set range

(7) Considerations for using Indexes
There are some tips and considerations when working with indexes:
The index does not contain a column with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
Use short Index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
Indexed column Sorting
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
Like statement operations
It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
Do not perform calculations on columns
SELECT * from the users where year (adddate) <2007;
The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to
SELECT * from users where adddate< ' 2007-01-01 ';
Do not use not in and <> operations
Reference: Http://zhidao.baidu.com/link?url=xF1BrmM9CNSE_ptqmRcEZQgTHIw5UK7WbMI_iAOMWzYS8MdNYbVMrpv820wzn6nxlkpyx2JAH_ oxmaqfbfr32k

Introduction to the creation and differentiation of MySQL single-column indexes and composite indexes