タグ「MySQL」が付けられているもの

MySQLのTimestamp

  • 投稿日:
  • カテゴリ:

MySQLのTimestampを勘違いしていました。
(そしてkazeburoさん素早い突っ込みありがとう( `・ω・)) 

OracleのTimestampとは全然違って、そういう場合はDateTime型を使うのが正しいらしい。Timestampのサイズが妙に小さいので気に入っていたのですが(´・ω・) で、何が違うのかと言うと、

  • データ追加時、または更新時、現在日時を自動で設定する。
  • データ更新時で更新前後で値の変化がない場合、TIMESTAMP 型の項目も更新しない。
  • NULL 値を設定した場合、現在日時が設定される。

(引用元)http://minazuki.cocolog-nifty.com/kaimemo/2008/01/mysql_timestamp_6d3b.html

というわけで、基本的にはレコードの更新時間が入るみたいです。

危ない危ない。
この前の内製アプリにおもいっきりTimestamp使っていました(;´Д`)

他にも色々勘違いしているものがありそうだ(((( ;゚д゚)))

MySQLのLIMIT

知り合いよりMySQLの使い方がひどいんですって話があって、その中でLIMITの話があったのでこういう事なのかなーと推測してみた。

その話によるとなんでもかんでもLIMITをつけている。SELECT * FROM XXXX LIMIT 1 みたいな事をいろんなところでやっているという事でした。MySQLはそんなに詳しくないのでDB2のOPTIMIZE FOR 1 ROWみたいなおまじないでつけてるんじゃないのかなーと思いつつ、実際に実行計画をとってみました。

テーブルを専用に用意するのは面倒なので、家のテーブルから適当にデータが入っているやつをチョイス。

mysql> describe BC2_ST_POSTLIST;
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field      | Type          | Null | Key | Default           | Extra                       |
+------------+---------------+------+-----+-------------------+-----------------------------+
| HASH       | bigint(20)    | NO   | MUL | NULL              |                             |
| LINK       | varchar(1024) | NO   |     | NULL              |                             |
| STATUS     | int(11)       | NO   |     | 0                 |                             |
| REGISTDATE | timestamp     | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> show index from BC2_ST_POSTLIST;
+-----------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| BC2_ST_POSTLIST |          1 | IDX_BC2_ST_POSTLIST   |            1 | HASH        | A         |     4166553 |     NULL | NULL   |      | BTREE      |         |
| BC2_ST_POSTLIST |          1 | IDX_BC2_ST_POSTLIST_2 |            1 | REGISTDATE  | A         |       41253 |     NULL | NULL   |      | BTREE      |         |
+-----------------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)
mysql> select count(*) from BC2_ST_POSTLIST;
+----------+
| count(*) |
+----------+
|  4164791 |
+----------+
1 row in set (0.00 sec)

これにSQLを発行してみる。

mysql> explain select * from BC2_ST_POSTLIST LIMIT 1;
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | BC2_ST_POSTLIST | ALL  | NULL          | NULL | NULL    | NULL | 4166649 |       |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

おや?rowsがひどいことになってます。(件数が上のCOUNTと違うのはオンラインで稼働中のテーブルなので・・)。これではLIMITをつけない場合と同じですね。OracleのCount Stopのような実行計画になると思っていました。

MySQLのリファレンスによると、色々かかれていますね。
http://dev.mysql.com/doc/refman/5.1/ja/limit-optimization.html

試しにORDER BYをINDEXをつけている列につけてみました。

mysql> explain select * from BC2_ST_POSTLIST ORDER BY REGISTDATE LIMIT 1;
+----+-------------+-----------------+-------+---------------+-----------------------+---------+------+------+-------+
| id | select_type | table           | type  | possible_keys | key                   | key_len | ref  | rows | Extra |
+----+-------------+-----------------+-------+---------------+-----------------------+---------+------+------+-------+
|  1 | SIMPLE      | BC2_ST_POSTLIST | index | NULL          | IDX_BC2_ST_POSTLIST_2 | 4       | NULL |    1 |       |
+----+-------------+-----------------+-------+---------------+-----------------------+---------+------+------+-------+
1 row in set (0.00 sec)

rowsが1件になりました。
というか、そもそもWHEREやORDER BYを指定しないクエリにLIMITをつける意味が私にはわかりませんでした(´・ω・)
意味というか、それで要件を満たせるユースケースってどんなのがあるかなーと。
ただし、

MySQL が要求された行数をクライアントに送信すると、クエリが中止されます(SQL_CALC_FOUND_ROWSを使用していない場合)。

とあるので、この場合は1件フェッチした場合にクエリが中断されるんだと思います。それを確認してみました。

mysql> explain select SQL_CALC_FOUND_ROWS * from BC2_ST_POSTLIST ORDER BY REGISTDATE LIMIT 1;
+----+-------------+-----------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | BC2_ST_POSTLIST | ALL  | NULL          | NULL | NULL    | NULL | 4174728 | Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)

