MySqlで巨大データのチューニング

現在作成してるものでメインデータ700万件超。
メインデータに紐づくタグデータ 6000万超。
と個人でなんとかするには無茶なデータ量。
当然、INDEXを張ったりなんだり。

覚えておく事として、mysqlは基本、1INDEXしか使わない。
複数のINDEXをしようして検索しませんよと。サブクエリーとかはまたそこでINDEXを使います。
1SQLで1INDEXというわけではないので注意。

で、当然こんだけあると遅い、遅い。
せめて1秒くらいで欲しいデータをだしたい。

そこで解析に使うのが[EXPLAIN]ですが、項目の簡単な見方。
あくまで簡易的であり私の私見なので間違ってたりするかもしれません。

table:
どのテーブルが対象なのかを表してます。

type:
検索パターンを示してます。ALLは基本的には無くす方向でチューニングします。
indexの場合には要注意くらいでも大丈夫かも。

possible_keys:
INDEX名が表示されます。

row:
実行するために必要だと判定したレコードの件数。
実際の件数ではないが、その件数を見る予定なので乱暴だが少ない方がいいと考えてよい。

Extra:
色々ありますが、Using filesort、Using temporaryが表示された場合は注意した方がいい。

MYSQLでキャッシュをつかわない。

mysqlで1回目は遅いけど同じSQLでは早い。 キャッシュを使ってるので当然といえば当然ですが、チューニングしてる場合にはキャッシュを使わずに計測したい。
いつも間違うのがこっち

SELECT NOCACHE col1,....

正しいのはこっち

SELECT SQL_NO_CACHE col1,...

というわけで備忘録です。

MySQLで縦列を横列にカンマ区切りにしたい。

MySQLで縦列を横列にカンマ区切りにしたいと思い色々検索してみる。 実際にはカラムにカンマ区切りで入れたいってのが要望です。 色々調べてみるが力技がほとんど。有ってもpivotが引っかかる。 どうも自分がしたい感じではない。 タグ表示に近い。 メインデータ

mainid	データ1	データ2
1	A	B

タグデータ

tagid	mainid	name
1	1	C#
2	1	MySQL
3	1	PHP

とDBに登録されているとして。こう表示させたい

id	データ1	データ2	タグ
1	A	B	C#,MySQL,PHP

色々調べた結果MySQLに便利な関数がありました。
GROUP_CONCATです。常識？今回の件で初めてしりましたよ。 以下、こんな感じで書き求めてる結果を出せました。

SELECT * 
FROM main 
  LEFT JOIN (SELECT 
      mainid,
      GROUP_CONCAT(name separator ',') AS タグ
      FROM tag
      GROUP BY mainid) tagd ON tagd.mainid = main.mainid

MySQLのCHARSET

文字化けするのでPDOでSET NAMEみたいなのって調べたら
SET NAMEは脆弱性の元とかいってダメとか。

 $con = new PDO(
     "mysql:host=".consClass::dbserver."; dbname=".consClass::dbname,
     consClass::dbuser,  consClass::dbpass,
     array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8")
 );

接続時にこれでOKでした。

MYSQL クエリーログ

my.iniのConfigに追記。 デバッグ用と思って本番ではコメントにしないと危険（おもに、容量的に）

[mysqld]
log=myquery.log

MySql

MySQL Connector/Netが入ってなかった 下のリンクから取得。(リンクのファイル名は私が取得した時のバージョン) mysql-connector-net-6.7.4-noinstall.zip 取得するのにOracleのアカウントが必要。 特にお金がかかるわけではないのでさくっと登録。 「.NET & MONO」を選択してダウンロード。 解凍すると。V2とV4のフォルダができます。 .Net4はV4のフォルダの「mysql.data.dll」を参照して追加します。 これで使えるようになるはず。