Backlogの自分の課題をPHPで取得

Backlogの自分の課題をPHPで取得

backlogロゴ

お久しぶりです。

横田です。

アウローラのシステム部では、バックログを使って課題管理してます!バックログはユーザービリティーに優れててすごく使いやすく、社内でのシステム開発にはなくてはならないツールです!

ただ、課題表示の件数に制限があるので、部署全員の個人タスクをひと目で確認したい時などは、少し不便でした。

良い解決方法がないか探してるときにバックログにはAPIが用意されてることを知りました。そこでAPIを使って、対象のユーザーの課題を取得するスクリプトを作りました。

この記事では、そのときに作った対象のユーザーの課題を取得するPHPスクリプトの紹介をします!

用意するもの

まずプロジェクトフォルダ作成、composer.jsonを用意

backlogApp
└── composer.json

composer.jsonの中身

{
    "require": {
        "atomita/backlog-v2": "*"
    }
}

プロジェクト内で以下のコマンドを実行しBacklog-v2をインストール!

composer install

PHPスクリプト

プロジェクトの構成はシンプルにこんな感じです!

backlogApp
├── index.php
├── vendor(Backlog-v2ライブラリー)
└── composer.json

index.phpにBacklog-v2ライブラリーを使ってデータ取得するロジックを書いていきます!

<?php
require "./vendor/autoload.php";

use \atomita\Backlog;
use \atomita\BacklogException;

$backlog = new Backlog('スペース名', 'APIキー');
try{
    $data = array(
        "assigneeId"=>array('ユーザーID')
    );
    $task = $backlog->param('issues')->get($data);
    echo "<pre>";
    print_r($task);
    echo "</pre>";
}
catch(BacklogException $e){
    // error
}
?>

上記のスクリプトを実行で指定したユーザーIDの担当課題一覧が取得できます!

感想

BacklogAPIには、他にも

  • ユーザーの一覧
  • 課題のステータス

など

色んなデータが取得できるAPIが用意されてるみたいです。

今後スプレッドシートとの連動など、APIを連携することで業務の効率化につながるかもと思いました。

興味がある方は是非使ってみてください!

【歴史の】弊社サービスがCakephpを採用している理由【始まり】

中武です。

弊社の主要WEBサービスは、その多くがPHPフレームワークCakePHPで作られています。

CakePHPを良く採用する理由は、最初に作った自社サービスがCakePHP(ver.1.1)だったから。というシンプルな理由です。

私はどのようにして自社サービスにCakePHPを採用したのか。

弊社がWEBサービスの開発を始めたのは約9年前の2008年からです。

その頃の私は、主に大手の業務システムや、基幹系システムのバックグラウンド開発を主として業務しており、JAVAC#VB.net(当時はVB6もまだまだ現役。今は?)等の言語の開発が多く、PHPの経験は個人でどうでも良いHPを作ってみたくらいで、業務経験はゼロ。という状態でした。

案件も規模の非常に大きいプロジェクトの中の一部分を開発。というものが多く、中規模のWEBサイトを一人でゼロから開発すること自体、未経験でしたので、フレームワークの選定には非常にとまどった記憶があります。

2008年当時、主流だったPHPフレームワークは、主に以下の3つ。

それぞれの比較は、書籍を購入してちょっとしたサンプルを作って見たり、以下のようなページを読み漁って検討を行いました。

http://www.phppro.jp/article/framework/comparison.php

検討を重ねた結果、以下の理由でCakePHPの採択と相成りました。

<script>
    frameworkList = new Array(
      'CakePHPだよ'
      ,'symfonyだよ'
      ,'zendだよ'
      ,'まさかのRuby on Rails'
      ,'まさかの自前'
      ,'smartyだけは嫌なのでやり直し'
      );
    function test(){
      selRnd = Math.floor( Math.random() * frameworkList.length );
      val = frameworkList[selRnd];
      document.getElementById("test").innerHTML = val;
    }
    </script>
<input type="button" value="test" />

<span id="test"></span>

update 2008/10/11

あくまでもツールはツール(手段)であり 大事なのはユーザにどのように魅力的なコンテンツを届けるか。ということですね。 (雑なソースはご愛嬌。。)

最強最速の帳票ツールExcelVBAを使いこなせ (2) VBAのマクロの警告をさける手軽な方法

アウローラで1番Excel VBAを知り尽くしている前山です。 弊社での営業支援システムで使われている、古くて新しいExcel VBAの技術(前山が試行錯誤したもの)を紹介します。

ExcelVBAを使いこなせ(1)- 知られざる入力補完 ExcelVBAを使いこなせ(2)- VBAのマクロの警告をさける手軽な方法

Excelマクロ、って、同じ作業を繰り返し実行するための仕組みです。 マクロの動作を更に確実に便利にコントロールできる物が Excel VBAという Visual Basicを拡張したプログラミング言語になります。 これを駆使すると、CSVファイルを読み込んで「マクロを実行」することで、毎月作成する帳票が自動で作成できたりします。

今回はこの「毎月自動で作成した帳票をセキュリティの警告なしで配布する」方法を共有します

→ すなわち、マクロ付きExcelファイルと配布用のデータ・帳票Excelファイルを分離するということについて説明します

