mysql 프로시저 insert into GeoLiteCityBlock

• MySQL 5.X - FUNCTION,PROCEDURE 만들기 예제 by S2 & NAMU

테이블 정보

-- 지역 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$$

IP별 지역정보를 연결한 자료를 테이블에 밀어넣기

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

-- 프로시저 실행
call geo();

하나의 IP 주소에대한 지역정보를 찾을 경우에는 MaxMind 에서 제공하는 쿼리로 문제없이 동작한다.

SELECT ip_country FROM geoip WHERE 404232216 >= begin_ip_num AND 404232216 <= end_ip_num LIMIT 1

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

결국은 현재 통계에 쌓여있는 IP와 Geo 자료 매핑테이블을 만들기로 했습니다.
이렇게밖에 할 수 없는 이유는 INT(4 byte)로 addr과 locID 2개의 필드에 최소 16,777,216에서 최대 3,758,095,359까지의 자료를 넣을 경우 2개 필드 평균 4 byte의 저장공간을 확보한다 해도 (3,758,095,359 - 16,777,216)*4 = 14,965,272,572 byte (약 14Gbyte )의 자료공간을 차지하게 됩니다. 거기에 인덱스 자료까지 생각한다면 공간낭비라 생각되는군요.
거기에다 16777216 ~ 3758095359 까지 INT형으로 PK 자료만 넣는데도 엄청난 시간이 소유되는군요.
약 25백만건의 자료를 프로시저로 addr과 locID 필드 자료를 넣는데 만 하루 이상이 소요되었습니다.
이걸 한방에 빠르게 해결할 수 있는 방법이 없을까 생각해 봤지만... 아직 저에겐 무리군요.. ^^;;

DELIMITER $$

CREATE DEFINER=`pickup`@`` PROCEDURE `geo`()
BEGIN
	DECLARE cur_state INT DEFAULT 0; 
	DECLARE addr INT DEFAULT 0; 

	DECLARE cur CURSOR FOR 
		SELECT su.addr
		FROM sta.sta_uri AS su
		WHERE su.addr NOT IN (SELECT addr FROM sta.GeoLiteCityBlock)
		ORDER BY su.addr;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET cur_state = 1;

	OPEN cur;
	REPEAT
		FETCH cur INTO addr; 
		IF NOT cur_state THEN
			INSERT INTO sta.GeoLiteCityBlock 
				SELECT addr, `locID` 
				FROM sta.GeoLiteCityBlocks 
				WHERE addr BETWEEN startIPNum AND endIPNum LIMIT 1
			ON DUPLICATE KEY UPDATE locID=VALUES(locID);
		END IF;
	UNTIL cur_state END REPEAT;
	CLOSE cur;
END

결국 위 프로시저는 아래 쿼리 한번으로 해결되는건데...

INSERT INTO sta.GeoLiteCityBlock 
	SELECT su.addr, geob.locID
	FROM sta.sta_uri AS su
		LEFT JOIN sta.GeoLiteCityBlocks AS geob ON su.addr BETWEEN geob.startIPNum AND geob.endIPNum
	WHERE su.addr NOT IN (SELECT addr FROM sta.GeoLiteCityBlock)
	GROUP BY su.addr