糞だと思いません？
私は糞だと思います。
というわけで今回はちょうど1年位前にかいた、mysqlをdisる会の続きとなります。
前回は、主に導入や設定方法についておもしろおかしく初見殺しの罠をかかせて頂きましたが、今回はクエリ周りの話がメインとなります。
今回はあまり笑えないかもしれません。（極力文章力ぅ……で楽しめるよう善処します）
それではいってみまーーーしょう！ヾ(｡>﹏<｡)ﾉﾞ✧*。
っとその前に使用しているMySQLとかは以下のとおりです
| OS | CentOS 7.4 (1708) |
| MySQL | 5.7.19 |
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;
| 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 を指定すると、その行が破棄され、エラーは発生しません。代わりに、無視されたエラーが警告を生成する可能性がありますが、重複キーエラーは生成しません。
追記：
とよくみたらちゃんとかかれてました……。
IGNORE が指定されていない場合は、エラーをトリガーするデータ変換によってステートメントが中止されます。IGNORE を指定すると、無効な値はもっとも近い値に調整されて挿入されます。警告は生成されますが、ステートメントは中止されません。mysql_info() C API 関数を使用すると、テーブルに実際に挿入された行数を確認できます。
うぐう！酷いよ MySQL くん……。
IGNORE は用法容量まもって正しくお使いください。
ちなみに DEFAULT 0 を外しても入ります。悲しいね。
InnoDB のCOUNT()はコストが高い
あたりまえだよなあ？
InnoDB の COUNT はテーブルスキャンが必要です。
まあよく考えれば当たり前なんだけど、MyISAM が高速なもんだからつい InnoDB も早いと思っちゃうよね。うん。
え、ひょっとして私だけ？（ひょっとして私の年収低すぎ？の画像略）
オプティマイザは日々進化している。そう。mysqlだからね。
mysql のオプティマイザは日々進化しています。
オプティマイザがテーブルスキャンを使用する方が効率的であると判断しないかぎり、各テーブルインデックスがクエリーされ、最適なインデックスが使用されます。かつて、スキャンは、最適なインデックスがテーブルの 30% 超にまたがっているかどうかに基づいて使用されていましたが、固定のパーセンテージによって、インデックスを使用するか、スキャンを使用するかの選択が決定されなくなりました。現在のオプティマイザは複雑になり、テーブルサイズ、行数、I/O ブロックサイズなどの追加の要因に基づいて推定します。
クソなことを書くつもりが mysql やるじゃん。って話になってしまいました。
古い記事を鵜呑みにせず、自分が使用するmysqlのバージョンのドキュメントを読む癖をつけましょう。
そんなこといってるお前が5.6のドキュメントをひっぱってくるのはなんなんだ。と突っ込んではいけません。
TRUNCATE TABLE は DELETE FROM xxx; より高速であるがそれ故に
BEGIN/COMMIT で囲っても意味がありません。
むしろ囲うな。囲うのは姫と普通のクエリだけにしろ。
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 の指定
指定できるから効くかとおもったらそんなことはないのである。
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。
5.6では解析はされるが無視されるのである。
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 ;
| 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 系でする場合はユーザ定義変数を使用するという方法があります。
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%";
| Variable_name | Value |
|---|---|
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
んで実際に作ったテーブルは以下の様な感じです。
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 したレコードに対して制限を超えるクソクエリで更新をかけてみます。
tbl_update.sql
UPDATE tbl_antelope SET
txt1="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt2="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt3="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt4="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt5="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt6="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt7="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt8="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt9="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt10="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt11="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt12="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt13="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt14="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt15="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt16="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt17="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt18="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ",
txt19="ああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああああ"
WHERE id=1;
実行するとどうなるか？
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;
| COUNT(*) |
|---|
| 20000000 |
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 が効かなくなるので注意が必要。
DATETIME 型と TIMESTAMP 型
もうやだこいつら。DATE型についても書くかもしれません。
はじめに
DATETIME 型も TIMESTAMP 型もどちらも日時を扱う型ですが、
この２つの大きな違いは、サポートされている範囲が異なるということです。
TIMESTAMP 型は 1970-01-01 00:00:01 から 2038-01-19 03:14:07 の範囲が有効な事に対し、
DATETIME 型は 1000-01-01 00:00:00 から 9999-12-31 23:59:59 の範囲をサポートしています。
と、最近のMySQLでは範囲のみの違いで留まっています。
って思うじゃん？ 範囲の違いだけではなく、TIMESTAMP 型ではデフォルトの動作で、自動で時刻を更新します。
一方 DATETIME 型はデフォルトの動作では更新しません。という違いもあったりします。
11.3.1 DATE、DATETIME、および TIMESTAMP 型
https://dev.mysql.com/doc/refman/5.6/ja/datetime.html
MySQL小話 ～ TIMESTAMP 型は TIMESTAMP だった ～
MySQL 5.6.5 より前は TIMESTAMP 型のみ自動更新機能が備わっていました。
※DEFAULT CURRENT_TIMESTAMP と ON UPDATE CURRENT_TIMESTAMP のことです。
この機能を使うためだけに TIMESTAMP 型をわざわざ選ぶ。
なんてこともあったとかなかったとか。
んで、この TIMESTAMP 型なんですが、MySQL 5.6.5 以降においても
自動更新機能がデフォルトで備わっています。
ただし、その挙動が少々ややこしいです。（この挙動自体は昔からのはずです）
TIMESTAMP および DATETIME カラムには、明示的に指定されないかぎり自動プロパティーはありません。
ただし、DEFAULT CURRENT_TIMESTAMP と ON UPDATE CURRENT_TIMESTAMP がどちらも明示的に指定されていない場合は、デフォルトで最初の TIMESTAMP カラムに両方とも存在します。
つまり、TIMESTAMP 型において一番最初のTIMESTAMP型で特に指定がない場合デフォルトでこの自動更新が行われる挙動になります。
やってみましょう。
CREATE TABLE tbl_timestamp_test_1 (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
value INTEGER NOT NULL,
update_time TIMESTAMP NOT NULL,
PRIMARY KEY (id)
);
--
mysql> INSERT INTO tbl_timestamp_test_1 (value) VALUES (0);
mysql> SELECT * FROM tbl_timestamp_test_1;
| id | value | update_time |
|---|---|---|
| 1 | 0 | 2019-02-03 15:39:39 |
なんということでしょう。
デフォルト値を適当に設定してみましょう。
CREATE TABLE tbl_timestamp_test_2 (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
value INTEGER NOT NULL,
update_time TIMESTAMP NOT NULL DEFAULT "2001-01-01 00:00:00",
PRIMARY KEY (id)
);
--
mysql> INSERT INTO tbl_timestamp_test_2 (value) VALUES (0);
mysql> SELECT * FROM tbl_timestamp_test_2;
| id | value | update_time |
|---|---|---|
| 1 | 0 | 2001-01-01 00:00:00 |
mysql> UPDATE tbl_timestamp_test_2 SET value=1 WHERE id = 1;
mysql> SELECT * FROM tbl_timestamp_test_2;
| id | value | update_time |
|---|---|---|
| 1 | 1 | 2001-01-01 00:00:00 |
なるほどね。
おまけ。
CREATE TABLE tbl_timestamp_test_3 (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
value INTEGER NOT NULL,
create_time TIMESTAMP NOT NULL,
update_time TIMESTAMP NOT NULL,
PRIMARY KEY (id)
);
ERROR 1067 (42000): Invalid default value for 'update_time'
CREATE TABLE tbl_timestamp_test_3 (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
value INTEGER NOT NULL,
create_time TIMESTAMP NOT NULL,
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.60 sec)
なるほどね。
というわけで結構難解な動作をします。
何が難解ってこれにつきますね。
デフォルトで最初の TIMESTAMP カラムに両方とも存在します。
この機能無効にする手段が用意されていますが、デフォルトの動作が
これであるので、そもそも TIMESTAMP を安易に選択しない。に尽きるかもしれません。
DATETIME 型のサポートしている範囲
これに関しては前述したとおり 1000-01-01 00:00:00 から 9999-12-31 23:59:59 の範囲となります。
1000～9999ってすげえ！って話なんですが、格納はできるが格納できるとはいったが！！！
みたいな感じになります。うーん。
どういうことか？
create table tbl_datetime_test (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
future_time DATETIME NOT NULL,
PRIMARY KEY(id)
) ;
mysql> INSERT INTO tbl_datetime_test (future_time) VALUES ("4000-01-01 00:00:00");
mysql> SELECT future_time, UNIX_TIMESTAMP(future_time) FROM tbl_datetime_test;
| future_time | UNIX_TIMESTAMP(future_time) |
|---|---|
| 4000-01-01 00:00:00 | 0 |
UNIX_TIMESTAMP() に範囲外の日付を渡すと、0 が返されます。
UNIX_TIMESTAMP() に範囲外の日付を渡すと、0 が返されます。
UNIX_TIMESTAMP() に範囲外の日付を渡すと、0 が返されます。
大事なので3回書きました。
DATETIME 型に対する厳密モードについて
厳密であるとはいったがすべてのケースで厳密であるとは一言もいってない！！
ってやつです。
mysql> INSERT INTO tbl_datetime_test (future_time) VALUES ("0000-00-00 00:00:00");
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'future_time' at row 1
mysql> INSERT INTO tbl_datetime_test (future_time) VALUES ("2012-02-31 00:00:00");
ERROR 1292 (22007): Incorrect datetime value: '2012-02-31 00:00:00' for column 'future_time' at row 1
mysql> INSERT IGNORE INTO tbl_datetime_test (future_time) VALUES ("2012-02-31 00:00:00");
Query OK, 1 row affected, 1 warning (0.52 sec)
mysql> SELECT * FROM tbl_datetime_test;
| id | future_time |
|---|---|
| 1 | 4000-01-01 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
0000-00-00 00:00:00
！？
INSERT IGNORE INTO の動作はこちらです
IGNORE を指定すると、無効な値はもっとも近い値に調整されて挿入されます。
なるほど。なるほど？
他
ちょっとかきたいこと忘れたので思い出したら都度加筆します。