日々の覚書

2015/07/15

MySQL 5.7では迂闊にperformance_schemaをOFFするとSHOW STATUSが使えない（修正されました）

【2015/12/16 22:19】

MySQL 5.7にやられないためにおぼえておいてほしいこと from yoku0825

動作は5.7.7で確認していて、 5.7.8で暗黙のデフォルトがOFFになるって書いてあるのでやってみた。

MySQL 5.7.6から show_compatibility_56 というサーバー変数が追加されている。

SHOW {GLOBAL|SESSION} STATUS, SHOW {GLOBAL|SESSION} VARIABLES は今まで内部的にinformation_schemaのテーブルを叩いていた。これをperformance_schemaのテーブルを叩くように変えるのがこのパラメーター。

ONだとinformation_schemaから、OFFだとperformance_schemaから情報を取ってくる。

mysql57> SELECT @@version;
+--------------+
| @@version    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql57> SELECT @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql57> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|      476 |
+----------+
1 row in set, 2 warnings (0.01 sec)

mysql57> SELECT COUNT(*) FROM performance_schema.global_variables;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql57> SHOW GLOBAL VARIABLES;
..
476 rows in set, 1 warning (0.01 sec)

これが5.7.7までのデフォルトのままの動作。

mysql57> SELECT @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql57> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql57> SELECT COUNT(*) FROM performance_schema.global_variables;
+----------+
| COUNT(*) |
+----------+
|      475 |
+----------+
1 row in set (0.00 sec)

mysql57> SHOW GLOBAL VARIABLES;
..
475 rows in set (0.00 sec)

これが、5.7.8からの暗黙のデフォルトでの動作。

件数が微妙に違うのはいいとして(?)、その他ちょこちょこと。

* performance_schemaをOFFにしてると怒られる。

mysql57> SELECT @@performance_schema;
+----------------------+
| @@performance_schema |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql57> SELECT @@show_compatibility_56;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

mysql57> SHOW STATUS;
ERROR 1286 (42000): Unknown storage engine 'PERFORMANCE_SCHEMA'

performance_schema= 0だったらshow_compatibility_56= 0できないようにしてほしい(´･ω･｀)

* performance_schemaのテーブルに対するSELECT権限が必要。

mysql57> SHOW GRANTS;
+--------------------------------------+
| Grants for yoku0825@%                |
+--------------------------------------+
| GRANT USAGE ON *.* TO 'yoku0825'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

mysql57> SHOW STATUS;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'session_status'

information_schemaへのアクセスを制限するものはなかったけれど、performance_schemaはフツーのdbと同じくGRANTの制限を受けるのでこうなる。

さてさて…自作スクリプトでinformation_schema.GLOBAL_STATUSとか当たってるんだけどどうしようかな。。あと、GRANTの方はどこかで引っかかりそうな気がする。監視用ユーザーとか。

忘れないようにしておかないと(´･ω･｀)

2015/07/09

kamipo traditional (というかSTRICT_ALL_TABLES) では防げないMyISAMという名の化け物

TL;DR

kamipo traditionalですら完全に防ぎきれないアレがあるので、そこを気にするなら出来る限りさっさとMyISAMからInnoDBに引っ越しましょう。

これらの記事を読んだ人向けです。

ルーク！MySQLではkamipo TRADITIONALを使え！ | おそらくはそれさえも平凡な日々

Javaでkamipo traditionalを有効にする - その手の平は尻もつかめるさ

アプリでミスって不正なデータが入るくらいだった500になったほうがマシ。というのが個人的な考えです。

+激しく同意+

さて、激しく同意したところで、kamipo traditionalでは倒せないMyISAMという名の化け物の話をしたいと思います。

kamipo traditionalに対してもMyISAMは果敢に立ち向かう。

その隙間を縫って不正なデータを入れようとしてくるのだッ！

mysql56> CREATE TABLE t1 (num int, val varchar(1)) Engine= MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET SESSION sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> INSERT INTO t1 VALUES (1, 'a'); -- Of course, this succeeds and data is stored.
Query OK, 1 row affected (0.00 sec)

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)


mysql56> INSERT INTO t1 VALUES (2, 'ab'); -- Of course, this fails and data is not stored.
ERROR 1406 (22001): Data too long for column 'val' at row 1

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

だがkamipo traditional(というかSTRICT_ALL_TABLES)も譲らない。桁の切り詰めをワーニングで許さず、エラーにする。正しい。圧倒的に正しい。

だがMyISAMが攻勢に転じる。出、出～Bulk Insert奴～。

mysql56> INSERT INTO t1 VALUES (3, 'c'), (4, 'dd'), (5, 'e'); -- This returns error, but..?
ERROR 1406 (22001): Data too long for column 'val' at row 2

mysql56> SELECT * FROM t1;
+------+------+
| num  | val  |
+------+------+
|    1 | a    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

