［MariaDB］インデックス

データベースとテーブルの作成

CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

##src|SQL|sql##
CREATE TABLE articles (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    status_id   INT,
    category_id INT,
    
    INDEX idx_status_id (status_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status_category (status_id, category_id)
);

テストデータの作成
bash で次のコマンドを実行して CSV ファイルを作成する。

seq 1 10000 | awk -v srand_seed="$(date +%s)" '
BEGIN { srand(srand_seed) }
{
    title = "記事 " $1
    status = int(rand()*20)+1
    category = int(rand()*20)+1
    print title "," status "," category
}' > articles_data.csv

awk は「awk（オーク）の使い方について」を参考にしてください。

MariaDB に接続して、

use test_db;

LOAD DATA LOCAL INFILE 'articles_data.csv' INTO TABLE articles FIELDS TERMINATED BY ',' (title, status_id, category_id);

EXPLAIN SELECT * FROM articles WHERE status_id = 1;

+------+-------------+----------+------+-----------------------------------+---------------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys                     | key           | key_len | ref   | rows | Extra |
+------+-------------+----------+------+-----------------------------------+---------------+---------+-------+------+-------+
|    1 | SIMPLE      | articles | ref  | idx_status_id,idx_status_category | idx_status_id | 5       | const | 523  |       |
+------+-------------+----------+------+-----------------------------------+---------------+---------+-------+------+-------+

possible_keys は、使用するインデックスの候補です。
key は、実際に使用したインデックスです。上記の場合、idx_status_id が使用されており、インデックスの効果があります。
rows は、スキャンする推定の行数です。

EXPLAIN SELECT * FROM articles WHERE category_id = 1;

+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+
| id   | select_type | table    | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+
|    1 | SIMPLE      | articles | ref  | idx_category_id | idx_category_id | 5       | const | 536  |       |
+------+-------------+----------+------+-----------------+-----------------+---------+-------+------+-------+

key が idx_category_id となっており、インデックスが使用されています。

MariaDB [test_db]> EXPLAIN SELECT * FROM articles WHERE 3 >= category_id  AND category_id >= 1;

+------+-------------+----------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys   | key             | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+-----------------+-----------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | articles | range | idx_category_id | idx_category_id | 5       | NULL | 1522 | Using index condition |
+------+-------------+----------+-------+-----------------+-----------------+---------+------+------+-----------------------+

key が idx_category_id となっており、インデックスが使用されています。
category_id の 1 ～ 3 のデータの検索をしていますが、1 ～ 5 の範囲に広げると、ヒットする行数が増えると推定されて、フルスキャンになる可能性があります。

EXPLAIN SELECT * FROM articles WHERE status_id = 1 AND category_id = 1;

+------+-------------+----------+------+---------------------------------------------------+---------------------+---------+-------------+------+-------+
| id   | select_type | table    | type | possible_keys                                     | key                 | key_len | ref         | rows | Extra |
+------+-------------+----------+------+---------------------------------------------------+---------------------+---------+-------------+------+-------+
|    1 | SIMPLE      | articles | ref  | idx_status_id,idx_category_id,idx_status_category | idx_status_category | 10      | const,const | 33   |       |
+------+-------------+----------+------+---------------------------------------------------+---------------------+---------+-------------+------+-------+

key が idx_status_category となっており、複合インデックスの方が使用されています。

EXPLAIN SELECT * FROM articles WHERE title = '記事1';

+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | NULL | 9976 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

type が ALL になっており、インデックスが使用されないため、テーブルがフルスキャンされます。