Mysql index


##　レプリケーション
2種類ある

- マスター、スレーブ構成
- デュアル・マスター構成がある

### スレーブ・master


#### master
insert,updateなど

#### スレーブ
select専用

### メリット
- 処理が分散できる。

###仕組み

マスターがスレーブにバイナリーログを送る。
その情報をもとにデータベースが更新します。

### 実装


## インデックス

### 確認方法


# この下の図も書き直し
```
mysql> show index from room;
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| room  |          0 | PRIMARY               |            1 | bill_id     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| room  |          0 | PRIMARY               |            2 | room_id     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| room  |          1 | index_room_on_room_id |            1 | room_id     | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
```

###indexの作成方法

準備

db作成

```mysql
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
```

db選択
```mysql
mysql> use test_db;
Database changed
```

テーブル作成

```
mysql> CREATE TABLE `rooms` (
  `room_id` int(11) NOT NULL default '0',
  `bill_id` int(11) NOT NULL default '0',
  `description` char(40) character set utf8 collate utf8_general_ci default NULL,
  PRIMARY KEY  (`bill_id`,`room_id`)
) ENGINE= InnoDB DEFAULT CHARSET=utf8;

```

データを準備

```ruby
require 'active_record'
require 'mysql2'

development_hash = {"adapter"=>"mysql2", "encoding"=>"utf8", "pool"=>300, "username"=>"root", "password"=>"", "socket"=>"/var/lib/mysql/mysql.sock", "database"=>"test_db"}


ActiveRecord::Base.establish_connection(development_hash)
class Room < ActiveRecord::Base
end

LOOP_COUNT = 1000

LOOP_COUNT.times do |i|
  Room.create(room_id:i,bill_id:[*1..LOOP_COUNT/10].sample,description:[a..z].sample(5))
end
```

#### 現状確認

```
mysql> show index from rooms;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rooms |          0 | PRIMARY  |            1 | bill_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          0 | PRIMARY  |            2 | room_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
```



####すでにあるテーブルにindexを作る

`alter table テーブル名 add index インデックス名(カラム名);`

```mysql
mysql> alter table rooms add index index_rooms_on_rooms_id(room_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

確認すると

```
mysql> show index from rooms;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rooms |          0 | PRIMARY                 |            1 | bill_id     | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          0 | PRIMARY                 |            2 | room_id     | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          1 | index_rooms_on_rooms_id |            1 | room_id     | A         |        1000 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

```

`index_rooms_on_rooms_id`が追加された

##### 削除

`alter table テーブル名 DROP index インデックス名;`

カラム名が作成時異なり、不要

``` 
 mysql> alter table rooms drop index index_rooms_on_rooms_id;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

```