(　ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ

(；ﾟдﾟ)

(つд⊂)ｺﾞｼｺﾞｼ
_, ._
（；ﾟ Дﾟ）

はい、エラーが返ってるのにデータはINSERTされてます。さすがMyISAM、俺たちにできないことを平然とやってのける。この動作はご存知の方も多いと思います。マニュアルに堂々と書いてあるしね。

非トランザクションテーブルの場合、挿入または更新される最初の行に不適切な値があるとき、どちらのモードでも動作は同じになり、ステートメントが中止されて、テーブルはそのまま変更されません。ステートメントが複数行を挿入または変更し、2 行目以降に不適切な値がある場合、どちらの厳密モードが有効になっているかによって結果は異なります。

STRICT_ALL_TABLES では、MySQL はエラーを返し、残りの行を無視します。ただし、それより前の行が挿入または更新されているため、結果は部分更新となります。これを防ぐには、テーブルを変更することなく中止できる単一行ステートメントを使用します。

STRICT_TRANS_TABLES では、MySQL は無効な値をカラムについてのもっとも近い有効な値に変換し、調整された値を挿入します。値が欠落している場合、MySQL はカラムデータ型の暗黙のデフォルト値を挿入します。いずれの状況でも MySQL はエラーでなく警告を生成し、ステートメントの処理を続行します。暗黙的なデフォルトについては、「データ型デフォルト値」に記載されています。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.7 サーバー SQL モード

うん。strict指定がなければ丸めてワーニング返すし、STRICT_TRANS_TABLESでもこのケースは丸めて3行入れてワーニングを返す。STRICT_ALL_TABLESでは1行だけ(というか入れられるところまで)入れてエラーを返す。
エラーを返すけど、もちろんロールバックとかされないからデータは残る。これがトランザクション非対応ってことだ。ACIDのAがないとこうなる。「エラーが返らなければそれは成功しているし、エラーが返るならそれは失敗している」という期待を鮮やかに裏切る。さすがMyISAM。

非strictモード, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, どれもいやだ。でも選ばないといけない。嫌だ。

他のトランザクション非対応なストレージエンジンもこんな動作になるし、ストレージエンジン固有のワーニングとかあるとそのハンドルはストレージエンジン側の実装責任になるので、strictなモードと結構相性が悪かったりしそうな予感。。

2015/07/01

innodb_stats_on_metadataが0でも問題なく（？）統計情報は再計算されるよ

innodb_stats_on_metadata=1でディスク容量激増とCPU負荷が発生 | DEVLAB を読んだ誰か（忘れた）に「innodb_stats_on_metadata= 0だと統計情報ズレない？手でANALYZE TABLEしないといけないの？」って聞かれて答えたメモ（だと思う）

そもそも8ページとか20ページじゃ全然足りないじゃんというのはここでいう「問題なく」の中には含まれない。

innodb_stats_on_metadataは *メタデータにアクセス(=information_schema, SHOW TABLE STATUSなど)* した時に統計情報を再作成するかどうかのフラグで、データが大量に更新された時の統計情報の再作成はここらへんでinnodb_stats_on_metadataを見ずにトリガーされている。

(gdb) b dict_stats_update
Breakpoint 1 at 0xa7c997: file /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc, line 3085.

gdbを突き刺しておいて

mysql56> SELECT @@innodb_stats_on_metadata;
+----------------------------+
| @@innodb_stats_on_metadata |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

mysql56> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.04 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 99750
 Avg_row_length: 68
    Data_length: 6832128
..

統計情報はこんな感じ。
ここに、統計情報がアップデートされるくらいの行数を変更するクエリーを投げ込むと

mysql56> DELETE FROM t1 WHERE num < 10000;

ここで止まる。

Breakpoint 1, dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
3085            if (table->ibd_file_missing) {
(gdb) bt
#0  dict_stats_update (table=0x7fdad80180a8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x0000000000a7e25a in dict_stats_process_entry_from_recalc_pool (arg=)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:313
#2  dict_stats_thread (arg=) at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats_bg.cc:355
#3  0x00000036dac079d1 in start_thread (arg=0x7fdaf75fe700) at pthread_create.c:301
#4  0x00000036da8e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

バックグラウンドスレッドらしい。こんなのいたのか。。 gdbを放してやるとクエリーが返ってきて、

mysql56> DELETE FROM t1 WHERE num < 10000;
Query OK, 9999 rows affected (28.51 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..

統計情報は無事に更新されている。

mysql56> DELETE FROM t1 WHERE num < 100;
Query OK, 0 rows affected (0.01 sec)

mysql56> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 89775
 Avg_row_length: 76
    Data_length: 6832128
..

テーブルの10%または6.25%が更新されたら、っぽいので、それよりずっと小さい値にすればしばらく待ってても統計情報の更新は走らない。

他に統計情報が更新されるトリガーとしてはこのへん？

* テーブルキャッシュが存在しない場合は統計情報がアップデートされる。
* テーブルキャッシュが足りていないと頻繁にこれが走る羽目に遭う。

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a4fa4 in dict_stats_init (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/include/dict0stats.ic:174
#2  ha_innobase::open (this=0x7f8bea635410, name=0x7f8bea634f38 "./d1/City", mode=,
    test_if_locked=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:4859
#3  0x000000000059370e in handler::ha_open (this=0x7f8bea635410, table_arg=,
    name=0x7f8bea634f38 "./d1/City", mode=2, test_if_locked=2) at /home/yoku0825/mysql-5.6.25/sql/handler.cc:2505
#4  0x0000000000766c8c in open_table_from_share (thd=0x7f8be9f45000, share=,
    alias=, db_stat=39, prgflag=, ha_open_flags=0, outparam=0x7f8bea61da00,
    is_create_table=false) at /home/yoku0825/mysql-5.6.25/sql/table.cc:2355
#5  0x000000000068e2fe in open_table (thd=0x7f8be9f45000, table_list=0x7f8bea65a210, ot_ctx=0x7f8bfb8a74d0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:3167
#6  0x0000000000690d48 in open_and_process_table (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:4662
#7  open_tables (thd=0x7f8be9f45000, start=0x7f8bfb8a7588, counter=0x7f8be9f46e38, flags=0,
    prelocking_strategy=0x7f8bfb8a75a0) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5095
#8  0x0000000000690ea8 in open_normal_and_derived_tables (thd=0x7f8be9f45000, tables=0x7f8bea65a210,
    flags=) at /home/yoku0825/mysql-5.6.25/sql/sql_base.cc:5791