WebサービスExcelマクロの相性の悪さ

業務系のWebサービスで、良くある機能がCSVファイル。 こちら、CSVファイルが欲しいと言うよりも、データをダウンロードしてそのデータをローカルで色々と編集したい。という要件に基づくはずです。

本来ならばデータの編集・分析、その共有もWeb上で行えればいいのですが、それなりにつくりこまれたWeb上の分析ツールって計画・実装も大変だし、使う側にとっても学習コストが高い割には手慣れているExcelの機能が使えなくてストレスとなる。

ということが良くあるかなと。

その落としどころが「CSVのダウンロード機能」なのかなと。思います。

業務でCSVを定期的にダウンロードする。ということは、Excelによる定型作業がその先にある。というケースが半分以上あるのではないかと考える次第です。

そこで、「Excelでの定型作業」と来ればExcelマクロ、Excel VBAの出番となる。わけですが・・

  1. CSVファイルをExcelで開いて、そのデータをマクロ付きExcelファイルにコピーしてそこで,集計
  2. CSVファイルをテキストエディタで開いて、テキストをマクロ付きExcelファイルにコピーし、「区切り位置」機能で調整(0~始まる電話番号とかを取り込むとなる必須ですね)、その後マクロ実行
  3. 定型作業で使うExcelマクロが決まっているのならば、マクロ付きExcelファイルをサーバー上に保持して、そのExcelのシートに値を書き出してダウンロードする

を行うと。その結果を他の人に共有するのが難しくないですか?

古い話ですが、昔はマクロ付きのExcelファイルを配っても「危険だ」とか言われることなくマクロの実行が可能でした(コンピューターウイルスが社会問題になる前) 現在は、Excelマクロが実行可能なファイルを他の人(例えば会社の別の部署の方)に開いてもらおうとすると、開く度に警告が出ます。 結果として、「集計するのは自分だけ」「集計結果を他の人に共有したい」といった場合でも、「セキュリティの警告」を表示される。といったことになります。

snipping_44459.png

セキュリティの警告を無条件に出さないようにする

まずは、いろいろなマニュアルに有る「マクロ付きファイルを開いても警告を出さないようにする」方法です。 メニュー:「ファイル」より、バックステージビューを開き、 snipping_45931.png 「オプション」から「セキュリティセンター」を開きます snipping_5015.png 更に「セキュリティセンターの設定(T)...」のボタンを実行すると snipping_516.png

「マクロの設定」を変更することができます snipping_5143.png

ここで「すべてのマクロを有効にする(推奨しません。危険なコードが実行される可能性があります)(E)」を選択すると、無条件にマクロ付きファイルを開く事ができます。

とはいえ、推奨される物ではありません。

マシな選択肢としてマクロに「デジタル署名」をしてある場合、「デジタル署名されたマクロを除き、すべてのマクロを無効にする」を選択されている端末だと警告なしでExcelマクロが実行可能です。 かといって、この設定をするのに、「セキュリティセンター」にたどり着く必要があるため、この時点で既にハードルが高い気がします・・

そこで、マクロ付きファイルとデータ・帳票ファイルをわける

各端末でマクロを実行してもらう必要がある場合は「Excelアドオン」にして、配布するという方法が一番手軽(だと信じています) 「Excelアドオン」として「他部署の方にマクロで実装した機能を安全に動かす」ことについては別の機会にお話しするとして、今回はその一つ前の段階となる 「マクロは開発した自分の所だけ動かせば良く、他部署の人にはマクロなしの「データ」「帳票」ファイルを配れば良い」 ということを説明します。

「標準モジュール」と「Microsoft Excel Objects」の大きな違い

snipping_5950.png いきなり、VBAエディタの話からです。

ExcelファイルにいきなりVBAを書こうとすると「Microsoft Excel Objects」というシートと同名のオブジェクト、及びWorkBookが並んでいます。

例えば、シート1を操作するからといって、シート1のオブジェクトにVBAをコーディングしていく。まあ、これでも動きます。

この状態では、次回以降のマクロの実行のためには、必要なデータをそのシート上にコピーしてマクロを実行する必要があります。

毎月新たに更新されるデータCSVなどに対してマクロを実行して、新しいシートを作成する。といった作業の場合は「標準モジュール」に作成する様にしましょう。 (「マクロの記録」でも、標準モジュールにマクロが記録されていきます)

シートオブジェクトに記載されたソースコードは、そのシートが削除されると一緒に削除されていまいます。

データが含まれるマクロなしファイルに、マクロで帳票シートを生成する

やりたいことは、決まった形で受け渡されるデータファイルから帳票を自動で作成すること。 マクロ付きExcelにデータを読み込ませるかコピーするかして、マクロを実行して別シートに帳票を作る。という方法が一般的にとられていると思いますが、この記事でお薦めしたいのは

  1. マクロ付きのExcelファイル(例:DataContoller.xlsm) を開く
  2. マクロ付きExcelファイルを開いた上で、データを含むマクロなしのExcelファイル(2017Oct.xlsx)を開く
  3. Datacontroller.xlsmのマクロを実行する
  4. データを含むマクロなしExcelファイル(2017Oct.xlsx)に新しいシートで帳票を整形する

