Case analysis of establishing efficient index in MySQL

Time:2021-4-20

This paper introduces how to build an efficient index with MySQL. The details are as follows:

How to build an ideal index?

  • Query frequency
  • discriminative power
  • Index length
  • Override field

discriminative power

Assuming 1 million users and 50 W for each gender, the discrimination is low.

The length is small

Index length directly affects the size of index file, the speed of adding, deleting and modifying, and indirectly affects the speed of query

High discrimination and small length

Question: if the discrimination is high and the length is small?

A: you can create an index by intercepting the values in the column from left to right

(1) The shorter the truncation is, the higher the repetition is, the smaller the discrimination is, and the worse the indexing effect is
(2) The longer the truncation, the lower the repetition and the higher the discrimination, the better the indexing effect, but the greater the impact – the slow change of addition and deletion, and affect the query speed

Therefore, we need to strike a balance between discrimination and length. Usual technique: cut different lengths and test their discrimination.

Suppose we have a table: CET-4 word list, which has 13324 records, how can we quote the name field?

这里写图片描述

If we calculate the discrimination?

Truncate the non repetition number of the first digit of a word:


select count(distinct left(name,1)) from dict

Total quantity:


select count(*) from dict

Discrimination: number of non repeats / total number. The SQL statement is as follows:


select (select count(distinct left(name,1)) from dict) / (select count(*) from dict) as rate;

Then follow these steps to find out the discrimination corresponding to other lengths. If you look at this chart, you can see that when the length is 11, the repetition is only 1%. We can consider building an 11 bit index

这里写图片描述


alter table dict add index name name(11);

The left prefix is difficult to distinguish

For the column whose left prefix is not easy to distinguish, how to build index

Such as URL column

http://www.baidu.com
http://www.web-bc.cn

The first 11 characters of the column are all the same and are not easy to distinguish. The following two methods can be used to solve the problem

(1) Store column contents upside down and index them

moc.udiab.www//:ptth
nc.cb-bew.www//://ptth

In this way, the left prefix distinguishes greatly

(2) Pseudo hash index effect

Save both URL and URL_ Hash column

#Table building
create table t10 (
id int primary key,
url char(60) not null default ''
);
#Insert data
insert into t10 values
(1,'http://www.baidu.com'),
(2,'http://www.sina.com'),
(3,'http://www.sohu.com.cn'),
(4,'http://www.onlinedown.net'),
(5,'http://www.gov.cn');
#Modify table structure and add urlcc column
alter table t10 add urlcrc int unsigned not null;

When storing, the CRC32 code corresponding to the URL is inserted into the database, and then the index is established according to the urlcc field. When searching, we convert the corresponding URL to CRC32 in the business layer to search, and then we can use the index.

Because the result of CRC is 32-bit int unsigned, when the data exceeds 4 billion, there will be duplication, but it is worth it. (the index length is int4 bytes)

Multi column index

Consider the factors of multi column index – the query frequency of the column and the discrimination of the column. Pay attention to the actual business scenario

Taking ECSHOP as an example, cat in the goods table_ id,brand_ ID, do multi column index, from the perspective of discrimination, brand_ However, in terms of the actual business of the mall, customers generally choose big category, small category and brand first, and finally choose to establish two indexes

(1)index(cat_id,brand_id)
(2)index(cat_id,shop_price)

You can even add (3) index (CAT)_ id,brand_ id,shop_ Price), 3 redundant indexes

However, the first two columns in (3) are the same as those in (1), so you can remove (1) and create two indexes

index(cat_id,price)andindex(cat_id,brand_id,shop_price);

For more information about mysql, readers interested in this site can see the following topics: summary of MySQL index operation skills, summary of MySQL common functions, collection of MySQL log operation skills, summary of MySQL transaction operation skills, collection of MySQL stored procedure skills, and summary of MySQL database lock related skills

I hope this article will be helpful to your MySQL database design.