MySQL이 SET 절에서 SELECT를 사용하여 UPDATE 쿼리에 인덱스 사용을 거부함

MySQL이 SET 절에서 SELECT를 사용하여 UPDATE 쿼리에 인덱스 사용을 거부함

나는 그 일을 채워야 합니다.location야전에 나서다users국가 이름이 있는 표geoip테이블, 사용자의 IP에 따라 달라집니다.

테이블의 CREATE 코드는 다음과 같습니다.

CREATE TABLE `geoip` (
    `IP_FROM` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `IP_TO` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `COUNTRY_NAME` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`IP_FROM`, `IP_TO`)
)    
ENGINE=InnoDB;

CREATE TABLE `users` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `login` VARCHAR(25) NOT NULL DEFAULT '' 
    `password` VARCHAR(64) NOT NULL DEFAULT ''
    `ip` VARCHAR(128) NULL DEFAULT ''
    `location` VARCHAR(128) NULL DEFAULT ''
    PRIMARY KEY (`id`),
    UNIQUE INDEX `login` (`login`), 
    INDEX `ip` (`ip`(10))   
)
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC;

실행하려고 하는 업데이트 쿼리는 다음과 같습니다.

UPDATE users u
SET u.location = 
(SELECT COUNTRY_NAME FROM geoip WHERE INET_ATON(u.ip) BETWEEN IP_FROM AND IP_TO)

문제는 이 쿼리가 사용을 거부한다는 것입니다.PRIMARY색인을 보다geoip테이블, 일이 훨씬 빨라지겠지만 말입니다.설명을 통해 알 수 있습니다.

id  select_type         table   type    possible_keys   key     key_len ref rows    Extra
1   PRIMARY             u       index   NULL            PRIMARY 4       NULL        1254395 
2   DEPENDENT SUBQUERY  geoip   ALL     PRIMARY NULL    NULL    NULL    62271       Using where

결국은 제가 말을 바꿨습니다.geoip이 쿼리에 대해서만 메모리 엔진에 테이블을 제공합니다. 하지만 올바른 방법이 무엇이었는지 알고 싶습니다.

업데이트 제가 사용하고 있는 DBMS는 MariaDB 10.0.17입니다.

이런 식으로 지수를 강요하려고 했습니까?

UPDATE users u
SET u.location = 
(SELECT COUNTRY_NAME FROM geoip FORCE INDEX (PRIMARY) 
 WHERE INET_ATON(u.ip) BETWEEN IP_FROM AND IP_TO) 

또한 ip가 NULL일 수 있기 때문에 인덱스 최적화를 방해할 수 있습니다.

IP 범위가 중복되지 않는 것이 맞습니까?IPv6 주소를 받지 못했습니까?(전 세계적으로 IPv4는 몇 년 전에 고갈되었습니다.)

아니요, 인덱스가 사용되지 않거나 최소한 원하는 만큼의 성능을 발휘하지 못합니다.그래서 저는 그것을 해결하기 위한 방안을 생각해 냈습니다.그러나 스키마를 재구성하고 저장 루틴을 구축해야 합니다.내 IP 범위 블로그 보기;IPv4와 IPv6의 코드에 대한 링크를 가지고 있습니다.테이블의 절반을 스캔할 필요 없이 일반적으로 테이블의 한 행만 터치합니다.

편집

MySQL은 일치해야 하는 범위(범위/범위)가 하나밖에 없다는 것을 알지 못합니다.그래서 너무 많이 스캔을 해요.IP(INT UNSIGNED vs VARCHAR)의 두 인코딩 간의 차이로 인해 하위 쿼리 대신 JOIN을 사용하기가 어렵습니다.일치하는 것이 정확히 하나라는 것을 이해하지 못하기 때문에 알라 조인은 더 나은 것이 아닐 것입니다.한 번 시도해 보십시오.

UPDATE  users u
    SET u.location = 
      ( SELECT  COUNTRY_NAME
            FROM  geoip
            WHERE  INET_ATON(u.ip) BETWEEN IP_FROM AND IP_TO
            LIMIT  1   -- added
      )

속도를 크게 향상시키지 못할 경우 다음에서 변경합니다.VARCHAR로.INT UNSIGNED인에users(없이) 다시 시도합니다.INET_ATON).

언급URL : https://stackoverflow.com/questions/29624514/mysql-refuses-to-use-index-for-update-query-with-select-in-set-clause