EXPLAIN 인덱스 검증 · 무결성 가드
-- =====================================================================
-- 전수 검증 스크립트 (감사 반영판): 픽스처 → EXPLAIN 50종 → 방어장치 부정 테스트
-- 실행 전제: base DDL → V2 → routines 적용된 빈 nestpay 스키마
-- 판정: 대형 테이블 = 의도 인덱스(const/ref/range). 소형·저빈도 = full 허용 주석.
-- =====================================================================
-- ============================ 픽스처 ============================
INSERT INTO users (id, login_id, password_hash, name, name_norm, ci_hash, active_ci, phone_enc, phone_hash, status, marketing_agree, created_at)
SELECT seq, CONCAT('user', seq), 'x', CONCAT('사용자', seq), CONCAT('사용자', seq),
SHA2(CONCAT('ci', seq),256), IF(seq % 20 = 0, NULL, SHA2(CONCAT('ci', seq),256)),
'enc', SHA2(CONCAT('ph', seq),256), IF(seq % 20 = 0,'WITHDRAWN','ACTIVE'), seq % 2, NOW() - INTERVAL seq HOUR
FROM seq_1_to_2000;
INSERT INTO admin_accounts (id, login_id, password_hash, name, is_root, otp_enabled, status, created_at)
VALUES (1,'root','x','루트',1,1,'ACTIVE',NOW()),(2,'op1','x','운영1',0,1,'ACTIVE',NOW());
INSERT INTO merchants (id, biz_no, biz_name, ceo_name, ceo_name_norm, ceo_ci_hash, biz_type, vat_mode, vat_rate, status, created_at)
SELECT seq, LPAD(seq,10,'0'), CONCAT('매장', seq), '대표', '대표', SHA2(CONCAT('mci',seq),256), 'SOLE', 'TAXED', 10.00,
IF(seq%25=0,'PENDING','ACTIVE'), NOW() - INTERVAL seq HOUR
FROM seq_1_to_200;
INSERT INTO cards (id, user_id, number_hash, number_enc, cvc_enc, issued_at, expires_on, design_code, is_primary, status, created_at)
SELECT seq, ((seq-1) % 2000)+1, SHA2(CONCAT('card',seq),256), 'enc', 'enc', NOW(), DATE_ADD(CURDATE(), INTERVAL 5 YEAR), 'OCEAN', 1, 'ACTIVE', NOW()
FROM seq_1_to_3000;
INSERT INTO wallets (id, owner_type, card_id, balance, created_at)
SELECT seq, 'USER_CARD', seq, 100000, NOW() FROM seq_1_to_3000;
INSERT INTO wallets (id, owner_type, merchant_id, balance, created_at)
SELECT 3000+seq, 'MERCHANT', seq, 500000, NOW() FROM seq_1_to_200;
INSERT INTO wallets (id, owner_type, system_code, balance, created_at) VALUES
(3201,'SYSTEM','FEE_REVENUE',0,NOW()),(3202,'SYSTEM','EXPIRED',0,NOW()),
(3203,'SYSTEM','SEIZED',0,NOW()),(3204,'SYSTEM','UNMATCHED',0,NOW()),
(3205,'SYSTEM','SETTLEMENT_CLEARING',0,NOW()),(3206,'SYSTEM','FORFEITED',0,NOW()),
(3207,'SYSTEM','SEIZE_BURNED',0,NOW());
INSERT INTO bank_accounts (owner_type, owner_id, bank_code, account_enc, account_hash, holder_name, holder_name_norm, verified_at, status, created_at)
SELECT 'USER', seq, '004', 'enc', SHA2(CONCAT('acc',seq),256), '홍길동', '홍길동', NOW(), 'ACTIVE', NOW() FROM seq_1_to_2000;
-- 거래 4종 - 종류마다 허용된 세부종류·상태만 씁니다(chk_txn_type_subtype / chk_txn_type_status).
-- 0 DEPOSIT : VACCT · CONFIRMED
-- 1 PAYMENT : QR_STORE · CONFIRMED|CANCELED · 매장 필수(chk_txn_payment_merchant)
-- 2 GIFT : GIFT_DIRECT · CONFIRMED
-- 3 WITHDRAW : FIRMBANK · HOLD|PENDING|CONFIRMED|FAILED
INSERT INTO transactions (txn_uid, type, subtype, status, initiator_type, initiator_id, card_id, counterparty_card_id, merchant_id,
amount, fee_amount, bank_tran_ref, related_txn_id, scheduled_at, created_at, confirmed_at)
SELECT CONCAT('T', LPAD(seq,25,'0')),
ELT(1+(seq%4),'DEPOSIT','PAYMENT','GIFT','WITHDRAW'),
ELT(1+(seq%4),'VACCT','QR_STORE','GIFT_DIRECT','FIRMBANK'),
CASE seq%4
WHEN 1 THEN IF(seq%20=1,'CANCELED','CONFIRMED')
WHEN 3 THEN ELT(1+((seq DIV 4)%4),'HOLD','PENDING','CONFIRMED','FAILED')
ELSE 'CONFIRMED' END,
'USER', ((seq-1)%2000)+1, ((seq-1)%3000)+1,
IF(seq%4=2, ((seq*7-1)%3000)+1, NULL),
IF(seq%4=1, ((seq-1)%200)+1, NULL),
1000+(seq%9000), seq%100, IF(seq%7=0, CONCAT('BR',seq), NULL),
IF(seq%11=0, seq-1, NULL),
IF(seq%4=3, NOW() + INTERVAL (CAST(seq%48 AS SIGNED)-24) HOUR, NULL),
NOW() - INTERVAL (seq % 90) DAY,
IF(seq%4=3 AND (seq DIV 4)%4 IN (0,1), NULL, NOW() - INTERVAL (seq % 90) DAY)
FROM seq_1_to_50000;
-- 포인트 덩어리 - 어느 거래로 생겼는지 반드시 남깁니다(V61 로 txn_id 필수).
INSERT INTO point_lots (txn_id, wallet_id, lot_type, source, amount_init, amount_remaining, expires_at, created_at)
SELECT seq, ((seq-1)%3000)+1, IF(seq%5=0,'REWARD','DEPOSIT'), 'DEPOSIT', 50000, IF(seq%3=0, 0, 30000),
NOW() + INTERVAL (CAST(seq%400 AS SIGNED) - 30) DAY, NOW() FROM seq_1_to_9000;
-- 이용제한·압류·은행 목록(V62·V64·V65)
INSERT INTO banks (code, name, sort_order, status, created_at)
SELECT LPAD(seq,3,'0'), CONCAT('은행', seq), seq, IF(seq%10=0,'HIDDEN','ACTIVE'), NOW() FROM seq_1_to_50;
INSERT INTO account_restrictions (principal_type, principal_id, feature, reason, created_by, created_at, released_by, released_at, release_reason)
SELECT 'USER', seq, ELT(1+(seq%4),'DEPOSIT','WITHDRAW','GIFT','PAYMENT'), '검증용', 1, NOW(),
IF(seq%3=0, 1, NULL), IF(seq%3=0, NOW(), NULL), IF(seq%3=0, '검증용 해제', NULL)
FROM seq_1_to_1500;
INSERT INTO seizures (principal_type, principal_id, wallet_id, amount, reason, status, seize_txn_id, created_by, created_at)
SELECT 'USER', seq, seq, 10000, '검증용', ELT(1+(seq%3),'HELD','RETURNED','BURNED'), seq, 1, NOW()
FROM seq_1_to_1000;
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at)
SELECT seq, ((seq-1)%3000)+1, 'DR', 1000+(seq%9000), 100000, NOW() - INTERVAL (seq % 90) DAY FROM seq_1_to_50000;
INSERT INTO ledger_entries (txn_id, wallet_id, direction, amount, balance_after, created_at)
SELECT seq, 3000+((seq-1)%200)+1, 'CR', 1000+(seq%9000), 500000, NOW() - INTERVAL (seq % 90) DAY FROM seq_1_to_50000;
INSERT INTO lot_allocations (ledger_entry_id, lot_id, amount)
SELECT seq, ((seq-1)%9000)+1, 100 FROM seq_1_to_30000;
INSERT INTO outbox_jobs (job_type, payload, run_after, status, created_at)
SELECT 'PUSH', '{}', NOW() - INTERVAL (seq%100) MINUTE, IF(seq%3=0,'DONE','PENDING'), NOW() FROM seq_1_to_5000;
INSERT INTO notification_inbox (principal_type, principal_id, ntype, title, read_at, created_at)
SELECT 'USER', ((seq-1)%2000)+1, 'TXN', '알림', IF(seq%2=0, NOW(), NULL), NOW() - INTERVAL (seq%40) DAY FROM seq_1_to_20000;
INSERT INTO deposit_notices (source, raw_text, dedup_key, bank_tran_ref, parsed_amount, parsed_name, parsed_name_norm, identifier_value, status, received_at)
SELECT 'SMS', 'raw', SHA2(CONCAT('dn',seq),256), CONCAT('DN', seq), 10000, '홍길동', '홍길동', CONCAT('VA', LPAD(seq%2000+1,12,'0')),
ELT(1+(seq%4),'MATCHED','MATCHED','NEW','UNMATCHED'), NOW() - INTERVAL (seq%30) DAY FROM seq_1_to_5000;
INSERT INTO unmatched_deposits (notice_id, amount, status, created_at)
SELECT seq, 10000, IF(seq%3=0,'RETURNED','PENDING'), NOW() FROM seq_1_to_120;
INSERT INTO pg_orders (merchant_id, order_no, amount, vat_amount, status, is_sandbox, created_at)
SELECT ((seq-1)%200)+1, CONCAT('ORD', seq), 10000, 1000, IF(seq%3=0,'CREATED','PAID'), 0, NOW() - INTERVAL (seq%72) HOUR
FROM seq_1_to_3000;
INSERT INTO merchant_api_credentials (merchant_id, client_id, api_key_hash, status, requested_at, mode, created_at)
SELECT seq, CONCAT('cli_', LPAD(seq,8,'0')), 'kh', ELT(1+(seq%3),'REQUESTED','APPROVED','APPROVED'), NOW(), 'SANDBOX', NOW()
FROM seq_1_to_200;
INSERT INTO webhook_deliveries (merchant_id, event_type, payload, target_url, attempts, next_retry_at, status, created_at)
SELECT ((seq-1)%200)+1, 'PAYMENT_COMPLETED', '{}', 'https://x', seq%5,
NOW() - INTERVAL (seq%60) MINUTE, IF(seq%4=0,'PENDING','DELIVERED'), NOW() FROM seq_1_to_2000;
INSERT INTO inquiries (principal_type, principal_id, title, body, status, created_at)
SELECT 'USER', ((seq-1)%2000)+1, '문의', '내용', IF(seq%3=0,'OPEN','ANSWERED'), NOW() - INTERVAL (seq%30) DAY FROM seq_1_to_1500;
INSERT INTO audit_logs (actor_type, actor_id, action, target_type, target_id, created_at)
SELECT 'ADMIN', 1+(seq%2), 'ACTION', 'USER', ((seq-1)%2000)+1, NOW() - INTERVAL (seq%60) DAY FROM seq_1_to_3000;
INSERT INTO login_histories (principal_type, principal_id, method, ip, result, created_at)
SELECT 'USER', ((seq-1)%2000)+1, 'PIN', '1.1.1.1', 'SUCCESS', NOW() - INTERVAL (seq%60) DAY FROM seq_1_to_10000;
INSERT INTO pii_access_logs (admin_id, target_type, target_id, fields, created_at)
SELECT 1+(seq%2), 'USER', ((seq-1)%2000)+1, 'phone', NOW() - INTERVAL (seq%20) DAY FROM seq_1_to_5000;
INSERT INTO auth_devices (principal_type, principal_id, device_uid, platform, status, created_at)
SELECT 'USER', seq, CONCAT('dev', seq%500), 'IOS', 'ACTIVE', NOW() FROM seq_1_to_2000;
INSERT INTO reject_logs (principal_type, principal_id, ip, action, reject_code, created_at)
SELECT 'USER', ((seq-1)%2000)+1, CONCAT('10.0.', seq%250, '.1'), 'GIFT', 'USER_NOT_FOUND', NOW() - INTERVAL (seq%600) MINUTE
FROM seq_1_to_10000;
INSERT INTO daily_summaries (summary_date, metric, value)
SELECT CURDATE() - INTERVAL (seq%180) DAY, ELT(1+(seq%3),'DEPOSIT_SUM','PAYMENT_SUM','FEE_SUM'), seq*100 FROM seq_1_to_540
ON DUPLICATE KEY UPDATE value = VALUES(value);
INSERT INTO daily_wallet_snapshots (snap_date, wallet_id, balance, last_ledger_id)
SELECT CURDATE() - INTERVAL 1 DAY, seq, 100000, seq FROM seq_1_to_3000;
INSERT INTO fee_policies (fee_type, scope, target_id, rate, fixed_amount) VALUES
('USER_WITHDRAW','GLOBAL',NULL,0,500),('MERCHANT_PAYMENT','GLOBAL',NULL,2.0,0),('MERCHANT_PAYMENT','MERCHANT',3,1.5,0);
INSERT INTO global_settings (skey, svalue, updated_at) VALUES
('REJOIN_WAIT_DAYS','30',NOW()), ('BANK_COOLDOWN_HOURS','24',NOW()), ('FORFEIT_MAX_AMOUNT','1000',NOW()),
('CPM_CONFIRM_THRESHOLD','100000',NOW()), ('MAINTENANCE_ON','0',NOW());
ANALYZE TABLE users, merchants, cards, wallets, bank_accounts, point_lots, transactions, ledger_entries,
lot_allocations, outbox_jobs, notification_inbox, deposit_notices, unmatched_deposits,
pg_orders, merchant_api_credentials, webhook_deliveries, inquiries, audit_logs, login_histories,
pii_access_logs, auth_devices, reject_logs, daily_summaries,
daily_wallet_snapshots;
-- ===================== EXPLAIN 전수 (E1~E52) =====================
SELECT 'E1|카드 이용내역(커서)|ix_txn_card' AS t; EXPLAIN SELECT id FROM transactions WHERE card_id=7 AND (created_at < NOW() OR (created_at = NOW() AND id < 999999)) ORDER BY created_at DESC, id DESC LIMIT 30;
SELECT 'E2|한도 합산|ix_txn_initiator_date' AS t; EXPLAIN SELECT SUM(amount) FROM transactions WHERE initiator_type='USER' AND initiator_id=7 AND type='PAYMENT' AND status IN ('PENDING','HOLD','UNKNOWN','CONFIRMED') AND subtype NOT IN ('REISSUE') AND created_at >= CURDATE();
SELECT 'E3|매장 당일 매출|ix_txn_merchant' AS t; EXPLAIN SELECT DATE(created_at), SUM(amount) FROM transactions WHERE merchant_id=3 AND type IN ('PAYMENT','CANCEL') AND created_at >= CURDATE() GROUP BY DATE(created_at);
SELECT 'E5|선물 번호검색|ix_users_phone_hash' AS t; EXPLAIN SELECT id FROM users WHERE phone_hash=SHA2('ph7',256) AND status='ACTIVE';
SELECT 'E6|로트 FIFO(결제)|ix_lots_fifo' AS t; EXPLAIN SELECT id FROM point_lots WHERE wallet_id=7 AND amount_remaining>0 AND expires_at>NOW() ORDER BY expires_at LIMIT 10;
SELECT 'E7|만료 배치(워터마크)|ix_lots_expiry' AS t; EXPLAIN SELECT id FROM point_lots WHERE expires_at > NOW() - INTERVAL 40 DAY AND expires_at <= NOW() AND amount_remaining>0 LIMIT 200;
SELECT 'E8|outbox 폴링|ix_outbox_poll' AS t; EXPLAIN SELECT id FROM outbox_jobs WHERE status='PENDING' AND run_after<=NOW() ORDER BY run_after LIMIT 50;
SELECT 'E9|알림함|ix_inbox_owner' AS t; EXPLAIN SELECT id FROM notification_inbox WHERE principal_type='USER' AND principal_id=7 AND created_at >= NOW() - INTERVAL 30 DAY ORDER BY created_at DESC LIMIT 30;
SELECT 'E10|원장 지갑체인|ix_ledger_wallet_chain' AS t; EXPLAIN SELECT id, balance_after FROM ledger_entries WHERE wallet_id=7 ORDER BY id LIMIT 100;
SELECT 'E11|아이디 조회|login_id(uq)' AS t; EXPLAIN SELECT id FROM users WHERE login_id='user7';
SELECT 'E12|회원 상태목록|ix_users_status' AS t; EXPLAIN SELECT id FROM users WHERE status='WITHDRAWN' ORDER BY created_at DESC LIMIT 20;
SELECT 'E13|매장 사업자번호|biz_no(uq)' AS t; EXPLAIN SELECT id FROM merchants WHERE biz_no='0000000007';
SELECT 'E14|매장 심사큐|ix_merchants_status' AS t; EXPLAIN SELECT id FROM merchants WHERE status='PENDING' ORDER BY id DESC LIMIT 20;
SELECT 'E15|거래 txn_uid|txn_uid(uq)' AS t; EXPLAIN SELECT id FROM transactions WHERE txn_uid='T0000000000000000000000007';
SELECT 'E16|admin 기간검색|ix_txn_created' AS t; EXPLAIN SELECT id FROM transactions WHERE created_at >= NOW() - INTERVAL 3 DAY AND created_at < NOW() ORDER BY created_at DESC LIMIT 50;
SELECT 'E17|펌뱅킹 참조|ix_txn_bank_ref' AS t; EXPLAIN SELECT id FROM transactions WHERE bank_tran_ref='BR7';
SELECT 'E18|재가입 CI판정|ix_users_ci' AS t; EXPLAIN SELECT id, status, withdrawn_at FROM users WHERE ci_hash=SHA2('ci40',256) ORDER BY id DESC LIMIT 1;
SELECT 'E19|미매칭 큐|소형 허용' AS t; EXPLAIN SELECT ud.id FROM unmatched_deposits ud JOIN deposit_notices dn ON dn.id=ud.notice_id WHERE ud.status='PENDING' ORDER BY ud.id LIMIT 50;
SELECT 'E20|통지 미처리 스캔|ix_notice_status' AS t; EXPLAIN SELECT id FROM deposit_notices WHERE status='NEW' ORDER BY received_at LIMIT 100;
SELECT 'E22|문의 큐|ix_inquiry_queue' AS t; EXPLAIN SELECT id FROM inquiries WHERE status='OPEN' ORDER BY created_at LIMIT 50;
SELECT 'E23|PG 만료 스윕|ix_pg_status' AS t; EXPLAIN SELECT id FROM pg_orders WHERE status='CREATED' AND created_at <= NOW() - INTERVAL 1 HOUR LIMIT 100;
SELECT 'E24|감사 대상|ix_audit_target' AS t; EXPLAIN SELECT id FROM audit_logs WHERE target_type='USER' AND target_id=7 ORDER BY id DESC LIMIT 50;
SELECT 'E25|감사 행위자|ix_audit_actor' AS t; EXPLAIN SELECT id FROM audit_logs WHERE actor_type='ADMIN' AND actor_id=1 AND created_at >= NOW() - INTERVAL 30 DAY ORDER BY created_at DESC LIMIT 50;
SELECT 'E26|일 마감 집계|ix_txn_created' AS t; EXPLAIN SELECT type, status, COUNT(*), SUM(amount) FROM transactions WHERE created_at >= CURDATE() - INTERVAL 1 DAY AND created_at < CURDATE() GROUP BY type, status;
SELECT 'E27|대시보드 추이|ux_summary_day_metric' AS t; EXPLAIN SELECT summary_date, metric, value FROM daily_summaries WHERE summary_date >= CURDATE() - INTERVAL 30 DAY ORDER BY summary_date;
SELECT 'E28|만료 리포트|ix_lots_expiry' AS t; EXPLAIN SELECT DATE(expires_at), SUM(amount_remaining) FROM point_lots WHERE amount_remaining>0 AND expires_at BETWEEN NOW() AND NOW()+INTERVAL 30 DAY GROUP BY DATE(expires_at);
SELECT 'E29|미읽음 수|ix_inbox_owner' AS t; EXPLAIN SELECT COUNT(*) FROM notification_inbox WHERE principal_type='USER' AND principal_id=7 AND read_at IS NULL AND created_at >= NOW() - INTERVAL 30 DAY;
SELECT 'E30|계좌 조회|ix_bank_owner' AS t; EXPLAIN SELECT id FROM bank_accounts WHERE owner_type='USER' AND owner_id=7 AND status='ACTIVE';
SELECT 'E31|로그인 이력|ix_login_hist' AS t; EXPLAIN SELECT method, created_at FROM login_histories WHERE principal_type='USER' AND principal_id=7 ORDER BY created_at DESC LIMIT 20;
SELECT 'E32|웹훅 재시도|ix_webhook_retry' AS t; EXPLAIN SELECT id FROM webhook_deliveries WHERE status='PENDING' AND next_retry_at <= NOW() ORDER BY next_retry_at LIMIT 50;
SELECT 'E33|홈 캐러셀|ix_cards_user' AS t; EXPLAIN SELECT c.id, w.balance FROM cards c JOIN wallets w ON w.card_id=c.id WHERE c.user_id=7 AND c.status='ACTIVE';
SELECT 'E34|수수료 해석|ux_fee_policy' AS t; EXPLAIN SELECT rate, fixed_amount FROM fee_policies WHERE fee_type='MERCHANT_PAYMENT' AND ((scope='MERCHANT' AND target_id=3) OR scope='GLOBAL') ORDER BY (scope <> 'GLOBAL') DESC LIMIT 1;
SELECT 'E36|시스템 지갑|ix_wallets_owner_type' AS t; EXPLAIN SELECT system_code, balance FROM wallets WHERE owner_type='SYSTEM';
SELECT 'E37|원장 일범위|파티션 프루닝' AS t; EXPLAIN PARTITIONS SELECT COUNT(*) FROM ledger_entries WHERE created_at >= CURDATE() - INTERVAL 1 DAY AND created_at < CURDATE();
SELECT 'E39|FDS 입금스캔|ix_txn_created' AS t; EXPLAIN SELECT id, initiator_id, created_at FROM transactions WHERE type='DEPOSIT' AND created_at >= NOW() - INTERVAL 1 HOUR;
-- ==== 감사 신설 경로 E40~E52 ====
SELECT 'E40|취소 로트 역추적|ix_alloc_entry' AS t; EXPLAIN SELECT la.lot_id, la.amount FROM lot_allocations la WHERE la.ledger_entry_id = 777;
SELECT 'E41|추적 CTE 역참조|ix_txn_related' AS t; EXPLAIN SELECT id FROM transactions WHERE related_txn_id = 776;
SELECT 'E42|통합 내역(커서)|ix_txn_initiator_created' AS t; EXPLAIN SELECT id FROM transactions WHERE initiator_type='USER' AND initiator_id=7 AND (created_at < NOW() OR (created_at = NOW() AND id < 999999)) ORDER BY created_at DESC, id DESC LIMIT 30;
SELECT 'E43|출금 실행 배치|ix_txn_withdraw_sched' AS t; EXPLAIN SELECT id FROM transactions WHERE type='WITHDRAW' AND status='HOLD' AND scheduled_at <= NOW() ORDER BY scheduled_at LIMIT 100;
SELECT 'E44|받은 선물|ix_txn_counterparty' AS t; EXPLAIN SELECT id FROM transactions WHERE counterparty_card_id=7 AND type='GIFT' AND (created_at < NOW() OR (created_at = NOW() AND id < 999999)) ORDER BY created_at DESC, id DESC LIMIT 30;
SELECT 'E45|PG 인증(client_id)|client_id(uq)' AS t; EXPLAIN SELECT id, api_key_hash, mode FROM merchant_api_credentials WHERE client_id='cli_00000007';
SELECT 'E46|API 신청 큐|ix_apicred_queue' AS t; EXPLAIN SELECT id FROM merchant_api_credentials WHERE status='REQUESTED' ORDER BY requested_at LIMIT 50;
SELECT 'E48|PII 대상 조사|ix_pii_target' AS t; EXPLAIN SELECT admin_id, fields, created_at FROM pii_access_logs WHERE target_type='USER' AND target_id=7 ORDER BY created_at DESC LIMIT 50;
SELECT 'E49|PII 관리자 조사|ix_pii_admin' AS t; EXPLAIN SELECT target_id, created_at FROM pii_access_logs WHERE admin_id=1 AND created_at >= NOW() - INTERVAL 30 DAY ORDER BY created_at DESC LIMIT 50;
SELECT 'E50|FDS 다계정기기|ix_device_uid' AS t; EXPLAIN SELECT principal_id FROM auth_devices WHERE device_uid='dev7';
SELECT 'E51|FDS IP열거|ix_reject_ip' AS t; EXPLAIN SELECT ip, COUNT(*) FROM reject_logs WHERE created_at >= NOW() - INTERVAL 10 MINUTE AND ip='10.0.7.1' GROUP BY ip;
SELECT 'E52|별도관리 대사|ix_lots_type_remaining' AS t; EXPLAIN SELECT SUM(amount_remaining) FROM point_lots WHERE lot_type='DEPOSIT' AND amount_remaining > 0;
SELECT 'E53|이용제한 조회|ix_restriction_lookup' AS t; EXPLAIN SELECT feature FROM account_restrictions WHERE principal_type='USER' AND principal_id=7 AND released_at IS NULL;
SELECT 'E54|압류 보유 조회|ix_seizure_holder' AS t; EXPLAIN SELECT id, amount FROM seizures WHERE principal_type='USER' AND principal_id=7 AND status='HELD';
-- E55 는 full 허용: 은행 표는 수십 행짜리 소형 표라 옵티마이저가 풀스캔을 고릅니다.
-- 게다가 BankService 가 결과를 캐시해 두어 매 요청 조회하지 않습니다.
SELECT 'E55|은행 목록|ix_banks_list' AS t; EXPLAIN SELECT code, name FROM banks WHERE status='ACTIVE' ORDER BY sort_order;
-- ===================== 방어장치 부정 테스트 (전부 오류가 나야 정상) =====================
-- G1: UPDATE ledger_entries SET amount=1 WHERE id=1; → 45000 append-only
-- G2: DELETE FROM transactions WHERE id=1; → 45000 append-only
-- G3: UPDATE transactions SET amount=999 WHERE id=1; → 45000 불변 컬럼
-- G4: UPDATE point_lots SET amount_remaining=amount_remaining+1 WHERE id=1; → 45000 감소만
-- G5: UPDATE wallets SET balance=-1 WHERE id=1; → 4025 CHECK(SYSTEM 제외)
-- G6: INSERT wallets (owner_type='SYSTEM', card_id=1, ...) → 4025 CHECK owner 정합
-- G7: 동일 (initiator, idempotency_key) 재INSERT → 1062 ux_txn_idem
-- G8: 동일 active_ci 재INSERT → 1062 1인 1활성
-- G9: UPDATE deposit_notices SET raw_text='x' WHERE id=1; → 45000 증거 불변
-- G10: UPDATE pii_access_logs SET fields='x' WHERE id=1; → 45000 append-only
-- G11: UPDATE wallets SET balance=-1 WHERE owner_type='SYSTEM' LIMIT 1; → 성공해야 정상(음수 허용 규약)
-- (실행은 회귀검증 스크립트가 개별 세션으로 수행 - 본 파일에 두면 이후 구문이 중단되므로 주석 유지)