Performance, Operation, Security, and Data Utilization Platforms
Domain 5 - Performance, operation, security, and data utilization platforms
This domain covers インデックス and 実行計画, パーティション, backup and monitoring, security, and platforms such as DWH, データマート and NoSQL.
Core rules
- A B+tree index supports equality and range scans. A composite index (A,B) serves predicates on A and on A+B, but not B alone (leftmost prefix).
- The コストベースオプティマイザ uses 統計情報 (cardinality, 選択率); stale statistics produce bad plans.
- Join methods: ネステッドループ (small outer, indexed inner), ソートマージ, ハッシュ結合 (equi-join, large inputs).
- Range/list/hash partitioning enables partition pruning; indexes may be local or global.
- Backup types: full, differential (since last full), incremental (since last backup of any level); add archived logs for point-in-time recovery.
- Security: authentication, authorization through roles and views, column masking, encryption at rest and in transit, 監査ログ, and bind variables against SQL injection.
- DWH uses ETL, star/snowflake schemas and OLAP; NoSQL key-value/document/column/graph stores trade consistency for scale under CAP.
Worked example
Query: WHERE 顧客ID = ? AND 日付 BETWEEN ? AND ?. With index (顧客ID, 日付), both columns are used efficiently. A query filtering only on 日付 cannot use this index well; create (日付) or (日付, 顧客ID). Wrapping the column, as in WHERE substr(日付,1,6) = '202401', disables the index and forces a scan.
Traps
Indexing low-cardinality columns; over-indexing slowing INSERT/UPDATE; SELECT * preventing covering indexes; assuming a view always improves performance; treating differential and incremental backups as the same.