FrontPage
概要 †
- データベース操作言語
- RDBの数学的な観点を全部無視して下手な入門サイトが作りまくられている状況に怒りがある。
リンク †
目次 †
関連ページ †
環境構築 †
- ローカルSQLの環境は、Dockerを使ってやるのが一番手っ取り早い。
- 手順
- sudo apt-get install mysql-server
- これにフォロー
- Docker内からの接続
$ docker exec -it Docker-mysql_mysql_1 bash -p
$ mysql -u root -p -h 127.0.0.1
- しかし、docker外からつなごうとするとむり
- ERROR 1698 (28000): Access denied for user 'root'@'localhost'
学習 †
勉強方法 †
- 以下のコマンドで管理画面に移れる
mysql -u root -p
用語 †
- MySQL
- データベース
- インデックスが一個、ビューが複数個、テーブルが複数個で構成
- mysqlデータベースとinformation_schemaデータベースは、管理用で慎重に触る
- testデータベースがデフォルトである
- テーブル
- (列名、型、属性）のタプルのリストに対して、データが入っている。
- 型はintとか
- 属性は主キーとか、UNIQUEとか
- ユーザ
- rootが管理者用。
- rootはユーザを追加できる。
- 列名
- fieldとも言う（が、行列の類推から列名と言ったほうがわかりやすいのでこっちで統一）
- 型
- 属性
- (Null, key, default, extra)のタプル。
storage engine †
フラグ †
sql_mode †
innodb_flush_log_at_trx_commit †
- 障害耐性を下げて書き込みクエリパフォーマンスを上げる闇魔術
- Flushing the log to durable storage means that InnoDB asks the operating system to actually flush the data out of the cache and ensure it’s written to the disk.
- 具体的には、書き込みクエリ→InnoDB Buffer Pool(memory)->redo log(local file)-> page cache = OS の書き込み依頼キャッシュ(memory)->durable storage(storage) の流れは、トランザクションごとに行うのがデフォルトなのだが、
- 0: 1 秒に一回しか InnoDB Buffer Pool -> redo log を行わない（InnoDB プロセスが死ぬと 1 秒トランザクションが消える）
- 2: 1 秒に一回しか OS の書き込み依頼キャッシュ -> durable を行わない (OS クラッシュや電源喪失すると 1 秒トランザクションが消える)
- パラメータ「innodb_flush_log_at_trx_commit」の値でredo logの書出しを制御します。
- ① 0:トランザクションコミット時に、redo logをredo log bufferにのみ保存
- ② 1:トランザクションコミット時に、redo logをHard Diskに保存
- ③ 2:トランザクションコミット時に、redo logをPage cacheに保存
- what is the purpose of innodb_flush_log_at_trx_commit?
- InnoDB performs most of its operations at the memory (InnoDB Buffer Pool). All the modified data is written to InnoDB transaction log file and then flushed (written) to durable storage (hard disk).
- 0 - Write to log file and flush to disk at every second (data is in buffer pool not written to log file - for performance gain). 1 - Flush to disk when a transaction commits - default(For data safety - ACID compliance) 2 - write to log file for every transactions and flush to disk at every second. (For performance gain)
- In many cases, flush to disk means the data is written from InnoDB buffer pool (memory) to Operating systems cache, not actually written to storage disk (permanent storage). In case of failure, at the worst case, you may lose data up to one second)
- If performance is the main goal of application, InnoDB provides a variable to control the frequency of log flushing - innodb_flush_log_at_timeout - which allows you set log flushing frequency range from 1 to 2700 seconds, by default it is 1.
スキーマ †
主キー †
- 要するにunordered_setのキー
- 例えば、同姓同名の人を入れたい場合は、姓名をキーにできない。
- 重複する値を取らない（UNIQUE）
- 空の状態は許されない(NOT NULL)
- 他にも性質はDEFAULT, AUTO INCREMENTというものがある
CREATE TABLE bookinfo (
isbn VARCHAR(20),
title VARCHAR(100),
price INTEGER NOT NULL,
PRIMARY KEY(isbn,title)
)
;
文法 †
分類 †
- データ定義言語（DDL）：データ構造定義
- データ操作言語（DML）：データ検索・新規登録・更新・削除
- データ制御言語（DCL）:データに対するアクセス制御
selectクエリ †
- 全テーブルに名前をつけるべきレベル。サブクエリにも名前をつけられる。
- SQL文にも型がある
- 例えばselectのfromの中のサブクエリは何個rowが合っても良いが、selectの中のサブクエリはかならずスカラを返す必要があり、これをやらないと上のランタイムエラーになる！！
- スカラ (プリミティブな文字列・整数・booleanなど)
- スカラは as NAME とすることでカラムとして参照できるようになる
- テーブル型
- テーブル
- テーブル型 テーブルで、テーブル型がテーブルにコピーされる。
- テーブルには、n by mと定義できて、n by 1のものは特にカラムと呼ぶ
SELECT [カラム | スカラ] (, [カラム | スカラ], ...)
FROM [テーブル | テーブル名](, [テーブル | テーブル名], ...)
WHERE CONDITION(テーブル, スカラ)
GROUP BY [カラム], [カラム]...
HAVING CONDITION(テーブル, スカラ)
ORDER BY [カラム]
LIMIT 100
OFFSET 3;
- 実行される順序は以下
- FROM → WHERE → GROUPBY → HAVING → SELECT → ORDER BY → LIMIT → OFFSET
- FROM [テーブル | テーブル名](, [テーブル | テーブル名], ...)
- from employees としてもよいが、from employees eもしくはfrom employees as eのようにテーブルにしておいたほうがクエリが書きやすい。
- テーブルが複数ある場合は、テーブルの直積が取られる！
- 自分のテーブルの2つ以上の行の比較をしたい場合、直積を取ってwhereをすればよい
- WHERE CONDITION
- 一般に列を限定するために使う。
- conditionの書き方について特記すべきこと: 比較演算子は=, !=は、<>もしくはnotを前につける、ワイルドカード検索は、LIKE '%値%'、&&, ||は、AND, OR
- GROUP BY [カラム], [カラム]...
- カラムのタプルが一致しているものでshuffle操作が行われる。
- それぞれのshuffle操作が行われたあとには、タプルごとにグループテーブルが一時生成される。
- select文は、これらの一時生成されたテーブルごとに1 rowのみを出力する
- 1 rowしか出力しないので、タプルに含まれている変数と、グループテーブルを何らかの形でスカラにaggregateした値しかrowに含めることができない。
- HAVING CONDITION(テーブル, スカラ)
- GROUP BYすると、その後に使えるカラム・スカラが変化する（具体的にはgroup byに含まれる変数か、グループテーブルのaggregate結果のみになる）
- つまり、whereはrowのフィルターであったのに対し、havingはグループテーブルのフィルターになっている。
- ORDER BY カラム[, カラム, ...]
- 表示順をソートしたい場合
- 最後にORDER BY 列名 ASC|DESCをつける。
- 単数キー：SELECT * FROM bookinfo ORDER BY price ASC;
- 複数キー：SELECT * FROM bookinfo ORDER BY price ASC,isbn DESC;
- OFFSET 3
- 初めのn行を無視する。ただしアクセス自体はしているので高速化にはあまりならない。
便利関数 †
WITH CTE1 AS (
SELECT item_id, item_name FROM items
), CTE2 AS (
SELECT item_id, item_name FROM items
)
- テーブル JOIN テーブル (on CONDITIONS)
- テーブルを出力する
- リレーション系の真骨頂 参考1, 参考2
- テーブルとテーブルの直積を取ったテーブルを出力する。
- onで直積のテーブルに条件をつけてフィルターをかけることができる。
- LEFT, RIGHT, FULL(=LEFT RIGHT) JOINというものは、そちら側のテーブルのrowに対応する行が少なくとも一つ出力されるようになる
- もちろん対応行がないのに無理やり出力しているので、対応行がない行はNULLで埋められる。
- from a, bと a INNER JOIN bは基本同じだが、INNER JOINじゃないとONが使えない。,の場合は代わりにwhereでやる。
- group by
- 列を同一視することができる。
- 同一視した部分は、グループテーブルのグループ代表変数みたいに扱われる。
- 同一視した部分以外はのものはaggを使って集合のように扱わないとアクセスできない。
- agg関数群
- agg(カラム)でスカラが出力される。これはグループテーブルごとのreduction。各rowはかならずどこかのグループに属している。
- agg(カラム)over(range)でスカラが出力される。これはグループテーブルは全く関係ない！
- reductionには、max, sum, avg, min, countなどが存在する。
- 集合に対する枝刈りをしたいならば、having count(*) > 10とか、having max(Quantity) > 100とかのようにする
- countはカラムじゃなくテーブルでも動くが、他はカラムじゃないとランタイムエラー
- 日付系
- SELECT * FROM `table` WHERE `date` > ( NOW( ) - INTERVAL 1 DAY )
- DATEDIFFという関数があって、これを使うと日付間の演算が出来る。
関数的なもの †
- CREATE PROCEDURE と CREATE FUNCTION という Statement がある
- PROCEDURE はテーブル操作を含むやつ、FUNCTION は含まないやつ
- DEFINER で誰が作成したかを指定することができる。主に監査用。以下で確認できる
- SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.EVENTS;
- SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.ROUTINES;
- SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.TRIGGERS;
- SELECT DISTINCT DEFINER FROM INFORMATION_SCHEMA.VIEWS;
if †
- SELECT 文の中で使う場合、select *, case when CONDITION then true else false end from table; みたいな感じで case when を使うのがまるい
別名 †
- 別名と言っているが、省略以上の意味を持っている。
- 例
- Employee emp
- Employee as emp
重複除外 †
- select distinctとすると、重複除外される。
IS NULL †
表記 †
- MySQLに頑張って入れるのはめんどいので、なんかこういう方式の入力形式が流行っている。
- {"headers": {"Employee": ["Id", "Name", "Salary", "ManagerId?"]}, "rows": {"Employee": [[1, "Joe", 70000, 3], [2, "Henry", 80000, 4], [3, "Sam", 60000, null], [4, "Max", 90000, null]]}}
- headersが列名、rowsの一つ一つが行に相当する。
更新操作 †
- INSERT INTO：末尾追加
# INSERT INTO bookinfo(列名1,列名2,列名3) VALUES(値1,値2,値3);
INSERT INTO bookinfo(isbn,title,price) VALUES('00002','SQL test',2300);
INSERT INTO fruit (name, price) VALUES ("apple", 120), ("pineapple", 200);
INSERT INTO fruit SET name="melon", price=1500;
# auto_incrementの項がある場合は、その場所を飛ばしてタプルを書けば良い。また、強引にauto_incrementの部分に変な値を追加すると、次からはその値を基準にincrementしていく
INSERT INTO users (name, password, created_at) VALUES ("John Legendary","75D2DB8E-45E5-08D5-6669-BBA06F615472","2017-04-01 08:13:56"); #こんな感じでvaluesに対して列名を固定させることもできる
# UPDATE テーブル名 SET 列名1='', 列名2='' WHERE [行抽出用condition];
UPDATE bookinfo SET title='Android基礎テキスト',price=2000 WHERE isbn='00005';
# DELETE FROM テーブル名 WHERE [行抽出用condition];
- トランザクション：必ず連続して行わなければならない処理A->Bを確実に行う
- Aを行った後、普通にBが終了すれば問題ない（このことをコミットという）
- Aを行った後、Bがトラブルで実行不能の場合、Aを取り消す（ロールバックという）
- テーブルを作るときに、ENGINE=InnoDBを指定する必要がある
BEGIN;
[処理]
COMMIT; # もしくはROLL BACK（BEGINの前の状態に戻る）
関数 †
- return文の閉じカッコの中にセミコロンを最後につけてはいけない。
- declare, setなどは関数のbeginに入れて置かなければならない。
条件分岐 †
- case when CONDITION then DATA1 else DATA2 end
- どのデータもこれで分岐させることができる
- DATA1とDATA2は同じ型じゃなくてもいいが、そうじゃいと基本的には後段がバグる
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M = N-1;
RETURN (
case when N > (select count(distinct e.salary) from employee e)
then
null
else (
select a.optional_salary
from
(
select distinct e.salary as optional_salary
from employee e
order by e.salary desc
) a limit 1 offset M)
end
);
END
管理用コマンド †
- SET NAMES CP932; #日本語入力OKにする（Ubuntuでは効かなかった）
- show databases; #データベースの一覧を出力
- create database newdatabase; #新しいデータベースの追加
- USE newdatabase;#データベースの選択
- 今のデータベースの確認
show tables; #テーブルの一覧を出力
CREATE TABLE bookinfo( #テーブルの追加。フィールドを必要な分だけ指定（[field, type, attribute]+）。
isbn VARCHAR(20) PRIMARY KEY,
title VARCHAR(100),
price INTEGER NOT NULL
);
SHOW FIELDS FROM bookinfo; #テーブルのフィールドを出力
DROP TABLE bookinfo;
- USE mysql;#管理用データベースの選択（rootユーザが必要）
- select user from user #ユーザの一覧を出力
- GRANT ALL PRIVILEGES ON mybookdb.* TO newuser@localhost IDENTIFIED BY 'newuserpassward' #新しいユーザの追加
- QUIT; #終了. Ctrl-Dでもよい
- dropというものはSQLのテーブルを削除する操作
インデックス †
- indexとは、要するにmap<ll, S>であって、indexが[l, r)の範囲のデータ in Sを全列挙するというクエリが簡単にできるもの。
- もっと一般化すると、d次元データの矩形領域に含まれる点群を高速全列挙できれば何でも良いという話がある。
- B木が平衡二分木が使われているというのが有名
- 基本的には上のスキームに乗ってさえいれば全然問題なくて、亜種がある。例としては
- r=l+1ならハッシュindex（WHERE id = 1;とかのクエリだけが早くなる）
- Sがbinaryだったらビットマップ索引
- 地理情報であれば、Segment Treeやkd木などの多次元矩形領域全列挙アルゴリズムを使えば良いねということになる
- クラスタ化で、そもそも冗長にデータをディスクそのものに持っておきましょうという話になると、indexがちゃんと整合していない部分が現れる。こういうものは、実際にアクセスしたときに初めて整合するように代入し直すので、
計算量 †
設計 †
結局これどうやってシステムと組み合わせるの？
雑多 †
select id, name from a order by 2;
とすると、1-indexedでスキーマの2番目を取得することができる。なぜかというと一時テーブルを生成する場合とかに名前をつけてられないので
SQL
safe devide
if
date_diff
concat
format
unnest
SQLのunionとunion allの差は非常に少なくて、要するに重複がないかどうかに尽きる（union allだとmultisetになる）
unionは、縦方向に連結する。スキーマは必ず同じじゃないといけない、さもなくばエラー
over句は分析関数と呼ばれる。「行をまとめないのに集合関数のように集計する」これが分析関数の基本的動作です。
https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5 この資料めっちゃよい
集合関数
SELECT COUNT(*) FROM test_orders;
COUNT(*)
6
分析関数
SELECT order_id, item, COUNT(*) OVER () FROM test_orders;
ORDER_ID ITEM COUNT(*)OVER()
1001 Apple 6
1005 Banana 6
1010 Banana 6
1021 Apple 6
1025 Apple 6
1026 Apple 6
over句には行の同一視というか、集計範囲が指定できる。指定方法にはpartition, window, groupがある。partitionはgroup byと同じで同じ値のものが同一視される。ordered by itemは「itemでソートしたときの先頭行からの累積和」というもとの意味と異なるもの。windowはgroup byのオプション。ORDER BYでは先頭行から現在行までが対象になると説明しましたが、これは実はWINDOW指定を省略したときのデフォルト動作。ROWS BETWEEN If replication lag is high but steady, you can delete the failover replica and recreate it. However, if the write load continues, and the replication lag continues to increase, you must take action
レプリカの同期が遅れている場合、別にレプリカを消して作り直せば、データベースにクエリを適用するのではなく、マスターをそのままコピーできるからそれでも良い
3 preceeding 2 foillowingのようなもので、今の行プラスマイナス何行を範囲に含めるか？という話になる。
RANGEは、行数ではなくORDER BYで指定したカラムの値で範囲指定する。つまり、10「日」前までの総計などのクエリになり、すごい。
partition by ID order by SortKey?はどちらかというとpair<S, T>でのソートをしているという感覚っぽい。https://www.oracle.com/technetwork/jp/articles/otnj-sql-image3-4-323606-ja.html
ちなみに、select *とかやるとスキーマがテーブルから読み込まれるが、
「select 'hoge', 3, timestamp '2015-12-4 3:13:43'」はそれそのものが無名表となる。
無名の表は無名な部分が前からf0_, f1_…と連番でつけられていく（無名, data, 無名のようになっち得たらf0_, data, f1_と名付けられる）
SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34';
ちなみに、普通はinsert into test_orders values (1001, 'Apple', 4, date'2018-1-10');とするらしい。
timestamp_diffはタイムスタンプの引き算を行う
format_timestamp(string, timestamp)は%Xとかさまざまな表現があるっぽい。
timestamp部分の抽出を行い、以下の％から始まる文字列を利用することができる。
%Y-%m-%d %H:%M:%S %Xで時間以下だけ表示
You can redirect Dremel query output from within a client with the -> operator:
SELECT f1 FROM Table -> my_output_file.csv;
You can use the ->> operator to append client query output to the end of an existing file:
SELECT f2 FROM Table ->> my_output_file.csv;
With - as構文は便利で、複数のテーブルを前処理で取得して変数に格納することができる。サブクエリ構文って何に使うの？
https://www.postgresql.jp/document/9.4/html/queries-with.html
なんかselect, having, where, fromのどこにも入って来る可能性があるらしくかなりヤバそう
fromに入っているのはほとんどwith as句と同じ。しかも、with as句はDAGになってさえいれば順序を木にする必要ない
なおwith as句を複数のクエリに渡って使いたい場合は、create view句というものが存在
CREATE VIEW s1 AS
SELECT age, COUNT(age) as age_count
FROM students
GROUP BY age;
microsoft SQLでは、limit 10の代わりにselect top(names)を使うらしい。どれくらいの出力かとかわからないし使いみち不明だけど。
SQLではlikeで%=0個以上の文字、_で1文字に対応して検索できる
binlog †
- 実際に実行された更新系クエリの情報が記述されていてなんらかの理由によりデータが壊れた際のデータ復旧とかにも役にたつ。
- binlog の format には以下の 3 種類ある
- フォーマットの種類 設定値(文字列) 設定値(数字) 備考
- ステートメントベース STATEMENT 1 実際に実行された SQL を記録
- 行ベース ROW 2 実際に変更された行のデータの情報を記録
- ミックス MIXED 0 基本的にはステートメントベースと同じで非決定性のクエリの際は行ベースと同じ形式のログを出力する
レプリケーション [#q82f6f7f]
遅延への対処 †
- If replication lag is high but steady, you can delete the failover replica and recreate it. However, if the write load continues, and the replication lag continues to increase, you must take action
- レプリカの同期が遅れている場合、別にレプリカを消して作り直せば、データベースにクエリを適用するのではなく、マスターをそのままコピーできるからそれでも良い
InnoDB †
バッファプール †
- InnoDBでは、全ての変更はバッファプール上で行われ、後から遅れてバッファプールの内容がテーブルスペースへ書き込まれる。そして、永続性を保証するためにログに更新した内容を記録するようになっている。ログへの更新はシーケンシャルな書き込みになるためテーブルスペースへの書き込みに比べると高速だから
- デフォルトでは、buffer pool のメモリは 25% のみ維持される。メンテナンス直後にはまず buffer pool がすべて開放され、その後バックグランドで 25% の innodb buffer pool がリストアされます。
- 25% は innodb_buffer_pool_dump_pct は で変更ができる
- アプリケーションのメンテナンス中に素早く innodb をウォームアップする回避策としては、Cloud SQL のメンテナンス後にデータ読み込みのために SELECT COUNT(*) FROM db_name.tb_name を実行し、セカンダリインデックスの読み込みのために SELECT COUNT(*) FROM db_name.tb_name FORCE INDEX(key_name) を実行するという方法が知られています。
mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_dump_pct%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_pct | 25 |
+-------------------------------------+----------------+
- 25% のバッファプールのロードを早める方法としては、再起動後にMySQL にログインして SET GLOBAL innodb_buffer_pool_load_now=ON; を実行することで、innodb のウォームアップを即時で行うことができます [5]。
Dirty pages †
https://qiita.com/shinyashikis@github/items/638accdd74bb6972b0ce
ダーティページはfsyncされていないバッファプール内部のデータのこと
チェックポイントはダーティページ→テーブルスペース(SSD)のflush processで、ファジーとシャープがある。シャープはどうしてもダーティページの閾値が越えた時に発生するやつ
dirty=badではなく、パフォーマンスへの影響はsharp checkpoint プロセスが走らない限りは大丈夫。https://stackoverflow.com/questions/10107845/how-to-fix-innodb-dirty-pages べつに直すべきものではなく、ダーティが多いと何が起きるかというと、突然再起動した時にデータが損失する可能性があるというだけ
トラブルシューティング †
EXPLAIN †
PostgresSQL EXPLAIN はMySQL explainとは結構違う
https://www.slideshare.net/MikiShimogai/postgre-sql-explain
http://nippondanji.blogspot.com/2009/03/mysqlexplain.html
https://qiita.com/kzbandai/items/ea02727f4bb539fcedb5
https://thinkit.co.jp/article/9658
https://taityo-diary.hatenablog.jp/entry/2017/09/10/091729
https://www.casleyconsulting.co.jp/blog/engineer/259/
SQL マテリアライズド・ビュー
https://qiita.com/wanko5296/items/61c3e6ec4561b26beb5c#:~:text=%E3%83%9E%E3%83%86%E3%83%AA%E3%82%A2%E3%83%A9%E3%82%A4%E3%82%BA%E3%83%89%E3%83%93%E3%83%A5%E3%83%BC%E3%81%A8%E3%81%AF%E3%80%81%E3%83%AA%E3%83%AC%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%8A%E3%83%AB,%E3%81%9F%E3%82%81%E6%80%A7%E8%83%BD%E3%81%8C%E5%90%91%E4%B8%8A%E3%81%99%E3%82%8B%E3%80%82
ロック †
テーブルロックさせないためには、インデックスのあるカラムで条件指定する
idカラムで条件指定をした場合は行ロックで、nameカラムで条件指定をした場合はテーブルロックがかかってしまう、ということでトランザクションの更新クエリの条件指定には注意する必要があります。
idカラムとnameカラムの違いは何かというとインデックスの有無で、インデックスでレコードを特定している場合は行ロックとなるとのことです。
lock †
- ロックは、transaction での非直感的な挙動を抑制するために作られている。
- 例えば、ネクストキーロックは Dirty Reads, Non-repetable Reads, Phantom Reads を抑制している https://softwarenote.info/p1067/ (Phantom Reads = 並列実行中の他のトランザクションが挿入しコミットしたレコードを読み込んでしまうため、以前存在しなかった行データが読めてしまう。)
- 変な挙動を起こさないように頑張るとパフォーマンスが落ちるので、4 種類 MySQL ではトランザクション分離レベルを定義している。
トランザクション分離レベル【Isolation levels】は、これらのリード現象が発生するかしないかによって表すことができます。
分離レベル ダーティリード ノンリピータブルリード ファントムリード
Read uncommitted ○ ○ ○
Read committed × ○ ○
Repeatable read × × ○
Serializable × × ×
スクロールできます
- テーブルロックと行（レコード）に対するロックがあります。
- ロックの強度は排他と共有の2種類があります（MySQL5.7からはその中間である共有排他（SX）ロックがありますがいったん置いときます）
- テーブルロックに対するロックは以下です。
- 行（row）に対するロックは次の3種類です。
- 行（レコード）ロック
- ギャップロック
- ネクストキーロック
- サーバコアのロック
- サーバコアの機能で観測可能 SHOW PROCESSLIST で Waiting for global read lock などで観測できる
- ストレージエンジン (InnoDB) のロック (InnoDB のレコードレベルのロックには、レコードロック、ギャップロック、ネクストキーロックなどの複数のタイプがあります。)
- SHOW PROCESS LIST では Sending data とのみ表示
- レコードロック: これはインデックスレコードのロックです。
- ギャップロック: これはインデックスレコード間にあるギャップのロック、または先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロックです。
- ネクストキーロック: これはインデックスレコードに対するレコードロックと、そのインデックスレコードの前にあるギャップに対するギャップロックとを組み合わせたものです。
レコードロック †
レコードロック：（行に対するロックではなく）インデックス行に対するロック
ネクストキーロック †
あるインデックスに値 10、11、13、20 が含まれているとします。このインデックスでは、次の間隔をカバーするネクストキーロックが使用される可能性があります。ここで、( や ) は間隔の端点が含まれないことを表し、[ や ] は間隔の端点が含まれることを表します。
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
一つ目のトランザクション
mysql> begin;
Query OK, 0 rows affected （0.00 sec）
mysql> select * from test.j_pedigree where id < 5 for update;
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
| id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg |
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
| 1 | ジョージ（1世） | ジョースター | NULL | NULL | 0 | 0 |
| 2 | メアリー | ジョースター | NULL | NULL | 0 | 0 |
| 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 |
| 4 | エリナ | ジョースター | NULL | NULL | 0 | 0 |
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
IDが５未満なので4までを行ロックは取得しています。
これにギャップロックはその末尾のindex値を持つ行の後のギャップもロックを取るんで5もロックされるというわけです。
試してみます。
2つ目のトランザクション
mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0);
ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction
はい、やはり待たされてタイムアウトエラーとなりました。
ギャップロック †
ギャップロックの特徴としては、
index値を持つ行と行の間にあるギャップ
先頭のindex値を持つ行の前のギャップ
末尾のindex値を持つ行の後のギャップ
に対してロックをかけます。
なんのこっちゃと思われると思ったので、実際に試してみます。
一つ目のトランザクションでIDが1から6のレコードを抽出（for update付）しました。
この時点ではID＝5のレコードはありません。
mysql> begin;
Query OK, 0 rows affected （0.00 sec）
mysql> select * from test.j_pedigree where id between 1 and 6 for update;
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
| id | last_name | first_name | father_id | mother_id | pucci_flg | jap_flg |
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
| 1 | ジョージ（1世） | ジョースター | NULL | NULL | 0 | 0 |
| 2 | メアリー | ジョースター | NULL | NULL | 0 | 0 |
| 3 | ジョナサン | ジョースター | 1 | 2 | 0 | 0 |
| 4 | エリナ | ジョースター | NULL | NULL | 0 | 0 |
| 6 | ディオ | ブランドー | 5 | NULL | 0 | 0 |
- ----+--------------------+--------------------+-----------+-----------+-----------+---------+
5 rows in set （0.00 sec）
二つ目のトランザクションでID＝5のレコードを追加してみます。
行単位でのロックであれば入るはずですが・・・、
mysql> begin ;
Query OK, 0 rows affected （0.00 sec）
mysql> insert into test.j_pedigree values(5,'ダリオ','ブランドー',null,null,0,0);
ERROR 1205 （HY000）: Lock wait timeout exceeded; try restarting transaction
INSERTは待たされ、最終的にはタイムアウトしました。
1つ目のトランザクションでj_pedigree テーブルのイメージはこうなっています。
id データ有無 ロック
1 あり 排他ロック
2 あり 排他ロック
3 あり 排他ロック
4 あり 排他ロック
5 無し 排他ロック
6 あり 排他ロック
データがない部分に対しても排他ロックがかかり、そこにデータを入れようとしてもロック待ちとなる。
select engine,object_schema,object_name,index_name,lock_type,lock_mode from performance_schema.data_locks;
lock_mode がX/IXは排他ロックを意味しています。
https://free-engineer.life/mysql-innodb-lock-mode/
インテンションロックと共有・排他ロック
ある行を共有ロックまたは排他ロックする前に、必ずインテンションロックを獲得する仕組みになっている。
行の共有ロックを獲得するために、テーブルのインテンション共有ロックを獲得
行の排他ロックを獲得するために、テーブルのインテンション排他ロックを獲得
そして、インテンションロック（IS・IX）と共有ロック（S）、排他ロック（X）の競合関係は次の表のようになる。
X IX S IS
X × × × ×
IX × ○ × ○
S × × ○ ○
IS × ○ ○ ○
○: 競合しない、×: 競合する
既存のロックと競合しない場合は、ロックが付与される。
競合している場合は、ロックが付与されない。（競合している既存のロックが解放されるまで待機）
トランザクションは、競合している既存のロックが解放されるまで待機する。既存のロックと競合し、デッドロックが発生する場合は、エラーが発生する。
この表をよく見るとわかるように、インテンションロック（IS・IX）同士は競合しないため、InnoDBの排他制御は行ロックとなることがわかる。
一方、排他ロック（X）がかけられている場合は、インテンションロック（IS・IX）を取得することができないので、テーブルの参照も更新もできません。（ロックが解放されるまで待たされる）
未整理 †
SQL92でCASE句が新しくできた。
Oracleではdecode, MySql?ではIfというものができたが、互換性がある文CASEのほうがつよい
elseを書かずにそのまま出た場合にはNULLが買えるので注意！
EXISTS(テーブル)
テーブルを引数にとって。行が存在していればTrue, そうでないならFalseを返す関数
WHENの代わりに使うことも出来る
ビューって何
SELECTとかWHEREは句
!=は、<>で表される
NULLはNULL以外の何物でもない
NLULが入った演算はSQLでは全部unknownになる。NULLは値ではない
NULLを対象とした演算で唯一演算がunknownにならないのは、NULL IS NULL NULLしかない
三値論理の一つでtrue, unknown, falseの三つに分かれている
true=2, unknown=1, false = 0とした時、論理演算ANDはmin, 論理演算ORはMAXに相当することになる。
よくやられる高速化として、CASEよりEXISTSのほうが早いみたいなのがある。これはEXISTSはインデックスをハルことが出来るから
CASEとEXISTSの交換は必ず同値となるが、
NOT CASE とNOT EXISTは同値とならない可能性がある。具体的にはNOT INの後にunknownが入っていると、必ずNOT INは何にもヒットしないというほぼバグみたいな仕様があるので注意
限定述語と呼ばれる、ANY, ALLという量化演算子が使える。これによってWHEREの中にfor allの術後を入れる子おtが出来る
限定述語の評価は、普通にandが展開されると思って問題ない
極値関数=min, maxのこと
max関数に空テーブルが入力されると、NULLがかえる。ちなみにAVGとかSUMも同様の挙動をする
ORDER BYは表示をするには便利だが、それ自体がリレーショナル演算子であるわけではない！これはカーソル定義の一部。
GROUP BYがない場合のHAVING句は、group by 1でテーブル全体が選択された場外になっている。なので、テーブル全体がある条件を満たしていたら何かの行を出力みたいな使い方ができる。
COUNT(*)はNULLを数えるが、COUNT(column)はNULLを数えないという差がある。なので、テーブルや グループテーブルのcount(*)とcount(column)が一致していればNULLがないという判定が出来る
SELECT countries.name FROM countries as c;
だと Error: near line 1: no such column: countries.name となりクエリが失敗しますが、
SELECT countries.name FROM countries;
だと普通に結果が取得できます。
SQL Standard に準拠した環境では、as で alias したオリジナルのテーブル名は使えなくなります。実際のところ AS は「alias = 別名をつける」というよりは、SQL Standard では「rename = 名前変更」に近い動作を行います [0]。そのため、テーブルに別名をつけた時点で、そのクエリの中で元の名前は使用することはできなくなります [1, 2]。SQL Standard は有料の資料なので具体的にリファレンスを示すことはできませんが、AS で別名をつけたテーブル名はそのクエリ内では使えないとご理解ください。
[0] https://modern-sql.com/feature/table-column-aliases#footnote-0
Although “alias” is the commonly used term, “rename” is more appropriate (and also used by the standard). Alias typically implies that an object can be referred to by its alias as well as by its original name. The feature described on this page actually renames the objects: you cannot use the original names to refer to these objects anymore.
[1] https://stackoverflow.com/questions/15318199/why-cant-i-use-a-tables-alias-and-its-original-name-in-the-same-query-when-two
[2] https://stackoverflow.com/questions/21381642/mysql-alias-and-table-name
Once you assign an alias to a table, that's its new name during the query - the original name is then not available. The "why" is due to the SQL standard.
selectにfor update指定をすることで、select されたそれぞれの行で行ロックが発生します。行ロックされた行を、行ロックしたり update しようとする場合、行ロックが解除されるまで待ち状態になります。行ロックしたトランザクションが commit or rollback されると、行ロックが解除されます。
ロックは、V$LOCKED_OBJECTテーブルで確認します。
select
OBJECT_ID
| SESSION_ID |
| ORACLE_USERNAME |
| OS_USER_NAME |
| PROCESS |
| LOCKED_MODE |
from v$locked_object;
ロック状態がある場合はデータが表示されます。
systemユーザで実行しました。権限がないと実行できません。
データのクエリーを実行してから、同じトランザクション内で関連データを挿入または更新する場合は、通常の SELECT ステートメントで十分な保護が提供されません。ほかのトランザクションは、クエリーが実行されたばかりの同じ行を更新または削除できます。InnoDB では、追加の安全性が提供される 2 つのタイプのロック読み取りがサポートされています。
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
