MySQL where id in (1,2,3,4,...) 的效率问题

SQL code

create table t_06 (
    id    int not null primary key,
    c1    varchar(30),
    i2    int
) engine = myisam; 

delimiter //

CREATE PROCEDURE prepareData_t_06 ()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i < 500000 DO
        insert into t_06 values (i,concat('AA',i),i);
        SET i = i + 1;
    END WHILE;
END;
//

delimiter ;

CALL prepareData_t_06();

select count(*) from t_06;
show index from t_06;
show table status like 't_06';

现在如果由程序在数组中提供 100 个 ID 如下
38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220,187731,365665,212799,227788,255724,384636,465766,417310,313148,483987,328761,402876,237274,249183,174185,28187,189155,259940,67800,60065,340172,311667,354861,182,305523,115981,365082,213915,47894,131301,198754,358852,112496,404423,486725,233123,322936,325337,125932,299260,128791,295663,469897,120580,347766,34859,364204,37597,268974,351155,256955,214013,309192,412394,216800,30315,411242,16678,233247,359013,401666,30792,452394,408649,14159,5519,91705,227648,120966,319599,351170,68129,368701,233566,144256,156172,41972,499687,390955,6549,298079,498230,196397,239493,242037

select * from t_06 where id in (38068,238833,308799,274344,299910,309823,337486,290812,56393,64413,492511,260426,58626,450987,499220



方法:

1。 直接使用 IN (38068,238833,308799 ... )
2。 将 (38068,238833,308799 ... ) 放入临时表，然后用 JOIN
3。 直接在程序中 执行多个 select * where id = 38068; select * from where id=238833; ...
4。 使用 inner join (select 38068 union all select 238833 union all ... )

结论:
在特大的高压下，会发现IN的效率很高！