実行計画とレプリケーション

実行計画 EXPLAIN

クエリとスキーマの最適化を見極める。
http://dev.mysql.com/doc/refman/5.1/ja/explain.html

※show slave status \G でテーブルがみやすくなる

mysql> explain select * from emp;

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

ALL

NULL

NULL

NULL

NULL

50346

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.10 sec)

1.id
SELECT識別子。クエリ内におけるこの SELECTの順序番号

2.select_type
だいたいこれ-> SIMPLE 単純なSELECT (UNIONやサブクエリを使用しない)。
simple
primary
union
union result
※↑これら意外がでたら疑う。

unionクエリの場合これになる↓
mysql> explain select * from emp union select * from emp;

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1	PRIMARY	emp	ALL	NULL	NULL	NULL	NULL	50346
2	UNION	emp	ALL	NULL	NULL	NULL	NULL	50346
NULL	UNION RESULT		ALL	NULL	NULL	NULL	NULL	NULL

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

3 rows in set (0.00 sec)

3.table
結果を得るために参照するテーブル。

4.type
各結合型を最適なものから順に紹介する。
1.「const」 OK PK、UNIQUEインデックスでの等価検索
2.「ref」 OK UNIQUEじゃないインデックスを使用検索時
3.「range」 微妙 インデックスを使用した範囲検索時
4.「index」 微妙 index内でのフルスキャン
5.「ALL」 アウト 全表操作している。

■constになる場合
mysql> explain select * from emp where empid = '1';

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

const

PRIMARY

PRIMARY

4

const

1

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.00 sec)

テーブルに、一致するレコードが最大で 1 つあり、クエリの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。constテーブルは、1 回しか読み取られないため、非常に高速である。
この場合、typeはconstになる

■rangeになる場合
インデックスを使用して、一定の範囲にあるレコードのみが取り出される。

mysql> explain select * from emp where empid > '1';

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

range

PRIMARY,test_idx

PRIMARY

4

NULL

25294

Using where

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.01 sec)

■refになる場合
mysql> explain select deptid, count(*) from emp where hiredate = '1900/04/01';

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

ref

test_idx

test_idx

4

const

2423

Using where; Using index

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.00 sec)

ユニークじゃないインデックスで検索時

■ALLになる場合
インデックスを削除した。
フルテーブルスキャンが実行される。
不適切。

■※注意関数を使うとインデックスを使わない

mysql> explain select deptid, count(*) from emp where cast(hiredate as date) = '1900/04/01' group by deptid;

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

index

NULL

test_idx

8

NULL

50000

Using where; Using index; Using temporary; Using filesort

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.00 sec)

インデックスを使ってGROUP BYを解決できない場合には、テンポラリテーブルを使ってクエリを解決する。

mysql> explain select * from emp;

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1

SIMPLE

emp

ALL

NULL

NULL

NULL

NULL

50346

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

1 row in set (0.00 sec)

5.possible_keys
このテーブル内のレコードの検索に MySQL で使用可能なインデックスを示す。
where句から読み取る。

6.key
keyカラムは、MySQL が実際に使用を決定したキー（インデックス）を示す。

7.key_len
MySQL が実際に使用を決定したキーの長さを示す。 keyが NULLの場合、この長さは NULLになる。

8.ref
検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。
すなわち、where empid = '1'; の用な場合はconst。

9.Extra
MySQL でどのようにクエリが解決されるかに関する追加情報が記載される。
クエリの実行速度をあげたい場合はmUsing filesortとUsing temporaryのExtra値に注目する。
そのクエリを実行するためにオプティマイザがどのような戦略を選択したかということを示すフィールドである。

■Using where
頻繁に出力される追加情報である。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される。

■Using index
クエリがインデックスだけを用いて解決できることを示す。高速。
カバリングインデックスを利用している場合などに表示される。

■Using filesort
クイックソートでソートを行っていることを示す。

■Using index for group-by
MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す。

