トップ 差分 一覧 ソース 検索 ヘルプ PDF RSS ログイン

MySQL Tips

[カテゴリ:MySQL]

ユーザ権限

select user, host from mysql.user;
show grants for <username>;

ユーザホストの%には、localhostは含まれない?

MySQL ユーザのホストをワイルドカードで指定してもlocalhostは含まれない | b.l0g.jp
http://b.l0g.jp/mysql/user-at-localhost/

SELECT結果をCSV,TSV出力したい。

MySQL の結果を csv 形式で標準出力させたい - BOOLEANLABEL
http://d.hatena.ne.jp/fd0/20090801/p1

mysqldump や SELECT ... INTO OUTFILE でもできるが、、、

CSVなら

% mysql -uroot -pvertrigo --protocol=tcp -e "SELECT * FROM help_category limit 5" mysql | sed -e 's/\t/,/g'

TSVなら(パイプ経由しないとタブ区切りにならない)

% mysql -uroot -pvertrigo --protocol=tcp -e "SELECT * FROM help_category limit 5" mysql | cat

mysqldump

mysqldump の --single-transaction、--master-data、--flush-logs オプションの違い

mysqldump --single-transaction に --flush-logs をつけてはいけない - @tmtms のメモ
http://tmtms.hatenablog.com/entry/20110713/mysqldump

mysqldump - innodbの場合

エクスポート

mysqldump -u root -pPASS --single-transaction DBNAME | gzip > DBNAME_`date +%Y%m%d%H%M%S`.sql.gz

インポート

zcat XXXX.sql.gz | mysql -u root -pPASS DBNAME

リモートでmysqldump&圧縮をして、ローカルファイルに保存する。

time (ssh user@host "mysqldump -u root -pPASS --single-transaction --all-databases | gzip" > mysql_host_`date +%Y%m%d%H%M%S`.sql.gz)

ローカルのdumpファイルを使って、リモートのmysqlに流し込む

time (zcat XXXX.sql.gz | ssh user@host "mysql -u root -pPASS)

クエリログ

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql.log';

スロークエリーログ

show variables like 'slow%';
show variables like 'long%';
SET GLOBAL long_query_time = 0;
SET GLOBAL slow_query_log = 1;

漢(オトコ)のコンピュータ道: MySQL 5.1のスロークエリログ
http://nippondanji.blogspot.jp/2009/01/mysql-51.html

MySQL、warningをすぐに表示させる

cl.pocari.org - MySQL の warnings をすぐに表示させる方法
http://cl.pocari.org/2007-10-01-1.html
ysql/calc_found_rows.html

LIMITとCOUNT(*)を1クエリーで行う。

SQL_CALC_FOUND_ROWS を毎回忘れるのでメモっておく件 - Yet Another Hackadelic
http://d.hatena.ne.jp/ZIGOROu/20091126/1259218194

LIMITで件数制限をしつつ、全件数を取得する方法 SQL_CALC_FOUND_ROWS FOUND_ROWS() - [MySQL/SQL] ぺんたん info
http://pentan.info/sql/mysql/calc_found_rows.html

キャッシュ

設定表示

show variables like '%query%';

状況表示

SHOW STATUS LIKE'Qcache%';

クリア

RESET QUERY CACHE;

キャッシュを無効化

SET @@global.query_cache_type=0;

MySQL クエリーのキャッシュヒット率をSQLで求める方法 - Qiita
http://qiita.com/hit/items/18962283e94c709b19a6

キャッシュヒット率計算

SELECT 
  (SELECT VARIABLE_VALUE 
  FROM 
     INFORMATION_SCHEMA.GLOBAL_STATUS 
WHERE 
  VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE) 
FROM 
  INFORMATION_SCHEMA.GLOBAL_STATUS 
WHERE 
  VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;

チューニング

MySQLノウハウ
http://txqz.net/blog/2006/12/13/0943

日々の覚書: MySQLをプロファイる(仮) at MyNA(日本MySQLユーザ会)会 2014年4月
http://yoku0825.blogspot.jp/2014/04/mysql-at-mynamysql-20144.html

フレームワーク開発時代のSQLチューニング基礎(1)EXPLAIN句|Media Technology Labs (MTL) : メディアテクノロジーラボ
# 具体的かつまとまっており読みやすい。
http://mtl.recruit.co.jp/blog/2011/02/sql.html

MySQL Index勉強会外部公開用
# まとまっており読みやすい。
http://www.slideshare.net/crooz_techblog/mysql-index-26016127

インデックス

現在のインデックスを表示

SHOW INDEX FROM <table name>;

INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011 - SH2の日記
http://d.hatena.ne.jp/sh2/20111217

MYSQL INDEXのまとめ - Yuta.Kikuchiの日記
http://d.hatena.ne.jp/yutakikuchi/20110418/1303083112

きょうもぼへぼへちゃんがゆく : MySQL インデックスのチューニング
http://blog.livedoor.jp/ashibuya0128/archives/51723503.html

vmstat,topからボトルネックを見つける

MySQL のチューニング (ボトルネックの検出) : Figure out!! -ドリコムエンジニアブログ
http://eblog.drecom.jp/entry/10

MySQLでInnoDB、バランスを取り戻せ! (ゆめ技:ゆめみスタッフブログ)
http://yumewaza.yumemi.co.jp/2009/09/mysqlinnodb_1.html

innodb、bufferpoolヒット率


(1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)) ×100

