日々の覚書: 全文検索のテスト用にtweets.csvを食わせるSQL のMroonga用な部分をInnoDBに変えて(FTインデックスはまだ作らない)
mysql56> ALTER TABLE tweets ADD fts text NOT NULL, ADD FULLTEXT KEY (fts); Query OK, 0 rows affected, 1 warning (0.20 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql56> SHOW WARNINGS; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.03 sec)
分かち書き用のカラムを足して、そこにInnoDB FTインデックスを作る。
( ´-｀).oO（LOAD DATA INFILEするときに久々に /usr/my.cnfの罠 にはまった。面倒だからMySQLに色々切り詰めさせてるのよね。。
FTインデックスを足したら https://gist.github.com/yoku0825/f0708574294042cbf350 こんなので分かち書きしてやって、
mysql56> SELECT table_name, index_name, COUNT(*) AS pages, SUM(data_size) AS size FROM innodb_buffer_page GROUP BY 1, 2; -- 1回再起動してバッファプールを空にしておいた
+--------------------------------+-----------------------+-------+------+
| table_name | index_name | pages | size |
+--------------------------------+-----------------------+-------+------+
| NULL | NULL | 65521 | 0 |
| `mysql`.`innodb_index_stats` | PRIMARY | 1 | 545 |
| `mysql`.`innodb_table_stats` | PRIMARY | 1 | 56 |
| `mysql`.`slave_master_info` | PRIMARY | 1 | 0 |
| `mysql`.`slave_relay_log_info` | PRIMARY | 1 | 0 |
| `mysql`.`slave_worker_info` | PRIMARY | 1 | 0 |
| `SYS_COLUMNS` | CLUST_IND | 1 | 7341 |
| `SYS_DATAFILES` | SYS_DATAFILES_SPACE | 1 | 813 |
| `SYS_FIELDS` | CLUST_IND | 1 | 1586 |
| `SYS_FOREIGN` | FOR_IND | 1 | 0 |
| `SYS_FOREIGN` | REF_IND | 1 | 0 |
| `SYS_INDEXES` | CLUST_IND | 1 | 1756 |
| `SYS_TABLESPACES` | SYS_TABLESPACES_SPACE | 1 | 801 |
| `SYS_TABLES` | CLUST_IND | 1 | 1931 |
| `SYS_TABLES` | ID_IND | 1 | 986 |
+--------------------------------+-----------------------+-------+------+
15 rows in set (1.06 sec)
mysql56> SELECT tweet_id, text FROM tweets WHERE match(fts) against('肉') ORDER BY _rowid DESC LIMIT 3;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id | text |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 564643277329014784 | RT @groonga: Groonga 5.0.0リリース (2015-02-09) 年に一度の2月9日肉の日！Groongaはメジャーバージョンアップしました。シャーディングを実現する実験的なプラグインがはいったよ。http://t.co/XisU7ppRl0 |
| 560652761247645700 | RT @ktou: 今日は今年最初の肉の日だから最新PostgreSQLでGroongaを使えるようにするPGroongaをリリースしたよ！最初のリリースだよ！CentOS 7用にはパッケージがあるよ！ http://t.co/wOufGV1GS7 |
| 549583026510774272 | RT @groonga: 今日は今年最後の肉の日ですね！Mroonga 4.09をリリースしました！BOOLEAN MODEでGroongaのスク リプト構文を使える機能とか普通のMroongaユーザーにはあまり必要のない改良が主です。 http://t.co/qL6qxsO5LN |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql56> SELECT table_name, index_name, COUNT(*) AS pages, SUM(data_size) AS size FROM innodb_buffer_page GROUP BY 1, 2;
+------------------------------------------------------+-----------------------+-------+--------+
| table_name | index_name | pages | size |
+------------------------------------------------------+-----------------------+-------+--------+
| NULL | NULL | 65469 | 0 |
| `d1`.`FTS_0000000000000045_000000000000006e_INDEX_6` | FTS_INDEX_TABLE_IND | 2 | 16322 |
| `d1`.`FTS_0000000000000045_CONFIG` | IND | 1 | 234 |
| `d1`.`FTS_0000000000000045_DELETED_CACHE` | IND | 1 | 0 |
| `d1`.`FTS_0000000000000045_DELETED` | IND | 1 | 0 |
| `d1`.`tweets` | FTS_DOC_ID_INDEX | 19 | 271784 |
| `d1`.`tweets` | PRIMARY | 28 | 412807 |
| `mysql`.`innodb_index_stats` | PRIMARY | 1 | 545 |
| `mysql`.`innodb_table_stats` | PRIMARY | 1 | 56 |
| `mysql`.`slave_master_info` | PRIMARY | 1 | 0 |
| `mysql`.`slave_relay_log_info` | PRIMARY | 1 | 0 |
| `mysql`.`slave_worker_info` | PRIMARY | 1 | 0 |
| `SYS_COLUMNS` | CLUST_IND | 1 | 7341 |
| `SYS_DATAFILES` | SYS_DATAFILES_SPACE | 1 | 813 |
| `SYS_FIELDS` | CLUST_IND | 1 | 1586 |
| `SYS_FOREIGN` | FOR_IND | 1 | 0 |
| `SYS_FOREIGN` | REF_IND | 1 | 0 |
| `SYS_INDEXES` | CLUST_IND | 1 | 1756 |
| `SYS_TABLESPACES` | SYS_TABLESPACES_SPACE | 1 | 801 |
| `SYS_TABLES` | CLUST_IND | 1 | 1931 |
| `SYS_TABLES` | ID_IND | 1 | 986 |
+------------------------------------------------------+-----------------------+-------+--------+
21 rows in set (1.05 sec)
とまあフツーに検索もできるし、インデックスも使われてる。
さてここから、information_schemaのInnoDB FT関連の何かを探すと、
mysql56> SHOW TABLES LIKE 'INNODB\_FT\_%'; +----------------------------------------------+ | Tables_in_information_schema (INNODB\_FT\_%) | +----------------------------------------------+ | INNODB_FT_DELETED | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_INDEX_TABLE | | INNODB_FT_BEING_DELETED | | INNODB_FT_INDEX_CACHE | | INNODB_FT_CONFIG | +----------------------------------------------+ 6 rows in set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_DELETED; Empty set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_INDEX_TABLE; Empty set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_BEING_DELETED; Empty set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_INDEX_CACHE; Empty set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_CONFIG; Empty set (0.00 sec)
:(；ﾞﾟ'ωﾟ'): あっれえINNODB_DEFAULT_STOPWORD以外何も出力してくれない。
と思ったら、情報を出力させたいテーブルを innodb_ft_aux_table に指定するらしい。my.cnfに書いてもダメで、必ずSET GLOBALで設定しなきゃダメよとか書いてある。
mysql56> SET GLOBAL innodb_ft_aux_table= 'd1/tweets'; Query OK, 0 rows affected (0.01 sec) mysql56> SELECT * FROM INNODB_FT_INDEX_TABLE; +------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------+--------------+-------------+-----------+--------+----------+ | 00 | 572 | 23196 | 77 | 572 | 224 | | 00 | 572 | 23196 | 77 | 578 | 111 | | 00 | 572 | 23196 | 77 | 666 | 151 | .. mysql56> SELECT word, count(*) FROM INNODB_FT_INDEX_TABLE GROUP BY 1 ORDER BY 2 DESC LIMIT 10; +------+----------+ | word | count(*) | +------+----------+ | の | 16573 | | て | 11059 | | に | 11036 | | が | 10630 | | た | 9673 | | は | 8460 | | _ | 7707 | | で | 7624 | | を | 6804 | | と | 6432 | +------+----------+ 10 rows in set (5.34 sec)
どうも INNODB_FT_INDEX_TABLE では転置索引の中身を確認できるっぽい。MyISAMでいうところの myisam_ftdump といったところか。この機能好きなので嬉しい。Mroongaさんにはコレないのよねぇ。。(Rroongaのgroonga-index-dumpの出力をパースしてがんばってたなぁ。。 日々の覚書: groonga-index-dumpで遊んでみる )
mysql56> SELECT * FROM INNODB_FT_CONFIG; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 23475 | | stopword_table_name | | | use_stopword | 1 | +---------------------------+-------+ 4 rows in set (0.00 sec) mysql56> SELECT * FROM INNODB_FT_INDEX_CACHE; Empty set (0.00 sec)
INNODB_FT_CONFIG が表示されるようになったのはいいし、INNODB_FT_*DELETEDが空っぽなのもいいんだけど、INNODB_FT_INDEX_CACHE はそのままでは何も出力してくれない。OPTIMIZE TABLEが必要だって書いてある。OPTIMIZE TABLEしたらINNODB_FT_INDEX_TABLEと同じような形式で表示された。
ANALYZE TABLEじゃなくてOPTIMIZE TABLEってどういうことだろう。ANALYZE TABLEだとダメだった。
バッファプールにはもちろん載ってるんだけど、バッファプールと別のパラメーター(innodb_ft_cache_size)で制御するから別物のことを言ってるんだよなぁ。。"Index inserts and updates are only committed to disk when the innodb_ft_cache_size size limit is reached."って書いてあるから、チェンジバッファ的な振る舞いをするのかしらん。
5.7で日本語全文検索が来てもいいように、練習中なのでした。
0 件のコメント :
コメントを投稿