Geekly Articles each Day

InnoDBでの全文検索

こんにちは、Habrachitatel！
InnoDBフルテキストデータマイニングは、多くのMySQL / InnoDB開発者にとって有名な頭痛の種です。 最新ではない人のために、私は説明します。 MyISAMテーブルタイプには全文データ検索がありますが、テーブル自体には歴史的に個々のプロジェクトに不可欠な制限がありました。 より高度なInnoDBテーブルタイプには全文検索がありません。 そのため、貧しい開発者はMyISAMの制限またはInnoDBでの検索の欠如のいずれかに耐えなければなりません。 InnoDBで魔法を使わずに、通常の手段のみを使用して本格的な検索を整理する方法を教えてください。 各メソッドの速度特性を比較することも興味深いでしょう。

例えば、10,000レコードで小さなテーブルを取ります。

CREATE TABLEユーザー（
id INT （ 11 ） NOT NULL AUTO_INCREMENT 、
ログインVARCHAR （ 255 ） DEFAULT NULL 、
`password` VARCHAR （ 255 ） DEFAULT NULL 、
name VARCHAR （ 255 ） DEFAULT NULL 、
surname VARCHAR （ 255 ） DEFAULT NULL 、
メールVARCHAR（255）NOT NULLと、
国VARCHAR （ 255 ） DEFAULT NULL 、
city VARCHAR （ 255 ） DEFAULT NULL 、
主 キー （ id ）
）
エンジン= INNODB


このテーブルには、サイトのユーザーのデータが保存されます。 サイト自体には、「Tolstoy Yasnaya Polyana」という形式の任意のクエリを入力できるユーザー検索フォームがあります。 そのようなリクエストを処理するには、一度に複数のフィールドで検索を実行する必要があります。 私たちは、 ログイン フィールド、名、姓、都市、国の検索を必要とします。 要求は、単一の単語（名前または都市）、またはスペースで区切られた一連の単語のいずれかです。 問題は、複数のフィールドでこの単語セットを一度に検索する必要があることです。これは、追加の機能を使用せずにInnoDBで行うのは困難です。

InnoDB内でフルテキストデータを検索するための比較的簡単ないくつかの方法があります。
  1. MyISAMテーブルで表- 「ミラー」と
  2. キャッシュされたデータのMyISAMで表- 「ミラー」と
  3. MyISAMテーブル内のキーワードの表の助けを借りて
  4. InnoDB内のクエリと直接探索を解析
  5. サードパーティのソリューションを使用する

私たちは詳細にそれらの各を考えてみましょう。

MyISAMテーブルで表 - 「ミラー」と


最初に提案する方法は、MyISAMで追加のテーブルを作成することです。 ご存知のように、MyISAMは全文検索を非常によくサポートしており、これを使用できます。 メインテーブル（ users ）のすべてのデータがこの追加テーブルにコピーされます。 同期は、トリガーによって確保されています。 新しいテーブルで、フィールドlogin 、 name 、 surname 、 city 、 countryを追加します。 したがって、メインテーブルの「ミラー」を作成し、それを操作します。 全文検索を有効にするには、5つのフィールドすべてに沿ってFULLTEXTインデックスを追加します。

CREATE TABLE検索（
id INT （ 11 ） DEFAULT NULL 、
ログインVARCHAR （ 255 ） DEFAULT NULL 、
name VARCHAR （ 255 ） DEFAULT NULL 、
姓VARCHAR（255） のDEFAULT NULL、
国VARCHAR （ 255 ） DEFAULT NULL 、
city VARCHAR （ 255 ） DEFAULT NULL 、
全文索引 IX_search （都市、国、ログイン、名前、姓）
）
ENGINE = MYISAM


メインテーブルとミラーテーブルの間でデータを同期するために、 ユーザーの書き込み、変更、読み取りのトリガーを設定します 。

記録上トリガー：

CREATE
トリガー 「挿入」
INSERT AFTER
ONユーザー
各行ごと
BEGIN
INSERT INTO検索（ `id` 、 ` login` 、 `name` 、 ` surname` 、 `country` 、 ` city` ） VALUES （
新品 id
新品 `ログイン` 、
新品 「名前」 、
新品 `surname`、
新品 「国」 、
新品 `city`
） ;
終了


変更をトリガーします：