です。帳票が新たに作成されたExcelファイルにはマクロがないため、開くときに警告が出ません。

そのためには以下の考え方でマクロをプログラミングするといいでしょう

'メインロジック
Public Sub main()
    Dim macroBook As Workbook
    Dim dataBook As Workbook

    Set dataBook = ActiveWorkbook
    Set macroBook = ThisWorkbook

    Dim dataSht As Worksheet
    Dim trgtSht As Worksheet
    ‘ 具体的な処理を記載する    



End Sub

ActiveWorkbookが最後に開いたExcelファイルなので、dataBook = ActiveWorkbook で、その読み込み対象となるWebからダウンロードなりしたExcelファイルが取得できます。 帳票の元となるテンプレートシートをマクロ側のExcelに持ったりしている場合、そのマクロを実行している"ThisWorkbook"を Set macroBook = ThisWorkbook として取得すろと、macroBook.Sheet(“goal”)を dataBookにコピーして、そこにdataBookのデータを集計して転記することも可能です。下のような感じですね。

    Dim baseSht As Worksheet
    Set dataSht = dataBook.Sheets("目標")
    macroBook.Sheets("goal").Copy before:=dataSht

とはいえ、マクロを実行するときに DataController.xlsmをアクティブにしてマクロを実行すると、ActiveWorkBookがマクロ付きファイルになってしまいます。

そこは、マクロなしExcel(配布したい方)の「開発」メニューから「マクロ」を呼び出すことで、現在開いているExcelファイルから「実行可能な」マクロを実行する事ができます。 snipping_8223.png

さらに便利なのが、マクロにショートカットを割り当てること snipping_82113.png

ここで、 ctrl+1とかにマクロ実行を割り当てると、データファイル側で"ctrl+1"を呼び出すとマクロが実行できます(ctrl+sなどExcel側で割り当てているものに被らないように要注意)

記録されたマクロベースで開発をすると、ActiveSheetというオブジェクトをそのまま、多用したり、無条件で現在アクティブなセルに対しての操作を使いがちです

Sub Macro()
'
' Macro Macro
'

'
    Range("F7:I16").Select
    Selection.Copy
    Range("M16").Select
    ActiveSheet.Paste
End Sub

そこをあえて、

    Set dataBook = ActiveWorkbook
    Set macroBook = ThisWorkbook

と意識してThisWorkbookを使い、Data側のExcelとマクロを保持するExcelを分けた開発をすることがこの方式では大事です。

(明示的にDimやSetを使うと入力補完が便利に使えます。詳しくは前回の記事をご参照ください)

結局、マクロの警告を避けるには?

ごめんなさい、「実行時に」マクロの警告を避ける方法。ここで紹介した

  • デジタル署名をつけたうえで、セキュリティセンターで設定を変える
  • ともかくすべてのマクロが実行できるようにセキュリティセンターで設定を変える については、そんなに手軽ではないです。

その代わりに、「実行時」だけ、マクロの警告がでるものの他人と共有する際は警告が出ない方法はかなりおすすめです。 なんだったら、よく使うマクロだけ集めた MyVBA.xlsmみたいなファイルを一つ作り、それを常に立ち上げておく。とか行うといろいろなExcelドキュメントで(マクロ付きドキュメントではない)オリジナルの便利機能が利用できるので試してみてはいかがでしょうか?

この考えを推し進めたものがExcelアドインというものです。 そして、ここではマクロ実行とかショートカットでマクロを実行していましたが、これを手軽にメニュー(正式名称はリボン)に表示させることもVBAではサポートされています。 そこまでやれば「実行時にマクロの警告を避ける」という意味ではほぼベストな状況が作れるのではと思っていますが、そちらについてはまた次回の連載(?)で

Selenium環境構築してWebの自動テストをしてみる

こんにちは、びーちゃんといいます。ちなみになぜびーちゃんか?といいますと特に理由がなくびーちゃんと名づけられました(笑)。

QAエンジニアとして入社しまして日々テストケース作成、テスト実施などの品質管理業務を行っています。新しい機能を実装したときに既存の機能が正常に動くことを保障するということは非常に重要な課題です。例えば応募フォームが毎日正常に動いているかの確認など。そんな中目をつけたのがSeleniumSeleniumでブラウザ上での自動実行が可能になります。自動実行できると何が良いのかというと、同じ手順を繰り返しテスト実施するなど人の手で行うのは非常に工数がかかる作業も「正確に」「手早く」行うことができます。 また、Jenkinsと連携することによって定期的にテストの自動実行が可能となり、Seleniumはリグレッションテストにとても役立つツールです。

ということで、今回はWindowsでのPHPunit-Seleniumの環境構築と簡単なWeb自動テスト実行までの方法をご紹介したいと思います。

JavaSDKインストール

JavaSDKインストール

からローカル環境に合わせたインストーラをダウンロードしてインストールする。 これはPHPなのに何で必要なの?って感じだけどSeleniumServerを起動するために必要です。

XAMPPインストール

XAMPPインストール

からXAMPPをダウンロードしてインストールする。

Composerのインストール

Composerを使用しない方法もあるのですが嵌る箇所がかなりあったのでComposerを使用することでカンタンにSeleniumパッケージのインストールすることができます!

