SQLについて私が知っている二、三の事柄

自分が初めてSQLを知ったのは、バブル崩壊の少し前になる。
入社研修後の初業務のACOSで動いていたRIQSで、まだISAMVSAM全盛だったが1 、そろそろMS-DOSからWindows3.1の頃だった。
ちなみに当時の派遣先の上司がI出身で、PCの話題を振ると「え? PC? (w」みたいな鼻でせせら笑われた記憶が。
その後、IBM RS/6000AIXDB2、NT ServerでOracle 7.x、更にはSybaseMS SQL Server 6.5、2000 あたりを。
信じられないかもしれないけど当時全くSQLのマニュアル本が無く2 「Oracleのマニュアルが一番分かり易い」と言われていた気がする。
思うに、今となっては、自分が一番脂が乗っていた頃とでも言うか、時間があればシステムに潜って色々と調べてたなぁと…シゴトしろよおい、だけど、そういうまだ大らかさが残っている時代だった…気がする。

その後、印象としては、まずはPostgressSQLの知名度が上がり、レンタルサーバでMySQLが普通に使える様になった、と記憶している。
当時Linuxの登場にはそれ程衝撃を感じなかったけど、エンタープライズ級の高価なSQLサーバまでもがオープンソースで存在する事を知って衝撃を受けた。

そして自分は業界を離れ、オープンソースのプロダクトを触っていて、気になった事があった。
と言うのは、業務系で普通に使っていたが3 、オープンソースでは、例えばインデックステーブルはおろか、ストアドやパーティションテーブル、その他モロモロの技術を全く使用していない事だった…間違いかもしれないが。
使わないのが流儀なのか何なのか良く分からないが、チームビルディングは別として、個人的に近頃、大量の…と言っても数万件のデータをMBAで扱う機会があり、例えばインデックス一つ追加しただけでかなりのパフォーマンスの向上が図れた。
じゃぁ、と言う事で、昔取った杵柄では無いけど当時知った・使っていたSQLに関する技術・テクニック・手法は、MySQLで今もあるのかどうかを調べてみたら、結構普通にあるんだと。

知ったかぶりの戯れ言として

return to toc

さすがにテーブルのプライマリキーは複合主キーや固定長文字列に「してしまった」場合も含めて良く使うけど、割と今風のフレームワークのサンプルで出題される、ビジネスロジックとは直接関係の無い数値型のオートインクリメントをプライマリキーにするのは、ちょっと苦手と言うか抵抗があるかなぁと。
それはともかく、単純なインデックスも含めて使わないのが普通かと思って、使わなかったのは勿体無いなぁと今更後悔…ただ、深刻な問題を引き起こす程の大容量データを扱っていた案件では無かったのが救いといえば救いだけど。

インデックステーブル

return to toc

運用やテーブル設計にもよるが、今回は TRUNCATE table して INSERT INTO table、後はテーブルを読むだけのバッチ処理4 だけという、随時データの追加削除が行われない環境下だったので5CREATE table 時、もしくはデータの一括インポート後に ALTER TABLE table ADD INDEX で対応。
バッチ処理を実行して、パフォーマンスが悪い時にあまり何も考えず適当にインデックスを張り、改めてバッチ処理を行なうと、見違える程処理が早くなるのを実感。
データ量が少なかったからかもしれないけど、CREATE table でインデックスも同時に定義、もしくは、データのインポート後にインデックスを貼っても、インデックスを用いない場合と比較しても、構築にさほど時間が延びた気はしなかった。

パーティションテーブル

return to toc

前述したインデックスを使い始めて、ああ、そう言えば当時こういうのもあったな、という事で調べてみたら存在したので、ちょっと驚いた。
Oracleの場合、2006年の記事になるが、@ITのOracleパーティショニング実践講座にある通りだけど、自分はレンジとリストしか知らない…と言うか実践で見た事が無い。
ざっと調べてみたところ、そこそこの労力を必要としそうなので、ちょいプロ的な場合への適用はしんどいかなぁという印象。

ストアドプロシージャ

return to toc

自分はこの機能に特別の思い入れがある。
C/S全盛の頃、メインのVisual Basicロジックがクライアント機側に実装されており、常に(データベース)サーバとのコネクションが発生しているのでレスポンスが異常に悪く6 レスポンス対応にアタマを抱えた。
そこで試しにストアド(SQL Server 6.5)で書き直してクライアント側から、そのストアドをシステム年月日などの引数と共にキックするだけの処理にすると異常にレスポンスが良くなった、という経験があるので。
もちろんMySQLにも存在するが、よくよく考えると現在、こういうC/S時代のシチュエーションは考えにくい気がする。
なので暴論だが、「ストアド(でカーソルを)使うのだったら、PHPなどの書き慣れた言語で書いた方が良いんじゃないのかなぁ」と思っている。7

トリガ

return to toc

使いはしなかったが、気になったので調べてみた、レベル。
Oracleを使った案件で誰も対応するヒトが居なかったので適当に書いて、その後自分はその案件を離れて尻拭いさせられた後輩君が「パフォーマンスが悪くて対応に苦労した」と、ゴメンナサイ。

内部一時テーブル

return to toc

試しに使ってみたレベル…と言うか、当たり前の様にあるんだと。
イチイチスキーマ切って、という程では無い場合に重宝するので、イメージ的には無名関数みたいなイメージが。
ではあるけど、PDOを使うならばともかく、PHPフレームワークやDBライブラリ8 を使う場合、サポートされていない事が多く、そのフレームワークやライブラリのEXECUTEを発行、みたいな、ちょっと統一性に欠ける可能性があるけど。
なお、クローズドな環境での使用だけど、オンライン的な処理で大した量を保存しないテンポラリテーブル使用のプログラムを見たけど、配列化なりで対応して、個人的にはオンラインでは使わない方が良いんじゃないかと思った。

RAWデバイス

return to toc

自分がこの機能を知ったのは、RAIDを組んだSunOSでSybaseを動かしていたシステムの保守・運用を担当していた頃で、UNIXが管理するファイルシステムとは別レイヤで動くので、確かに理に適った方法だと思った。
でもまさかMySQLで、と思ったけど、あるのね…
しかし…この機能はMacで使えるのだろうか…

外部キー

return to toc

業務で使ったり使っているのを見た事が無いし、自分も使えない…
微妙なんですよねぇ…外部キー制約使わず自分でコードを書いてしまったり…

MERGE

return to toc

これはOracleで知った構文になるが、MySQLでは INSERT ... ON DUPLICATE KEY UPDATE として存在。

UNION

return to toc

当然存在する。
ところで VIEWUNION・トリガなどって、ある程度までは業務系のロジックも吸収できる・できてしまうので、MVCのModelとの棲み分けと言うか何と言うか、どうしたものかと。
個人的にはストアドまで行くと大層だけど、DB側で全て実装できるなら全て実装して、プログラムでは簡単なSQL文の発行程度に留めてしまいたいけど、余程単純なビジネスロジックの場合を除き、多分無理な気がしている。

VIEW

return to toc

いやいや当然MySQLに存在するのは知ってたけど、実際に使ってるのは見た事無いなぁと。
以前関わった案件では、直接テーブルを操作せずビューを通じて、みたいな内部規則があった様な無かった様な…
ケースバイケースだけど、個人的に苦手な JOIN9 をこれで実装したりと。

バルクコピー

return to toc

…という用語は、Sybase・MS SQL Serverのそれかもしれないけど、高速なインポート(・エクスポート)で、ロールバック可能なログ・ジャーナルに記録されない、つまりロールバックできない、させないため、高速に動作する仕組みに関して。
なんだけど、MySQLの LOAD DATA INFILE はトランザクションが効くらしい。
mysqldump でエクスポートされたSQLのデータならともかく、提示されたデータがCSVだったので、恥ずかしながら今までCSVから INSERT 文を作る実装しか採ってなかった。
今後はCSVからテーブルには、余程のロジックが必要とされない場合は SET 何がしかでデータの加工で何とかなるならば積極的に使っていくと思う…癖が多そうなのと、データ量によりけりだけど。
例えば、事前にCSVをnkfで文字コード変換した方が良いかもしれないし、改行コードも変換した方が良いかもしれないし、その辺のトリッキーな作業については出たとこ勝負になると思う。
…ただ、仮に本番環境の場合は、SSHなりが使えないと無理なのかも。

MEMORYストレージエンジン

return to toc

さすがにこれは自分が現役の頃は無かった気がする。
試しに使ってみた程度なので言及は避けるが、MBA内で完結したシステム組んでいたので10 、あまりプラスの恩恵を感じなかった。
つまりこの時はSSDで完結したシステムだったので、HDD使っていたら結果は違ったかもしれないし、そもそも、MEMORYストレージエンジンに最適化された手法を用いていたのかも怪しいので。

パフォーマンス測定・チューニング

return to toc

すみません、良く分からないです…

色々と思う事など

return to toc

実際問題として

return to toc

あくまで一例だが、オープンソースの中の人に「何故使わないの?」と尋ねてみたら、「互換性の問題とか、かなぁ」という回答だった。
当時はハードが割高だったので、この様な機能が出てきたのかと思うが、今は仮想化の時代だし実装に拘るよりスケールアウトなりスケールアップする時代なのかと思う。
もしくは、取り敢えずは労力を掛けずお仕着せ通りさくっとさっさと作って、そういう事態に遭遇してから何らかの対策を打つ、みたいなのが良いのだろうか、とか。
そういう面で今となってはこれらはロストテクニックなのかぁと思わなくもないし、逆にハイレベルな問題解決の手段なのかなぁと思わなくもない(が、当然自分にはそこまでのスキルは皆無)。

SQLに感じた違和感(詳しくありません)

return to toc

他言語との連携という意味合いで。

自分はC/Sの頃の人間なので、当時は、あるいは今も、結局SQLの構文(文字列)を作る、という作業をプログラミングさせる事になる場合が大半で、何とも違和感と言うか水と油と言うか…そういうのを常に感じていた。
自分がSQLを触る以前に少しだけdBaseIIUNISYSのMAPPERといった(昔懐かし)4GLを触っていた事もあり、「SQLがもっとインテリジェンスになればいいのになぁ」みたいな事を思っていたのも事実だった。
その後、DIコンテナO/Rマッパーを横目に、と言うか、全くその様な技術を使う事も無く今に至るので11 、知ったかぶり以外の何物でも無いが。
ただ、NoSQLになるがMongoDBを触ってみて、「ああこうすればプログラミング言語と上手く融合するのか」と思った。

ついでに

return to toc

下記、MAMPをベースにしているけど、現時点(2016年初頭)ではMAMPの使用を止めたので、良く分からない箇所があるが、最初の項目以外は関係無さそうにも思うが未確認。

MAMPのMySQLをコマンドラインで

return to toc

下記 test.sql は普通のSQL文を。
当然、出力結果もリダイレクト可能。
mysql.sock を指定する必要があり、面倒ならば my.cnf に指定しておくなど、Web検索すれば分かるかと。

/Applications/MAMP/Library/bin/mysql \
 --user=foo --password=var \
 --socket=/Applications/MAMP/tmp/mysql/mysql.sock \
 --default-character-set=utf8
 db-name < test.sql > output.txt

なお、my.cnf の場所が分からない場合は下記。

$ mysql --help | grep my.cnf

テーブル・フィールドのコメント

return to toc

漢字も使えるので、個人的には積極的に使った方が良さそうな。
ただし、CREATE DATABASE には使えなさそうな。12

CREATE TABLE `hoge_table` (
    `id` int(11) NOT NULL AUTO_INCREMENT
  , `field1` text NOT NULL COMMENT '漢字のコメントだよ'
  , PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'このテーブルはダミーだよ';

フィールド名に日本語

return to toc

MySQLとして成果物を外部に収める必要が無かったのと、項目数がかなり多く英語表記を考えるのが面倒だったので、MAMPで試しに使ってみたら使えた。
ただしSQL文を発行する際は、バッククォートで囲む必要がある。13
…だけではいけないと思うのは、例えば mysql_query(‘set names UTF8′) をPHPで指定するなど…詳細を詳しく調べてないが、今後は出たとこ勝負で。

CREATE TABLE `HOGE_tbl` (
    `id` int(11) NOT NULL AUTO_INCREMENT
  , `フィールド1` text NOT NULL COMMENT '漢字のコメントだよ'
  , PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'このテーブルもダミーだよ';
  :
SELECT `フィールド1` FROM `HOGE_tbl`;

ところでどうでも良いけど、自分はテーブル名・フィールド名は、全文字に大文字で区切りにアンダースコアを使う傾向が強いみたいで。(作例では小文字を使っているけど)
小文字が入るとプログラムを見た時にそれがプログラム言語なのか、データベースなのか区別が付きにくくて…
何が何でも英語化するのも手間なのと、やたら長くなる傾向があるので躊躇無く和名も使うし、更に母音を落とした3レターもプログラム・データベース関係無く、躊躇無く使うあたりがStaticおじさんたる所以です。(開き直り)
例えば「在庫」、通常は「なんたら在庫」になると思うけど、HOGE_ZAIKOHOGE_ZK とかで、決して HogeInventory みたいにはしないだろうなぁと。
…当然、他人が設計したらそれに従うし、既存の改修だとそのクセに従ってコーディングするので、悪目立ちはしない様に心掛けているけど。

2014/06/01 初稿
2015/01/08 大幅修正

Footnotes

  1. 1. 当然メインフレームでの開発で、RIQSはパフォーマンスが悪かった記憶がある。また、ISAMやVSAMを触ったのはそれから随分後だった。使いにくかった。

  2. 2. たしかピアソンぐらいしか出版してなかった記憶が。

  3. 3. 自分達がそこまで設計したのではなく、アーキテクト系のチームが設計・主導していたかもしれない。

  4. 4. ここで言う「バッチ処理」とは、Web等の画面の介在が全く存在せず、ターミナルからPHPスクリプトを呼び出すだけのプログラミングという意味で。

  5. 5. インデックスを張っているテーブルに随時更新を行なうとパフォーマンスが低下する、というイメージが。

  6. 6. ネットワーク状況や端末のパフォーマンスにも左右されるが。

  7. 7. そう言えば上記とは別のACOSのCOBOL/Sのリプレース案件(炎上)で、Oracleのストアドで実装、というプロジェクトがあったなぁと。

  8. 8. 個人的にはIdiormかなぁ…Parisは機能云々よりプロダクト名が検索しにくいので…

  9. 9. Sybaseの JOIN が最初だったためか、未だに右や左や言われてもピンと来ない…

  10. 10. あくまでもクライアント先が要求していたのは、システムでなく、あるデータの塊の解析…とまでは言わないけど、結果を欲していたので。

  11. 11. 直接意識してPDOさえも使った事無いしなぁ…フレームワークに任せっきりで…

  12. 12. 基本的にはphpMyAdminでユーザ名とデータベース名を込みで作るので意識した事が無かったけど、今はphpMyAdminを導入していないので、それぞれ別々に発行している。

  13. 13. 以前MS-ACCESSのVBAで変数名などに漢字を使っているのを見て違和感を感じたけど、まさか自分も変数では使わないけど、フィールド名に積極的に使おう推進派になるとは思わなかった。