糞だと思いません?
私は糞だと思います。

というわけで今回はちょうど1年位前にかいた、mysqlをdisる会の続きとなります。

前回は、主に導入や設定方法についておもしろおかしく初見殺しの罠をかかせて頂きましたが、今回はクエリ周りの話がメインとなります。
今回はあまり笑えないかもしれません。(極力文章力ぅ……で楽しめるよう善処します)
それではいってみまーーーしょう!ヾ(。>﹏<。)ノ゙✧*。

っとその前に使用しているMySQLとかは以下のとおりです

OS CentOS 7.4 (1708)
MySQL 5.7.19

INSERT IGNORE INTO について

ユニーク制約がついたキーに対して、すでにレコードがある状態で INSERT をすると重複エラーとなるのは当たり前かと思いますが、場合によっては、ないときだけ INSERT してほしいが、あるときはなにもしないで欲しい。
といった要件もあるかと思います。
そういったとき、ぐぐると多くのページで INSERT IGNORE INTO をかけばいいのよ!
という記事がゴロゴロでてきますが、わたしは待てといいたい。 (詳細をかかず使えばいいという記事は滅びて欲しい)

まあとりあえずやってみましょう。
手始めに以下のようなテーブルを用意します。

create_table.sql
CREATE TABLE tbl_unique_test (
    id INTEGER NOT NULL, 
    value INTEGER NOT NULL DEFAULT 0, 
    PRIMARY KEY (id)
);

挿入してみます。

mysql> INSERT INTO tbl_unique_test VALUES (1,1);
Query OK, 1 row affected (0.50 sec)

同じクエリをもう一回叩いてみます。

