LoginSignup
5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@BRSF

cakePHPのクエリ処理が遅いときには、SQLを今一度、要確認

Last updated at Posted at 2021-02-25

実は、cakePHPは2021年1月現在でも、日本で最も稼働中のプログラムが多いPHPフレームワークです。ですが、近年はそんな日本でもLaravelが徐々に勢力を伸ばしてきています。

そのCakePHPが下火となってきている原因として指摘されているのがクエリ処理の遅さですが、そのときにパフォーマンスを考えたSQL構築が正しく行われていたのかすごく気になりました。

同じ環境を一から作成してLaravel7とcakePHP4.1を入れてみたのですが、体感速度はほぼ同じぐらいで、気にならないぐらいの速度にはなりました。既存のサーバなら他にも遅延の原因があり、キャッシュのクリア、php.iniの設定など他の人がいろいろなチューニング方法を載せてくれているので、それも参考にしてください。

要件定義

では、これから2種類のテーブル(Persons、Teams)に対して、検索フォームを含んだCRUDシステムを構築していきます。

テーブル設計

2つのテーブルの定義はこのようになっています。
Persons

カラム 型 指定
id int(11) primary
name mediumtext
team_id int(4)
uniform_number int(5)

Teams

カラム 型 指定
id int(11) primary
name varchar(100)
league varchar(50)

では、この2つのカラムを結合（結合条件はPersonsのteam_idとTeamsのid）し、そのテーブルを元に検索結果を表示するシステムを作ってみました。

設計はinitializeメソッドに置く

cakePHPを動かしているコントローラ部分は言ってしまえば、オブジェクト指向で制御しているだけです。なので、テーブル構造はコンストラクタ的な役割を果たしているinitializeメソッドになどに置いた方がいいです。

PersonsController.php
class PersonsContorller extends AppController
{
protected $t_persons;
protected $sql;

//結合して作ったテーブル
public function initialize() :void
{
		$t_persons = $this -> Persons->query()->
				select([
					'id'=>'p.id',
					'name' =>'p.name',
					'team' => 't.name',
                                        'team_id' => 'p.team_id',
					'league' => 't.league',
					'uniform_number' => 'p.uniform_number',
				])->
				from([
					'p' => 'persons', //元となるテーブルのエイリアス設定
				])->
				join([
					'table'=>'teams',
					'alias' => 't',
					'type'=>'INNER',
					'conditions' => ['t.id = p.team_id'],
				]);
              $this->t_persons = $t_persons; //インスタンスを返す
}

表修飾をきちんと行っているか

この部分が、この記事を書こうとしたきっかけです。結合しようとしている表別名のエイリアス指定は至るページで方法が紹介されているのですが、元となるテーブルをエイリアス指定する方法がどこにも記載されていませんでした。

ですがselectメソッドで参照しようとしているカラムも適宜、表修飾した方が確実に処理が早くなります。要はクエリメソッドはSQLを構築していくメソッドなので、表修飾しないカラムを処理しようとすると、逐一テーブルを確認するので処理が遅くなるのは体験済みだと思います。

※表別名は必ずしも指定しなくてもいいのですが、しておいた方が記述が圧倒的に楽です。ただし、テーブルソート機能を用いる場合は後述する対処が必要になります。

元となるテーブルの表別名（エイリアス）指定

注意点として、元となるテーブルの場合表別名を定義するには、この方法で行けるみたいです。むしろ、後述するようなtableとaliasプロパティを逐一定義してもうまく行きません。

$this -> テーブル名 ->from([表別名 => 表名])

PersonsController.php
->from([
    'p' => 'persons', 
])->…

ただし、結合先のテーブルを表別名定義するには上記の方法を使用してください。fromメソッドと同じ記述方法だとエラーとなります。

PersonsController.php
->join([
    'table'=>'teams', //結合先のテーブル
    'alias' => 't', //結合先テーブルの表別名
    'type'=>'INNER', //結合の種類
    'conditions' => ['t.id = p.team_id'], //結合条件
]);

whereメソッドを付す場合

whereメソッドはSQLのwhere句と同様に、OR、AND条件を付与することができます。そして、その場合においてもやはり同様に表修飾はしておいた方が確実です。

PersonsController.php

public function index()
{
	$ar_data = $this->request->getData(); //フォームの値
	$ar_orwhere = []; //OR条件を格納
	$ar_andwhere = []; //AND条件を格納
	$ar_where = []; //whereメソッド処理用のオブジェクトを格納
	if(isset($ar_data['find'])&& $ar_data['find'] != NULL) $ar_orwhere['p.name like'] = "%{$ar_data['find']}%";
	if(isset($ar_data['team'])&& $ar_data['team'] != NULL) $ar_orwhere['t.id'] = $ar_data['team'];
	if(isset($ar_data['league'])&& $ar_data['league'] != NULL) $ar_andwhere['t.league'] = $ar_data['league'];
	if(isset($ar_orwhere) && $ar_orwhere != NULL) $ar_where['OR'] = $ar_orwhere;
	if(isset($ar_andwhere) && $ar_andwhere != NULL) $ar_where['AND'] = $ar_andwhere;
	$sql = $this -> t_persons -> where($ar_where);
	//echo($sql->sql()); //ここでSQLの記述をデバッグ
	$persons = $this -> paginate($sql); //ページャー機能で返すと処理が楽になる
    $this -> set(compact('persons')); //ビューに値を出力
}

whereメソッドの中身

whereメソッドは一見ややこしく見えますが、この2つのルールを把握するだけで簡単です。

