MYSQL
ログ書き込み設定
通常はトランザクション単位でログを書き出す設定だが、
下記で、1秒おきなど一定期間ごとにまとめてログを書き出すようになる。
下記でDBの設定を確認できる
$ sudo mysql -u root
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
下記でDBに設定
[mysqld]
innodb_flush_log_at_trx_commit=2
外部アドレスからの接続を許可
ユーザーの設定を確認
$ sudo mysql -u root
mysql> SELECT user, host FROM mysql.user where user = 'isucon';
+------------------+-----------+
| user | host |
+------------------+-----------+
| isucon | localhost |
+------------------+-----------+
どこからでも接続できるユーザーを作成
$ sudo mysql -u root
> CREATE USER "isucon"@"%" IDENTIFIED BY "isucon";
> GRANT ALL PRIVILEGES ON *.* TO "isucon"@"%";
> SELECT user, host FROM mysql.user where user = 'isucon';
+------------------+-----------+
| user | host |
+------------------+-----------+
| isucon | % |
| isucon | localhost |
+------------------+-----------+
設定ファイルを編集
$ sed -i 's/bind-address/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
$ sudo service mysql restart
チューニング(index編)
slow queryの設定
下記でDBの設定を確認できる
$ sudo mysql -u root
mysql> show variables like 'slow_query%';
下記でDBに設定
[mysqld]
slow_query_log=1
slow_query_log_file=’/tmp/slow.log’
long_query_time=0 ## 本当は1秒とかを設定する?
slow queryを確認
## 合計時間
$ sudo mysqldumpslow -s t /tmp/slow.log | head
## 平均実行時間
$ sudo mysqldumpslow -s at /tmp/slow.log | head
## クエリ件数
$ sudo mysqldumpslow -s c /tmp/slow.log | head
遅いクエリを見つける
対象のSQLをEXPLAINをつけて実行
mysql> EXPLAIN select * from chair where height_category = 1 and kind = '座椅子' and color = '青' and stock > 0 order by popularity desc, id asc limit 10 offset 1;
+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | chair | NULL | ref | idx_height_category,idx_sample_id | idx_height_category | 5 | const | 6177 | 0.33 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
適当な解説
SQLの結果のpossible_keysが使用可能なindexのこと
keyが使用したindexのこと
indexを貼る
mysql> create index idx_custom on isuumo.chair(height_category,kind,stock,id);
再度、対象のSQLをEXPLAINをつけて実行
mysql> EXPLAIN select * from chair where height_category = 1 and kind = '座椅子' and color = '青' and stock > 0 order by popularity desc, id asc limit 10 offset 1;
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | chair | NULL | range | idx_height_category,idx_sample_id,idx_custom | idx_custom | 267 | NULL | 1511 | 10.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
想定したindexがkey
になってればOK
key
が設定したidx_saikyo_cutum
になっってるのでコレはOK
なってなければ頑張る
idを良い感じにしてindexを効かせる
下記のようなSQLが処理の中にあったとする。
select * from estate where (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) order by popularity desc, id asc limit 10;
indexを貼る
create index idx_e_dw_dh_pd_id on isuumo.estate(door_width,door_height,popularity desc,id);
EXPAINで効いてるか確認
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
| 1 | SIMPLE | estate | NULL | ALL | idx_e_dw_dh_pd_id | NULL | NULL | NULL | 28452 | 45.61 | Using where; Using filesort |
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
idx_e_dw_dh_pd_idは使用されていない。
idを取得したあとに取得する用に変更
select * from estate where id in (
select id from estate where (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110)
) order by popularity desc, id asc limit 10;
EXPLAINで確認
+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | estate | NULL | range | PRIMARY,idx_e_dw_dh_pd_id | idx_e_dw_dh_pd_id | 8 | NULL | 14226 | 91.22 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | estate | NULL | eq_ref | PRIMARY | PRIMARY | 4 | isuumo.estate.id | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+
idx_e_dw_dh_pd_idが使われていることが分かる。
SQL_CALC_FOUND_ROWS と FOUND_ROWS()
下記のような同じ条件のSQLを実行していて、rowと件数の取得を実行しているものがあるとする。
最初のSQLで全件取得するとI/Oで時間がかかる。
let estates = conn.exec(
format!(
"select * from estate where {} order by popularity desc, id asc limit ? offset ?",
search_condition
),
¶ms,
)?;
let row = conn.exec_first("select count(*) where {} order by popularity desc, id asc limit ? offset ?", ())?;
let count = row.map(|(c,)| c).unwrap_or(0);
SQL_CALC_FOUND_ROWSを使用してSQLを実行するとlimitやoffsetに関係なく、whereで絞り込んだ条件の件数を保存してくれる。直後にFOUND_ROWS()を呼ぶとその件数を参照できる。
そのため上記のSQLは下記で高速化出来る。
let estates = conn.exec(
format!(
"select SQL_CALC_FOUND_ROWS * from estate where {} order by popularity desc, id asc limit ? offset ?",
search_condition
),
¶ms,
)?;
let row = conn.exec_first("select FOUND_ROWS()", ())?;
let count = row.map(|(c,)| c).unwrap_or(0);
値の量子化と仮想カラム
下記のようなSQLが実行されるとする。
select * from estate where width >= 80 and width < 110
また、widthの値は80以下,80,110,150,150以上しか指定されない。
修正
ASの値に式を書くことで、仮想カラムを追加する。
VITUALだと計算コストがかかるのでSTOREDに設定して実値を保存する。
ALTER TABLE chair ADD width_category INTEGER AS (
case
when width < 80 then 0
when 80 <= width and width < 110 then 1
when 110 <= width and width < 150 then 2
when 150 <= width then 3
else null
end
) STORED;
indexを貼る。
create index idx_width_category on isuumo.chair(width_category);
修正したSQL
上記の設定を利用すると、SQLをは下記のように変更できる
select * from estate where width_category = 1
空間Index と 仮想カラム
下記で貼れる
ALTER TABLE isuumo.estate ADD point POINT AS (POINT(latitude, longitude)) STORED NOT NULL;
CREATE SPATIAL INDEX idx_point on isuumo.estate(point);
insertしてlatitude, longitudeに値を保存した後にカラムを作ろうすると、下記みたいなエラーで作れない。
ERROR xxxx (xxxxxx) at line XX: Invalid use of NULL value
参考文献
MYSQLクエリ設定
https://www.slideshare.net/kazeburo/isucon-summerclass2014action2final
他
https://www.slideshare.net/kazeburo/mysql-casual7isucon
https://qiita.com/kkyouhei/items/d2c40d9e3952c7049ca3