#9  0x00000000006d64d3 in execute_sqlcom_select (thd=0x7f8be9f45000, all_tables=0x7f8bea65a210)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5107
#10 0x00000000006db682 in mysql_execute_command (thd=0x7f8be9f45000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#11 0x00000000006dde67 in mysql_parse (thd=0x7f8be9f45000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#12 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8be9f45000,
    packet=0x7f8bea6a6001 "SELECT * FROM City", packet_length=3932528674) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#13 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#14 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea251000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#15 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d56a0) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#16 0x00000030628079d1 in start_thread (arg=0x7f8bfb8a9700) at pthread_create.c:301
#17 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

* ANALYZE TABLE実行時はもちろん統計情報をアップデートする

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e410, flag=28, is_analyze=true)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000009a1c23 in ha_innobase::analyze (this=, thd=,
    check_opt=) at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:11186
#3  0x0000000000821cc5 in mysql_admin_table (thd=0x7f8bea738000, tables=0x0, check_opt=0x7f8bea73ab40,
    operator_name=0xbae3c6 "analyze", lock_type=TL_READ_NO_INSERT, open_for_modify=true, repair_table_use_frm=false,
    extra_open_options=0, prepare_func=0, operator_func=
    (int (handler::*)(handler *, THD *, HA_CHECK_OPT *)) 0x592970 ,
    view_operator_func=0) at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:654
#4  0x0000000000822a08 in Sql_cmd_analyze_table::execute (this=, thd=0x7f8bea738000)
    at /home/yoku0825/mysql-5.6.25/sql/sql_admin.cc:1077
#5  0x00000000006db2dc in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:4976
#6  0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#7  0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000, packet=0x7f8bea73c001 "",
    packet_length=3933540386) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#8  0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#9  0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#10 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#11 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#12 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

* innodb_stats_on_metadata= 1だとSHOW TABLE STATUSでも呼ばれる。

(gdb) bt
#0  dict_stats_update (table=0x7f8bea6413e8, stats_upd_option=DICT_STATS_FETCH_ONLY_IF_NOT_IN_MEMORY)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/dict/dict0stats.cc:3085
#1  0x00000000009a138d in ha_innobase::info_low (this=0x7f8c1a43e810, flag=212, is_analyze=false)
    at /home/yoku0825/mysql-5.6.25/storage/innobase/handler/ha_innodb.cc:10836
#2  0x00000000007125f0 in get_schema_tables_record (thd=0x7f8bea738000, tables=0x7f8bea7734d8, table=0x7f8bea472010,
    res=, db_name=, table_name=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4543
#3  0x000000000070c1ad in fill_schema_table_by_open (thd=0x7f8bea738000, is_show_fields_or_keys=false, table=0x7f8bea472010,
    schema_table=0x12496a0, orig_db_name=0x7f8bfb866210, orig_table_name=0x7f8be9f2e410,
    open_tables_state_backup=0x7f8bfb866170, can_deadlock=false) at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:3588
#4  0x00000000007181c8 in get_all_tables (thd=0x7f8bea738000, tables=, cond=0x7f8bea471eb8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:4252
#5  0x0000000000706a61 in do_fill_table (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7412
#6  get_schema_tables_result (join=0x7f8bea471a30, executed_place=PROCESSED_BY_JOIN_EXEC)
    at /home/yoku0825/mysql-5.6.25/sql/sql_show.cc:7513
#7  0x00000000006fa85d in JOIN::prepare_result (this=0x7f8bea471a30, columns_list=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:822
#8  0x00000000006b628c in JOIN::exec (this=0x7f8bea471a30) at /home/yoku0825/mysql-5.6.25/sql/sql_executor.cc:116
#9  0x00000000006fc2d8 in mysql_execute_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0,
    fields=, conds=0x0, order=, group=0x7f8bea73a590, having=0x0,
    select_options=2684619520, result=0x7f8bea471a08, unit=0x7f8bea739e48, select_lex=0x7f8bea73a490)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1100
#10 mysql_select (thd=0x7f8bea738000, tables=0x7f8bea7528f8, wild_num=0, fields=, conds=0x0,
    order=, group=0x7f8bea73a590, having=0x0, select_options=2684619520, result=0x7f8bea471a08,
    unit=0x7f8bea739e48, select_lex=0x7f8bea73a490) at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:1221
