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<inr DO
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<inr DO
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<inr DO
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:更新记录很多的时候,字段上有索引快还是没有索引快?