MySQL全文インデックスを使用して検索エンジンのサンプルコードの簡易版を実現する

MySQL全文インデックスを使用して検索エンジンのサンプルコードの簡易版を実現する

序文

  • 全文インデックスを使用できるのは Innodb と MyISAM ストレージ エンジンのみです (Innodb は MySQL 5.6 以降で全文インデックスをサポートしています)
  • char、varchar、およびtext型のフィールドはフルテキストインデックスを作成できます(フルテキストインデックスタイプ)
  • 全文インデックスはキーワードに基づいています。異なるキーワードを区別するには、ストップワードを使用する必要があります。
  • 英語の単語はスペースとカンマで区切られますが、中国語の単語の区切り方は不便です(文中の異なるキーワードを区別する方法がわかりません)
  • 組み込みの単語分割パーサー ngram は、中国語、日本語、韓国語をサポートします (文を一定数のフレーズに分割します)
  • テーブルに大量のデータを書き込む場合、データを書き込んだ後にフルテキストインデックスを作成する方が高速です(インデックスの維持にかかるオーバーヘッドが削減されます)
  • 全文索引付け原則の逆索引 (データ構造) では、通常、連想配列を使用して、単語と文書内の単語の位置とのマッピングを補助テーブルに格納します。

使用

MATCH() で検索 ... AGAINST

match() は列が検索されていることを示し、awards は文字列が検索されていることを示します。

デフォルトの単語分割を表示します(これらの単語を使用して異なるキーワードを区別します)。単語分割をカスタマイズして異なるキーワードを区別することもできます。

information_schema.INNODB_FT_DEFAULT_STOPWORD から * を選択します。

のように

+-------+ 
| 値 | 
+-------+ 
| ア | 
| について | 
| の | 
| は | 
| として | 
| で | 
| である | 
| 作成者 | 
| com | 
| ドイツ語 | 
| ja | 
| のために | 
| から |

3種類の全文検索方法

自然言語検索

MATCH AGAINSTでチェックする特定の文字列を渡します。デフォルトのメソッド

ブール検索

検索文字列に演算子を追加します。「+」は必ず含むことを意味し、「-」は含まないことを意味し、「*」はワイルドカードを意味します。渡された文字列が短い場合やストップワードに含まれていても、フィルタリングされません。

クエリ拡張検索

検索文字列は自然言語検索を実行するために使用され、検索によって返された最も関連性の高い行の単語が検索文字列に追加され、検索が再度実行されると、クエリは2番目の検索からの行を返します。

関連パラメータ

関連パラメータを設定する

innodb_ft_min_token_size

デフォルト値は 3 です。これは、少なくとも 3 文字をキーワードとして使用できることを意味します。この値を増やすと、フルテキスト インデックスのサイズを小さくすることができます。

innodb_ft_max_token_size

デフォルト値は 84 です。つまり、キーワードには最大 84 文字を含めることができます。この値を制限すると、フルテキスト インデックスのサイズを縮小できます。

nグラムトークンサイズ

デフォルト値は 2 です。これは、組み込みの単語分割パーサーのキーワードとして 2 つの文字が使用されることを意味します。たとえば、「abcd」の全文インデックスを作成する場合、キーワードは「ab」、「bc」、「cd」です。
innodb_ft_min_token_size と innodb_ft_max_token_size は、ngram パーサーを使用する場合は効果がありません。

これら 3 つのパラメータは動的に変更できないことに注意してください。これらのパラメータを変更する場合は、MySQL サービスを再起動してフルテキスト インデックスを再確立する必要があります。

フルテキストインデックスを使用して InnoDB エンジンをテストする

準備する

1. 目標

  • 記事に特定のキーワードが含まれているかどうか、一連の記事に特定のキーワードが何回登場するかを確認します。
  • 記事のタイトルに特定のキーワードが含まれているかどうかを確認する

2. ディスクIOの負荷を軽減するために以下のパラメータを設定します。

