mysql 测试时间限制_MySQL 大致测试更新时间

1：需求：把一个2千万条数据的一个表，随机更新其中的二十行需要大致多久？

DROP TABLE IF EXISTS test20;

CREATE TABLE test20(

id INT AUTO_INCREMENT PRIMARY KEY,

money DOUBLE,

createdtime DATETIME)ENGINE=INNODB DEFAULT CHARSET=UTF8;

DROP PROCEDURE IF EXISTS pro_test20;

DELIMITER &&

CREATE PROCEDURE pro_test20(IN inr int)

BEGIN

DECLARE i INT DEFAULT 0;

START TRANSACTION;

WHILE i

INSERT INTO test20(money,createdtime) values(234567891023,'2014-06-22 20:18:38');

SET i=i+1;

END WHILE;

COMMIT;

END &&

DELIMITER ;

mysql> call pro_test20(10000000);

Query OK, 0 rows affected (4 min 50.37 sec)

mysql> call pro_test20(10000000);

Query OK, 0 rows affected (8 min 18.97 sec)

#20次

INSERT INTO test20(money,createdtime) values(234567891023,'2013-03-33 20:18:38');

mysql> INSERT INTO test20(money,createdtime) values(234567891023,'2013-03-03 20:18:38');

Query OK, 1 row affected, 1 warning (0.02 sec)

UPDATE test20 SET money=money+10000000,createdtime=now()

WHERE createdtime='2013-03-03 20:18:38';

mysql> UPDATE test20 SET money=money+10000000,createdtime=now() WHERE id BETWEEN 200000 AND 200020;

Query OK, 21 rows affected (0.01 sec)

Rows matched: 21 Changed: 21 Warnings: 0

mysql> UPDATE test20 SET money=money+10000000,createdtime=now() where id in (36,457,67842,21,324,68,23,12,345,547,56,234,7263,233525,252522,3523,76666,1231,54);

Query OK, 19 rows affected (0.05 sec)

Rows matched: 19 Changed: 19 Warnings: 0

1：需求：把一个2千万条数据的一个表，随机更新其中的一千行需要大致多久？

存储过程测试1：(效率很低)

DROP PROCEDURE IF EXISTS pro_tab1000;

DROP TABLE IF EXISTS tab_test20;

CREATE TABLE tab_test20(id int)ENGINE=INNODB DEFAULT CHARSET=UTF8;

DELIMITER &&

CREATE PROCEDURE pro_tab1000(IN inr int)

BEGIN

DECLARE i INT DEFAULT 0;

WHILE i

INSERT INTO tab_test20 SELECT floor(RAND() * (SELECT MAX(id) FROM test20));

SET i=i+1;

END WHILE;

END &&

DELIMITER ;

CALL pro_tab1000(1000);

UPDATE test20 SET money=money+10000000,createdtime=now() WHERE id in (SELECT DISTINCT id FROM tab_test20);

存储过程测试2：2s

DELIMITER &&

DROP PROCEDURE IF EXISTS pro_test1000;

CREATE PROCEDURE pro_test1000(IN inr int)

BEGIN

DECLARE i INT DEFAULT 0;

DECLARE j INT ;

SELECT FLOOR(RAND()*(SELECT MAX(id) FROM test20)) into j;

WHILE i

UPDATE test20 SET money=money+10000000,createdtime=now() WHERE id = j;

SET i=i+1;

END WHILE;

END &&

DELIMITER ;

效率很低的语句：

SELECT id FROM test20 ORDER BY rand() LIMIT 10

mysql> SELECT id FROM test20 ORDER BY rand() LIMIT 10;

+----------+

| id |

+----------+

| 1876355 |

| 10266755 |

| 14746331 |

| 17533398 |

| 8164806 |

| 8438406 |

| 12884382 |

| 17285257 |

| 9817314 |

| 10273314 |

+----------+

10 rows in set (26.67 sec)

问题1：更新记录很多的时候，字段上有索引快还是没有索引快？