法人番号公表サイトについてのメモ
EDINETから財務情報を取得するメモ
EDINETってなに
EDINETはAPIを公開しているので画面をポチポチしなくても情報を取ってくることができる
流れ
この辺は、EDINETの操作ガイドにある仕様書に記載があるので詳細は譲る
書類一覧API
- 単一日付を指定してその日に提出された書類一覧を取得する
- 基本的には提出書類一覧及びメタデータを取得するので
type=2になるはず
用途にもよるがある程度の企業情報を必要とする場合は日付分APIを呼び出す必要がある あまり負荷がかからないように気をつけよう こちらを参考にしつつ、必要な期間の日付を生成 【Python】指定した期間の連続した日付をだす - Qiita
import os
import requests
import datetime
from datetime import timedelta
import json
import time
def daterange(_start, _end):
for n in range((_end - _start).days):
yield _start + timedelta(n)
base_url = 'https://disclosure.edinet-fsa.go.jp/api/v1/documents.json' # v1は変わる可能性があるらしい
base_filename = 'documents_'
def download(self, date, type=2):
param_date = str(date)
params = {
"date": param_date,
"type": type
}
filename = self.base_filename + param_date + ".json"
path = "./documents/" + filename # パスは適当
if not os.path.exists(path): # 再取得を避ける
url = self.base_url
response = requests.get(url, params=params)
json = response.content
with open(path, 'wb') as file:
file.write(json)
time.sleep(2) # 適当な時間待たせる
return path
for target_date in daterange(start_date, end_date):
print(target_date)
path = download(target_date)
取得したJSONに法人番号も含まれているので紐付けておくと吉 書類管理番号(docID)があるので必要な書類のIDを控えておく
書類取得API
あくまで一例(個人的には、書類管理番号(docID)の配列を作って↓を回すのが効率的な気がする)
import os
import requests
import time
import glob
import zipfile
import shutil
doc_id = xxxxx # 書類一覧APIから取得した書類管理番号(docID)
url = "https://disclosure.edinet-fsa.go.jp/api/v1/documents/" + doc_id # v1は変わる可能性があるらしい
params = {"type": 1}
filename = "./edinet/" + doc_id + ".zip" # パスは適当
if not os.path.exists(filename): # 再取得を避ける
res = requests.get(url, params=params ,stream=True)
if res.status_code == 200:
with open(filename, 'wb') as file:
for chunk in res.iter_content(chunk_size=1024):
file.write(chunk)
time.sleep(3) # 適当な時間待たせる
# ダウンロードするのがzipファイルなので解凍までしておく
zip_filepath = "./edinet/" + doc_id
zip_file = zipfile.ZipFile(filename)
zip_file.extractall(zip_filepath)
# 必要なものがわかっている場合はそれだけ残してあとは消す
files = glob.glob(zip_filepath + "/XBRL/PublicDoc/*.xbrl")
for file in files:
shutil.move(file, "./edinet/xbrl/" + doc_id + ".xbrl")
print(file)
dir = "/".join(file.split("/")[:3])
if os.path.exists(dir):
shutil.rmtree(dir)
あとは、XRBLの仕様書とにらめっこしながら必要な情報を抽出する 目検のときはPDF版と照らし合わせると確認しやすい
XRBLのパースの一例
import xmltodict
with open(file) as f:
doc = xmltodict.parse(f.read())
company_name = doc["xbrli:xbrl"]["jpcrp_cor:CompanyNameCoverPage"]["#text"]
ざっくり始めるクエリパフォーマンスチューニング
お断り
クエリの実行順序
- ちゃんとしたソースが見つからなかった
- 基本的には以下の通り
- FROM, including JOINs
- WHERE
- GROUP BY
- HAVING
- WINDOW functions
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT and OFFSET
- ただしMySQLは
selectのあとにhaving,group byが来ることもあるらしい select句で別名を付けてもwhere句やgroup by句では使えず、order by句では使えるのは評価順の問題
大きい括りでの基本戦略
- あくまで個人的な意見
- DBサーバにはDBサーバにしかできないことをやらせる戦略
- アプリサーバでできるなら任せる
- 例えば、データのソート、絞り込み
- 転送量との相談にはなるがざっくり投げてもネットワークに負荷がかからない、アプリサーバのメモリに乗り切る場合はアプリサーバにぶん投げてアプリサーバ上でやる
- DBサーバのリソースは貴重です
- DBのキャッシュを有効にしているとキャッシュヒット率が改善されるかもしれません
- MySQL8.0からキャッシュが無効になったので過度なキャッシュ利用は避けましょう
- キャッシュは麻薬
- DBのキャッシュ機能をオフっていてもOSキャッシュが効く可能性がある
- DBサーバのリソースはDBサーバにしかできないことに使いましょう
- アプリサーバでできるなら任せる
- これが絶対正しいわけではない
実行計画のもととなるデータ
- 統計情報を元に実行計画を決めている
select * from mysql.innodb_table_stats- 実際のデータを見ているわけではない
ANALYZE TABLEにより統計情報を更新できる
実行計画の改善の基本戦略
- インデックスを活用する
- 不要なデータは取得しない
- ただしアプリに処理を委ねるときはその限りではない
- 駆動表は可能な限り小さく
- オンメモリで完結させる
| 名前 | 読込速度 | 読込速度(MB/s) |
|---|---|---|
| メモリ | 10GB/s | 10000MB/s |
| 有線ネットワーク | 1GB/s | 1000MB/s |
| SSD(シーケンシャルアクセス) | 100MB/s | 100MB/s |
| HDD(シーケンシャルアクセス) | 100MB/s | 100MB/s |
| SSD(ランダムアクセス) | 10MB/s | 10MB/s |
| HDD(ランダムアクセス) | 1MB/s | 1MB/s |
カーディナリティ
- 多様性のこと
- 都道府県やフラグ、モードなどデータの種類が限られていること
- データの種類が限られていることをカーディナリティが低いという
- 一般的にはカーディナリティが低いとインデックスの効果が薄いと言われる
- Bツリーでの絞り込み効果が低いため
- ただし、カバリングインデックスを効かせるためにあえて複合インデックスの一部として設定することもある
- クラスタインデックス側を参照しなくて良いのでパフォーマンスは向上する
駆動表
- MySQLにはjoinの方法がNested loop(NL)しかない
- NLはループの入れ子のようなものなので一番外側の件数が多ければ多いほど処理量が増える
- そのため、結合のもととなる駆動表を小さく保つ必要がある
- https://qiita.com/AykeJq0ILeYFOR4/items/53eea003333b171472f4
インデックス
出力フォーマットの変更
- MySQL5.6から実行計画はテーブル形式の他にJSON形式でも出力できる
explain format=json select ~
- テーブル形式より細かい粒度の情報が出力される
- 特にコストが表示されるので実際に何にコストがかかっているかを分解してみることができる
- それぞれのコストとかの説明とかあると良いよね
実行計画の改善
実行計画の出力イメージ
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| SELECT 識別子。 | SELECT 型 | 出力行のテーブル | 一致するパーティション | 結合型 | 選択可能なインデックス | 実際に選択されたインデックス | 選択されたキーの長さ | インデックスと比較されるカラム | 調査される行の見積もり | テーブル条件によってフィルタ処理される行の割合 | 追加情報 |
type
- レコードへのアクセスタイプ。上のほうが良い
| type名 | 説明 | 意訳 |
|---|---|---|
| system | systemテーブルの話 | |
| const | 一致するレコードが1つしかない | 1件しかないので高速 |
| eq_ref | プライマリーキーもしくはユニークなキーでの結合 | 1対1でマッチするから高速 |
| ref | 上記以外の等価結合 | NULLの可能性もあるが1対1でマッチするから高速 |
| fulltext | FULLTEXTインデックスが使われる | よく知らん |
| ref_or_null | IS NULLを使ったアクセス |
NULLアクセスはインデックス効かないけど最適化されているので若干マシ |
| index_merge | 2つのキーをマージする | 単体のインデックスアクセスよりは高速 |
| unique_subquery | サブクエリの最適化パターン。一意キーを選択した場合これになる | サブクエリなので一般的な最適化されたアクセスタイプよりは遅い |
| index_subquery | サブクエリの最適化パターン。一意でないキーを選択した場合これになる | ユニークではない分、unique_subqueryより遅い |
| range | 範囲指定をした場合でのアクセスタイプ | 範囲指定している分アクセス対象が多いので遅め |
| index | インデックスをフルスキャン | インデックスをあまり上手く使えてないよ |
| ALL | フルテーブルスキャン | 基本的には避ける。ただしデータが小さいテーブルはこれになることもあるしそれが最適なこともある |
extra
- 自分も知らないしかいつまんで
- 特にこの2つはメモリに乗り切らない可能性があり外部装置に吐き出された瞬間パフォーマンスが悪化する要因になる
- 特に目くじらを立てて一掃する必要はないが、そもそも不要な処理をさせている可能性があるので使っていることを理解し問題ない判断をした上で使うことをおすすめする
- ORマッパが吐き出すクエリを基本的には信用しない
| extra | 説明 | 意訳 |
|---|---|---|
| Using filesort | ソート処理でクイックソートが実行されている | ソートは本当にDB上でする必要がありますか？ |
| Using temporary | 一時的にテーブルを作成している | 結果が多すぎませんか？もっと小さくできませんか？ |
その他
- 実行されたクエリ自体はGeneral Logに出力されるので本番環境とかでも確認は可能
- ただし、実行時間までは出ない
- 可能ならslow query logに出力させることで実行時間を知ることができる
slow_query_log = 1,long_query_time = 0と設定すると実行されるクエリがすべて出力される(かしこー)- ただし再起動しないと反映されないので注意
- https://nishinatoshiharu.com/mysql-slow-query-log/
MySQLの権限を絞ってみた
権限設定でカラムレベルというものが存在するらしいのでどういうものか確かめてみた
root権限
mysql> create table if not exists grant_columns(
-> id int unsigned not null AUTO_INCREMENT,
-> name varchar(255) NOT NULL,
-> granted_name varchar(255) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into grant_columns (name, granted_name) values ('aaa', 'bbb');
Query OK, 1 row affected (0.00 sec)
mysql> create user test_user@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR test_user@localhost;
+-----------------------------------------------+
| Grants for test_user@localhost |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'localhost' |
+-----------------------------------------------+
1 row in set (0.01 sec)
mysql> GRANT SHOW DATABASES ON test.* TO 'test_user'@'localhost';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT select(`granted_name`) ON test.grant_columns TO 'test_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW GRANTS FOR test_user@localhost;
+----------------------------------------------------------------------------------+
| Grants for test_user@localhost |
+----------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'test_user'@'localhost' |
| GRANT SELECT (granted_name) ON `test`.`grant_columns` TO 'test_user'@'localhost' |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
test_user
mysql> use test; ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'test' mysql> SHOW GRANTS FOR test_user; ERROR 1141 (42000): There is no such grant defined for user 'test_user' on host '%' mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | grant_columns | +----------------+ 1 row in set (0.00 sec) mysql> desc grant_columns; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | granted_name | varchar(255) | NO | | NULL | | +--------------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> select * from grant_columns; ERROR 1143 (42000): SELECT command denied to user 'test_user'@'localhost' for column 'id' in table 'grant_columns' mysql> select granted_name from grant_columns; +--------------+ | granted_name | +--------------+ | bbb | +--------------+ 1 row in set (0.00 sec) mysql>
descでは他にカラム定義がないように見えるのに*を使うと怒られる(´・ω・｀)
「見せる」設定はできるけど「見せない」という設定はできないように見える。
MySQLについていろいろ書いてみる(2020年)
重複しているものもあるけど一旦書く
ストレージエンジン
ストレージエンジンとは？
- MySQLはいくつかのコンポーネントを組み合わせてできている
- その中で実際にデータを格納してデータを取得するところはストレージと呼ばれていて選択することが可能
- 他にも全文検索用の
MroongaやオンメモリのMemoryストレージなどがある - それまではMyISAMがデフォルトだった
InnoDB
- MySQL5.5からデフォルトになったストレージエンジン
- トランザクションをサポートしている
- パフォーマンスの懸念があったがどっかのタイミングでMyISAMを抜いた
- 一般的にMySQLの文脈でストレージエンジンに関することを言っていたら大体InnoDBのことを言っていると言っても良い
- この説明も基本的にInnoDBのことについて書く
MyISAM
- MySQL5.4まではデフォルトだったストレージエンジン
- なんとトランザクションをサポートしていなかった
- パフォーマンスは悪くなく敢えてMyISAMを選択することもあったとか
- パフォーマンスでInnoDBに抜かれたことで存在価値がなくなった
- お疲れ様でした
インデックス
B+ツリーとは？
- Bツリーの改良版
- ツリーなのでパフォーマンスは
O(log b n) - ノード連結リストでつながっていることが多いので範囲指定がやりやすい(range検索に有効)
- https://ja.wikipedia.org/wiki/B%2B%E6%9C%A8
クラスタインデックス
- 主キーが設定されていると主キーのインデックスにデータが格納されている
- MySQL(InnoDB)で主キー検索が速いのはこの構造による
セカンダリインデックス
- 主キー以外のインデックスのこと
- 主キー以外なので、インデックスにはインデキシングされた値と主キーが格納されている
- セカンダリインデックスの場合は、2回インデックスの走査が行われる
- ちなみにOracleはインデックスとデータが2つに別れているのでそもそも2回走る
カバリングインデックス
- MySQLに関わらず、クエリで参照される値がインデックスだけで十分な場合、インデックスの値のみで完結するので速い
- パフォーマンスチューニングでよく利用されるテクニックの一つ
インデックスを設定するときの注意点
- インデックスにはコストがかかる
- 単純にインデックスを格納するストレージ
- インデックスを作成するイニシャルコスト(CPU、メモリ、ストレージ)
- 最近(MySQL5.6以降)はオンラインDDLで実行できるのでそこまで気にしていないかも？
- インデックスを維持するコスト
- 特に更新系のクエリを実行するとインデックスの再構築を行う
- インデックスを設定するときは本当に使うのか？本当に必要か？を考えて設定する
ロック
- ここからは特に断りがなければInnoDBかつREPEATABLE READを前提にします
テーブルロック
- ロックの範囲のこと
- テーブル自体をロックする
- これが起こるとパフォーマンスが低下する場合が多くなるべく避ける
行ロック
- ロックの範囲のこと
- 最小のロック単位のためこれになるように工夫する
- 行自体をロックする
- 特定のカラムだけロック、とかはない
ギャップロック
- 範囲指定してロックをかけたときにその間も一緒にかけるロックのこと
- 例えば、実データがID1、4、5とあったとき、ID1〜5でロックを書けるとID1、5はもちろんID4もロックをかけ、ID2、3もINSERTできないようになる
- ギャップロックは存在しないデータに対してもロックをかける
- 空振るとテーブルロック相当のロックをかける
- ギャップロックはインデックスをベースにしてロックをかけているのでインデックスないところをロックしようとしてもテーブルロックになるので注意
ネクストキーロック
- 結構難しい
- 行ロックとギャップロックを組み合わせたもの
- これのおかげでInnoDBではファントムが発生しない
ロックの確認
information_schema.INNODB_TRXロッキングリードとノンロッキングリード
- ロッキングリードは
select ~ for updateやselect ~ for shareなどロックをかけて読み取ること - ノンロッキングリードは通常の読み取り
- MVCCで更新中のトランザクションがある場合に2つのリードの結果が異なることがある
- ノンロッキングリードで読み取られるのは古いバージョンのデータ
- なので、最新のデータが何であろうと、ロックされていようがいまいが構わない
- つまり、最新の情報がほしいときはロッキングリードを用いる必要がある
MVCC
- MultiVersion Concurrency Control
- 日本語では多重バージョン並行処理制御などと呼ばれる
- ロック中に読み取り性能を損なわないために更新前のスナップショットを返す仕組み
- MySQLに限らず、Oracle、PostgreSQLや他の製品にもある
- InnoDBではこれがあるため、ファントムリードが起きないことになっている
- これがあることで別の問題が発生することもある
- https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control
トランザクション分離レベル
REPEATABLE READ
- MySQL(InnoDB)のREPEATABLE READでは、ネクストキーロックがあるので事実上、ファントムリードを防ぐのでSERIALIZABLE相当の実装になっている。
- ただし、ロストアップデートをストレージエンジンが防いでくれないので気をつけないといけない(PostgreSQLとかは防いでくれるらしい)
結合の種類
NL
- Nested Loop
- InnoDBにはこれしかない
- 駆動表が大きくなると不利なのでなるべく小さくしましょう
- 要はループの入れ子
- なので一番外のループ回数が少ないと性能は上がる
Hash
- ハッシュテーブルを作成する
- メモリを食う
- インデックスがなくてフルスキャンが必要なときにはパフォーマンスを発揮するらしい
ソートマージ
- 結合する方、される方どちらもソートされている必要がある
- 完全外部結合などをするときに高いパフォーマンスを発揮するらしい
- あんま使われているところを見たことない
- こちらもメモリを食う
実行計画
統計情報
- 実行計画を作成するときに参照される情報
- インデックス使うべきか、とかデータ量からテンポラリファイルを作成するべきか、とかを計画するのに使う
- 基本的にはサンプリングされた情報が入る
- 更新頻度によっては実態と乖離することもある
- これを手動で更新して痛い目をみたことがある(Oracle)
SELECT * FROM mysql.innodb_table_statsで確認できる- https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html
カーディナリティ
- データの多様性
- 限られた種類ならインデックス効いてなくても良いかな
- カバリングインデックスを狙ってインデックス設定するのはあり
ロックの性質
- 行ロックでも書き込みが頻発するなら待ちが発生しやすい
ヒント句
- MySQLにはインデックスヒントを指定することができる
- ただしオススメしない
- 最近のMySQLのオプティマイザはバカじゃないので基本的に任せたほうが良い
- 統計情報が更新されると適切な計画に自動で変更してくれる
- 特にデータが増えたときに適切なインデックスを選択できなくなるリスクがあるので基本的にはおまかせする
- 自分がオプティマイザより賢いという自負があるなら設定すると良い
テーブル設計
正規化
実際的な設計のポイント
- 正しく行ロックをかけていたとしても更新が集中するレコードがある場合は期待値通りのパフォーマンスが出ないことがある
- 例えばカウンターとか。単純にインクリメントしていくやつ
- そういう場合は、レコード追加で定期的に集計とかで代用できないか考える
敢えて正規化を崩す
- JOINのコストを減らしたいということだと思われる
- データを冗長に持つことを許容する？
- 多分その延長線上にNoSQLがある気がする
- 少なくとも普通のWebサービスでそこまでのトレードオフを迫られることはないのでは？
サンプル
準備
docker-compose.yml
version: '3'
services:
mysql:
image: mysql:5.7
command: --default-authentication-plugin=mysql_native_password
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: test
MYSQL_USER: user
MYSQL_PASSWORD: password
TZ: 'Asia/Tokyo'
ports:
- 3306:3306
volumes:
- ./my.cnf:/etc/mysql/conf.d/my.cnf
my.cnf
[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci [client] default-character-set=utf8mb4
$ docker-compose exec mysql bash
データ準備
create table if not exists user(
id int unsigned not null AUTO_INCREMENT,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
insert into user (name) values ("user1"), ("user2"), ("user3"), ("user4"), ("user5");
-- ID=4 を削除
delete from user
where id = 4;
パターン1：trx1でインデックスを指定してUPDATE、trx2ではINSERTできる
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = 'user5-1' where id = 5; |
|
| 4 | -- データを確認 select * from user |
|
| 5 | begin; | |
| 6 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 7 | -- 成功 insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 成功 insert into user (name) values("name6"); |
|
| 9 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 10 | -- データを確認(id=4,6は見えない) select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1872
trx_state: RUNNING
trx_started: 2020-03-15 12:10:17
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1871
trx_state: RUNNING
trx_started: 2020-03-15 12:10:16
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
もちろんこの状態でtrx1がID=4へのINSERTをしてもロックされている
パターン2：trx1でインデックスを設定していないカラムを指定してUPDATE、trx2でINSERTできない→テーブルロックがかかる
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = 'user5-1' where name = 'name5'; |
|
| 4 | -- データを確認 select * from user |
|
| 5 | begin; | |
| 6 | -- データを確認(id=5のnameはname5) select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 待ち insert into user (name) values("name6"); |
|
| 9 | -- データを確認(id=5のnameはname5) select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1883
trx_state: LOCK WAIT
trx_started: 2020-03-15 16:10:49
trx_requested_lock_id: 1883:26:3:10
trx_wait_started: 2020-03-15 16:10:54
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: insert into user (id, name) values(4, "name4")
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1882
trx_state: RUNNING
trx_started: 2020-03-15 16:10:39
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 5
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
ここでtrx_rows_lockedが5なのはテーブルロックがかかったため実データの4件+ギャップロックの1件(削除したID=4)のデータと思われる
パターン3：trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできるものとできないものがある
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = concat(name, '-1') where id > 1 and id < 5; |
|
| 4 | -- データを確認 select * from user; |
|
| 5 | begin; | |
| 6 | -- データを確認 select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 成功 insert into user (name) values("name6"); |
|
| 9 | -- データを確認 select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1887
trx_state: RUNNING
trx_started: 2020-03-15 16:19:55
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1886
trx_state: RUNNING
trx_started: 2020-03-15 16:19:24
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 4
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
- これはギャップロックによるもの
- trx_id=1886の
trx_rows_lockedが3になっているのはID=2,3,4のこと- ID=4は存在しないがギャップロックによりロック対象になる
- whereで指定した範囲は2〜4だったので最終行にINSERTできた
パターン4：trx1でインデックスを範囲指定してUPDATE、trx2ではINSERTできない
| NO | trx1 | trx2 |
|---|---|---|
| 1 | begin; | |
| 2 | -- データを確認 select * from user; |
|
| 3 | update user set name = concat(name, '-1') where id > 1 and id <= 5; |
|
| 4 | -- データを確認 select * from user; |
|
| 5 | begin; | |
| 6 | -- データを確認 select * from user; |
|
| 7 | -- 待ち insert into user (id, name) values(4, "name4"); |
|
| 8 | -- 待ち insert into user (name) values("name6"); |
|
| 9 | -- データを確認 select * from user; |
最終的なロックの状態
mysql> SELECT * FROM information_schema.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1890
trx_state: RUNNING
trx_started: 2020-03-15 16:25:20
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 7
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 1136
trx_rows_locked: 2
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1889
trx_state: RUNNING
trx_started: 2020-03-15 16:24:52
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 5
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 4
trx_rows_modified: 3
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql>
ロッキングリードとノンロッキングリード
| NO | trx1 | trx2 |
|---|---|---|
| 1 | mysql> begin; Query OK, 0 rows affected (0.01 sec) |
|
| 2 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 3 | mysql> begin; Query OK, 0 rows affected (0.00 sec) |
|
| 4 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 5 | mysql> update user -> set name = concat(name, '-1') -> where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
| 6 | mysql> select * from user\G 1. row id: 1 name: user1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 7 | mysql> commit; Query OK, 0 rows affected (0.01 sec) |
|
| 8 | mysql> select * from user\G 1. row id: 1 name: user1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.01 sec) |
|
| 9 | mysql> select * from user for update\G 1. row id: 1 name: user1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.00 sec) |
- ロッキングリードとノンロッキングリードだと結果が異なる
- 本当にずれると困る値などはトランザクション中はロッキングリードでロックしておくとtrx2では更新待ちになって不整合が起きにくい
- ちなみにこのまま更新処理を行うとこうなる
- 多分想定していない結果になっているはずで変数として一度プログラムに読み込んで計算した結果をUPDATEとかすると期待値とずれることがあるだろう(ロストアップロード)
| NO | trx1 | trx2 |
|---|---|---|
| 10 | mysql> update user -> set name = concat(name, '-1') -> where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
| 11 | mysql> select * from user\G 1. row id: 1 name: user1-1-1 2. row id: 2 name: user2 3. row id: 3 name: user3 4. row id: 5 name: user5 4 rows in set (0.00 sec) |