#11 0x00000000006fcbcf in handle_select (thd=0x7f8bea738000, result=0x7f8bea471a08, setup_tables_done_option=0)
    at /home/yoku0825/mysql-5.6.25/sql/sql_select.cc:110
#12 0x00000000006d6655 in execute_sqlcom_select (thd=0x7f8bea738000, all_tables=0x7f8bea7528f8)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:5134
#13 0x00000000006db682 in mysql_execute_command (thd=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:2656
#14 0x00000000006dde67 in mysql_parse (thd=0x7f8bea738000, rawbuf=, length=,
    parser_state=) at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:6386
#15 0x00000000006df7bb in dispatch_command (command=COM_QUERY, thd=0x7f8bea738000,
    packet=0x7f8bea73c001 "show table status like 'City'", packet_length=3933540397)
    at /home/yoku0825/mysql-5.6.25/sql/sql_parse.cc:1340
#16 0x00000000006a713d in do_handle_one_connection (thd_arg=)
    at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:982
#17 0x00000000006a7272 in handle_one_connection (arg=0x7f8bea738000) at /home/yoku0825/mysql-5.6.25/sql/sql_connect.cc:898
#18 0x000000000097d957 in pfs_spawn_thread (arg=0x7f8c097d5740) at /home/yoku0825/mysql-5.6.25/storage/perfschema/pfs.cc:1860
#19 0x00000030628079d1 in start_thread (arg=0x7f8bfb868700) at pthread_create.c:301
#20 0x00000030624e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115

もっと詳しく知りたい場合、前に翻訳した Yakst - InnoDBのインデックス統計情報についていくつかが役に立つかも。

新ConoHaのMariaDBを試してみた

TL;DR

* Amazon RDS for MySQL的なものを探しているなら、これじゃないです。
* 単にユーザーとして観測できる範囲で調べて推測しているだけなので、違ったらごめんなさい。

ConoHaでマネージドデータベースサービスを始めたらしいので試してみた。MariaDB 10.0系ですってよ奥様。

チュートリアル的なものは本家のものを。

データベースサーバーを使う - このべん(ConoHa)

で、これはどうやら共用サーバーのようです。
WEBコンソール中の「データベースを作成する」は

mysql> CREATE DATABASE database_name CHARSET utf8;

に、

「データベースユーザーを追加する」は

mysql> CREATE USER user_name@hostname IDENTIFIED BY 'password_string';

に、

「ユーザーがアクセスできるデータベースを設定する」は

mysql> GRANT ALL ON database_name.* TO user_name@hostname

にマッピングされているようです。

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for username@xxx.xxx.xxx.xxx                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'xxx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*E021B6BBA043401E045906A6646199BFADD9A0B1' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

こんなかんじで。

あ、共用サーバーっぽいの関係上、全世界に3306番ポート解放ってことになると思うので、ユーザー名(特に接続元ホスト指定)とパスワードはかなり重要です。たぶん。

取り敢えずデータベース権限だけでグローバル権限は一切無いので、

* レプリケーション関連の機能は一切使えない
* SET GLOBALで設定する必要がある変数は一切いじれない
* INSTALL PLUGINもちろんできない

WordPressユーザー想定してる感じなんですかね。
なので俺はたぶんお呼びでないし、それならWordPressテンプレート（というのがConoHaにはある）でいいんじゃね？って感じがするけれど。。

有料オプションでバックアップがあるんだけど、これってどうやって取ってるんだろう。XtraDBがある以上オンラインで取るならMyDumperかxbかmysqldumpか、整合性を保つためには一度スキーマの中全部をロックしてやらないといけないんだけど、その辺どうなってるのかも気になる。PITRもできるの？ MariaDBが落ちたらちゃんとDNS切り替わるの？とか、クォータもなかなか共用サーバーでやろうとすると奥が深い（information_schemaからデータを引くと誤差とか誤差じゃない何かがあったり、REVOKEやFLUSH PRIVILEDGESでは即時反映されなかったり）ので、どうやってんのかなぁとか。

権限は限られていますが、取り敢えずSHOW GLOBAL VARIABLESの中でめぼしいパラメーターを拾い読み。

*************************** 15. row ***************************
Variable_name: aria_recover
        Value: NORMAL
*************************** 16. row ***************************
Variable_name: aria_repair_threads
        Value: 1
*************************** 323. row ***************************
Variable_name: myisam_recover_options
        Value: DEFAULT
*************************** 324. row ***************************
Variable_name: myisam_repair_threads
        Value: 1

MyISAMとAriaのリペアオプションが有効になってる。

