我在一个表上做一个选择,查找在上界和下界之间匹配的行
SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;(简化的) create表是:
CREATE TABLE `ranges` (
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
`hi` varchar(15) DEFAULT NULL,
`hilong` bigint(20) DEFAULT NULL,
`lo` varchar(15) DEFAULT NULL,
`lolong` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `hilong` (`hilong`),
KEY `lolong` (`lolong`)
) ENGINE=InnoDB AUTO_INCREMENT=234447 DEFAULT CHARSET=utf8说明它没有使用索引:
mysql> explain SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | ranges | NULL | ALL | hilong,lolong | NULL | NULL | NULL | 7232 | 24.83 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+即使我试图强迫它,它也只使用一个索引
mysql> explain SELECT * FROM ranges force index (hilong,lolong) WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | ranges | NULL | range | hilong,lolong | hilong | 9 | NULL | 2757 | 65.14 | Using index condition; Using where |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+这似乎不对,我如何让MySQL同时使用两个索引,这似乎是不必要地扫描行来查找这个简单的查询
发布于 2017-04-24 03:53:41
每当您使用AND子句并选择确切的字符串/整数类型时,使用的都是等于=。综合指数法是一种行之有效的方法。
试着拥有composite index on hilong and lolong。查看查询是否选择索引,因为在它们自己的范围内有两个不同的列,我不确定它是否有用。
这主要取决于应用程序db体系结构是如何定义的。如果我是你的话。
发布于 2017-04-24 04:00:25
两列之间的检查本质上是不可优化的。优化器不知道有多少对匹配,所以它一直持续到表的末尾。
只要有一个专栏,做很多额外的工作,就有可能解决这个问题。使用IP地址范围执行此操作。
发布于 2019-12-24 10:09:18
让我们重新表述这个问题:
SELECT *
FROM ranges
WHERE 2091484391 BETWEEN lolong AND hilong;指数,如:
KEY lolong_hilong (lolong, hilong)可能有助于一些人同意
https://dba.stackexchange.com/questions/171792
复制相似问题