作成
トリガー `更新`
UPDATE AFTER
ONユーザー
各行ごと
開始
`search` WHERE` id` = NEWから削除し ます。 `id` ;
INSERT INTO `search` （ ` id` 、 `login` 、 ` name` 、 `surname` 、 ` country` 、 `city` ） 値 （
新品 id
新品 `ログイン` 、
新品 「名前」 、
新品 `surname`、
新品 「国」 、
新品 「市」
） ;
終了


そして、単純な削除トリガー：

作成
トリガー `削除`
削除後
ONユーザー
各行ごと
開始
`search` WHERE` id` = FROM DELETE OLD。 `id` ;
終了


検索は、次のクエリを使用して実行されます。

`users`を 選択し ます。* FROM` users`
INNERは `search`を 登録しよう
`search`で 。 `id` = ` users` 。 `id`
どこ
MATCH （ `search`。City 、 ` search`。Country 、 `search`。Login 、 ` search`。Name 、 `search`。Surname ） AGAINST （ 'Vladimir Tupin Saint Petersburg' IN BOOLEAN MODE ） > 0
ORDER BY MATCH （ `search`。City 、 ` search`。Country 、 `search`。Login 、 ` search`。Name 、 `search`。Surname ） AGAINST （ 'Vladimir Tupin Saint Petersburg' IN BOOLEAN MODE ） DESC

ここでは、データは検索テーブルで検索され、結果は関連性によってソートされ、出力ではユーザーテーブルから対応するエントリを取得します。

このアプローチの主な利点は、インデックスを追加し、新しい検索の組み合わせ（国+都市またはログイン+名前+姓）をコンパイルすることによる検索の柔軟性です。 したがって、検索ルールと関連ルールの新しいセットを自由に作成できます。
この方法の欠点（および「ミラー」を作成するすべての方法）は、データの過剰な保存です。 したがって、この例のように、少量のデータで使用することをお勧めします。

キャッシュされたデータのMyISAMで表 - 「ミラー」と


2番目の方法もデータミラーを作成することですが、ここでは1つのフィールドにのみデータを保存します。 このタスクでは、フィールドのグループによって検索がすぐに実行され、それらをスペースで区切られた1つのテキストフィールドに結合しようとします。 したがって、テーブル内のデータのセット全体は、一つのフィールドのユーザに対応することになります。 idとtextの 2つのフィールドを持つ検索テーブルを作成します 。 Id-メインテーブル（ users ）のidに対応します。 テキストは「キャッシュされた」データです。

CREATE TABLE検索（
id INT （ 11 ） DEFAULT NULL 、
`text` TEXT DEFAULT NULL 、
全文索引 IX_search_text （ `text` ）
）
ENGINE = MYISAM


同期もトリガーを使用して実行されます。

補遺：

作成
トリガー 「挿入」
挿入後
ONユーザー
各行ごと
BEGIN
INSERT INTO search （ `id` 、 ` text` ） VALUES （ NEW 。 `Id` 、
（LOWER
CONCAT_WS（ ''、
新品 「名前」 、
新品 `surname`、
新品 `ログイン` 、
新品 「国」 、
新品 `city`
）
）
） ;
終了


変更：

作成
トリガー `更新`
更新後
ONユーザー
各行ごと
BEGIN
DELETE FROM検索WHERE `id` = NEW 。 `id` ;
INSERT INTO search （ `id` 、 ` text` ） VALUES （ NEW 。 `Id` 、
（LOWER
CONCAT_WS（ ''、
新品 「名前」 、
新品 `surname`、
新品 `ログイン` 、
新品 「国」 、
新品 「市」
）
）
） ;
作成を終了


取り外し：

トリガー `削除`
削除後
ONユーザー
各行ごと
開始
DELETE FROM検索WHERE `id` = OLD 。 `id` ;
終了


次のように検索クエリは次のようになります。

`users`を 選択し ます。* FROM` users`
内部 結合 「検索」
`search`で 。 `id` = ` users` 。 `id`
どこ
MATCH （ `search`.` Text` ） AGAINST （ 'Vladimir Tupin Saint Petersburg' IN BOOLEAN MODE ） > 0
ORDER BY MATCH （ `search`.` Text` ） AGAINST （ 'Vladimir Tupin Saint Petersburg' IN BOOLEAN MODE ） DESC


この方法は、前の方法ほど柔軟ではありませんが、後で見るように、多数のさまざまな要求で速度が優先されます。

MyISAMでキーワードテーブルを使用する



