isucon practice



# MYSQL
## ログ書き込み設定

通常はトランザクション単位でログを書き出す設定だが、
下記で、1秒おきなど一定期間ごとにまとめてログを書き出すようになる。

下記でDBの設定を確認できる

```sql:はやくなる設定.sql
$ sudo mysql -u root
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
```

下記でDBに設定

```text:/etc/mysql/my.conf
[mysqld]
innodb_flush_log_at_trx_commit=2
```

## 外部アドレスからの接続を許可

ユーザーの設定を確認

```bash
$ sudo mysql -u root
mysql> SELECT user, host FROM mysql.user where user = 'isucon';
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| isucon           | localhost |
+------------------+-----------+
```

どこからでも接続できるユーザーを作成

```bash
$ 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 |
+------------------+-----------+
````

設定ファイルを編集

```bash
$ sed -i 's/bind-address/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
$ sudo service mysql restart
```

<br>
<br>

## チューニング(index編)

### slow queryの設定

下記でDBの設定を確認できる

```bash
$ sudo mysql -u root
mysql> show variables like 'slow_query%';
```

下記でDBに設定

```text:/etc/mysql/my.conf
[mysqld]
slow_query_log=1
slow_query_log_file=’/tmp/slow.log’
long_query_time=0 ## 本当は１秒とかを設定する？
````
### slow queryを確認

```bash
## 合計時間
$ 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をつけて実行

```sql
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を貼る

```sql
mysql> create index idx_custom on isuumo.chair(height_category,kind,stock,id);
```


再度、対象のSQLをEXPLAINをつけて実行

```sql
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
なってなければ頑張る

<br>

## idを良い感じにしてindexを効かせる

下記のようなSQLが処理の中にあったとする。

```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で効いてるか確認

```sql
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
| 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を取得したあとに取得する用に変更

```sql
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**が使われていることが分かる。


<br>

## SQL_CALC_FOUND_ROWS と FOUND_ROWS()

下記のような同じ条件のSQLを実行していて、rowと件数の取得を実行しているものがあるとする。
最初のSQLで全件取得するとI/Oで時間がかかる。

```rust
let estates = conn.exec(    
    format!(
        "select * from estate where {} order by popularity desc, id asc limit ? offset ?",    
        search_condition
    ),
    &params,  
)?;
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は下記で高速化出来る。

```rust
let estates = conn.exec(    
    format!(
        "select SQL_CALC_FOUND_ROWS * from estate where {} order by popularity desc, id asc limit ? offset ?",    
        search_condition
    ),
    &params,  
)?;
let row = conn.exec_first("select FOUND_ROWS()", ())?;    
let count = row.map(|(c,)| c).unwrap_or(0);   
```

<br>

## 値の量子化と仮想カラム

下記のようなSQLが実行されるとする。

```sql
select * from estate where width >= 80 and width < 110
```
また、widthの値は80以下,80,110,150,150以上しか指定されない。

### 修正

ASの値に式を書くことで、仮想カラムを追加する。
VITUALだと計算コストがかかるのでSTOREDに設定して実値を保存する。

```sql
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を貼る。

```sql
create index idx_width_category  on isuumo.chair(width_category);
```

### 修正したSQL

上記の設定を利用すると、SQLをは下記のように変更できる

```sql
select * from estate where width_category = 1
```

<br>

## 空間Index と 仮想カラム

下記で貼れる

```sql
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に値を保存した後にカラムを作ろうすると、下記みたいなエラーで作れない。

```sql
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