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

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

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

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

INSERT IGNORE INTO について

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

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

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;

なんということでしょう。
このように 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;

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

mysql> SELECT * FROM tbl_rep;

おわかりいただけただろうか…。
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では解析はされるが無視されるのである。

8.0では……？
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

サポートされます。やったー！

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

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

うっかり 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 みたいなクソみたいな環境だとオペミスで普通に起こり得るので気をつけましょう。
あと slave には read_only をつけておきましょう。こんな事故が防げます。

さてとりあえず同期を再開させて次の検証をはじめますよっと。

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

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

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 ;

SLAVE

mysql> SELECT * FROM tbl_rep;

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

mysql> SELECT * FROM tbl_rep;

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

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;

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;

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 系でする場合はユーザ定義変数を使用するという方法があります。

InnoDB 8KB の壁 (Antelope)

InnoDB のファイルフォーマットが Antelope の場合俗にいう神テーブルみたいな構成になってるとあっさり 8KB の壁に到達してしまいます。

InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い
http://blog.kamipo.net/entry/2014/12/05/235641

D.10.4 テーブルカラム数と行サイズの制限
https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html

可変長カラムのストレージには長さバイトが含まれ、これには行サイズに対して評価されます。たとえば、VARCHAR(255) CHARACTER SET utf8 カラムは、値の長さを格納するために 2 バイトを使用するので、それぞれの値は最大 767 バイトを使用できます。

というわけでやってみましょう。
今回私が使用している 5.7.19 ではすでに「Barracuda」となっていましたので、
この検証をわざわざするために「Antelope」に変更します。

# 以下を追加
innodb_file_per_table=0
innodb_file_format=antelope
innodb_file_format_max=antelope # 設定しても無視された。set global でセットしても deprecated がでる。

show global variables like "%innodb_file_format%";

んで実際に作ったテーブルは以下の様な感じです。

CREATE TABLE tbl_antelope (
    id integer NOT NULL AUTO_INCREMENT, 
    txt1  VARCHAR(255) NOT NULL,
    txt2  VARCHAR(255) NOT NULL, 
    txt3  VARCHAR(255) NOT NULL, 
    txt4  VARCHAR(255) NOT NULL, 
    txt5  VARCHAR(255) NOT NULL, 
    txt6  VARCHAR(255) NOT NULL, 
    txt7  VARCHAR(255) NOT NULL, 
    txt8  VARCHAR(255) NOT NULL, 
    txt9  VARCHAR(255) NOT NULL, 
    txt10 VARCHAR(255) NOT NULL, 
    txt11 VARCHAR(255) NOT NULL, 
    txt12 VARCHAR(255) NOT NULL, 
    txt13 VARCHAR(255) NOT NULL, 
    txt14 VARCHAR(255) NOT NULL, 
    txt15 VARCHAR(255) NOT NULL, 
    txt16 VARCHAR(255) NOT NULL, 
    txt17 VARCHAR(255) NOT NULL, 
    txt18 VARCHAR(255) NOT NULL, 
    txt19 VARCHAR(255) NOT NULL, 
    txt20 VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB, ROW_FORMAT=COMPACT;

なにこれ辛い。

mysql> SHOW TABLE STATUS LIKE "tbl_antelope"\G

*************************** 1. row ***************************
           Name: tbl_antelope
         Engine: InnoDB
        Version: 10
     Row_format: Compact
     ...
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment: 
1 row in set (0.00 sec)

適当に挿入します。

INSERT INTO tbl_antelope VALUES(0,"","","","","","","","","","","","","","","","","","","","");

いま INSERT したレコードに対して制限を超えるクソクエリで更新をかけてみます。

実行するとどうなるか？

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

こうなります。
これは極端な例なのですが、8KB に収まる場合は普通にクエリの更新ができます。
ただし 8KB を超えるとこのようにエラーを返します。
テーブル設計時にはおそらく気づきにくいです（エラーや警告がでるわけでもないので）

もしそういうあやしいテーブルがあるなら一応 ROW_FORMAT の確認、場合によってはカラムの型の確認や変更を視野に入れても良いかもしれません。
まあ普通はないと思いますけど。ないよね？

あとついでに、このテーブルの ROW_FORMAT を変更して同様のクエリを叩いてみましょう。

まずは ROW_FORMAT を DYNAMIC に変更します

mysql> ALTER TABLE tbl_antelope ROW_FORMAT = DYNAMIC;

一応確認します

mysql> SHOW TABLE STATUS LIKE "tbl_antelope"\G

           Name: tbl_antelope
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
     ...
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)

