Web サービスの完全 HTTPS 化

インフラストラクチャー部長の星 (@kani_b) です。

2017年1月5日をもって、クックパッド における全ページで HTTPS が使われるようになりました。 完全 HTTPS 化をするにあたり、その理由や具体的な進め方について紹介します。 以前 SRE Tech Talks #2 にて一部発表した内容も含みますので、ご興味のある方はあわせてスライドもご覧ください。

完全 HTTPS 化に踏み切った理由

以前のクックパッドは、ログインや登録情報の参照など、いわゆる個人情報や認証情報を扱う箇所のみに HTTPS が使われていました。 このように「必要な箇所にのみ HTTPS を使う」構成は、ある程度歴史のある Web サービスにおいてよく使われている構成です。 この状態から、完全 HTTPS 化に踏み切った理由を説明します。

サービスをよりセキュアにするため

HTTPS の利用を考えるにあたりまず思い浮かぶ利点は、「通信を暗号化できる」そして「通信先を認証できる」ことでしょう。前述の通り、これまでは機密性の高い情報を扱う箇所のみで HTTPS を利用してきました。これだけでも、守りたい情報が簡単に窃取できてしまう状態は避けることができます。しかし、現代は公衆無線 LAN などオープンなネットワークがかなり普及しており、また国家レベルでの盗聴なども明らかになってきています。

「レシピサイトくらいで大げさな」と思われるかもしれませんが、食事は人間の生活と密接に紐付いており、思わぬ情報を得られる場合があります。例えばクックパッドはレシピの検索機能を提供しています。この機能にはこれまで HTTP が使われてきました。しかし、実際の検索動向を見てみると、例えば「ダイエット」であったり、特定の病気 (糖尿病など) に適したレシピなど、プライバシに大きく関わるキーワードが含まれることがあります。

こう見ると、「では検索は HTTPS にしたほうが良い」という気持ちが働きます。しかし、検索だけではなく、他の機能についても同様のことが考えられます。「その機能が実際どのように使われるのか」を完全に想定することは困難です。よって、全ての通信が暗号化されている状態をまず前提とすることにしました。もちろん、HTTPS の上でやり取りされる情報がどのように扱われるか、はまた別の話であり、アプリケーションレイヤにおいてどのように情報を扱うかは今後も考慮していく必要があります。

プラットフォームの進化

iOS の App Transport Security (ATS) 対応必須化などをはじめ、プラットフォーム側で通信を HTTPS にする流れも進んでいます。現在完全 HTTPS 化が進められている主な要因はここにあるのではないかと思います。Chrome においても近年は HTTPS 絡みの変更が盛んです。Chrome 56 でリリースされた、 HTTP ページにログインフォームが表示されている場合に "安全でないページ" という警告を出す機能は、多くの HTTP ページでログイン用のモーダルを表示していたクックパッドにも影響を及ぼす変更でした。

また、検索エンジン側の変更も要因の一つです。Google は検索ランキングにおいて HTTPS の利用有無をランキングアルゴリズムに利用することを発表しています。また多くの検索エンジンが検索画面そのものを HTTPS 化しており、 HTTPS 化なしに自社サービスへの流入などを正確に計測することは難しくなります。こうした外部のプラットフォームが HTTPS 化へと舵を切ってきたことも、移行理由の一つになりました。

開発のやりにくさ

完全 HTTPS 化をしていないサービスの多くで、ログインフォームや登録情報の参照など一部の画面のみが HTTPS 化されています。クックパッドでも長い間同様に HTTPS が使われている画面を使い分けていましたが、開発者が「この画面には HTTPS が必要かどうか」を判断して使い分けていたため、本来 HTTPS であるべき画面がそうでない、などの事故が起きうる状況でした。また、HTTPS 画面で提供されているエンドポイントにアクセスするために CORS に対応する必要があるなど、普段の開発にも影響が及んでいました。

新技術への対応

HTTPS は、現在出てきている新しい技術の必須要件とされることも多くなりました。例えば HTTP/2 はその代表格 (正確には HTTP/2 自体が HTTPS を要求しているわけではないが、インターネットサービスにデプロイするためにはほぼ必須) でしょう。他にも、ServiceWorker や Web Push, iOS の Shared Web Credentials などは HTTPS が利用されていることが要件となっています。 こうした新しい技術を活かしていくためにも、完全 HTTPS 化は必要でした。

以上が、クックパッドを完全 HTTPS 化するに至った理由です。Web サービスにおいて、もはや完全 HTTPS 化をしないポジティブな理由はないと考えます。

完全 HTTPS 化までの道のり

