Mysql

0. 主要Issue
1. MYSQLの基本作法
2. Tableへの制約の設定 (整合性制約定義)
3. Dump
4. Sequel Pro の活用
4. Administration
5. Optimization
6. 文字化け/encoding

0. 主要Issue

CAP定理

設計の考え方

インフラレイヤの設計: 開発環境の整備
- 「開発インフラ」と「運用インフラ」の2つを「安く,早く, よいもの」が出来るように設計する
- 意識する変数は下記で、CAP定理に基づき、サービスに応じてコンポーネントを選択する
  - ① Availability: 高可用性: サーバーが一台落ちてもサービスが継続するか
  - ② Consistency: データ整合性:
  - ③ Scalability: Amazon EC2等の普及により、Scale up よりScale outが低コスト,高機能に (運用.管理の手間は増大する傾向)
  - ④ Partition:
- 選択すべきコンポーネントは「DBの種類」と「DBの組み合わせ」
  - MongoDB

Architecture: データの完全性を担保するための制約定義及び、テーブル構造はどのようなものか?

Performance: Performanceを最大化させる、制約定義及び、テーブル構造はどのようなものか?

MySQL WorkBench上での、効率的なArchitecture 設計はどのように行うものなのか?

1. MYSQLの基本作法

Login

mysql -h 127.15.0.1 -P 23306 -u uenoyama -p

basic

     * select [☆1. 縦にColumns as XXX を選択する] 
        * xxx as aaa
        * yyy as bbb
        * count(distinct XXXX) as ccc 
           * IFNULL(式A,式B) as DAU
     * from  [☆2. 選択元のTable を選択する]
        *  table_name 
        * join dbname.tablename g ON g.XXXX = YYYY 
        * join dbname.tablename g ON g.XXXX = YYYY  
     * where  [☆3. 選択する条件と指定する]
        * xxx >= aaa 
        * and yyy = bbb
        * and zzz like ccc
     * group by [4. 出力する条件を指定する]
        *  date
     * order by 
        * date

例

SELECT 
  g.gme_title AS GAME,
  gme_version as VERSION,
  p.ptf_device AS PLATFORM,
  date(lse_client_timestamp) AS DATE,
  cat.ctg_name AS CATEGORY,
  subcat.sctg_name AS SUBCATEGORY,
  count(*) AS LANDING_PAGE_HITS,
  count(distinct lse_usr_id) as UU
FROM `gii_analytics`.`landing_screen_events` master
  JOIN `gii_analytics`.`games` g ON g.gme_id = master.lse_gme_id
  JOIN `gii_analytics`.`platforms` p ON p.ptf_id = master.lse_ptf_id
  JOIN `gii_analytics`.`categories` cat ON cat.ctg_id = master.lse_ctg_id
  JOIN `gii_analytics`.`subcategories` subcat ON subcat.sctg_id = master.lse_subcategory_ctg_id
WHERE
 gme_id = 1
 AND master.lse_usr_id IN (
     SELECT subchild.ugc_usr_id
     FROM `gii_analytics`.`user_catalog` subchild
      WHERE date(subchild.ugc_join_date) < date_sub(master.lse_date, interval 1 day)
      AND subchild.ugc_game_id=master.lse_gme_id
      AND subchild.ugc_ptf_id=master.lse_ptf_id 
      )
GROUP BY 
  GAME, VERSION, PLATFORM, DATE, CATEGORY, SUBCATEGORY
ORDER BY 
  GAME, VERSION, PLATFORM, DATE, CATEGORY, SUBCATEGORY
;

Column情報表示

show columns from table_name;

行数数える

     * select count(*) from tweet;  
     * count(distinct, XXXX)

unique 設定／解除

     * ALTER TABLE companyapp_development.crunchbase_companylist ADD CONSTRAINT UNIQUE(permalink);
        * ALTER TABLE talbe_name ADD [CONSTRAINT [symbol]] UNIQUE [index_name] (col_name,...)
        * ALTER TABLE talbe_name DROP INDEX index_name

重複列削除

     * CREATE TABLE temp_table as SELECT * FROM main_table GROUP BY field_1, field_2
     * create table companyapp_development.crunchdb_companylist as select * from companyapp_development.crunchbase_companylist group by permalink;

change

     * drop 
     * alter table XXX rename to YYY

join (単純結合、等価結合、外部結合、再帰結合)


SELECT
  g.gme_title AS GAME,
  gme_version as VERSION,
  p.ptf_device AS PLATFORM,
  test.usr_udid AS UDID,
  qee_usr_id as USER_ID,
  date(qee_client_timestamp) AS DATE,
  cat.ctg_name AS CATEGORY,
  qee_quest AS QUEST_ID,
  count(qee_quest) AS NUM_ENDS
FROM `gii_analytics`.`quest_end_events` master
  JOIN `gii_analytics`.`games` g ON g.gme_id = master.qee_gme_id
  JOIN `gii_analytics`.`platforms` p ON p.ptf_id = master.qee_ptf_id
  JOIN `gii_analytics`.`categories` cat ON cat.ctg_id = master.qee_ctg_id
  JOIN `gii_analytics`.`users` test ON test.usr_id = master.qee_usr_id