グローバルsync_binlog=100を設定します。
グローバル innodb_flush_log_at_trx_commit=2 を設定します。

3. 1kwデータをインポートしてフルテキストインデックスをテストする

データはオンライン検索から得たものである

抽出コード: iyip

4. 記事テーブルの構造

テーブル「記事」を作成します(
 `id` bigint(10) NULLではない、
 `url` varchar(1024) 文字セット latin1 NOT NULL デフォルト '',
 `title` varchar(256) NOT NULL デフォルト '',
 `source` varchar(32) DEFAULT '' COMMENT '真のソース',
 `keywords` varchar(32) デフォルト NULL,
 `publish_time` タイムスタンプ NULL デフォルト NULL、
 主キー (`id`)、
 キー `title_idx` (`title`)
) エンジン=InnoDB

myloader を使用して複数のスレッドでテストデータをインポートする

まずテストデータを解凍します tar -zxf mydumper_dump_article.tar.gz
時間 myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump_article -v 3

5. データのインポート後のデータ量とデータファイルおよびインデックスファイルのサイズ

`article` から COUNT(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 10000000 |
+----------+
セット1列(7.85秒)

SELECT table_name, CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),'M') AS dbdata_size, CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),'M') AS dbindex_size, CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),'G') AS `db_size(G)`, AVG_ROW_LENGTH,table_rows,update_time FROM information_schema.tables WHERE table_schema = DATABASE() and table_name='article';
+------------+-------------+--------------+-------------+----------------+------------+---------------------+
| テーブル名 | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | テーブル行 | 更新時間 |
+------------+-------------+--------------+-------------+----------------+------------+---------------------+
| 記事 | 3,710.00M | 1,003.00M | 4.60G | 414 | 9388739 | 2019-07-05 15:31:37 |
+------------+-------------+--------------+-------------+----------------+------------+---------------------+

デフォルトの方法を使用してフルテキストインデックスを作成する

1. 表にはすでにキーワードフィールド(記事の内容の簡単な説明)があり、「,」が単語の区切りとして使用されています。

記事からキーワードを10個まで選択してください。
+-------------------------------------------------+
| キーワード |
+-------------------------------------------------+
| NULL |
| NULL |
| 、結婚、愛|
| ヘアスタイル、サイドパート、メイク、ファッション |
| シャオ A、|
| 、服の組み合わせ、女性、ファッション |
| 美しい、女性|
| バレンタインデー、東莞、女性 |
| 肌、スキンケア、スキンケア、食品栄養、美容、ヘルスケア |
| 三里屯、北京、ファッション |
+-------------------------------------------------+

2. 全文インデックスなしでキーワードを検索する

テーブル全体のスキャンが必要です

'%Fashion%' のようなキーワードが含まれる記事から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 163 |
+----------+
セット1列目(7.56秒)

3. キーワードフィールドの全文インデックスを作成します(単語セグメントとして、を使用します)。

my.cnf 構成ファイルで innodb_ft_min_token_size を設定し、MySQL サービスを再起動します (キーワードとして使用される最小文字数は 2 で、キーワードとして使用されるデフォルトの文字数は 3 です)

[mysqld]
innodb_ft_min_token_size=2

3.1 カスタムストップワードの設定(単語の分割)

mysqlを使用します。
テーブル my_stopwords(VALUE VARCHAR(30)) を作成します。エンジン = INNODB;
my_stopwords(VALUE) に VALUE (',') を挿入します。
グローバル innodb_ft_server_stopword_table = 'mysql/my_stopwords' を設定します。
グローバル変数を表示します。WHERE Variable_name IN('innodb_ft_min_token_size','innodb_ft_server_stopword_table');
+---------------------------------+--------------------+
| 変数名 | 値 |
+---------------------------------+--------------------+
| innodb_ft_min_token_size | 2 |
| innodb_ft_server_stopword_table | mysql/my_stopwords |
+---------------------------------+--------------------+

3.2 全文インデックスを作成する

