2014-07-15

【MySQL】インデックスを貼ったら32.2倍で動いた

レコードを数えたり、重複を削除したりと便利なMySQLですが、思ったほど速度が出ない状況があります。
そんなときのKVSか！と思ったのですが、インデックス周りを見直すだけでもかなりチューニングできそうなので検証してみました。

テストデータ

処理

キーワードが与えられると、そのキーワードが各文書にどれだけ含まれているかを返す処理を想定します。

テーブル構造

文書と単語の中間テーブル

doc_word_id	doc_id	word_id
1	1	1
2	1	2
3	1	3
4	2	4
5	2	2

単語テーブル

word_id	surface	pos
1	私	名詞
2	名前	名詞
3	鈴木	名詞
4	趣味	名詞
5	ギター	名詞

中間テーブルのレコード数

count(doc_word_id)
143317

単語テーブルのレコード数

count(word_id)
4792

検証

上記の処理をするプログラムをPythonで書いて、time コマンドで実行時間を測ってみます。

結果はこんな感じになりました。
（realのみ掲載）

番号	index	実行時間
1	なし	5m21.873s
2	doc_id	2m55.429s
3	surface	2m30.906s
4	doc_id, surface	0m57.415s
5	複合（doc_id, word_id）	1m14.861s
6	複合（doc_id, word_id）, surface	0m10.287s

6番めっちゃ速い！
複合キー万歳ですね。

もう1点、

「MySQLでは、1つのクエリを実行するとき、1つのテーブルにつき1つのインデックスしか仕様できないのである。」

実践ハイパフォーマンスMySQL P66

とありますが、内部結合したときは結合後で1つのテーブルとして扱われるのかも気になっていました。
2番、3番と比べて4番が早くなっていることから結合前のテーブル数でカウントされている（結合後で1つのテーブルとして扱われていない）ようですね。

まとめ

複合キー速い！
内部結合するときはそれぞれのテーブルにインデックスを貼ると速い！

参考

O'Reilly Japan - 実践ハイパフォーマンスMySQL 第3版（僕が見たのは第1版）
インデックスの作成(INDEX) - テーブルの作成 - MySQLの使い方

2014-07-07

【mysql-connector-python】PythonからMySQLを操作する

PythonのMySQLドライバ、mysql-connector-pythonを使っていて躓いたので、その備忘録も兼ねて紹介します。

PythonでMySQLを操作しようと思ったら

まずドライバをインストールしなければいけません。
いろんな種類があり、紹介マニアどらふと版: Python の MySQL ドライバはどれを利用すれば良いのかに分かりやすくまとめられています。

僕はmysql-connector-pythonをチョイスしました。
特徴はMySQL公式のドライバだという点ですね。
公式がいいのか！と言われるとケースバイケースだと思いますが、最近使っているMySQL Workbenchという公式のGUI ツールが使いやすかったので。

インストール

pipでいけます。

$ pip install mysql-connector-python

基本的な使い方

次のようなデータをinsertし、それをselectするコードを書いてみます。
（テーブルは作成済みとする）

student_id	name	sex
1	山田太郎	男

test.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# ドライバをimport
import mysql.connector

if __name__ == '__main__':
    # データベースに接続
    connect = mysql.connector.connect(user='hoge', password='hoge', host='hoge', database='hoge', charset='utf8')
    cursor = connect.cursor()

    name = '山田太郎'
    sex = '男'

    # insert
    cursor.execute('insert into student_table (name, sex) values (%s, %s)', (name, sex))

    # select
    cursor.execute('select * from student_table')
    row = cursor.fetchone()

    # 出力
    for i in row:
        print(i)

    # データベースから切断
    cursor.close()
    connect.close()

実行結果

$ python test.py
1
山田太郎
男

今回はレコードを1つinsertし、それをselectするというものだったのでfetchone 関数を使いましたが、複数のレコードを取得したい場合はfetchall 関数を使います。

# select
cursor.execute('select * from student_table')
rows = cursor.fetchall()

# 出力
for i in rows:
    print(i[0])
    print(i[1])
    print(i[2])

詰まったところ

上のコードでも使った

# insert
cursor.execute('insert into student_table (name, sex) values (%s, %s)', (name, sex))

execute 関数の第1引数内の変数を %s とし、第2引数に変数を入れることで、特定の文字列をエスケープできるのですが、第2引数はタプルで表現しなければならないんですよ。
つまり、第二引数に変数を一つだけ与えたい場合、

(name)

や

name

ではエラーになってしまいます。
で、正解はこう。

(name,)

タプル恐ろしや。

参考

2014-07-01

【備忘録】第3正規形

RDBのテーブル設計における第3正規形の導出ですが、いつも導出例を見ながら雰囲気でやってしまうので、一回きちんと勉強しようと思った足跡的な記事。

第3正規形までの持ってき方忘れた
第3正規形ってこれであってる？

となった時のための備忘録。

非正規形

以下の表を例に正規化を進めていきます。

学籍番号	性	名	名前	地区コード	住所	部活動
101	鈴木	太郎	鈴木太郎	1	地方A	野球部、吹奏楽部
102	田中	一郎	田中一郎	2	地方B	サッカー部、軽音楽部
103	吉田	健太	吉田健太	3	地方C	水泳部

第1正規形

定義

リレーションがスカラ値のみを持ちうる

リレーションの正規化 - Wikipedia

用語

スカラ値

それ以上分割できない値

リレーションの正規化 - Wikipedia

つまり

全ての属性（列）の値が、複数の値や他の属性から導出できる値でない状態。

例

非正規形の状態では、部活動属性に複数の値を持つ学生がいるので分割します。

学籍番号	性	名	名前	地区コード	住所	部活動
101	鈴木	太郎	鈴木太郎	1	地方A	野球部
101	鈴木	太郎	鈴木太郎	1	地方A	吹奏楽部
102	田中	一郎	田中一郎	2	地方B	サッカー部
102	田中	一郎	田中一郎	2	地方B	軽音楽部
103	吉田	健太	吉田健太	3	地方C	水泳部

また、名前属性はそれぞれ性属性と名属性から導出できるので除去します。

学籍番号	性	名	地区コード	住所	部活動
101	鈴木	太郎	1	地方A	野球部
101	鈴木	太郎	1	地方A	吹奏楽部
102	田中	一郎	2	地方B	サッカー部
102	田中	一郎	2	地方B	軽音楽部
103	吉田	健太	3	地方C	水泳部