mysql> INSERT INTO tbl_unique_test VALUES (1,1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

はい。
IGNORE を指定してみます。

mysql> INSERT IGNORE INTO tbl_unique_test VALUES (1,1);
Query OK, 0 rows affected, 1 warning (0.00 sec)

OK が帰ってきました。やりたいことは実現できていますね?
ところが IGNORE には問題があります。
そう IGNORE はユニーク制約だけを無視するわけではありません。
他のエラーすらも無視します。
例えば、

mysql> INSERT INTO tbl_unique_test VALUES (2,"あああああああああああああああ!!!!!");
ERROR 1366 (HY000): Incorrect integer value: 'あああああああああああああああ!!!!!' for column 'value' at row 1

これにIGNOREをつけてみます。

mysql> INSERT IGNORE INTO tbl_unique_test VALUES (2,"あああああああああああああああ!!!!!");
Query OK, 1 row affected, 1 warning (0.56 sec)

なんということでしょう……。匠(IGNORE)の手により入ってしまいました。

mysql> SELECT * FROM tbl_unique_test;

id value
1 1
2 0

なんということでしょう。
このように IGNORE を指定すると本来エラーとしてほしいものまではいってしまいます。
こわいですねえ。

ちなみにこれはしっかりと公式ドキュメントにかかれているのですが、あくまで無視されますとなっているので、意図しないレコードが入ってしまうのはアレかもしれません。
https://dev.mysql.com/doc/refman/5.6/ja/insert.html

IGNORE キーワードを使用した場合、INSERT ステートメントの実行中に発生したエラーは無視されます。たとえば、IGNORE を使用しない場合は、テーブル内の既存の UNIQUE インデックスまたは PRIMARY KEY 値を複製する行によって重複キーエラーが発生し、このステートメントは中止されます。IGNORE を指定すると、その行が破棄され、エラーは発生しません。代わりに、無視されたエラーが警告を生成する可能性がありますが、重複キーエラーは生成しません。

うぐう!酷いよ mysql くん……。
IGNORE は用法容量まもって正しくお使いください。
ちなみに DEFAULT 0 を外しても入ります。悲しいね。

InnoDB のCOUNT()はコストが高い

あたりまえだよなあ?
InnoDB の COUNT はテーブルスキャンが必要です。
まあよく考えれば当たり前なんだけど、MyISAM が高速なもんだからつい InnoDB も早いと思っちゃうよね。うん。
え、ひょっとして私だけ?(ひょっとして私の年収低すぎ?の画像略)

オプティマイザは日々進化している。そう。mysqlだからね。

mysql のオプティマイザは日々進化しています。

https://dev.mysql.com/doc/refman/5.6/ja/where-optimizations.html

オプティマイザがテーブルスキャンを使用する方が効率的であると判断しないかぎり、各テーブルインデックスがクエリーされ、最適なインデックスが使用されます。かつて、スキャンは、最適なインデックスがテーブルの 30% 超にまたがっているかどうかに基づいて使用されていましたが、固定のパーセンテージによって、インデックスを使用するか、スキャンを使用するかの選択が決定されなくなりました。現在のオプティマイザは複雑になり、テーブルサイズ、行数、I/O ブロックサイズなどの追加の要因に基づいて推定します。

クソなことを書くつもりが mysql やるじゃん。って話になってしまいました。
古い記事を鵜呑みにせず、自分が使用するmysqlのバージョンのドキュメントを読む癖をつけましょう。
そんなこといってるお前が5.6のドキュメントをひっぱってくるのはなんなんだ。と突っ込んではいけません。

TRUNCATE TABLE は DELETE FROM xxx; より高速であるがそれ故に

begin/commitで囲っても意味がありません。
むしろ囲うな。囲うのは姫と普通のクエリだけにしろ。

https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html

TRUNCATE TABLE は論理的に、すべての行を削除する DELETE ステートメントや、DROP TABLE および CREATE TABLE ステートメントのシーケンスに似ています。高性能を実現するために、データを削除するための DML の方法をバイパスします。そのため、ロールバックすることができず、ON DELETE トリガーが起動されることはなく、さらに親子の外部キー関係を持つ InnoDB テーブルに対して実行することもできません。

また TRUNCATE TABLE を行うとオートインクリメントのカウンタも初期化されます。
DELETE FROM が高速になったというよりは DROP TABLE して CREATE TABLE するのと同じと考えるのが良いかもしれません。

REPLACE INTO は INSERT or UPDATE ではない

こちらをみていただきたい

CREATE TABLE tbl_rep (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    pid INTEGER NOT NULL UNIQUE, 
    PRIMARY KEY(id)
);

mysql> REPLACE INTO tbl_rep VALUES(0,1);
Query OK, 1 row affected (0.01 sec)

mysql> REPLACE INTO tbl_rep VALUES(0,2);
Query OK, 1 row affected (0.01 sec)

mysql> REPLACE INTO tbl_rep VALUES(0,3);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM tbl_rep;

id pid
1 1
2 2
3 3

mysql> REPLACE INTO tbl_rep (pid) VALUES(2);
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM tbl_rep;

id pid
1 1
4 2
3 3

おわかりいただけただろうか…。
INSERT or DELETE して INSERT なのである。
そもそも普通に考えてこんな更新の仕方しないと思うが。
というか例がよくないな……うう……。

CREATE INDEX の ASC or DESC の指定

指定できるから効くかとおもったらそんなことはないのである。

https://dev.mysql.com/doc/refman/5.6/ja/create-index.html

index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

5.6では解析はされるが無視されるのである。

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.7では解析はされるが無視されるのである。

レプリケーションの同期はエラーで止まる

ドジっこ美少女がやりそうなやつ

うっかり SLAVE で
CREATE TABLE tbl_test2 (value1 INTEGER NOT NULL, value2 INTEGER NOT NULL, value3 INTEGER NOT NULL);
こんなのを叩いてしまって「あ~間違えちゃった~///」
とか対処をせずに MASTER で同じやつを叩くとSLAVE側では

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
以下略
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
以下略
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'tbl_test2' already exists' on query. Default database: 'aa'. Query: 'CREATE TABLE tbl_test2 (value1 INTEGER NOT NULL, value2 INTEGER NOT NULL, value3 INTEGER NOT NULL)'
                 Skip_Counter: 0
以下略
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'tbl_test2' already exists' on query. Default database: 'aa'. Query: 'CREATE TABLE tbl_test2 (value1 INTEGER NOT NULL, value2 INTEGER NOT NULL, value3 INTEGER NOT NULL)'
以下略
1 row in set (0.00 sec)

Slave_SQL_Running: No

となります。(ノ∀`)アチャー
手作業 is GOD みたいなクソみたいな環境だとオペミスで普通に起こり得るので気をつけましょう。
さてとりあえず同期を再開させて次の検証をはじめますよっと。

トランザクション内で同期が止まるとどうなるか

あとせっかくなので、こちらの検証もしておこうと思います。

SQL_SLAVE_SKIP_COUNTER がまずいもう一つの理由
https://yakst.com/ja/posts/14

この記事は4年前に書かれたもののようです。
全く同じことをして今もとまるのか試してみることにします。

CREATE TABLE tbl_rep (
    id INTEGER NOT NULL AUTO_INCREMENT, 
    pid INTEGER NOT NULL, 
    PRIMARY KEY(id)
);

たぶんこんなのだろうか?

MASTER

mysql> INSERT INTO tbl_rep VALUES(0, 1);
Query OK, 1 row affected (0.52 sec)

mysql> INSERT INTO tbl_rep VALUES(0, 2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tbl_rep VALUES(0, 3);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tbl_rep ;

id pid
1 1
2 2
3 3

SLAVE

mysql> SELECT * FROM tbl_rep;

id pid
1 1
2 2
3 3

mysql> DELETE FROM tbl_rep WHERE id=2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tbl_rep;

id pid
1 1
3 3

スレーブがぶっ壊れるとされるクエリをぶん投げてみます

MASTER

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tbl_rep WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM tbl_rep WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM tbl_rep WHERE id = 3;
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

SLAVE

mysql> SELECT * FROM tbl_rep;

id pid
1 1
3 3

OH...

mysql> SHOW SLAVE STATUS \G

Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table aa.tbl_rep; Can't find record in 'tbl_rep', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.xxx, end_log_pos xxx

oh...

mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM tbl_rep;

id pid
1 1
3 3

oh...

Window 関数

PostgreSQL にあるようなWindow 関数はない。

でもねやっと入るみたいなんですよ……8.0でな!

MySQL王国に黒船(Window関数)がやってきた!
https://qiita.com/kakuka4430/items/8c66e743800fcb8bc040

12.19.2 Window Function Concepts and Syntax
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

一応 RANK っぽいのを 5.5 とかの 5.x 系でする場合はユーザ定義変数を使用するという方法があります。

ちょっとかきたいこと忘れたので思い出したら都度加筆します。

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.