ふむふむ。実際にEXPLAINじゃなくて実行してみた。
データの中身は見せられないので切り取り。

mysql> select SQL_NO_CACHE SQL_CALC_FOUND_ROWS * from BC2_ST_POSTLIST LIMIT 1;
1 row in set (5.20 sec)
mysql> select SQL_NO_CACHE * from BC2_ST_POSTLIST LIMIT 1;
1 row in set (0.00 sec)

ふむふむ。ということはやっぱり中断はされているっぽいですね。
MySQLってOracleのように何バイト読み込んだかみたいなものって見れないのかな・・。

いいお勉強になりました。
うーん。これくらいじゃ目くじら立てる原因にはならなそうなので他の要因がありそうです(まぁ積もりにつもった様々なイライラだと思いますがw)。テーブルの全データをメモリに載せようとしちゃうのかな。実行計画上は全件スキャンするようになっているし。
これ以上は知識が無いのでソース読んだ方が早そう(´・ω・)

まぁテーブル構造知らないしどんな要件でどんなSQLを発行しているのか分からないので、なんとも言えませんでした(;´Д`)

 

RDBで文字列にインデックスをそのまま張ってる事って結構あると思います。
(普通はないかな?)

例えばURLに対してINDEXを作った場合、1エントリに対してのインデックスの量が多くなってしまいます。
そこでカラムを一つ追加して、その文字列のハッシュ値を格納するようにします。
そして文字列のカラムにはインデックスを張りません。
WHERE句を書くときには、「ハッシュ値カラム AND 文字列カラム」とします。
これでインデックスツリーが小さくなってDBサーバのメモリ効率が上がります。
クライアントサイドでハッシュを求めることになりますが、クライアントサイドはスケール化が簡単な事と、ハッシュ値計算なんて微々たるものなので問題にはならないと思います。

Javaの場合であればhashCode()メソッドがあるので自前でハッシュ関数を作る必要はありません。

MySQLを例にしてみます。
(直接手打ちしたので構文エラーになるかもしれませんw)

-- これは悪い例
CREATE TABLE TBL_URL(
  IDX INTEGER NOT NULL,
  URL VARCHAR(4096) NOT NULL,
  STATUS INTEGER NOT NULL,
  TITLE VARCHAR(1024),
  BODY MEDIUMTEXT,
  PRCDATE TIMESTAMP NOT NULL,
  CONSTRAINT PK_TBL_URL PRIMARY KEY (IDX)
);
CREATE INDEX IDX_TBL_URL_01 ON TBL_URL(URL(255));
-- SELECT * FROM TBL_URL WHERE URL = ?
-- 改善例
CREATE TABLE TBL_URL(
  IDX INTEGER NOT NULL,
  URLCD INTEGR NOT NULL,
  URL VARCHAR(4096) NOT NULL,
  STATUS INTEGER NOT NULL,
  TITLE VARCHAR(1024),
  BODY MEDIUMTEXT,
  PRCDATE TIMESTAMP NOT NULL,
  CONSTRAINT PK_TBL_URL PRIMARY KEY (IDX)
);
CREATE INDEX IDX_TBL_URL_01 ON TBL_URL(URLCD);
-- SELECT * FROM TBL_URL WHERE URLCD = ? AND URL = ?

Sequenceをエミュレート(MySQL+iBatis版)

GeneratedKeyを使う方法は以前書きましたが、今回はiBatisを使った場合の方法です。
1回の命令でシーケンスをエミュレートするやりかたを書いておきます。

まず、iBatisは以下の事が言えます。

  • Insertの時だけSelectKeyで値を返すことができる(Updateの時は戻り値は更新件数)
  • InsertタグでもUpdate文を発行する事は可能
    (JDBCレベルではexecuteUpdateを発行しているため)

これらの事をふまえると、
Insertタグの中にUpdate文を記述して、SelectKeyも併せて書くことでインクリメントした値を返すことができます。

さっそくサンプルを。

シーケンステーブルの準備

CREATE TABLE SEQUENCE (
    NAME VARCHAR(32) NOT NULL,
    SEQ INTEGER NOT NULL,
    CONSTRAINT PK_SEQUENCE PRIMARY KEY(NAME)
) Type=InnoDB;
INSERT INTO SEQUENCE (NAME, SEQ) VALUES('IMGID', 0);

iBatisの設定ファイル

<insert id="GetNextSequence" parameterClass="java.lang.String">
	UPDATE SEQUENCE SET SEQ = LAST_INSERT_ID(SEQ+1) WHERE NAME = #name#
	<selectKey resultClass="int">
		SELECT LAST_INSERT_ID()
	</selectKey>
</insert>

Javaプログラム

int id = (Integer) sqlMap.insert("GetNextSequence", "IMGID");

OracleやPostgreSQLにはSequenceがあるのですが、MySQLにはありません。
AUTO_INCREMENTは便利なのですが、外部キーの参照先となるような場合に困ってしまいます。
具体的には次のような感じです。

テーブル設計や正規化云々の話は面倒なので言及しません。

名簿テーブル
ID NAME YOMI
1 陽菜 ひな
2 あおい
3 さくら さくら
4 結衣 ゆい
5 結菜 ゆうな
係り活動テーブル 
ID NEN GAKKI KAKARI
1 1 1 いきもの
1 1 2 図書
1 1 3 保健
1 2 1 風紀

名簿テーブルのIDがAUTO_INCREMENTの場合に係り活動テーブルに入れるIDの取得に困ります。

  1. 名簿テーブルにデータをINSERTする。(IDは自動生成で1が入ったとする)
  2. 自動生成された1を取得するために以下の方法で取得する
    1. SELECTを発行する
    2. LAST_INSERT_ID()をコールする
  3. 係り活動テーブルにデータをINSERTする。

SELECTの場合、MAX関数使った場合はマルチスレッド、マルチプロセスで破綻します。
LAST_INSERT_ID()の場合、発行する順番に気をつければ同一セッション内で値が保持されている・・・?
詳しくはわかりません(´・ω・`)

