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

This article introduces the relevant knowledge of "the basic concepts of MYSQL index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

What is an index?

Indexes are used to quickly find records with specific values, and all MySQL indexes are saved in the form of a B-tree. If there is no index, MySQL must scan all records of the entire table from the first record when executing the query until a record that meets the requirements is found. The more records there are in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly find the location of the target record without scanning any records. If the table has 1000 records, finding records by index is at least 100 times faster than scanning records sequentially.

Suppose we create a table called people:

CREATE TABLE people (peopleid SMALLINT NOT NULL, name CHAR (50) NOT NULL)

Then we randomly insert 1000 different name values into the people table. There is no clear order for name columns in the data file. If we create an index on the name column, MySQL will sort the name column in the index, and for each item in the index, MySQL internally keeps a "pointer" to the location of the actual record in the data document. Therefore, if we want to find the peopleid of the record with name equal to "Mike" (the SQL command is "SELECT peopleid FROM people WHERE name=Mike;"), MySQL can look for the "Mike" value in the index of name, and then go directly to the corresponding row in the data file, returning the peopleid of that row exactly. In this process, MySQL only needs to process one row to return the result. If there is no index for the "name" column, MySQL scans all records in the data file, that is, 1000 records! Obviously, the fewer records that need to be processed by MySQL, the faster it will complete the task.

II. Type of index

MySQL provides several index types to choose from:

General Index:

This is the most basic index type, and it has no uniqueness or the like. A normal index can be created in the following ways:

Create an index, such as CREATE 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 index] (list of columns))

Uniqueness index:

This index is basically the same as the previous "normal index", but there is one difference: all values of the index column can only appear once, that is, they must be unique. Uniqueness indexes can be created in the following ways:

Create an index, such as CREATE UNIQUE INDEX ON tablename (list of columns)

Modify the table, such as ALTER TABLE tablename ADD UNIQUE [name of index] (list of columns)

Specify an index when creating a table, such as CREATE TABLE tablename ([...], UNIQUE [name of index] (list of columns))

Primary key:

The primary key is a unique index, but it must be specified as "PRIMARY KEY". If you have ever used AUTO_INCREMENT-type columns, you may already be familiar with concepts such as primary keys. The primary key is generally specified when the table is created, 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);". Each table can have only one primary key.

Full-text index:

MySQL supports full-text indexing and full-text retrieval since 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 column of type VARCHAR or TEXT. It can be created by the CREATE TABLE command, or by the ALTER TABLE or CREATE INDEX command. For large datasets, creating a full-text index through the ALTER TABLE (or CREATE INDEX) command is faster than inserting records into an empty table with a full-text index. The following discussion in this article no longer deals with full-text indexing; for more information, see MySQL documentation.

Single-column index and 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 named Mike Sullivan (the SQL command is 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 search for firstname=Mike records through this index, and then search for other conditions on this "intermediate result set": it first excludes 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 three columns, firstname, lastname, and age, My

SQL only needs to search once to find the correct results! 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 firstname, lastname, and age, will it have the same effect 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 stringent index. However, even the strictest single-column index is certainly much less powerful than the multi-column indexes on firstname, lastname, and age.

Fourth, leftmost prefix

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,agefirstname,lastnamefirstname

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 the index at all: SELECT peopleid FROM people WHERE lastname=Sullivan; SELECT peopleid FROM people WHERE age=17; SELECT peopleid FROM people WHERE lastname=Sullivan AND age=17

5. Select index column

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 for 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 ONpeople.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.

This is the end of the content of "basic Concepts of MYSQL Index". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.