( ´-｀).oO(あれ、これクラッシュ後にリペアしてる真っ最中のロックってどうなるんだっけ。。

*************************** 25. row ***************************
Variable_name: back_log
        Value: 150

共有サーバーなので大き目なのかな。

*************************** 26. row ***************************
Variable_name: basedir
        Value: /usr
*************************** 45. row ***************************
Variable_name: character_sets_dir
        Value: /usr/share/mysql/charsets/
*************************** 52. row ***************************
Variable_name: datadir
        Value: /var/lib/mysql/

rpmでインストールしたかな？

*************************** 34. row ***************************
Variable_name: binlog_format
        Value: MIXED
*************************** 277. row ***************************
Variable_name: log_bin
        Value: ON

ふむ。一応ONだけど、もちろんREPLICATION SLAVE権限もREPLICATION CLIENT権限も使えない。内部でバックアップ取ってるのかな。

*************************** 131. row ***************************
Variable_name: innodb_buffer_pool_size
        Value: 10737418240
*************************** 142. row ***************************
Variable_name: innodb_concurrency_tickets
        Value: 5000
*************************** 144. row ***************************
Variable_name: innodb_data_file_path
        Value: ibdata1:12M:autoextend
*************************** 154. row ***************************
Variable_name: innodb_file_per_table
        Value: ON
*************************** 157. row ***************************
Variable_name: innodb_flush_method
        Value:
*************************** 175. row ***************************
Variable_name: innodb_io_capacity
        Value: 200
*************************** 176. row ***************************
Variable_name: innodb_io_capacity_max
        Value: 2000
*************************** 189. row ***************************
Variable_name: innodb_log_file_size
        Value: 268435456
*************************** 190. row ***************************
Variable_name: innodb_log_files_in_group
        Value: 2

innodb_concurrency_ticketsはデフォルトより大きくしてある。
InnoDBのコンテキストスイッチ的なものを制御する変数で、OLTPっぽい細かいI/O向けのシステムには小さく, OLAPみたいな大きくI/Oを食うシステムには大き目にするっていうのがあるんだけど、これはOLAPっぽく使うだろうと中の人が想定したんだろうか。

HDDの玉で仮想環境（かどうかは知らないけど）とかだとinnodb_flush_methodを未指定(Linux上ではfsync相当)でinnodb_io_capacityは無理にいじらないのは当たりだと思う。

*************************** 143. row ***************************
Variable_name: innodb_corrupt_table_action
        Value: assert

これはXtraDBのオプションなんだけど、吊るしのまま。

*************************** 151. row ***************************
Variable_name: innodb_file_format
        Value: Antelope

Super権限がないのでこの値は変えられないし、ということはROW_FORMAT= DynamicやCompressedはここでは使えないってことだ。。

*************************** 296. row ***************************
Variable_name: max_connect_errors
        Value: 999999999
*************************** 297. row ***************************
Variable_name: max_connections
        Value: 10000

(･∀･)ゞ

TCPポートを叩くだけ叩いてヘルスチェックにしちゃうやつだと、max_connect_errorsを大きくしておかないとそのうちホストごと接続拒否られるというアレか。
この形式だと、mysqldごと落ちてくれるやつはちゃんと切り替わるんだけど、mysqldがストールした場合って切り離してくれないことが多いのよね。

そして、max_connections= 10000が本当に押し寄せたらストールしちゃうんじゃないか感はある。

*************************** 311. row ***************************
Variable_name: max_tmp_tables
        Value: 32

何人で共有するかわからないけれど、max_connections 10000に対してちょっと少なすぎるんじゃ。。
とか思ったら、これセッション変数だった。失礼。

*************************** 312. row ***************************
Variable_name: max_user_connections
        Value: 30

max_user_connectionsが30になっているので、1ユーザーあたりの接続可能コネクションは30まで。

MySQL :: MySQL 5.6 Reference Manual :: 5.1.4 Server System Variables

$ perl -MDBI -e 'my @tmp; while (push(@tmp, DBI->connect("dbi:mysql:db_name:host_name", "user_name", "password"))) { print ++$n, "\n"; }'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DBI connect('db_name:host_name','user_name',...) failed: User user_name already has more than 'max_user_connections' active connections at -e line 1

*************************** 280. row ***************************
Variable_name: log_output
        Value: FILE
*************************** 286. row ***************************
Variable_name: log_warnings
        Value: 1
*************************** 287. row ***************************
Variable_name: long_query_time
        Value: 10.000000

*************************** 435. row ***************************
Variable_name: slow_query_log
        Value: ON

出力されてはいるぽいけど、ユーザーからアクセスする方法はなさげ。

*************************** 385. row ***************************
Variable_name: query_cache_size
        Value: 0
*************************** 387. row ***************************
Variable_name: query_cache_type
        Value: ON

クエリーキャッシュは無効化されてる。
グローバルロックに巻き込まれる心配はなかった :)

*************************** 451. row ***************************
Variable_name: ssl_ca
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-ca.crt
*************************** 452. row ***************************
Variable_name: ssl_capath
        Value:
*************************** 453. row ***************************
Variable_name: ssl_cert
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-server.crt
*************************** 454. row ***************************
Variable_name: ssl_cipher
        Value:
*************************** 455. row ***************************
Variable_name: ssl_crl
        Value:
*************************** 456. row ***************************
Variable_name: ssl_crlpath
        Value:
*************************** 457. row ***************************
Variable_name: ssl_key
        Value: /etc/my.cnf.d/ssl/ast.tyo1.database-hosting.conoha.io-server.key

一応SSL接続は有効化されてるけど、公開鍵もらえないとSSLで接続できない。。

*************************** 465. row ***************************
Variable_name: system_time_zone
        Value: UTC

( ﾟдﾟ) えっ（ホントにNOW()がUTCで返ってきた）

*************************** 467. row ***************************
Variable_name: table_open_cache
        Value: 400

