Performance analysis of different field types updated by SQL update

When MySQL uses update to update data, if the type of the condition field is Numeric but the parameter is numeric and the condition does not match the record, it will cause serious performance problems.

. As follows:

The Code is as follows:	Copy code
1. update test007 set key1 = key1 + '1' where id = 200000; 2. update test007 set key1 = key1 + '1' where id = '000000 ';

Note that the difference in the preceding query statement is that the parameter type is different. The former is numeric, the latter is numeric, And the id is 200000. This record does not exist.

If the second query is used and the record does not exist, the query will have a serious efficiency problem. The test is as follows:

Ii. Test practices
1. Create a test data table test007

The Code is as follows:	Copy code
Create table 'test007 '( 'Id' int (10) unsigned not null AUTO_INCREMENT, 'Key1' int (10) not null default '0 ', 'Key2' int (10) not null default '0 ', 'Key3' int (10) not null default '0 ', Primary key ('id ') ) ENGINE = MyISAM AUTO_INCREMENT = 0 default charset = gbk

2. Create Test Data

The Code is as follows:	Copy code
<? Php $ Db = mysql_connect ("localhost", "root ",""); Mysql_select_db ("test "); Set_time_limit (0 ); $ Table = 'test007 '; For ($ I = 0; I I <1000000; $ I ++ ){ $ K1 = rand (random ); $ K2 = rand (0, 3 ); $ K3 = rand (1,100000 ); Mysql_query ("insert into $ table (key1, key2, key3) values ('". $ k1 ."','". $ k2 ."','". $ k3. "')", $ db ); } ?>

Note: 1000000 (100 million records) records are created, and the data size is 16.2 MB.

3. When the test parameter type is Numeric

The Code is as follows:

Copy code

Mysql & gt; update test007 set key1 = key1 + '1' where id = 10000001; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 Query statement Performance + ---------------------- + ---------- + | Status | Duration | + ---------------------- + ---------- + | Startling | 0.000104 | | Check permissions | 0.000005 | | Opening tables | 0.000010 | | System lock | 0.013440 | | Table lock | 0.000004 | | Init | 1, 0.000035 | | Updating | 0.000020 | | End| 0.000034 | | Query end | 0.000002 | | Freeing items | 0.000028 | | Logging slow query | 0.000001 | | Cleaningup | 0.000005 | + ---------------------- + ---------- + 12 rows in set (0.00 sec)

Description: The field type of the primary key id is Numeric.

4. test parameter type

The Code is as follows:

Copy code

Mysql> update test007 set key1 = key1 + '1' where id = '000000 '; Query OK, 0 rows affected (0.03 sec) Rows matched: 0 Changed: 0 Warnings: 0 Query statement Performance + ---------------------- + ---------- + | Status | Duration | + ---------------------- + ---------- + | Startling | 0.000108 | | Check permissions | 0.000005 | | Opening tables | 0.029382 | | System lock | 0.000003 | | Table lock | 0.000003 | | Init | 1, 0.000039 | | Updating | 0.000074 | | End| 0.000022 | | Query end | 0.000002 | | Freeing items | 0.000033 | | Logging slow query | 0.000001 | | Cleaningup | 0.000001 | + ---------------------- + ---------- + 12 rows in set (0.00 sec)

If the type of the updated field does not match the assigned value when the UPDATE record is used, MySQL converts the value to a value of the corresponding type. If this field is of the numerical type and the value is assigned beyond the maximum range of the Data Type, MySQL converts the value to the maximum or minimum value of this range. If the string is too long, MySQL will cut off the extra strings. If you set a non-empty field to null, set this field to their default value. The default value of the number is 0, and the default value of the string is a null string (not null, it is "").

Because the data volume in the test environment is small, the test results are not obvious, but the key is to pay attention to the consistency between the field type and the parameter type during development, avoid extra data overhead during data update and deletion under specific circumstances.

5. Test the large data volume. The process is as follows:

The Code is as follows:

Copy code

Step 1: create a data table Create table 'test008 '( 'Id' int (10) unsigned not null AUTO_INCREMENT, 'Key1' int (10) not null default '0 ', 'Key2' text, 'Key3' int (10) not null default '0 ', Primary key ('id ') ) ENGINE = MyISAM AUTO_INCREMENT = 0 default charset = gbk Step 2: Create Test Data Create 1000000 (100 million) records with a data size of 2.07 GB (2,224,000,000 bytes) Step 3: performance comparison of the two queries Mysql> update test008 set key1 = key1 + '1' where id = '2013 '; Query OK, 0 rows affected (0.03 sec) Rows matched: 0 Changed: 0 Warnings: 0 Mysql & gt; update test008 set key1 = key1 + '1' where id = 100000001; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 Step 4: Create an index Mysql> alter table test008 add index key3 (key3 ); Query OK, 1000000 rows affected (5 min 54.33 sec) Records: 1000000 Duplicates: 0 Warnings: 0 Step 5: Test different conditions Mysql> update test008 set key1 = key1 + '1' where id = ''; Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0 Mysql> update test008 set key1 = key1 + '1' where id = '2013 '; Query OK, 0 rows affected (44.58 sec) Rows matched: 0 Changed: 0 Warnings: 0 Mysql & gt; update test008 set key1 = key1 + '1' where id = 12321232123; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 Mysql> update test008 set key1 = key1 + '1' where id = 'test '; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

Note: Some of the conditions in the above test have exceeded the range of the id field.