mysql "IN“运算符删除几行还是几个查询？哪个更快？

Question

我使用的是MySQL。表引擎是MyIsam

哪种方法更快？

DELETE FROM TABLE WHERE id IN (1,2,3);

或

DELETE FROM TABLE WHERE id = 1; 
DELETE FROM TABLE WHERE id = 2;
DELETE FROM TABLE WHERE id = 3;

id字段为主键

哪一个运行得更快？

Answer 1

在中应该更快，因为在第二个解决方案中，mysql会在每次查询后更新索引和移动数据块，而第一个查询只会发生一次。

这里有一些关于MySQL的测试(对于一个有一个int列和3个varchars的表，用随机数据填充，对WHERE...makes中列的索引没有索引的意义，因为在这两个cases...but中花费更多的时间仍然比3个查询慢得多)。

mysql> call prepare_data();
Query OK, 1 row affected (34.25 sec)

mysql> delete from t1 where trt in (5, 6, 7);
Query OK, 300049 rows affected (5.25 sec)

mysql> call prepare_data();
Query OK, 1 row affected (35.18 sec)

mysql> delete from t1 where trt=5;delete from t1 where trt=6;delete from t1 where trt=7;
Query OK, 99961 rows affected (2.25 sec)

Query OK, 99842 rows affected (2.38 sec)

Query OK, 99558 rows affected (2.69 sec)

mysql> 

因此，这三个查询花费了更多的时间(7.39秒与5.25秒的IN，增加了40% )。以下是prepare_data过程：

DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  TRUNCATE TABLE t1;
  WHILE i < 1000000 DO
    INSERT INTO t1 (a, b, c, trt) VALUES ('fasdfadsf', 'asdfasdfa', 'asdfasdf', FLOOR( 1 + RAND( ) *10 ));
    SET i = i + 1;
  END WHILE;
END $$
DELIMITER ;

Answer 2

SQL Server2008IBMExpress，R2 t60p笔记本电脑，核心双核T2500 2.0 HDD处理器，7200rpm硬盘

100000行( col2上没有索引，id为col1，随机数在0到10之间的col2 )

测试1:三个查询

1. Insert 100000随机行(40秒)
2. 测试查询表
3. Insert 100000随机行。(38秒)表测试查询(253ms)
4. Truncate
6. 插入100000个随机行。(39秒)表测试查询(253ms)
7. Truncate

测试2:单查询

1. Insert 100000随机行(37秒)
2. Tested
4. Insert 100000随机行。(39秒)表测试查询(327ms)
5. Truncate
7. 插入100000随机行。(38秒)
8. 测试查询(313ms)

用于插入的查询：

    truncate table testTable

    DECLARE @counter int, @col2 int


    SELECT @counter=0, @col2=RAND(@@spid + cpu + physical_io)
    FROM master..sysprocesses where spid=@@spid

    WHILE (@counter < 1000000)
         BEGIN
         SELECT @counter=@counter + 10,   
         @col2= CONVERT(int, RAND() * 100) % 10


        INSERT testTable VALUES (@counter, @col2)
    END

用于测量的查询：

    DECLARE @StartTime datetime,@EndTime datetime
    SELECT @StartTime=GETDATE()

    DELETE FROM testTable WHERE col2 = 7;
    DELETE FROM testTable WHERE col2 = 8;
    DELETE FROM testTable WHERE col2 = 9;
    --DELETE FROM testTable where col2 in (7,8,9);


    SELECT @EndTime=GETDATE()

    SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in microseconds]