Summary of MySQL indexes that will not be used

Time:2021-3-7

The type of index in MySQL

Generally, it can be divided into four categories

  • Common index: the most common index
  • Unique index: the value of index column must be unique, but null value is allowed
  • Primary key index: a special unique index that does not allow null values
  • Joint index: the index column has multiple fields, which should meet the leftmost prefix principle

General index

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

1. Create index

The code is as follows:


CREATE INDEX indexName ON mytable(username(length));

For char and varchar, the length can be less than the actual length of the field; for blob and text, the length must be specified, the same below.

2. Modify the table structure

The code is as follows:


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

Directly specify when creating a table


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

Delete index syntax:


DROP INDEX [indexName] ON mytable;

unique index

It is similar to the previous ordinary index, except that the value of index column must be unique, but null value is allowed. If it is a composite 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))

When creating a table, directly specify:


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

primary key

It is a special unique index and does not allow null values. In general, the primary key index is created when 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 also use the alter command. Remember: a table can only have one primary key.

Union index

In order to compare the single column index with the composite index, several fields are added 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 composite index. Name, city and age are built into an index

The code is as follows:


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

The situation of index not being used

The index doesn’t take effect every time. If we don’t do it right, it’s very likely that we’re going through a full table scan instead of going through the index_ key、key_ Len and key parameters can analyze whether our SQL statement uses the index.

The following will cause the index to fail

  1. For example, select id, name, age from student where id! = 2 is used in the query column;
  2. Query columns use function operations, such as pow (ID, 2) to square id, and index is not used
  3. If there is an or in the condition, even if some of the conditions are indexed, they will not be used (this is why or is used as little as possible)
  4. Even if the leftmost prefix principle is satisfied in the union index, the index will not be used if the first condition is a range query
  5. If there is an implicit conversion of the data type of the index column, the index is not used. For example, if the column type is string, the data must be quoted in quotation marks in the condition, otherwise the index is not used
  6. If MySQL estimates that using full table scan is faster than using index, index is not used

You can use explain to test whether the index is used.

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.