![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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]>