Shulou(Shulou.com)06/01 Report--

Next, let's learn what the main index types are in mysql. I believe you will benefit a lot after reading it. The text is not much in essence. I hope mysql has what main index types this short article is what you want.

The index types in mysql are: the most basic, unrestricted general index, the unique index that the value of the index column must be unique, the primary key index, the combined index created on multiple fields, and the full-text index used to find keywords in the text.

1. General index

Is the most basic index, it does not have any restrictions. It can be created in the following ways:

Create an index directly

CREATE INDEX index_name ON table (column (length))

How to modify the table structure to add an index

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

Create an index when you create 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`), INDEX index_name (title (length)

Delete index

DROP INDEX index_name ON table

2. Unique index

Similar to the previous normal index, except that the value of the index column must be unique, but null values are allowed. If it is a combined index, the combination of column values must be unique. It can be created in the following ways:

Create a unique index

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

Modify table structure

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

Specify directly when you create 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

Is a special unique index where a table can have only one primary key and no null values are allowed. Typically, the primary key index is created at the same time as the table is created:

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

4. Combined index

An index created on multiple fields, which is used only if the first field when the index was created is used in the query condition. Follow the leftmost prefix set when using a combined index

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

5. Full-text index

It is mainly used to find keywords in the text, rather than comparing them directly to the values in the index. The fulltext index is very different from other indexes in that it is more like a search engine than a simple parameter match for a where statement. Fulltext indexes are used with match against operations, rather than normal where statements 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 and varchar,text columns. It is worth mentioning that when the amount of data is large, 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 a fulltext for a table and then write the data.

Create a table 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))

Modify the table structure to add a full-text index

ALTER TABLE article ADD FULLTEXT index_content (content)

Create an index directly

CREATE FULLTEXT INDEX index_content ON article (content)

Shortcoming

1. Although the index greatly improves the query speed, it slows down the speed of updating the table, such as insert, update, and delete on the table. Because when updating the table, you should not only save the data, but also save the index file.

2. Build an index file that takes up disk space. In general, this problem is not too serious, but if you create multiple combined indexes on a large table, the index file will grow very fast. Indexes are only one factor in improving efficiency, and if you have tables with a large amount of data, you need to take the time to study how to build the best indexes, or to optimize query statements.

Matters needing attention

There are some tips and considerations when using indexes:

1. The index will not contain columns with null values

As long as the column contains a null value, it will not be included in the index, and as long as one column in the composite index contains a null value, the column is invalid for the composite index. So let's not let the default value of the field be 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 column of char (255), if most values are unique within the first 10 or 20 characters

Then do not index the entire column. Short indexes can not only improve the query speed, but also save disk space and Imax O operations.

3. Sort the index column

The query uses only one index, so if the index is already used in the where clause, the columns in order by will not use the index. Therefore, the default sort of the database can be characterized by

Do not use sorting operations if required; try not to include multiple column sorting, and it is best to create a composite index on those columns if necessary.

4. Like statement operation

Generally speaking, like operation is not recommended. If you have to use it, how to use it is also a problem. Like "% aaa%" does not use an index while like "aaa%" can use an index.

5. do not operate on the column, which will cause the index to fail and perform a full table scan, such as

SELECT * FROM table_name WHERE YEAR (column_name)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.