preface

Sometimes, in order to test the efficiency of a CERTAIN SQL statement or function, we need a large amount of test data to complete the function. We can use the method of data worm to simulate a large amount of data

Continuously insert the queried data into the specified data table. Typically, mysql worm replication is used to test table stress.

introduce

Data worm, scientific name (worm replication) :

As the name suggests, it copies itself and multiplies itself exponentially. Mysql worm replication is all about replication.Copy the code

Significance of worm replication:

  1. Copy data from an existing table to a new table

  2. It can quickly expand the data in the table to a certain order of magnitude, which is used to test the pressure and efficiency of the table.

grammar

Insert into (select * from (select * from (select * from); / / case insert into users select null, username, password and nickname, avatar, created_at, updated_at, deleted_at from users;Copy the code

test

Here is the test table structure:

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nickname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `avatar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

Query information fields in an existing table

Execute worm statement

Performed again

View the number of data items

The data are 2,4,8,16,32… That is, we are familiar with exponential growth. Then keep operating, you can make the data in the table swell to a certain order of magnitude in a short time, so as to achieve the purpose of testing the pressure of the table.

Simulating real data

The amount of data is satisfied, but the authenticity of data needs to be improved. The name, password, nickname, and profile picture of all users are not the same. Therefore, you need to modify the fields of name, password, nickname, and profile picture.

Modify the name | nickname:

Here we can set the name and nickname for test_id | test_name format, data and uniqueness. CONCAT = CONCAT; CONCAT = CONCAT; CONCAT = CONCAT;

Change password:

The password is realized using random number and MD5 built-in encryption function

Updated When:

Use MySQL built-in time function implementation

update users set username = concat('test',id),nickname = concat('testName',id),password=md5(rand()*100),updated_at=now();
Copy the code

The final numbers are as follows, which are fairly realistic.

Construct megabytes of data to use.