3番目の方法は、「キーワード」のリスト（検索タグ）の作成に基づいています。 キーワード- ユーザテーブルのフィールド。 たとえば、フィールドを持つユーザーのための(id=2144; login= leo; name=;surname=;city=' ';country=;email=leo@tolstoy.ru;password=;)キーワードがします(«leo»; «»; «»; « »; «») 。 これらの言葉のすべては、二つのフィールドIDとテキストになり、別のテーブルのMyISAM、に書き込まれます。 ID番号は、マスターテーブル（ユーザ）と一致します。 テキストは、キーワードタグが書き込まれるフィールドです。 usersテーブルの各ユーザーには、新しい検索テーブルに5つのエントリがあります。 だから我々は、各ユーザータグテーブルを得ました。

CREATE TABLE検索（
id INT （ 11 ） DEFAULT NULL 、
`text` VARCHAR （ 255 ） DEFAULT NULL 、
FULLTEXT INDEX IX_search_text（ `text`）
）
エンジン= MYISAM


データの同期は、トリガを犠牲にして行われます。

作成：

CREATE
トリガー 「挿入」
挿入後
ユーザーON
各行ごと
BEGIN
INSERT INTO search （ `id` 、 ` text` ） 値
（ NEW 。 `Id` 、 NEW.` Login` ） 、
（ NEW 。 `Id` 、 NEW.` Name` ） 、
（ NEW 。 `Id` 、 NEW.`姓 ` ） 、
（ NEW 。 `Id` 、 NEW.` Country` ） 、
（ NEW 。 `Id` 、 NEW.` City` ） ;
END


変更します。

CREATE
トリガー `更新`
更新後
ユーザーON
各行ごと
BEGIN
検索`id` = NEW FROM DELETE 。 `id`;
INSERT INTO search （ `id` 、 ` text` ） 値
（ NEW 。 `Id` 、 NEW.` Login` ） 、
（ NEW 。 `Id` 、 NEW.` Name` ） 、
（ NEW 。 `Id` 、 NEW.`姓 ` ） 、
（ NEW 。 `Id` 、 NEW.` Country` ） 、
（ NEW 。 `Id` 、 NEW.` City` ） ;
終了


除去：

CREATE
トリガー `削除`
削除後
ONユーザー
各行ごと
BEGIN
DELETE FROM検索WHERE `id` = OLD 。 `id`;
終了


検索：

`users`を 選択し ます。* FROM` users`
INNERは `search`を 登録しよう
`search`で 。 `id` = ` users` 。 id
どこ
MATCH （ `search`.` Text` ） AGAINST （ 'ウラジミール トゥ パン サンクトペテルブルク' ブーリアンモード） > 0
GROUP BY `search` 。 id
COUNT（*）DESC BY ORDER


以前の関連性がMyISAMビルトイン検索エンジンによって決定された場合、この場合は自分で決定することに注意してください。 検索の結果、リクエストに一致するタグのみを受け取りました。 また、ユーザーが持つタグが多いほど、サンプル内のタグは高くなります。
指定された例には欠点があります。タグの数が等しい場合、いくつかのレコードには自然なソートがありますが、これは関連性の観点からは必ずしも当てはまりません。
ただし、この方法にはさらなる開発の可能性が高い。 最初に、 MATCH AGAINSTからの関連性評価の合計をORDER BY照合に追加できます。 したがって、上記の欠点が解消されます。 次に、このテーブルに重量タグの追加の重量フィールドを追加し、メインテーブルの各フィールドにこの重量の値を割り当てることができます。 したがって、我々は、各フィールドの重要性（重量）に基づいて並べ替えを追加することができます。 これにより、検索の品質を損なうことなく、いくつかの分野に集中する機会が得られます。

InnoDB内のクエリと直接探索を解析


4番目の方法は過酷で、以前の方法としてMyISAMを使用しません。 また、追加のテーブルおよびトリガーはありません。 私たちは、単に既存のテーブルを探します。 最初に、検索が実行されるすべてのフィールドにインデックスを付ける必要があります。

CREATE TABLEユーザー（
IDのINT（11）NOT NULLと、
ログインVARCHAR （ 255 ） DEFAULT NULL 、
`password` VARCHAR （ 255 ） DEFAULT NULL 、
name VARCHAR （ 255 ） DEFAULT NULL 、
surname VARCHAR （ 255 ） DEFAULT NULL 、
メールVARCHAR（255）NOT NULLと、
国VARCHAR （ 255 ） DEFAULT NULL 、
city VARCHAR （ 255 ） DEFAULT NULL 、
PRIMARY KEY（ID）、
INDEX city （ city ） 、
INDEX国（国） 、
INDEXメール（ email ） 、
INDEX login （ login ） 、
INDEX name （ name ） 、
INDEXパスワード（ password ） 、
INDEX姓（姓）
）
エンジン= INNODB


