What are the indexes in the MySQL database?

This article mainly describes how to accelerate the Dynamic web site of MySQL index analysis and optimization.

I. What is an index?

Indexes are used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records 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 get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.

Let's say we've created a table named people:

CREATE TABLE people (Peopleid SMALLINT not NULL,

Name CHAR (not NULL);

Then, we completely randomly insert 1000 different name values into the People table. The Name column does not have any explicit order in the data file. If we create the index of the name column, MySQL will sort the name column in the index, and for each item in the index, MySQL internally holds a pointer to the location of the actual record in the data file. So if we want to find the name equals "Mike" Record Peopleid (The SQL command is "Select Peopleid from people WHERE Name= ' Mike ';"), MySQL is able to find the "Mike" value in the index of name , and then go directly to the corresponding line in the data file, returning exactly the line's Peopleid (999). In this process, MySQL only has to process one row to return the results. If there is no index to the "name" column, MySQL scans all records in the data file, that is, 1000 records! Obviously, the fewer records that require MySQL to process, the faster it will complete the task.

Second, the type of the index www.2cto.com

MySQL offers a variety of index types to choose from:

Normal index:

This is the most basic type of index, and it has no limitations such as uniqueness. 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));

Uniqueness Index:

This index is basically the same as the previous "normal index", but there is one difference: all the values of an 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));

Primary key:

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.

Full-text index:

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 documentation for more information.

Third, single-row index and multi-column index

The index can be a single-column index or a multicolumn index. Let's take a concrete example to illustrate the differences between the two indexes. Suppose there is such a people table:

CREATE TABLE People (Peopleid

SMALLINT not NULL auto_increment,

FirstName char () NOT NULL, LastName char (.) is not NULL,

Age SMALLINT is not NULL,

Townid SMALLINT not NULL, PRIMARY KEY (Peopleid));

Here is the data we inserted into this people table:

The data fragment contains four people named "Mikes" (two of them Sullivans, two surname McConnells), two are 17 years of age, and a different name for Joe Smith.

The primary purpose of this table is to return the corresponding Peopleid based on the specified user name, first name, and age. For example, we may need to find the Peopleid (SQL command for select Peopleid from people WHERE Firstname= ' Mike ' and Lastname= ' for the user who is named Mike Sullivan, who is 17 years of age Sullivan ' and age=17;). Since we don't want MySQL to scan the entire table every time it executes a query, we need to consider using an index.

First, we can consider creating an index on a single column, such as FirstName, LastName, or the Age column. If we create an index of the FirstName column (ALTER TABLE people add index FirstName (firstname);), MySQL will quickly limit the search to those firstname= ' Mike ' records through this index , and then search for other criteria on this "intermediate result set": It first excludes records whose lastname are not equal to "Sullivan", and then excludes records whose age is not equal to 17. When the record satisfies all search criteria, MySQL returns the final search results.

Because of the FirstName column index, MySQL is much more efficient than performing a full scan of the table, but we require that the number of logs scanned by MySQL still far exceeds what is actually needed. Although we can delete the index on the FirstName column, and then create an index of the LastName or age column, it seems that the efficiency of creating an index search is still similar, regardless of which column.

To improve search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for the three columns of FirstName, LastName, and age, MySQL can find the correct results in just one search! Here is the SQL command to create this multicolumn index:

ALTER TABLE People ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

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.

Four, the 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

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

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 @ 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 ';

V. Select an indexed column

In the performance optimization process, choosing which columns to create indexes on is one of the most important steps. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause. Consider the following query:

SELECT Age # # does not use an index

From people WHERE firstname= ' Mike ' # # consider using an index

and Lastname= ' Sullivan ' # # consider using an index

This query is slightly different from the previous query, but it still belongs to 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 of the age column for this query. The following is a more complex example:

SELECT People.age, # #不使用索引

Town.name # #不使用索引

From people left JOIN

People.townid=town.townid # #考虑使用索引

WHERE firstname= ' Mike ' # #考虑使用索引

and Lastname= ' Sullivan ' # #考虑使用索引

