Learning Plan MySQL Index

Sentiment:

Index this thing is really very abstract, at first very do not understand, just like I was just beginning to touch Ajax. But when you touch it, it's really not that hard.

--

First recommend the article:

It's really good to write: http://www.cnblogs.com/whgk/p/6179612.html

--

One: What is an index? What is the purpose of indexing?

The data in the database is actually stored in the form of a file, just like a book. We've got all sorts of data in them. The index, like a bookmark, can help us find the content directly, avoiding a page-by-page search.

This will save a lot of time.

If there is a 2w data table, if there is no index, then the database to traverse the search, then if the data more?

If you have an index, the fields that need to be queried are stored in a way that enables you to quickly find the corresponding data when you query the information on that field, rather than traversing the 2W data.

There are two types of storage for indexes in MySQL: BTREE, HASH. That is, using a tree or hash value to store the field, to know how detailed it is to find, you need the knowledge of the algorithm. Now we just need to know what the index does, and what the function is.

--

Two: Advantages and disadvantages of the index in MySQL and its usage principles

Advantages:

　　　　Greatly speed up the query.

　　Disadvantages:

　　　　Excerpt from the article

1. It takes time to create indexes and maintain indexes, and increases the time it takes to increase the amount of data

2, the index also need to occupy space, we know the data table data will also have the maximum on-line settings, if we have a large number of indexes, the index file may be faster than the data file to reach the line value

3, when the data in the table to increase, delete, modify, the index also needs dynamic maintenance, reduce the data maintenance speed.

　　Principle of Use:

　　　　Excerpt from the article

Through the advantages and disadvantages mentioned above, we should be able to know that not every field of the index is good, not the more the better, but need to use their own reasonable.

1, to the frequently updated table to avoid excessive indexing, the fields that are frequently used for querying should create an index,

2, the data volume of the table is best not to use the index, because the data is small, may query all the data spend more time than the time to traverse the index, the index may not have an optimization effect.

3, in the column with less value (on the field) do not index, such as in the student table "gender" field only male, female two different values. Conversely, there are many different values on a field, but indexes are established.

--

Three: Index type

Recommended article inside already very detailed explained the type, I also do not want simple copy/paste. Here are just a few words about my understanding of this.

Index categorical single-column index (normal index, unique index, primary key index), composite index, full-text index, spatial index,

--

Four: Normal index

Basic index type, with no restrictions, allowing duplicate and null values to be inserted in the column that defines the index, simply to query the data a little faster, open to build multiple indexes

Because I'm used to adding and indexing after I've created a table, I only introduce creating indexes using CREATE INDEX.

Let's take a look at my index table structure

Mysql> desc book;+------------------+--------------+------+-----+---------+-------+| Field            | Type         | Null | Key | Default | Extra |+------------------+--------------+------+-----+---------+-------+| BookID           -int |      NO   |     | NULL    |       | | bookname         | varchar (255) | NO   |     | NULL    |       | | authors          | varchar (255) | NO   |     | NULL    |       | | info             | varchar (255) | YES  |     | NULL    |       | | comment          | varchar (255) | YES  |     | NULL    |       | | year_publication | year (4)      | NO   |     | NULL    |       | +------------------+--------------+------+-----+---------+-------+6 rows in Set (0.00 sec)

Now add a normal index to the table BookName

Mysql> CREATE INDEX bkbooknameidx on book (bookname);

View Table Index

Mysql> SHOW INDEX from book \g;; 1. Row ***************************        table:book   non_unique:1     key_name:bkbooknameidx seq_in_index:1  Column_name:bookname    collation:a  cardinality:0     sub_part:null       packed:null         NULL:   index_ Type:btree      comment:index_comment:1 Row in Set (0.00 sec)

--

V: Unique Index

The values in the indexed column must be unique, but allow null values.

CREATE UNIQUE INDEX bookid on book (BookID);

View Index

Mysql> SHOW INDEX from book \g;*************************** 1. Row ***************************        table:book   non_unique:0     key_name:bookid seq_in_index:1  Column_ Name:bookid    collation:a  cardinality:0     sub_part:null       packed:null         NULL:   index_type:btree      comment:index_comment:

--

Six: PRIMARY key index

is a special unique index and is not allowed to have null values.

You can set primary key index and unique index in one field at the same time

mysql> ALTER TABLE ' book ' ADD PRIMARY KEY (' BookID ');

--

VI: Combined index

indexes created on multiple field combinations in a table are used only when the left field of these fields is used in the query criteria, and the combined index is used to follow the leftmost set of prefixes.

The combination index is to follow the leftmost prefix, using the leftmost Lie in the index to match the row, such a set of columns called the leftmost prefix, do not understand that it is okay, for example, here is the index of the ID, name and Age3 fields, the index row in the order of Id/name/age, The index can index the following field combination (Id,name,age), (Id,name), or (ID). If the field to be queried does not constitute the leftmost prefix of the index, then the index will not be used, for example, the age or (name,age) combination will not use the index query

CREATE INDEX Bookidaas on book (authors,bookid);

--

Six: Full-text indexing

Http://blog.sina.com.cn/s/blog_ae1611930101a063.html

--

About the use of the index feeling is only to understand a general, and then use a more in-depth understanding of the words back to supplement this knowledge.

Learning Plan MySQL Index