A misunderstanding of MySQL optimization techniques

With regard to SQL optimization techniques, you may have seen n versions, especially for junior-level programmers. If you do not have a bit of analytical practice, just take it as a Bible and practice it in your work, you are most likely to be out of the hole. When the code is running, the loop is unresponsive and the damage to the project results in economic loss.

Nonsense not much to say directly.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/90/F6/wKioL1jzcDvxLbs2AACDHofFibU963.png "title=" 2017-04-16 21-15-22 screen. png "alt=" Wkiol1jzcdvxlbs2aacdhoffibu963.png "/>

It seems that there is no problem with a cursory glance at the above technique. But is that the truth?

The conclusion is, of course, negative. and See example Analysis:

        

create table  ' T_auxiliary_info '   (   ' id '  int (one)  unsigned not null  AUTO_INCREMENT,   ' ac_id '  tinyint (3)  unsigned NOT NULL COMMENT  ' Category ID ',   ' name '  varchar ( NOT NULL DEFAULT  '  COMMENT  ' name '),    ' number '  smallint (6)  unsigned NOT NULL DEFAULT  ' 1 '  comment   ' number ',   ' attr '  varchar ($)  NOT NULL DEFAULT  '  COMMENT  ' property ' ,   ' Fdbid '  int (Ten)  unsigned NOT NULL COMMENT  ' user ID ',   ' Status '  tinyint (1)  unsigned NOT NULL DEFAULT  ' 1 '  COMMENT  ' state: 1 valid, 0 invalid ',    ' Stock_type '  tinyint (1)  unsigned NOT NULL DEFAULT  ' 0 '  comment   ' stock type: 1 stock items, 2 raw materials, 3 weeks transfer material ',  primary key  (' id '), #请注意这里的索引   KEY  ' uniq_cid _acid '   (' fdbid ', ' ac_id'))  engine=innodb auto_increment=645101 default charset=utf8 row_format=dynamic 

Above is an ordinary business table, carefully looking at the index set in the table:

PRIMARY key (' id '), #主键索引 key ' uniq_cid_acid ' (' fdbid ', ' ac_id ') #联合索引

Use the above in or not in to practice the following, and see the actual effect through the Explain execution Plan tool. (In order to be fair, I don't use the primary key ID, and the data in the in operation is not contiguous.) ）

SELECT * from T_auxiliary_info where fdbid in (' 1000 ', ' 1500 ', ' 1234 ', ' 5155 ', ' 6789 ', ' 3423 ', ' 5368 ', ' 245645 ');

In the above SQL, we use the field ' Fdbid ' contained in the Federated Index ' Uniq_cid_acid ' as the search criteria

It's time to witness the miracle.

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/90/F9/wKiom1jzdaPAsg35AAB8Daxo-zs623.png "title=" 2017-04-16 21-45-07 screen. png "alt=" Wkiom1jzdapasg35aab8daxo-zs623.png "/>

By executing the plan, we can clearly see that this SQL retrieval type is simple and simple to retrieve, belongs to the scope query, and has been used to index uniq_cid_acid, and there is no full table scan (the number of rows scanned is 2804, and the number of data bars in this table is 645101).

You can conclude that not all in queries in SQL are scanned in full table. This reverses the conclusion that in will cause a full table scan.

So under what circumstances can you use the in operation as an index, not a full table scan?

1. In must be a field with an index.

2. The data in (...) is best quoted, even if the field type is a number.

It may be questioned here that the field fdbid in the table above is of type int, do numeric fields also need to be quoted? I laughed without words.

SELECT * from T_auxiliary_info where fdbid not in (1000,1500,1234,5155,6789,3423,5368,245645);

The truth is here:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/90/FA/wKioL1jzeajC3kv_AACBbcpzw3Y564.png "title=" 2017-04-16 22-02-45 screen. png "alt=" Wkiol1jzeajc3kv_aacbbcpzw3y564.png "/>

This is the full table scan!!!

So the final conclusion is:

1. The field that uses the in condition must be an indexed field.
   

2. And, IN (... The values in parentheses are quoted, regardless of the field type.

Welcome to discuss the advice.

This article is from the "My PHP path" blog, so be sure to keep this source http://phpme.blog.51cto.com/663593/1916492

A misunderstanding of MySQL optimization techniques