What are the index types in mysql

The index types in mysql are: 1. Ordinary index; 2. Unique index; 3. Primary key index; 4. Combined index; 5. Full-text index. A normal index is the most basic index and has no restrictions. A unique index requires that the value of the index column must be unique, but null values ​​are allowed.

The index types in MySQL are as follows

• Normal index

• Unique index

• Primary key index

• ##Combined index

• Full-text index

(Recommended tutorial:

mysql video tutorial)

1. Ordinary index

is the most basic index, it has no restrictions. It has the following creation methods:

Create index directly

CREATE INDEX index_name ON table(column(length))

Add index by modifying table structure

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

Create an index at the same time when creating the 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 ,    PRIMARY KEY (`id`),    INDEX index_name (title(length))
)

Delete the index

DROP INDEX index_name ON table

2. Unique indexSimilar to the previous ordinary index, the difference is that the value of the index column must be unique, but null values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique. It has the following creation methods:

Create a unique index

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

Modify the table structure

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

Create When specifying a table, directly specify

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 is a special unique index. A table can only have one primary key, and no null values ​​are 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. Composite index refers to the index created on multiple fields. Only the created index is used in the query conditions. When the first field, the index will be used. When using combined indexes, follow the leftmost prefix set

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

5. Full-text index is mainly used to find keywords in the text, rather than directly comparing with values ​​in the index. The fulltext index is very different from other indexes. It is more like a search engine rather than a simple parameter matching of the where statement. The fulltext index is used with the match against operation instead of the general where statement plus like. It can be used in create table, alter table, and create index, but currently only full-text indexes can be created on char, varchar, and text columns. It is worth mentioning that when the amount of data is large, it is better to put the data into a table without a global index and then use CREATE index to create a fulltext index than to first create a fulltext for a table and then write the data. The speed is much faster.

It is suitable to add a full-text index 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 ,    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

Modify the table structure and add a full-text index

ALTER TABLE article ADD FULLTEXT index_content(content)

Create directly Index

CREATE FULLTEXT INDEX index_content ON article(content)

Disadvantages

1. Although the index greatly improves the query speed, it will also reduce the speed of updating the table, such as inserting and updating the table. and delete. Because when updating the table, not only the data must be saved, but also the index file must be saved.

2. Creating an index file will occupy disk space. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will grow quickly. Indexes are only one factor that improves efficiency. If you have a table with a large amount of data, you need to spend time researching and building the best index or optimizing query statements.

Notes

When using indexes, there are some tips and precautions:

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 one column in the composite index contains a null value, then this column will be invalid for the composite index. Therefore, when designing the database, we should not let the default value of the field be null.

2. Use short index

to index the string. If possible, you should specify a prefix length. For example, if you have a char(255) column, if most values ​​are unique within the first 10 or 20 characters, then don't index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

The query only uses one index, so if the index has been used in the where clause, the columns in order by will not use the index. Therefore, do not use sorting operations when the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create composite indexes for these columns.

4. Like statement operation

Generally, it is not recommended to use the like operation. If it must be used, how to use it is also a problem. Like “�a%” will not use the index but like “aaa%” will.

5. Do not perform operations on columns, which will cause the index to fail and perform a full table scan, such as

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

6. Do not use not in and a8093152e673feb7aba1828c43532094 operations

The above is the detailed content of What are the index types in mysql. For more information, please follow other related articles on the PHP Chinese website!