かみぽわーる

kamipo's blog

InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い

この投稿はMySQL Casual Advent Calendar 2014の5日目の記事です。

先週ツイッターInnoDBのことを質問されまして、せっかくなのでアドカレのネタにしようと思いますってことでInnoDBのファイルフォーマット毎の違いをカジュアルに説明しようと思います。

InnoDBのファイルフォーマットBarracudaと新機能

InnoDBにはファイルフォーマットとして昔からあるAntelopeと新しいフォーマット(5年も前からあるので新しくはないが)のBarracudaがあって、新しいフォーマットのBarracudaにはいろいろ改良や新機能が追加されています。

例えば、ROW_FORMAT=COMPRESSEDとかは分かりやすい新機能で、上のCOMPRESSEDではなくDYNAMICを選んでいる理由を質問されたときには、とりいそぎ圧縮展開するため最大スループットが落ちますという回答をしたんですけど、COMPRESSEDの性能については@さんのエントリが参考になります。

他には、innodb_large_prefixによるインデックスサイズ制限の拡張もBarracudaの新機能です。

もうひとつ、見た目地味だけどドキュメントには書いてある大きな違いがあって、それは可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いについてです。

可変長カラム(VARBINARY, VARCHAR, BLOB, TEXT)の扱いの違い

ざっくりいうと、従来のAntelopeでは可変長カラムの値は先頭768バイトはローカルページに保存して残りの部分を外部のオーバーフローページに保存するけど、Barracudaは可変長カラムの値をすべて外部のページに保存してローカルページにはそのページへの20バイトのポインタだけ保存します。

これは実際どういう効果が期待できるかという話なんですけど、サイズの大きい可変長カラムをクラスタインデックスから追い出して行のサイズを小さく保てるということは、ページあたりより多くの行を詰め込めるようになるということで、バッファプールにより多くの行を乗せられるようになることを期待できると思います。

また、InnoDBには1つの行の最大サイズはページサイズの半分(innodb_page_sizeのデフォルトは16KBなので約8000バイト)までという制限があるので、Antelopeだと例えばutf8でvarchar(255)のカラムが11個あるテーブルに最大サイズまでデータを入れようとするとエラーになるんですけど、Barracudaだとローカルページにはポインタしか保持してないので余裕で保存できたりします。