InnoDB内で検索のみLIKE演算子を用いて行うことができるが、その効率的な動作のためにワード、または複数の単語からなるリクエストにクエリを分割する必要があり、結果ずに残ります。 単語に分割され、要求の関数を記述します
作成
関数検索（ str VARCHAR （ 255 ） ）
varchar （ 255 ） CHARSET cp1251を返します
BEGIN
DECLARE output VARCHAR （ 255 ） DEFAULT '' ;
DECLARE temp_str VARCHAR （ 255 ） ;
DECLARE first_part VARCHAR （ 255 ） DEFAULT "CONCAT_WS（ ''、` name`、 `surname`、` login`、 `country`、` city`）LIKE '％ " ;
DECLARE last_part VARCHAR（255）DEFAULT "％「";

長さ（ str ）ながら ！ = 0 DO
SETのtemp_str = SUBSTRING_INDEX（STR、 ' '、 1）;
IF temp_str = str
THEN
SET str = '' ;
その他
SET STR = SUBSTRING（STR、LENGTH （temp_str）+ 2）。
END IF;

出力IF！ = ''
その後
SET出力= CONCAT （出力、 「OR」 ） ;
END IF;

SET出力= CONCAT（出力、first_part 、temp_str、last_part）。

終了中;
RETURN出力。
終了


機能は私たちに、単純に置き換えると、実行する必要がある検索クエリを形成フラグメントを、返します。

SET @ WHERE = CONCAT （ 'SELECT * FROM `users` WHERE' 、 search （ 'Habra Khabrovich' ） ） ;
WHERE @ から調製PREPARE。
EXECUTE準備済み;


一時テーブルを使用することもできます。これらはクエリ結果を処理する際に具体的な利便性を提供します。

サードパーティのソリューションを使用します


サードパーティの全文検索ソリューションがいくつかあります。 最も人気のあるプラットフォームは、 SphinxおよびApache Luceneベースのプロジェクトです。 これらの使用は、（私たちの例のように）少量のデータのために意味がない、時にはそれが原因な制約（ホスティング事業者、邪悪な管理、カーブの手のように。D.）に単純に不可能です。

比較


典型的なクエリの実行速度に（サードパーティソリューションを除く）どのようにフルテキスト検索を参照してください比較。 例として、複雑さの異なる50個のクエリを使用して比較します。 これを行うには、上記の各方法で検索の平均速度を客観的に計算するPHPスクリプトを作成します。 測定値を実際の条件に近づけるために、同じ検索クエリが使用される2回目の制御測定を実行します。 ここで、MySQLキャッシングメカニズムが各メソッドでどの程度使用されているかを評価できます。

InnoDBテーブルのMySQLデータベースでのさまざまな方法による検索クエリの速度の比較：



多く：

テクニック単一の要求（複数可）の平均速度。1回の繰り返しリクエストの平均実行速度（秒）
MyISAMでミラーテーブルを使用する0.0297380.011974
キャッシュされたデータでMyISAMのミラーテーブルを使用する0.0256520.012027
MyISAMでキーワードテーブルを使用する0.0278760.008866
InnoDBでのクエリ解析と直接検索0.1360910.09541


予想されたように、InnoDBでの直接のLIKE検索は最も遅く、他のすべての人にとって大幅に失われることが判明しました。 もちろん、この方法は引き続き最適化できますが、速度が大幅に向上することはほとんどありません。
残りの検索方法のうちの3つは、同じレベルであることが判明しました。 実践が示しているように、多数の同一のリクエストがある場合、MyISAMでキーワード（タグ）を使用することは明確な利点をもたらします。 キャッシュされたミラーの作成 - さまざまな検索クエリ数の多い第二の方法を提供します勝ちます。 一部のフィールドのサイズが他のフィールド（記事コンテンツ、ニューステキスト）と非常に異なる場合、最初の方法の方が効果的です-ミラーテーブルを作成します。

MyISAMミラーの作成は、テーブルにさらにレコードがあり、サードパーティのメカニズム（Sphinx、Apache Lucene）を使用できる技術的な機能がある場合、小さなテーブル（テーブルに1万から5万レコード）に使用する必要があります。

Source: https://habr.com/ru/post/J114572/

More articles:


All Articles