Index in MySQL database (ii)--use of index, leftmost prefix principle

In this article, we learned about the implementation principle of the index under different engines of MySQL, and in this paper we will continue to explore the use and optimization of indexes.

Creating an index can greatly improve the performance of your system.

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Perhaps someone will ask: there are so many advantages to adding indexes, why not create an index for each column in the table? Because there are many disadvantages to increasing the index.

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Indexes are built on top of some columns in a database table. When you create an index, you should consider which columns you can create indexes on and which columns you cannot create indexes on. In general, indexes should be created on these columns: on columns that are frequently searched, you can speed up the search, enforce the uniqueness of the column on the column that is the primary key, and arrange the structure of the data in the organization table; These columns are often used on connected columns, which are mostly foreign keys, to speed up the connection Create an index on a column that often needs to be searched by scope, because the index is sorted, its specified range is contiguous, and the index is created on columns that are often ordered, because the index is sorted so that the query can take advantage of the sorting of the index to speed up the sort query time To speed up the judgment of a condition by creating an index on a column that is often used in the WHERE clause.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics:

First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

Third, for those columns defined as text, the image and bit data types should not be indexed. This is because the amount of data in these columns is either quite large or has very little value.

The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting . When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

Let's take a look at the classification of indexes, which can be broadly categorized as follows:

From the data structure angle

1. B + Tree index (O (log (n))): for the B + Tree index, you can refer to the data structure behind the MySQL index and the algorithm principle

2. Hash index:

A. can only satisfy "=", "in" and "<=>" query, can not use range query
B. Its retrieval efficiency is very high, index retrieval can be located at once, unlike the B-tree index need from the root node to the side point, and finally access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index
C. Only memory storage engine Display Support hash index

From the physical storage perspective

1. Clustered index (clustered)

2. Nonclustered indexes (non-clustered index)

From a logical point of view

1, ordinary index or single-column index

2. Unique index

3. Primary KEY index: Primary key index is a special unique index, no null value allowed

4. Multi-column index (composite index): A composite index is an index created on multiple fields, and the index is used only if the first field when the index is created is used in the query criteria. To follow the leftmost prefix collection when using a composite index

5. Full-Text Indexing

In the above, we have seen the data structure and the physical storage angle, today we mainly look at the index from a logical point of view:

1, normal index: This is the most basic type of index, and it does not have uniqueness and other restrictions. Normal indexes can be created in the following ways:
Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));

2. Unique index: This index is basically the same as the previous "normal index", but there is one difference: all the values of the indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:
Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns);
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns));

3. Primary KEY index

The primary key is a unique index, but it must be specified as "PRIMARY key". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when creating the table, such as "CREATE TABLE TableName ([...], PRIMARY KEY (List of columns)"; ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.

4. Composite index (combined index, multi-column index)

To visually compare single-column and composite indexes, add multiple fields to the table:

CREATE TABLE INT  not NULL VARCHAR (nonullVARCHAR(notnullINT  not NULL );

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age);

When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than 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, in fact, is equivalent to establishing the following three sets of MySQL database index:

Usernname,city,age usernname,city Usernname Why not city,age such a combination index? 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. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined MySQL database index:

SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou"

SELECT * FROM MyTable whree username= "admin"

And the next few are not used:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

On the question of the leftmost prefix, we will discuss the following

5. Full-Text Indexing

MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index. The following discussion in this article no longer involves full-text indexing, see MySQL full-text indexing for more information.

The leftmost prefix principle

MySQL establishes a multi-column index (federated Index) with the principle of the leftmost prefix, that is, the leftmost priority, such as:

If there is an index of 2 columns (COL1,COL2), an index has been established on (col1), (col1,col2);
If there is a 3-column index (COL1,COL2,COL3), an index has been established on (col1), (Col1,col2), (COL1,COL2,COL3);

1, B + Tree data item is a composite data structure, such as (Name,age,sex), B + Tree is based on the order from left to right to establish a search tree, such as when (Zhang San, 20,f) such data to retrieve, B + Tree will first compare name to determine the next direction of the search, If name is the same, then compare age and sex, and finally get the retrieved data, but when the (20,F) does not have the name of the data, the B + tree does not know which node to look at the first step, because the search tree when the name is the first comparison factor, You must search by name first to know where to go next.

2, such as when (Zhang San, F) such data to retrieve, B + tree can use name to specify the direction of the search, but the next field of age is missing, so only the name equals Zhang San data are found, and then match the gender is the data of F, this is a very important property, that is, the index of the leftmost matching characteristics. (Federated index is not available in this case)

The use of the leftmost prefix has the following two notes:

• The leftmost prefix matching principle, very important principle, MySQL will always match right until it encounters a range query (>, <, between, like) to stop the match, such as a = 1 and B = 2 and C > 3 and D = 4 If set (A,b,c,d) Shun The index of the order, D is not indexed, if the establishment (A,B,D,C) index can be used, a,b,d order can be arbitrarily adjusted.
• = and in can be disorderly, such as a = 1 and B = 2 and c = 3 build (a,b,c) index can be arbitrary order, MySQL query optimizer will help you to optimize the form of the index can be recognized

For an example of the leftmost prefix, please refer to: https://www.kancloud.cn/kancloud/theory-of-mysql-index/41857

When will the index expire?

1. If there is or in the condition, it will not be used even if there is a conditional index ( which is why it is possible to use or less .) Note: If you want to use or, and you want the index to take effect, you can only index each column in the OR condition
2. For multi-column indexes, not the first part of the use, the index is not used (that is, does not conform to the leftmost prefix principle)
3. The like query starts with a%
4. If the column type is a string, be sure to use quotation marks in the condition to reference the data, otherwise you will not use the index
5. If MySQL estimates that using a full table scan is faster than using an index, the index is not used

Also, view the usage of the index

Show status like ' handler_read% ';
We can note:
Handler_read_key: The higher the value the better, the higher the number of times the index is queried Handler_read_rnd_next: The higher the value, the less efficient the query

Index in MySQL database (ii)--use of index, leftmost prefix principle