  • AND条件の場合はそのままオブジェクトに格納(キーをANDにしても動く)。
  • OR条件の場合は連想配列でキーをORにして格納

そして、値が代入されているかを判断してから変数$ar_whereに対象のオブジェクトを作っていった方がよいでしょう。冗長な検索条件はやはり検索を遅くします。後は、sql()メソッドを使用して、実際どのように処理されていたのか確認してみると確実です。

なお、$ar_whereの中身はこのように入っていきます（全部検索条件を入力した場合）。

$ar_where = [
              "OR"=>[
                          "p.name like"=> "%名前の部分一致%",
                          "t.id" => "チーム名",
                    ],
              "AND"=>[
                          "t.league" => "所属リーグ",
                     ],
            ];

実際動かしてみた

これが実際に動いているテスト操作用のシステムです。

PersonsContoroller.php
declare(strict_types=1);

namespace App\Controller;

use Cake\ORM\TableRegistry;
use Cake\Http\ServerRequest;
use Cake\View\Helper\PaginatorHelper;
use Cake\View\Helper\SessionHelper;
use App\Controller\AppController;
use App\Form\PersonForm;
use Cake\Http\ConnectionManager;
use Cake\Http\Exception\NotFoundException;

class PersonsController extends AppController
{
	protected $t_persons;
	protected $persons;
	protected $sql;
	protected $ses;
	public $paginate = [
		'limit' => 5,
		'order' => ['p.id' => 'asc'],
	];

