WebアクセスSQL挿入について考える(課題)。

(1) webページへのアクセスを、mysqlに保存するとしたら、どんなcreate tableにするか。

  • principal的なのものはcookie、対象はurl、あとはアクセス時間が、このドメインで必要な情報
  • アクセステーブルには、この3つの情報とテーブルへのinsert日時を保存。あと、cookieとユーザーidを関連づける、cookie_userテーブルもついでに定義
  • ミリ秒マイクロ秒の単位で同じ時刻に、同じcookieが別urlにアクセスすることは無い、ユーザーは複数のcookieを持つ、という前提
  • どういうリレーションか説明
  • 制約かけれるところはかける
  • パスワードカラムを追加して、そこに保存するデータを生成する関数作って。生パスワードを受け取って、dbに保存すべき値を返すもの。

  • パスワード作成(python3)

>>> a = PasswordHasher("kokok")
>>> a.password_encode()
'sha256$10000$AD9UMsvQ$c915a34ec0eadfceefc57cf55a2a97a5fae41c6b88bdade43ec6054dbf2c4446'
import hashlib
import string

class PasswordHasher(object):

    def __init__(self, password, salt=None):
        self.password = password
        self.hashname = "sha256"
        self.iterations = 10000
        if salt is None:
            self.salt = self.salt()
        else:
            self.salt = salt

    def password_encode(self):
        hash = make_hash(self.password, self.salt, self.iterations)
        return "%s$%d$%s$%s" % (self.hashname, self.iterations, self.salt, hash)

    def make_hash(self, password, salt, iterations=10000):
        if not isinstance(password, str):
            return False
        term = salt + password
        for i in range(iterations):
            term = term.encode('ascii')
            m = hashlib.sha256()
            m.update(term)
            term = m.hexdigest()
        return term

    def salt(self, n=8):
        return ''.join(
            [random.choice(string.ascii_letters + string.digits) for i in range(n)]
        )
  • mmid作成:mmid_maker(python3)
from datetime import datetime
import uuid
import random


TOP_PAD = 1 << (55 + 4)
TS_LOW_MASK = (1 << 12) - 1
TS_HIGH_MASK = (1 << 55) - 1 - TS_LOW_MASK
VERSION = 4 << 12
LOCAL_EPOCH_TS = datetime(2017, 1, 1).timestamp()


def mmid_maker():
    # 16バイトのmmidを作成する関数

    ts = int((datetime.now().timestamp() - LOCAL_EPOCH_TS) * 1e7)
    ts_low = ts & TS_LOW_MASK
    ts_high = ts & TS_HIGH_MASK

    # バージョンはuuid4と同じ表示にしてる
    ts_part = (TOP_PAD | (ts_high << 4) | VERSION | ts_low) << 64

    # バリアント10
    random_part = random.randrange(1 << 62) | (1 << (64 - 1))
    return (ts_part | random_part).to_bytes(16, byteorder='big')


def mmid_timestamp_parser(mmid):
    # mmidから時間を算出する関数

    mmid_ts = int.from_bytes(mmid, byteorder='big') >> 64
    ts_low = mmid_ts & TS_LOW_MASK
    ts_high = (mmid_ts & (TS_HIGH_MASK << 4)) >> 4
    ts = float(ts_high | ts_low) / 1e7 + LOCAL_EPOCH_TS
    return datetime.fromtimestamp(ts).strftime("%Y-%m-%d %H:%M:%SZ")
CREATE TABLE t_user(
   user_id varchar(255) NOT NULL,
   password binary(16) NOT NULL,
   updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`user_id`),
   INDEX index_updated_dt(`updated_dt`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

CREATE TABLE t_cookie(
   session_id binary(16) NOT NULL,
   user_id varchar(255) NOT NULL,
   data json DEFAULT NULL,
   updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`session_id`),
   FOREIGN KEY (`user_id`) REFERENCES t_user(`user_id`),
   INDEX index_updated_dt(`updated_dt`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

CREATE TABLE t_access(
   session_id binary(16) NOT NULL,
   url varchar(255) NOT NULL,
   created_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`session_id`, `url_id`, `created_dt`),
   FOREIGN KEY (`session_id`) REFERENCES t_cookie(`session_id`),
   INDEX index_created_dt(created_dt)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

最後の処理で "Foreign keys are not yet supported in conjunction with partitioning"のエラーが出た。 Foreign keysを設定しているとパーティショニングできないらしいので、諦めた。 が、本番環境への投稿用を作成すればForeignKeyが入らないので作成。

*本番用SQL:web_access_store_prd.sql

CREATE TABLE t_user(
   user_id varchar(255) NOT NULL,
   password binary(16) NOT NULL,
   updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`user_id`),
   INDEX index_updated_dt(`updated_dt`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

CREATE TABLE t_cookie(
   session_id binary(16) NOT NULL,
   user_id varchar(255) NOT NULL,
   data json DEFAULT NULL,
   updated_dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`session_id`),
   INDEX index_user_updatedt(`user_id`,`updated_dt`)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

CREATE TABLE t_access(
   session_id binary(16) NOT NULL,
   url varchar(255) NOT NULL,
   created_dt DATETIME DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`session_id`, `url_id`, `created_dt`),
   INDEX index_created_dt(created_dt)
) ENGINE=InnoDb DEFAULT CHARSET=utf8;

ALTER TABLE `t_access` PARTITION BY RANGE (to_days(`created_dt`)) (
   PARTITION pNULL VALUES LESS THAN (0),
   PARTITION p20111218 VALUES LESS THAN (TO_DAYS('2017-4-18')),
   PARTITION p20111225 VALUES LESS THAN (TO_DAYS('2017-4-25')),
   PARTITION p20111225 VALUES LESS THAN (TO_DAYS('2017-5-1')),
   PARTITION pNew VALUES LESS THAN MAXVALUE
);

リレーション

t_access

名前 カラム 参照テーブル 参照カラム
t_access_ibfk_1 session_id t_cookie session_id

t_cookie

名前 カラム 参照テーブル 参照カラム
t_cookie_ibfk_1 user_id t_user user_id

github.com

サイトマップについて

WebサイトをGoogleにうまく検索結果として出力させるために、 サイトマップを作る必要がある。
サイトマップにはHTMLサイトマップxmlサイトマップがあって、
前者は人用で後者はロボット(crawl)用なのかな。。知らんけど。
良さげな記事があったので、メモしておく。

bazubu.com

マルチインデックスという考え方

MySQLで大量データに対してクエリを投げる時、WHERE配下のカラム名にマルチインデックスを適応すればサクサクいく。 これ関してはEXPLAIN句で確認すればいい。

mysql> ALTER TABLE landing_pages ADD INDEX index_name(user_id, created)

qiita.com

phpjavascriptroom.com

また、SQLで処理が終わらなくなった時の対応方法。

mysql> show processlist;

mysql> kill 1100;

MySQLで処理に長時間かかっている複数クエリをまとめて殺す方法

処理中のプロセスをバックグラウンドで回す

「Ctr + z」で一時停止をし、jobsで確認し、bg %~で対応する。

$ zcat db.sql.gz | mysql -u root db

^Z
[1]+  停止                  zcat db.sql.gz | mysql -u root db
$ jobs
[1]+  停止                  zcat db.sql.gz | mysql -u root db
$ bg %1
[1]+ zcat db.sql.gz | mysql -u root db &
$ jobs
[1]+  実行中               zcat db.sql.gz | mysql -u root db &

kazmax.zpp.jp