SQL Update update performance analysis for different field types

。 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 = ' 200000 ';

Note the difference between the above query statement is the type of parameter, the former is numeric, the latter is character type, and ID 200000 does not exist.

If you use the second query and the record does not exist, this query will have a serious efficiency problem, and the test is as follows:

Second, test practice
1, create a test data table test007

The code is as follows	Copy Code
CREATE TABLE ' test007 ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' Key1 ' int (a) not NULL DEFAULT ' 0 ', ' Key2 ' int (a) not NULL DEFAULT ' 0 ', ' Key3 ' int (a) 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 <1000000; $i + +) { $k 1 = rand (10000,300000); $k 2 = rand (0,3); $k 3 = rand (1,100000); mysql_query ("INSERT into $table (Key1,key2,key3) VALUES ('") $k 1. "', '". $k 2 "', '". $k 3. "", $db); } ?>

Description: Create 1000000 (100W) record with data size of 16.2 MB

3, the test parameter type is the case of the digital type

The code is as follows

Copy Code

mysql> Update test007 set key1=key1+ ' 1 ' where id=10000001; Query OK, 0 rows Affected (0.00 sec) Rows matched:0 changed:0 warnings:0 Performance Scenarios for Query statements +----------------------+----------+ | Status | Duration | +----------------------+----------+ | Starting | 0.000104 | | Checking Permissions | 0.000005 | | Opening Tables | 0.000010 | | System Lock | 0.013440 | | Table Lock | 0.000004 | | init | 0.000035 | | Updating | 0.000020 | | End | 0.000034 | | Query End | 0.000002 | | Freeing items | 0.000028 | | Logging Slow Query | 0.000001 | | Cleaning Up | 0.000005 | +----------------------+----------+ Rows in Set (0.00 sec)

Description: The field type of the primary key ID is numeric

4, the test parameter type is character type case

The code is as follows

Copy Code

mysql> Update test007 set key1=key1+ ' 1 ' where id= ' 100000001 '; Query OK, 0 rows affected (0.03 sec) Rows matched:0 changed:0 warnings:0 Performance Scenarios for Query statements +----------------------+----------+ | Status | Duration | +----------------------+----------+ | Starting | 0.000108 | | Checking Permissions | 0.000005 | | Opening Tables | 0.029382 | | System Lock | 0.000003 | | Table Lock | 0.000003 | | init | 0.000039 | | Updating | 0.000074 | | End | 0.000022 | | Query End | 0.000002 | | Freeing items | 0.000033 | | Logging Slow Query | 0.000001 | | Cleaning Up | 0.000001 | +----------------------+----------+ Rows in Set (0.00 sec)

When you update a record by using update, MySQL converts the value to the corresponding type of value if the type of the field being updated does not match the assigned value. If the field is a numeric type and the assignment exceeds the maximum range of the data type, MySQL converts the value to the maximum or minimum value for that range. If the string is too long, MySQL truncates the extra string. If you set a non-empty field to null, set the field to their default value, the default value for the number is 0, and the string's default value is an empty string (not NULL, is "").

Because of the small amount of data in the test environment, the results of the test are not obvious, but the key is to be aware of the consistency of the field type with the parameter type during the development process, and to avoid the extra overhead of the data in the process of updating and deleting under certain circumstances.

5, test the large amount of data, the process is as follows

The code is as follows

Copy Code

First step: Create a datasheet CREATE TABLE ' test008 ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' Key1 ' int (a) not NULL DEFAULT ' 0 ', ' Key2 ' text, ' Key3 ' int (a) not NULL DEFAULT ' 0 ', PRIMARY KEY (' id ') ) Engine=myisam auto_increment=0 DEFAULT CHARSET=GBK Step Two: Create test data Create a 1000000 (100W) record with a data size of 2.07 GB (2,224,000,000 bytes) Step three: Two query performance comparison mysql> Update test008 set key1=key1+ ' 1 ' where id= ' 100000001 '; Query OK, 0 rows affected (0.03 sec) Rows matched:0 changed:0 warnings:0 mysql> 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 Fourth: 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 Fifth: test for 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= ' 12321232123 '; Query OK, 0 rows affected (44.58 sec) Rows matched:0 changed:0 warnings:0 mysql> 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 test above have exceeded the scope of the ID field