CREATE TABLE tbl_compact (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=COMPACT;

CREATE TABLE tbl_dynamic (
    col01 varchar(255),
    col02 varchar(255),
    col03 varchar(255),
    col04 varchar(255),
    col05 varchar(255),
    col06 varchar(255),
    col07 varchar(255),
    col08 varchar(255),
    col09 varchar(255),
    col10 varchar(255),
    col11 varchar(255)
) CHARACTER SET utf8 ROW_FORMAT=DYNAMIC;
# error
$ perl -le 'print "INSERT INTO tbl_compact VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
ERROR 1118 (42000) at line 1: 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.
Error (Code 1118): 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.
Error (Code 1030): Got error 139 from storage engine

# ok
$ perl -le 'print "INSERT INTO tbl_dynamic VALUES (",join(q{,},map{q{"}.("あ"x255).q{"}}1..11),")"' | mysql -u root test
参考

Rails複数DB Casual TalksでMySQLとActiveRecordの話をしてきた

Rails複数DB Casual Talks - connpass お疲れさまでした。

当日は準備不足で資料がただのリンク集だったのを公開用に当社比800%ぐらい加筆したので、当日参加できなかった人が見ても内容がわかるようになってると思います。

MySQLとActiveRecord

@さんのツイートふぁぼってたらみんなが複数DBの話するなかでひとりだけMySQLの話するとか公開処刑なのでは!?と思ったけど、ちょうどみんなestablish_connectionの話でおなかいっぱいでしたからね、楽しんでもらえたようでよかったです。

Rails 複数DB Casual Talks #mdb_casual - Togetterまとめ

MySQLユーザーのためのPostgreSQL対応表

毎回わからなくなってググってるから今度からここに追記していく。

MySQL PostgreSQL
show databases; \l
use dbname \c dbname
show tables; \dt
show table status from dbname; わからん
show create table [dbname.]tblname; わからん
show full columns from [dbname.]tblname; \d+ tblname もしくは↓で代替
SELECT * FROM information_schema.columns WHERE table_schema = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position; SELECT * FROM information_schema.columns WHERE table_catalog = 'dbname' AND table_name = 'tblname' ORDER BY ordinal_position;

ISUCON4予選に参加してきた

ISUCON4予選お疲れさまでした。

すこし時間が経ってしまったけど、当日うまくいかなかったことの復習をしたので備忘としてここに記します。

今回のチームメンバーは@さんと@ちゃんでした。ギリギリのオファーにも関わらず一緒に参加してくれてありがとう!

チームメンバーの参加エントリはコチラ

当日うまくいかなかったこと

役割分担で僕が目指していたのは、セットアップや開発基盤をすばやく整えて、負荷やアクセスログを分析して根拠をもってなにをすべきかを明らかにすることで、メンバーそれぞれが力を発揮して問題に取り組めるようにできればいいなと思ってた。いわゆるファシリテータというやつなんですかね。

結果からいって自己評価は、そのほとんどがうまくいかなかったと思っていて、その部分がとても心苦しかったです。

うまくいかなかったと思うことが大きく2つあって

ひとつが、isuconユーザのパーミッション問題にハマって(結局終わってみるまでパーミッションの問題だとすら気づけなかった)、それ絡みかどうか解決できてないのでわからないけど、僕がサーバ側で git push できない状態を解決できなかったので Initial commit の git push に30分以上ぐらい掛かってしまったこと(ローカルに rsync してきて git push した)。

もうひとつが、詳細はやっぽさんのエントリ参照ですが、//mypageはアプリ通さずnginx側で返せるようにできるよねってことで、じゃあちょっとnginxの様子見てくる!って張り切って出ていったきり、それを実現できずにずっとハマってたこと。

それで、アクセス数多いからせめて/だけでも静的に返そうってことで全員で nginx.conf いじりながら/で静的ファイル返す方法を模索することになってしまった。

最終的にやっぽさんが気合いで解決してくれて、その状態で提出となりました。

/を静的に返せるようになったアクセスログを集計したのが以下。

*** HTTP requests total: 202764 ***
 
*** HTTP requests stats order by time ***
 
count: 18433, total: 188.261999999996, mean: 0.0102133130798023
path: /login
 
count: 3483, total: 8.70599999999991, mean: 0.00249956933677861
path: /mypage
 
count: 1, total: 0.397, mean: 0.397
path: /report
 
count: 36866, total: 0, mean: 0
path: /images/isucon-bank.png
 
count: 36866, total: 0, mean: 0
path: /stylesheets/bootstrap.min.css
 
count: 36866, total: 0, mean: 0
path: /stylesheets/bootflat.min.css
 
count: 36866, total: 0, mean: 0
path: /stylesheets/isucon-bank.css
 
count: 18433, total: 0, mean: 0
path: /
 
count: 11654, total: 0, mean: 0
path: /?error=not_found
 
count: 2091, total: 0, mean: 0
path: /?error=banned
 
count: 1205, total: 0, mean: 0
path: /?error=locked
 
*** HTTP methods stats ***
GET:  184331
POST: 18433
 
*** HTTP statuses stats ***
200: 184331
302: 18433

/を1ms未満で返せるようになったけどスコアはそんなに伸びなかったので、/loginが10msぐらいなのを根本的に解決しないと上位は難しそう、という集計結果を出すことができなかった。

/loginがもっと速くないとダメだってことなら、じゃあこれ以上速くするならusersは不変なマスターデータだから起動時にプロセスのメモリに持とうとか、tsvから読み込んだら生パスワード分かってるからpassword_hash計算しなくていいよねとか、そういうことに根拠をもって取り掛かれたはず。

ハマって時間に余裕ない状態じゃなければ、やっぽさんはbenchmarkerのバイナリ見て、このDOMチェックしてるから人間が見たときの見た目変えずに最小のDOM返せばよくね?って言ってて、いや、それってありなん…みたいなのも余裕があったら試してみることもできたと思う。

ただ今回は、予選特有の環境における攻略法が知られていたからか、それだけではボーダーを超えるのが難しい予選だったので結果はどうなったか分からなかったわけですが、それでもやりたいことはやりきったよねと思える状態まで行きたかったなというのが正直なところ。

やっぽさんが気合いで解決してくれたことでなにでハマってたか分かったので、復習してみて//mypageをnginx側で返すことができた。/のときと同様/mypageも1ms未満で返せるようになったが、スコアの伸びは1割程度だった。どうすればよかったかのミニマムな設定を以下に示す。

http {
    upstream app {
        server 127.0.0.1:8080;
    }

    map $arg_error $index {
        default   index.html;
        locked    err_locked.html;
        banned    err_banned.html;
        not_found err_not_found.html;
        not_login err_not_login.html;
    }

    server {
        root /home/isucon/webapp/public;
        index $index;

        location /mypage {
            rewrite /mypage /mypage.html;
        }

        location /mypage.html {
            ssi on;
            set $login   $cookie_login;
            set $last_ip $cookie_last_ip;
            set $last_at $cookie_last_at;
            # 2014-10-02 03:51:08 <- 2014-10-02%2003%3A51%3A08
            if ( $last_at ~ (.*)%20(.*) ) {
                set $last_at "$1 $2";
            }
            if ( $last_at ~ (.*)\s(.*)%3A(.*)%3A(.*) ) {
                set $last_at "$1 $2:$3:$4";
            }
        }

        location ~ ^/(login|report) {
            proxy_pass http://app;
        }
    }
}

ログイン情報を、クエリストリングに入れて渡すとスペースが%20に、cookieに入れて渡すと加えて:%3Aエンコードされてくるのをデコードする方法が分からなかったのでそこだけがんばったけど、それ以外は仕組みが分かればなんということはなかった。

ハマってたのは location のマッチに関する挙動をちゃんと理解していなくて、ちょっとした書き方の差でうまく動いてないのが、全部location /に吸われていってるせいだということに全然気づけてなかった。

まずassets系をnginxで返すのに

        location ^~ /(stylesheets|images) {
            root /home/isucon/webapp/public;
        }

        location / {
            proxy_pass http://app;
        }

と書いてたけど、これが全部location /にマッチして静的ファイルがちゃんと返せてないのに気づいて試行錯誤して

        location /images {
            alias /home/isucon/webapp/public/images;
        }

        location /stylesheets {
            alias /home/isucon/webapp/public/stylesheets;
        }

こう書いてなんとか返せるようになったけど、^~を使ってたのがよくなくて

        location ~ ^/(stylesheets|images) {
            root /home/isucon/webapp/public;
        }

こうすればマッチした。

あと/をエラーメッセージで分岐して静的ファイルを返すのに

        map $arg_error $index {
            default   index.html;
            locked    err_locked.html;
            banned    err_banned.html;
            not_found err_not_found.html;
            not_login err_not_login.html;
        }

        location = / {
            root /home/isucon/webapp/public;
            index $index;
        }

        location / {
            proxy_pass http://app;
        }

という感じに書いていたけど、これはindexディレクティブによってrewriteされた結果location /にマッチするらしく、これも期待した動作をしなかった。

SSIを試そうとしたときも、最終的にlocation /にマッチしてしまってバックエンドが静的ファイルを返してしまっていたからSSIのタグが置換されずにそのまま出てしまっていた。

もし一度でもlocation /を消したミニマムなケースで試していたらすぐに気づけたかもしれないけど、そもそも基本的なことを理解していなかったことが要因なので実に不甲斐なかった。

おわりに

ダメだったとこ復習して反省したらだいぶスッキリしたので、今度チームでおいしい日本酒飲みに行きましょう!

YAPC::Asia 2014に行ってきた

#yapcramen

OSSにも貢献しました!

あと最近声優ソムリエ業で忙しい@さんに30日ぐらい放置されてたpullreqもYAPC::Asiaのおかげでマージされました!

───、以下感想。

今年はYAPC::Asiaの週の頭から体調くずしてて昼から参加だったので、あんまりトーク聞かずにイベントホールとかHUBでまったりしてた。

どの部屋も満席立ち見だったようでそれはそれでよかったんですが、聞きたいトークが聞けなかったりずっと立ち見で不満を言ってる人とかもいて、なんというかワインみたいなもんで良作の年もあり不作の年もありそれ含めて楽しんで参加できたらいいなと僕は思ってるんですが、たぶん今回はじめて参加してそれが不満だった人はもう次からは参加してくれなくなるのかなって思うとちょっと残念だけど仕方ないのやもしれぬ。

トークを聞かずにHUBでは@さん、@さん、@さんが飲んでるテーブルにお邪魔してActiveRecordの運用まわり

聞いてた。

あとはMySQLの話で

あとなんかいろいろ話せて有意義だった。

あと今回はリクルートホールディングス様の粋な計らいにより夕方からHUB貸し切りのフリードリンクだったおかげで、終電までずっと飲んでだべってられたのがすごいよかった。

去年は協生館と学食以外あんま出歩かなかったからあまり日吉を堪能してなかったけど、今年はYAPC::Asia Ramen Challengeのおかげで日吉のラーメンも堪能できて@++でした。

今年のYAPC::Asiaも楽しかった。

また次回のYAPC::Asiaも楽しもうと思う。

٩(๑❛ᴗ❛๑)۶

プライマリキーを使った1:1関連のテーブル分割で自動採番をしないようにする

プライマリキーを使った1:1関連でカラム数の多いテーブルを分割する - Hidden in Plain Sight

プチ・デザインパターン的なやつ、僕もよくやってます。

で、運用エンジニア的にはデータの不整合を起こしうる要因はできる限りDB側の制約でも防ぎたいので、このusersとprofilesの場合だと、usersで自動採番されたidをprofilesでも使うのでprofilesの自動採番する機能は残しておくと事故るリスクがあるので落としたいわけです。

なので、僕はいつもこんな感じでmigrationを書いてます。

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t| 
      t.string :email, charset: 'ascii', collation: 'ascii_bin', null: false
      t.string :password_digest, charset: 'ascii', collation: 'ascii_bin'
    end

    add_index :users, :email, name: "idx_email", unique: true
  end
end

class CreateProfiles < ActiveRecord::Migration                                                                                              
  def change                                                                                                                                
    create_table :profiles, id: false do |t|                                                                                                
      t.integer  :id, null: false
      t.string   :name
      t.integer  :gender, limit: 1                                                                                                          
      t.datetime :birthday                                                                                                                  
    end

    execute("ALTER TABLE `profiles` ADD PRIMARY KEY (`id`)")                                                                                
  end
end

usersテーブルの文字セットや照合順序の指定はRails - ActiveRecordでカラム毎にcharsetとcollationを指定する - Qiitaでやってます。

ActiveRecordが作る自動採番されるプライマリキーとは異なる定義を使うとrake db:schema:loadスキーマが正確に復元できなくなるので config/application.rbスキーマのフォーマットをSQLに変更しています。

config.active_record.schema_format = :sql

db/structure.sqlにこんな感じでダンプされます。

-- (snip)

--
-- Table structure for table `profiles`
--

DROP TABLE IF EXISTS `profiles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `profiles` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `schema_migrations`
--

DROP TABLE IF EXISTS `schema_migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `schema_migrations` (
  `version` varchar(191) NOT NULL,
  UNIQUE KEY `unique_schema_migrations` (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `password_digest` varchar(255) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

-- (snip)

これでうっかりミスってprofilesにINSERTして採番されちゃうリスクが軽減されて安心感が増しますね!

InnoDBのロックの範囲とネクストキーロックの話

この記事はMySQL Casual Advent Calendar 2013 3日目の記事です。

はじめに

以前にSELECT ... FOR UPDATEとロックの挙動 - I sort my thought...の記事にTwitterで少し言及したんですが、それの補足というか、InnoDBのロックの範囲について僕はこう理解していますよという話です。

MySQLといえば、InnoDBをネットワークサーバとして使うためのフレームワークであり、SQLInnoDBのインデックスにアクセスするためのDSLといっても過言ではないでしょう。

InnoDBのロックとはつまるところインデックス行のロックなので、InnoDBのロックの範囲を理解するためにInnoDBのインデックスについて少し前置きしておきます(だいぶ端折ったけど長くなった…)。

クラスタインデックスとセカンダリインデックス

すでにInnoDBのインデックスについてよく知ってるという人はここは飛ばして大丈夫です!
ちょっと予習しておきたい人は以下の記事を参考にしてください。

今回の説明に必要そうなところを要約すると

  • 主キーがあるテーブルは主キーがクラスタインデックスになる
  • セカンダリインデックスのリーフにはクラスタインデックスのキー値が保存される
  • セカンダリインデックスからデータを参照するには保存されてるキー値からクラスタインデックスを参照する

こんなところ。

ロックの範囲の種類

MySQL :: MySQL 5.6 Reference Manual :: 14.2.2.6 InnoDB Record, Gap, and Next-Key Locksから引用すると、InnoDBには3種類のロックがあります。

  • Record lock: This is a lock on an index record.

  • Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

  • Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.

ざっくりいうと

  • レコードロック: 単一インデックスレコードのロック
  • ギャップロック: インデックスレコード間のギャップのロック、先頭のインデックスレコードの前や末尾のインデックスレコードの後のギャップのロック
  • ネクストキーロック: レコードロックとそのレコードの直前のギャップロックの組み合わせ

という感じになります。

InnoDBでインデックスを走査するとき、遭遇したインデックスレコードをロックしていく。なのでロックする範囲を知るにはどのインデックスをどの値からどっちに(ASCかDESCか)どこまで走査するかが重要になる。

あとInnoDBのデフォルトの分離レベル(REPEATABLE-READ)ではロックの単位はネクストキーロックが基本で、あるインデックスに値 10、11、13、20 が含まれているときのネクストキーロックの区間は以下のような感じで、基本的にこの区間単位でロックすることになる。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

ネクストキーロック

ところで、ネクストキーロックというとsh2さんのMySQL InnoDBのネクストキーロック おさらい - SH2の日記の記事が有名ですよね。この、ひとつ先のインデックスレコードまでロックするのもネクストキーロックと呼ぶし、レコードロックとその直前のギャップロックの組み合わせもネクストキーロックと書いてるし、議論するときにはどちらの意味で使ってるのか文脈読み取れる社会性が必要そうです(今回はレコードロックとその直前のギャップロックの組み合わせの意味で使います)。

ロックの範囲を確認する

長くなったけどこのぐらい押さえておけば大丈夫だと思います!

トランザクションがブロックする、ブロックしないというのは、つまるところロックの範囲が被ったか被ってないかなので、walf443さんの記事にあるのと同じ感じのテーブルでロックの範囲を確認してみます。

CREATE TABLE `mysqlcasual` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) NOT NULL,
  `col2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `mysqlcasual`(`col1`) VALUES (2),(4),(6),(8),(10),(12),(14),(16);

SELECT * FROM mysqlcasual;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    2 |    0 |
|  2 |    4 |    0 |
|  3 |    6 |    0 |
|  4 |    8 |    0 |
|  5 |   10 |    0 |
|  6 |   12 |    0 |
|  7 |   14 |    0 |
|  8 |   16 |    0 |
+----+------+------+

ロックの範囲を確認するにはinnodb_lock_monitorを有効にするとSHOW ENGINE INNODB STATUSTRANSACTIONSセクションに詳細なロック情報が出力されるようになります。

CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;

これでロックの範囲が確認できる、予定だったんですがちょっと思ってた通りの出力にならないんで簡単な例だけやりたいと思います!*1

BEGIN;
SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;

これでTRANSACTIONSセクションを見ると

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028789 lock mode S locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000003; asc     ;;

わかるところだけ読むとlock mode Sってのが共有ロック(でネクストキーロック)、locks gap before recがギャップロックなのでロックの範囲はidx_col1上の

(2, 4]
(4, 6)

の範囲でidx_col1しか読んでないのでカバリングインデックスになってるってやつです。 クエリを以下に変えてインデックスに含まれないカラムを加えると

BEGIN;
SELECT id, col1, col2 FROM mysqlcasual WHERE col1 = 4 LOCK IN SHARE MODE;

TRANSACTIONSセクションに以下のインデックスレコードも加わります。

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028790 lock mode S locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

locks rec but not gapってのがレコードロックです。
col2の値を取得するためにクラスタインデックスにアクセスしてしまってカバリングインデックスが効いてないのがわかります。

また、定義してない謎のフィールドがあるように見えますがInnoDBの行の構造についてはMySQL :: MySQL 5.6 Reference Manual :: 14.2.2.13 InnoDB Table and Index Structuresに書いてあるので気になる人はそっち見てください。

次は排他ロックの場合。

BEGIN;
SELECT id, col1 FROM mysqlcasual WHERE col1 = 4 FOR UPDATE;

更新するつもり(lock_mode X)のときはカバリングインデックスになっていてもクラスタインデックスもレコードロックします。

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028791 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;;
 1: len 4; hex 80000003; asc     ;;

これらは分離レベルREPEATABLE-READのときの挙動で、READ-COMMITTEDにするとギャップロックが無効になります。

RECORD LOCKS space id 7423 page no 4 n bits 72 index `idx_col1` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 7423 page no 3 n bits 72 index `PRIMARY` of table `test`.`mysqlcasual` trx id 1028792 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000fb273; asc      s;;
 2: len 7; hex dd000001ec011d; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000000; asc     ;;

この状態だとギャップに対してINSERTできるのでファントムリードが起きる状態になります。

さいごに

innodb_lock_monitorは大量にログを吐くので使い終わったら消しておきましょう。

DROP TABLE innodb_lock_monitor;

明日(12/4)はdo_aki@殿堂入りさんです!

*1:レコードが少なすぎたせいでした https://twitter.com/kamipo/status/408032716186071040 https://twitter.com/kamipo/status/408033740690968576