Programmer’s day

Programmers should deal a lot with dbAs on a daily basis, because every now and then he will throw you a slow SQL and ask you to optimize.

But for you just in the new village, this is undoubtedly a big problem, because they do not know how to optimize the index, how to do it?

But do not be afraid, today if you learn my rule, any SQL optimization is not difficult to you, let’s get to the topic!

First of all, why do we use indexes

I believe you will be asked this question during the interview process.

An index is an ordered data structure. Because of its sequential nature, we can query a piece of data in a mass of data, which will make it more efficient. But the price is that the index takes up some space, and mysql uses a space-for-time strategy.

So, what is this law?

Since it is dry goods, I will not keep in suspense, this law is the most classic: the leftmost matching principle

Here’s the principle:

This principle is mainly aimed at the establishment of joint indexes, and the optimization in this article is also mainly aimed at such indexes.

Select a,b,c from t where a=1 and b=2. Select a,b,c from t where a=1 and b=2

Now that you know the leftmost rule, what SQL can cause a joint index query to fail and how to determine if all columns in the joint index are valid

How to use the leftmost matching rule to help you get a high salary 

CREATE TABLE `test_left_index` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) COLLATE utf8_bin NOT NULL,

  `age` int(11) NOT NULL,

  `address` varchar(50) COLLATE utf8_bin NOT NULL,

  `email` varchar(255) COLLATE utf8_bin DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `name_age_addr` (`name`,`age`,`address`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;



INSERT INTO `test_index`.`test_left_index`(`id`, `name`, `age`, `address`) VALUES (1, 'yang'Ten,Hefei city, Anhui Province);

INSERT INTO `test_index`.`test_left_index`(`id`, `name`, `age`, `address`) VALUES (2, 'zhang'Ten,'Anqing, Anhui Province');

Copy the code

explain select name from test_left_index where NAME =’111′ and age =10

  • Whether to go index yes

  • Which fields use the indexes name and age

  • Index Length 156

  • Cause analysis,

    According to the execution plan, we can see that the SQL meets the left-most prefix matching principle, and takes the index;

    Index length = Name x 3 + 2 + age The length is 156. Based on the length, you can determine that the SQL uses the combined index of name and age

explain select name from test_left_index where NAME =’111′ and age > 10

  • Whether to go index

    is

  • Which fields use the indexes name and age

  • Index Length 156

  • Cause analysis,

    According to the left-most prefix matching principle, we can see that SQL first uses the name constant filter, the filtered data, age is in order, so when the range query, can be directly compared with the index from left to right, so the index will be followed.

    Index length = Name x 3 + 2 + age The length is 156. Based on the length, you can determine that the SQL uses the indexes of name and age

Explain select name from test_left_index where name =’111′ and age > 10 and address =’ anhui ‘

  • Whether to go index

    is

  • Which fields use the indexes name and age

  • Index Length 156

  • Cause analysis,

    According to the left-most prefix matching principle, we can see that SQL first uses name constant filtering, filtered data, age is in order, so when the range query, can be directly compared with the index from left to right, so the index will be followed; After filtering according to the age field, the Adrees field is out of order, because age will match more than one, resulting in the address corresponding to age is out of order, we can see the figure:

According to the analysis, the second part is the result filtered by name and age, which is unordered and cannot be sorted by index. Mysql will select full table scan, resulting in invalid address index field

Index length = Name x 3 + 2 + age The length is 156. Based on the length, you can determine that the SQL uses the indexes of name and ageCopy the code

explain select name from test_left_index where NAME like ‘y%’ and age = 10

  • Whether to go index

    is

  • Which fields use the indexes name and age

  • Index Length 156

  • Cause analysis,

    The author’s previous understanding of the query after like and range are the same, the field itself will be indexed, but the field after like will not be indexed, because according to the leftmost matching principle can be drawn such a conclusion.

    However, we still have to follow the actual mysql optimization results. For specific reasons, we can use the trace tool to analyze:

 set session optimizer_trace="enabled=on",end_markers_in_json=on;

select name from test_left_index  where NAME like 'y%' and age = 10;

select * from information_schema.OPTIMIZER_TRACE;

Copy the code

According to the final optimization result of mysql, it is found that this statement uses all indexes, which is more efficient. It may be because of the small amount of data. Whether to choose indexes in the end depends on the final optimization result of mysql.

Index length = Name x 3 + 2 + age The length is 156. Based on the length, you can determine that the SQL uses the indexes of name and ageCopy the code

SQL > select * from ‘in’; SQL > select * from ‘like’; SQL > select * from ‘in’;

explain select name from test_left_index where NAME =’yang’ and address=’1111′;

  • Whether to go index

    is

  • Which fields use index name

  • Index Length 152

  • Cause analysis,

Because we create is the name for the order of the joint index – > age – > address, you can see this SQL to skip the age, so after name to filter the data, the age is according to the order, but the address is out-of-order, through the figure is very easy to see:

Insert a picture description here

Address cannot be queried in index order, so the field does not follow the index. This is similar to a range query, where the index cannot be used for the fields following the range query.

Let’s do one last one

explain select name from test_left_index where NAME =’yang’ and address=’1111′ and age= 10

  • Whether to go index

    is

  • Which fields use the indexes name, age,address

  • Index Length 308

    Calculation method: 3 x Name +2+3 x Address +2+4 (Age length) =308

  • Cause analysis,

You are not very surprised, why add a field, and will go to the index? Mysql > select * from ‘where’ where (select * from ‘where’, select * from ‘where’);

conclusion

So we can finally come to the conclusion: when writing SQL, try to follow the leftmost matching principle, use the index efficiently; In addition, the type,key and key_len fields in the explain execution plan results can be used to determine whether to use the index. Key_len is mainly used in the joint index to determine which fields in the joint index use the index. Finally, attach the index length calculation formula:

  • String • char(n) n length of bytes • varchar(n) 2 bytes specifies the length of a character string. If it is utf-8, the value is 3n+2
  • Value type • TinyINT 1 byte • SmallINT 2 byte • INT 4 byte • BIGINT 8 byte
  • Time type • Date 3 bytes • TIMESTAMP 4 bytes • Datetime 8 bytes

If the field is allowed to be empty, the length of the corresponding field needs to be added by one byte

Welfare big broadcast

Follow the wechat official account “AI Coder” to receive the interview materials and the latest full set of micro service courses