テーブル article を変更し、フルテキスト インデックス idx_full_keyword(keywords) を追加します。
* [ ] クエリは正常、影響を受けた行は 0 行、警告は 1 件 (1 分 27.92 秒)
* [ ] レコード: 0 重複: 0 警告: 1

3.3 残りのディスク容量は十分である必要があります。元のテーブルは 4.6G で、残りのディスク容量は 5.7G です。フルテキスト インデックスの追加も失敗します。

df -h
ファイルシステムサイズ 使用済み 使用可能使用率 マウント先
/dev/vda1 7.8G 6.3G 1.2G 85% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/mapper/vg_opt-lvol0
            19G 12G 5.7G 68% /データ

元のテーブルサイズの一時ファイルが作成されます 8.6K 7月5日 16:19 #sql-5250_3533.frm
 4.4G 7月5日 16:20 #sql-ib117-1768830977.ibd


テーブル article を変更し、フルテキスト インデックス idx_full_keyword(keywords) を追加します。
エラー 1114 (HY000): テーブル「article」がいっぱいです

3.4 作成した全文インデックスを使用してキーワードの出現回数を照会する

クエリの応答時間が大幅に改善され、わずか0.05秒になりました。「%time尚%」などのキーワードを使用すると7.56秒かかります。

select count(*) from article where match(keywords) against('%time尚%');
+----------+
| カウント(*) |
+----------+
| 163 |
+----------+
セット内の1行(0.05秒)

3.5 複数のキーワードを同時に完全に一致させる必要がある場合は、ブール全文検索を使用します。

「北京市三里屯」に完全に一致するレコードの数を示します。select count(*) from article where match(keywords) against('+北京市三里屯' in boolean mode);
+----------+
| カウント(*) |
+----------+
| 1 |
+----------+
セット内の1行(0.06秒)

「Sanlitun」または「Beijing」に一致するレコードの数を示します。select count(*) from article where match(keywords) against('Sanlitun,Beijing');
+----------+
| カウント(*) |
+----------+
| 8 |
+----------+
セット内の1行(0.06秒)

3.6 全文インデックスを作成した後、他のファイルも作成されます

96K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_1.ibd
96K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_2.ibd
96K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_3.ibd
96K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_4.ibd
128K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_5.ibd
256K 7月5日 16:30 FTS_00000000000000a7_000000000000000c0_INDEX_6.ibd
96K 7月5日 16:29 FTS_000000000000000a7_BEING_DELETED_CACHE.ibd
96K 7月5日 16:29 FTS_000000000000000a7_BEING_DELETED.ibd
96K 7月5日 16:30 FTS_000000000000000a7_CONFIG.ibd
96K 7月5日 16:29 FTS_000000000000000a7_DELETED_CACHE.ibd
96K 7月5日 16:29 FTS_000000000000000a7_DELETED.ibd
- 最初の6つは逆インデックス(補助インデックステーブル)を表します
- 7 列目と 8 列目は、現在フルテキスト インデックスからデータが削除されている、削除されたドキュメントを含むドキュメント ID (DOC_ID) を表します。
- 9番目はFULLTEXTインデックスの内部状態を示します
- 10列目と11列目には、削除されたが、フルテキストインデックスからまだデータが削除されていない文書が含まれています。

ngram パーサーを使用して全文インデックスを作成する

1. タイトル フィールドの全文インデックスを作成します (このフィールドには固定ストップワードがなく、ngram パーサーを使用します)

my.cnf 構成ファイルで ngram_token_size (デフォルトは 2、ngram のキーワードとして 2 文字) を設定し、MySQL サービスを再起動する必要があります。ここではデフォルト値の 2 を使用します。

