Differences between MySQL primary key index, unique index, normal index, full-text index, combined index

Original: MySQL primary key index, unique index, normal index, full-text index, composite index difference

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 + The tree finds a single record faster than a hash index, but because it is more suitable for sorting operations, he is more popular with users. After all, it is not possible to do a single record of the database.

MySQL common indexes are: primary key index, unique index, normal index, full-text index, composite 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 ')
combination 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: 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.

Differences between MySQL primary key index, unique index, normal index, full-text index, combined index