Shulou(Shulou.com)05/31 Report--

This article mainly introduces "mysql's support for unique_checks". In daily operation, I believe many people have doubts about mysql's support for unique_checks. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "mysql's support for unique_checks"! Next, please follow the editor to study!

The parameter unique_checks is used to check whether the primary key and unique key are duplicated.

There is a big difference between tokudb and innodb for this parameter.

It makes no difference when you turn on (unique_checks=1 or on). All need to be checked for uniqueness.

When you turn off (unique_checks=0 or off), there is a difference.

When unique_checks is off, no matter what the engine, there are two cases in which the performance improvement is objective:

1. Only primary key and auto-increment and secondary index, no unique index, turn off unique_checks at this time, the performance of batch insertion is still very optimistic.

If 2.primary key has a large amount of random data (that is, the discrete value is very large), or unique index has a large amount of random data (large discrete value), the performance of batch insertion will be greatly improved.

Let's talk about the difference between innodb and tokudb:

Innodb:off just shuts down secondary indexs. In fact, this sentence is a bit superfluous. As long as it is not unique index, whether the inspection actually has little difference. Officials say shutting down unique_checks will improve the performance of bulk insert. Secondary indexs. I studied it carefully. During bulk inserts, no matter how secondary index is organized or updated (the index will be updated or may be reorganized after inserting data), primary key and unique index need to be updated or reorganized. Therefore, under innodb, the performance improvement of this parameter is not very significant.

The state of tokudb:off is different. In the off state, only check whether the primary key is duplicated. At present, the index of tokudb is still in the research stage, so only refer to the official manual: in the state of off, it will improve the efficiency of batch insert (provided there are not only primary, but also other indexes, otherwise, the effect will not be obvious).

When unique_checks is off, unique index and other indexes can be repeated except that primary cannot be repeated. This is a super bug. All allowed to repeat, still unique? And in two insert with the same primary but different unique, the latter one will overwrite the data of the previous one, which is too lame. Such as the following example:

Mysql > show create table T3\ G

* * 1. Row *

Table: t3

Create Table: CREATE TABLE `t3` (

`id`int (11) NOT NULL

`id2` int (11) DEFAULT NULL

`d3` int (11) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `d3` (`d3`)

KEY `sec_ key` (`id2`)

) ENGINE=TokuDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Mysql >

Mysql >

Mysql > select * from T3

+-- +

| | id | id2 | d3 | |

+-- +

| | 1 | NULL | NULL |

| | 2 | 2 | NULL |

| | 3 | 3 | 3 |

| | 4 | 4 | 3 | |

+-- +

4 rows in set (0.00 sec)

Mysql >

Mysql >

Mysql > insert into T3 values (2mem1pl 1)

Query OK, 1 row affected (0.14 sec)

Mysql > select * from T3

+-- +

| | id | id2 | d3 | |

+-- +

| | 1 | NULL | NULL |

| | 2 | 1 | 1 |

| | 3 | 3 | 3 |

| | 4 | 4 | 3 | |

+-- +

4 rows in set (0.00 sec)

It can be known that there are hidden dangers in shutting down unique_checks. If a large number of primary duplicates, the previous data will be overwritten (only the last duplicate data will be retained), and the secondary index will be easily corrupted. At this time, the index may need to be rebuilt, resulting in a greater performance loss.

At this point, the study on "mysql's support for unique_checks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.