WHERE
  date(qee_client_timestamp) >= date_sub(curdate(), interval 30 day)
  AND gme_title LIKE '%3176'
GROUP BY
  GAME, VERSION, PLATFORM, DATE, QUEST_ID,USER_ID 
ORDER BY
  GAME, VERSION, PLATFORM, DATE, QUEST_ID,USER_ID
;

create table new_table as select * XXXXX.

CREATE TABLE tweetnew (
tweet_id varchar(255),
tweet_time varchar(255),
twitter_user varchar(255),
twitter_user_id varchar(255),
tweet_body varchar(255) );

データ挿入

INSERTINTO tweetnew (tweet_id,tweet_time,twitter_user,twitter_user_id,tweet_body)
select tweet_id,tweet_time,twitter_user,twitter_user_id,tweet_body from tweet_tests limit 10;  #ここにSQL文

Nullを0に変更

  * update crunchdb_companyinfo4 set founded_year=coalesce(founded_year,0);

2. Tableへの制約の設定 (整合性制約定義)

考え方

データの完全性を守るために様々な制約を定義する。Versionによって定義できる制約が異なる
- 行数が増えて行くだけの静的DBか更新が頻繁に掛かる動的DBかで設計が異なる
基本的には各テーブルにPRIMARY KEYを設定し、Crontabで週次／月次更新でアップデート
- 1. 各テーブルの一つのカラムにプライマリーキー制約を付ける
  - UNIQUE, NOT NULL 制約 (PRIMARYという名前のユニークインデックスを作成する事と同義)
  - 複数のカラムに設定すると、対象カラム情報の組み合わせがが完全一致したデータの格納をはじく
- 2. UNIQUE ID番号を自動振り分けする場合はAUTO INCREMENT 制約を用いる
より厳重な完全性が必要な場合(データ更新が頻繁等) は、外部キーを貼ることで、テーブル間の整合性も取る
- 親テーブル値のみを子テーブルに存在させ、データの整合性を担保する
- 適宜更新されていくテーブルを軸に、連鎖更新、連鎖削除を設定する(user名の更新等)

PRIMARY_KEY設定

CREATE TABLE db_name.tbl_name
 (col_name1 data_type1 NOT NULL PRIMARY KEY, col_name2 data_type2, ...);

e.g.

CREATE TABLE  company_list
 (company_id varchar(255) NOT NULL PRIMARY KEY, company_name varchar(255));

http://www.dbonline.jp/mysql/table/index8.html

UNIQUE 制約

INDEX, KEY 設定

FOREIGN KEY(外部キー)制約

親テーブルと子テーブル親 -> 子
- 親テーブルのカラムの値は子テーブルから参照される
- 子テーブルの指定したカラムに格納できる値を親のテーブルに格納されている値だけに限定
  - 親テーブルに存在しない値を持つデータを追加しようとするとエラーが発生
- 親テーブル側のカラムの値を更新したり削除したりする場合には注意が必要
  - Restrict/No action : 親テーブルの更新,削除がエラーになる
  - Cascade: 親テーブルに合わせて、子テーブルを更新
  - Set Null : Null 値になる

AUTO_INCREMENT

CREATE TABLE db_name.tbl_name
  (col_name1 data_type1 NOT NULL PRIMARY KEY, col_name2 data_type2, ...);

3. Dump

◆ Step1: Dump to .sql file

$ mysqldump -u[user] -p [dbname] ([table]) > backup.sql 
$ mysqldump -u uenoyama -p XXXXX > backup.sql
$ mysqldump -u [email protected] -p XXXXX(tweet)  > backup.sql

◆ Step2: scp transfer is better than sftp transfer

$ scp [email protected]:/home/uenoyama/backup.sql   ./
$ scp [email protected]:/home/katsuya/apps/promotionapp/app/views   nana.png

http://cns-guide.sfc.keio.ac.jp/2004/2/6/2.html

◆ Step3: Insert to Mysql 入れたいサーバ

$ mysql -u[user] -p[passwrd]  < backup.sql

1. 元データのサーバからDump $ mysqldump -u[user] -p[passwrd] [dbname] ([table]) > backup.sql

2. 他サーバーへ転送 scp [email protected]:/home/uenoyama/backup.sql ./

scp [email protected]:/home/katsuya/apps/promotionapp/app/views/tweets nana.png

3. 入れたいサーバへの挿入 $ mysql -u[user] -p[passwrd] < backup.sql

4. Sequel Pro の活用

Shot cut

4. Administration

  * create user
  * system authorization

5. Optimization

http://dev.mysql.com/doc/refman/4.1/ja/mysql-optimisation.html

6. 文字化け/encoding

 
 1. emacsのencodingをすべてutf-8に揃える → .emacs をいじる 
    - emacs上で M x  + describe current coding system で表示
 2. Mysql のフォントをutf-8に合わせる

　 - set character_set_server = utf8; みたいなコマンド諸々

 3. DB接続ごとに、処理実行前に「SET NAMES UTF8」というクエリを発行する

「Mysql」をウィキ内検索

最終更新：2012年04月02日 16:15

programming_note @ ウィキ

1. BackEnd

2. Front End

3. UNIX

4. API

5. Scraping

6. Machine Learning

7. Network Analysis

8 Architecture設計

更新履歴