Through some of the previous index design related introduction and examples, I believe that we have some fragmented understanding of the index design piece, this article to make a summary, give a detailed specification of the index design.

  1. Index naming convention:

Single-valued index. It is recommended to start with IDX_ and all letters are lowercase.

Alter table t1 add key idx_r1(r1);

Composite index, recommended to start with dx_multi_, all lowercase letters.

Alter table t1 add key idx_multi_1(r1,r2,r3);

Unique index, recommended to start with udx_, all lowercase letters; If it is a multi-valued unique index, the naming method is similar to udx_multi_1, etc.

Alter table t1 add unique key udx_f1(r1); Alter table t1 add key udx_multi_1(r1,r2,r3);

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

Alter table t1 add fulltext ft_r1(r1) with parser ngram;

Prefix index. It is recommended to start with idx_ and end with _prefix.

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

Functional index, recommended to begin with IDX_FUNC_, all lowercase letters.

Alter table t1 add key idx_func_r1((mod(r1,4)));

  1. Index column selection specification:

    ##### The field type of the indexed column:

If the index itself is ordered, choose integer column as the index as far as possible. If you cannot avoid the string as the index, you can choose to HASH the character type, and then index it based on the HASH result.

The primary key column data type should also be integer. Avoid establishing primary keys for irregular strings (such as UUID mentioned in discussion of primary keys, which should be avoided since InnoDB tables are indexes). Not because the UUID is not ordered, 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, you can build a prefix index based on the first 10 characters. Large object fields only support the establishment of prefix indexes.

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

##### Optional index columns:

Building indexes based on highly selective fields, while building indexes on less selective fields may be counterproductive; This rule also applies to composite indexes, where multiple fields are created together and the order of the composite indexes is determined by the selectivity of the index key. Here’s an example:

Table T1 has 1000 records, field R1 has 800 different values, field R2 has 500 different values, and field R3 has 100 different values. The order of the combined index should be (r1,r2,r3).

##### Control of the number of indexes in a single table:

Although MySQL supports a maximum of 64 indexes on a single table, it is better to have as few indexes as possible. For example, it can be treated separately according to the table structure and the business access model, and different tables can have different numbers of indexes.

##### Frequently updated columns are not recommended to have indexes:

Indexing columns that are updated frequently is not recommended. Indexing such a column will update the index key distribution for the column when the data is frequently updated. For example, the inventory quantity attribute of goods will be updated frequently. Indexing should be avoided. In general data access request, the commodity ID is a necessary filtering condition, and the inventory quantity can only be displayed.

##### Functional index:

Do not create a functional index unless it is necessary to do a function search based on the column.

For example, for column R1, where mod-based (R1,4) operations are much more frequent than R1-based operations, it would be necessary to have a functional index on column R1.

##### Duplicate Index:

The index is based on the leftmost column scan to find the entry, and then continue traversal. If not necessary, avoid the existence of multiple indexes starting with the leftmost column at the same time.

For example, if column R1 is contained by three indexes, you might expect 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 can simply create a combined index starting with r1, the index idx_multi_1.

Alter table t1 add key idx_multi_1(r1,r2,r3);

But if the following statements are also run 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 a composite index starting with field r1 and ending with field r4 and r5.

Alter table t1 add key idx_multi_2(r1,r4,r5);

If the filter criteria (R1, R2, R3) are used more frequently than the filter criteria (R1, R4, R5), then it is possible to combine the two composite indexes into one large composite index. (r1, r2, r3, r4, r5).

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

Mysql > create a reverse index based on a table query;

For example:

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;

These SQL filter field is limited in the fields above r1, r2, r3 and r4 four, and is not fixed, this time can separate each column index, determined by the MySQL with which index or multiple index together.

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

##### Avoid indexing based on small tables:

For example, table t1 may always have only 1000 rows, so there is no need to create an index other than the primary key.

Select * from t1 where r1 = 10;

The above statement is very simple, go index speed may not be sequential sweep table to fast.

##### Specify index order:

In general scenarios, if not mandatory, the default is ascending order by index key value, unless the query filter condition specifies sorting.

Select r1, R2,r3 from t1 order by r1 desc, R2 asc, r3 desc

This SQL order is expensive if indexed by default. You can specify the order in which the fields are indexed.

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

This chapter will be the end of the index design section. If you have any questions after reading the index design chapter, please feel free to send a private message or leave a message.

Is there anything else you’d like to know about MySQL’s technical content? Leave a message to tell Xiaobian!