■Using temporary
クエリの解決に MySQL で結果を保持するテンポラリテーブルの作成が必要であることを示す。これは一般に、GROUP BYを実行したカラムセットと異なるカラムセットに対して ORDER BYを実行した場合に発生する。

■カウント関数の利用
mysql> select count(*) from emp;

• • • • • • • • • • • +

count(*)

• • • • • • • • • • • +

50000

• • • • • • • • • • • +

1 row in set (0.02 sec)

MyISAMでのカウント関数。
MyISAMはinsertされたら集計しているので速い。
mysql> select count(*) from emp_copy;

• • • • • • • • • • • +

count(*)

• • • • • • • • • • • +

50000

• • • • • • • • • • • +

1 row in set (0.00 sec)

パラメーター

my.cnfの設定

innoDBに限る
※エンジンの確認
show table status \G;

※エンジンの変更
ALTER TABLE tbl1 ENGINE InnoDB;

1.innodb_buffer_pool_size
実メモリの8割ぐらい割り当てる。

innodb_buffer_pool_size	8388608
innodb_log_buffer_size	1048576

# vim /etc/my.cnf
innodb_buffer_pool_size=16M
innodb_log_file_size=10M

innodb_buffer_pool_size	16777216
innodb_log_buffer_size	1048576

2.innodb_log_file_size
ログのファイルサイズ。

3.innodb_log_buffer_size
メモリ上のバッファサイズ。

4.max_connections
とりあえず1000くらい設定しても大丈夫な値。

# vim /etc/my.cnf
max_connections=100000

5.slow_query_log,long_query_time
遅いクエリを出す。

# vim /etc/my.cnf

slow_query_log=ON
log_slow_queries = /var/lib/mysql/slow_query.log
long_query_time=0 閾値

# /etc/init.d/mysqld restart

6.クエリキャッシュ

確認
mysql> SHOW VARIABLES LIKE "%query_cache%";

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

Variable_name

Value

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

have_query_cache	YES
query_cache_limit	1048576
query_cache_min_res_unit	4096
query_cache_size	1048576
query_cache_type	ON
query_cache_wlock_invalidate	OFF

• • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • • +

6 rows in set (0.00 sec)

レプリケーションを使った負荷分散

Master 更新系
Slave 参照系

Master が吐き出したログはバイナリログ
Slaveが吐き出したログはリレーログ

Masterが吐き出すバイナリログには何が入るか？
INSERT UPDATE DELETE 更新系
バイナリログ 2種類

• > SQL文 ステートメントベース SQL文
• > 値が記録される 行ベース 更新後の値 5.1.5から

■問い合わせ
1.ロードバランサ (L4ロードバランサ)

長所
AP側か分散しているDBにアクセスしていることを意識しなくていもいい。

短所
購入コスト。
複雑な振り分けができない。

2.APサーバ

長所
プログラム側で接続先を指定できる。

短所
振り分けについて設計しないとだめ。

MySQLのレプリケーション設定

マスターの設定
やること:

①設定ファイルにマスター設定を追加
# vim /etc/my.cnf
server-id=22 <-サーバ識別ID
log-bin=mysql-bin <-バイナリファイルの設定

②ユーザに権限を与える
GRANT REPLICATION SLAVE ON *.* TO 'ユーザ'@'相手のホスト' IDENTIFIED BY 'パスワード'; <-スレイブ権限追加
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.65' IDENTIFIED BY 'replpass';

③マスターの情報確認
SHOW MASTER STATUS;

④データベース作成
CREATE TABLE `pokemon_master` (
`id` BIGINT,
`name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `pokemon_master`(`id`,`name`) VALUES(2,"awamura");

スレーブ側

server-id = 18 (←ユニークな適当な数字へ変更)

また以下を設定ファイル内のどこかに追記(ある場合はそのままでOK)
log-bin = mysql-bin

CHANGE MASTER TO
MASTER_HOST='マスターのIPアドレス',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=189;

SHOW SLAVE STATUS\G