記事のタイトルを 10 件まで選択してください。
+--------------------------------------------------------------------------------+
| タイトル |
+--------------------------------------------------------------------------------+
| 価値がある |
|Launchpad 江南皮革工場の小さな展示会 |
|RAWの舞台裏の珍しい瞬間:「Mad Man」が舞台裏に運ばれる|
|生: 息子は父親に向かって「お前は緑茶の息子だ」と叫んだ。彼は一人で4人と戦った。|
| 細かく装飾された 30 平方メートルの小さなアパートメント 4 組、多数の写真、間取り図 |
|ナイトクラブの女王によるセクシーなスモーキーキャットアイメイク|
|ビッグショーがロック・ジョンソンを激しく非難 |
|少女時代チェ・スヨンの服飾知識 リン・ユナ、ホアン・ミイン、キム・テヨン、チョン・スジョン|                       
|徳陽でのアウトドア、花畑でのバーベキュー|
+--------------------------------------------------------------------------------+

2. タイトルフィールドの全文インデックスを作成する

テーブル article を変更し、パーサー ngram を使用してフルテキスト インデックス ft_index_title(title) を追加します。
クエリは正常、影響を受けた行は 0 行 (3 分 29.22 秒)
レコード: 0 重複: 0 警告: 0

3. 転置インデックスが作成されます(タイトルフィールドが長いほど、作成される転置インデックスが大きくなります)

112M 7月5日 21:46 FTS_0000000000000000a7_000000000000000cd_INDEX_1.ibd
28M 7月5日 21:46 FTS_000000000000000a7_000000000000000cd_INDEX_2.ibd
20M 7月5日 21:46 FTS_000000000000000a7_000000000000000cd_INDEX_3.ibd
140M 7月5日 21:46 FTS_000000000000000a7_000000000000000cd_INDEX_4.ibd
128M 7月5日 21:46 FTS_00000000000000a7_000000000000000cd_INDEX_5.ibd
668M 7月5日 21:46 FTS_000000000000000a7_000000000000000cd_INDEX_6.ibd

4. 全文インデックスを作成せずにタイトル内のキーワードを検索する

タイトルが '%Outdoor%' のような記事から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 22058 |
+----------+
セット1列(8.60秒)

タイトルが '%Background%' のような記事から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1142 |
+----------+

5. 全文インデックスを使用してキーワードを検索する

応答時間が大幅に改善されました

記事からcount(*)を選択し、titleと'Outdoor'が一致するか確認します。
+----------+
| カウント(*) |
+----------+
| 22058 |
+----------+
セット内の1行(0.07秒)

タイトルが '%Background%' のような記事から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1142 |
+----------+
セット1列目(8.31秒)

6. 検索キーワードの文字数が2を超える場合(ngram_token_sizeでサイズが定義されます)、不整合が発生することに注意してください。

通常の検索では、実際にこのキーワードを含むレコードの数は6です。
select count(*) from article where title like '%公子大%';
+----------+
| カウント(*) |
+----------+
| 6 |
+----------+
セット1列目(8.40秒)

全文検索、キーワードを含むレコード数は9443件
select count(*) from article where match(title) against('公子大');
+----------+
| カウント(*) |
+----------+
|9443|
+----------+
セット内の1行(0.06秒)

このキーワードを含む実際のレコード数は 1 です
select count(*) from article where title like '%花田自服%';
+----------+
| カウント(*) |
+----------+
| 1 |
+----------+
セット1列目(8.33秒)

全文検索でこのキーワードを含むレコードの数は3202です
select count(*) from article where match(title) against('花田自服');
+----------+
| カウント(*) |
+----------+
| 3202 |
+----------+
セット内の1行(0.06秒)

結論は

  • MySQL フィールドに、固定のストップワード (英語ではスペース、中国語では「、」「-」など) がある場合、そのフィールドに対してフルテキスト インデックスが作成され、特定のキーワードを含む関連レコードをすばやく検索できるため、シンプルな検索エンジンの効果が得られます。
  • MySQLフィールドに固定ストップワードがない場合、組み込みパーサーngramを使用してフィールド値を固定数のキーワード(ngram_token_size)に分割し、高速検索を行うことができます。検索キーワードの文字数がngram_token_sizeで定義されたサイズと等しくない場合、実際の状況と一致しない問題が発生します。
  • 全文インデックスを使用すると検索が高速になりますが、インデックスの維持にオーバーヘッドも発生します。フィールド長が長いほど、作成される全文インデックスも大きくなり、DML ステートメントのスループットに影響します。専用の全文検索エンジン ES を使用してこれを行うことができます。

