在不使用索引的情况下左联接
在不使用索引的情况下左联接
提问于 2016-12-19 13:03:22
我试图连接两个表，条件是table1的值一列位于table2的两列的值之间。
表1
CREATE TABLE `values`(
`id` INT,
`name` VARCHAR(50),
`num_addr` BIGINT UNSIGNED
);表2
CREATE TABLE `ranges`(
`id` INT,
`range_name` VARCHAR(50),
`range_start` BIGINT UNSIGNED,
`range_end` BIGINT UNSIGNED,
INDEX `idx_start` (`range_start` ASC),
INDEX `idx_end` (`range_end` ASC),
INDEX `idx_range` (`range_start` ASC, `range_end` ASC)
);查询：
SELECT
`v`.`name`,
`v`.`num_addr`,
`r`.`range_name`
FROM
`values` `v`
LEFT JOIN `ranges` `r` ON `v`.`num_addr` BETWEEN `range_start` AND `range_end`查询的EXPLAIN EXTENDED显示不使用索引，信息“为每个记录检查范围(索引映射:0x7)”。这是一个性能问题，因为ranges表有超过500,000行，而且查询是时间敏感的。
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, r, ALL, idx_start,idx_end,idx_range, , , , 1, 100.00, Range checked for each record (index map: 0x7)实际上，FORCE INDEX ON JOIN会使事情变得更糟，因为优化器只看到建议的索引，而不使用它。
是否有一种方法可以对这样的联接使用索引？
补充说明：
- 将
BETWEEN更改为value >= range_start AND value <= range_end并不会更改执行计划。 - 删除
idx_start和idx_end索引并不能改善这种情况。 - 向
num_addr添加索引不会影响连接执行。 (range_start, range_end)的间隔可以重叠。- 有一些
num_addr不在一个范围内。 - 一个很好的类比是电话号码:在
ranges表中会有一个记录：('UK', 44000000000000, 44999999999999)和另一个('UK Vodafone', 44700000000000, 44799999999999)。
回答 1
Database Administration用户
发布于 2016-12-22 16:17:00
重叠范围是一个特别困难的优化问题。但是，这里有一种提供重要性能的技术，但它需要对模式进行重大更改。
添加另一个表；让我们称它为Prefixes。它包含两列：
prefix DECIMAL(4,0) NOT NULL,
range_id INT, -- for JOINing to `ranges`
PRIMARY KEY(prefix)然后，要查找数字4432109...，首先在Prefixes. This will lead to one or morerange.id`值中查找4432，以检查现有表。
请注意，对于您的示例，Prefixes中有100个条目用于“UK”，10个条目用于“UK Vodafone”。这意味着需要额外的代码来维护Prefixes。
这方面的一个变体是将其余的ranges列移到我的新表中，并去掉表ranges。(这取决于列的数量和性质、代码中的麻烦、前缀的大小、表的大小(S)等等)
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/158563
复制相关文章
相似问题
腾讯云开发者