	public function initialize() :void
	{	
                $this -> loadComponent('Flash'); //FLASHコンポネントの呼び出し
		$ses = $this -> request -> getSession(); //セッション制御

		$teams = [
			"1" => "Hawks",
			"2" => "Lions",
			"3" => "Marines",
			"4" => "Eagles",
			"5" => "Fighters",
			"6" => "Buffaloes",
			"7" => "Giants",
			"8" => "Tigers",
			"9" => "Dragons",
			"10" => "Baystars",
			"11" => "Carp",
			"12" => "Swallows",
		];
		$leagues = [
			''=>'なし',
			'Central' => 'Central',
			'Pacific' => 'Pacific',
		];
		//結合して作ったテーブル
		$t_persons = $this -> Persons->query()->
			select([
				'id'=>'p.id',
				'name' =>'p.name',
                'team' => 't.name',
                'team_id' => 'p.team_id',
				'league' => 't.league',
				'uniform_number' => 'p.uniform_number',
			])->
			from([
				'p' => '`persons`',
			])->
			join([
				'table'=>'`teams`',
				'alias' => 't',
				'type'=>'INNER',
				'conditions' => ['t.id = p.team_id'],
			]);
		$this -> set("teams",$teams);
		$this -> set("leagues",$leagues);
		$this -> t_persons = $t_persons;
		$this -> ses = $ses;
	}
    //一覧
    public function index()
    {
		$persons = $this -> persons;
		$t_persons = $this -> t_persons;
		$sql = $this -> sql;
		$ses = $this -> ses;
        //検索条件が入力されている場合
		if( $this -> request -> is(['put'])){
			$ar_data = $this->request->getData(); //フォームの値
        //ページを遷移した場合
		}elseif($this -> request -> is(['get'])){
			$ar_data = $ses -> read('find'); //検索条件の引継
			$ses -> delete('find');
		}else{
			$ar_data = [];
		}
		if($this->request->is(['put','get']) ){
			$ar_where = [];
            //検索条件
			if(isset($ar_data['find'])&& $ar_data['find'] != NULL) $ar_orwhere['p.name like'] = "%{$ar_data['find']}%";
			if(isset($ar_data['team'])&& $ar_data['team'] != NULL) $ar_orwhere['t.id'] = $ar_data['team'];
			if(isset($ar_data['league'])&& $ar_data['league'] != NULL) $ar_andwhere['t.league'] = $ar_data['league'];
			if(isset($ar_orwhere) && $ar_orwhere != NULL) $ar_where["OR"] = $ar_orwhere;
			if(isset($ar_andwhere) && $ar_andwhere != NULL) $ar_where[] = $ar_andwhere;
			$sql = $t_persons -> where($ar_where);
			try{
				$persons = $this -> paginate($sql);
			}catch(NotFoundException $e){
				$persons = [];
				$persons = $this->redirect(['page'=>1]);
			}
			$ses -> write(['find'=> $ar_data,'sql'=> $sql->sql()]);
		}else{
			$persons = $this->paginate($this->t_persons);
		}
        $this->set(compact('persons'));
		$this -> sql = $sql;
		$this -> ses = $ses;
    }
    //詳細
    public function view($id = null)
    {
		$persons = $this -> t_persons -> where([ "p.id" => $id ]);
		foreach($persons as $person);
        $this->set(compact('person'));
    }
    
    //新規作成
    public function add()
    {
        $person = $this->Persons->newEmptyEntity();
        if ($this->request->is('post')) {
            $person = $this->Persons->patchEntity($person, $this->request->getData());
            if ($this->Persons->save($person)) {
                $this->Flash->success(__('データを追加しました'));
                return $this->redirect(['action' => 'index']);
            }else{
                $this->Flash->error(__('データ追加に失敗しました'));
            }
        }
        $this->set(compact('person'));
    }

    //編集
    public function edit($id = null)
    {
		$persons = $this -> t_persons -> where([ "p.id" => $id ]);
		foreach($persons as $person){};
		if ($this->request->is(['patch', 'post', 'put'])) {
			$person = $this->Persons->patchEntity($person, $this->request->getData());
			if ($this->Persons->save($person)) {
				$this->Flash->success(__('データを更新しました'));
				return $this->redirect(['action' => 'index']);
			}else{
			    $this->Flash->error(__('データ更新に失敗しました'));
                        }
		}
		$this->set(compact('person'));
    }

