2015年07月13日

カンマ区切りのデータを検索する時に便利な「FIND_IN_SET()」

LIKE演算子であいまい検索を行ったりする場合に、引っかかってほしくないデータまで引っかかってしまう場合があります。例えばフォームからチェックボックスを付けた値だけを受け取る場合、カンマ区切りで数値を文字列として保存する方法がよく用いられますが、このような場合に便利なのが「FIND_IN_SET()」です。

続きを読む
posted by Liang Zhaohong at 11:25 | Comment(0) | TrackBack(0) | MySQL | このブログの読者になる | 更新情報をチェックする

2013年09月16日

クエリを最適化するためのステートメント「EXPLAIN」

ある日、200万件を超えるレコードを抱えたDB(MySQL)が言うことをきかなくなってしまいました。というのも、やりたいことをやろうとクエリを書いても、遅くて遅くて最後にはエラーが発生してしまうような状態になってしまったのです。

これは困った、きっと自分のせいだろう、と調べて見た結果、「EXPLAIN」というキーワードにたどり着きました。「EXPLAIN」を実行してクエリがどのように実行されているかを確認できるそうです。

例えば「test1」というテーブルに対してこのように実行すると、「type」が「ALL」となっています。

mysql> EXPLAIN SELECT * FROM table;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | test1 | ALL | NULL | NULL | NULL | NULL | 2057552 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+


これに対して、「WHERE」句を追記して実行すると、「type」が「const」となりました。

※uidにPRIMARY KEYが設定されている前提です

mysql> EXPLAIN SELECT * FROM table WHERE uid = 10;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+


この「type」が重要との事で、「type」には下記のような種類があります。

const ・・・ 最速。PRIMARY KEYもしくはUNIQUEインデックス。
eq_ref ・・・ constに並ぶ速度で、JOINにおけるPRIARY KEYまたはUNIQUE KEY。
ref ・・・ ユニーク(PRIMARYもしくはUNIQUE)ではないインデックスを使った等価検索(WHERE key = value)。
range ・・・ インデックスによる範囲検索。
index ・・・ 遅い。インデックス全体のスキャン。
ALL ・・・ 遅い。全表検索。OLTP系の処理では改善必須。


文字通りとすれば、「type」が「index」もしくは「ALL」となっていると、表示速度が遅くなる=クエリの改善が必要な状態だと考えられます。上記例でWHERE句を追記する事で解決しているように、なんらかクエリを見直すことで「type」が変わり、それがconstなら最速である、と認識できます。

MySQLで多くのレコードがあり、どうにも表示速度が改善できない場合は、一度「EXPLAIN」を使ってクエリを見直してみましょう。
タグ:explain
posted by Liang Zhaohong at 00:02 | Comment(0) | TrackBack(0) | MySQL | このブログの読者になる | 更新情報をチェックする

2013年06月12日

MySQLでテーブルが壊れてしまった時の対処法

急なトラブルが恐ろしいサーバー運用ですが、今回はMySQLのテーブルが壊れてしまうという恐ろしい事態に。原因はサーバー再起動時にうまく起動できなかったのか、フロント側を止めずに再起動したのが悪かったのか、定かではありませんが、とにかく壊れているのです。

確認してみたところ、あるテーブルにおいて下記のようなエラーが発生していました。

****** is marked as crashed and should be repaired.


なんじゃこら。という事で、下記のようにしてみました。
※ユーザー、パスワード、テーブル名等は仮です
※実行は自己責任にてお願いします

とりあえずMySQLにログイン。
$ mysql -u user -p↵
Enter Password:password


DBを選択。
mysql >use dbname;↵
Database changed


おかしいと思うテーブルをチェック。
mysql >CHECK TABLE tablename MEDIUM;↵
+------------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------------------------+
| dbname.tablename | check | warning | Table is marked as crashed |
+------------------+-------+----------+----------------------------


crashedなんですね。壊れてますね。という事で、修復!
mysql >REPAIR TABLE tablename;↵
+------------------+--------+----------+------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------+--------+----------+------------------------------------+
| dbname.tablename | repair | warning | Number of rows changed from 0 to 1 |
| dbname.tablename | repair | status | OK |
+------------------+--------+----------+------------------------------------+
※約170万レコード、約6GBの内容で、30分待ちました
※REPAIR TABLE 実行中は書き込みも読み込みも出来なくなるようです


ちゃんと修復されているか確認してみます。
mysql >CHECK TABLE tablename;↵
+------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+-------+----------+----------+
| dbname.tablename | check | status | OK |
+------------------+-------+----------+----------+


特に REPAIR TABLE を実行している間、不安で不安でたまりませんでしたが。。
待てばちゃんと実行してくれるんですね。無事解決してほんま良かった;;;;;
MySQLでテーブルが壊れてしまった時の解決法でした。
posted by Liang Zhaohong at 23:33 | Comment(0) | TrackBack(0) | MySQL | このブログの読者になる | 更新情報をチェックする

2010年12月10日

MySQLで一括置換

このカラム内の、このテキストを、一括置換したい。よくこんな場面に出くわすたび、コツコツと作業しながら『一括置換出来ると便利なのになぁ〜」と思っていた矢先。やっぱり方法はあるのですね。先に調べればよかった;