( ´-｀).oO(innodb_stats_on_metadsataがOFFでも、テーブルキャッシュからあふれるとそのたび統計情報の更新がトリガーされちゃうから、このサイズキツくないかなぁ。。

*************************** 470. row ***************************
Variable_name: thread_handling
        Value: one-thread-per-connection

スレッドプール無効。
(もったいない。。)

*************************** 474. row ***************************
Variable_name: thread_pool_size
        Value: 4

thread_pool_sizeの暗黙のデフォルトはプロセッサー数なので、4vCPUってことかな。。

mysql> SELECT plugin_name, plugin_type, plugin_status FROM all_plugins WHERE plugin_status <> 'ACTIVE';
+-------------------------------+--------------------+---------------+
| plugin_name                   | plugin_type        | plugin_status |
+-------------------------------+--------------------+---------------+
| FEEDBACK                      | INFORMATION SCHEMA | DISABLED      |
| QUERY_CACHE_INFO              | INFORMATION SCHEMA | NOT INSTALLED |
| rpl_semi_sync_slave           | REPLICATION        | NOT INSTALLED |
| QUERY_RESPONSE_TIME           | INFORMATION SCHEMA | NOT INSTALLED |
| QUERY_RESPONSE_TIME_AUDIT     | AUDIT              | NOT INSTALLED |
| rpl_semi_sync_master          | REPLICATION        | NOT INSTALLED |
| Mroonga                       | STORAGE ENGINE     | NOT INSTALLED |
| Mroonga_stats                 | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB                        | STORAGE ENGINE     | NOT INSTALLED |
| TokuDB_trx                    | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_lock_waits             | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_locks                  | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_file_map               | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_fractal_tree_info      | INFORMATION SCHEMA | NOT INSTALLED |
| TokuDB_fractal_tree_block_map | INFORMATION SCHEMA | NOT INSTALLED |
| pam                           | AUTHENTICATION     | NOT INSTALLED |
| METADATA_LOCK_INFO            | INFORMATION SCHEMA | NOT INSTALLED |
| InnoDB                        | STORAGE ENGINE     | NOT INSTALLED |
| INNODB_TRX                    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_LOCKS                  | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_LOCK_WAITS             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP                    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_RESET              | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMPMEM                 | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMPMEM_RESET           | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_PER_INDEX          | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_CMP_PER_INDEX_RESET    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_PAGE            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_PAGE_LRU        | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_BUFFER_POOL_STATS      | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_METRICS                | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_DEFAULT_STOPWORD    | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_DELETED             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_BEING_DELETED       | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_CONFIG              | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_INDEX_CACHE         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_FT_INDEX_TABLE         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLES             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLESTATS         | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_INDEXES            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_COLUMNS            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FIELDS             | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FOREIGN            | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_FOREIGN_COLS       | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_TABLESPACES        | INFORMATION SCHEMA | NOT INSTALLED |
| INNODB_SYS_DATAFILES          | INFORMATION SCHEMA | NOT INSTALLED |
| LOCALES                       | INFORMATION SCHEMA | NOT INSTALLED |
| unix_socket                   | AUTHENTICATION     | NOT INSTALLED |
| handlersocket                 | DAEMON             | NOT INSTALLED |
| SERVER_AUDIT                  | AUDIT              | NOT INSTALLED |
| SPIDER                        | STORAGE ENGINE     | NOT INSTALLED |
| SPIDER_ALLOC_MEM              | INFORMATION SCHEMA | NOT INSTALLED |
| SEQUENCE                      | STORAGE ENGINE     | NOT INSTALLED |
| SQL_ERROR_LOG                 | AUDIT              | NOT INSTALLED |
| SPHINX                        | STORAGE ENGINE     | NOT INSTALLED |
+-------------------------------+--------------------+---------------+
55 rows in set (0.02 sec)

MariaDBにバンドルされているプラグインはほぼ全滅。 ~~MariaDBである意味がほとんどない。。~~
InnoDB関連ぽいのが軒並みNOT INSTALLEDになってるのは、InnoDBの代わりにXtraDBが使われているから(WHERE plugin_status= 'ACTIVE'で引くとXtraDB側のInnoDBやInnoDB関連のinformation_schemaがACTIVEになっている)

Mroongaさん有効にしてほしいなぁ。。起動したらそのままSQLで全文検索できるよとか素敵じゃない？クォータかけづらい気もするけど。

という訳で何をどう考えても、ConoHa VPSに自分でMariaDBをyumで突っ込んだ方が良さげな雰囲気がしました。まる。

と書いてから気づいたんだけど、旧ConoHaでいうところのWordPressテンプレート的なものってなんかMySQL 5.1.73になってて(旧ConoHaのはPercona Serverだったはず）なんか劣化してるんじゃ。。:(；ﾞﾟ'ωﾟ'):

2015/06/29

MySQLのHandlerレイヤーが何をしているのか探る旅 at #ChugokuDB

過日、このイベントでしゃべってきました。

MySQL・PostgreSQLユーザーグループ（MyNA・JPUG）合同DB勉強会 in 東京 - 中国地方DB勉強会 | Doorkeeper

Handlerさんコンニチワ from yoku0825

」

最初はスライドの副題の通り、主にInnoDB memcached PluginとNDB memcached Engineの違い、要は、memcachedプロトコルをしゃべるmysqldプロセスと、NDB APIをしゃべるmemcachedプロセスの違い…とかなんとかしゃべろうとしてたんですが、気が付いたら各daemon pluginがどの辺のレイヤーまで横取りしているのかを調べていました。

本当は「redisからデータを取り出すストレージエンジンがあってredisプロトコルをしゃべるdaemon pluginがあればほら！ redis-cliでredisからデータが取り出せるMySQLのできあがり！変態！」とかやりたかったんですけど、daemon pluginの壁は高かったです。残念。

このイベント、

MySQLとPostgreSQLのユーザ会の合同データベース勉強会なのに
セミナーは各データベースのNoSQL関連機能縛りという
ユニークなイベントになっています。

[mysql 16234] MyNA・JPUG合同DB勉強会 in 東京のご案内

ということだったんですけど、MySQLって昔から(少なくとも俺が本格的に触り始めたころには)HandlerSocketがあったしMySQL Clusterのmemcached(NDB memcached Engine)もあったので、InnoDB memcached Pluginも「ああ、また出たのね」って感じだしMySQL HTTP Pluginも「ああ、また出たのね」って感じだし、MySQLにとってはよくあることなんじゃないかなーと思ってます。

世に出回っているdaemon pluginの出来のよさにもちょっと驚きました。MySQL HTTP Plugin *以外は*

あと、PostgreSQLはやっぱり真面目で、JSON(B)型とそれを入出力する関数や演算子の充実を丁寧にやっていて、あー、やっぱりMySQLとは違うんだなぁという感じがしました。

@soudaiさんお疲れ様でした！

【2015/06/29 12:59】

そういえば去年こんなやり取りがあって、

ふと@yoku0825に会いに関東の勉強会に行きたいなーと思ったけど俺が行くよりそもそも講師で呼んだ方が俺のスタイルな気がした。
— そーだい＠初代ALF (@soudai1025) 2014, 10月 8

中国地方DB勉強会 in 東京なんていう不思議な勉強会で実現して初めて顔を合わせたの、感慨深い。

2015/06/25

MYSQL HTTP PLUGIN確認の補足

MySQL HTTP Plugin確認 | variable.jp [データベース,パフォーマンス,運用] を写経して失敗したので補足メモ。

2.　my.cnfをマニュアルを見て編集
myhttp_default_mysql_user_name = http_sql_user
myhttp_default_mysql_user_passwd = sql_secret
myhttp_default_mysql_user_host = 127.0.0.1

* myhttp_default_mysql_user_nameは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うユーザー名。暗黙のデフォルトは "root"
* myhttp_default_mysql_user_passwdは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときに使うパスワード。暗黙のデフォルトは ""(空文字列)

ということでこの2つは、`3. myhttpデータベースを検証用に作成しテスト用テーブルとユーザーを作成` の後ろの方でCREATE USERしたユーザー/パスワードと合わせる。

このホスト部分はcurlで指定したホスト部分に書き換わるので、↑の記事の通りcurl --url 192.168.56.x:8080 ..ならそのまま通るけど、curl --url 127.0.0.1:8080にした場合はhttp_sql_user@'192.168.56.0/255.255.255.0'に対してhttp_sql_user@127.0.0.1でログインしようとしちゃうので、curlが通らなくなる。罠い。

しかもタチの悪いことに、

$ curl --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple"

$ tail data/error.log
..
2015-06-25T07:44:56.207437Z 2 [ERROR] Invalid user

HTTPレスポンス何か返せよ！エラーメッセージ雑だよ！せめて自分がmyhttpプラグインだって名乗れよ！

あと、

* myhttp_default_mysql_user_hostは BASIC認証に成功した後に myhttpプラグインからMySQLに接続しに行くときの…ホスト…。

* ってことは、myhttpプラグインがインストールされているマシンじゃないMySQLにもつなぎにいけるってことか…この変態め…。
* と思ったけど、127.0.0.1以外の何かに書き換えてもダメ。どうやら、curlでリクエストしたホスト名に置き換えているぽい。

$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://172.17.0.2:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf80005780, user=0x2eeb148 "http_sql_user",
    host=0x7fdf800055a8 "172.17.0.2", db=0x7fdf80005668 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));


