カテゴリー別アーカイブ: MySQL

使用するインデックスを明示する – MySQL インデックスヒントで最適化

 MySQLでクエリをExplainした時にpossible_keysに、IDX_IMPORT, IDX_FSEARCH, IDX_STAT等と複数の候補が存在してkeyにIDX_STATが選択されている時、実際にはIDX_IMPORTの方が高速だったり、他のインデックスよりIDX_STATが低速だったりする時、このインデックスを利用すると高速・低速であるというのをオプティマイザへ知らせる方法としてインデックスヒントがある。

インデックスヒントは

SELECT * FROM estimates USE INDEX(IDX_IMPORT, IDX_FSEARCH) WHERE total>=10000 AND type=1

のように、テーブルに対してUSE/IGNORE/FORCEにより指定する。

USE INDEXはその名の通り指定したインデックスを使用することを推奨するので、インデックス候補中で速い物を指定する。
FORCE INDEXはフルスキャンが非常に重いことを知らせた上で、指定したインデックスを使用することを推奨する。
※特定の条件ではインデックススキャンよりもフルスキャンの方が高速となるパターンがあるので、USEでインデックスを知らせてもそれを使用しないことがあるが、FORCE指定するとフルスキャンを回避してインデックスを使う様になる。 FORCE指定した場合、フルスキャンよりも低速になる可能性もあるので、実データで検証した上で指定した方が良い。
IGNORE INDEXは、逆に使用しないインデックスを指定するので、インデックス候補中で明らかに低速なインデックスがある場合にそれを除外するために使う。

USEとFORCEを比較

EXPLAIN SELECT id FROM estimates USE INDEX (PRIMARY) WHERE type=1 ORDER BY id

possible_keys:NULL key:NULL

EXPLAIN SELECT id FROM estimates FORCE INDEX (PRIMARY) WHERE type=1 ORDER BY id

possible_keys:NULL key:PRIMARY
 ちなみに、ヒントを与えない場合は、possible_keys:NULL key:NULLである。 このクエリを実際に発行すると、USEで0.25秒、FORCEで0.33秒と言うことで、インデックスを使っているけど逆に遅くなった。
 インデックスは何でも張って何でも使えばいいと言う物ではないと言うことだ。
 なお、このようにインデックスを使用すると遅くなる状況でオプティマイザがインデックスを使用するのを防ぐために、USE INDEX ()として、インデックスを使用しないことを明示することも出来る。

(21749)


カテゴリー: MySQL | コメントをどうぞ

MySQL – インデックスが働かない / Zend Framework

MySQLで

stock
id:int name:varchar num:int

みたいなテーブルに

SELECT * FROM stock WHERE num=’1′

みたいなクエリを投げる。
厳密に言えば、numはint型で、クォーテーションでくくったものは文字列型であるから、数値=文字列の比較は常に成立しないのだけど、MySQLは気を利かせてnum=1を検索して返してくれる。
しかし、このとき、インデックスは動作しない。 型をミスってSQL文を埋め込んでいても勝手に変換して動作するくせに、インデックスが働かずボトルネックになるというアレな状態だ。
適当な感じのインデックスが張ってあるのに、EXPLAINしてやると使用キーが無かったりするのはこんな原因だったりする。
普通にSQLを自分で構築している場合には、コードを修正してクォーテーションを外せば良い。

この問題は、表題のZend Frameworkにもあって、自動エスケープモードを有効にしていると、型に関係なく勝手にクォーテーションでくくられてしまう。 ZendFrameworkのDB機能ではSELECT * FROM stock WHERE num=:numみたいなSQL文とarray(‘num’=>1)の様なパラメータを渡してやるわけだけど、自動エスケープモードで発行するとSELECT * FROM stock WHERE num=’1′になってしまうので、インデックスが働かない状態になる。
このような場合、DBインスタンスを作るときに自動エスケープを無効化して、必要なパラメータを手動でエスケープしなければならない。 また、自動エスケープ処理部分で、is_string($var)の場合だけクォーテーションをかけるようにフレームワーク側を修正する手もある(面倒な場合、DB定義をALTER TABLE stock CHANGE num num VARCHAR(11)のようにして文字列型にしてしまう手もある)

(403)


カテゴリー: MySQL | コメントをどうぞ

MySQL de ビュー

 DBMSのビュー機能って言うのは、簡単にいえばSELECT文でテーブルの複製のようなものを構築する機能。

例えば、
orderテーブル

id
item_id
unit

itemテーブル

id
name
price

みたいなテーブルがあって、ビューを

CREATE VIEW order_view AS SELECT order.id, name, unit*price as total_price FROM order, item WHERE order.item_id = item.id

