1. Index naming conventions:

Single valued index, idx is recommended_ All lowercase letters begin with.

For example: alter table t1 add key idx_r1(r1)；

Combined index, DX is recommended_ multi_ Start with all lowercase letters.

For example: alter table t1 add key idx_multi_1(r1,r2,r3) ;

Unique index, recommended in udx_ Is the beginning, and all letters are lowercase; If it is a multivalued unique index, it is named like udx_multi_1, etc.

For example:
alter table t1 add unique key udx_f1(r1)；
perhaps
alter table t1 add key udx_multi_1(r1,r2,r3);

Full text index, recommended in ft_ Start with all lowercase letters, and it is recommended to use the ngram plug-in by default.

For example: alter table t1 add fulltext ft_r1(r1) with parser ngram;

prefix index, idx is recommended_ Start with_ prefix end.

For example: alter table t1 add key idx_r1_prefix(r1(10));

Function index, IDX is recommended_ func_ Start with all lowercase letters.

For example: alter table t1 add key idx_func_r1((mod(r1,4)));

1. Index column selection specification:

Field type of index column:

The index itself is orderly. Try to select integer columns as the index. If you can't avoid string indexing, you can choose to HASH the character type, and then index based on the HASH results;

The data type of the primary key column should also be an integer to avoid establishing a primary key for an irregular string (for example, the UUID mentioned in the discussion of the primary key should be avoided because the INNODB table is an index. It is not because the UUID is unordered, but because a single UUID is too large)

Character length of index column:

Try to control the character length of the index. For example, in the field r1 varchar(200), if only the first 10 characters change frequently and the following characters are close to static data, a prefix index can be established based on the first 10 characters. Large object fields only support prefix indexing.

alter table t1 add key idx_r1_prefix(r1(10));

Optional index columns:

Indexing based on fields with higher selectivity may be counterproductive; This rule is also applicable to composite indexes. Multiple fields establish composite indexes together, and the order of composite indexes is determined according to the selectivity of index keys. for instance:

There are 1000 records in table t1, 800 different values in field r1, 500 different values in field r2 and 100 different values in field r3. The order of combined indexes should be (r1,r2,r3).

Number control of single table indexes:

Although a single MySQL table supports 64 indexes at most, the number of specific indexes should be as few as possible. For example, it can be treated separately according to the table structure and business access model. Different tables can have different numbers of indexes.

Indexes are not recommended for frequently updated columns:

Indexing frequently updated columns is not recommended. When indexing such a column, the index key value distribution corresponding to this column will be updated at the same time when the data is frequently updated. For example, the inventory quantity attribute of goods will be updated frequently, and indexing should be avoided. In general data access requests, the commodity ID is a necessary filter condition, and the inventory quantity can only be displayed.

Functional index:

If it is not necessary, the function index is not established, unless the function retrieval must be done based on this column.

For example, for column R1, the operations based on mod(r1,4) are much more frequent than those based on R1, so it is necessary to establish a functional index for column R1.

Duplicate index:

The indexes are based on the leftmost column scanning to find the entry, and then continue to traverse. Under unnecessary conditions, avoid the simultaneous existence of multiple indexes starting from the leftmost column.

For example, if column r1 is contained by three indexes, you may think of the following statement:

select * from t1 where r1 = 2 and r2 = 2 and r3 = 2;
   
select * from t1 where r1 = 2;
   
select * from t1 where r1 = 2 and r2 = 2;

If the above three statements run frequently, you only need to create a combined index starting with r1, that is, index idx_multi_1 .

For example:
   
alter table t1 add key idx_multi_1(r1,r2,r3);

However, if the following statement also runs frequently:

select * from t1 where r1 = 1 and r4 = 1 and r5 = 1;
  
select * from t1 where r1 = 1 and r4 = 1;

You need to create another composite index that starts with field r1 and ends with fields R4 and R5.

For example:
   
alter table t1 add key idx_multi_2(r1,r4,r5);

If the filter conditions (R1, R2, R3) are used more frequently than the filter conditions (r1,r4,r5), you can consider merging the two composite indexes into a large composite index. (r1,r2,r3,r4,r5).

For example:

alter table t1 drop key idx_multi_1, drop key idx_multi_2, add key idx_multi_1(r1,r2,r3,r4,r5);

Reverse indexing based on table query statements:

For example, the following statement:

select * from t1 where r1 = 2;

select * from t1 where r2 = 2;

select * from t1 where r3 = 2;

select  * from t1 where r4 = 2;

select * from t1 where r1 = 2 or r2 = 2 or r3 = 2 or r4 = 2;

select * from t1 where r2 = 2 or r4 = 2;

select * from t1 where r2 = 2 and r3 = 2;

select * from t1 where r3 = 2 or r4 = 2;

select * from t1 where r1 = 2 and r2  = 2 and r3  = 2 and r4 = 2;

The above SQL filter fields are limited to fields R1, R2, R3 and R4, and are not fixed. At this time, you can create an index for each column separately, and MySQL determines which index to use or multiple indexes to use together.

For example:

alter table t1 add key idx_r1(r1),
add key idx_r2(r2), 
add key idx_r3 (r3), 
add key idx_r4(r4);

Avoid small table based indexing:

For example, the number of table t1 records may always be only 1000 rows, so there is no need to establish other indexes except the primary key.

For example:

 select * from t1 where r1 = 10;

The above statement is very simple. The speed of index walking may not be as fast as that of sequential table scanning.

Designation of index order:

In general scenarios, if the requirement is not mandatory, the index key values are sorted in ascending order by default, unless the query filter criteria specify sorting.

For example, query statements: select r1,r2,r3 from t1  order by r1 desc, r2 asc, r3 desc

If the index is established by default, the cost of this SQL sequence is very large. At this point, you can specify the field order for indexing.

For example:

alter table t1 add key idx_multi_sort_1(r1 desc, r2 asc, r3 desc);