MySQLのMECABによる最近つぶやかれている単語の解析
MySQLのMecabプラグインを利用して集計したデータを解析する過程で、INNODB_FT_INDEX_TABLEにて含まれている単語を確認し集計しようとしたが、
サーバーのスペックが低く集計中にハングしてしまったので、メモリーのINNODB_FT_INDEX_TABLEの内容をInnoDBテーブルに落として集計してみた。
もし、同様に形態素解析で解析されたワードを集計したいけれども、メモリー量が不足していて単語を集計出来ない場合の回避策として。参考までに。
root@localhost [(none)]> desc INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; +--------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+-------+ | WORD | varchar(337) | NO | | | | | FIRST_DOC_ID | bigint(21) unsigned | NO | | | | | LAST_DOC_ID | bigint(21) unsigned | NO | | | | | DOC_COUNT | bigint(21) unsigned | NO | | | | | DOC_ID | bigint(21) unsigned | NO | | | | | POSITION | bigint(21) unsigned | NO | | | | +--------------+---------------------+------+-----+---------+-------+ 6 rows in set (0.03 sec) root@localhost [confirm]> show create table INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE\G *************************** 1. row *************************** Table: INNODB_FT_INDEX_TABLE Create Table: CREATE TEMPORARY TABLE `INNODB_FT_INDEX_TABLE` ( `WORD` varchar(337) NOT NULL DEFAULT '', `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0', `DOC_ID` bigint unsigned NOT NULL DEFAULT '0', `POSITION` bigint unsigned NOT NULL DEFAULT '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 1 row in set (0.06 sec)
メモリーのINNODB_FT_INDEX_TABLEからInnoDBのテーブルにデータをコピーする
root@localhost [(confirm)]> SET GLOBAL innodb_ft_aux_table='APP/Tweets'; Query OK, 0 rows affected (0.00 sec) root@localhost [(confirm)]> SET GLOBAL innodb_optimize_fulltext_only=ON;optimize table Tweets; Query OK, 0 rows affected (0.00 sec) root@localhost [confirm]> CREATE TEMPORARY TABLE `FTS_INDEX_TABLE` ( -> `WORD` varchar(337) NOT NULL DEFAULT '', -> `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0', -> `DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `POSITION` bigint unsigned NOT NULL DEFAULT '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.01 sec) root@localhost [confirm]> insert into FTS_INDEX_TABLE select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; Query OK, 11642045 rows affected (12 min 51.51 sec) Records: 11642045 Duplicates: 0 Warnings: 0
オーバーヘッド
MEMORYからデータのコピー中はサーバースペックも低いのでメモリー+SWAPが最大限に利用されている。
時間も上記のように12分もかかってしまいました。
メモ:再集計する時に、TRUNCATEするのでTEMPORARYのままでもいいけど、念のためInnoDB間のみでデータコピー速度を確認してみた。
こちらは、1分半程度で終わっています。
root@localhost [confirm]> CREATE TABLE `FTS_WORD_TABLE` ( -> `WORD` varchar(337) NOT NULL DEFAULT '', -> `FIRST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `LAST_DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `DOC_COUNT` bigint unsigned NOT NULL DEFAULT '0', -> `DOC_ID` bigint unsigned NOT NULL DEFAULT '0', -> `POSITION` bigint unsigned NOT NULL DEFAULT '0' -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.01 sec) root@localhost [confirm]> insert into FTS_WORD_TABLE select * from FTS_INDEX_TABLE; Query OK, 11642045 rows affected (1 min 29.70 sec) Records: 11642045 Duplicates: 0 Warnings: 0
TWEETワードランキング
以下、直近のTweet集計結果です。コロナの影響もあり、殆どがコロナ関連の単語が上位にきている事が分かります。
root@localhost [confirm]> select rank() OVER(ORDER BY count(*) desc) ranking,WORD,count(*) -> from FTS_WORD_TABLE where length(WORD) > 4 group by WORD having count(*) >= 10000 order by ranking; +---------+-----------------+----------+ | ranking | WORD | count(*) | +---------+-----------------+----------+ | 1 | ウイルス | 282891 | | 2 | コロナ | 232527 | | 3 | 新型 | 173684 | | 4 | 感染 | 157965 | | 5 | https | 137958 | | 6 | ます | 101664 | | 7 | ない | 97355 | | 8 | から | 81599 | | 9 | する | 70057 | | 10 | こと | 66332 | | 11 | まし | 65643 | | 12 | いる | 64802 | | 13 | です | 62606 | | 14 | 拡大 | 43248 | | 15 | バス | 40119 | | 16 | 日本 | 31022 | | 17 | ある | 29622 | | 18 | 対策 | 29344 | | 19 | ため | 28480 | | 20 | この | 25914 | | 21 | 中国 | 24729 | | 22 | てる | 24267 | | 23 | など | 23112 | | 24 | まで | 22573 | | 25 | という | 22429 | | 26 | よう | 22123 | | 27 | 検査 | 21856 | | 28 | なっ | 20436 | | 29 | 医療 | 20188 | | 30 | 東京 | 19908 | | 31 | これ | 18426 | | 32 | 影響 | 18350 | | 33 | 確認 | 18141 | | 34 | ので | 17266 | | 35 | あり | 17175 | | 36 | さん | 16897 | | 37 | について | 16705 | | 38 | ませ | 16701 | | 39 | たら | 16575 | | 40 | マスク | 16337 | | 41 | なり | 16270 | | 42 | 緊急 | 16173 | | 43 | より | 15770 | | 44 | なく | 15662 | | 45 | 防止 | 15512 | | 46 | 受け | 15471 | | 47 | 世界 | 15462 | | 48 | って | 15407 | | 49 | けど | 15108 | | 50 | 発表 | 14998 | | 51 | として | 14969 | | 52 | 政府 | 14914 | | 53 | れる | 14863 | | 54 | yahoonewstopics | 14258 | | 55 | なる | 14207 | | 56 | 自粛 | 12808 | | 57 | その | 12730 | | 58 | 情報 | 12621 | | 59 | 事態 | 12430 | | 60 | だけ | 12010 | | 61 | 武漢 | 11872 | | 62 | お知らせ | 11751 | | 63 | そう | 11453 | | 64 | できる | 11284 | | 65 | による | 11218 | | 66 | でき | 11120 | | 67 | 開催 | 11085 | | 68 | たい | 10899 | | 69 | 宣言 | 10855 | | 70 | でも | 10801 | | 71 | ニュース | 10720 | | 72 | 状況 | 10663 | | 73 | 中止 | 10641 | | 74 | 新た | 10626 | | 75 | おり | 10507 | | 76 | 患者 | 10391 | | 77 | だっ | 10352 | | 78 | なら | 10304 | | 79 | 時間 | 10096 | | 80 | 対応 | 10093 | | 81 | 病院 | 10092 | +---------+-----------------+----------+ 81 rows in set (26.52 sec) root@localhost [confirm]>