A brief analysis of MySQL indexes

MySQL various indexes (because the analysis is mostly not deliberately distinguish between search engines)

Index (normal index): The most basic index, without any restrictions

ALTER TABLE ' table_name ' ADD INDEX index_name (' column ')

Unique (unique index): Similar to "normal index", the difference is that the value of the indexed column must be unique, but allow a null value.

ALTER TABLE ' table_name ' ADD UNIQUE (' column ')

Note: The purpose of creating a unique index is not to increase access speed, but to avoid duplication of data. A unique index can have more than one but the value of the indexed column must be unique, and the value of the indexed column allows for a null value. If you can determine that a data column will contain only values that are different from each other, you should use the keyword unique when creating an index for that data column and define it as a unique index.

PRIMARY key (primary key index): It is a special unique index and is not allowed to have null values.

ALTER TABLE ' table_name ' ADD PRIMARY KEY (' column ')

Fulltext (full-text index): Available only for MyISAM tables, it is time-consuming to generate full-text indexes for larger data.

ALTER TABLE ' table_name ' ADD fulltext (' column ')

COMBINED (combined index): To improve MySQL efficiency, you can set up a composite index and follow the "leftmost prefix" principle.

ALTER TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')

What you should know about using the index:

1, although the index greatly improves the query speed, but also reduces the speed of updating the table, such as INSERT, UPDATE and delete the table. Because when updating a table, MySQL not only saves the data, but also saves the index file.

2. index files that make up the index will consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.

3, index is only a factor to improve efficiency, if your MySQL has a large data volume of the table, it will take time to study the establishment of the best index, or optimize query statements.

After you build the index, you want to protect the well-indexed execution:

Please refer to the question of the protection index to be aware of this text.