MySQL index type

Original link: https://www.cnblogs.com/luyucheng/p/6289714.html

1, Introduction

MySQL currently has the following index types:
1. General index
2. Unique index
3. Primary key index
4. Combined 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 indicates unique index and 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, which indicates the length of the index. Only string type fields can specify the index length
6.asc or desc specifies the index value storage 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) Create indexes when creating tables

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 at the same time when creating the table:

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

4. Combined index
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 the keywords in the 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 the parameter matching of a simple 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 occupy disk space when indexing. Generally, this problem is not too serious, but if you create multiple combined 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 the column contains null value, it will not be included in the index. As long as a column in the composite index contains null value, this column is invalid for this composite index. Therefore, we should not make the default value of the field null when designing the database.
2. Use short index
Index the string column and specify a prefix length if possible. For example, if you have a char(255) column, and 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 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 use of 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 calculate on the column
This will result in index invalidation and a full table scan, such as

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

6. Do not use not in and < > operations