MySQL查询有效选择多行？
您需要使用索引来有效地选择多行。让我们首先创建一个表-
mysql> create table DemoTable1501 -> ( -> Id int NOT NULL PRIMARY KEY, -> URL text -> );
这是创建索引的查询-
mysql> create index id_index on DemoTable1501(Id); Records: 0 Duplicates: 0 Warnings: 0
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1501 values(101,'www.facebook.com'); mysql> insert into DemoTable1501 values(110,'www.google.com'); mysql> insert into DemoTable1501 values(220,'www.gmail.com'); mysql> insert into DemoTable1501 values(350,'www.youtube.com');
使用select语句显示表中的所有记录-
mysql> select * from DemoTable1501;
这将产生以下输出-
+-----+------------------+ | Id | URL | +-----+------------------+ | 101 | www.facebook.com | | 110 | www.google.com | | 220 | www.gmail.com | | 350 | www.youtube.com | +-----+------------------+ 4 rows in set (0.00 sec)
以下是查询以有效地选择多行-
mysql> select * from DemoTable1501 -> where Id in(101,220,350);
这将产生以下输出-
+-----+------------------+ | Id | URL | +-----+------------------+ | 101 | www.facebook.com | | 220 | www.gmail.com | | 350 | www.youtube.com | +-----+------------------+ 3 rows in set (0.00 sec)
为了证明这一点，请使用SHOW命令，其中Handler_read_key使用4个ID中的3个-
mysql> SHOW STATUS LIKE 'Handler_%';
这将产生以下输出-
+----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 18 rows in set (0.00 sec)