ヤフー社内でやってるMySQLチューニングセミナー大公開 https://www.slideshare.net/techblogyahoo/mysql-58540246
- 処理量をなるべく減らす（INよりEXISTS等）
- 中間テーブル作らない（サブクエリ減らす）
- 暗黙ソートを回避する
- インデックスが効くかどうか
- サブクエリを引数にとる場合は、INよりEXISTSまたは結合（JOIN）
- 結合キーにインデックスが張られていれば、インデックス参照するのみで済む
- INの場合はインデックス参照できない？
- MySQLの場合、サブクエリを含むSQLは外側から内側に向かって順に実行される
- IN句とサブクエリの組み合わせは処理できないらしく内部的にはEXISTSに変換される（MySQL5.6で解決されている？）http://tech.aainc.co.jp/archives/397
- EXISTSは１行でも条件に合致する行があればそこで検索を打ち切る
- INは全表検索してしまう。（NOT EXISTSでも同様）
- 結合(JOIN)であれば、インデックスを利用できる可能性が高まる
- サブクエリがなくなるので、一時的なワークテーブルがなくなる＝利用するメモリが減る
- EXISTSとJOINならEXISTSの方がよいケースがある
- UNION,INTERSECT,EXPECTは普通に使うと重複排除のためのソートを行う
- 重複が発生しない場合は、ALLオプションを使う
- DISTINCTをEXISTSで代用することでソートを回避できる
- MAX,MINもソートを発生させる
- インデックスを張っている項目でMAX,MINを実行させることで高速化できる
- WHERE句で書ける条件はHAVING句には書かない
- GROUP BYによる集約はソートを使うので、事前に行数を絞った方がソートの負荷が軽減できる
- WHERE句の条件でインデックスが利用できるかも
- GROUP BY後は元テーブルのインデックは引き継がれないケースが多い
- GROUP BY やORDER BYはソートを行うがインデックスで検索を高速化できる
- ユニーク・インデックスを持つ列を指定した場合は、ソート自体をスキップできる実装もある
- IS NULL IS NOT NULLを指定した場合はインデックスは利用できない
- 否定形（<> != NOT IN)はインデックスを利用できない
- ORを使うとインデックスが利用できなくなる or ANDに比べると非効率な検索になる（ビットマップインデックスがあるが、更新コストが高くなるデメリットがある
- 複合索引は列の順番を間違えてはいけない
- 列の順番を守れない場合は、別々のインデックスに分割することを検討する
- LIKE述語で、前方一致検索以外はインデックスがきかない（'%a', '%a%'）
- 暗黙の型変換はインデックス利用できない・オーバーヘッドが発生する（明示的な型変換を行う
- 中間テーブルはメモリを消費する
- IN述語で複数キーを利用する場合は１箇所にまとめる
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1333488913 メリット；
- SQL文がすっきりする
- 速度面はあまり期待できない（場合によっては効果あるかも）
- サブクエリの場合、有効な可能性はある
http://naoberry.com/tech/slowquery/
mysql> show variables like 'slow%';
+---------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/query.log |
+---------------------+-----------------------------------------+
3 rows in set (0.00 sec)
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
スロークエリログの設定ファイルを書く
mysql> set global slow_query_log_file = '/tmp/mysql-slow.log';
指定した秒数以上実行にかかったクエリを記録する
mysql> set global long_query_time = 5;
スロークエリログの有効/無効
mysql> set global slow_query_log = ON;
https://nishinatoshiharu.com/mysql-slow-query-log/
- パフォーマンスチューニング後、改めてベンチマークを実行する場合は前回のログが残っていると正しい解析ができない→ログローテートしておく
- スロークエリログはファイル自体が存在しないと出力先ファイルがないということでエラーになる
# mysqldumpslow -s t /var/lib/mysql/query.log
見づらいので見やすくしたい...
# pt-query-digest /var/lib/mysql/query.log
結果も、遅いSQLも綺麗に整えて表示してくれるので、何をすればいいか分かりやすくなる
https://thinkit.co.jp/article/9617 OS：CentOS6
yum install https://www.percona.com/downloads/percona-toolkit/2.2.17/RPM/percona-toolkit-2.2.17-1.noarch.rpm
スローログの「いつからいつまでを集計範囲にするか」を指定できる
- 「--since」オプション
- 「--until」オプション
SELECT `col1` FROM `t1`
WHERE `datetime` >= '2019-02-04 00:00:00'
AND `datetime` <= '2019-02-10 23:59:59'
GROUP BY `col1`
対処：datetimeカラムへのINDEX設定 成果：859s→166s（ひどい）
[MYSQL] datetimeカラムのインデックスの使われ方をテストしてみた https://www.m0de.ml/c0d3man/posts/6ln4gl7s3ohye2s0p02d
8.2.1.20 フルテーブルスキャンを回避する方法 https://dev.mysql.com/doc/refman/5.6/ja/how-to-avoid-table-scan.html
インデックス貼ってもフルテーブルスキャンになる場合
- テーブルがきわめて小さい→フルテーブルスキャンの方が早い
- インデックスが設定されたカラムに対して、ON または WHERE 句に使用可能な制限がない
- 使用可能な制限がない：上記インデックス利用不可ケースを参照
- (インデックスツリーに基づいて) 指定された定数がテーブルのきわめて大きい部分をカバーしている
- SQLを切り分けてPHPで繋ぎこんだ方がいい？
- I/Oが少ない＝いい設計
- PHPと組み合わせてI/Oが少なくなるならそちらがよい
- がんばってSQL一発で書かなくてもI/Oが少なくなるならそちらを取る
- 可読性の面もあるが、自分の技術力不足なのか本当に読みづらいのか判別しづらい問題
https://www.amazon.co.jp/dp/B07JGK82RR/ref=dp-kindle-redirect?_encoding=UTF8&btkr=1
- B+ツリーインデックス：
- 経路情報と木構造を持つ
- 等価比較と範囲検索に利用できる
- LIKE：前方一致のみ
- インデックスを更新するコストは高い→安易につけるべきではない
- インデックスの設計
- クエリが決まってから高速化する為にインデックスを設計する
- インデックスはリファクタは簡単→インデックスは後から変えられる
- 正規化は重要
- カラム数が絞られる→インデックスが減る
- インデックスが多いと更新性能悪化+容量を食う
- 主キーを必ずつける→重複行をなくす
- NULLはINDEXと相性が悪い
- 最適なインデックスをつけるには？
- 検索条件に適合したインデックスが必要
- なんでもかんでもインデックスを作るのもダメ
- インデックスが増えるほどテーブル更新時のオーバヘッド増・容量増
- インデックス使わないでもいいケース
- インデックスを使うクエリの実行頻度が低い
- テーブルのサイズが小さい
- インデックスが利用可能なケース
- WHERE句：カラムが等号・不等号で比較
- 前方一致であること
- WHERE句：カラムが等号・不等号で比較
- 基本的にTEXT型はINDEXを貼ったり条件に使ったりするのは苦手で低速
- 最大長がわかっていてVARCHAR型にすることができるのであれば型を変えることもおすすめ
- https://teratail.com/questions/63901
- カバリングインデックス：
- SQL文に出てくる全ての列がインデックスに含まれる場合
- インデックスのはたらきによって、テーブルアクセスしなくても良くなる＝超高速
- あらゆるチューニング法の中でも最強の方法の1つ
- https://use-the-index-luke.com/ja/sql/clustering/index-only-scan-covering-index
- マルチカラムインデックス：
- 制約
- INDEX作成時のカラム順に基づいてWHERE句が組み立てられてないとINDEXが使えない
- １テーブルにつき１つのINDEXしか使用できない
- WHERE句：
- 「=」（一致）の項目を先に持ってくる
- 「>」「<」「!=」の項目は膨大な検索結果がヒットする可能性があるので後にする
- 例） SELECT * FROM t WHERE col1 > 100 AND col2 = 'abc'
- col2,col1の順序でマルチカラムインデックスを設定する
- 基本的に「OR」でマルチカラムインデックスは使えない（項目ごとにインデックスを設定する必要がある）
- GROUP BY句：
- 最も効率的な方法はインデックスを使用してグループ化するカラムを直接取得する事
- https://dev.mysql.com/doc/refman/5.6/ja/group-by-optimization.html
- JOIN句
- 結合されるテーブルの順序が大事
- 駆動表：最初にアクセスされる表
- 内部表：その表に対して結合されるもう一方の表
- こちらはインデックス利用される
- https://www.magata.net/memo/index.php?%C9%BD%B7%EB%B9%E7%A4%CE%B4%F0%C1%C3%C3%CE%BC%B1
- クラスタ化されたインデックス：主キーインデックスを表す InnoDB 用語
- セカンダリインデックス：クラスタ化されたインデックス以外のインデックス
- 何処に索引を付けるべきか
- 駆動表の絞り込み条件となる列
- 内部表の結合条件となる列
- JOINの結合条件→JOIN後のデータへのWHERE句での条件でマルチカラムインデックスを利用すると効果的
- 下のSQLだとt2のcol2,col4のマルチカラムインデックス
- t1は駆動表でインデックス利用できないのでマルチカラムインデックスは利用できない
- 制約
SELECT * FROM t1 JOIN t2
ON t1.col1 = t2.col2
WHERE t1.col3 = 100
AND t2.col4 = 'abc'- テーブルを複数のパーティションに分割しキーによってどのパーティションに属すべき行なのかを振り分ける
- パーティションキーからどのパーティションに属するか分かる→検索速度向上
- パーティショニングが適したケース
- 検索によって大量のデータがフェッチされる場合
- パーティションが絞り込める場合（絞り込めないと遅くなる）
- 実際利用するといいケースはあまりない
パフォーマンスに影響を与える要因の一つが、ディスクI/Oです。 ディスクI/Oを抑えられるよう、SQL実行時に使うメモリを適切に設定することで パフォーマンスを改善することが出来ます。
データとindexをメモリ上にキャッシュする領域です。 poolが一杯になると、古くて使わないものから削除されていきます。
- ディスクに書き込まれているデータを吸い上げてメモリに乗せられる→データの検索が高速になる
- 「書き込むとき」ときのパフォーマンスの向上にも繋がる
基本データは全部バッファープールに載せる
更新頻度の高いテーブルのインデックスや 検索に時間のかかるインデックスなどの分はバッファープールに入れても良い
- データとインデックスを含めたサイズを割り当てる(理想)
- データのサイズを割り当てる
InnoDBの利用状況を確認する
SHOW ENGINE INNODB STATUS \G;Free buffers：確保したバッファプールを使い切っていないか Pages made young xxx, not young xxx：pool内のリストが更新され続けているか （youngの値が大きければ更新頻度高い。not youngが大きければ更新頻度低い
参考）innodb_buffer_poolの適正値を算出してくれるクエリ innodbテーブルの全てのデータサイズとインデックスサイズを合計したものが理想値とのこと。 https://corporate.inter-edu.com/developper/1373
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;データの変更（INSERT/UPDATE）があった時に、この変更分をハードディスクのファイル上にためておくサイズです。 http://mondenu.blog86.fc2.com/blog-entry-53.html
- INSERT・UPDATEによる負荷を軽減
- 変更分を貯めていって、ファイルの上限までくるとDBに書き込みに行く→DBへの書き込みが減り、速くなる
table_cacheは1度開かれたテーブルをメモリ上に維持し再利用することでテーブルを開くことによる負荷を低減するためのもの MySQLではオープンしているテーブル数は「show open tables」コマンドで確認できます。
https://github.com/major/MySQLTuner-perl mysqlTunerというOSSを利用し簡単に設定項目の適正値を求めることが出来ます。
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv- Recommendationsをチェックする
https://qiita.com/Ets/items/a631e91acb563e42da7d https://soudai.hatenablog.com/entry/mackerel-plugin-mysql
OS：CentOS6
curl -fsSL https://mackerel.io/file/script/setup-all-yum.sh | MACKEREL_APIKEY='xxxxx' sh
# yum install mackerel-agent-plugins
試しに「mackerel-plugin-mysql」を実行してみる
# mackerel-plugin-mysql -host=localhost -username=xxxx -password=xxxx
問題なく各種項目の値が表示されるようであれば、下記設定箇所にコマンドを入れる
# /etc/mackerel-agent/mackerel-agent.conf
[plugin.metrics.mysql]
command = "mackerel-plugin-mysql -host=localhost -username=xxx -password=xxx"
mackerel-agentを再起動する
# /etc/init.d/mackerel-agent restart
これで、カスタムメトリックにmysqlの項目が追加される