UPDATEを高速にしたい

Question

###前提・実現したいこと
UPDATEが非常に遅いので速くしたいです。
accountsテーブルに約1千万件のデータが入っています。
そのうち約180万件に対してUPDATEをする必要があるのですが、約3時間たっても終わりません。CPU使用率も高い状態が続いています。（htopコマンドで確認しました）

###該当のソースコード
最終ログイン日時が2017/10/01以前のアカウントのみflagを1にします。
別のプログラムでflagの値を元に処理する必要があるからです。

※flagの値は全て0になっています。

UPDATE accounts a
SET a.flag = 1
WHERE a.last_login_time < '2017/10/01';

###テーブル情報

• accounts テーブル

全データ数約1千万件

key	column	data_type	length
Primary	user_id	int	11
Key	uuid	varchar	36
	name	varchar	50
	creation_time	timestamp
Key	last_login_time	timestamp
Key	flag	int	11

CREATE TABLE `accounts` (
	`user_id` INT(11) NOT NULL AUTO_INCREMENT,
	`uuid` VARCHAR(36) NOT NULL,
	`name` VARCHAR(50) NOT NULL,
	`creation_time` TIMESTAMP NOT NULL DEFAULT '',
	`last_login_time` TIMESTAMP NOT NULL DEFAULT '',
	`flag` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`user_id`),
	INDEX `uuid` (`uuid`),
	INDEX `flag` (`flag`),
	INDEX `last_login_time` (`last_login_time`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0;

###その他情報

• UPDATE対象の件数

SELECT count(*)
FROM accounts a
WHERE a.last_login_time < '2017/10/01';
/* 出力 1,810,588 */
/* Affected rows: 0  Found rows: 1  注意: 0  Duration for 1 クエリー: 13.719 sec. */

###MariaDBのコンフィグ情報

• /etc/my.cnf.d/server.cnf

[server]

[mysqld]
innodb_buffer_pool_size=24000000000
max_allowed_packet=256MB
character-set-server = utf8
max_connections = 10000
max_user_connections = 10000
max_connect_errors = 9999999
wait_timeout = 30
event_scheduler=ON
datadir=/data/mysql
socket=/data/mysql/mysql.sock

[galera]

[embedded]

[mariadb]

[mariadb-10.1]

###試したこと

• 不要なインデックスを削除する

accounts.nameに付いていたインデックスを削除しました。
速度に変化はありませんでした。

• Optimizeを実行する

以下を実行しましたが速度に変化はありませんでした。

OPTIMIZE TABLE db_name.accounts

/* note: Table does not support optimize, doing recreate + analyze instead */
/* status: OK */

###補足情報(言語/FW/ツール等のバージョンなど)

• VPS(KVM)をレンタルしています
• CentOS 7.3 64bit
• MariaDB 10.2.7

Answer 1

条件式に

a.flag != 1

を追加したら件数絞れますよね。

Comments

[aglkjggg]

flagは全て0になっています。
情報不足だった点申し訳ないです。

[退会済みユーザー]

もし、身近にそのような設計をしたとしたら、そもそも、そのフラグは本当に必要なのかと問いかけると思います。日付で状態を判定できるわけですからねぇ。

[aglkjggg]

失念しておりました。flagは不要でした。

論点が少しズレてきて申し訳ないですが、
以下のクエリを実行して約30分経過しましたが結果が帰ってきません。CPU使用率が高い状態のままです。
CPU 12コア メモリ32GBのそれなりに高性能なサーバですので遅くても数分で結果が帰ってきても良いと考えているのですがここまで遅い原因がわかりかねます。

この点について何かご存知ありませんでしょうか。
そもそも100万件のUPDATEは数時間は必ずかかる処理と考えたほうがいいのでしょうか？

UPDATE accounts a SET a.flag = 1 limit 1000000;

[退会済みユーザー]

そうですね。普通UPDATE自体はインデックスの再構築などコストの高いものですから、一度に大量に更新するような処理を避けるように設計すべきです。

[退会済みユーザー]

木になる点が、なぜこんなにmysqlのスレッドが上がっているのか？まさかループでUPDATE流してません？

[aglkjggg]

ありがとうございます。
スレッド数について深く探っていった所、自己解決しました。

flagを利用して処理をする別のアプリケーションの動作が原因でUPDATEが非常に遅くなっておりました。

別のアプリケーションは、1分ごとに自動的にflag=1があるかをチェックしてある動作をするのですが、そのチェックが千スレッド近い数で一度にDBに対してSELECTするような動きになっていました。

このSELECTと今回質問させて頂いたUPDATEのタイミングが重なるとDBとしては高負荷（もしくはテーブルロック系の問題？）となり数時間たってもUPDATEが終わらないという事になっていました。

別のアプリケーション側を完全に停止させた所、今回質問させて頂いたUPDATEのSQLは1分37秒で正常終了しました。

ご指摘のとおり、不要かつ負荷の高いUPDATEは取り除く設計に修正します。

再設計については今回の質問とは外れるので以上で区切りとさせて頂きます。

解決の糸口を見つけていただきありがとうございました。

Answer 2

以下のクエリを実行して約30分経過しましたが結果が帰ってきません。CPU使用率が高い状態のままです。
UPDATE accounts a SET a.flag = 1 limit 1000000;

・where で検索対象を絞りましょう
・インデックスを張りましょう

追記：
実際whereは含まれていませんし，「不要なインデックスを削除した」とは書かれていますがどの程度インデックスが張られているかは書かれていないですし，基本に立ち返るのは大切なので，現時点での回答として不適切とは思いませんが．
「評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。」とも書かれているはずです．