Issue 32: Index Design (Detailed Specification for Index Design)

Through some of the previous related introductions and examples on index design, I believe that everyone has some piecemeal understanding of index design. Let's make a summary and give a detailed specification of index design.

1. Index naming convention:

Single-value index, it is recommended to start with idx_ and all letters are lowercase.

例如：alter table t1 add key idx_r1(r1)；

Combined index, it is recommended to start with dx_multi_ with all lowercase letters.

例如：alter table t1 add key idx_multi_1(r1,r2,r3) ;

For the unique index, it is recommended to start with udx_ with all lowercase letters; if it is a multi-value 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);

For full-text indexing, it is recommended to start with ft_, all lowercase letters, and it is recommended to use the ngram plugin 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));

Function index, it is recommended to start with idx_func_ and all letters are lowercase.

例如： alter table t1 add key idx_func_r1((mod(r1,4)));

2. Index column selection specification:

   ##### Field type of index column:

If the index itself is ordered, try to choose integer columns for indexing. If you cannot avoid string indexing, you can choose to HASH the character type, and then index based on the HASH result;

The data type of the primary key column is preferably an integer, to avoid creating a primary key for irregular strings (such as the UUID mentioned when discussing the primary key. Since the INNODB table is an index, it should be avoided. It is not because the UUID is not ordered, but Because a single UUID is too big)

##### 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. Only the prefix index is supported for large object fields.

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

##### Optional index column:

Indexing based on fields with higher selectivity may backfire on fields with lower selectivity; this rule also applies to composite indexes. Multiple fields are built together to create a composite index, and the combination is determined according to the selectivity of the index key. The order of the index. for 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).

##### Single table index number control:

Although MySQL single table supports up to 64 indexes, as for the specific number of indexes, it is best to have as few as possible. For example, it can be treated separately according to the table structure and business access model, and different tables can have different numbers of indexes.

##### Indexes are not recommended for frequently updated columns:

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

##### Function Index:

No function index is established for non-essential conditions, unless a function search must be done based on this column.

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

##### Duplicate index:

Indexes are based on scanning the leftmost column to find the entry, and then continue to traverse. Under non-necessary conditions, avoid multiple indexes starting with the leftmost column to exist at the same time.

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

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 composite index starting with r1, that is, the index idx_multi_1.

例如：
   
alter table t1 add key idx_multi_1(r1,r2,r3);

But if the following statements are also frequently run:

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

You need to build a composite index starting with field r1 and ending with fields r4, r5.

例如：
   
alter table t1 add key idx_multi_2(r1,r4,r5);

Let's say that the filter conditions (r1, r2, r3) are used more frequently than the filter conditions (r1, r4, r5), you can consider combining these two composite indexes into a 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);

Reverse index creation based on table query statement:

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 the four fields r1, r2, r3, and r4, and they are not fixed. At this time, each column can be indexed separately, and MySQL decides which index to use or multiple indexes to use 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 adding indexes based on small tables:

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

例如：

 select * from t1 where r1 = 10;

The above statement is very simple, and the indexing speed may not be as fast as the sequential scanning of the table.

##### Specification of index order:

In general scenarios, if it is not mandatory, it will default to the ascending order of the index key, unless the query filter condition specifies the sort.

比如查询语句：select r1,r2,r3 from t1  order by r1 desc, r2 asc, r3 desc

If the index is built in the default way, the overhead for this SQL sequence is very high. At this time, you can specify the order of the fields to create an index.

例如：

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

Regarding the index design, this chapter is over. If you have any questions after reading the index design related chapters, please send a private message or leave a message.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!