As in the previous example, because FirstName and LastName appear in the WHERE clause, these two columns still have the necessary to create an index. In addition, because the Townid of the town table is listed in the JOIN clause now, we need to consider creating the index of the column. So, can we simply assume that each column that appears in the WHERE clause and the JOIN clause should be indexed? Almost so, but not entirely. We also have to take into account the type of operator that compares the columns. MySQL uses the index only for the following operators: <,<=,=,>,>=,between,in, and sometimes like. The case in which you can use an index in a like operation is when another operand is not preceded by a wildcard character (% or _). For example, "Select Peopleid from people WHERE firstname like ' mich% ';" This query will use the index, but "select Peopleid from people WHERE firstname like '%ike ';" This query does not use the index

First, introduce the type of index

MySQL Common indexes are: Primary key index, unique index, normal index, full-text index, composite index
PRIMARY key (primary key index) ALTER TABLE ' table_name ' Add PRIMARY KEY (' column ') unique (unique index) ALTER TABLE ' table_name ' Add unique ( ' Column ')
Index (normal index) ALTER TABLE ' table_name ' Add INDEX index_name (' column ') fulltext (full-text index) ALTER TABLE ' table_name ' Add Fulltex T (' column ')
Combined index ALTER TABLE ' table_name ' ADD index index_name (' Column1 ', ' column2 ', ' column3 ')

MySQL various index differences:

Normal index: The most basic index, without any restrictions
Unique index (unique): Similar to "normal index", the difference is that the value of the indexed column must be unique, but allow a null value.
Primary key index (PRIMARY): It is a special unique index and is not allowed to have null values.
Full-text index (FULLTEXT): Available only for MyISAM tables, for retrieving textual information in an article, generating a full-text index for large data is a time-consuming space.
Combined index: For more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

For example, for example, you are making a membership card system for a mall.
This system has a membership table
The following fields are available:
Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT

Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the normal index
Member ID number if you want to index, then you can choose unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.

Fulltext, however, works best when it comes to searching for a long post.
Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.

Creating an index: Create UNIQUE index indexname on tableName (tablecolumns (length))

Syntax for dropping an index: Drop index index_name on TableName

Second, index single-column index and composite index

Single-column index: That is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index.
Composite index: That is, a cable contains multiple columns.

To visually compare the two, build a table:

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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 butterflies 5 vc_name= "Erquan" records, but the city,age,school of the combination of different.
Look at this T-sql:

Copy the code as follows: SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

First consider building a 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 efficiency of a single-column index established separately in vc_city and I_age is similar.

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);--note that when the table is built, the vc_name length 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 combined index efficiency? Hey, big different, far below our portfolio index ~ ~ Although there are three indexes at this time, MySQL can only use one of the single-column indexes which it considers to be the most efficient.

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

?

1 2 3

Vc_name,vc_city,i_age vc_name,vc_city Vc_name

Such a combination of three indexes! 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:

?

1 2	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:

?

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

Iii. Use of indexes

Should you build and use the index here? But under what circumstances do you need to index it? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like (explained later).
SELECT t.vc_name from Testindex T left joins Myindex m on T.vc_name=m.vc_name WHERE m.i_age=20 and m.vc_city= ' Zhengzhou ' when there is a pair of myind The vc_city and i_age of the ex table need to be indexed, since the vc_name of the Testindex table is now in the join clause and it is necessary to index it.

Just now, you only need to index the like at some point? Yes. Because MySQL does not use indexes when querying with wildcards% and _, such as

?

1	SELECT * from Myindex WHERE vc_name like ' erquan% '

will use the index, and

?

1	SELECT * from Myindex wheret vc_name like '%erquan '

The index is not used.

Iv. shortcomings of the index

It says so many good words in the index, does it really have as great as the legend? Of course there will be shortcomings.

1. Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file

2. index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.

End of article:

So much is said to use indexes to improve the efficiency of database execution. But indexing is just one factor in improving efficiency. If your MySQL has big data tables, you'll need to spend time studying to build the best indexes or refine query statements.

What are the indexes in the MySQL database?