Composerインストール

からComposer-Setup.exeをダウンロードしインストール。とくに難しいことはなくNextを押していけばOK。

Composerを使用してPHPUnit,Seleniumパッケージの追加

先ほどインストールしたComposerでSeleniumパッケージを入れます。まずcomposer.jsonを作成します。

{
    "require": {
        "phpunit/phpunit": "4.3.*"
         ,
        "phpunit/phpunit-selenium": ">=1.2"
    }
}

あとは作成したjsonでcomposer requireすればパッケージがインストールされます。

SeleniumServerインストール

SeleniumServerインストール

からSelenium Standalone SeverのJARファイルをダウンロードし、適当なディレクトリに配置します。 JARファイルを配置後、

java -jar selenium-server-standalone-○.○.○.jar(○にはダウンロードしたver)

コマンドプロンプトでコマンドを叩けばSelenium Server Runningと表示されSeleniumServerが立ち上がります。

テストしてみる

上記で環境構築完了です!というわけでカンタンなテストケースを作成して実行してみる。

<?php
require_once("/vendor/autoload.php");

class Example extends PHPUnit_Extensions_SeleniumTestCase
{
        protected function setUp()
        {
                $this->setHost('○.○.○.○');  //Selenium Server IP
                $this->setPort(4444);
                $this->setBrowser("firefox");
                $this->setBrowserUrl("https://www.google.co.jp");
        }

        public function testMyTestCase()
        {
                $this->open("/");
                $this->type("id=lst-ib","test");
                $this->type("name=btnK");           
        }
}
?>

phpunit test.phpを実行するとgoogleWebサイトを開いて「test」という単語を自動で検索するテストケースの完成。以上、Seleniumの環境構築から簡単なWeb自動テスト実行までのお話でした。

Windows + vagrant環境で大小文字が区別されない問題

アウローラでは、開発環境にWindows&MAC + vagrant + virtual boxを用いています。今回はこの開発環境にてある問題点が上がり、その問題点の解決方法に関して共有させていただきます。 その問題とは、ファイル名の大小文字が区別されないという問題です。 詳しく説明させていただきます。

問題が発覚した経緯

問題が発生した経緯に関しては、Windows端末での開発(Windows + Vagrant + Linux)では正常に動いていたアプリケーションがLinuxで動作しているプロダクション環境(ステージング環境)にデプロイを行ったところ、アプリケーションの不具合が起きてしまいました。(;´Д`)アウ...

原因

原因はphpのrequire_once文、include文で読み込んでいたファイルと実ファイルの大文字と小文字で違っていたためでした。(;´д`)トホホ。。 なぜ開発環境で正常に動いて、プロダクション環境では動かなかったのでしょう。

開発環境とプロダクション環境で挙動が違った原因

問題は開発環境にありました。 ホストOS側のファイルシステムが大小文字を区別しない場合、ゲストOSがLinuxのような大小文字を区別するファイルシステムでも、結果的には大小文字が区別されなくなります。 つまり、ホストOSがWindowsMac OSの場合、VirtualBox上の仮想マシンでも大小文字が区別されないのです。 結果として、上記のように開発環境では正常に動作していたが、プロダクション環境にあげたとたん動かなくなるという挙動をおこしてしまいます。

解決方法

解決方法に関しては2通りあります。 2通りともvagrantのSync Folderのタイプを変える事によって実現可能になります。 何も指定していない場合(vagrantfileに)defaultのVirtualBoxタイプが使用されます。 これは、VirtualBoxの共有フォルダ機能になります。VirtualBoxタイプだと大小文字を区別してくれないので、別のタイプに変更してやります。

  • 1つ目は、rsyncタイプを使用する。 これはVirtualBoxタイプのようにマウントするのではなく、ホストOS側のファイルをゲストOSへファイル転送(ファイル同期)を行うだけなのでゲストOS側のネイティブなファイルシステムが利用できます。 よって、大小文字を区別可能になります。 ただしrsyncタイプは、ホストOSからゲストOSへ一方通行なのでゲストOSで生成されたファイルはホストOSへ同期されません。 例えば、エラーログ等はゲストOS側のみで生成されるため、いちいちsshで接続して確認する必要があり少し手間ではあります。 後、初回の起動時に全ファイルのrsyncを行うため、同期対象のファイルが多い場合、起動にかなりの時間がかかります。 また、Windows端末ではデフォルトでrsyncコマンドが使用できないためCygwin等をインストールする必要があります

  • 2つ目は、nfsタイプを使用する。 nfsとはネットワーク上のファイルにアクセスするための、プロトコルです。 これはVirtualBoxタイプと同様にファイルをマウントしてホストOSとゲストOS間のファイルの同期を行います。 ただ、NFSプロトコルでは大小文字の区別を行うので、ゲストOSのネイティブファイルシステムが使用できます。 ホストOSがWindows端末であれば、デフォルトでNFSでのマウントができません。そのためvagrantの専用プラグインWindowsでもnfsマウント使用できる)をインストールする必要があります。 また、NFSVirtualBoxのHostOnlyNetworkでしか、使用できません。そのためブリッジアダプターやNATを使用している方はマウント専用のイーサーネットを用意する必要があります。

