MySQL 更新走全表和索引的评估记录数

#!/usr/bin/perl   
use DBI;  
$db_name='scan';  
$ip='127.0.0.1';  
$user="root";  
$passwd="1234567";  

$dbh = DBI->connect("dbi:mysql:database=$db_name;host=$ip;port=3306",$user,$passwd,{  
                          RaiseError => 1,  
                          AutoCommit => 0  
                        }) or die "can't connect to database ". DBI-errstr;  
	for ($i = 1;$i <= 10;$i++){ 					
eval{  
$dbh->do("insert into t2(id,info) values('$i','a$i')") ;   
$dbh->commit();};  
if( $@ ) {  
    #warn "Database error: $DBI::errstr
";  
             $dbh->rollback(); #just die if rollback is failing   
             };  
			 };
                     $dbh->disconnect;
					 
					 

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `info` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB AUTO_INCREMENT=8222 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select id,count(*) from t2 group by id;
+------+----------+
| id   | count(*) |
+------+----------+
|    1 |       10 |
|    2 |       10 |
|    3 |       10 |
|    4 |       10 |
|    5 |       10 |
|    6 |       10 |
|    7 |     1280 |
|    8 |     1280 |
|    9 |     1280 |
|   10 |     1280 |
+------+----------+
10 rows in set (0.00 sec)


Session 1:
mysql> explain update t2 set id=100 where id=1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t2    | index | NULL          | PRIMARY | 4       | NULL | 5180 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


Session 2；

Database changed
mysql> insert into t2(id,info) values(11,'a11'); ---HANG


/*继续测试2****************************
Session1:
mysql> create index t2_idx1 on t2(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain update t2 set id=100 where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | t2    | range | t2_idx1       | t2_idx1 | 5       | const |   10 | Using where; Using temporary |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
1 row in set (0.00 sec)

Session2:
mysql> insert into t2(id,info) values(11,'a11');
Query OK, 1 row affected (0.01 sec)