完全 HTTPS 化する理由がまとまったところで、次に実際どのように HTTPS 化したか、具体的な進め方について説明します。完全 HTTPS 化は、概ね以下のように進めました。

1. HTTPS テスト環境を作成する
2. mixed content をなくす
3. 段階的リリース
4. 全体リリース

完全 HTTPS 化は社内のアプリケーション、検索エンジンはもちろんのこと、提携他社からのアクセス、メディア媒体掲載時に利用される URL などあらゆる範囲に影響するため、社内での宣言は早めに行いました。結果として、多くの部署の協力を得ながら完全 HTTPS 化を進めることができました。

HTTPS テスト環境の作成

まずは HTTPS になった際のアプリケーションをテストできる環境をつくる必要があります。 クックパッドでは、Rails の HTTPS 必須化スイッチである force_ssl を利用し、特別な Cookie をリクエストに差し込んだ場合に force_ssl が有効化された専用のアプリケーションサーバにルーティングされるようにしていました。こちらは、段階的リリースのタイミングでは内部アクセスや Cookie の扱いに不都合があったため、リバースプロキシでリダイレクトする形に変更しています。

mixed content をなくす

mixed content とは、HTTPS の中に HTTP のリソースが含まれることです。多くのブラウザは、mixed content になっているリソースはロードしない、あるいは動作させないような制約を持っているため、完全 HTTPS 化にあたり mixed content をなくすことは必須です。最も大変なのがこの対応だと思います。

