MariaDB (MySQL) で Mroonga を使った日本語の全文検索を行ってみよう.CentOS で通常の手順で MariaDB (あるいは MySQL)をインストールしたとき,日本語の全文検索は行なえません.ここの手順で Mroonga を設定すると日本語であっても全文検索を高速に行えるようになります.インストールと MySQL パスワードの設定までができた状態を前提にします.なお,SQL のコマンドは大文字で表示していますが,大半は小文字で入力しても動作します.
MySQL にログインするには mysql
コマンドを使います.まずは管理者 (root) でログインします.-p
オプションをつけることで,コマンド入力後にパスワードが尋ねられます.
[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎ Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 22 Server version: 10.4.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
インストールされた MariaDB のプラグインを一覧で表示し,Mroonga がインストールされていることを確認する.
MariaDB [(none)]> SHOW PLUGINS; ⏎
+-------------------------------+----------+--------------------+------------------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| wsrep | ACTIVE | REPLICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CLIENT_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INDEX_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| TABLE_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| USER_STATISTICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| SQL_SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_MUTEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_SEMAPHORE_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| INNODB_TABLESPACES_SCRUBBING | ACTIVE | INFORMATION SCHEMA | NULL | BSD |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| SEQUENCE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| unix_socket | ACTIVE | AUTHENTICATION | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| user_variables | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Mroonga | ACTIVE | STORAGE ENGINE | ha_mroonga_official.so | GPL |
+-------------------------------+----------+--------------------+------------------------+---------+
55 rows in set (0.001 sec)
データベースの一覧を表示する.
MariaDB [(none)]> SHOW DATABASES; ⏎ +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.007 sec)
新しいデータベースを作成する.
MariaDB [(none)]> CREATE DATABASE mroonga_test; ⏎ Query OK, 1 row affected (0.000 sec)
データベースの一覧を表示すると,今作成したデータベースが確認できる.
MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database |
+--------------------+
| information_schema |
| mroonga_test |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
利用するデータベースを指定する.
MariaDB [(none)]> USE mroonga_test; ⏎ Database changed
テーブルの一覧を表示する.まだテーブルはありません.
MariaDB [mroonga_test]> SHOW TABLES; ⏎ Empty set (0.000 sec)
テーブルを作成する.このとき,日本語全文検索を利用するためには Engine=Mroonga
を指定する.
MariaDB [mroonga_test]> CREATE TABLE comments (
id INT PRIMARY KEY AUTO_INCREMENT,
body VARCHAR(255),
FULLTEXT INDEX (body)
) Engine=Mroonga DEFAULT CHARSET utf8; ⏎
Query OK, 0 rows affected (0.101 sec)
テーブルの一覧を表示する.
MariaDB [mroonga_test]> SHOW TABLES; ⏎ +------------------------+ | Tables_in_mroonga_test | +------------------------+ | comments | +------------------------+ 1 row in set (0.000 sec)
テーブルの定義を確認する.
MariaDB [mroonga_test]> DESC comments; ⏎ +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | body | varchar(255) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+----------------+ 2 rows in set (0.001 sec)
テーブル定義の詳細情報を確認する.このとき body カラムにインデックスが設定されていることに注意する.
MariaDB [mroonga_test]> SHOW CREATE TABLE comments; ⏎
+----------+------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------+
| comments | CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`body` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `body` (`body`)
) ENGINE=Mroonga DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------+
1 row in set (0.001 sec)
データを投入する.
MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("暗号とデジタル署名"); ⏎ Query OK, 1 row affected (0.005 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("換字式暗号と転置式暗号"); ⏎ Query OK, 1 row affected (0.001 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("共通鍵暗号方式"); ⏎ Query OK, 1 row affected (0.000 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("公開鍵暗号方式"); ⏎ Query OK, 1 row affected (0.000 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("ハイブリッド暗号方式"); ⏎ Query OK, 1 row affected (0.000 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("ハッシュ関数"); ⏎ Query OK, 1 row affected (0.000 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("デジタル署名"); ⏎ Query OK, 1 row affected (0.000 sec) MariaDB [mroonga_test]> INSERT INTO comments (body) VALUES ("認証局"); ⏎ Query OK, 1 row affected (0.001 sec)
データをすべて表示する.
MariaDB [mroonga_test]> SELECT * FROM comments; ⏎ +----+-----------------------------------+ | id | body | +----+-----------------------------------+ | 1 | 暗号とデジタル署名 | | 2 | 換字式暗号と転置式暗号 | | 3 | 共通鍵暗号方式 | | 4 | 公開鍵暗号方式 | | 5 | ハイブリッド暗号方式 | | 6 | ハッシュ関数 | | 7 | デジタル署名 | | 8 | 認証局 | +----+-----------------------------------+ 8 rows in set (0.000 sec)
インデックスを使った検索を行ってみる.まずは「署名」というキーワードが含まれるレコードを抽出すると,うまく検索できている.
MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("署名"); ⏎ +----+-----------------------------+ | id | body | +----+-----------------------------+ | 1 | 暗号とデジタル署名 | | 7 | デジタル署名 | +----+-----------------------------+ 2 rows in set (0.001 sec)
別のキーワードでも検索してみる.キーワード「暗号」もうまく検索できました.
MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号"); ⏎ +----+-----------------------------------+ | id | body | +----+-----------------------------------+ | 2 | 換字式暗号と転置式暗号 | | 1 | 暗号とデジタル署名 | | 5 | ハイブリッド暗号方式 | | 4 | 公開鍵暗号方式 | | 3 | 共通鍵暗号方式 | +----+-----------------------------------+ 5 rows in set (0.014 sec)
さらに「暗号方式」で検索すると,「暗号」が含まれるが「方式」を含まないレコードも検索されてしまいました.
MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号方式"); ⏎ +----+-----------------------------------+ | id | body | +----+-----------------------------------+ | 2 | 換字式暗号と転置式暗号 | | 1 | 暗号とデジタル署名 | | 5 | ハイブリッド暗号方式 | | 4 | 公開鍵暗号方式 | | 3 | 共通鍵暗号方式 | +----+-----------------------------------+ 5 rows in set (0.001 sec)
Mroonga では「IN BOOLEAN MODE」というオプションをつけて検索すると,うまく検索できます.
MariaDB [mroonga_test]> SELECT * FROM comments WHERE MATCH(body) AGAINST("暗号方式" IN BOOLEAN MODE); ⏎ +----+--------------------------------+ | id | body | +----+--------------------------------+ | 3 | 共通鍵暗号方式 | | 5 | ハイブリッド暗号方式 | | 4 | 公開鍵暗号方式 | +----+--------------------------------+ 3 rows in set (0.001 sec)
なお,インデックスを使わずに LIKE
を使った検索も可能ですが,この方法は検索性能(速度)の観点からおすすめできません.
MariaDB [mroonga_test]> SELECT * FROM comments WHERE body LIKE "%暗号%"; ⏎ +----+-----------------------------------+ | id | body | +----+-----------------------------------+ | 1 | 暗号とデジタル署名 | | 2 | 換字式暗号と転置式暗号 | | 3 | 共通鍵暗号方式 | | 4 | 公開鍵暗号方式 | | 5 | ハイブリッド暗号方式 | +----+-----------------------------------+ 5 rows in set (0.000 sec)
データベースからログアウトします.
MariaDB [mroonga_test]> EXIT ⏎ Bye [vagrant@centos8 centos8ansible]$
管理者 (root) ではすべてのデータに対して閲覧・操作が可能になるので,一般的にはユーザを作成して,特定のデータベースに権限を与えることが行われる.ここでは,myappDB
というデータベースを作成し,このデータベースを操作できる myapp_user
というユーザを作成する例を示す.
まず,管理者 (root) でログインする.
[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎ Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 24 Server version: 10.4.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
データベースの一覧をまず確認する.
MariaDB [(none)]> SHOW DATABASE; ⏎ +--------------------+ | Database | +--------------------+ | information_schema | | mroonga_test | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec)
データベースを作成する.
MariaDB [(none)]> CREATE DATABASE myappDB; ⏎ Query OK, 1 row affected (0.000 sec)
データベースの一覧を確認すると,いま作成した myappDB
が見える.
MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database |
+--------------------+
| information_schema |
| mroonga_test |
| myappDB |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
mysql というデータベースにユーザ情報が登録されているので,ユーザの一覧を確認する.
MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎ +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ 3 rows in set (0.007 sec)
ユーザ myapp_user
を作成し,データベース myappDB
のすべてのテーブルにすべての権限を付与する.なお,パスワードは「hogehogehoge」にする(もちろん適宜変更してください).
MariaDB [(none)]> GRANT ALL ON myappDB.* to myapp_user@localhost identified by 'hogehogehoge'; ⏎ Query OK, 0 rows affected (0.001 sec)
ユーザが登録できたことを確認する.
MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User | Host |
+-------------+-----------+
| mariadb.sys | localhost |
| myapp_user | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)
一旦ログアウトする.
MariaDB [(none)]> EXIT ⏎ Bye [vagrant@centos8 centos8ansible]$
まず,mysql にログインする.このときのユーザ名は先程作成した myapp_user
にする.
[vagrant@centos8 centos8ansible]$ mysql -u myapp_user -p ⏎
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.4.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
データベースの一覧を確認するが,権限を持つデータベースしか表示されないことに注意する.
MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database |
+--------------------+
| information_schema |
| myappDB |
+--------------------+
2 rows in set (0.000 sec)
使用するデータベースを指定する.権限を持つデータベースに対しては様々な操作が可能です.
MariaDB [(none)]> USE myappDB; ⏎ Database changed
ログアウトする.
MariaDB [myappDB]> EXIT; ⏎ Bye [vagrant@centos8 centos8ansible]$
まず,管理者 (root) でログインして,ユーザの一覧を表示する.
[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 10.4.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎
+-------------+-----------+
| User | Host |
+-------------+-----------+
| mariadb.sys | localhost |
| myapp_user | localhost |
| mysql | localhost |
| root | localhost |
+-------------+-----------+
4 rows in set (0.001 sec)
ユーザ myapp_user
を削除する.
MariaDB [(none)]> DROP USER myapp_user@localhost; ⏎ Query OK, 0 rows affected (0.008 sec)
再びユーザの一覧を表示して,削除されたことを確認する.
MariaDB [(none)]> SELECT user, host FROM mysql.user; ⏎ +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ 3 rows in set (0.001 sec)
ログアウトする.
MariaDB [(none)]> EXIT ⏎ Bye [vagrant@centos8 centos8ansible]$
まずは,管理者 (root) でログインして,データベースの一覧を表示する.
[vagrant@centos8 centos8ansible]$ mysql -u root -p ⏎
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 32
Server version: 10.4.17-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES; ⏎
+--------------------+
| Database |
+--------------------+
| information_schema |
| mroonga_test |
| myappDB |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
データベースを指定して,テーブル一覧を確認する.
MariaDB [(none)]> USE mroonga_test; ⏎ Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mroonga_test]> SHOW TABLES; ⏎ +------------------------+ | Tables_in_mroonga_test | +------------------------+ | comments | +------------------------+ 1 row in set (0.000 sec)
テーブル comments
を削除する
MariaDB [mroonga_test]> DROP TABLE comments; ⏎ Query OK, 0 rows affected (0.004 sec)
テーブルが削除されたこと確認する.
MariaDB [mroonga_test]> SHOW TABLES; ⏎ Empty set (0.000 sec)
次に,データベース mroonga_test
を削除する.
MariaDB [mroonga_test]> DROP DATABASE mroonga_test; ⏎ Query OK, 0 rows affected (0.005 sec)
データベースの一覧を見ると,削除されたことが確認できる.
MariaDB [(none)]> SHOW DATABASES; ⏎ +--------------------+ | Database | +--------------------+ | information_schema | | myappDB | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec)
さらに データベース myappDB
も削除する.
MariaDB [(none)]> DROP DATABASE myappDB; ⏎ Query OK, 0 rows affected (0.000 sec)
データベースの一覧で結果を確認する.
MariaDB [(none)]> SHOW DATABASES; ⏎ +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec)
ログアウトする.
MariaDB [(none)]> EXIT ⏎ Bye [vagrant@centos8 centos8ansible]$