必要な作業

  • rsyncタイプ
    • rsyncを使用可能にする(Mac OSはデフォルトで搭載済み、Windows端末はCygwin等を入れる)
    • vagrantfileのsync_folderの指定のみ
    • 一部抜粋 node.vm.synced_folder ".", "/var/www/html/", type: "rsync",owner: "vagrant", group: "apache",rsync__exclude: [".git/", ".idea/"]
  • NFSタイプ
    • vagrantnfsプラグインをインストールする vagrant plugin install vagrant-winnfsd
    • vagrantfileのsync_folderの指定&HostOnlyNetworkの指定
      • HostOnlyNetworkの指定 node.vm.network :"private_network" , ip: "○○○.△△△.◇◇◇.×××"
      • sync_folderの指定 node.vm.synced_folder ".", "/var/www/html/" , type: "nfs"

最後に

本件を解決するにあたり、参考にさせて頂いた記事及び公式サイトを下記にリストアップ致しました。

Percona Took Kitでビジュアルに実行計画を確認しよう

こんにちは、マイケルです。 チーム内ではDBおじさんとか呼ばれていますが、前職まではOracleなどの商用製品を扱っていたので、MySQLについてはまだまだ勉強中です。 そんな私が普段からとても不満に思っているのが、MySQLのEXPLAINコマンドです。 本来、実行計画はツリー構造であるはずなのに、MySQLのEXPLAINの結果は表形式で表示されるため、ツリーがわかりにくいと常々思っていました。 もちろん、MySQL WorkbenchのビジュアルEXPLAIN機能を使えばツリーで表示されますが、こちらはExtra列の情報などが表示されません。