またAUTO_INCREMENTをやめてシーケンステーブルを作成した場合、SELECTは上記と同じ理由で破綻(ロックかけるなら別ですけど。)、LAST_INSERT_IDは上手くいくが、どちらにせよ2往復している事になります。

もしDBがSEQUENCEをサポートしている場合(名簿テーブルのIDはAUTO_INCREMENTではありません)、

  1. シーケンスを進めて値を取得する(仮に1とする)
  2. 名簿テーブルにデータをINSERTする
  3. 係り活動テーブルにデータをINSERTする

こんな感じで済みます。

とりあえず、2往復は効率が悪そう。でMySQLはシーケンスをサポートしていない。となれば作るしかなさそうです。

INSERTの戻り値として自動生成された値を取得する場合、PERLではDBIのmysql_insertidを使えば出来、JDBCの場合はStatement#getGeneratedKey()を使う事で取得が可能になるようです。ちなみにOracleの場合はgetGeneratedKey()でカラムを取得してもRowId型になった気がします。(誰かのBLOGに載っていた予感)

というわけで、早速コードを書いてみました。 

 

MySQLのINDEXが壊れる件について

  • 投稿日:
  • カテゴリ:

普通のSQLを発行するとINDEXが壊れて、自動リペアなども出来なくなります。
エラーログには日本語訳すると「自動でリペアしようとしたけど失敗しちゃった!!テヘッ」と書かれてます。
おのれ~・・・。こんなトラブルは初めてだ・・。

Got error 124 from storage engine  / ERRORNo:1030

MySQL5.0.33(ソースからビルド)で確認しました。
やり方は以下の通りで。

CREATE TABLE TEST(N1 INTEGER, N2 INTEGER);
CREATE INDEX IDX_TEST_01 ON TEST(N1,N2);
INSERT INTO TEST(N1,N2)
SELECT 1,(SELECT MAX(N2)+1 FROM TEST WHERE N1 = 1);

INDEXを貼っていなければ問題はありません。
また、INSERT文中のSELECTの1列目の"1"と2列目のWHERE句中の"1"が異なる場合も問題ありません。2列目のSELECT MAX(N2)がSELECT COUNT(N2)でも問題ありません。2列目のSELECT MAX文で1件でもHITすれば問題ありません。

酷い目にあった・・・。
MySQL5.0.37ではエラーにはなるものの、INDEXが壊れることはない感じです。

くそう・・・。ピンポイントですか(´・ω・)ぷんぷん

JamesとMySQLのメモφ(`д´)

  • 投稿日:
  • カテゴリ:

忘れないように備忘録。

JamesのストレージにMySQLを利用すると、起動しません。
MySQLでは途中のバージョンからVARHCARのサイズ拡張が変更されており、VARCHAR(200)の場合は最大200バイトのはずですが(少なくともOracleではそうだったはず・・・)MySQLでは文字コード依存になります。200バイトではなく、200文字。なのでSJISの場合は1文字が2バイト扱いになり、UFT8では1文字が3バイト扱いになります。
そしてMySQLの制限の一つに、主キーのセットは1000バイト以下というものがあります。
JAMESのDDLによると、VARCHAR(200)とVARCHAR(255)を複合主キーとしているので、(200+255)*3=1365バイト。正確には1367バイトになるの?よくわかりません・・・。

こんな制限初めてしったよ(`□´)
もともとこの列の定義としては、200バイトという意味っぽいので列の定義に「CHARACTER SET latin1」を追加して回避しました。
VARCHAR(200) NOT NULL → VARCHAR(200) CHARACTER SET latin1 NOT NULL

備忘録φ(`д´)メモメモ...