Shulou(Shulou.com)06/01 Report--

This article mainly introduces what are the misunderstandings of mysql optimization skills, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what misunderstandings mysql optimization skills have.

There seems to be no problem with a cursory glance at the above technique. But is that the truth?

The conclusion is, of course, negative. Let's take a look at the example analysis:

CREATE TABLE `troomauxiliaryinfo` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `fdbid` tinyint (3) unsigned NOT NULL COMMENT 'category ID', `name`varchar' 'COMMENT' name', `number`smallint (6) unsigned NOT NULL DEFAULT'1' COMMENT 'number', `attr`varchar 'NOT NULL DEFAULT' 'COMMENT' attribute', `fdbid`int (10) unsigned NOT NULL COMMENT 'user ID', `status` tinyint (1) unsigned NOT NULL DEFAULT' 1' COMMENT 'status: 1 valid 0 invalid', `stock_ type` tinyint (1) unsigned NOT NULL DEFAULT'0' COMMENT 'inventory type: 1 inventory goods 2 raw materials, 3 turnover materials', PRIMARY KEY (`id`), # Please note the index KEY `uniq_cid_ acid` (`fdbid`, `ac_ id`) ENGINE=InnoDB AUTO_INCREMENT=645101 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC here

The above is a common business table. Take a closer look at the indexes set in the table:

PRIMARY KEY (`id`), # primary key index KEY `uniq_cid_ acid` (`fdbid`, `ac_ id`) # Union index

Then use the above in or not in to practice the following, through the explain execution plan tool to see the actual effect. (to be fair here, 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 (in sql above, we use the field `fdbid` contained in the federated index `fdbid` as the search condition.

The time has come to witness miracles.

Through the execution plan, we can clearly see that the retrieval type of this sql is simple retrieval, belongs to scope query, and index uniq_cid_acid has been used, and there is no full table scan (the number of rows scanned is 2804, while the number of data items in this table is 645101).

It can be concluded that not all in queries in sql are fully scanned. This overturns the conclusion that in will lead to full table scans.

So under what circumstances can indexes be used with in operations without a full table scan?

A: the field of in must be a field with an index.

Ps: in (...) It is best to quote the data in, even if the field type is numeric.

Take a look at not in

Select * from t_auxiliary_info where fdbid not in (1000, 1400, 1234, 5155, 6789, 3423, 5368, 245645)

Here's the truth:

Not in does scan the entire table.

After reading the above about mysql optimization skills, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.