Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "MySQL single-column index and multi-column index example analysis", the content is easy to understand, clear, hope to help you solve the doubt, the following let Xiaobian lead you to study and learn "MySQL single-column index and multi-column index sample analysis" this article.

1. Single column index

During performance tuning, choosing which columns to create an index on is one of the most important steps. There are two main types of columns that you can consider using an index: columns that appear in the Where clause and columns that appear in the join clause. Take a look at the following query:

Select age # # do not use index FROM people Where firstname='Mike' # # consider using index AND lastname='Sullivan' # # consider using index

This query is slightly different from the previous query, but it is still a simple query. Because age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, it is not necessary to create an index on the age column for this query.

Here is a more complex example:

Select people.age, # # No index town.name # # No index FROM people LEFT JOIN town ON people.townid=town.townid # # consider using index Where firstname='Mike' # # consider using index AND lastname='Sullivan' # # consider using index

As in the previous example, because firstname and lastname appear in the Where clause, it is still necessary for these two columns to create an index. In addition, since the townid column of the town table appears in the join clause, we need to consider creating an index for that column.

So, can we simply assume that every column that appears in the Where clause and the join clause should be indexed? Pretty much, but not completely. We must also take into account the types of operators that compare columns. MySQL uses indexes only for the following operators: =, BETWEEN,IN, and sometimes LIKE.

An index can be used in a LIKE operation when another Operand does not start with a wildcard character (% or _).

For example:

Select peopleid FROM people Where firstname LIKE 'Mich%'

This query will use an index; however, the following query will not use an index.

Select peopleid FROM people Where firstname LIKE'% ike'

two。 Multi-column index

An index can be a single-column index or a multi-column index. Let's use specific examples to illustrate the difference between the two indexes. Suppose you have a people table like this:

Create TABLE people (peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR (50) NOT NULL, lastname CHAR (50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid))

Here is the data we inserted into the people table:

In this data fragment, there are four people with the first name "Mikes" (two surnames Sullivans and two McConnells), two people aged 17, and one with a different name, Joe Smith.

The main purpose of this table is to return the corresponding peopleid based on the specified user's last name, first name, and age. For example, we might need to find the peopleid of a 17-year-old user with the name Mike Sullivan:

Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan' AND age=17

Since we don't want MySQL to scan the entire table every time the query is executed, we need to consider using indexes here.

First, consider creating an index on a single column, such as a firstname, lastname, or age column. If we create an index on the firstname column (Alter TABLE people ADD INDEX firstname (firstname);), MySQL will quickly limit its search to those firstname='Mike' records through this index, and then search for other conditions on this "intermediate result set": it first excludes those records whose lastname is not equal to "Sullivan" and then excludes those records whose age is not equal to 17. When the record meets all the search criteria, MySQL returns the final search results.

Due to the indexing of the firstname column, MySQL is much more efficient than performing a full scan of the table, but we still require MySQL to scan far more records than we actually need. Although we can delete the index on the firstname column and then create an index on the lastname or age column, overall, the search efficiency is similar no matter which column we create an index on.

In order to improve search efficiency, we need to consider the use of multi-column indexes. If you create a multi-column index for the three columns firstname, lastname, and age, MySQL can find the correct results with only one search! Here is the SQL command to create this multi-column index:

Alter TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)

Because the index file is saved in B-tree format, MySQL can immediately go to the appropriate firstname, then to the appropriate lastname, and finally to the appropriate age. Without scanning any of the records in the data file, MySQL correctly finds the target record for the search!

So, if you create a single-column index on the three columns firstname, lastname, and age, is it the same as creating a multi-column index of firstname, lastname, and age?

The answer is no, the two are completely different. When we execute a query, MySQL can only use one index. If you have three single-column indexes, MySQL will try to choose the most restrictive index. However, even the most restrictive single-column index is certainly much less restrictive than the multi-column indexes on firstname, lastname, and age.

3. Leftmost prefix (Leftmost Prefixing) in multi-column indexes

Multi-column indexes have another advantage, which is reflected in a concept called the leftmost prefix (Leftmost Prefixing). Continuing with the previous example, we now have a multi-column index on the firstname, lastname, age column, which we call fname_lname_age. MySQL uses the fname_lname_age index when the search criteria are a combination of the following columns:

Firstname,lastname,age

Firstname,lastname

Firstname

On the other hand, it is equivalent to creating an index on the combination of (firstname,lastname,age), (firstname,lastname), and (firstname) columns. You can use this fname_lname_age index for all of the following queries:

Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan' AND age='17';Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan';Select peopleid FROM people Where firstname='Mike'

The following queries cannot use this fname_lname_age index:

Select peopleid FROM people Where lastname='Sullivan';Select peopleid FROM people Where age='17';Select peopleid FROM people Where lastname='Sullivan' AND age='17'; above is all the contents of the article "sample Analysis of single-column and Multi-column Indexes in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.