の様に定義しておくと、id, name, total_priceを持つビューができる。
 ビューはテーブル同様、 SELECT * FROM order_view の様に処理できる。

 CREATE TABLE ~ SELECT ~形式でテーブルを作ると似た結果になるが、CREATE TABLE ~ SELECT ~では、CREATE TABLEしたタイミングの情報がテーブルに保持されるのに対して、ビューはビューに対してクエリが投げられる度に、AS~の文を実行してデータを取得する。
 よく使う取得形式について、SELECT *で済ませる様に定義したり、制限ユーザにテーブル全項目ではなくカラムを限定してビューとして提供したり、内部構造の変更を吸収するレイヤーとして使うことができる。

 便利に思えるビューではあるが、色々と制限もある。
 例えば更新系クエリ(INSERTUPDATE)は、レコードが一意に元テーブルのレコード・カラムを特定できる事が最低条件である。
 先に示した定義の場合、total_priceunitpriceの演算により求められるカラムであり、total_priceが元テーブルにどのような値を与えるか不定であるから更新できない。
手の込んだ構造のビューは基本的に更新できない、見る(view)ためだけの物であると考えたほうが良い。
 また、ビューにはインデックスを設定することもできない(クエリを投げた段階でSELECTしてデータを取得するので、SELECT後にインデックスを構築して検索したとしても、コストが増えるだけ)ため、パフォーマンス的に弱いし、集計操作などにも癖がある。
 パフォーマンス制限について、一回の接続で繰り返し同じビューを利用するなら、ビューではなく、一時テーブル(CREATE TEMPORARY TABLE ~)を接続するたびに構築した方が高速な事もある(接続中はマスタの変更は反映されないし、更新系は結局、元テーブルに投げなければならないが)

(86)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

mysql チューニング – 上物 explain mysqldumpslow

インフラ的なチューニング項目もあるけど、今回は上物のチューニング。

上物のチューニングといえば、「Wordpressでページ送りが表示されない」の記事で出てきたEXPLAIN文。
こいつは、SQL文の前にEXPLAINとつけて実行すると、そのSQL文が実行されるときの実行計画が表示される。

また、チューニングするには情報収集が大事で、クエリのログを取得するのが第一歩。
長時間を要するログを取得するのに、MySQLアップデート - epel/remiリポジトリでやったスロークエリログを取得するほかに、

my.cnf

log = /var/log/mysql-query.log

の様に記述することで、クエリ等のログを取得できるが、スロークエリの方が解析しやすいと思う。
で、取得したスローログは、mysqldumpslowコマンドで処理できる。

mysqldumpslow -s t all-sql.log >sorted.txt

の様に実行することで、総消費時間(実行時間x実行回数)順でソートしたSQL文一覧が出力されるので、これの上からチューニングを施していく。

SQLのチューニングと言えばインデックスを張ることで、ユニークなキー、クエリの要素を出来るだけ多く含んだキーを設定することで速度的に大きな恩恵が得られる事が多い。
そして、インデックスがどのように働くかというのを知る方法が、冒頭に述べたEXPLAIN文である。
sorted.txt

Count: 11108  Time=0.04s (451s)  Lock=0.00s (0s)  Rows=0.0 (425), est_bk[est]@localhost
SELECT price FROM est WHERE r_id = ‘S’ AND s_id =N LIMIT N

の様に出てきた場合、平均実行時間0.04秒のクエリが11108回実行されていると言うことになる。このSQL文を

EXPLAIN SELECT price FROM est WHERE r_id = ‘S’ AND s_id =0 LIMIT 10

等の様に実行する(S/Nは文字列・数値の代替文字なので、適宜数値などを設定する)

実行結果は

id select_type table type possible_keys
1 SIMPLE est ALL NULL
key key_len ref rows Extra
NULL NULL NULL 5935 Using where

のように出力される。
この場合、SIMPLE(UNION等を含まない)セレクトがestテーブルに実行されて、有効キーはなく5935行をALL(全域スキャン)を行う計画になっている。 全域スキャンはコストが高いので、インデックスを設定することで高速化できそう。
確認のため、インデックスを張る前に、EXPLAINを外して普通に実行すると、クエリの実行時間 0.0580 秒であった。

ALTER TABLE est ADD INDEX INDEX_R_S_PRICE ( r_id , s_id )

のようにしてインデックスを張って、再度、EXPLAIN文を実行すると

id select_type table type possible_keys
1 SIMPLE est ref INDEX_R_S_PRICE
key key_len ref rows Extra
INDEX_R_S_PRICE 44 const,const 1 Using where

のように、INDEX_R_S_PRICEが有効化された。
この状態で、再度、EXPLAINを外した文を実行すると、クエリの実行時間 0.0009 秒となった。
これで、インデックスを張る前よりも1.55%まで処理時間を短縮できた事になる。

