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

Types of indexes in MySQL

Generally, it can be divided into four categories:

General index: the most common index unique index: the value of the index column must be unique, but null values are allowed in the primary key index: a special unique index, no null values are allowed in the joint index: the index column has multiple fields and needs to meet the leftmost prefix principle when used.

General index

This is the most basic index, and it has no restrictions. It can be created in the following ways:

1. Create an index

The code is as follows:

CREATE INDEX indexName ON mytable (username (length))

For CHAR,VARCHAR, length can be less than the actual length of the field; for BLOB and TEXT, length must be specified, same as below.

two。 Modify table structure

The code is as follows:

ALTER mytable ADD INDEX [indexName] ON (username (length))

Specify directly when you create a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, INDEX [indexName] (username (length)

Syntax for deleting an index:

DROP INDEX [indexName] ON mytable

Unique index

It is 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 UNIQUE INDEX indexName ON mytable (username (length))

Modify the table structure:

ALTER mytable ADD UNIQUE [indexName] ON (username (length))

Specify directly when you create the table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, UNIQUE [indexName] (username (length)

Primary key index

It is a special unique index and no null values are allowed. Typically, the primary key index is created at the same time as the table is created:

The code is as follows:

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.

Joint index

To visually compare single-column indexes with combined indexes, add multiple fields to the table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, city VARCHAR (50) NOT NULL, age INT NOT NULL)

In order to further extract the efficiency of MySQL, it is necessary to consider the establishment of a composite index. Is to build name, city, and age into an index:

The code is as follows:

ALTER TABLE mytable ADD INDEX name_city_age (name (10), city,age)

A situation in which the index is not needed.

The index does not always take effect, if we do not operate correctly, then it is likely to be a full table scan rather than a walking index. The possible_key, key_len and key parameters in Explain can analyze whether our SQL statement uses the index.

The following conditions will cause the index to fail

Used in the column of the query! = for example, select id,name,age from student where id! = 2 Functional operations are used in the columns of the query, such as the functional expression pow (id,2) squares id, and the index will not be used if there is or in the condition, even if some of the conditions are indexed (which is why or is used as little as possible) even if the leftmost prefix principle is satisfied in the federated index, the first condition has a range query. Then the invisible conversion of the data type in which the index has an index column will not be used, for example, if the column type is a string, then the data must be referenced in quotation marks in the condition, otherwise the index will not be used. If MySQL estimates that using a full table scan is faster than using an index, then do not use the index.

All of the above can be tested with Explain to see if an index is used.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.