検索機能の設計と仕様書の書き方｜検索条件・ソート・ページング

検索機能の仕様書とは、「どの項目を・どの一致方式で・未入力のときどう扱い・結果をどの順で・何件ずつ見せるか」を表で決めた文書です。 画面の見た目だけを決めて作り始めると、「大文字と小文字は区別するのか」「2つ選んだら AND か OR か」「0件のとき何を表示するか」があとから次々に出てきます。先に表で決めておけば、実装もテストも同じ表を見て進められます。

この記事では、検索条件定義表とソート・ページング・0件時・上限件数の仕様表をテンプレートと記入例で示し、一致方式ごとにインデックスが使われるかを PostgreSQL と MySQL の EXPLAIN で確かめた結果を載せます。

確認環境：PostgreSQL 16.15（データベースの照合順序は C.UTF-8、拡張 pg_trgm）、MySQL 8.0.46（照合順序は既定の utf8mb4_0900_ai_ci）。どちらも20万行のテーブルで 2026-10-05 に実行しました。

この記事の結論

• 検索機能の仕様は4つの表で決めます。検索条件定義表、ソート仕様、ページング仕様、結果表示（0件・上限超過）の仕様です。
• 検索条件は1項目ごとに、一致方式・複数指定時の AND/OR・未入力時・大文字小文字・全角半角・初期値の6つを必ず埋めます。
• 一致方式は「コードや番号は完全一致か前方一致」「名前は部分一致」「長い文章は全文検索」が基本で、件数が多い項目の部分一致には専用のインデックスを用意します。
• ソートは同じ値が並んだときの第2キー（主キーなど）まで書きます。書かないと、ページをめくったときに同じ行が2回出たり抜けたりします。
• 「大文字小文字を区別しない」と書くだけでは足りません。同じ SQL でも、PostgreSQL と MySQL で結果が変わるので、区別の決まりは項目ごとに言葉で書きます。

検索仕様で決めること早見表

検索条件定義表のテンプレートと記入例

テンプレート

1つの検索項目を1行で書きます。空欄を残さないのが決まりです。該当しない列には「―」を入れます。

各列に書く内容は次のとおりです。

あわせて、項目と項目のあいだは AND でつなぐのかを表の上に1行で書きます（ほとんどの一覧画面は AND です）。

記入例：備品予約システムの備品一覧

架空の備品予約システムの「備品一覧」画面を題材にした記入例です。項目間はすべて AND でつなぎます。

表計算ソフトに貼れるタブ区切りです。

No  項目名 入力形式    対象の列    一致方式    複数指定時   未入力時    大文字小文字  全角半角・かな 初期値 最大桁数    備考
1   備品コード   テキスト    items.code  前方一致    ―（1つだけ） 条件にしない  区別しない   全角英数字は半角に直して検索  空   20  前後の空白は取り除く
2   備品名 テキスト    items.name  部分一致    空白で区切った語をAND    条件にしない  区別しない   全角半角を同じとみなす。ひらがなとカタカナは区別する  空   50  語は最大5つ。%と_は文字として扱う
3   分類  複数選択    items.category  いずれかに一致 選んだ値のOR 条件にしない（全分類） ―   ―   未選択 ―   選択肢は分類マスタから取得
4   登録日 日付の範囲   items.created_at    範囲  ―   開始だけ・終了だけの指定も可  ―   ―   空   ―   終了日は当日の23:59:59までを含む。開始が終了より後ならエラー
5   貸出中を除く  チェックボックス    reservations    条件つき除外  ―   チェックなしは全件   ―   ―   チェックなし  ―   今日の予約がある備品を除く
6   備考  テキスト    items.note  全文検索    空白で区切った語をAND    条件にしない  区別しない   全角半角を同じとみなす 空   100 単語単位で一致（語の途中からは一致しない）

見落としやすい決めごと

ソート・ページング・0件時・上限件数のテンプレート