$ curl -s --user basic_auth_user:basic_auth_passwd --url "http://127.0.0.1:8080/sql/myhttp/SELECT+%2A+FROM+simple" | jq .

Breakpoint 1, my_da_sql_init_security_ctx (thd=0x7fdf780040e0, user=0x2eeb148 "http_sql_user",
    host=0x7fdf7800e378 "127.0.0.1", db=0x7fdf78001988 "myhttp")
    at /export/home/pb2/build/sb_0-13293843-1411506406.69/mysql-5.7.5-labs-http/sql/sql_da_sql.cc:146
146         char * user_copy= my_strdup(key_memory_Security_context, user, MYF(MY_WME));

一応ホスト名とか受け取れるけど、ロードバランサーとかの下に入れるとどういう動きするんでしょうねこれ。。

curl --userで指定するBASIC認証部分のユーザー名とパスワードはそれぞれ

* myhttp_basic_auth_user_name .. 暗黙のデフォルト "basic_auth_user"
* myhttp_basic_auth_user_passwd .. 暗黙のデフォルト "basic_auth_passwd"

地雷ヤーズのためのメモでした。

2015/06/19

現代のMySQLはもう不要にINをEXISTSに書き換えない

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。

この記事は 2009/3/25 に書かれたもののようである。
2009年3月といえばMySQL 5.1がGAになってわずか半年、MySQL 6.0.10-alphaがリリースされた頃で、MariaDBもまだ姿を見せていない頃だ。

