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

This article mainly introduces what type of index in mysql, the article is very detailed, has a certain reference value, interested friends must read it!

At present, the main index types of MySQL are: general index, unique index, primary key index, combined index, full-text index. The following article will introduce these MySQL indexes to you, hoping to be helpful to you.

By adding an index to the field, the reading speed of the data can be improved, and the concurrency and pressure resistance of the project can be improved. An optimization method in mysql when an index is optimized. The function of the index is equivalent to the catalogue of the book, and the content you need can be found quickly according to the page number in the catalog.

Let's take a look at the main types of indexes currently available in MySQL:

1. General index

A normal index is the most basic index, it has no restrictions, and the value can be empty; only accelerate the query. You can create or delete in the following ways:

1), create the index directly

CREATE INDEX index_name ON table (column (length))

2) add an index by modifying the table structure

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

3), delete the index

DROP INDEX index_name ON table

2. Unique index

A unique index is similar to a 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. To put it simply: the unique index is the accelerated query + column values are unique (you can have null). Created in the following ways:

1), create a unique index

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

2). Modify the table structure

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

3. Primary key index

A primary key index is a special unique index in which a table can have only one primary key and no null values are allowed. To put it simply: the primary key index is an accelerated query + column values unique (no null) + only one in the table.

Typically, the primary key index is created at the same time as the table is created:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, PRIMARY KEY (ID))

Of course, you can use the ALTER command. Remember: a table can have only one primary key.

4. Combined index

A combined index is an index created on multiple fields, and an index is used only if the first field when the index was created is used in the query condition. Follow the leftmost prefix collection when using a combined index.

It can be said that a composite index is an index composed of multiple columns of values, which is specially used for combinatorial search, and its efficiency is higher than that of index merging.

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

5. Full-text index

The full-text index is mainly used to find keywords in the text, rather than directly comparing with 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.

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 the table structure to add a full-text index

ALTER TABLE article ADD FULLTEXT index_content (content)

3), create the index directly

CREATE FULLTEXT INDEX index_content ON article (content)

To put it simply: full-text indexing is to segment and search the content of the text.

The above is all the contents of the article "what are the types of indexes in mysql?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.