ソート仕様

ページング仕様

結果表示の仕様（0件・上限件数・エラー）

完全一致・前方一致・部分一致・全文検索の違い

PostgreSQL 16 で EXPLAIN を実行する

20万行の備品テーブルを作り、一致方式ごとに実行計画を見ます。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE TABLE items (
  id         bigint PRIMARY KEY,
  code       text NOT NULL,
  name       text NOT NULL,
  category   text NOT NULL,
  note       text NOT NULL,
  created_at timestamp NOT NULL
);
INSERT INTO items
SELECT g,
       'EQ-' || lpad(g::text, 6, '0'),
       (ARRAY['プロジェクター','ノートPC','モニター','ホワイトボード','延長コード','Webカメラ','スピーカー','三脚'])[1 + g % 8] || ' ' || (g % 5000)::text || '号',
       (ARRAY['映像','PC','音響','文具'])[1 + g % 4],
       'room' || (g % 300)::text || ' shelf' || (g % 37)::text || ' ' || md5(g::text),
       timestamp '2026-01-01' + (g % 250) * interval '1 day'
FROM generate_series(1, 200000) g;
CREATE INDEX items_code_idx ON items (code);
ANALYZE items;

完全一致は、通常のインデックスがそのまま使われます。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM items WHERE code = 'EQ-012345';

 Index Scan using items_code_idx on items (actual rows=1 loops=1)
   Index Cond: (code = 'EQ-012345'::text)

