MySQL8 1億レコードを試す

飛行機の航跡データを1分間隔でAPIで取得して追加してます。

1回の追加レコード数が3500から9000位、検索を頻繁に行うカレントテーブルのレコードが多すぎると、検索、更新で速度パフォーマンスが劣化するので、カレントは4日分、4日より古いのは旧テーブルにMySQLのイベントで追加していってます。(4日より多い期間の検索は3テーブルをUNION ALLでつないだ仮想テーブルを SELECT DISTINCT してます)

ユーザーテーブルは利用中ユーザーのライブ地図、ビュー地図のAPI応答を保存していて、主にパストトラック線の描画、ADS-B応答の停止状況のチェックに使ってます。
検索結果にUNIONでマージされるので、更新インターバルが異なるデータからのビュー地図の表示が不自然になり、業務用途では統一性に欠け、目的不明瞭でばつな気がしますが、ホビー用途としては、全ユーザーの利用結果を含めたプレイバック再現が出来て、オープンデータ利用による機能向上の見本みたいなのになるかと思ってます。

旧テーブルはinnoDBのバッファープールを圧迫するといけないのでエンジンはMyISAMにしてます。

カレントテーブルのレコード数は約2200万をキープ、旧テーブルは現在、約6300万で増え続けてるので、足して1億を超えた時に、パフォーマンスが劇的に劣化するようなことがあれば、保存期間を設定して古い追加時刻から削除していくつもりです。

検索処理でのスロークエリの発生状況も気になるところなので、Seleniumとか使ってテストしていきます。

< カレント履歴テーブル >

1分間隔で全世界のADS-B応答を取得した結果を蓄積
保存期間 : 4日

CREATE TABLE `h_pasttrack_all` (

	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` CHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(11) NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(11) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` CHAR(4) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `h_pasttrack_all_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_all_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_all_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_all_INM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_all_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_all_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrackall_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky全機応答航跡履歴'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

< 旧履歴テーブル >

カレントテーブルの4日より前のレコードを蓄積
保存期間 : 現時点では未設定、レコードは増え続ける


        CREATE TABLE `h_pasttrack_old` (
	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` CHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(10) NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(10) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` CHAR(4) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `h_pasttrack_all_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_all_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_all_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_all_INM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_all_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_all_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrackall_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_all_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_all_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky全機応答航跡履歴旧'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

< ユーザーテーブル >

ユーザー設定デフォルト5秒間隔で指定した地図のバウンダリに存在する飛行機のADS=B応答を蓄積
保存期間 : 24時間

CREATE TABLE `h_pasttrack` (
	`ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
	`NM_SESSION` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID' COLLATE 'utf8_general_ci',
	`CD_ICAO` VARCHAR(4) NOT NULL DEFAULT '' COMMENT 'ICAO航空会社コード, ICAO空港コード' COLLATE 'utf8_general_ci',
	`CD_ICAO24` CHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO24機材識別コード' COLLATE 'utf8_general_ci',
	`NM_CALLSIGN` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '機材コールサイン' COLLATE 'utf8_general_ci',
	`UTM_POSITION` INT(10) NOT NULL DEFAULT '0' COMMENT 'ADS-B送信時刻',
	`UTM_LASTCONTACT` INT(10) NOT NULL DEFAULT '0' COMMENT '最終取得時刻',
	`LAT` FLOAT NOT NULL DEFAULT '-500' COMMENT '緯度',
	`LON` FLOAT NOT NULL DEFAULT '-500' COMMENT '経度',
	`ALT_BARO` FLOAT NOT NULL DEFAULT '0' COMMENT '気圧高度 (m)',
	`ALT_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地高度 (m)',
	`SPEED_GEO` FLOAT NOT NULL DEFAULT '0' COMMENT '対地速度 (m/s)',
	`DEG_TRUE` FLOAT NOT NULL DEFAULT '-1' COMMENT '飛行方向角',
	`RT_VERTICAL` FLOAT NOT NULL DEFAULT '-500' COMMENT '上昇下降率 (m/s)',
	`IS_ONGROUND` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '地上 or 空中フラグ',
	`NM_SQUAWK` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'スコークコード' COLLATE 'utf8_general_ci',
	`CD_ACTYPE` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'ICAO機材コード' COLLATE 'utf8_general_ci',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	PRIMARY KEY (`ID`) USING BTREE,
	INDEX `h_pasttrack_NM_SESSION_CD_ICAO` (`NM_SESSION`, `CD_ICAO`) USING BTREE,
	INDEX `h_pasttrack_TM_ADD` (`TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_CD_ICAO24` (`CD_ICAO24`) USING BTREE,
	INDEX `h_pasttrack_NM_CALLSIGN` (`NM_CALLSIGN`) USING BTREE,
	INDEX `h_pasttrack_NM_LAT` (`LAT`) USING BTREE,
	INDEX `h_pasttrack_NM_LON` (`LON`) USING BTREE,
	INDEX `h_pasttrack_IS_ONGROUND` (`IS_ONGROUND`) USING BTREE,
	INDEX `h_pasttrack_NM_SQUAWK` (`NM_SQUAWK`) USING BTREE,
	INDEX `h_pasttrack_UTM_POSITION` (`UTM_POSITION`) USING BTREE,
	INDEX `h_pasttrack_CD_ICAO24_TM_ADD` (`CD_ICAO24`, `TM_ADD`) USING BTREE,
	INDEX `h_pasttrack_LAT_LON_TM_ADD` (`LAT`, `LON`, `TM_ADD`) USING BTREE
)
COMMENT='OpenSky応答航跡履歴'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

< パフォーマンス向上策として行ったこと >

1) 文字列のデータ型をVARCHARからCHARに変更 (CHARの方が効率がよいという説あり)
2) 検索条件になる列の複合インデックスの列順 (狭く絞れるのを先に置く)
3) 最初は、PRIMARY KEYにAUTO_INCREMENTの列を存在させたが、一説によると、AUTO_INCREMENTの採番が負荷、メモリ消費なので削除
4) 経緯度データの型はFLOATにしてサイズ節約(OpenSkyの応答もFLOATなのでDOUBLE, DECIMALを使って数mの精度にすることが、無理でそもそも扱ってるデータにそこまでの精度は要求されてない)