Memo

メモ > 技術 > データベース: MySQL > チューニング（インデックス）
チューニング（インデックス）

インデックスによる高速化を実際に行った時の計測メモ。

■テーブル

以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていく。

ツイートテーブルとユーザテーブルを連結して一覧表示。
キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい。
CREATE TABLE `tweets` (
    `id`                varchar(80) NOT NULL     COMMENT 'ID',
    `created`           datetime NOT NULL        COMMENT '登録日時',
    `user_id`           varchar(80) NOT NULL     COMMENT 'ユーザID',
    `text`              text NOT NULL            COMMENT 'ツイート内容',
    `retweeted_id`      varchar(80) DEFAULT NULL COMMENT 'リツイートID',
    `retweeted_user_id` varchar(80) DEFAULT NULL COMMENT 'リツイートユーザID',
    PRIMARY KEY (`id`),
    KEY `id` (`id`),
    KEY `created` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ツイート';

CREATE TABLE `users` (
    `id`              varchar(80) NOT NULL      COMMENT 'ID',
    `created`         datetime NOT NULL         COMMENT '登録日時',
    `name`            varchar(255) NOT NULL     COMMENT '名前',
    `screen_name`     varchar(80) NOT NULL      COMMENT 'スクリーンネーム',
    `location`        varchar(80) DEFAULT NULL  COMMENT '位置情報',
    `description`     text                      COMMENT '説明',
    `url`             varchar(255) DEFAULT NULL COMMENT 'URL',
    `image_url`       varchar(255) NOT NULL     COMMENT '画像URL',
    `followers_count` int(11) NOT NULL          COMMENT 'フォロワー数',
    `friends_count`   int(11) NOT NULL          COMMENT 'フォロー数',
    `listed_count`    int(11) NOT NULL          COMMENT 'リスト数',
    `statuses_count`  int(11) NOT NULL          COMMENT 'リツイート数',
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザ';
■環境

CentOS 7 + nginx 1.12.2 + MySQL 5.7.21
https://refirio.net/twitter/■検証内容

30万件くらいまでは、何も考えずに作れる。
30万件くらいになると、「データ比較」「データ推移」が少し重い。最初の一覧やキーワード検索もほんの少し重いがまだ問題ない。
50万件くらいでも十分に早い。Apache→nginxで、PHPもMySQLも新しくしているからか。
60万件くらいになると、CPU使用率でuserの使用が確認できるようになってきた。ただし0.6%程度なので大したものではない。
80万件くらいになると、最初の一覧でもはっきり遅いと感じる。「データ比較」はかなり重い。

120万件。
トップページの表示が3秒ほどかかる。
データ取得時に記事数をキャッシュするようにし、それをもとにページ数などを求めるように変更。
トップページが2秒ほどで表示されるようになった。
levis標準機能のエクスポートではエラーになるが、execコマンドでmysqldumpを実行すればダンプできる。

200万件。
トップページの表示が3〜4秒ほどかかる。
キーワード検索すると8〜10秒ほどかかる。
データ比較は20秒ほどかかる。
データ推移は3秒ほどかかる。
CPU使用率は2〜3%くらい。
実用的ではない程度には重い。

インデックスを張る。（3つ目だけで良かったかもしれないが要検証。）
ALTER TABLE users ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (id);
ALTER TABLE tweets ADD INDEX (created);


トップページの表示が一瞬になった。
キーワード検索も一瞬になった。
データ比較とデータ推移は1〜2秒で表示されるようになった。
CPU使用率も下がった。（2〜3%あった使用率が0.7%くらいになった。）
SWAPの発生も20〜40KB/sあったものが0.01KB/sくらいになった。
全体的に明確な改善が見られる。

300万件。
トップページの表示は一瞬。
キーワード検索も一瞬。
データ比較とデータ推移は1〜2秒で表示される。
ユーザごとのページは3秒くらいかかる。

400万件。
（300万件のときと大差なし。）

600万件。
（300万件のときと大差なし。）

1200万件。
（300万件のときと大差なし。）

1900万件。
トップページの表示は一瞬。
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される。
ユーザごとのページは5秒くらいかかる。

2700万件。
トップページの表示は一瞬。
キーワード検索とデータ比較とデータ推移は1〜2秒で表示される。
ユーザごとのページも1〜2秒で表示される。
何故か早くなっている。さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明。
Memo

Advertisement
