LoginSignup
6
0

More than 5 years have passed since last update.

CakePHPAdvent Calendar 2018Day 5
@yKanazawa(金澤 裕毅)inランサーズ株式会社

CakePHP実装の隙をついてFORCE INDEXを書いていた話

Last updated at Posted at 2018-12-04

※このドキュメントは、負荷対策のためにCakePHP 1.3のサービスにFORCE INDEXを付与したときに作ったドキュメントです。

FORCE INDEXを適用してレビューを依頼したときに、レビュアーに「そんな実装ができるはずがない」と言われました。でも実際できているし、ならなぜできるのかを説明しろと言われて作ったものです。

調査の結果、CakePHPとしてはFORCE INDEXはサポートしておらず、CakePHP実装の隙をついていただけという結論になりました。

イントロダクション

CakePHPのORM機能を使えば、直接SQLを記述するよりも実装が用意で、かつ、SQLインジェクション等のセキュリティ脅威も作りにくくなります。

しかしながら、アクセス量、データ量の増加に伴い、SQLが複雑になるとMySQLが適切なインデックスを選択してくれず、パフォーマンスや負荷の問題を抱えるようになります。

MySQLでは、FORCE INDEX等のヒント句を加えることで、適切なインデックスを選択するように誘導させることができます。

ヒント句を付与する一番簡単な方法は、queryメソッドで直接SQLを記述することですが、この方法だと上記のメリットが損なわれてしまいます。

また、SQLが動的に変更されることを考慮すると、できるだけORM機能で解決したいところです。

CakePHP 1.3のORMでヒント句を記述する方法

実は、CakePHPのfindメソッド内でも、以下のように実装することで、FROM句の直後にFORCE INDEXやUSE INDEXを付与することが可能です。（※JOIN句には付与できません）


<?php
$this->Hoge->find('all',array(
    'conditions' => array(
        'id' => array(1,2,3,4)
    ),
    'fields'  => array(
        'id','name'
    ),
    'joins' => array('FORCE INDEX(PRIMARY)'),
));

これで、以下のSQLが生成されます。

SELECT 
   `Hoge`.`id`, `Hoge`.`name` 
FROM 
  hoges AS Hoge FORCE INDEX(PRIMARY) 
WHERE 
  `Hoge`.`id` IN ( 1,2,3,4 );

しかし、この方法は、CakePHPで用意した仕様ではなく、実装上の抜け穴を利用したノウハウとして広まったものと推測されます。文字列型とarrayの連結を行っており、直感的に内部的な処理を想像しにくいため、良く知らずに修正すると障害を起こしやすいかも知れません。

FORCE INDEX(USE INDEX)を使う際に知っておきたい内部処理

ヒント句を付与する前に、内部的な処理を理解しておきましょう。
以下、FORCE INDEXを付与する場合の例を説明します。

find等のSQL生成メソッドを呼び出すと、CakePHPライブラリの
cake/libs/model/datasources/dbo_source.php
でSQL生成の処理が行われます。

そして、FORCE INDEXの付与関連処理は、以下の箇所で行われます。

1450行目 buildStatement関数


/**
 * Builds and generates an SQL statement from an array.  Handles final clean-up before conversion.
 *
 * @param array $query An array defining an SQL query
 * @param object $model The model object which initiated the query
 * @return string An executable SQL statement
 * @access public
 * @see DboSource::renderStatement()
 */
    function buildStatement($query, &$model) {
        $query = array_merge(array('offset' => null, 'joins' => array()), $query);
        if (!empty($query['joins'])) {
            $count = count($query['joins']);
            for ($i = 0; $i < $count; $i++) {
                if (is_array($query['joins'][$i])) {
                    $query['joins'][$i] = $this->buildJoinStatement($query['joins'][$i]);
                }
            }
        }
        return $this->renderStatement('select', array(
            'conditions' => $this->conditions($query['conditions'], true, true, $model),
            'fields' => implode(', ', $query['fields']),
            'table' => $query['table'],
            'alias' => $this->alias . $this->name($query['alias']),
            'order' => $this->order($query['order'], 'ASC', $model),
            'limit' => $this->limit($query['limit'], $query['offset']),
            'joins' => implode(' ', $query['joins']),
            'group' => $this->group($query['group'], $model)
        ));
    }

1424行目 buildJoinStatement関数

/**
 * Builds and generates a JOIN statement from an array.  Handles final clean-up before conversion.
 *
 * @param array $join An array defining a JOIN statement in a query
 * @return string An SQL JOIN statement to be used in a query
 * @access public
 * @see DboSource::renderJoinStatement()
 * @see DboSource::buildStatement()
 */
    function buildJoinStatement($join) {
        $data = array_merge(array(
            'type' => null,
            'alias' => null,
            'table' => 'join_table',
            'conditions' => array()
        ), $join);
        if (!empty($data['alias'])) {
            $data['alias'] = $this->alias . $this->name($data['alias']);
        }
        if (!empty($data['conditions'])) {
            $data['conditions'] = trim($this->conditions($data['conditions'], true, false));
        }
        return $this->renderJoinStatement($data);
    }

1479行目 renderJoinStatement関数

