アーカイブ

SQLの速度改善~MySQLのEXPLAIN

業務で使用するSQL

通常は、納品直後は問題ないのが殆どだけど、やっぱりデータ量によっては
徐々に重くなり、気が付けば1画面表示するのに何秒も掛かるケースもあります。

先日、調査したSQLも修正前は4s(秒)ほどだったのが0.028sに改善されました。
この時は、「MySQLのEXPLAIN」を使用してALLになっていた箇所をみて
インデックスをいくつか追加したのみでした。

その後、もう一件、他の一覧表示のSQL改善もしました。
16.8s→7.7sに改善
これはインデックスでは改善しなくて、SQLの取得方法を見直しました。
もう少し改善の余地もありそうでしたが、複雑なSQLにて見送りました。

使い方はいたって簡単。

普段書くSQLの最初にEXPLAINといれてから実行するだけ
EXPLAIN
SELECT XXX FROM stock_tbl WHERE stock_id = ? AND ・・・

その中でALLとなっているSQLを見直すだけです。
これでかなり改善されます。
インデックスで改善されないこともあるし複雑なSQLだと難しい場合もあります。
そもそもの構造の問題だったりデータ量の問題だったりもありますし
でも、やみくもにやるよりヒントがあるほうがやりやすいのかなと思います。

私は「A5:SQL Mk-2」のSQLエディタを愛用しています。
元同僚が作成したツールなんだけど、凄く良くできていて本当に使いやすい。
大手企業でも標準ツールとして紹介される程の優れもので
ERを作成したりDDL生成したりというのも出来て本当びっくりです。
確かに彼は、他のツールもチャチャチャッと作る人でした。
このSQLエディタでSQLのテストしてOKだったらJAVAソース用に秀丸で一括返還してJAVAに貼り付けしています。

重たいSQLだと効率が良いですし、テストの時も取得結果をエクセルにコピーするのも簡単で
凄く効率良くできるのが嬉しい。

EXPLAINもこのツールで表示されたので楽でした。
だからあまりコマンドでmysqlを起動することが滅多にないです。

AccessからMySQLデータバックアップ

Access + MySQL でシステムを稼働していて
定期的にMySQLデータをバックアップさせたいという要望があり
当初はメニュー画面にボタンを付けてと提案したのですが
画面終了時に毎回確認メッセージ表示してバックアップするしないを選択
させてバックアップをしたいという事でした。

でも何故か私の所では上手く動作しなくて(正しくバックアップファイル生成されない)
原因不明のままなのですが、お客様の環境では正常に動作しているというので
そのまま採用になりました。。

コードはこんな感じになりました。

メニューフォームのcloseイベント

Rem ----------------------------------------------------------------------------------
Rem         Close/閉じる
Rem ----------------------------------------------------------------------------------
Private Sub Form_Close()
Dim sMsg As String

On Error GoTo Error

    '===================
    ' バックアップ
    '===================
    sMsg = "バックアップを実行しますか?"
    If MsgBox(sMsg, vbInformation + vbYesNo + vbDefaultButton1, C_MSG_TITLE) = vbYes Then
        '*** バックアップ
        Call subDBBackUp
    End If

    ' access終了
'    Application.Quit
    DoCmd.Quit acQuitSaveNone

Exit Sub
Error:
    MsgBox "エラーが発生しました。エラー内容:" & ERR.Description & Chr$(10), vbCritical, C_MSG_TITLE
   
End Sub
Rem ----------------------------------------------------------------------------------
Rem         Click/終了
Rem ----------------------------------------------------------------------------------
Private Sub cmd終了_Click()
    DoCmd.Close
End Sub
</blockquote>

<blockquote>
Rem ----------------------------------------------------------------------------------
Rem     関数名   : subDBBackUp
Rem     処理内容 : MYSQLのバックアップをします
Rem     引  数  : なし
Rem     戻り値  : なし
Rem ----------------------------------------------------------------------------------
Public Sub subDBBackUp()
Dim objShell As Object
Dim strUser, strPass
Dim strPath As String
Dim strTimestamp As String
Dim strCmd As String

On Error GoTo ERR
    
    ' バックアップパス取得
    strPath = ReadINI("BACKUP", "BACKUP_PATH")
    strUser = ReadINI("MYSQL", "DB_USER")
    strPass = ReadINI("MYSQL", "DB_PASS")
    
    Set objShell = CreateObject("WScript.Shell")
    strTimestamp = Replace(CStr(Now()), "/", "")
    strTimestamp = Replace(strTimestamp, " ", "")
    strTimestamp = Replace(strTimestamp, ":", "")
    
    strCmd = "mysqldump -u " & strUser & " -p" & strPass & " " & C_DB_NAME & ">" & strPath & C_DB_NAME & strTimestamp & ".dmp"
    objShell.Run "cmd /c " & strCmd, vbNormalFocus, True

    Set objShell = Nothing

