MariaDBでインデックスを活用してデータベースのパフォーマンスを改善する
MariaDBでテーブルを時間軸で適切にインデックスする方法
インデックスの種類
MariaDBには、さまざまな種類のインデックスが用意されています。時間軸でテーブルをインデックスする場合、次のインデックスタイプを検討する必要があります。
- PRIMARY KEY インデックス: これは、テーブルの各行を一意に識別する列に作成されるデフォルトのインデックスです。PRIMARY KEY 列は、時間ベースの列である場合がよくあります。
- UNIQUE インデックス: このインデックスは、列の値が重複しないことを保証します。時間ベースの列で UNIQUE インデックスを使用すると、特定のタイムスタンプに関連する行を迅速に検索できます。
MariaDBでインデックスを作成するには、次の構文を使用します。
CREATE INDEX index_name ON table_name (column_name);
例
次の例では、mytable テーブルの timestamp 列に PRIMARY KEY インデックスを作成します。
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL,
data VARCHAR(255)
);
CREATE UNIQUE INDEX idx_timestamp ON mytable (timestamp);
CREATE INDEX idx_timestamp_range ON mytable (timestamp);
インデックスは、クエリで使用される列に指定することで使用できます。次の例では、timestamp 列を使用して mytable テーブルから行を検索します。
SELECT * FROM mytable WHERE timestamp = '2024-04-13';
このクエリは、timestamp 列に INDEX インデックスがある場合、インデックスを使用して迅速に実行されます。
インデックスは、パフォーマンスを向上させるために役立ちますが、適切に使用しないと逆効果になる可能性があります。インデックスを作成する前に、クエリのパターンを分析し、必要に応じてインデックスを削除または再作成することが重要です。
MariaDBのインデックスに関する詳細情報については、次のリソースを参照してください。
注意事項
- 上記の例は、あくまでも参考情報です。実際の状況に合わせて、適切なインデックスを作成してください。
- インデックスを作成する前に、クエリのパターンを分析し、必要に応じてインデックスを削除または再作成することが重要です。
- インデックスは、テーブルのサイズを増加させる可能性があります。
MariaDBでテーブルを時間軸でインデックスするサンプルコード
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL,
data VARCHAR(255)
);
例2：UNIQUE インデックス
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL UNIQUE,
data VARCHAR(255)
);
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL,
data VARCHAR(255),
INDEX (timestamp)
);
クエリ
以下のクエリは、timestamp 列を使用して mytable テーブルから行を検索します。
SELECT * FROM mytable WHERE timestamp = '2024-04-13';
MariaDBでテーブルを時間軸でインデックスするその他の方法
パーティショニング
テーブルを時間範囲に基づいてパーティション化することで、特定の期間内のデータにのみクエリを実行できます。これにより、インデックスのスキャン範囲が狭くなり、パフォーマンスが向上します。
次の例では、mytable テーブルを月ごとにパーティション化します。
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL,
data VARCHAR(255)
)
PARTITION BY MONTH(timestamp)
(
PARTITION p2024_01 VALUES LESS THAN (LAST_DAY('2024-01-01') + INTERVAL 1 DAY),
PARTITION p2024_02 VALUES LESS THAN (LAST_DAY('2024-02-01') + INTERVAL 1 DAY),
PARTITION p2024_03 VALUES LESS THAN (LAST_DAY('2024-03-01') + INTERVAL 1 DAY),
PARTITION p2024_04 VALUES LESS THAN (LAST_DAY('2024-04-01') + INTERVAL 1 DAY)
);
以下のクエリは、2024年4月のデータのみを検索します。
SELECT * FROM mytable
WHERE timestamp >= '2024-04-01' AND timestamp < '2024-05-01';
ロールアップパーティショニングを使用すると、複数のパーティションをまとめてクエリすることができます。これにより、データ量が多い場合でも、パフォーマンスを維持することができます。
CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
timestamp DATETIME NOT NULL,
data VARCHAR(255)
)
PARTITION BY MONTH(timestamp),
SUBPARTITION BY YEAR(timestamp)
(
PARTITION p2024_01_2024 VALUES LESS THAN (LAST_DAY('2024-01-01') + INTERVAL 1 DAY),
PARTITION p2024_02_2024 VALUES LESS THAN (LAST_DAY('2024-02-01') + INTERVAL 1 DAY),
PARTITION p2024_03_2024 VALUES LESS THAN (LAST_DAY('2024-03-01') + INTERVAL 1 DAY),
PARTITION p2024_04_2024 VALUES LESS THAN (LAST_DAY('2024-04-01') + INTERVAL 1 DAY),
PARTITION p2024_ALL_2024 VALUES LESS THAN (LAST_DAY('2024-12-31') + INTERVAL 1 DAY)
);
SELECT * FROM mytable
WHERE timestamp >= '2024-04-01' AND timestamp < '2024-05-01';
MATERIALIZED VIEW
マテリアライズドビューを使用すると、集計済みのデータを別のテーブルに保存することができます。これにより、集計クエリのパフォーマンスを向上させることができます。
次の例では、mytable テーブルの月ごとの集計データを mytable_monthly_summary テーブルに保存します。
CREATE MATERIALIZED VIEW mytable_monthly_summary AS
SELECT
MONTH(timestamp) AS month,
COUNT(*) AS count,
MIN(timestamp) AS min_timestamp,
MAX(timestamp) AS max_timestamp
FROM mytable
GROUP BY MONTH(timestamp);
SELECT * FROM mytable_monthly_summary
WHERE month = 4 AND year = 2024;
サブクエリを使用すると、クエリ内で別のクエリを実行することができます。これにより、複雑なクエリをより簡単に記述することができます。
次の例では、mytable テーブルから2024
mariadb
