2010-10-21

『売上猫くん on MySQL』開発日記 - 番外16 - FMで大きなSQLテーブルは扱えるのか? ― その1

MySQLを使用するメリットの一つは、何百万、何千万もの大量のレコードを持つ巨大なテーブルを扱えること。 ところが、そうした巨大テーブルをFileMaker アプリから操作しようとすると、大きな問題にぶつかる。(尚、下記の環境は、クライアントからFileMaker Server上のアプリファイル(NekoApp50.fp7)にアクセスしている。ローカルでNekoApp50.fp7を実行すれば、実行速度は改善される。)

問題1. だらだらクエリ
例えば、12万件のレコードを持つ郵便番号テーブルをFileMaker のレイアウト上に表示し、一番最後のレコードに移動してみる。移動すると「検索実行中...  クエリーを処理中」と数十秒またされることになる。これはMySQLのログを見てみるとわかるが、

SELECT DISTINCT ID FROM vw_zips WHERE ID>19 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>1019 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>2019 ORDER BY ID
SELECT DISTINCT ID FROM vw_zips WHERE ID>3019 ORDER BY ID
・・・
・・・ 中略
・・・
SELECT DISTINCT ID FROM vw_zips WHERE ID>119019 ORDER BY ID

といったクエリをテーブルの全レコード件数(この例では12万件)に達するまで延々と発行し続け、FileMakerにこれまた延々とロードし続ける、という仕様なのである。 実務上、これではユーザから苦情が来るのは必至。 さらに、テーブルデータが100万、1000万件に達する場合は、実用に耐えない。

尚、一度ロードしてしまえば、アプリを閉じるまで、上記の『だらだらクエリ』は実行しないようである。


問題2. 検索を実行すればするほどデータの並びがグチャグチャに?
FileMakerはクエリを実行した順にデータをロードし、一旦ロードされると再ロードは行わない。ユーザの検索実行数が増せば増すほど、レコードは作成順に並ばず、グチャグチャに並んでるように見える。

例えば、郵便番号レイアウトを開くと、FileMakerはそのレイアウトに割り当てられたテーブル(レイアウトテーブル)のレコードの1番目から数十番目までをSELECTする以下のようなクエリを実行し、レイアウト上に表示する(SELECTされるレコード数は、レイアウトの形状、ウインドウの大きさにより異なる)。 

SELECT ID,`〒`,`住所` FROM 郵便番号 WHERE ID IN (1,2,3,4, ~中略~ ,26,27)

次に、ユーザ自ら郵便番号の「9071801」(沖縄県)を検索し、次に「2010004」を検索し、その後レコードメニューから「全レコードを表示」を行うと、以下のような並びになってしまう。


郵便番号「2010004」の後に「0600012」移行が並ぶのは、上記の「全レコード表示」の直後にスクロールダウンした為、SELECT ID,`〒`,`住所` FROM 郵便番号 WHERE ID IN (28,29,~,40,41)というクエリが実行され、該当するレコードがロードされた結果だ。 

FileMaker は内部の SELECTクエリを実行した順にレコードをロードする。 再度検索を行っても、プライマリキーが同じものは再ロードされない。 ユーザが該当件数が少ない検索条件を実行すればする程、レコードは作成順には並ばないため、ユーザからみればグチャグチャに見える。


問題3. レコード総数取得クエリ
SQLテーブルをレイアウトに最初にロードすると以下のクエリが走る。

SELECT COUNT(*) FROM (SELECT DISTINCT ID FROM vw_salesdtls) COUNTER_TABLE 

この SELECT DISTINCT には時間がかかり、180万件のテーブルだとレイアウトにレコードを表示するだけで30秒程度はかかってしまう(但し、一旦レイアウトテーブルをロードすると、アプリファイルを閉じるまで、このクエリは再発行されない)。巨大なテーブルを扱う場合は、VIEWによりレコード総数を制限する必要がある。


問題3については、数百万レコード程度であれば、ユーザも我慢できるかもしれないが、1と2ついてはなんらかの対応が必要だろう。


土屋 


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-13

『売上猫くん on MySQL』開発日記 - 番外15 - 再び権限(私的メモ)

特定のテーブルにGRANTできる権限/グローバルにしかGRANTできない権限
できる権限は以下。
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, and ALTER

つまり、上記以外の権限はテーブル毎に設定することはできない。

  • " The EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax)."
参考URL
Re: Grant Execute on selected procedures
MySQL - How to grant FILE privilege?

Privileges Provided by MySQL 権限の詳細説明

(土屋)


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧

2010-10-12

『売上猫くん on MySQL』開発日記 - 番外14 - テーブル毎に権限を設定する(ほぼ私的メモ)

MySQLにロールやグループは無い
かねがね疑問だったんだけど、権限をまとめて管理する機能(FileMakerのアクセス権限セット/グループ定義のようなもの)はMySQL本体にはないことがわかった。 コミュニティーで「他のDBにあんだから、付けてよー」って要望はあるけど、Priority は LOW なので期待薄。 Ver5.5 ではどうなんだろうか。

参考URL:
Creation of user groups http://bugs.mysql.com/bug.php?id=13131


ちなみに、MySQL Workbench には、DBA、BackupAdmin といった権限の“Role”が用意されており、これを選択することにより User に対して簡単に Role を割り当てることができる。 また、下図のように、Role そのものをユーザが定義することができる。


GRANT ~ ON scheme.* すると、 Revoke ~ ON scheme.table できない
あるデータベース内のすべてのテーブルに権限を与え、その後、そのデータベースの特定のテーブルから与えた権限を剥奪=revoke しようとしても、

Error Code: 1147 There is no such grant defined for user 'testuser' on host '%' on table 'infos'

とエラーになってしまう。


GRANTでテーブルの列記はできない
GRANT ~ on dbname.table1, dbname.table2 ~ のようなテーブルの列記はできない 。



ということで、多数のユーザ登録が必要で、且つテーブル毎/コラム毎に権限を設定する場合は、大量のGRANT文を書かなければならない。


(土屋)


関連リンク:『売上猫くん on MySQL』開発日記の記事一覧