Exit Sub
ERR:
    MsgBox "環境設定ファイルを確認してください。" + Chr$(10) + "バックアップに失敗しました。", vbCritical
End Sub
Rem ----------------------------------------------------------------------------------
Rem     関数名   : ReadINI
Rem     処理内容 : INIファイルの情報取得
Rem     引  数  : Section,Entry
Rem     戻り値  : 無し
Rem ----------------------------------------------------------------------------------
Public Function ReadINI(Section As String, Entry As String) As String
  Dim n As String * 255
  Dim rc As Long
  
  rc = GetPrivateProfileString(Section, Entry, "", n, 255, MyPath & IniFileName)
  ReadINI = funLeftB(n, InStr(n, Chr(0)) - 1)

End Function

ちなみにiniファイルは、
メモ帳に

[MYSQL]
SERVER_NAME=localhost
DB_NAME=hoge
DB_USER=root
DB_PASS=hoge
DRIVER_NAME=MySQL ODBC 5.1 DRIVER
[BACKUP]
BACKUP_PATH=C:\prj\access1\SYSTEM_BACKUP\

を書いて保存時にXXX.iniとファイル名を指定してaccessと一緒のフォルダに入れます。
hogeの箇所は環境によって修正
これで自動バックアップはOKです。(細かいコード説明は省略します)
ODBCドライバのバージョンは各環境のに合わせてください。

iniファイル便利です。最近小規模システムでは採用すること多いです。
メリットは、DBの名前や環境をかえた時にvbaソースを修正しなくて良いこと
デメリットは、セキュリティ的に大丈夫なの?ということ

一応、iniファイル採用の際は、お客様に確認して導入していますが
小規模だとその方がむしろ納品しやすくて喜ばれます。
というのは、やっぱりお客様環境とは微妙に環境が違うので
パスとかもろもろ、自由に指定できるほうが便利といえば便利なんですよね。
用途に応じて、バックアップパスだけiniファイルに入れるとかでも
良いんじゃないかなって思います。

帳票出力する際のPDF出力パスとか、画像パスとか
VBA内の定数で定義するのもありだけど、定数よりiniファイルの方が
お客様も修正できるので便利ですね。

Accessレポートが重い時はパススルークエリを使うと良い

リンクテーブル使用のAccessレポートが重いという事象が発生しました。

Access + Mysqlの環境で私が作成した在庫管理システムで
お客様からレポートが異常に重いと問い合わせがあり調査した所
予想外に重くなっていて、印刷ボタンを押すと「応答なし」になり
しばらくするとようやく印刷されるという・・・

しかも出庫伝票出すだけなのにどうして?という・・

<原因>
・データ量がかなり増えてきたこと
 →ちなみに ship_unit 51888件ほどでSQLに関数を使用したりしていたのも要因のようです。
・リンクテーブル使用のレポートは重くなる

<回避策>
案1)レポートのソースをリンクテーブルではなく実テーブル(ローカルテーブル)にして
 印刷ボタン押下時に必要なデータだけ抽出し印刷用テーブルにdelete & insertする
 →デメリット accessを複数分散して配布している場合すべてのaccessソースを入れ替えないといけない

案2)レポートに指定しているクエリの使用方法を変更
 通常のクエリを組み合わせたものを指定していたのを
 リンクテーブルを使わないで直接mysqlにアクセスする「パススルークエリー」を使う
 
■使い方
上部メニュー作成→クエリデザイン
SQL→パススルーを選択
20161104_access

パススルーにすると直接mysqlにアクセスしてデータを抽出します。
なのでかなり軽減されます。
構文もmysqlの構文でSQLを書いて保存します。
パススルーで作成したクエリデータとローカルテーブルを組み合わせた
クエリを作成して帳票を出力することで速度アップができました。
パススルークエリに引数も設定できるようですが
今回は、一覧で何件選択されるかわからないデータに対し
選択された件数分、伝票を出したいということだったので
ローカルテーブル=一覧表示にしてチェックされたのはローカルテーブルの
フラグを立てているので、ローカルテーブルとパススルークエリをJOINした
クエリを作成してレポートに紐づけることで、解決しました。

なおしたのは、クエリを修正したのみです。

必要に応じてmysqlのインデックスも貼ったりすれば良いかと思います。
accessの限界か?と思って諦めそうになっていたけれど無事に解決して良かったです。