Brief introduction to MYSQL index type _ MySQL

This article describes seven MySQL index types. Index fields in database tables can greatly improve the query speed. By making good use of these indexes, you can make MySQL Query and operation more efficient. I. INTRODUCTION to index types

Mysql common indexes include: primary key index, unique index, common index, full-text index, and 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 (common INDEX) alter table 'Table _ name' add index index_name ('column ') FULLTEXT (full-text INDEX) ALTER table 'Table _ name' add fulltext ('column ')
Alter table 'Table _ name' add index index_name ('column1 ', 'column2', 'column3 ')

Differences between various indexes in Mysql:

Common INDEX: the most basic INDEX with no restrictions
UNIQUE index: similar to "normal index", the difference is that the index column value must be UNIQUE, but null values are allowed.
PRIMARY key index (PRIMARY): It is a special unique index and does not allow null values.
Full-text index (FULLTEXT): it can be used only for MyISAM tables. it is very time-consuming to generate full-text indexes for retrieving text information in an article.
Composite Index: to improve mysql efficiency, you can create composite indexes following the "leftmost prefix" principle.

For example, you are creating 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 number VARCHAR (10)
Member address VARCHAR (50)
Member remarks TEXT

The member number is used as the PRIMARY key and PRIMARY is used.
If you want to create an INDEX for the member name, it is a normal INDEX.
If you want to create an index for the member ID card number, you can select UNIQUE (UNIQUE, not allowed to be repeated)
Member remarks. if you need to create an index, you can select FULLTEXT for full-text search.

However, when FULLTEXT is used to search for a long article, it is the best.
It is used in short text. if there are only one or two lines of text, the normal INDEX is also acceptable.

Create index: create unique index indexName ON tableName (tableColumns (length ))

Syntax for deleting an INDEX: drop index index_name ON tableName

2. index is divided into single-column indexes and composite indexes

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

To visually compare the two, create another table:

CREATE TABLE myIndex (i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(50) NOT NULL,vc_City VARCHAR(50) NOT NULL,i_Age INT NOT NULL,i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID));

Five vc_Name = "erquan" records are distributed in the 10000 records, except that the combination of city, age, and school is different.
Look at this T-SQL:

The code is as follows:

SELECT I _testID FROM myIndex WHERE vc_Name = 'erquanc' AND vc_City = 'zhengzhou 'AND I _Age = 25;

First, create a single column index:

An index is created on the vc_Name column. When the T-SQL was executed, MYSQL quickly locked the target to five records in vc_Name = erquan and pulled them out to the intermediate result set. In this result set, records with vc_City not equal to "Zhengzhou" are excluded, records with I _Age not equal to 25 are excluded, and a unique matching record is filtered out.

Although an index is created on vc_Name, MYSQL does not need to scan the entire table during query, which improves the efficiency, but there is still some distance from our requirements. Similarly, the efficiency of single-column indexes created in vc_City and I _Age is similar.

To further extract MySQL efficiency, you must consider establishing a composite index. Vc_Name, vc_City, and I _Age are built into an index:
Alter table myIndex add index name_city_age (vc_Name (10), vc_City, I _Age); -- note that when creating a TABLE, the length of vc_Name is 50. why is 10 used here? In general, the length of the name will not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.

When you run the T-SQL, MySQL finds a unique record without scanning any record !!

Someone must ask, if you create a single column index on vc_Name, vc_City, and I _Age respectively, so that the table has three single column indexes, the query efficiency is the same as that of the preceding Composite Index? Hey, big difference, far lower than our combined index ~~ Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.

The establishment of such a composite index is actually equivalent to the establishment

    vc_Name,vc_City,i_Age    vc_Name,vc_City    vc_Name

These three composite indexes! Why are there no composite indexes such as vc_City and I _Age? This is because mysql composite index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. Not as long as the query contains these three columns will use the composite index, the following T-SQL will use:

SELECT * FROM myIndex WHREE vc_Name = "erquan" AND vc_City = "Zhengzhou" SELECT * FROM myIndex WHREE vc_Name = "erquan"

The following are not used:

SELECT * FROM myIndex WHREE I _Age = 20 AND vc_City = "Zhengzhou" SELECT * FROM myIndex WHREE vc_City = "Zhengzhou"

III. use indexes

So far, should you create and use indexes? But under what circumstances do indexes need to be created? IN general, you need to create an index for the columns that appear IN the WHERE and JOIN operations, but this is not the case because MySQL only applies to <, <=, =,>,> =, BETWEEN, IN, and sometimes LIKE (as described later) will use the index.
SELECT t. vc_Name FROM testIndex t left join myIndex m ON t. vc_Name = m. vc_Name WHERE m. I _Age = 20 AND m. when vc_City = 'zhengzhou ', you need to create an index for the vc_City and I _Age of the myIndex table. because the vc_Name of the testIndex table is opened in the JOIN clause, it is also necessary to create an index for it.

As I mentioned earlier, only LIKE needs to be indexed in some cases? Yes. Because MySQL does not use an index when it starts with the wildcard "%" and "_", as shown in

  SELECT * FROM myIndex WHERE vc_Name like'erquan%'

Will use the index, and

  SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

The index will not be used.

IV. index limitations

So many good words about indexes are mentioned above. Is it really as good as the legend? Of course, there will be disadvantages.

1. although the index greatly improves the query speed, it also reduces the speed of updating the table, such as performing INSERT, UPDATE, and DELETE operations on the table. When updating a table, MySQL not only needs to save data, but also stores the index file.

2. index files that occupy disk space when an index is created. This problem is not serious in general, but if you create multiple composite indexes on a large table, the index file will expand very quickly.

At the end of the article:

After talking about this, I just want to use indexes to improve the efficiency of database execution. However, indexing is only a factor to improve efficiency. If your MySQL has big data tables, you need to spend time researching and creating the best indexes or optimizing query statements.

The above is all the content of this article. I hope you will like it.