Simple introduction to MySQL's index type

Simple introduction to MySQL's index type

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:

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:

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

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:

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:

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

SELECT * from Myindex WHERE vc_name like ' erquan% '

will use the index, and

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.

The above mentioned is the whole content of this article, I hope you can like. 

Reference Source:
Simple introduction to MySQL's index type
Http://www.lai18.com/content/435668.html

Simple introduction to MySQL's index type