前方一致は、今回の環境（照合順序 C.UTF-8）では通常のインデックスが使われず、全行を読みました。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM items WHERE code LIKE 'EQ-0123%';

 Gather (actual rows=100 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on items (actual rows=33 loops=3)
         Filter: (code ~~ 'EQ-0123%'::text)
         Rows Removed by Filter: 66633

text_pattern_ops を指定したインデックスを足すと、前方一致でもインデックスが使われます。

CREATE INDEX items_code_pat_idx ON items (code text_pattern_ops);

 Index Scan using items_code_pat_idx on items (actual rows=100 loops=1)
   Index Cond: ((code ~>=~ 'EQ-0123'::text) AND (code ~<~ 'EQ-0124'::text))
   Filter: (code ~~ 'EQ-0123%'::text)

部分一致は、通常のインデックスでは全行を読みます。pg_trgm の GIN インデックスを足すと、インデックスから候補を絞れます。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM items WHERE name LIKE '%カメラ 12%';

 Gather (actual rows=520 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on items (actual rows=173 loops=3)
         Filter: (name ~~ '%カメラ 12%'::text)
         Rows Removed by Filter: 66493

CREATE INDEX items_name_trgm_idx ON items USING gin (name gin_trgm_ops);

 Bitmap Heap Scan on items (actual rows=520 loops=1)
   Recheck Cond: (name ~~ '%カメラ 12%'::text)
   Heap Blocks: exact=139
   ->  Bitmap Index Scan on items_name_trgm_idx (actual rows=520 loops=1)
         Index Cond: (name ~~ '%カメラ 12%'::text)

同じインデックスがあっても、検索語が2文字のときは全行を読む計画になりました（'%4999号%' のように長い語ではインデックスが使われました）。pg_trgm は文字列を3文字の組に分けて索引にするしくみで、公式文書にも、3文字の組を取り出せない検索語ではインデックス全体を読むことになると書かれています。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM items WHERE name LIKE '%9号%';

 Seq Scan on items (actual rows=20000 loops=1)
   Filter: (name ~~ '%9号%'::text)
   Rows Removed by Filter: 180000

全文検索は、to_tsvector の結果に GIN インデックスを作ります。

CREATE INDEX items_note_fts_idx ON items USING gin (to_tsvector('simple', note));
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM items
WHERE to_tsvector('simple', note) @@ to_tsquery('simple', 'room12 & shelf7');

 Bitmap Heap Scan on items (actual rows=18 loops=1)
   Recheck Cond: (to_tsvector('simple'::regconfig, note) @@ '''room12'' & ''shelf7'''::tsquery)
   Heap Blocks: exact=18
   ->  Bitmap Index Scan on items_note_fts_idx (actual rows=18 loops=1)
         Index Cond: (to_tsvector('simple'::regconfig, note) @@ '''room12'' & ''shelf7'''::tsquery)

全文検索は単語の単位で一致します。単語 room12 では 667 件が見つかりますが、語の途中から始まる oom12 では 0 件でした。同じ内容を LIKE の部分一致（'%oom12 %'）で探すと 667 件です。「部分一致」と「全文検索」は結果が変わるので、仕様書では別の一致方式として書き分けます。

SELECT count(*) AS word FROM items WHERE to_tsvector('simple', note) @@ to_tsquery('simple', 'room12');
SELECT count(*) AS part FROM items WHERE to_tsvector('simple', note) @@ to_tsquery('simple', 'oom12');
SELECT count(*) AS like_part FROM items WHERE note LIKE '%oom12 %';

 word | 667
 part | 0
 like_part | 667

（3つの結果を1つにまとめて表示しています。）

MySQL 8.0 で EXPLAIN を実行する

同じ内容の20万行を MySQL に入れて確かめます。

CREATE TABLE items (
  id         bigint PRIMARY KEY,
  code       varchar(20) NOT NULL,
  name       varchar(100) NOT NULL,
  category   varchar(20) NOT NULL,
  note       varchar(200) NOT NULL,
  created_at datetime NOT NULL,
  KEY items_code_idx (code),
  KEY items_name_idx (name)
);
SET SESSION cte_max_recursion_depth = 200000;
INSERT INTO items
WITH RECURSIVE seq(g) AS (SELECT 1 UNION ALL SELECT g + 1 FROM seq WHERE g < 200000)
SELECT g,
       CONCAT('EQ-', LPAD(g, 6, '0')),
       CONCAT(ELT(1 + g % 8, 'プロジェクター','ノートPC','モニター','ホワイトボード','延長コード','Webカメラ','スピーカー','三脚'), ' ', g % 5000, '号'),
       ELT(1 + g % 4, '映像','PC','音響','文具'),
       CONCAT('room', g % 300, ' shelf', g % 37, ' ', MD5(g)),
       TIMESTAMPADD(DAY, g % 250, '2026-01-01')
FROM seq;
ANALYZE TABLE items;

EXPLAIN SELECT * FROM items WHERE code = 'EQ-012345';
EXPLAIN SELECT * FROM items WHERE code LIKE 'EQ-0123%';
EXPLAIN SELECT * FROM items WHERE name LIKE '%カメラ 12%';
EXPLAIN SELECT * FROM items WHERE code LIKE '%2345';

ALTER TABLE items ADD FULLTEXT INDEX items_name_ft (name) WITH PARSER ngram;
EXPLAIN SELECT * FROM items WHERE MATCH(name) AGAINST('+"カメラ 12"' IN BOOLEAN MODE);

EXPLAIN の結果から、type・key・rows・Extra の列を抜き出したものです。

MySQL では、前方一致は通常のインデックスがそのまま使われ（type が range）、部分一致と後方一致は name にインデックスがあっても全行を読みました（type が ALL）。ngram の全文検索インデックスを足すと、部分一致と同じ 520 件をインデックスから取り出せました。

SELECT COUNT(*) AS ft_phrase FROM items WHERE MATCH(name) AGAINST('+"カメラ 12"' IN BOOLEAN MODE);
SELECT COUNT(*) AS like_cnt FROM items WHERE name LIKE '%カメラ 12%';
SELECT COUNT(*) AS ft_1char FROM items WHERE MATCH(name) AGAINST('号' IN BOOLEAN MODE);
SELECT COUNT(*) AS like_1char FROM items WHERE name LIKE '%号%';

ft_phrase   520
like_cnt    520
ft_1char    0
like_1char  200000

ngram の区切りの長さ（ngram_token_size）は既定で 2 でした。1文字の検索語は全文検索では 0 件になり、LIKE では全 200,000 件に一致します。全文検索を選ぶときは、仕様書に「検索語は2文字以上」と書いて、入力チェックにも入れておきます。

インデックスが使われるかのまとめ

一致方式を選ぶ判断基準

件数の目安は出発点です。想定する最大件数のデータを入れて EXPLAIN と応答時間を確かめ、その結果を仕様書の備考に書くと、あとから見直す人が判断の根拠をたどれます。

大文字小文字・全角半角の扱いを決める

「区別しない」と書いただけでは、データベースによって結果が変わります。同じ比較を2つのデータベースで実行しました。

-- PostgreSQL
SELECT 'ABC' = 'abc' AS upper_lower, 'ＡＢＣ' = 'ABC' AS full_half,
       'カメラ' = 'ｶﾒﾗ' AS kana_width, 'かめら' = 'カメラ' AS hira_kata,
       'ABC' ILIKE 'abc' AS ilike,
       normalize('ＡＢＣｶﾒﾗ', NFKC) AS nfkc,
       lower(normalize('ＡＢＣ', NFKC)) = 'abc' AS norm_eq;

 upper_lower | full_half | kana_width | hira_kata | ilike |   nfkc    | norm_eq 
-------------+-----------+------------+-----------+-------+-----------+---------
 f           | f         | f          | f         | t     | ABCカメラ | t

-- MySQL（照合順序 utf8mb4_0900_ai_ci）
SELECT 'ABC' = 'abc' AS upper_lower, 'ＡＢＣ' = 'ABC' AS full_half,
       'カメラ' = 'ｶﾒﾗ' AS kana_width, 'かめら' = 'カメラ' AS hira_kata,
       'ハ' = 'パ' AS dakuten;

+-------------+-----------+------------+-----------+---------+
| upper_lower | full_half | kana_width | hira_kata | dakuten |
+-------------+-----------+------------+-----------+---------+
|           1 |         1 |          1 |         1 |       1 |
+-------------+-----------+------------+-----------+---------+

MySQL の既定の照合順序では、「ハ」と「パ」も同じとみなされます。区別したいときは照合順序を指定します。今回の実行では、utf8mb4_bin で 'ABC' = 'abc' が 0（別の文字）、utf8mb4_ja_0900_as_cs で大文字小文字は 0 でひらがなとカタカナは 1（同じ）、utf8mb4_ja_0900_as_cs_ks でひらがなとカタカナも 0 になりました。

PostgreSQL で区別をなくしたいときは、ILIKE で英字の大文字小文字を、normalize(値, NFKC) で全角半角をそろえられます。実際に、小文字で name LIKE 'webカメラ%' と検索すると PostgreSQL は 0 件、ILIKE に変えると 25,000 件、MySQL は LIKE のままで 25,000 件でした。

仕様書には「同じとみなす組み合わせ」を具体的に書きます。

ソートとページングで見落としやすい点

今回のテーブルは 200,000 行ですが、created_at の値は 250 種類しかありません。登録日だけで並べると、同じ日の行どうしの順番が決まらず、ページをめくるたびに入れ替わることがあります。主キーを第2キーに加えると、順番が1通りに決まります。

SELECT count(*), count(DISTINCT created_at) FROM items;

 count  | count 
--------+-------
 200000 |   250

SELECT id FROM items ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40;

ソート仕様に「第2キー」の行を設けているのは、このためです。利用者が選んだ並び順のあとにも、必ず主キーを付けると決めておきます。

確かめて分かったこと

• PostgreSQL の前方一致は、インデックスを作っただけでは使われないことがあります。 照合順序が C.UTF-8 の今回の環境では、通常のインデックスだと全行読みになり、text_pattern_ops を指定したインデックスで使われました。MySQL は通常のインデックスで使われました。
• pg_trgm のインデックスは、検索語が短いと使われませんでした。 '%カメラ 12%' や '%4999号%' では使われ、'%9号%' では全行読みでした。仕様書の「最小の文字数」は性能にも関わります。
• MySQL の ngram 全文検索は、1文字の検索語で 0 件になりました。 LIKE なら 200,000 件に一致する語です。全文検索に切り替えるときは、検索結果が変わる点を利用部門に伝えておきます。
• 全文検索は語の途中からは一致しませんでした（PostgreSQL の simple 設定で oom12 は 0 件）。「部分一致」と書いた項目を全文検索で実装すると、仕様と結果が合わなくなります。
• MySQL の既定の照合順序は、ひらがなとカタカナ、濁点の有無まで同じとみなしました。 PostgreSQL ではどれも別の文字です。データベースを決める前に仕様書を書くときほど、区別の決まりを言葉で書いておく価値があります。
• 登録日のように同じ値が多い列は、並び順の第2キーが欠かせません。 20万行で値が250種類しかなく、1つの日付に 800 行が並びます。

よくある質問

Q. 検索機能の仕様書には何を書けばよいですか？ A. 検索条件定義表、ソート仕様、ページング仕様、結果表示（0件・上限件数・エラー）の4つを書きます。検索条件は1項目1行で、一致方式・複数指定時・未入力時・大文字小文字・全角半角・初期値を埋めます。

Q. 部分一致と全文検索の違いは何ですか？ A. 部分一致は文字の並びがどこかに含まれていれば一致し、全文検索は文章を単語や決まった長さに分けた索引で一致を調べます。今回の実行でも、語の途中から始まる検索語や1文字の検索語で結果の件数が変わりました。

Q. 前方一致なのにインデックスが使われないときは？ A. PostgreSQL では、照合順序によって通常のインデックスが LIKE の前方一致に使われません。text_pattern_ops を指定したインデックスを追加し、EXPLAIN で Index Scan になることを確かめます。

Q. 複数の検索条件は AND と OR のどちらでつなぎますか？ A. 項目と項目のあいだは AND、1つの項目で複数選んだ値どうしは OR が基本です。テキスト項目に空白区切りで語を入れた場合は AND にすることが多く、仕様書には項目ごとに明記します。

Q. 検索結果が0件のときは何を表示すればよいですか？ A. 0件であることを伝える文言と、次の行動（条件を減らすなど）の案内を表示します。エラーの表示とは文言と見た目を分け、表の見出しを残すかどうかも仕様に書きます。

まとめ

• 検索機能の仕様は、検索条件定義表・ソート仕様・ページング仕様・結果表示の仕様の4つの表で決めます。
• 検索条件は1項目ごとに、一致方式・複数指定時・未入力時・大文字小文字・全角半角・初期値を空欄なしで埋めます。
• コードは完全一致か前方一致、名前は部分一致、長い文章は全文検索が基本で、件数が多い部分一致には専用のインデックスを用意します。
• 前方一致のインデックスは PostgreSQL と MySQL で使われ方が違いました。 想定件数のデータで EXPLAIN を確かめます。
• 文字の区別は、同じとみなす組み合わせを言葉で書きます。データベースの既定の動きに任せると、製品ごとに結果が変わります。
• ソートには主キーを第2キーとして必ず付け、0件時と上限件数の動きも文言まで決めておきます。

参考・出典

• PostgreSQL 16 文書「pg_trgm」（https://www.postgresql.org/docs/16/pgtrgm.html ）
• PostgreSQL 16 文書「Operator Classes and Operator Families」（https://www.postgresql.org/docs/16/indexes-opclass.html ）
• MySQL 8.0 リファレンスマニュアル「ngram Full-Text Parser」（https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html ）

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。