@[TOC](MySQL index classification)

Mysql indexes are divided into three main types:

1. Single column index 2. Combined index (compound index) 3

—————————————————–

Before introducing the classification of indexes, we must know what an index is:

MySQL official definition is:

Indexes are data structures that help mysql efficiently retrieve data. In addition to the data, the database system maintains data structures that satisfy specific lookup algorithms, and that reference the data in a way that allows high-level algorithms to be implemented on these data structures. This data structure is called an index and an index is essentially a data structure. An index is a sorted, fast lookup data structure. Is to solve the SQL data is too large caused by the efficiency of the optimization programCopy the code

Let’s talk about indexes and briefly explain my understanding of indexes:

1. Single-value index

Single-value indexes are the most common and simplest kind of indexes. An index contains only one column, and a table can have multiple single-column indexes.Copy the code

This is the most basic index with no restrictions. Here are a few ways to create it (length can be ignored):

CREATE INDEX [indexName]ON t1(colname); CREATE TABLE myTABLE (ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length))); Alter table tableName ADD INDEX indexNameCopy the code

2. Unique index

It is similar to the normal index above except that:

The value of the index column must be unique, but can be NULL;Copy the code

When we declare the unique keyword, we automatically add a unique index to its field. It can also be created in three ways, two of which are familiar:

CREATE TABLE myTABLE (ID INT NOT NULL, UNIQUE username VARCHAR(16) NOT NULL, UNIQUE [indexName] (rowname(length)) ); Alter TABLE T1 ADD UNIQUE INDEX [indexName] (rowName (length)) CREATE UNIQUE INDEX [indexName] ON TABLENAME(rowname(length))Copy the code

3. Composite index (combined index)

Composite indexes are the most powerful of all indexes. Indexes can cover multiple data columns at the same time. For composite indexes: mysql uses fields in the index from left to right. A query can use only a portion of the index, but only from the left.

For example, we define the composite index index (c1, c2 and c3), when can we find the c1, c2 and c3 | c1 and c2 | c1 to find these three combinations, only from the left to start, if you use the c2, c3 lookup indexes will fail. An index is useful when the leftmost field is a constant reference.

CREATE INDEX idx_c1_c2_c3ON tablename(c1,c2,c3)
Copy the code

Compound indexes are created when we need to use fields frequently and we can determine the order in which the fields should be used. However, if we are not sure which fields to use, we can only add indexes for these fields separately. Adding useless composite indexes will cause index failure and bring pressure to mysql to change and delete.

Compound index optimization for sorting:

Remember that compound indexes are only optimized for order BY statements that are in exactly the same or opposite order as when the index was created

Create a compound index where:
CREATE INDEX idx_c1_c2_c3ON tablename(c1 DESC,c2 DESC,c3 DESC)

# Exactly the same order
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1 DESC , C2 DESC , C3 DESC;
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1 DESC , C2 DESC ;
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1 DESC ;
# The order is completely reversed
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1, C2, C3 ;
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1, C2;
SELECT c1 ,c2 ,c3 FROM t1 ORDER BY c1;
Copy the code