    //削除
    public function delete($id = null)
    {
        $this->request->allowMethod(['post', 'delete']);
        $del = $this->Persons->get($id);
        if ($this->Persons->delete($del)) {
            $this->Flash->success(__('削除しました'));
        } else {
            $this->Flash->error(__('削除に失敗しました'));
        }

        return $this->redirect(['action' => 'index']);
    }
}

こちらがビューとなります。cakePHP3と異なり、ctpファイルではなく、phpファイルで処理されます。

index.php
<div class="persons index content">
    <?= $this->Html->link(__('新規作成'), ['action' => 'add'], ['class' => 'button float-right']) ?>
	<?= $this->Form->create($persons,['action'=>'Persons']) ?>
	<?= $this->Form->input('find') ?>
	<?= $this->Form->select('team',$teams,['empty'=> 'チームを選択']) ?>
	<?= $this->Form->radio('league',$leagues,['value'=> NULL]) ?>
	<?= $this->Form->button(__('Submit')) ?>
	<?= $this->Form->end() ?>
    <h3><?= __('球団マスコット') ?></h3>
    <div class="table-responsive">
        <table>
            <thead>
                <tr>
                    <th><?= $this->Paginator->sort('id') ?></th>
                    <th><?= $this->Paginator->sort('名称') ?></th>
                    <th><?= $this->Paginator->sort('所属チーム') ?></th>
                    <th><?= $this->Paginator->sort('背番号') ?></th>
                    <th class="actions"><?= __('Actions') ?></th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($persons as $person): ?>
                <tr>
                    <td><?= $this->Number->format($person->id) ?></td>
                    <td><?= $this ->Text -> Autoparagraph(h($person->name)) ?></td>
                    <td><?= $this ->Text -> Autoparagraph(h($person->league)) ?></td>
                    <td><?= $this->Text -> Autoparagraph(h($person->uniform_number)) ?></td>
                    <td class="actions">
                        <?= $this->Html->link(__('詳細'), ['action' => 'view', $person->id]) ?>
                        <?= $this->Html->link(__('修正'), ['action' => 'edit', $person->id]) ?>
                        <?= $this->Form->postLink(__('削除'), ['action' => 'delete', $person->id], ['confirm' => __('Are you sure you want to delete # {0}?', $person->id)]) ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>
    <div class="paginator">
        <ul class="pagination">
        <?= $this->Paginator-> numbers([
            'before' => $this->Paginator->hasPrev() ?
                $this->Paginator->first('<<') . '・' : '',
            'after' => $this->Paginator->hasNext() ?
                '・' . $this->Paginator->last('>>') : '',
            'modulus' => 4,
            'separator' => '・'
        ]) ?>
		</ul>
        <p><?= $this->Paginator->counter(__(' {{pages}}ページ中、{{page}}ページを表示しています。{{start}}件目から{{end}}件目まで表示。（ 全{{count}} レコード中 {{current}}件ずつ表示）')) ?></p>
    </div>
</div>

こんな感じで動いています（現在の検索条件はaを含む、かつセ・リーグ）。

cakephp.jpg

ページャーもちゃんと動いています（現在の検索条件はセ・リーグで、2ページ目に遷移した状態。検索をやり直した場合は1ページ目に戻る制御もしています）。
cakephp2.jpg

弊害（ソート機能）に対応

さて、この表別名の指定ですが、思わぬ弊害が発生します。それはソート機能がうまく機能しなくなるというもので、具体的に言うと、cakePHPのsortメソッドは自動でソートしてくれるのですが、それにはSQLに

order by テーブル名.対象カラム=ソート方向

と自動的に付与してしまう機能なので、表別名を指定した場合、order by句も表別名で指定しなければいけないため、SQLの記述ルール違反でエラーが生じてしまうのです。

つまり

 order by p.id asc;

とならなければいけないところ

