MySQL index introduction and differences between common indexes (primary key index, unique index, normal index, full-text index, composite index)

MySQL various index differences:
Normal index: The most basic index, without any restrictions
Unique index: Similar to "normal index", the difference is that the value of the indexed column must be unique, but a null value is allowed.
Primary KEY index: It is a special unique index and is not allowed to have null values.
Full-Text indexing: Available only for MyISAM tables, generating full-text indexes is a time-consuming space for larger data.
Combined index: For more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

MySQL Index concept:
Say the MySQL index, see a few example: index is like a book directory, it will let you find content faster, obviously the directory (index) is not the more the better, if the book 1000 pages, 500 is also the directory, it is of course inefficient, the directory is to occupy the paper, and the index is to occupy disk space.

The MySQL index has two main structures: B + tree and hash.

Hash:hsah index is less useful in MySQL, he organizes the index of the data in hash form, so it is very fast when looking for a record. At that time, because it is a hash structure, each key only corresponds to one value, And is the way the hash is distributed. So he doesn't support features such as range lookup and sorting.
B + Tree: B+tree is the most frequently used index data structure in MySQL, the data structure is organized in the form of a balanced tree, because it is a tree structure, so it is more suitable for processing sorting, scope lookup and other functions. Relative hash index, B + tree in the search for a single record speed, although not compared to the hash index, But because it is more suitable for sorting and other operations, so he is more popular with users. After all, it is not possible to do a single record of the database.

Common MySQL 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 fulltext (' column ')

Combined index ALTER TABLE ' table_name ' ADD index index_name (' Column1 ', ' column2 ', ' column3 ')

MySQL index introduction and differences between common indexes (primary key index, unique index, normal index, full-text index, composite index)