update `table` set name=replace (name,"置換したいテキスト","置換しました");

「table」というテーブルの、「name」というカラム内にある、「置換したいテキスト」というテキストを、「置換しました」に一括置換する、という例です。もちろん例の通り、日本語OKです。ただし、どんな環境でも100%大丈夫という保障は出来かねますので、試す前にバックアップを取る事をお勧めします。
posted by Liang Zhaohong at 23:45 | Comment(0) | TrackBack(0) | MySQL | このブログの読者になる | 更新情報をチェックする

2010年03月11日

MySQLで使用してはいけない『予約語』について

DBに接続できない。DBの内容が表示されない。またまた発生した、簡単そうで抜け出せないバグ地獄。


DBからデータを呼び出すのは毎度のごとく自己流ライブラリを使用しているにも関わらず、エラーが発生する。同じソースで他サイトならエラーにならないのに、このサイトではエラーが発生する。そんな時に調べて調べて1時間。なにやらMySQLには「カラム名」「テーブル名」に使用してはいけない『予約語』というものがあるらしく。これに気付かずハマってしまうとなかなか抜け出ることが出来ないやっかいモノ。


MySQLでどうしてもエラーが解決できない場合、以下いずれかの単語をテーブル名もしくはカラム名に使用していないかチェックして、使用している場合はリネームしてみましょう。簡単にバグ地獄から抜け出せるかもしれません。



【予約語一覧】
ADD
ALL
ALTER
ANALYZE
AND
AS
ASC
BEFORE
BETWEEN
BIGINT
BINARY
BLOB
BOTH
BY
CASCADE
CASE
CHANGE
CHAR
CHARACTER
CHECK
COLLATE
COLUMN
COLUMNS
CONSTRAINT
CONVERT
CREATE
CROSS
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
DATABASE
DATABASES
DAY_HOUR
DAY_MICROSECOND
DAY_MINUTE
DAY_SECOND
DEC
DECIMAL
DEFAULT
DELAYED
DELETE
DESC
DESCRIBE
DISTINCT
DISTINCTROW
DIV
DOUBLE
DROP
DUAL
ELSE
ENCLOSED
ESCAPED
EXISTS
EXPLAIN
FALSE
FIELDS
FLOAT
FLOAT4
FLOAT8
FOR
FORCE
FOREIGN
FROM
FULLTEXT
GRANT GROUP
HAVING
HIGH_PRIORITY
HOUR_MICROSECOND
HOUR_MINUTE
HOUR_SECOND
IF
IGNORE
IN
INDEX
INFILE
INNER
INSERT
INT
INT1
INT2
INT3
INT4
INT8
INTEGER
INTERVAL
INTO
IS
JOIN
KEY
KEYS
KILL
LEADING
LEFT
LIKE
LIMIT
LINES
LOAD
LOCALTIME
LOCALTIMESTAMP
LOCK
LONG
LONGBLOB
LONGTEXT
LOW_PRIORITY
MATCH
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
MIDDLEINT
MINUTE_MICROSECOND
MINUTE_SECOND
MOD
NATURAL
NOT
NO_WRITE_TO_BINLOG
NULL
NUMERIC
ON
OPTIMIZE
OPTION
OPTIONALLY
OR
ORDER
OUTER
OUTFILE
PRECISION
PRIMARY
PRIVILEGES
PROCEDURE
PURGE
RAID0
READ
REAL
REFERENCES
REGEXP
RENAME
REPLACE
REQUIRE
RESTRICT
REVOKE
RIGHT
RLIKE
SECOND_MICROSECOND
SELECT
SEPARATOR
SET
SHOW
SMALLINT
SONAME
SPATIAL
SQL_BIG_RESULT
SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT
SSL
STARTING
STRAIGHT_JOIN
TABLE
TABLES
TERMINATED
THEN
TINYBLOB
TINYINT
TINYTEXT
TO
TRAILING
TRUE
UNION
UNIQUE
UNLOCK
UNSIGNED
UPDATE
USAGE
USE
USING
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
VALUES
VARBINARY
VARCHAR
VARCHARCTER
VARYING
WHEN
WHERE
WITH
WRITE
X509
XOR
YEAR_MONTH
ZEROFILL

【予約語一覧(MySQL 4.0)】
CHECK
FORCE
LOCALTIME
LOCALTIMESTAMP
REQUIRE
SQL_CALC_FOUND_ROWS
SSL
X509
XOR

【予約語一覧(MySQL 4.1)】
BEFORE
COLLATE
CONVERT
CURRENT_USER
DAY_MICROSECOND
DIV
DUAL
FALSE
HOUR_MIXROSECOND
MINUTE_MICROSECOND
MOD
NO_WRITE_TO_BINLOG
SECOND_MICROSECOND
SEPARATOR
SPATIAL
TRUE
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
VARCHARACTER



「mysql_free_result」や「mysql_free_result」でエラーが出ていればまずチェックをおすすめします。



ino@
posted by Liang Zhaohong at 01:55 | Comment(0) | TrackBack(0) | MySQL | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。