 order by Persons.id asc; //表別名に指定しているので、テーブル名も別名で指定しないといけない！

となってしまいます。

対応策

では、この現象の対応方法を説明していきます。

手順１：リスト表示とパラメータ用のカラム表示の振り分け

ひとまずはパラメータに表示させるカラム名をテーブルと合わせないといけません。そのため、現状のままだとリスト表示用の日本語が入ってきてしまいます。なので、リスト部分を以下のように書き換えます。

$this -> paginator -> sort(カラム名,リスト名)

index.php
                <tr>
                    <th><?= $this->Paginator->sort('id') ?></th>
                    <th><?= $this->Paginator->sort('name','名称') ?></th>
                    <th><?= $this->Paginator->sort('team','所属チーム') ?></th>
                    <th><?= $this->Paginator->sort('uniform_number','背番号') ?></th>
                    <th class="actions"><?= __('Actions') ?></th>
                </tr>

手順２：ソート対象カラムのホワイトリスト化

次は勝手に表名を取得する現象を回避しないといけません。それには表別名をパラメータに覚えさせる必要があります。そのために必要な作業はソート対象のカラムをまずはホワイトリスト入りさせることで、これを行うと指定した名称のみがソート対象となります。

PersonsController.php
	public $paginate = [
		'limit' => 10,
		'sortWhitelist'=>['p.id','p.name','p.team','p.uniform_number'],
	];

こうすれば、表別名がソート対象となってくれるので、SQL生成時にエラーが発生しなくなります。

手順３：GETパラメータの取得

ただ、今度はこのパラメータ通りに、手動でorder by句を生成する必要があります。そのためには、ひとまずソート用のパラメータを取得しなければいけないのですが、cakePHP4の場合はgetQueryメソッドでGETパラメータを取得できます。そして、対象カラムを取得するsortキーとソート方向を示すdirectionキーにそれぞれ値が格納されます。

PersonsController.php
//中略
	}elseif($this -> request -> is(['get'] )){
		$order = [];
		$order = $this->request->getQuery(); //パラメータの値
		if(isset($order['sort'])){
			$sort = $order['sort']; //ソート対象のカラム
			$dir = $order['direction']; //ソート方向
		}
	        $ar_data = $ses -> read('find'); //前検索条件の呼び出し
	}else{

手順４：ORDER BY句の手動生成

次にorder by句を手動で生成ですが、パラメータは取得してこない場合もありますので、if文で有無を判定しておきましょう。こうしないと、パラメータを取得していない場合にSQL構築のレンダリングエラーを起こし、プログラムが止まってしまいます。

PersonsControlller.php
//中略
	$sql = $t_persons -> where($ar_where); 
	if(isset($order['sort'])) $sql->order([$sort=>$dir]); //order by句の付与
	//var_dump($sql->sql()); //デバッグ用
	$persons = $this -> paginate($sql);
//中略

手順５：ソート方向の切換

このままだとソート方向が一方通行なので、切換用の制御もしておきます。

まずはコントローラにascとdescが切り換えられるようにしてから、ビューに返すためにキーをカラム名にして変数$dirに代入するように制御します。あと、最初から$dirに使用するキー名と値を準備しておきましょう。そうしないとundefined indexというエラーが表示されてしまいます。

PersonsController.php
//中略
		$dir = ['id'=>'asc','name'=>'asc','team'=>'asc','uniform_number'=>'asc'];
		if( $this -> request -> is(['put'])  ){
			$ar_data = $this->request->getData(); //フォームの値
		}elseif($this -> request -> is(['post'] )){
			$ar_data = $this->request->getData(); //フォームの値
		}elseif($this -> request -> is(['get'] )){
			$order = [];
			$order = $this->request->getQuery(); //パラメータ取得
			if(isset($order)){
				$sort = $order['sort'];
				$dir[$sort] = ($order['direction']== "asc")?"desc":"asc";
			}
			$ar_data = $ses -> read('find'); //前検索条件の呼び出し
		}else{
			$ar_data = [];
		}

これでコントローラー上の制御はできましたが、今度はそれをビューに返します。compact関数で返しましょう。

PersonsController.php
        $this->set(compact('persons'));
	$this -> set(compact('dir')); //ソート用の制御をビューに返す
	$this -> set('models',$ar_data);

そして、ビューのsortメソッドの第三引数にソート方向を取得できるようにします。

index.php
        <th><?= $this->Paginator->sort('id','id',['direction'=>$dir['id']]) ?></th>
        <th><?= $this->Paginator->sort('name','名称',['direction'=>$dir['name']]) ?></th>
       <th><?= $this->Paginator->sort('team','所属チーム',['direction'=>$dir['team']]) ?></th>
        <th><?= $this->Paginator->sort('uniform_number','背番号',['direction'=>$dir['uniform_number']]) ?></th>
        <th class="actions"><?= __('Actions') ?></th>

これで、表別名を使用しても問題なくソートも機能するようになります。

結論

結局、cakePHPのクエリメソッドが遅いというより、クエリメソッドの説明が曖昧だった上に、テーブル別名定義において説明が見当たらないことでSQL構築が不十分のままクエリを実行しているため、データ処理が遅くなっている可能性は十分考えられます。

cakePHP4は3より更に構築しやすくなっている(CRUD機能を一瞬で自動生成してくれるコマンドがある)ので、cakePHPerとしては少しでも勢いを盛り返してくれるといいなと（Laravelもビューが簡潔なので気に入ってますが、マイグレーションの制限の多さとクエリ構築の面倒さについては、少しはcakePHPの簡潔さを見習ってほしい）。

このCRUDの作成についてもまた別記事を作れたらと思います。

5
6
1

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
5
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?