同様の手法で、ログを上から片付けていけばDB動作の高速化が望める(但し、インデックスを張りすぎると、INSERT等の更新系処理で、インデックスを構築する負荷が増加するのに注意が必要)

(319)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

MySQLと文字コードと

まじめも:collationでUTF8日本語を使う場合、候補にutf8_general_ciとutf8_unicode_ciのどちらを選ぶか迷う事がある

これについては、
MySQL5.5ユニコードキャラクタセットの説明
の中程に

For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation.

A difference between the collations is that this is true for utf8_general_ci:
ß = s
Whereas this is true for utf8_unicode_ci, which supports the German DIN-1 ordering (also known as dictionary order):
ß = ss

generalはunicodeに比べて高速だと書かれている。
挙動の違いとして、上記エスツェット(ドイツ語拡張文字)の違いが挙げられているが、現在の書法ではss(unicodeの挙動)が正しい。
しかし、日本語の扱いの違いが上げられていないが、実際に評価すると以下の様な挙動を示す。

test_table
id name
1 ユニコード
2 ゆにこーど
SELECT * FROM test_table WHERE name LIKE ‘%ゆにこーど%’;
collation設定 id name
unicode 1 ユニコード
2 ゆにこーど
general 2 ゆにこーど

utf8_unicode_ciにすると、なんとカタカナとひらがな表記が同じであると判定されてしまうのだ。
これだと、実運用上問題が生じることが多いだろうから、一般的には、utf8_unicode_ciではなくutf8_general_ciを使う様にした方がいい。

(294)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

WordPressページ送りが表示されない – MySQLでEXPLAINが毎回実行される

このWordPressを動かしていてページ送り(古い記事へ・新しい記事へ)が動作しない問題が発生した。
MySQLで全SQLログを取得してみると、SELECT文の前に必ず’EXPLAIN 実行予定SELECT文’が実行されていた。
WordPressでは、ページ数を取得するために、MySQLのFOUND_ROWS()を実行して取得している。 これは

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
SELECT FOUND_ROWS();

このように実行すると、SQL_CALC_FOUND_ROWS キーワードをつけたSELECT文のLIMITをつけなかった場合の件数(SELECT count(*) FROM stockの結果に同じ)を得られるのだが、このFOUND_ROWS()は直近の文を読むので、今回の様にEXPLAINが挿入されると↓のようになってしまうので、FOUND_ROWSの戻り値が0になってしまう。

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
EXPLAIN SELECT FOUND_ROWS();
SELECT FOUND_ROWS();

で、このEXPLAINがどこから来ているのか。 WordPressの全ソース検索をしても出てこない。
色々調べて、PHPに行き着いた。
PHP5.3.5ソース
php_mysql.c:1431-1432

int newql = spprintf (&newquery, 0, “EXPLAIN %s”, query);
mysql_real_query(mysql->conn, newquery, newql);

これで実行されていた。 これが実行される条件は、
php_mysql.c:1426

if (MySG(trace_mode))

の条件が指定されていて、このtrace_modeは
php_mysql_structs.h:125

long trace_mode;

で定義されていて、php.iniの

mysql.trace_mode

で設定することが出来て、On(=1)の場合にEXPLAINが自動実行されて、Off(=0)なら実行されないため、WordPressを実行するサーバではmysql.trace_mode=Offとする(php.iniでグローバル設定する以外に、wpの設置ディレクトリに.htaccessを配置してphp_flag mysql.trace_mode Offの記述を入れてもOK)か、WordPressのquery.phpでカウントするSQLをcount()を使う様に修正しなければいけない。

(380)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ

MySQLトリガとか

テーブルstockでレコードが削除されるとき自動でバックアップテーブルに挿入するトリガ

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
END..
DELIMITER ;

MySQLのunix_timestamp()関数により削除時間(now)を追加して記録することが出来る。
全てのUPDATE操作をログしたいとき、DELETEをUPDATEに書き換えれば、トリガでログしておけるので、操作側の修正の手間が省ける。

更に、一定以上古いバックアップを削除する機能もつける場合、END..の前に

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
DELETE FROM stock_backup WHERE unix_timestamp(deleted_on)<(unix_timestamp()-60*60*24*7); END.. DELIMITER ;

の様にすれば、トリガ実行タイミングで7日より前のレコードをバックアップテーブルから自動削除できる。

なお、MySQLでは同じテーブルの同じ条件に複数のトリガを設定できないので、同じ条件に機能追加する場合は

DROP TRIGGER stock_auto_backup

のようにしてトリガを一度削除する。

とあるテーブルのAUTO_INCREMENT値を変更する。

ALTER TABLE stock AUTO_INCREMENT = 1;

この場合はstockテーブルを1にしている。

(132)


カテゴリー: LAMP[Linux, Apache, MySQL, PHP], MySQL | コメントをどうぞ