計算に必要な値の確認

mysql> show global status like "innodb_buffer_pool_read%";
+-----------------------------------+-------------+
| Variable_name                     | Value       |
+-----------------------------------+-------------+
| Innodb_buffer_pool_read_ahead_rnd | 4774        |
| Innodb_buffer_pool_read_ahead_seq | 26915       |
| Innodb_buffer_pool_read_requests  | 53493207281 |
| Innodb_buffer_pool_reads          | 121121      |
+-----------------------------------+-------------+
4 rows in set (0.01 sec)

算出クエリー

select (1 - ((select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_reads') / (select variable_value from information_schema.global_status where variable_name = 'innodb_buffer_pool_read_requests') )) * 100 as `innodb buffer pool ヒット率`;

innodb、SHOW INNODB STATUS で LAST DETECTED DEADLOCK が大きいとき、これ以降の項目が出力されない場合の対策

How to deliberately cause a deadlock in MySQL | Xaprb
http://www.xaprb.com/blog/2006/08/08/how-to-deliberately-cause-a-deadlock-in-mysql/

EXPLAIN(実行計画)

漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。
http://nippondanji.blogspot.jp/2009/03/mysql_25.html

MySQL の filesort プチテクニック - kazuhoのメモ置き場
http://d.hatena.ne.jp/kazuhooku/20081208/1228707040

MySQLのチューニング(インデックス&SQL) - Shoulder.jp
http://canalize.jp/archives/010429.php

EXPLAIN例

EXPLAINの出力順=テーブルの処理順

mysql> EXPLAIN SELECT * FROM Country, (SELECT * FROM City WHERE Population > 1000000) AS C1 WHERE Country.Code = C1.CountryCode;
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL           |  237 |             |
|  1 | PRIMARY     | Country    | eq_ref | PRIMARY       | PRIMARY | 3       | C1.CountryCode |    1 |             |
|  2 | DERIVED     | City       | ALL    | NULL          | NULL    | NULL    | NULL           | 4079 | Using where |
+----+-------------+------------+--------+---------------+---------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

EXPLAINフィールド説明

select_type = クエリの種類

  • SIMPLE・・・・・・・・・JOIN
  • PRIMARY ・・・・・・・・外部クエリを示す。
  • SUBQUERY・・・・・・・・相関関係のないサブクエリ。
  • DEPENDENT SUBQUERY・・・相関関係のあるサブクエリ。
  • UNCACHEABLE SUBQUERY・・実行する度に結果が変わる可能性のあるサブクエリ。
  • DERIVED ・・・・・・・・FROM句で用いられているサブクエリ。

table = アクセスするテーブル

type = インデックスに対するアクセス方法

  • const ・・・PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。
  • eq_ref・・・JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。
  • ref ・・・・ユニーク(PRIMARY or UNIQUE)でないインデックスを使って等価検索(WHERE key = value)を行った時に使われるアクセスタイプ。
  • range ・・・インデックスを用いた範囲検索。
  • index ・・・フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。
  • ALL ・・・・フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。

possible_keys = 利用可能なインデックスの候補

key = 選択されたキー

key_len = keyの長さ

ref = keyとJOINされるフィールド

rows = tableからフェッチされる行数の見積り

tabel=DERIVED の場合、EXPLAINは実際にサブクエリを実行するので正確な行数になる。
(tabel=DERIVED があると EXPLAINに時間がかかる)

この行数の全てが結果としてクライアントへ送られるわけではない。
(Extra=Using where の場合、WHERE句の条件が適用される為)

Extra = オプティマイザの戦略

  • Using where・・・頻繁に出力される追加情報である。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。
  • Using index・・・クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合などに表示される。
  • Using filesort・・・filesort(クイックソート)でソートを行っていることを示す。
  • Using temporary・・・JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す。

Extra field - MySQL Practice Wiki
http://www.mysqlpracticewiki.com/index.php/Extra_field

exists, not exists のチューニング

EXISTSとSQLの高速化について - 猫好きモバイルアプリケーション開発者記録
http://kkoudev.github.io/blog/2013/09/14/sql/

"Not Exists" なクエリの最適化 - いちいの日記
http://d.hatena.ne.jp/ichii386/20070705/1183575658

innodb 行ロック、テーブルロック

InnoDBで行ロック/テーブルロックになる条件を調べた #mysqlcasual Advent Calendar 2013 - あおうさ@日記
http://bluerabbit.hatenablog.com/entry/2013/12/07/075759

いくらwhere句で絞り込んで更新対象を1レコードとしても、
1レコードだけの行ロックになるとは限らない。

レコードにロックするというか、、、
「インデックスで参照したレコード」にロックするというイメージの方が近い。

テーブルサイズ確認

データベースとテーブルのサイズを確認する方法 - ふってもハレても
http://d.hatena.ne.jp/sho-yamasaki/20120405/1333640589

select  
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
Data_free,
floor((data_length+index_length)/1024/1024) as allMB,  #総容量
floor((data_length)/1024/1024) as dMB,  #データ容量
floor((index_length)/1024/1024) as iMB   #インデックス容量
from information_schema.tables  
where table_schema=database()  
order by (data_length+index_length) desc; 

InnoDBのロックの範囲とネクストキーロックの話 - かみぽわーる
http://blog.kamipo.net/entry/2013/12/03/235900

ロックの範囲には、レコードロック、ギャップロック、そして、
両者を組み合わせたネクストキーロックがある。

フラグメンテーション

MySQL Data Fragmentation - What, When and How ― DatabaseJournal.com
http://www.databasejournal.com/features/mysql/article.php/3927871/MySQL-Data-Fragmentation---What-When-and-How.htm

一時ファイル(tmp files)、一時テーブル(tmp tables)

MySQLのテンポラリテーブル - fixersの日記
http://d.hatena.ne.jp/fixers/20121227/1356570495

  • created tmp tables ・・・ テンポラリテーブルが作成された回数(MEMORY/MyISAM)
  • created tmp disk tables ・・・テンポラリテーブルがディスク上に、つまりMyISAMに作成された回数
  • created tmp files ・・・ ソート時にsort_buffer_sizeに収まりきらず、tmpファイルができた回数

ディスクへのアクセスは
created tmp disk tables と
created tmp files
これを減らせるかが重要

created tmp disk tables が多い場合、tmp_table_size(=max_heap_table_size) のサイズを増やす。

created tmp files が多い場合、
sort_buffer_size のサイズを増やす。

ディスクそのもののパフォーマンスは、

(FILE=/tmp/ddtest; dd bs=1M count=1000 if=/dev/zero of=${FILE} oflag=direct; rm -f ${FILE})

indexを大きく変更するとき

  • ALTER TABLE でのインデックス追加や削除は、内部的には結局、テーブルのデータを一から作り直しているそうだ。
  • 大きなテーブルのインデックスを変更する場合、ALTER TABLE で 1インデックス単位で変更すると、時間がかかりすぎる。カンマ区切りで記述すれば、1回のALTERで済む。

自前でテーブルをコピーする。

コピー元テーブル名を「aaa」とする。

元テーブルの構造を確認しておく。

SHOW CREATE TABLE aaa\G

元テーブルをリネームする。

ALTER TABLE aaa RENAME TO aaa_yyyymmdd;

新テーブルを作成する。このときindexなどを新しいものに定義しなおしておく。

CREATE TABLE aaa …

リネームしていたテーブルのデータを、新テーブルに流し込む。

INSERT INTO aaa SELECT * FROM aaa_yyyymmdd;

リネームしていたテーブルは不要になったので削除する。

DROP TABLE aaa_yyyymmdd;

注意点

  • CREATE TABLE の AUTO_INCREMENT=999 の値は、1にしておく。INSERT INTO … SELECT … で流し込んだデータのMAX値+1が最終的にセットされることになる。

in句でサブクエリーが遅い場合

mysqlでは、in句が処理できないらしい。そのため、内部的にexists句に置き返されるので、想定外に遅いことが発生しうる。
その場合は、inをjoinに書き換える。
mysql5.6ではオプティマイザが改善され解消されるらしい。

MySQLではIN句とサブクエリの組み合わせはインデックスが効かない!? | アライドアーキテクツ エンジニアブログ
http://tech.aainc.co.jp/archives/397

Packet for query is too large (999999999 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

1048576 が現在の設定
999999999が必要サイズ

mysql> set global max_allowed_packet = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> show variables like 'max_all%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_all%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

/etc/my.cnf

[mysqld]
max_allowed_packet=16M

最終更新時間:2015年08月04日 15時54分17秒