mysql - 选择查询的复杂性，其中列表中的 id 且 id 为表建立索引

Question

以下查询的复杂性(每个查询扫描的行数)是多少？

select col_name from table where id in (list).

表根据 id 建立索引。是否仅扫描和获取列表中具有 id 的每一行，或者仅访问列表中具有给定 id 的行？

Answer 1

EXPLAIN 可能会给您一个猜测。以下将是准确的:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

示例:

mysql> SELECT city, population FROM canada WHERE id IN (295560,295007,297104,293500,293583,297001,291726,296573,296239,295210,293022,295192,293038);
+----------------+------------+
| city           | population |
+----------------+------------+
| Abbotsford     |     151685 |
| Drummondville  |      59489 |
| Duncan         |      22199 |
| Glace Bay      |      21504 |
| Grande Prairie |      41463 |
| New Glasgow    |      20322 |
| Orangeville    |      32641 |
| Ottawa         |     874433 |
| Prince George  |      65558 |
| Saskatoon      |     198957 |
| Stratford      |      30234 |
| Waterloo       |     110800 |
| White Rock     |      66450 |
+----------------+------------+
13 rows in set (0.01 sec)

mysql> show session 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           | 13    |  -- Note
| 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)

请注意，读取了 13 行，而不是表中的 5484 行。(这需要 id 上的 KEY。)