MySQL index type

First, Introduction

MySQL currently has the following types of indexes:
1. General Index
2. Unique index
3. Primary KEY index
4. Combined Index
5. Full-Text Indexing

Second, the 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 that represents a unique index, a full-text index, respectively
The 2.index and key are synonyms, both of which have the same effect and are used to specify index creation
3.col_name is the field column for which you want to create an index, and the column must be selected from multiple columns in that definition in the datasheet
4.index_name the name of the specified index, as an optional parameter, if not specified, the default col_name is the index value
5.length is an optional parameter that represents the length of the index, only fields of the string type can specify the index length
6.ASC or desc Specifies that an ascending or descending index value is stored

Iii. type of index

1. General Index
Is the most basic index and it has no limitations. It is created in the following ways:
(1) Create an index directly

CREATE INDEX index_name on table (column (length))

(2) How to modify the table structure to add an index

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

(3) Create the index at the same time when creating the table

CREATE table ' table ' (    ' id ' int (one) not null auto_increment,    ' title ' char (255) CHARACTER NOT NULL,    ' content ' Text CHARACTER null,    ' time ' int (ten) null DEFAULT null,    PRIMARY KEY (' id '),    INDEX index_name (title (length)) )

(4) Deleting an index

DROP INDEX index_name on table

2. Unique index
Similar to the previous normal index, the difference is that the value of the indexed column must be unique, but allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:
(1) Create a 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) When creating a table, specify directly

CREATE table ' table ' (    ' id ' int (one) not null auto_increment,    ' title ' char (255) CHARACTER NOT NULL,    ' content ' Text CHARACTER null,    ' time ' int (ten) null DEFAULT null,    UNIQUE indexname (title (length));

3. Primary KEY index
is a special unique index, a table can have only one primary key, and no null value is allowed. The primary key index is typically created at the same time as the table is built:

CREATE table ' table ' (    ' id ' int (one) not null auto_increment,    ' title ' char (255) is not NULL,    PRIMARY KEY (' id ')) ;

4. Combined Index
Refers to indexes created on multiple fields, and the index is used only if the first field in the query condition is used to create the index. To follow the leftmost prefix collection when using a composite index

5. Full-Text Indexing
It is used primarily to find keywords in text, not directly compared to the values in the index. The fulltext index is very different from other indexes, it is more like a search engine than a simple where statement parameter matching. The fulltext index is used in conjunction with the match against operation instead of the generic where statement plus like. It can be used in Create Table,alter table, create INDEX, but only the char, varchar,text columns can be created at this time. It is worth mentioning that, when the data volume is large, the data is now placed in a table without a global index, and then create the fulltext index by creating index, more than the first one to establish a table fulltext and then write the data much faster.
(1) Create a table that is suitable for adding full-text indexes

CREATE table ' table ' (    ' id ' int (one) not null auto_increment,    ' title ' char (255) CHARACTER NOT NULL,    ' content ' Text CHARACTER null,    ' time ' int (ten) null DEFAULT null,    PRIMARY KEY (' id '),    Fulltext (content));

(2) Modify table structure add full-text index

ALTER TABLE Article ADD fulltext index_content (content)

(3) Create an index directly

CREATE Fulltext INDEX index_content on article (content)

Iv. shortcomings

1. Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when you update a table, you not only save the data, but you also save the index file.
2. index files that create indexes that consume disk space. The general situation is not very serious, but if you create multiple combinations of indexes on a large table, the index file will grow quickly.
Indexing is just one factor in efficiency, and if you have a table with large data volumes, you need to spend time studying to build the best indexes, or to refine the query statements.

Five, matters needing attention

There are some tips and considerations when working with indexes:
1. The index does not contain columns with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
2. Using a short index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
3. Index column sorting
A query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
4.like Statement Operations
In general, it is not recommended to use the like operation, if not used, how to use is also a problem. Like "%aaa%" does not use the index and like "aaa%" can use the index.
5. Do not perform calculations on columns
This causes the index to fail with a full table scan, for example

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

6. Do not use not and <> operations

Reference Link: https://www.cnblogs.com/luyucheng/p/6289714.html

Reference Link: https://www.cnblogs.com/forcheryl/p/7389798.html

MySQL index type