アプリケーションや CSS の中に埋まっている HTTP URL を探しだし、HTTPS に修正します。クックパッドにおいても、いくつか HTTP URL が記載されてしまっているケースがありました。 こういった URL は、 protocol-relative URL (//: ではじまる URL) に置き換えたり、アプリケーション側でリクエストプロトコルを見て URL を生成するように修正します。

大きなコードベースにおいて、この作業は根気の要るものです。また、この作業をしている間もサービスは開発され続けているため、終わりがありません。また、mixed content はコードそのもの以外 (ユーザの持つデータなど) に起因することも多いため、実際に本番に出してテストしていくことが大きな助けになります。そのため今回は、いくつかの主要機能を定めておき、それらに mixed content が発生しない段階で次のステップに進むことにしました。

ネットワーク広告の HTTPS 対応

ネットワーク広告は、その仕組み上実際に配信される広告クリエイティブが HTTPS を利用しているかが非常に重要です。クックパッドが完全 HTTPS 化に着手した2015年8月頃は、まだ多くの事業者が HTTPS に対応していない、あるいは対応を保証できない状態でした。つまり、ネットワーク広告を配信すると、mixed content が起きてしまう可能性が高かったのです。

しかし、ATS がリリースされたことによるものか、現在では多くの事業者が HTTPS 対応を進めています。クックパッドでも一部ネットワーク広告が利用されているため、2015年の着手時にはこれが原因となり一度ペンディングとなったものの、流れが変わったことで再び進めることができるようになりました。このあたりの事情は @suzu_v さんのスライドに非常にわかりやすくまとまっています。

完全 HTTPS 化完了後、ネットワーク広告の売上について事業部と確認も行いましたが、特に影響はありませんでした。

段階的リリース

完全 HTTPS 化による影響を確認するため、段階的なリリースを行いました。 リバースプロキシのレイヤで、特定の Cookie を用いて全ユーザのうち数%が完全 HTTPS 化されたアプリケーションサーバにアクセスするようにします。 アプリケーション側のエラーやパフォーマンスをトラックしつつ、ユーザからのご意見やお問い合わせをユーザサポート部門と連携しながら確認します。 結果として、いくつかのリダイレクトミスと不具合を見つけ、修正しました。

CSP Report の活用

今回の移行では利用できなかったのですが、Content Security Policy (CSP) の機能を使うことで、より効率的に mixed content の情報を集めることができます。CSP のディレクティブとして block-all-mixed-content ディレクティブがあり、これを指定するとブラウザは mixed content を一切読み込みません。また、CSP の機能として、ポリシ違反が発生した際に指定した URL にレポートを送出する機能 (report-uri *1 ) があります。 これらを活用することで、ユーザのブラウザで起きた mixed content 情報を収集することが可能です。

そのままユーザにリリースしてしまうと mixed content が存在した場合本当にリソースが読み込まれなくなってしまうため、Content-Security-Policy ヘッダでなく Content-Security-Policy-Report-Only ヘッダを利用します。

Content-Security-Policy-Report-Only: block-all-mixed-content; report-uri https://example.com/csp-report

上記のようなヘッダを送出すると、もし mixed content が発生した場合指定した URL にそのブラウザから JSON レポートが POST されます。内容は以下のようなものです。

{
    "csp-report": {
        "blocked-uri": "http://example.com/some_picture.png", 
        "disposition": "report", 
        "document-uri": "https://example.com/mixed_content.html", 
        "effective-directive": "block-all-mixed-content", 
        "line-number": 6, 
        "original-policy": "block-all-mixed-content; report-uri https://example.com/csp-report", 
        "referrer": "", 
        "source-file": "https://example.com/mixed_content.html", 
        "status-code": 0, 
        "violated-directive": "block-all-mixed-content"
    }
}

JSON が送られてくるだけですので、受け取るサーバの実装も簡素なことに加え、 Elasticsearch などに投入することで簡単に分析することが可能です。 クックパッドでは、一部のユーザに CSP を送出しています。また、レポートの受信には Amazon API Gateway を使い、受け取った JSON を Amazon Kinesis Firehose に送信して Amazon Elasticsearch Service で分析を行えるようにしています。実装が必要な箇所は API Gateway が JSON を受け取る箇所のみですので、非常に楽です。

全体リリース

段階的リリースにおいて徐々に公開範囲を広げていき、問題がなければ全体リリースへと進みます。 アプリケーションが HTTPS 接続を受け入れられる状態にした上で、リバースプロキシでリダイレクトを行いました。問題が判明した際すぐに切り戻せるよう、以下の点に気をつけていました。

• HTTP 301 ではなく 307 (Temporary Redirect) の利用
• Cookie 属性や HSTS など切り戻しのしにくい変更を行わない
• ユーザサポート部門との協力

リリース後に監視を行いましたが、大きな問題は見当たりませんでした。

切り戻しのしにくい変更

完全 HTTPS 化における "後戻りのしにくい" 変更として、Cookie への secure 属性の付与や HTTP Strict Transport Security (HSTS) があります。

Cookie の secure 属性は、Cookie を HTTPS 環境下でのみ送出する属性であり、設定ミスや攻撃による意図しない HTTP アクセスでの Cookie 漏出を防ぐことができます。 完全 HTTPS 化が完了すればこの属性を付与することは何ら問題ないのですが、万一 HTTP への切り戻しを行った際に、例えば既存のユーザセッションが全て無効 (セッション Cookie が送出されない) になるといった事態を招きます。 そのため、完全 HTTPS 化を行った上で、サービスに影響がないことを確認できたタイミングで付与する必要があります。

HTTP Strict Transport Security (HSTS) は、Web サービス側からブラウザに対し「次回以降このドメインには HTTPS でアクセスしてほしい」旨を伝え記憶してもらう仕組みです。 完全 HTTPS 化により、 HTTP ページへのアクセスを HTTPS ページにリダイレクトします。つまり、リダイレクトされるまでの初回アクセスは HTTP になってしまいます。 HSTS を付与することで、例えばユーザがブラウザにドメインのみを入力してアクセスする場合でも、そのドメインにアクセスしたことががあれば HTTPS を利用するようになります。

この仕組みも、ブラウザの挙動を変更するため導入には注意が必要です。HSTS が設定されたドメインで、切り戻しのために HTTPS から HTTP へのリダイレクトを単に行うとリダイレクトループが発生します。 HSTS は max-age を 0 にした HSTS ヘッダを送出することで無効にすることができますので、secure 属性よりは切り戻しやすいといえます。しかし、HSTS は常に HTTPS で送出される必要がある (無効にしたい場合でも) という点には注意が必要です。 例えばロードバランサの負荷が心配なケースで、一度 HSTS を全体で有効にしてしまうと、切り戻したあとも継続して HTTPS アクセスを (リダイレクトが行われるまで) 受け続ける必要があります。

上記のことを踏まえ、クックパッドではまず HSTS を有効にして意図せず HTTP でアクセスされているページや動かない機能がないことを社内のエンジニアや事業部門に確認してから secure 属性の付与を行いました。 アプリケーション側でヘッダ送出を行うことも可能ですが、設定の見通しをよくするためリバースプロキシを利用しています。

全体リリース後

画像やあまり利用されていない機能などでトラブルが起きることもあるため、ご意見やお問い合わせを見ながら個別に対応を行っています。 また、URL が変更されるため、集計バッチなどの動作にも気を配る必要がありました。

完了後の反響など

上記のような進め方で、無事にクックパッドを完全 HTTPS 化することができました。 移行にあたり、パフォーマンスなども懸念として上がっていましたが、現在特に問題にはなっていません。HTTPS のオーバーヘッドは当然存在しますが、近年では端末のリッチ化やネットワークの高速化、安定化により大きな問題にはなりにくいと考えています。また、HTTP/2 や TLS 1.3 などプロトコルの進化により、よりオーバーヘッドは減らせると考えています。 また、上述の通りネットワーク広告の売上や、検索エンジンの順位などにも影響はみられませんでした。

おわりに

クックパッドを完全 HTTPS 化した背景や具体的な進め方についてご紹介しました。現在は、公開している全てのサービスを HTTPS で提供しています。 完全 HTTPS 化は、エンジニアに限らず様々な人を巻き込む必要があり、場合によっては少し根気も要る作業になりますが、段階的リリースや CSP などを使うことでよりよく進めていくことも可能です。

個人的には、完全 HTTPS 化に限らず、ユーザが安全にインターネットを利用できるようにすることは Web サービス事業者の一つの責務と考えています。完全 HTTPS 化は、その中でも実行のための障壁がなくなりつつある改善の一つです。

ユーザの安全や新技術など、Web が次の段階に進んでいくためにも HTTPS 対応は今後より必須になることでしょう。この記事が、まだ HTTPS 移行が済んでいない方のお役に立てば幸いです。

*1:report-uri は CSP Level 3 において廃止されているため、今後は report-to を使っていく必要があります

MySQL with InnoDB のインデックスの基礎知識とありがちな間違い

こんにちは、サービス開発部の荒引 (@a_bicky) です。

突然ですが、RDBMS の既存のテーブルを見てみたら「何でこんなにインデックスだらけなの？」みたいな経験はありませんか？不要なインデックスは容量を圧迫したり、挿入が遅くなったりと良いことがありません。

そんなわけで、今回はレコードを検索するために必要なインデックスの基礎知識と、よく見かける不適切なインデックスについて解説します。クックパッドでは Rails のデータベースとして主に MySQL 5.6、MySQL のストレージエンジンとして主に InnoDB を使っているので、MySQL 5.6 の InnoDB について解説します。

InnoDB のインデックスに関する基礎知識

インデックスの構造 (B+ 木)

InnoDB では B+ 木が使われています。B+ 木は次のような特徴を持った木構造です。

• 次数を b とすると、各内部ノード（葉ノード以外のノード）は最大 b - 1 個のキーと最大 b 個の子ノードを持つ*1
• 内部ノードは値を持たない
• 葉ノードの各キーは値（または値へのポインタ）を持つ
• 葉ノードは次の葉ノードへのポインタを持つ
• で検索できる

次の図は次数 3 の例です。

例えば、key が 4 の value を取り出すには次のように木を辿れば良いです。

key が 2 〜 9 の value を全て取り出すには次のように木を辿ることができます。葉ノード間が繋がっていることによって範囲検索を効率的に行うことができます。

InnoDB の B+ 木では、インデックスに使われているカラムの値と主キーの値が value に格納されています。

ここで (c1, c2) のような複合インデックスを考えてみます。イメージとしては次のような構造になります。例えば、key の上位 4 bytes を c1 に割り当て、下位 4 bytes を c2 に割り当てるイメージです。

図からわかるように、c2 = 2 に対応するインデックスレコードを探そうとしても、c1, c2 の順番で並んでいるので、木を辿ることで探すことはできません。c1 = 4 AND c2 = 2 のように c1 の条件が指定されることで木を辿ることができるようになります。

また、c1 >= 2 AND c2 <= 4 のような条件の場合、c1 >= 2 の条件を満たすインデックスレコードは木を辿ることで探せますが、それらのインデックスレコードを更に c2 <= 4 の条件で絞り込むために木を利用することはできません。具体的には、図の例だと [7,5] が条件を満たさないインデックスレコードですが、その隣の [9,3] が条件を満たすかどうかはわからないですよね。結局のところ、c1 >= 2 のインデックスレコード全てを走査しないといけません。

よって、インデックスレコードの走査の数を減らす観点では c1 = 2 AND c2 <= 4 のように c1 の条件が等価比較になる場合に限って、複合インデックスとしての意味を持つことになります。*2

このことについては MySQL のドキュメントの 8.2.1.2 Range Optimization にも次のように言及されています。

The optimizer attempts to use additional key parts to determine the interval as long as the comparison operator is =, <=>, or IS NULL. If the operator is >, <, >=, <=, !=, <>, BETWEEN, or LIKE, the optimizer uses it but considers no more key parts.

InnoDB における B+ 木の実装について知りたい方は次の記事と言及されている関連記事を読むとかなり理解が深まると思います。

B+Tree index structures in InnoDB – Jeremy Cole

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

InnoDB の主キーはクラスタ化インデックス (clustered indexes) になっており、B+ 木の葉ノードにレコードの全データが格納されています。 特定のカラムに張るインデックスはセカンダリインデックスと呼ばれ、セカンダリインデックスの葉ノードには主キーが必ず含まれています。

セカンダリインデックスを使った検索は、まずセカンダリインデックスの B+ 木を辿って主キーを取得し、次にクラスタ化インデックスの B+ 木を辿ってレコードを取得することになります。

セカンダリインデックスに必要な情報が全て格納されていれば、クラスタ化インデックスを辿る手順をスキップすることができるので高速です。このようにレコードを取得する際にセカンダリインデックスで完結する場合のことをカバリングインデックスと呼びます。

MySQL がレコードを取得する大まかな手順

MySQL がレコードを取得する際の主要な登場人物として、executor*3 と storage engine (e.g. InnoDB) がいます。

storage engine が InnoDB の場合は次のようにレコードを取得します。

1. executor が storage engine にレコードを要求する
2. storage engine (InnoDB) はセカンダリインデックスの木を辿ることで、取得すべきレコードのインデックスに含まれているカラム値と主キーの値を得る
   • インデックスが使えない場合はクラスタ化インデックスに含まれている全レコード情報を executor に返す
3. storage engine は 2 で得たデータのうち、インデックスに含まれているカラム値の情報を使って取得すべきレコードをフィルタリングする (Using index condition)
   • インデックスに含まれている情報が使えない場合はスキップ
4. storage engine は 3 で得たデータの主キー情報を使ってクラスタ化インデックスからレコードを取得する
   • SELECT で指定されているカラムや WHERE で指定されているカラムの情報が全てインデックスに含まれている場合はスキップ (Using index)
5. storage engine は取得したレコード情報を executor に返す
6. executor は storage engine がフィルタリングできなかったレコードをフィルタリングする (Using where)
   • storage engine 側で全てフィルタリングされている場合はスキップ

「雑なMySQLパフォーマンスチューニング」はこの辺の内容をわかりやすく説明しているので、ピンとこなければ読むことをお勧めします。

以上の説明で、Explain で extra に Using index, Using index condition, Using where が出る場合にどのような処理が行われているかイメージが付くと思います。 Using index condition が出る場合は ICP (Index Condition Pushdown) 最適化と呼ばれ、MySQL 5.6 から導入されました。これによって、クラスタ化インデックスから取得するレコードが減るのでその分高速になります。c1 >= 2 AND c2 <= 4 のような条件のために (c1, c2) の複合インデックスを張っても意味がないと前述しましたが、ICP の恩恵を受けてパフォーマンスが改善する場合もあります。

インデックスを張る上でのポイント

インデックスを張る上では次のような内容がポイントになると思います。

• インデックスで絞り込めるレコード数が大きいか？（選択性が高いか？）
  • WHERE c1 = 1 AND c2 = 2 AND c3 = 3 AND c4 = 4 みたいな条件があるからといって、(c1, c2, c3, c4) の複合インデックスが必要とは限らない
  • c1 で十分絞りこめるのに他のカラムもインデックスに含めるとインデックスが肥大化する
• 複合インデックスを張る場合
  • 絞り込めるレコード数の大きいカラムを先にしているか？
  • 等価比較するカラムが先になっているか？
• カバリングインデックスの恩恵を十分に受けられるか？
• ICP の恩恵を十分に受けられるか？
• ソートに使うか？*4

不適切なインデックスの例

これまでに説明した内容がわかっていると、以下に挙げる内容が不適切なインデックスだとわかるはずです。

• インデックスの最初のカラムに範囲指定をする複合インデックス
• 選択性の悪いインデックス
• 他のインデックスで代替できるインデックス

具体例を挙げるために、次のようなテーブルを扱うことにします。商品情報を管理するテーブルで、現在以降に掲載される商品の情報が日々追加されていく想定です。

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` int(10) unsigned NOT NULL,  -- 商品を掲載している店舗の ID
  `name` varchar(255) NOT NULL,         -- 商品名
  `price` int(10) unsigned NOT NULL,    -- 商品の価格
  `starts_at` datetime NOT NULL,        -- 商品の掲載開始日時
  `ends_at` datetime NOT NULL,          -- 商品の掲載終了日時
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

それでは具体例を見ていきます。

インデックスの最初のカラムに範囲指定をする複合インデックス

次のインデックスが定義されているとします。

ALTER TABLE products ADD INDEX ix_ends_at_starts_at (ends_at, starts_at);

これは、次のようなクエリに対処するために定義されたインデックスですが、ends_at 単一のインデックスを張るのとあまり変わりません。*5

-- 現在掲載されている商品を抽出する
SELECT * FROM products WHERE starts_at <= NOW() AND ends_at >= NOW();

この理由は次のような B+ 木をイメージするとわかりやすいです。この木は (c1, c2) に対して構築された B+ 木ですが、c1 >= 2 AND c2 <= 5 のような条件でレコードを引こうと思った場合にインデックスレコードを走査する数が減りません。例えば、[2,8] のインデックスレコードが c2 の条件を満たしませんが、その隣の [3,1] が c2 の条件を満たすかどうかは木の構造から判断できず、c1 >= 2 のインデックスレコードを全て走査することになります。

MySQL 5.6 では前述した ICP 最適化という仕組みがあるので、starts_at >= NOW() での絞り込みで劇的にレコードが減るようであれば、ICP の恩恵を受けられるかもしれません。

ICP の効果は session status の Handler_read_next の値がどれだけ変わるかを見てみるのが良いでしょう。この値が少ないほどインデックスレコードの走査が少ないことを意味します。

-- ICP 有効
FLUSH STATUS;
SET @@optimizer_switch = "index_condition_pushdown=on";
SELECT * FROM products WHERE starts_at <= NOW() AND ends_at >= NOW();
SHOW SESSION STATUS LIKE 'Handler%';

-- ICP 無効
FLUSH STATUS;
SET @@optimizer_switch = "index_condition_pushdown=off";
SELECT * FROM products WHERE starts_at <= NOW() AND ends_at >= NOW();
SHOW SESSION STATUS LIKE 'Handler%';

ICP の効果が低いと判断したら、ix_ends_at_starts_at は削除して単一のインデックスを張りましょう。

ALTER TABLE products DROP INDEX ix_ends_at_starts_at,
  ADD INDEX ix_ends_at (ends_at);

似たような例として、次のようなインデックスを考えます。

ALTER TABLE products ADD INDEX ix_ends_at_shop_id (ends_at, shop_id);

これは、次のようなクエリに対処するために定義されたインデックスですが、同様に ends_at 単一のインデックスを張るのとあまり変わりません。

-- shop_id = 1234 の店舗の現在掲載される商品を抽出する
SELECT * FROM products WHERE shop_id = 1234 AND starts_at <= NOW() AND ends_at >= NOW();

shop_id は等価比較で使う前提なので、(shop_id, ends_at) の順番でインデックスを張ることで複合インデックスとしての恩恵が得られます。

ALTER TABLE products DROP INDEX ix_ends_at_shop_id,
  ADD INDEX ix_shop_id_ends_at (shop_id, ends_at);

選択性の悪いインデックス

次のインデックスが定義されているとします。

ALTER TABLE products ADD INDEX ix_shop_id_starts_at (shop_id, starts_at);

これは、次のようなクエリに対処するために定義されたインデックスですが、選択性の観点で良くありません。

-- shop_id = 1234 の店舗の現在掲載される商品を抽出する
SELECT * FROM products WHERE shop_id = 1234 AND starts_at <= NOW() AND ends_at >= NOW();

ix_shop_id_starts_at の選択性が良いかどうかはテーブルとクエリの特性次第ですが、products テーブルが現在以降に掲載される商品しか追加されず、過去に掲載されていた商品が残り続けるのであれば、starts_at <= NOW() という条件は該当レコードが日々増えていきます。一方で、ends_at >= NOW() は商品の増え方が一定であれば該当レコードの量は一定とみなせます。

よって、(shop_id, ends_at) にインデックスを張るべきです。

ALTER TABLE products DROP INDEX ix_shop_id_starts_at,
  ADD INDEX ix_shop_id_ends_at (shop_id, ends_at);

他のインデックスで代替できるインデックス

次のインデックスが定義されているとします。

ALTER TABLE products ADD INDEX ix_shop_id (shop_id),
  ADD INDEX ix_shop_id_ends_at (shop_id, ends_at);

これは次のような 2 種類のクエリに対処するために定義されたインデックスですが、ix_shop_id は冗長です。

-- shop_id = 1234 の店舗に掲載されたことがある、または掲載される予定の商品を全て抽出する
SELECT * FROM products WHERE shop_id = 1234;
-- shop_id = 1234 の店舗の現在掲載される商品を抽出する
SELECT * FROM products WHERE shop_id = 1234 AND starts_at <= NOW() AND ends_at >= NOW();

shop_id での絞り込みに特化したインデックスとして ix_shop_id を導入したと思われますが、次の 2 つの木を見れば ix_shop_id_ends_at が ix_shop_id の役割を包含していることは一目瞭然です。

よって、ix_shop_id は削除すべきです。

ALTER TABLE products DROP INDEX ix_shop_id;

最後に

以上、MySQL (InnoDB) のインデックスについて簡単に解説しました。InnoDB について完璧に理解しようと思うと膨大な知識が必要ですが、よくある単純な用途でインデックスを張るだけであれば、必要とされる知識はほんの少しであることがわかると思います。 本エントリーによって、世の中から不適切なインデックスだらけのテーブルが少しでもなくなれば幸いです。

*1:次数の解釈は文献によって異なるので、2017 年 4 月 17 日時点の Wikipedia に合わせています

*2:後述する ICP が効果を発揮する場合はその限りではありません

*3:executor を mysql server と表現している記事を見かけることがありますが、sql_executor.cc に実装されているので executor という表現の方が適切だと思います

*4:本エントリではソートには触れないので、興味のある方は「漢(オトコ)のコンピュータ道: Using filesort」を参照すると良いと思います

*5:Explain の key_len 的には starts_at も使われるように見えるので、ソースコードを読んでその理由を調べようと前々から思ってますが、未だに調査できてません…

ハードな案件のやわらかいプロジェクト管理

研究開発部 兼 クックパッド料理教室の伊尾木です。 暖かくなったり、寒くなったりと気温差が激しいですが、皆さんお体は大丈夫でしょうか。

ところで、最近クックパッド料理教室で、ビジネスモデル変更に伴うリニューアルプロジェクトを実施しました。

(ビジネスモデル変更に伴う全面リニューアル)

私はPMと開発リーダーを担当したのですが、そこで実施した「やわらかいプロジェクト管理」についてご紹介したいと思います。

炎上しそうな予感がいっぱい！

ビジネスモデル変更に伴うリニューアルって聞いただけで炎上の予感で胸が膨らみますね。 ビジネスモデルの変更だけでも大きな話なのに、システムの全面刷新まで同時に実施したので、プロジェクトとして不確定要素が多く、管理が難しいものになっていました。

20名弱（エンジニアが8名、他には営業チーム、ユーザサポートチームなどがありました）で8ヶ月程度のプロジェクトでした。一般的には非常に大規模というわけではないのですが、システムの全面刷新でしたので開発量が膨大で、クックパッド内の普段の開発規模からすると非常に大きなプロジェクトです。さらにはこの規模の管理を経験したメンバーも少なく、私がJOINした段階ではどのようにプロジェクトを管理するかもあまり明確に決まっていませんでした。

つまりは、大い燃え上がることが当初から想定できていたわけです。

じゃあガチガチのプロジェクト管理をやるのも以下の理由から難しいと考えました。

• 開発量が多く、不確定要素が多い
• 期間が短い
• そんなに管理工数を割けない（PMの私もバリバリに実装していました…）
• 文化に合わない

多くの場合、不確定要素が多いとガチガチのプロジェクト管理に走ってしまうのですが、管理のための管理が発生しやすく、結果的にプロジェクトの進行が遅くなると思っています。また、社内の普段の開発方法から大きく変わると、文化的な摩擦や、コミュニケーションミスが起きてしまうリスクも高いなと考えました。

そこで変更が多発すること前提とした「やわらかいプロジェクト管理」を実施することにしました。

やわらかいプロジェクト管理

ここでいう「やわらかいプロジェクト管理」とは、

• 開発対象をやや大きな粒度でタスク化し、各タスク間にあえて「遊び」を持たせることで、調整や変更を行いやすくする
• エンジニアの生産性を低下する管理を排除する

を実施することです。

厳密な計画を立ててその通りにやることを目標にするのではなく、変更が多発することを前提として、状況に応じて臨機応変に変更できるように管理することを目標としました。 こういうと「あーアジャイル開発ね」っと思われるかもしれませんが、ここで言っているのは開発方法論のような大きな話ではなく、それよりはもう少し細かく、タスクの管理のテクニックなどの話になります。

具体的には以下のことを実施しました。

• プロジェクト全体は月単位のタイムボックス管理
• 進捗管理は週単位のタイムボックス
• 開発メンバーとの進捗確認MTGを実施しない
• 心理的安全性を保証する

プロジェクト全体は月単位のタイムボックス管理

細かいスケジュールは立てませんでしたが、全体の流れとチームやタスクの依存関係を把握するために月単位のタイムボックス管理を行いました。

プロジェクト全体を1ヶ月程度のタイムボックスに分割して、各タイムボックスに大きな粒度のタスクのみを配置します。 そして、そのタスク間の順序や依存関係を整理して、どのタイムボックス中に何ができるかを把握しました。

各タイムボックスに作業の目安となるような目的を決めていたので（e.g. 「ビジネスモデルの詳細をつめる」「管理画面の主要な機能を開発する」等）いわゆるフェーズ管理とほぼ同じものになっていたと思います。ただ、フェーズ管理のように「今が何フェーズか」というのはあまり重要ではなく、各タイムボックスに積み残しがあっても次のタイムボックスに進むようにしました。

ちなみにここでは粒度の細かいタスクを配置しないように注意しました。このレベルで細かいタスク管理などをやってしまうと、管理工数が跳ね上がってしまいますし、柔軟な変更が難しくなってしまうからです。

進捗管理は週単位のタイムボックス

タスクの具体的な進捗管理は週単位のタイムボックスで管理を行いました。 月単位のタイムボックスに配置されたタスクを少しだけ分割して、週のタイムボックスに配置し、それらの進捗管理を週次で行うようにしました。

ちなみに、日次での進捗は追わないようにしました。よくある「タスクAは、X月Y日に終了します」というのを避けて、「今週中にAとBが終わればいい」という感じです。

1週間の中でどのように時間配分するかや、どのような開発順序にするかは、各エンジニアに任せるようにしました。 どのように働くのが効率がいいのか、いつが集中できるのかなどはエンジニアによって大きく異なるので、なるべくその人が良いと思うやり方を実施しました。

また、タスク自体もあまり細かい分割は避けました。タスクを細かくして管理してしまうと、管理のための管理が発生しやすいと考えたからです。 といっても粒度が大きすぎても管理できないので、大体1週間のタイムボックスに収まるように分割しました。

開発メンバーとの進捗確認MTGを実施しない

私はPMと開発チームのリーダーを兼務していましたが、開発メンバーとは進捗MTGを実施しませんでした（リーダー間の進捗MTGは週次で実施していました）。 進捗確認MTGは、どうしても余計な作業が発生してしまうので（進捗遅れの言い訳作りや、進捗を示すためのデータ集めなど）、そんなムダなことはしたくなかったですし、 進捗を報告させるのは一定のストレスを与えてしまうので、そういう余計なストレスも与えたくなかったためです。

そもそも開発の進捗は、GithubのPullRequestやIssueを見れば大体は把握できますし、細かい部分は現場でのメンバーの様子を見ていれば把握できるので、廃止しました。

心理的安全性を保証する

チームの心理的安全性が低いとどうしてもストレスから生産性が落ちてしまいます。また、タスクの柔軟な変更や、調整を行うには心理的安全性が高くないと、防衛的になってしまって、上手く調整できなくなってしまいます。そこで、心理的安全性を保証するように色々と配慮しました。

例えば、進捗の遅れを追求しないようにしていました。 進捗が遅れている人も、サボって遅れているわけではなくその人なりに最大限努力しているので、無理に急かしたところでメリットはありません。

何か問題があって遅れているのか、見積もりが間違っていたかだけなので、問題を取り除くか、スコープを調整する必要があります。いずれにしろ問題はメンバーではないということを意識して伝えました。

また、どんなに進捗が遅れていても、メンバーのプライベートを優先することを徹底しました。

どんなに忙しくてもライブやサッカーの観戦などの邪魔は絶対にしないようにしましたし、何時に来て何時に帰ろうが自由だという空気を作るようにしました。そもそもフルフレックスなので、当然の権利なわけですが、例えばお昼から出社する場合、みんなどうしても「すいません、お昼から出社します」というように謝ってしまいます。このような発言がある場合「謝る必要ないですし、そもそも報告も不要ですよ」と伝えるようにしました。

やわらかいプロジェクト管理のデメリット

上で述べたように、やわらかいプロジェクト管理では変更を吸収しながらプロジェクトを進めることがやりやすくなります。とはいえ、プロジェクト管理に銀の弾丸はなく、当然デメリットも存在します。

まず、リーダー（あるいはPM）の負荷が非常に高いです。言ってしまえば、メンバーに対してタスク管理をあえて甘くしているため、具体的な進捗や、リスクなどの把握をリーダーが一挙に把握し切る必要があります。 つまりは、リーダーが全体を常にオーガナイズして、各メンバーの生産性を高める手法だとも言えます。

今回の場合でいうと、私が開発の全てをコントロールしていました。いわゆる、トラックナンバー1の状態で、非常に危うい状態だっと思います。

また、心理的安全性を高めるための様々な配慮も必要になります。プロジェクトが佳境に入っても、リーダーは常にポジティブな空気を作る必要があり、一般的には簡単なことではないと思います。

このため、プロジェクトの規模がある程度大きくなってしまうと、この手法は適用しづらいと思われます。

さらには、ある程度メンバーを信用して管理を行うため、自走できるメンバーがいない場合も上手く回らない可能性があります。

終わりに

クックパッド料理教室のリニューアルプロジェクトで用いた「やわらかいプロジェクト管理」についてご紹介しました。何かの参考になれば幸いです。