1, Introduction

MySQL currently has the following index types:
1. General index
2. Unique index
3. Primary key index
4. Composite index
5. Full text index

2, Statement

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1. unique|fulltext is an optional parameter, which represents a unique index and a full-text index respectively
2. Index and key are synonyms. They have the same function and are used to specify index creation
3. col_name is the field column that needs to be indexed. This column must be selected from multiple columns defined in the data table
4. index_name specifies the name of the index. It is an optional parameter. If not specified, the default is col_name is the index value
5. Length is an optional parameter, indicating the length of the index. Only fields of string type can specify the index length
6. asc or desc specifies the index value store in ascending or descending order

3, Index type

1. General index
Is the most basic index, it has no restrictions. It can be created in the following ways:
(1) Create index directly

CREATE INDEX index_name ON table(column(length))

(2) Adding indexes by modifying the table structure

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

(3) Indexes are created when tables are created

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

(4) Delete index

DROP INDEX index_name ON table

2. Unique index
Similar to the previous ordinary index, the difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
(1) Create unique index

CREATE UNIQUE INDEX indexName ON table(column(length))

(2) Modify table structure

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

(3) Specify directly when creating a table

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3. Primary key index
It is a special unique index. A table can only have one primary key, and null values are not allowed. Generally, the primary key index is created when creating a table:

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4. Composite index
It refers to the index created on multiple fields. The index can only be used if the first field when creating the index is used in the query criteria. Follow the leftmost prefix set when using composite indexes

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5. Full text index
It is mainly used to find keywords in text, rather than directly compare with the values in the index. Fulltext index is very different from other indexes. It is more like a search engine than a simple parameter matching of where statement. The fulltext index is used in conjunction with the match against operation, rather than the general where statement plus like. It can be used in create table, alter table and create index, but at present, only char, varchar and text columns can create full-text indexes. It is worth mentioning that when there is a large amount of data, it is much faster to put the data into a table without a global index and then create a fulltext index with create index than to create fulltext for a table first and then write the data.
(1) Create a table that is suitable for adding a full-text index

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

(2) Modify table structure and add full-text index

ALTER TABLE article ADD FULLTEXT index_content(content)

(3) Create index directly

CREATE FULLTEXT INDEX index_content ON article(content)

4, Shortcomings

1. Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as insert ing, updating and deleting the table. Because when updating the table, you should not only save the data, but also save the index file.
2. Index files that take up disk space when indexing. Generally, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will grow rapidly.
   Index is only a factor to improve efficiency. If there is a large amount of data table, you need to spend time studying and establishing the best index or optimizing query statements.

5, Precautions

There are some tips and precautions when using index:

1. The index will not contain columns with null values
   As long as a column contains a null value, it will not be included in the index. As long as a column in a composite index contains a null value, this column is invalid for this composite index. Therefore, we should not let the default value of the field be null when designing the database.
2. Use short index
   Index a string column and specify a prefix length if possible. For example, if you have a char(255) column, if multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short index can not only improve query speed, but also save disk space and I/O operation.
3. Index column sorting
   The query only uses one index, so if the index is already used in the where clause, the column in order by will not use the index. Therefore, if the default sorting of the database can meet the requirements, do not use the sorting operation; Try not to include the sorting of multiple columns. If necessary, it is best to create a composite index for these columns.
4. like statement operation
   Generally, the like operation is not recommended. If it is necessary, how to use it is also a problem. Like "% aaa%" does not use indexes, but like "aaa%" can use indexes.
5. Do not operate on columns
   This will result in index invalidation and a full table scan, such as

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

1. not in and < > operations are not used