参照する

InnoDB FULLTEXT インデックス

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません:
  • MySQL でシンプルな検索エンジンを実装するためのサンプルコード
  • MySQL ベースの検索エンジンである MySQL-Fulltext の詳細な紹介
  • Scrapy+flask+htmlで検索エンジンのサンプルコードを作成する
  • Pythonは検索エンジンをベースに記事重複チェック機能を実装
  • Python 実践的な手書き検索エンジン
  • Python大規模検索エンジン画像クローラーツールの詳細説明
  • 360 検索エンジン自動組み込み PHP 書き換えソリューション
  • 検索エンジンのクロール記録を記録するための PHP 実装コード
  • Python ロスレス音楽検索エンジン実装コード
  • MySQL をベースにしたシンプルな検索エンジンを実装する

<<:  WeChatアプレット実装アンカー位置決め機能の例

>>:  VMware での Ubuntu Docker のインストール (コンテナ構築)

推薦する

Tomcat プロセスの CPU 使用率が高い場合の解決策

目次場合コンテキスト切り替えのオーバーヘッド?要約するCPU は多くの場合、システム パフォーマンス...

Vue Element-ui フォーム検証ルールの実装

目次1. はじめに2. ルール検証の入力モード2.1 サンプルコード2.2、フォーム項目2.3. 小...

SQLと各種NoSQLデータベースの使用シナリオの説明

SQL はメイントランクです。なぜ私はこのように理解するのでしょうか。技術的な観点からリレーショナル...

Vueのコンポーネントのprops属性について詳しく説明します

目次質問1: 小道具は具体的にどのように使用されますか?原理は何ですか?下を見る質問 2: 年齢に ...

CSS で要素を中央揃えにする N 通りの方法

目次序文インライン要素の中央揃えテキストを垂直に中央揃え要素を水平方向に中央揃えにするブロックレベル...

Ubuntu 上の MySQL における中国語文字化け問題の解決方法

問題を見つける最近 Django を学習しているのですが、MySQL データと組み合わせてデータを挿...

CSSを使用して画像フレームアニメーションと曲線の動きを実装する

すべてのアニメーションの基本原理は、対応する画像を短時間で次々に表示し、視覚的に動いているように見せ...

MySQLとOracleの誤解の詳細な説明

目次本質的な違いデータベースセキュリティ権限スキーマの移行パターンオブジェクトの類似性スキーマオブジ...

Linuxでファイルを削除してもスペースが解放されない問題の対処方法

問題の背景業務システムのサーバ監視システムからディスク使用率が90%に達したという早期警告通知が来た...

vue3 を使用してカウント関数コンポーネントのカプセル化例を実装する

目次序文1. カプセル化の重要性2. どのようにカプセル化しますか? 1. アイデア2. 準備2. ...

MySQL InnoDB テーブルスペース暗号化の例の詳細な説明

序文MySQL 5.7.11 以降、MySQL は、別の表領域に格納された InnoDB テーブルの...

MySQL 8.0.17 のインストールと設定のグラフィックチュートリアル

この記事は、参考のためにMySQL 8.0.17のインストールと設定のグラフィックチュートリアルを記...

Nginx サーバーで URL リンクを設定する方法

LNMPのようなアーキテクチャを持つウェブサイトは、一般的にPHPフレームワークに基づいて開発されて...

CSS3 で Taobao に空白スペースを実装する方法

Taobao用の空白スペースを作成します。 ブラウザページを縮小すると、コンテンツ領域は縮小されませ...

VMware Workstation での VMware vSphere のセットアップ (グラフィック チュートリアル)

VMware vSphere は、業界をリードする最も信頼性の高い仮想化プラットフォームです。 v...