MySQL's index

First, what is the index?

For example, if we want to find a word in the dictionary, how can we find it quickly? That is through the dictionary directory.

For a database, the function of an index is to add a directory to the ' data '.

Second, the index algorithm

With n Random records, without an index, the average search for N/2 times, then after the index?

Tree (binary tree) index log2n

Hash (hash) Index 1

Iii. Advantages and Disadvantages

Pros: Faster query speed (SELECT)

Disadvantage: Reduce the speed of adding and deleting (Update/delete/insert)

Increase the file size of the table (the index file may even be larger than the data file)

Iv. principles of Use of the index

However, the index must be used;

Index criteria columns (where the most frequently-followed conditions are more appropriate for the index)

Index hash value, too concentrated value not indexed

For example: To the Sex column ' Male ', ' female ' index, the meaning is not big

V. Type of index

1. Normal index: Just speed up the query

2. Unique index: Values on rows cannot be duplicated

3. Primary KEY index: cannot be duplicated

The difference between a primary key index and a unique index: The primary key must be unique, but the unique index is not necessarily the primary key;

You can have only one primary key on a table, but you may have one or more unique indexes

4. Full-text index: Fulltext index

Vi. viewing all indexes on a table

Show index from TableName [\g, if it is in the cmd window, can be wrapped];

Vii. Building an Index

ALTER TABLE name add INDEX/UNIQUE/FULLTEXT [index name] (column name); ---Index name can not be written, do not write by default use column name

ALTER TABLE name add primary key (column name)-Do not add index name because the primary key has only one

Viii. Deleting an index

Delete a non-primary key index

ALTER TABLE name DROP INDEX name;

To delete a primary key index:

ALTER TABLE name drop PRIMARY key;

Nine, case

There is a news table 15 columns, 10 columns on the index, a total of 500w rows of data, how to quickly import?

1. Delete all indexes of empty tables

2. Import data

3. After data import is complete, the index is built centrally

X. Full-text indexing and stop words

Full-text Index usage:

Match (full-text index name) against (' keyword ');

About stop words for full-text indexing:

Full-text indexing is not indexed for very frequent words

such as: This,is,you,my, etc.

Full-text indexing in MySQL, by default, is of little significance for Chinese.

Because there are spaces in English, punctuation is broken down into words, and then the words are indexed;

For Chinese, there are no spaces to separate words, and MySQL does not recognize each Chinese word.

You can use the Sphinx plug-in to index Chinese in a full-text index.

MySQL's index