【要約】SQLアンチパターン　「プアマンズ・サーチエンジン（貧者のサーチエンジン）」

# はじめに
開発をしていく上で、SQLの非効率な書き方やDB設計のアンチパターンを知っておきたいと思いました。

著書「[SQLアンチパターン](https://www.oreilly.co.jp/books/9784873115894/)」を読んで、各部から印象に残った章の内容を抜粋してまとめていきます。

読みづらいところなどあるかと思いますが、少しでも参考になれば幸いです。

:::note info
SQLアンチパターンの要約一覧です。
他の章についても要約してます！
- [【要約】SQLアンチパターン　「ナイーブツリー（素朴な木）」](https://qiita.com/k12da/items/52c3c938d93aaf42926f)
- [【要約】SQLアンチパターン　「インデックスショットガン（闇雲インデックス）」](https://qiita.com/k12da/items/a354caf1ed6a53cb0845)
- [【要約】SQLアンチパターン　「プアマンズ・サーチエンジン（貧者のサーチエンジン）」](https://qiita.com/k12da/items/15705034678e30b1f43d)
- [【要約】SQLアンチパターン　「ディプロマティック・イミュニティ（外交特権）」](https://qiita.com/k12da/items/1c59b4c094ace27a0803)
:::

# Ⅲ部 クエリのアンチパターン
## 16章 プアマンズ・サーチエンジン（貧者のサーチエンジン）

### アンチパターン：パターンマッチ述語を使用する
キーワード検索を実装するときは**LIKE述語**がよく使われており、0個以上の文字と一致するワイルドカード（%）を扱うことができます。
キーワードの前後にこのワイルドカードを指定すると、その語が含まれるすべての文字列がマッチします。

```Search/anti/like.sql
    SELECT * FROM Bugs WHERE description LIKE '%crash%';
```

多くのデータベース製品は、**正規表現**も独自の方法でサポートしています。正規表現を使うと、どのような部分文字列に対してもパターンマッチを行えるため、ワイルドカードは不要になります。

以下はMySQLの正規表現述語を使った例です。
```Search/anti/regexp.sql
    SELECT * FROM Bugs WHERE description REGEXP 'crash';
```

パターンマッチ述語の問題点
- **パフォーマンスの低下**
パターンマッチ述語は従来の<INS>インデックスのメリットを得られないため、テーブルの全ての行をスキャンしなければいけません</INS>。

- **LIKEや正規表現の単純なパターンマッチでは、意図しないマッチが生じてしまう**
以下のクエリは、単語「one」を含むテキストとマッチしますが、「money」、「prone」、「lonely」などもマッチしてしまいます。
```Search/anti/like-false-match.sql
    SELECT * FROM Bugs WHERE description LIKE '%one%';
```

### アンチパターンを用いても良い場合
**使用頻度が極めて低いクエリ**には、最適化に労力をかける意味がありません。
滅多に使用しないクエリのためにインデックスを更新するのは、その非効率なクエリをインデックス無しで実行するのと同じくらいコストがかかります。

単純なケースのためにパターンマッチを行う場合には、欠点を最小限に抑えつつ、十分な結果を得ることもできます。

### 解決策：適切なツールを使用する
最善の方法は、SQLの代わりに**全文検索**エンジンを使うことです。次善の策は、繰り返しのコストを減らすために検索結果を保存することです。

以下はデータベース製品における全文検索機能の概要です。

#### MySQLのフルテキストインデックス
MySQLのフルテキストインデックス（全文検索するためのインデックス）を定義できるのは、CHAR、VARCHAR、TEXT型の列です。

以下はBugsテーブルのsummary列とdescription列の内容を含むフルテキストインデックスの定義です。

```Search/soln/mysql/alter-table.sql
    ALTER TABLE Bugs ADD FULLTEXT INDEX bugfts(summary, description);
```
インデックスに格納されたテキストからキーワードを検索するには、MATCH関数を使います。
MATCH関数には、フルテキストインデックスに関連づけられた列名を指定する必要があります。列名は上記の`summary, description`のように複数指定することができます。

```Search/soln/mysql/match.sql
    SELECT * FROM Bugs WHERE MATCH(summary, description) AGAINST('crash');
```
※[AGAINST](https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search.html)には、検索する文字列と、実行する検索のタイプを示すオプションの修飾子を指定します。 

#### PostgreSQLでのテキスト検索
PostgreSQLを使ってパフォーマンスを最適化するには、テキスト検索可能な特別なデータ型[**TSVECTOR**](https://www.postgresql.jp/docs/9.4/datatype-textsearch.html)を用いてコンテンツを格納する必要があります。

```Search/soln/postgresql/create-table.sql
CREATE TABLE Bugs (
    bug_id      SERIAL PRIMARY KEY,
    summary     VARCHAR(80),
    description TEXT,
    ts_bugtext  TSVECTOR
    -- 他の列...
);
```
TSVECTOR列は、検索対象のテキスト列の内容と常に同期する必要があります。
PostgreSQLでは同期作業を容易にするために、組み込みのトリガーが提供されています。
```Search/soln/postgresql/trigger.sql
CREATE TRIGGER ts_bugtext BEFORE INSERT OR UPDATE ON Bugs
FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(ts_bugtext, 'pg_catalog.english', summary, description);
```
さらに、TSVECTOR列に対して[GIN(汎用転置インデックス)](https://www.postgresql.jp/docs/9.4/gin-intro.html)を作成しなければいけません。
```Search/soln/postgresql/create-index.sql
    CREATE INDEX bugs_ts ON Bugs USING GIN(ts_bugtext);
```
これで、PostgreSQLのテキスト検索演算子（`@@`）を用いて、フルテキストインデックスを活用した効果的な全文検索が行えるようになります。
```Search/soln/postgresql/create-index.sql
    SELECT * FROM Bugs WHERE ts_bugtext @@ to_tsquery('crash');
```

# まとめ
LIKEのようなパターンマッチ述語は、意図しないマッチや、テーブルの全ての行をスキャンしなければいけない問題などが発生することがある。
データベース製品の全文検索エンジンなどの機能を使用することで、検索におけるパフォーマンスを向上させることができる。

# 所感
今回は時間の都合で抜粋してまとめました。ですが正直言って、他の章の内容も良かったので、テーマを一つに絞って書くのは難しかったです。

（個人的に、13章の「フィア・オブ・ジ・アンノウン（恐怖のunknown）」はもう一度読み返したい）

ただそれよりも重要なのは、「**実務や実践の場で活かす**」ことだと思っています。今後DB設計や実装をしていく中で、アンチパターンについて思い出しながら理解を深めていければと思います。

# 参考記事
[世界一わかりやすい FULLTEXT INDEX の説明と気を付けるべきポイント](https://zenn.dev/hiroakey/articles/9f68ad249af20c)
[MySQL 8.0 リファレンスマニュアル  /  12.10 全文検索関数](https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search.html)
[PostgreSQL 9.4.5文書 8.11.1. tsvector](https://www.postgresql.jp/docs/9.4/datatype-textsearch.html)