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]> 

参考: Windows関数 on MySQL

Comments are closed.

Post Navigation