A brief description of the Mysql index type _mysql

First, introduce the type of index

MySQL Common indexes are: Primary key index, unique index, normal index, FULL-TEXT index, combined 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 FU Lltext (' column ')
Composite 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, except that the value of an indexed column must be unique, but a null value is allowed.
Primary key index (PRIMARY): It is a special unique index and does not allow null values.
Full-Text Indexing (fulltext): Available only for MyISAM tables, for retrieving text information in an article, generating full-text indexing for larger data is a time-consuming space.
Combined index: For more MySQL efficiency, you can establish a composite index, followed by the "leftmost prefix" principle.

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

So this membership number, as the primary key, uses PRIMARY
The name of the member if you want to build an index, then it is the normal index
Member ID number if you want to build an index, then you can choose unique (unique, do not allow duplicates)
Member note information, if you need to build the index, you can choose Fulltext, Full-text search.

But Fulltext is the best when it comes to searching for a long article.
Used in relatively short text, if the one or two lines of words, the normal INDEX can also.

Create an index: Create UNIQUE index indexname on tablename (tablecolumns (length))

Syntax for deleting an index: Drop index index_name on tablename

Index Single-column index and combined index

Single-column Index: An index contains only a single column, and a table can have multiple single-column indexes, but this is not a combined index.
Combined index: A cord contains multiple columns.

To visually contrast the two, build a table:

CREATE TABLE myindex (

i_testid INT not null auto_increment,

vc_name VARCHAR (x) not NULL,

vc_city VARCHAR (50 Not NULL,

i_age int is not NULL,

i_schoolid int is not NULL, PRIMARY KEY (I_testid)

);

In these 10,000 records, butterflies distribution of 5 vc_name= "Erquan" records, but City,age,school combinations are different.
Look at this T-sql:

Copy Code code as follows:

SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

First, consider a single-column index:

An index was established on the Vc_name column. When you execute T-SQL, MySQL quickly locks the target on the 5 records of Vc_name=erquan, taking it out and putting it in an intermediate 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 records, finally filtered out the only eligible records.

Although the index is established on the Vc_name, the query MySQL does not need to scan the entire table, 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 squeeze MySQL's efficiency, consider establishing 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, vc_city,i_age);--notice, when the table is built, the vc_name length is 50, why use 10 here? Because the name does not normally exceed 10 in length, this will speed up the indexing query, reduce the size of the index file, and increase the update speed of the insert.

When you execute 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 indexes, the query and the same combination index efficiency? Hey, big different, far less than our combined index ~ ~ Although there are three indexes at this time, but MySQL can only use the one that it seems to be the most efficient Single-column index.

The establishment of such a composite index, in fact, is equivalent to the establishment of a separate

    Vc_name,vc_city,i_age
    vc_name,vc_city
    vc_name

Such a combination of three index! Why not have such a combination index such as vc_city,i_age? This is because the MySQL composite index "leftmost prefix" results. The simple understanding is to just start from the left. Instead of using the combined index for queries that contain all three columns, the following T-SQL uses:

  SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou"
  select * from Myindex whree vc_name= "Erquan"

The following 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 be able to build and use the index? But under what circumstances should an index be built? In general, the columns that appear in the where and join need to be indexed, but not exactly, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like (followed by a description).
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.vc_city= ' Zhengzhou ' when there is a pair of Myin The need to index the vc_city and i_age of the Dex table is necessary because the vc_name of the Testindex table is out of the join clause.

Just now, there are times when the like needs to be indexed? Yes. Because MySQL does not use indexes when querying with wildcard% 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. deficiencies of the index

Is it true that there are so many good words in the index? Of course, there will be shortcomings.

1. Although indexing greatly improves query speed, it reduces the speed of updating tables, such as INSERT, UPDATE, and delete tables. Because MySQL will not only save the data, but also save the index file when updating the table

2. index files that create an index that consumes disk space. Generally this is not a serious problem, but if you create multiple combinations of indexes on a large table, the index files will swell up very quickly.

End of article:

So much to say is to use the index to improve the efficiency of database execution. But indexing is only one factor in improving efficiency. If your MySQL has large data tables, you'll need to spend time researching the best indexes or optimizing the query statements.

The above mentioned is the entire content of this article, I hope you can enjoy.