/**
 * Renders a final SQL JOIN statement
 *
 * @param array $data
 * @return string
 * @access public
 */
    function renderJoinStatement($data) {
        extract($data);
        return trim("{$type} JOIN {$table} {$alias} ON ({$conditions})");
    }

1492行目 renderStatement関数

/**
 * Renders a final SQL statement by putting together the component parts in the correct order
 *
 * @param string $type type of query being run.  e.g select, create, update, delete, schema, alter.
 * @param array $data Array of data to insert into the query.
 * @return string Rendered SQL expression to be run.
 * @access public
 */
    function renderStatement($type, $data) {
        extract($data);
        $aliases = null;
        switch (strtolower($type)) {
            case 'select':
                return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
            break;
            case 'create':
                return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
            break;
            case 'update':
                if (!empty($alias)) {
                    $aliases = "{$this->alias}{$alias} {$joins} ";
                }
                return "UPDATE {$table} {$aliases}SET {$fields} {$conditions}";
            break;
            case 'delete':
                if (!empty($alias)) {
                    $aliases = "{$this->alias}{$alias} {$joins} ";
                }
                return "DELETE {$alias} FROM {$table} {$aliases}{$conditions}";
            break;
            case 'schema':
                foreach (array('columns', 'indexes', 'tableParameters') as $var) {
                    if (is_array(${$var})) {
                        ${$var} = "\t" . join(",\n\t", array_filter(${$var}));
                    } else {
                        ${$var} = '';
                    }
                }
                if (trim($indexes) != '') {
                    $columns .= ',';
                }
                return "CREATE TABLE {$table} (\n{$columns}{$indexes}){$tableParameters};";
            break;
            case 'alter':
            break;
        }
    }

内部処理のポイント

これらの処理のポイントは以下の3つです。

  • arrayではなく、文字列で指定すると、JOIN句であるか否かのチェックが入らず、その文字列のまま最後のSQL生成まで通ってしまう
            for ($i = 0; $i < $count; $i++) {
                if (is_array($query['joins'][$i])) {
                    $query['joins'][$i] = $this->buildJoinStatement($query['joins'][$i]);
                }
            }
  • joins の記述順にJOIN句結合が行われる
        return $this->renderStatement('select', array(
            'conditions' => $this->conditions($query['conditions'], true, true, $model),
            'fields' => implode(', ', $query['fields']),
            'table' => $query['table'],
            'alias' => $this->alias . $this->name($query['alias']),
            'order' => $this->order($query['order'], 'ASC', $model),
            'limit' => $this->limit($query['limit'], $query['offset']),
            'joins' => implode(' ', $query['joins']),
            'group' => $this->group($query['group'], $model)
        ));

renderStatement関数で、 {$table} {$alias} {$join} の順に文字列結合される

    function renderStatement($type, $data) {
        extract($data);
        $aliases = null;
        switch (strtolower($type)) {
            case 'select':
                return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
            break;

故に、JOIN句の先頭にFORCE INDEXを文字列で記述すれば、以下のようにFROM句直後に付与させることができるわけです。

FROM users AS `User` FORCE INDEX (PRIMARY) LEFT JOIN ...

しかし、JOIN対象のテーブルにFORCE INDEXを与えるには、

LEFT JOIN works AS `Work` FORCE INDEX (PRIMARY) ON ... LEFT JOIN  ...

のように、JOIN 句とON句の間に記述する必要があります。

この記述を生成するには、CakePHP内のソースに手を加えない限りできません。

CakePHP2.8の場合

CakePHP 1.3 → 2.8にバージョンアップしたら、以下の記述では動かなくなりました。

'joins' => array('USE INDEX(created)'),

CakePHP 1.3では、以下のようにFORCE INDEXを付与できていたのですが、

SELECT 
  Proposal.id, 
  Proposal.user_id,
  Proposal.work_id,
  User.id, 
  User.nickname, 
  User.created, 
  User.modified 
FROM 
  proposals AS Proposal FORCE INDEX(created) 
LEFT JOIN 
  users AS User ON (Proposal.user_id = User.id) 
WHERE 
  Proposal.creative_check = 1 
AND 
  Proposal.created >= '2018-11-14 17:01:55' 
AND 
  Proposal.deleted = 0 
ORDER BY 
  Proposal.id ASC

CakePHP 2.8の場合は、以下の箇所についてしまいます。

SELECT 
  Proposal.id, 
  Proposal.user_id,
  Proposal.work_id,
  User.id, 
  User.nickname, 
  User.created, 
  User.modified 
FROM 
  proposals AS Proposal
LEFT JOIN 
  users AS User ON (Proposal.user_id = User.id) FORCE INDEX(created) 
WHERE 
  Proposal.creative_check = 1 
AND 
  Proposal.created >= '2018-11-14 17:01:55' 
AND 
  Proposal.deleted = 0 
ORDER BY 
  Proposal.id ASC

※以下の記述であればCakePHP 2.8でも動作するようです。

$joins = array(
    'FORCE INDEX(PRIMARY)',
    array(
        'type' => 'INNER',
        'alias' => 'Table2',
        'table' => 'table2',
        'conditions' => 'table1.hoge_id = table2.id'
    ),
);

どうしてそうなるのかは、CakePHP2.8のソースを読んで調べる必要があるのですが、それはまた別の機会に。

6
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
0