Mysql advanced index

Index: It is a directory created for data.

Function: It can speed up the query.

Negative impact: It reduces the speed of additions, deletions and modifications.

Principles for index creation:

1: Don’t over-index

2: Add to the column with the most frequent where condition. Do not add to the column with high repetition, such as gender;

3: Try to index the hash value, it is not meaningful to add the index to the value that is too concentrated.

Type of index

Ordinary index: index is just to speed up the query.

Unique index: unique The value on the index row cannot be repeated

Primary key index: primary key cannot be repeated.

The primary key must be unique, but the unique index does not necessarily have to be Primary key.

On a table, there can only be one primary key, but one or more unique indexes can be used.

Full-text index: fulltext index is of little significance for Chinese by default in mysql. Generally, a third-party solution is used Solution

Because English has spaces and punctuation marks to split words into words, and then index words.

For Chinese, there are no spaces to separate words, and mysql cannot recognize each Chinese word.

(the above 3 types Indexes work on column values, but full-text indexes can target a certain word in the value, such as an article.) Full-text indexes do not index very frequent words, such as this, is, you, my, etc. Etc.

Index length: Specify part of a column as an index

Multiple column index: Two or more column values ​​are treated as a whole and used as an index. On an English website, first name and last name are treated as a whole. At this time, only first name can play a role in the where condition, and last name does not play a role. This is the left prefix rule.

Redundant index: A certain column has multiple indexes, for example, create an index again on the last name above.

Creating an index

You can directly declare the index when creating the table, that is, declare the index after the column declaration is completed.

For example:

create table test5 (

id int,

username varchar(20),

school varchar(20),

intro text,

primary key (id),

unique (username),

index (school),

fulltext (intro)

) engine myisam charset utf8;

View all indexes on a table

Show index from table name

Create index

Alter table table name add index /unique/fulltext [index name] (column name)

Alter table table name add primary key (column name) // Do not add an index name, because there is only one primary key

Delete add index

Delete non-primary key index: Alter table table name drop/add index index name;

Delete primary key: alter table table name drop/add primary key