それでは先程のクエリを叩いてみましょう

mysql> UPDATE 長いので省略 ;

Query OK, 1 row affected (0.57 sec)
Rows matched: 1  Changed: 1  Warnings: 0

やったぜ☆

ORDER BY で指定できるのはソート順序だけではない。

実はいろいろできるよ！　だがしかし……。という話。
結論から言うとindexがちゃんと効かない。

ひとまず検証するために以下のテーブルを

CREATE TABLE sort_test(
    id INTEGER NOT NULL AUTO_INCREMENT, 
    value INTEGER NOT NULL, 
    PRIMARY KEY(id)
);

レコードは以下な感じで適当に用意

mysql> INSERT INTO sort_test SELECT 0, FLOOR(RAND()*100000);
mysql> INSERT INTO sort_test SELECT 0, FLOOR(RAND()*100000) FROM sort_test;

件数は切りよくこんな感じになるように調整。

mysql> SELECT COUNT(*) FROM sort_test;

mysql> EXPLAIN FORMAT=JSON SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value = 1 DESC LIMIT 10;

  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3933102.20"
    },
    "ordering_operation": {
      "using_filesort": true,
      "table": {
        "table_name": "sort_test",
        "access_type": "ALL",
        "rows_examined_per_scan": 19503286,
        "rows_produced_per_join": 19503286,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "32445.00",
          "eval_cost": "3900657.20",
          "prefix_cost": "3933102.20",
          "data_read_per_join": "297M"
        },
        "used_columns": [
          "id",
          "value"
        ]
      }
    }
  }
}

filesort: true
あたりまえだよなあ？

mysql> SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value = 1 DESC LIMIT 10;
10 rows in set (4.57 sec)

index 貼ります。

mysql> ALTER TABLE sort_test ADD INDEX idx_value(value DESC);

ひとまずindexが確実に効くクエリを作って explain します。
ちなみに上でも書いてますが、INDEXのASC、DESCの指定は実質無視されるので意味ありません。

mysql> EXPLAIN FORMAT=JSON SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value DESC LIMIT 10;

  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3933102.20"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "sort_test",
        "access_type": "index",
        "key": "idx_value",
        "used_key_parts": [
          "value"
        ],
        "key_length": "4",
        "rows_examined_per_scan": 10,
        "rows_produced_per_join": 19503286,
        "filtered": "100.00",
        "using_index": true,
        "cost_info": {
          "read_cost": "32445.00",
          "eval_cost": "3900657.20",
          "prefix_cost": "3933102.20",
          "data_read_per_join": "297M"
        },
        "used_columns": [
          "id",
          "value"
        ]
      }
    }
  }
}

"using_index": true,
"using_filesort": false,

きいてるきいてるｗｗｗｗｗ

mysql> SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value DESC LIMIT 10;
10 rows in set (0.00 sec)

はい。では先程の。

mysql> EXPLAIN FORMAT=JSON SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value = 1 DESC LIMIT 10;

  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3933102.20"
    },
    "ordering_operation": {
      "using_filesort": true,
      "table": {
        "table_name": "sort_test",
        "access_type": "index",
        "key": "idx_value",
        "used_key_parts": [
          "value"
        ],
        "key_length": "4",
        "rows_examined_per_scan": 19503286,
        "rows_produced_per_join": 19503286,
        "filtered": "100.00",
        "using_index": true,
        "cost_info": {
          "read_cost": "32445.00",
          "eval_cost": "3900657.20",
          "prefix_cost": "3933102.20",
          "data_read_per_join": "297M"
        },
        "used_columns": [
          "id",
          "value"
        ]
      }
    }
  }
}

mysql> SELECT SQL_NO_CACHE * FROM sort_test ORDER BY value = 1 DESC LIMIT 10;
10 rows in set (4.42 sec)

"using_filesort": true,
"using_index": true,

using index になってるものの、using filesort なのでクイックソートになっちゃってますね。
なんでや！　というわけで、条件をつけた瞬間indexが効かなくなるので注意が必要。

他

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