Atitit. Query principle and design implementation of multiple criteria for single-row index and multi-column index

Atitit. Query principle and design implementation of multiple criteria for single-row index and multi-column index

1 .  MySQL can use only one index 1

1 . 1. Leftmost prefix 1

.2.&NBSP, (FirstName lastname age) , Span style= "Font-family:times New Roman;" >lastname) ( FirstName)

2 .  One SQL can use multiple indexes 2

2 . 1. What is an index merge 2

2 . 2. What are the benefits of using index merging? 3

2 . 3. the specific index merge process 4

3 .  referred, 4

1.MySQL can use only one index

So, if you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age? The answer is no, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.

Author:: Old Wow's paw attilax Ayron, email:[email protected]

Reprint please indicate source: http://www.cnblogs.com/attilax/

1.1.Leftmost prefix

A Multi-column index has another advantage, which is manifested by the concept of the leftmost prefix (leftmost prefixing). Continuing to consider the previous example, we now have a multi-column index on the firstname,lastname,and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns,MySQL uses the fname_lname_age index:

* FirstName,lastname, Age
* FirstName,lastname
* FirstName

1.2.on the other hand, it is equivalent to what we created (FirstName,lastname,age),( FirstName,LastName) and (FirstName) The indexes on these column combinations.

Four

Five

2.One SQL can use multiple indexes

MySQL's index merge is not a new feature. Already implemented as early as the mysql5.0 version. The reason I wrote this blog post is because so many people still keep a SQL statement can use only one index of the wrong idea. This article uses some examples to illustrate how to use index merging.

2.1.What is an index merge

below we look at  mysql  Description of the index merge in the document:
the index merge  Method is used to retrieve rows with several range scans and  to merge their results into one. the merge can produce  unions, intersections, or unions-of-intersections of its underlying  scans. this access method merges index scans from a single  Table; it does not merge scans across multiple tables.

According to the instructions in the official documentation, we can learn:

1.

Index merging is the merging of the range scans of several indexes into one index.

2.

3.

When an index is merged, the indexes are combined, intersected, or intersected together to combine them into one index.

4.

5.

The indexes that need to be merged can only be one table. Multiple tables cannot be indexed for merging.

6.

2.2.What are the benefits of using index merging?

Simply put, the index is merged so that one SQL can use multiple indexes. Take the intersection of these indexes, set them , or fetch the intersection first and then the set. This reduces the number of data taken from the data table and improves query efficiency.

In the explain extra field, you will get the following:

·

Using Union Index to set

·

·

Using Sort_union Sorts the extracted data first by rowid and then takes the set

·

·

Using intersect index intersection

·

2.3.the specific index merge process
3.referred,

Multiple criteria for query indexing is the number of fields that establish an index or also each field is defined as an index _-CSDN forum-csdn.net-The largest IT technology community in China. html

MySQL index merging one SQL can use multiple indexes-sohu-dba-Micro Headlines (wtoutiao.com). htm

Atitit. Query principle and design implementation of multiple criteria for single-row index and multi-column index