そんなことを思いながら日々過ごしていたのですが、とあるトラブルについて調査していたときに、Percona Tool Kitなるサードパーティ製品があることを教えて頂きました。 なにやらMasterとSlaveでデータにズレがあっても同期ができるだとか(上記のトラブルはこれだったんですが)、slow-queryログの分析ができるだとか、ALTER TABLEをロック無しで実行できるだとか、MySQLの標準機能だけでは届かない、ムズ痒い所に手が届くようなツールっぽいです。 公式サイト( https://www.percona.com/ )のドキュメントを眺めていたところ、 pt-visual-explain という機能が目についたので色々試してみました。

前提

Percona Tool Kitはサードパーティ製品なので、当然MySQL Serverをインストールしただけでは入っていません。 別途インストールが必要です。 非常に便利な機能が沢山あるので、開発用サーバにはもちろん、本番用DBサーバにも事前に導入しておくことをお勧めします。 導入や使い方は公式ドキュメント( https://www.percona.com/doc/percona-toolkit/LATEST/index.html )を御覧ください。

サンプルテーブルの構造

実験用に手元のMySQL 5.6.24に以下のようなテーブルを作成しました。

所在地(location)テーブル、部署(dept)テーブル、社員(emp)テーブルです。 記事の最下部に、テーブル作成のDDL及びDMLを置いておきます。 手元で確認したい方はどうぞお使いください(主にうちの社員)

MySQLのEXPLAIN(単純なJOIN)

上記、3つのテーブルをJOINしたときのEXPLAINを確認してみます。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no;

SQLとしてはWHEREの絞り込みなしで、3つのテーブルをJOINするだけです。

結果は以下の通り。

+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+
| id | select_type | table | type | possible_keys           | key             | key_len | ref                | rows | Extra |
+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+
|  1 | SIMPLE      | l     | ALL  | PRIMARY                 | NULL            | NULL    | NULL               |    2 | NULL  |
|  1 | SIMPLE      | d     | ref  | PRIMARY,dept_loc_no_idx | dept_loc_no_idx | 5       | test.l.location_no |    1 | NULL  |
|  1 | SIMPLE      | e     | ref  | emp_dept_no_idx         | emp_dept_no_idx | 5       | test.d.dept_no     |    2 | NULL  |
+----+-------------+-------+------+-------------------------+-----------------+---------+--------------------+------+-------+

こうなりました。 ちょっとテキストの折り返しがひどいのでキャプチャした画像も置いときます。

今回はWHERE句の絞り込みがありませんので、必ずどこかのテーブルでフルスキャン(テーブル全件を舐める)が必要です。 Nested Loop Joinでは、最初にフェッチされるテーブルの行数が少ないほうが有利ですから、今回はlocationテーブルからスタートするのは予想通りです。 また、今回はJOINの条件で使われる列にはINDEXを作成していますので、順当にlocation → dept → emp とJOINをしていくはずです。 ということで、EXPLAINの結果は、上から順に読んでいけばいいと思えます。(id列の値が同じ場合の話。異なるid列の値がある場合は、id列の小さい順で処理されるとのこと)

では次の例ではどうでしょう。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no
WHERE
    d.dept_no = 10;

先程のSQLにWHERE句の絞り込みを追加しています。

ちなみにこの図、SQLで扱うテーブルのER図に、JOINの条件とかWHEREの絞り込み条件なんかを一緒に書き込んでます。 ごちゃっとしたSQLの解読が必要なときに、頭のなかを整理するのに便利なのでお勧めです。 結果は以下の通り。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

まずSQLで変わった点は、WHERE句に d.dept_no = 10 を追加したところだけです。 deptテーブルのdept_no列はPRIMARY KEYですから、等価条件で絞り込めば最大で1行しか結果を返しません。 つまり、Nested Loop Joinにおける起点のテーブルに最適なはずです。 EXPLAINの結果をみると、同じid列の値で上から順に、 dept → location → emp とJOINしており、やはり上から順に実行しているらしいと見て取れます。

では次に、WHERE句の条件を変更してみます。

EXPLAIN SELECT
    e.emp_no,
    e.emp_name,
    e.dept_no,
    d.dept_name,
    d.location_no,
    l.location_name
FROM
    emp e
INNER JOIN
    dept d
ON
    e.dept_no = d.dept_no
INNER JOIN
    location l
ON
    d.location_no = l.location_no
WHERE
    e.dept_no = 10;

絞り込みの対象をempテーブルへ変えました。 結果は以下の通り。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

empテーブルのdept_noにはINDEXがありますが、NON UNIQUEですので、 emp.dept_no=10 で絞り込んだ結果が複数行戻ります。 なので、少ない行のテーブルを起点のJOINをしようとする場合、empテーブルからスタートするのが最適かどうか微妙なところになります。 EXPLAINの結果を見ると、上から順に dept → location → emp と処理されたように見えます。

ではここで、EXLAINの中身をもう少し詳しくみてみます。 まず、一番上で処理されているdeptテーブルへのアクセスです。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

key にPRIMARY とありますので、dept_no列のINDEXを使用したということを表しています。 これっておかしくないですか? WHERE句の条件は empテーブルのdept_no列が10のもので絞り込みました。 それなのに、いきなりdeptテーブルのdept_no列が10のものを探そうとして、このINDEXを使ったように見えます。 本当にdeptテーブルから処理がスタートしているんでしょうか。

一方で、最後の行にあるempテーブルへの処理はこうなってます。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

empテーブルもdept_no列のINDEXを使っています。 この場合、上の業から順に処理した場合でも、empテーブルから処理した場合でも、どちらで考えてもこのINDEXを使うことはおかしくありません。

このような実行計画をみると、これは本当に上から順に読んで正解なのか?という疑問が出てきます。 公式ドキュメントにも、読む順番についての記述が見当たらないので、ずっと不安に思っていたところでした。 (もしドキュメントに説明があったら教えてください。)

Percona Too Kitのpt-visual-explainで見てみる

ここでPercona Took Kitを試してみます。 細かい使い方は公式ドキュメントを参照( https://www.percona.com/doc/percona-toolkit/LATEST/pt-visual-explain.html

まずは、deptテーブルで絞り込んだパターン 一々SQLをファイルに保存して、pt-visual-explainから呼び出すのが面倒なので、ワンライナーで渡しちゃいます。

mysql -u hoge -phuga dbname -e "EXPLAIN SELECT e.emp_no, e.emp_name, e.dept_no, d.dept_name, d.location_no, l.location_name FROM emp e INNER JOIN dept d ON e.dept_no = d.dept_no INNER JOIN location l ON d.location_no = l.location_no WHERE d.dept_no = 10" | pt-visual-explain

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          e
|  |  possible_keys  emp_dept_no_idx
|  +- Index lookup
|     key            e->emp_dept_no_idx
|     possible_keys  emp_dept_no_idx
|     key_len        5
|     ref            const
|     rows           5
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          l
   |  |  possible_keys  PRIMARY
   |  +- Constant index lookup
   |     key            l->PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        4
   |     ref            const
   |     rows           1
   +- Bookmark lookup
      +- Table
      |  table          d
      |  possible_keys  PRIMARY,dept_loc_no_idx
      +- Constant index lookup
         key            d->PRIMARY
         possible_keys  PRIMARY,dept_loc_no_idx
         key_len        4
         ref            const
         rows           1

おお、見事にツリー構造で表示されました。 実行計画のツリーは、ツリーの深い部分から処理されます。 ということはlocationテーブルとdeptテーブルのJOINをやってから、その結果とempテーブルをJOINしているということがわかります。 そして、locationテーブルとdeptテーブルでは、deptテーブルの方が下に出力されていますので、おそらく同じ階層の高さなら下から見ていけば良さそうです。(たまたまかもしれませんが) つまりこういう順での処理ですね。 1. deptテーブルから「dept_no = 10」のWHERE句の条件に合致する行を、PRIMARYのINDEXを使って1行取得 2. 1の結果の中のlocation_no列の値を使って、locationテーブルからPRIMARYのINDEXを使って1行取得 3. 1と2の結果をJOIN 4. 3の結果の中のdept_no列の値を使って、empテーブルからdept_no列のINDEXを使って5行取得 5. 3と4の結果を都度JOIN

とてもわかりやすい出力です。

では、WHERE句の条件をempテーブルのdept_noに対するものに変えてみます。

mysql -u hoge -phuga dbname -e "EXPLAIN SELECT e.emp_no, e.emp_name, e.dept_no, d.dept_name, d.location_no, l.location_name FROM emp e INNER JOIN dept d ON e.dept_no = d.dept_no INNER JOIN location l ON d.location_no = l.location_no WHERE e.dept_no = 10" | pt-visual-explain

結果は以下の通り。

JOIN
+- Bookmark lookup
|  +- Table
|  |  table          e
|  |  possible_keys  emp_dept_no_idx
|  +- Index lookup
|     key            e->emp_dept_no_idx
|     possible_keys  emp_dept_no_idx
|     key_len        5
|     ref            const
|     rows           5
+- JOIN
   +- Bookmark lookup
   |  +- Table
   |  |  table          l
   |  |  possible_keys  PRIMARY
   |  +- Constant index lookup
   |     key            l->PRIMARY
   |     possible_keys  PRIMARY
   |     key_len        4
   |     ref            const
   |     rows           1
   +- Bookmark lookup
      +- Table
      |  table          d
      |  possible_keys  PRIMARY,dept_loc_no_idx
      +- Constant index lookup
         key            d->PRIMARY
         possible_keys  PRIMARY,dept_loc_no_idx
         key_len        4
         ref            const
         rows           1

MySQLのEXPLAINコマンドで確認したように、やはりdeptテーブルからスタートしているようです。 つまり、先程の結果はやはりid列が同じものの中で、上から順に読んで正解だったということになります。

+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys           | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | d     | const | PRIMARY,dept_loc_no_idx | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | l     | const | PRIMARY                 | PRIMARY         | 4       | const |    1 | NULL  |
|  1 | SIMPLE      | e     | ref   | emp_dept_no_idx         | emp_dept_no_idx | 5       | const |    5 | NULL  |
+----+-------------+-------+-------+-------------------------+-----------------+---------+-------+------+-------+

なるほどなるほど。 ツリー表示されるとわかりやすいなー。

WHERE句で指定されたテーブルは?

はい、ここでまた先程の疑問です。 今回のWHERE句は 「WHERE e.dept_no = 10」 です。 empテーブルに対して絞り込みをしています。

それなのに、実行計画を見るとdeptテーブルから先に絞り込んでいます。 これは何が起きているのか。

という点については、実はoptimizer_traceという機能で、MySQLオプティマイザがどのようにSQLを最適化しているのかをみれば確認できますが、これについては別の記事で紹介したいと思います。 簡単にだけ言うと、以下のようなことをMySQLが考えて、SQLを組み替えてくれています。

  1. WHERE e.dept_no =10 っていう条件あるじゃん?
  2. empとdeptのJOINの条件が e.dept_no = d.dept_no じゃん?
  3. ということは、e.dept_no = 10 の結果でJOINするんだから、 WHERE d.dept_no = 10 と読み替えても一緒じゃん?

という感じで、今回はdeptテーブルからスタートした方が効率がよいと判断しているようです。 賢いですねー。 我々が多少へっぽこなSQLを書いても考えてくれているんですね。

まとめ

今回は、MySQLのEXPLAINの結果をツリー形式で表示してくれる pt-visual-explain というツールを紹介しました。 得られる情報としては、標準のEXPLAINと同じですが、ツリー構造が把握できる点と、お手軽に使える点がお勧めです。

EXPLAINの読み方については、処理順や、どのINDEXを使っているかだけでなく、他にも注目するべき情報が色々とありますが、そのあたりも別の記事で紹介できたらと思います。 また、このEXLPAINの結果を見た上で、どのような考え方でSQLを最適化していくのか、どのようなINDEXチューニングをすればよいのかもいつか説明したいと考えています。(主に弊社若手開発メンバー向けに)

サンプルスクリプト

CREATE TABLE location (
    location_no INT NOT NULL PRIMARY KEY,
    location_name VARCHAR(20));

INSERT INTO location VALUES(10, 'Shinjuku');
INSERT INTO location VALUES(20, 'Oomiya');
INSERT INTO location VALUES(30, 'Umeda');


CREATE TABLE dept (
    dept_no INT NOT NULL PRIMARY KEY,
    dept_name VARCHAR(30) NOT NULL,
    location_no INT);

CREATE INDEX dept_loc_no_idx ON dept(location_no);

INSERT INTO dept VALUES(10, 'Soumu', 10);
INSERT INTO dept VALUES(20, 'Keiri', 10);
INSERT INTO dept VALUES(30, 'Marketing', 20);
INSERT INTO dept VALUES(40, 'Eigyou', 30);
INSERT INTO dept VALUES(50, 'Kaihatsu', 30);


CREATE TABLE emp (
    emp_no INT NOT NULL PRIMARY KEY,
    emp_name VARCHAR(20) NOT NULL,
    tel VARCHAR(15),
    salary INT,
    dept_no INT);

CREATE INDEX emp_dept_no_idx ON emp(dept_no);

INSERT INTO emp VALUES(1, 'Furuse', '03-1111-2222', '1000000', 10);
INSERT INTO emp VALUES(2, 'Nakatake', '03-1111-2223', '500000', 20);
INSERT INTO emp VALUES(3, 'Maeyama', '03-1111-2224', '500000', 30);
INSERT INTO emp VALUES(4, 'Nakamura', '03-1111-2225', '500000', 40);
INSERT INTO emp VALUES(5, 'Takahashi', '03-1111-2226', '500000', 50);
INSERT INTO emp VALUES(6, 'Suzuki', '03-1111-2227', '500000', 10);
INSERT INTO emp VALUES(7, 'Yamada', '03-1111-2228', '500000', 20);
INSERT INTO emp VALUES(8, 'Yoshida', '03-1111-2229', '500000', 30);
INSERT INTO emp VALUES(9, 'Tanaka', '03-1111-2210', '500000', 40);
INSERT INTO emp VALUES(10, 'Satou', '03-1111-2222', '500000', 50);
INSERT INTO emp VALUES(11, 'Kondou', '03-1112-2222', '500000', 10);
INSERT INTO emp VALUES(12, 'Takeda', '03-1113-2222', '500000', 20);
INSERT INTO emp VALUES(13, 'Sasaki', '03-1114-2222', '500000', 30);
INSERT INTO emp VALUES(14, 'Aihara', '03-1115-2222', '500000', 40);
INSERT INTO emp VALUES(15, 'Katou', '03-1116-2222', '500000', 50);
INSERT INTO emp VALUES(16, 'Kimura', '03-1117-2222', '500000', 10);
INSERT INTO emp VALUES(17, 'Kobayashi', '03-1118-2222', '500000', 20);
INSERT INTO emp VALUES(18, 'Tachibana', '03-1119-2222', '500000', 30);
INSERT INTO emp VALUES(19, 'Chiba', '03-1110-2222', '500000', 40);
INSERT INTO emp VALUES(20, 'Tsuchida', '03-1111-2222', '500000', 40);
INSERT INTO emp VALUES(21, 'Tohno', '03-2222-2222', '500000', 10);
INSERT INTO emp VALUES(22, 'Nakano', '03-3333-2222', '500000', 20);
INSERT INTO emp VALUES(23, 'Nukumizu', '03-4444-2222', '500000', 30);
INSERT INTO emp VALUES(24, 'Nomura', '03-5555-2222', '500000', 40);

Wordpress対Joomla対Drupal

初めまして!システム部のミッチーです。フィリピン出身でまだ日本語勉強中です。現在、様々なWebサイトコンテンツマネジメントシステム(CMS)で作られている。CMSとはウェブコンテンツを構成するテキストや画像などのデジタルコンテンツを統合・体系的に管理し、配信など必要な処理を行うシステムの総称です。

CMSの世界シェアは以下の一覧の通りです。最も多く使われている上位3つは、ワードプレス、ジュームラ!、ドルーパルです。

「W3Techs.com, 28 August 2017 , 上位7位まで抜粋

ワードプレス

ワードプレスは最も人気のあるCMSで、理由は以下の通りです

  •  インストールしやすい- 多くのレンタルサーバやWebホスティングサービスで、自動的にWordPressをインストールする機能が提供されています。
  • カスタマイズしやすい- 他のCMSよりプラグイン、テーマなどが多い。
  • 無料- 沢山のテーマとプラグインが無料でインストールできる上、wordpress.comで無料ホストのオプションもあります。
  • サポート- 利用者数が多いので、サポートフォーラムでの質問にも回答してもらいやすいです。

夢みたいな話でしょう?しかし、他のCMSみたいに完璧ではないです。使いやすいのでサイトの構築を簡単に変えられません。

ジュームラ!

ワードプレスの次はジュームラです。簡単に言いますとワードプレスのフレキシブルタイプです。ワードプレスみたいに問題なくウエッブサイトを立ち上がれる。そして、ドルーパルの技術レベルほどではない。

どうしてジュームラ?

  • ECサイトー 他のCMSみたいにオンラインストアーをすぐ立ち上がれるけどワードプレスとドルーパルほどの努力と特別なカスタム化が不必要です。
  • 技術がほどほどー ジュームラはワードプレスの使いやしさとドルーパルの技術の間なのでサポートは余り必要がありません。
  • ヘルプポータルー ワードプレスより幅広くないですが確実にドルーパルのサポートオプションより安くて速いです。

ドルーパル

最後はドルーパルです。細かくカスタマイズが前提なので初心者に使いにくいです。

以下はドルーパルが傑出してる理由

  • 技巧的ー 技術はナンバーワンなので開発者にぴったりなCMSです。
  • パフォーマンスー ワードプレスとジュームラより資源集約ほどではないのでレスポンスが速い。
  • カスタマイズ化― プラグインとテーマが多い上、他のアプリケーション、システム、SQLXMLデータベースの統合は可能です。

ドルーパルは何でもできるCMSですが、使いこなすのは難しいです。このCMSにはHTML、PHPなどのプログラミング知識が不要ですが、エラーやトラブルシューティングにはプログラミング知識が役に立ちます。

まとめ

ワードプレスは使いやすいので初心者にピッタリ。

ジュームラはECサイトに良い。少しな技術が必要です。

ドルーパルは一番使いにくいけど一番パワーフルです。知識が必要特にHTML、CSS、とPHPです。

 

WordPress Joomla! Drupal
コスト 無料 無料 無料
ユーザビリティ ⭐⭐ ⭐⭐⭐
シェアー 59.4% 6.8% 4.7%

詳しくはwebsite.orgの記事を読んでください