寛容なMySQLを非寛容にすること(その3)


結論:MySQLのsql_modeには、ONLY_FULL_GROUP_BYを入れておく

ユーザを管理するテーブルを考える。ユーザはa, bという2つの属性を持つ。テーブルはこんな感じ。

CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a INT,
b INT,
INDEX (a),
INDEX (b)
);
INSERT users (a,b) VALUES (1,1);
INSERT users (a,b) VALUES (1,2);
INSERT users (a,b) VALUES (1,3);
SELECT * FROM users;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    1 |    2 |
|  3 |    1 |    3 |
+----+------+------+

属性aが1の人数は、次のように求められる。

SELECT count(*)
FROM users
WHERE a=1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

何かの事情でaの値も取得したいとき、安易に次のように書いてしまうかもしれない。

SELECT a,count(*)
FROM users
WHERE a=1;
+------+----------+
| a    | count(*) |
+------+----------+
|    1 |        3 |
+------+----------+

上のSELECT文は、手元のMySQL 5.0.84と5.1.37-1ubuntu5, 5.4.3-beta-communityでは動作する。しかし、MySQL 5.0.45-logでは次のようなエラーになる。

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

リファレンスマニュアルの3.3.4.8. Counting Rowsには、上のようなSELECT文は可能だという記述があるのだが、うまくいかない環境もある(「SET sql_mode='';」としてもだめ)。

次のように書けば、どの環境でも動く。一般的に、集約関数があるときは、SELECTするものはGROUP BYの対象になっていなければならない(最初のSELECT文を実行できたのは、MySQLの独自の拡張のため)。

SELECT a,count(*)
FROM users
WHERE a=1
GROUP BY a;

SELECT a,count(*) FROM users GROUP BY a HAVING a=1;」と書いてもいいが、これを素朴実行したらさすがに遅いだろう(オプティマイザが上の形に変換してくれるとも思えない)。

このようなバージョンによる振る舞いのぶれを無くすためには、「SET sql_mode='ONLY_FULL_GROUP_BY';」として、SQLモードを変更し、MySQLの独自の拡張を無効にすればよい。こうすることによって、以下のSELECT文はすべてのバージョンでエラーになる。(ちなみに、現在のSQLモードは「SELECT @@sql_mode;」でわかる。)

SET sql_mode='ONLY_FULL_GROUP_BY';
SELECT a,count(*)
FROM users
WHERE a=1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

そもそも、曖昧なSELECT文を実行させるような独自拡張に何の意義があるのだろうか。リファレンスマニュアルの12.16.3 MySQL Handling of GROUP BYには、You can use this feature to get better performance by avoiding unnecessary column sorting and groupingとあるから、パフォーマンスがよくなる可能性はある。本稿の例ではaの値は1しかないため、「GROUP BY a」をしなくて済むならそれに越したことはない。しかし、これはわざと作った例であり、実際にこういうことがうれしいという状況にあるときは、何かおかしなことをしているという自覚があるべきだろう(先に書いた「何かの事情」とはそういうことだ)。

それよりも、何か間違ったことをしているのに、MySQLの寛容さのためにその発見が遅れることがこわい。そういうことにならないための安全策として、普段から、曖昧なSELECT文を許容しないようにMySQLを設定しておくといいだろう。具体的には、my.cnfあるいはmy.iniの[mysqld]のセクションに、「sql-mode="ONLY_FULL_GROUP_BY"」と書いておけばよい。

もっと非寛容にしたい向きは、リファレンスマニュアルの5.1.7. Server SQL Modesから、それらしいものを見繕ってくればいい。TRADITIONAL(STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USERをまとめたもの)あたりから試すのがおすすめか。

お約束ですが、こういう話を基本から学びたいという方には、拙著『Webアプリケーション構築入門 実践!Webページ制作からマッシュアップまで 』(森北出版, 2011)がおすすめです。

SSD時代のデータベース管理システム


Solid Stateには近づくな

単純な読み書き性能のベンチマークはよく目にする一方で、(学術論文は別にして)あまり目にすることがなかったデータベース運用におけるHDDとSSDの違いについての解説記事が、2本立て続けに出ました。

SSDにはSingle-line Cell (SLC)とMulti-line Cell (MLC)の2種類があり、一般的にSLCは高速・小容量・長寿命、MLCは低速・大容量・短寿命です。定評のあるインテルの製品はどちらもAmazonにありますが、SLCであるX25-Mでは、価格がずいぶん違います。ノートPCに搭載されているものの大部分はMLCでしょう。

閑話休題

斉藤さんの記事では、データベースシステムの仕組みをまず解説し、それを踏まえてB+tree(挿入と大量SELECT)についてのベンチマーク、External Merge Sortについての学術論文紹介、トランザクション管理についての考察がなされています。アカデミックな雰囲気のある解説です。

松信さんの記事では、(HDDが苦手とする)インデックススキャン、(HDDが得意な)フルテーブルスキャン、(SSDが苦手だと予想される)更新系処理(DBT-2)の性能が調べられています。MySQLで利用する際に、SSDにデータを、HDDにREDOログとシステムテーブルスペースを置くと性能が50%以上向上するといった実践的な知識が得られます。

性能が上がれば単純にうれしいわけですが、HDDで使うことを(暗黙の)前提として作られていることが多いRDBMSのアーキテクチャが、SSDの普及とともに変わっていくのかどうかに私の興味はあります。

この点について、2本の記事の印象はずいぶん違います。

斉藤さんの記事を信じるなら、RDBMSのアーキテクチャは変わらないということになります。

従来使っていたHDDをSSDに取り替えるだけでDBMS自体はそのまま使える、という雰囲気になっています。(p.108)

既存のアーキテクチャのままで、HDDとSSDという性能特性のまったく違った記憶装置に対応できるなら、それはすばらしいことです。

松信さんの記事を信じるなら、RDBMSのアーキテクチャもアプリケーションもに大きく変わるということになります。

単なる著者の考察にすぎないので異論を持つ方もいるだろうが、活発な議論をして業界を盛り上げていけたらと思う。(p. 160)

データベースのアーキテクチャが劇的に変わる現場に遭遇できたら、それはすばらしいことです。

要注目です。

関連:HDD単体とRAID 0、SSDの比較

関連:容量の大きいSSD1台の性能、容量の小さいSSD2台によるRAIDの性能