How to use an index in MySQL for more detailed analysis and examples _ MySQL

How to use indexes in MySQL for detailed analysis and examples can greatly improve the query speed by using indexes in database tables. Suppose we have created a testIndex table:

Create table testIndex (I _testID INT NOT NULL, vc_Name VARCHAR (16) NOTNULL );

We randomly inserted 1000 records into the table, one of which is I _testID vc_Name 555 erquan.

When querying the record SELECT * FROM testIndex WHERE vc_Name = "erquan"; for vc_Name = "erquan", MySql does not need to perform any scans if an index has been created on vc_Name, this record can be found accurately! On the contrary, MySql scans all records to query 1000. The query speed is increased by 100 times by indexing.

I. 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.

II. introduce the index type

1. common indexes.

This is the most basic index with no restrictions. It can be created in the following ways:

(1) CREATE an INDEX: create index indexName ONtableName (tableColumns (length); for CHAR and VARCHAR types, the length can be smaller than the actual length of the field; for BLOB and TEXT types, length must be specified, the same below.

(2) modify the table structure: ALTER tableName add index [indexName] ON (tableColumns (length ))

(3) when creating a TABLE, specify: create table tableName ([...], INDEX [indexName] (tableColumns (length ));

2. unique index.

It is similar to the previous "normal Index". The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:

(1) CREATE an INDEX: create unique index indexName ONtableName (tableColumns (length ))

(2) modify the table structure: ALTER tableName add unique [indexName] ON (tableColumns (length ))

(3) when creating a TABLE, specify: create table tableName ([...], UNIQUE [indexName] (tableColumns (length ));

3. primary key index

It is a special unique index and does not allow null values. Create table testIndex (I _testID int not null AUTO_INCREMENT, vc_NameVARCHAR (16) not null, primary key (I _testID )); of course, you can also use the ALTER command. Remember: a table can only have one primary key.

4. full-text index

MySQL supports full-text indexing and full-text retrieval from version 3.23.23.

Syntax for deleting an INDEX: drop index index_name ON tableName

III. single-column index and Composite Index

To visually compare the two, create another table:

Create table myIndex (I _testID int not null AUTO_INCREMENT, vc_NameVARCHAR (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 top 7 and bottom 8 of the 10000 records, except that the combinations of city, age, and school are different.

Look at this T-SQL: SELECT I _testID FROM myIndex WHEREvc_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 );

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, will the query efficiency be the same as that of the preceding composite indexes? The difference is 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" ANDvc_City = "Zhengzhou"
SELECT * FROM myIndex WHREEvc_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"

4. using 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 ONt. 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. MySQL does not use indexes when querying with wildcards % and _, for example, SELECT * FROM myIndex WHERE vc_Name like 'erquan %'
The index will be used, and SELECT * FROM myIndex WHEREt vc_Namelike '% erquan' will not be used.

V. index deficiency

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.

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.