時は流れて2015年、MySQL 5.6がGAになって早2年半、5.7のGAﾏﾀﾞｧ-? (･∀･ )っ/凵⌒☆ﾁﾝﾁﾝ

な頃なので、もういい加減誰か言ってくれてもいいんじゃないかと思う。

もうMySQL(5.6)は不要にINをEXISTSに書き換えたりしないんだよって

mysql51> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  222 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    9 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.00 sec)


mysql55> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
| id | select_type        | table   | type           | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
|  1 | PRIMARY            | Country | ALL            | NULL          | NULL        | NULL    | NULL |  226 | Using where |
|  2 | DEPENDENT SUBQUERY | City    | index_subquery | CountryCode   | CountryCode | 3       | func |    7 | Using where |
+----+--------------------+---------+----------------+---------------+-------------+---------+------+------+-------------+
2 rows in set (0.02 sec)


mysql56> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
|  1 | SIMPLE      | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 | Using where                      |
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    9 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql57> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | SIMPLE      | Country | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  239 |    14.29 | Using where                      |
|  1 | SIMPLE      | City    | NULL       | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |   18 |    33.33 | Using where; FirstMatch(Country) |
+----+-------------+---------+------------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

mysql57> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

クエリーは先頭にリンクを張った、御大のブログ記事のものと一緒。world_innodb.sqlを流してから、3つのテーブルに対してANALYZE TABLEをかけてある。5.1.73, 5.5.44, 5.6.25, 5.7.7の比較だ。

最後の5.7のワーニングは、5.7のデフォルトのEXPLAINがEXPLAIN EXTENDEDになったので、オプティマイズした結果をワーニングバッファに突っ込んでいる。SHOW WARNINGSでオプティマイザーがどうクエリーを書き換えたかが見えるのでよく見ると。

select .. from `d1`.`Country` semi join (`d1`.`City`) where ..

semi-join最適化は5.6.5からの実装なので、MySQL 5.6のGAバージョンならこれは5.6でも同じように最適化できる。
MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.18 Subquery Optimization

もちろんすべてのINが良しなにされるとは限らない（本当に相関サブクエリーが必要なケースは相関サブクエリーになるし、5.7になっても相関サブクエリーは遅い）が、MySQLのオプティマイザーがバカなせいでつらい目を見た過去の俺とか俺とか俺とかにはとても嬉しい。

日本よ、これがMySQL 5.6だッ！！ (c) nippondanji

ちなみに、

MariaDB [d1]> SELECT @@version;
+----------------+
| @@version      |
+----------------+
| 5.3.12-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [d1]> EXPLAIN EXTENDED SELECT * FROM Country WHERE Continent = 'Asia' AND Code IN
    -> (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref             | rows | filtered | Extra                            |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
|  1 | PRIMARY     | Country | ALL  | PRIMARY       | NULL        | NULL    | NULL            |  269 |   100.00 | Using where                      |
|  1 | PRIMARY     | City    | ref  | CountryCode   | CountryCode | 3       | d1.Country.Code |    1 |   100.00 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+---------------+-------------+---------+-----------------+------+----------+----------------------------------+
2 rows in set, 2 warnings (0.00 sec)

MariaDB [d1]> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 'd1.Country.Population' of SELECT #2 was resolved in SELECT #1
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: select `d1`.`Country`.`Code` AS `Code`,`d1`.`Country`.`Name` AS `Name`,`d1`.`Country`.`Continent` AS `Continent`,`d1`.`Country`.`Region` AS `Region`,`d1`.`Country`.`SurfaceArea` AS `SurfaceArea`,`d1`.`Country`.`IndepYear` AS `IndepYear`,`d1`.`Country`.`Population` AS `Population`,`d1`.`Country`.`LifeExpectancy` AS `LifeExpectancy`,`d1`.`Country`.`GNP` AS `GNP`,`d1`.`Country`.`GNPOld` AS `GNPOld`,`d1`.`Country`.`LocalName` AS `LocalName`,`d1`.`Country`.`GovernmentForm` AS `GovernmentForm`,`d1`.`Country`.`HeadOfState` AS `HeadOfState`,`d1`.`Country`.`Capital` AS `Capital`,`d1`.`Country`.`Code2` AS `Code2` from `d1`.`Country` semi join (`d1`.`City`) where ((`d1`.`City`.`CountryCode` = `d1`.`Country`.`Code`) and (`d1`.`Country`.`Continent` = 'Asia') and (`d1`.`City`.`Population` > (`d1`.`Country`.`Population` / 2)))
2 rows in set (0.00 sec)

MariaDBはもっと前からできるんだけどな！ :)
（5.3で試してるけど、もとは5.2くらいだったような気がする。ただし裏は取ってない）

ところでsemi joinって聞くたびに蝉ジョインとか脳内変換されるんだけどこれなんとかなりませんか。

登録: 投稿 ( Atom )