BLUEBREEZE

참고

테이블 정보
[code sql]
-- 지역 IP 대역 정보 CREATE TABLE `GeoLiteCityBlocks` ( `startIPNum` int(10) unsigned NOT NULL, `endIPNum` int(10) unsigned NOT NULL, `locID` int(10) unsigned NOT NULL, PRIMARY KEY (`startIPNum`,`endIPNum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='지역IP대역 '$$ -- 지역정보 CREATE TABLE `GeoLiteCityLocation` ( `locID` int(10) unsigned NOT NULL, `country` char(2) default NULL, `region` char(2) default NULL, `city` varchar(45) default NULL, `postalCode` char(7) default NULL, `latitude` double default NULL, `longitude` double default NULL, `dmaCode` char(3) default NULL, `areaCode` char(3) default NULL, PRIMARY KEY (`locID`), KEY `idx_GeoLiteCity-Location` (`country`,`region`,`city`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='지역정보 '$$ -- IP별 지역 연결정보 CREATE TABLE `GeoLiteCityBlock` ( `addr` int(10) unsigned NOT NULL, `locID` int(10) unsigned NOT NULL, PRIMARY KEY (`addr`), KEY `idx_GeoListCityBlock` (`locID`), KEY `fk_GeoLiteCityBlock:locID` (`locID`), CONSTRAINT `fk_GeoLiteCityBlock:locID` FOREIGN KEY (`locID`) REFERENCES `GeoLiteCityLocation` (`locID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ [/code]

[code sql]
DELIMITER $$ CREATE DEFINER=`pickup`@`` PROCEDURE `geo`() BEGIN DECLARE cur_state INT DEFAULT 0; DECLARE startIPNum INT DEFAULT 0; DECLARE endIPNum INT DEFAULT 0; DECLARE locID INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT * FROM sta.GeoLiteCityBlocks WHERE startIPNum >= (SELECT if(count(addr)=0, 0, addr) as startIPNum FROM sta.GeoLiteCityBlock ORDER BY addr DESC LIMIT 1) ORDER BY startIPNum ASC LIMIT 1000; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_state = 1; OPEN cur; REPEAT FETCH cur INTO startIPNum, endIPNum, locID; IF NOT cur_state THEN WHILE startIPNum <= endIPNum DO INSERT INTO sta.GeoLiteCityBlock VALUES (startIPNum, locID); SET startIPNum = startIPNum+1; END WHILE; END IF; UNTIL cur_state END REPEAT; CLOSE cur; END[/code]

[code sql] -- 프로시저 실행 call geo(); [/code]

하나의 IP 주소에대한 지역정보를 찾을 경우에는 MaxMind 에서 제공하는 쿼리로 문제없이 동작한다.
[code sql]
SELECT ip_country FROM geoip WHERE 404232216 >= begin_ip_num AND 404232216 <= end_ip_num LIMIT 1 [/code]

하지만 IP Row 수가 100단위 이상 넘어갈 경우 쿼리 결과는 수십초를 넘은 실행시간을 잡아 먹는 관계로 결국은 IP와 locID간의 1:1 조건을 만족시키고 join으로 0.00x 대의 쿼리 실행시간을 위해서 GeoLiteCityBlock 테이블을 구성하고 IP:locID 정보를 밀어넣는 프로시저를 만들고 자료를 밀어 넣게 되었습니다.

하지만 쓸데없는 Table 아닐까하는 생각도 드네요.

거기에다 16777216 ~ 3758095359 까지 INT형으로 PK 자료만 넣는데도 엄청난 시간이 소유되는군요. 약 25백만건의 자료를 프로시저로 addr과 locID 필드 자료를 넣는데 만 하루 이상이 소요되었습니다.

이걸 한방에 빠르게 해결할 수 있는 방법이 없을까 생각해 봤지만... 아직 저에겐 무리군요.. ^^;;