我有表存储800M行的电话号码。
column
region_code_id smallint(4) unsigned YES
local_number mediumint(7) unsigned YES
region_id smallint(4) unsigned YES
operator_id smallint(4) unsigned YES
id int(10) unsigned NO PRI auto_increment
我需要找到number.id
region_code_id = 119 and localnumber = 1234567
select * from numbers where numbers.region_code_id = 119 and numbers.local_number = 1234567;
此查询执行超过 600 秒。我怎样才能改进它?
更新
感谢您的不懈,我知道我需要此专栏的索引,一旦我获得具有更多 SSD 的服务器,我就会尝试这样做,现在我有可用的 1GB SSD 空间。如何找出索引将占用多少空间?
考虑在WHERE
子句中使用的列上添加INDEX
。
入手:
ALTER TABLE `numbers`
ADD INDEX `region_code_id_local_number`
(`region_code_id`, `local_number`);
注意:建立索引可能需要一些时间。
更改前后,执行说明计划进行比较:
EXPLAIN EXTENDED select * from numbers where numbers.region_code_id = 119 and numbers.local_number = 1234567;
引用:
MySQL 如何使用索引
对于此查询:
select *
from numbers
where numbers.region_code_id = 119 and
numbers.local_number = 1234567;
您想要在numbers(region_code_id, local_number)
或numbers(local_number, region_code_id)
上建立索引。 列的顺序无关紧要,因为两列的条件相等。
create index idx_numbers_region_local on numbers(region_code_id, local_number);
我同意INDEX(region_code_id, local_number)
(按任一顺序)对于此问题是强制性的,但我正在坚持我的鼻子以进一步推进它。 这对不是"独特"的吗? 或者表中有重复的数字? 如果它是唯一的,那么摆脱id
并使该对PRIMARY KEY(region_code_id, local_number)
。 更改后,该表可能会更小。
回到你的"多大"的问题。 现在桌子有多大? 也许 40GB? 二级索引(如最初提议的那样)可能会增加大约 20GB。 而且您需要 20-60GB 的可用磁盘空间来执行ALTER
。 这取决于是否可以在该版本中"就地"添加索引。
更改 PK(如我建议的那样)将导致表的 GB 略小于 40GB。 执行ALTER
将需要40GB的可用空间。
一般来说(悲观地),计划一个同时需要原始表和新表在磁盘上的ALTER
。 这包括数据和索引的完整副本。
(附带问题:您确定local_number
到处都限制为 7 位数字吗?
这个问题的另一种方法... 要计算InnoDB中表或索引的大小,请将数据类型大小相加(MEDIUMINT
为3个字节,VARCHAR
为一些平均值等)。 然后乘以行数。 然后乘以 4;这将为您提供所需的大致磁盘空间。 (通常 2-3 足以满足最后一个乘数。
更改 PK 时,只需一步到位:
ALTER TABLE foo
DROP PRIMARY KEY,
ADD PRIMARY KEY(region_code_id, local_number);
更改 PK 不能"就地"完成。
编辑(主要针对其他读者)
@berap指出,id
是出于其他目的所必需的。 因此,放弃id
并切换PK不是一种选择。
但是,这有时是一种选择(在这种情况下可能不是):
ALTER TABLE foo
DROP PRIMARY KEY,
ADD PRIMARY KEY(region_code_id, local_number),
ADD INDEX(id);
笔记:
- 即使只有
INDEX
,id..AUTO_INCREMENT
也将继续工作. - 有问题的
SELECT
会更有效率,因为它是PK。 -
SELECT .. WHERE id = ...
效率会降低,因为id
是辅助密钥。 - 无论哪种方式,表的大小都相同;无论哪种方式,辅助键的大小也相同 - 因为每个辅助键也包含 PK 列。 (此说明是特定于InnoDB的。