2016年10月 – igreks開発日記

JIS（≒ISO-2022-JP）で定義されていない文字コードは、UTF8から変換しても、もちろん正常に表示されません。

しかし日本語のUTF8には、実は、濁点と半濁点をふくむ文字に2パターンの表示方法があります。

たとえば「が」というひらがなですが、普通は、

xE3x81x8C

となります。

しかし、UTF8の決まりでは、

xE3x81x8B + xE3x82x99　（「か」＋「 ○゛」）

でもOKなのです。

うちでは日本語文字コードの変換にUnicode::Japaneseを使ってます。非常に多くの変換パターンが実現できるので便利なのですが、上記の後者の場合（「か」＋「 ○゛」の場合）に、UTF8→JISなどの変換に失敗します。

失敗というか、厳密にはxE3x82x99（ ○゛）とxE3x82x9A（ ○゜）が未定義状態のようで、「が」は「が」のように変換されてしまいます。

エンコーダを変えればいいのかもしれませんが、やはりバグが怖いので、下記のようにJISへの変換前にコードを統一することで対応してます。

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana
$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g;	# か+○゛=> が
$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g;	# き+○゛=> ぎ
$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g;	# く+○゛=> ぐ
$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g;	# け+○゛=> げ
$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g;	# こ+○゛=> ご
$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g;	# さ+○゛=> ざ
$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g;	# し+○゛=> じ
$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g;	# す+○゛=> ず
$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g;	# せ+○゛=> ぜ
$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g;	# そ+○゛=> ぞ
$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g;	# た+○゛=> だ
$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g;	# ち+○゛=> ぢ
$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g;	# つ+○゛=> づ
$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g;	# て+○゛=> で
$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g;	# と+○゛=> ど
$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g;	# は+○゛=> ば
$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g;	# は+○゜=> ぱ
$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g;	# ひ+○゛=> び
$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g;	# ひ+○゜=> ぴ
$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g;	# ふ+○゛=> ぶ
$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g;	# ふ+○゜=> ぷ
$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g;	# へ+○゛=> べ
$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g;	# へ+○゜=> ぺ
$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g;	# ほ+○゛=> ぼ
$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g;	# ほ+○゜=> ぽ

# Katakana
$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g;	# カ+○゛=> ガ
$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g;	# キ+○゛=> ギ
$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g;	# ク+○゛=> グ
$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g;	# ケ+○゛=> ゲ
$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g;	# コ+○゛=> ゴ
$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g;	# サ+○゛=> ザ
$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g;	# シ+○゛=> ジ
$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g;	# ス+○゛=> ズ
$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g;	# セ+○゛=> ゼ
$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g;	# ソ+○゛=> ゾ
$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g;	# タ+○゛=> ダ
$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g;	# チ+○゛=> ヂ
$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g;	# ツ+○゛=> ヅ
$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g;	# テ+○゛=> デ
$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g;	# ト+○゛=> ド
$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g;	# ハ+○゛=> バ
$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g;	# ハ+○゜=> パ
$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g;	# ヒ+○゛=> ビ
$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g;	# ヒ+○゜=> ピ
$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g;	# フ+○゛=> ブ
$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g;	# フ+○゜=> プ
$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g;	# ヘ+○゛=> ベ
$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g;	# ヘ+○゜=> ペ
$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g;	# ホ+○゛=> ボ
$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g;	# ホ+○゜=> ポ

$str = "がぎぐげござじずぜぞだぢづでど・・・";

# Hiragana

$str =~ s/\xE3\x81\x8B\xE3\x82\x99/\xE3\x81\x8C/g; # か+○゛=> が

$str =~ s/\xE3\x81\x8D\xE3\x82\x99/\xE3\x81\x8E/g; # き+○゛=> ぎ

$str =~ s/\xE3\x81\x8F\xE3\x82\x99/\xE3\x81\x90/g; # く+○゛=> ぐ

$str =~ s/\xE3\x81\x91\xE3\x82\x99/\xE3\x81\x92/g; # け+○゛=> げ

$str =~ s/\xE3\x81\x93\xE3\x82\x99/\xE3\x81\x94/g; # こ+○゛=> ご

$str =~ s/\xE3\x81\x95\xE3\x82\x99/\xE3\x81\x96/g; # さ+○゛=> ざ

$str =~ s/\xE3\x81\x97\xE3\x82\x99/\xE3\x81\x98/g; # し+○゛=> じ

$str =~ s/\xE3\x81\x99\xE3\x82\x99/\xE3\x81\x9A/g; # す+○゛=> ず

$str =~ s/\xE3\x81\x9B\xE3\x82\x99/\xE3\x81\x9C/g; # せ+○゛=> ぜ

$str =~ s/\xE3\x81\x9D\xE3\x82\x99/\xE3\x81\x9E/g; # そ+○゛=> ぞ

$str =~ s/\xE3\x81\x9F\xE3\x82\x99/\xE3\x81\xA0/g; # た+○゛=> だ

$str =~ s/\xE3\x81\xA1\xE3\x82\x99/\xE3\x81\xA2/g; # ち+○゛=> ぢ

$str =~ s/\xE3\x81\xA4\xE3\x82\x99/\xE3\x81\xA5/g; # つ+○゛=> づ

$str =~ s/\xE3\x81\xA6\xE3\x82\x99/\xE3\x81\xA7/g; # て+○゛=> で

$str =~ s/\xE3\x81\xA8\xE3\x82\x99/\xE3\x81\xA9/g; # と+○゛=> ど

$str =~ s/\xE3\x81\xAF\xE3\x82\x99/\xE3\x81\xB0/g; # は+○゛=> ば

$str =~ s/\xE3\x81\xAF\xE3\x82\x9A/\xE3\x81\xB1/g; # は+○゜=> ぱ

$str =~ s/\xE3\x81\xB2\xE3\x82\x99/\xE3\x81\xB3/g; # ひ+○゛=> び

$str =~ s/\xE3\x81\xB2\xE3\x82\x9A/\xE3\x81\xB4/g; # ひ+○゜=> ぴ

$str =~ s/\xE3\x81\xB5\xE3\x82\x99/\xE3\x81\xB6/g; # ふ+○゛=> ぶ

$str =~ s/\xE3\x81\xB5\xE3\x82\x9A/\xE3\x81\xB7/g; # ふ+○゜=> ぷ

$str =~ s/\xE3\x81\xB8\xE3\x82\x99/\xE3\x81\xB9/g; # へ+○゛=> べ

$str =~ s/\xE3\x81\xB8\xE3\x82\x9A/\xE3\x81\xBA/g; # へ+○゜=> ぺ

$str =~ s/\xE3\x81\xBB\xE3\x82\x99/\xE3\x81\xBC/g; # ほ+○゛=> ぼ

$str =~ s/\xE3\x81\xBB\xE3\x82\x9A/\xE3\x81\xBD/g; # ほ+○゜=> ぽ

# Katakana

$str =~ s/\xE3\x82\xAB\xE3\x82\x99/\xE3\x82\xAC/g; # カ+○゛=> ガ

$str =~ s/\xE3\x82\xAD\xE3\x82\x99/\xE3\x82\xAE/g; # キ+○゛=> ギ

$str =~ s/\xE3\x82\xAF\xE3\x82\x99/\xE3\x82\xB0/g; # ク+○゛=> グ

$str =~ s/\xE3\x82\xB1\xE3\x82\x99/\xE3\x82\xB2/g; # ケ+○゛=> ゲ

$str =~ s/\xE3\x82\xB3\xE3\x82\x99/\xE3\x82\xB4/g; # コ+○゛=> ゴ

$str =~ s/\xE3\x82\xB5\xE3\x82\x99/\xE3\x82\xB6/g; # サ+○゛=> ザ

$str =~ s/\xE3\x82\xB7\xE3\x82\x99/\xE3\x82\xB8/g; # シ+○゛=> ジ

$str =~ s/\xE3\x82\xB9\xE3\x82\x99/\xE3\x82\xBA/g; # ス+○゛=> ズ

$str =~ s/\xE3\x82\xBB\xE3\x82\x99/\xE3\x82\xBC/g; # セ+○゛=> ゼ

$str =~ s/\xE3\x82\xBD\xE3\x82\x99/\xE3\x82\xBE/g; # ソ+○゛=> ゾ

$str =~ s/\xE3\x82\xBF\xE3\x82\x99/\xE3\x83\x80/g; # タ+○゛=> ダ

$str =~ s/\xE3\x83\x81\xE3\x82\x99/\xE3\x83\x82/g; # チ+○゛=> ヂ

$str =~ s/\xE3\x83\x84\xE3\x82\x99/\xE3\x83\x85/g; # ツ+○゛=> ヅ

$str =~ s/\xE3\x83\x86\xE3\x82\x99/\xE3\x83\x87/g; # テ+○゛=> デ

$str =~ s/\xE3\x83\x88\xE3\x82\x99/\xE3\x83\x89/g; # ト+○゛=> ド

$str =~ s/\xE3\x83\x8F\xE3\x82\x99/\xE3\x83\x90/g; # ハ+○゛=> バ

$str =~ s/\xE3\x83\x8F\xE3\x82\x9A/\xE3\x83\x91/g; # ハ+○゜=> パ

$str =~ s/\xE3\x83\x92\xE3\x82\x99/\xE3\x83\x93/g; # ヒ+○゛=> ビ

$str =~ s/\xE3\x83\x92\xE3\x82\x9A/\xE3\x83\x94/g; # ヒ+○゜=> ピ

$str =~ s/\xE3\x83\x95\xE3\x82\x99/\xE3\x83\x96/g; # フ+○゛=> ブ

$str =~ s/\xE3\x83\x95\xE3\x82\x9A/\xE3\x83\x97/g; # フ+○゜=> プ

$str =~ s/\xE3\x83\x98\xE3\x82\x99/\xE3\x83\x99/g; # ヘ+○゛=> ベ

$str =~ s/\xE3\x83\x98\xE3\x82\x9A/\xE3\x83\x9A/g; # ヘ+○゜=> ペ

$str =~ s/\xE3\x83\x9B\xE3\x82\x99/\xE3\x83\x9C/g; # ホ+○゛=> ボ

$str =~ s/\xE3\x83\x9B\xE3\x82\x9A/\xE3\x83\x9D/g; # ホ+○゜=> ポ

ご自由にコピペどうぞ。

参考サイト）

http://d.hatena.ne.jp/kamosawa/20151015

http://www.seiai.ed.jp/sys/text/java/utf8table.html

http://webmastertool.jp/other/utf.html

http://www.asahi-net.or.jp/~ax2s-kmtn/ref/unicode/u3040.html

サブクエリを使った検索において、検索件数が数百~数千程度であれば、マシンの力で何とかなることがあるが、この検索結果が数万単位の膨大な数となる場合、動作が極端に遅くなる場合がある。

例として、historyテーブルにメールの履歴、clickテーブルにメールの履歴番号をhistory_idとしたクリックURL、およびそのクリック回数が入っていて、レコードは複数あるとする。

historyテーブルにクリック数のデータはないので、clickテーブルを結合して、履歴データと、その履歴ごとのクリック数およびクリック率（CTR）の一覧を表示したい。またCTRの大きさで一覧をソートしたい。

例えば下記のSQL。

SELECT	SQL_CALC_FOUND_ROWS
	`history`.*,
	`click`.total_clicks,
	ROUND(
		(`click`.total_clicks / `history`.users) * 100,
		 2
	) AS ctr 
FROM	`history` 
LEFT JOIN (
	SELECT
		`click`.history_id,
		SUM(click_count) AS total_clicks 
	FROM	`click`
	GROUP	BY `click`.history_id
)
ON	`history`.id = `click`.history_id
ORDER BY ctr DESC
LIMIT   0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

`click`.total_clicks,

ROUND(

(`click`.total_clicks / `history`.users) * 100,

) AS ctr

FROM `history`

LEFT JOIN (

SELECT

`click`.history_id,

SUM(click_count) AS total_clicks

FROM `click`

GROUP BY `click`.history_id

)

ON `history`.id = `click`.history_id

ORDER BY ctr DESC

LIMIT 0, 30

この場合、clickテーブルのレコードが膨大になってしまうと、10~15行目のサブクエリ内のSELECTが全数検索になるため、結合後のテーブルが巨大となり、極端に動作が重くなる。history_idにINDEXを貼っていても、数万単位の量になってくると、同様だ。

いくら最後（18行目）にLIMIT句でレコード数を制限しても、内側のサブクエリから処理されるので、結合時点ではテーブルは巨大になってしまう。

「サブクエリ内でLIMIT使えばいいのでは？」と思うが、1つの履歴番号に紐付くclickテーブルのレコードが必ず存在するとは限らないし、存在したとしてもレコード数は一定ではないためこの時点でLIMITは確定できない。

つまり、「GROUP BY history.id」（15行目）したレコード数が、30になるとは限らない。仮にサブクエリ内で同じく「LIMIT 0,30」とやったとしたら、結合時にデータに不整合が生じる可能性がある。

またCTRは、historyテーブルに存在する読者数がわからないと出せないので、結合後にORDER BYするしかなく、サブクエリ内ではCTRによるORDER BYができない。この理由もありLIMITは使えない。

そこで発想の転換をして、clickテーブルにhistoryテーブルを結合してみる。

SELECT	SQL_CALC_FOUND_ROWS
	   `history`.*,
	   SUM(`click`.click_count) AS total_clicks,
	   ROUND(
              SUM(`click`.click_count) / `history`.users * 100,
              2
           ) AS ctr
FROM       `click`
RIGHT JOIN `history`
ON	   `click`.history_id = `history`.id
-- ここでwhereも使える --
GROUP BY   `history`.id
ORDER BY   SUM(`click`.click_count) / `history`.users * 100
LIMIT      0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

SUM(`click`.click_count) AS total_clicks,

ROUND(

SUM(`click`.click_count) / `history`.users * 100,

) AS ctr

FROM `click`

RIGHT JOIN `history`

ON `click`.history_id = `history`.id

-- ここでwhereも使える --

GROUP BY `history`.id

ORDER BY SUM(`click`.click_count) / `history`.users * 100

LIMIT 0, 30

ポイントは、

clickテーブルを基本として、クリック数の合計とCTRの計算をする。
historyテーブルをRIGHT JOINして、historyテーブルのレコード数に、最終的な結果レコード数をあわせるようにする。
ORDER BY の対象を「click.history_id」ではなく「history.id」とし、履歴番号に紐付くclickテーブルのレコード数がない時も、対応できるようにする。

こうすることで、サブクエリを使わずに、LIMITをうまく使って膨大なクリックデータを高速に処理できるようになった。

当然ページ送りも、このLIMITの値を変更するだけで対応できる。

経験上、どうしてもサブクエリを使わなければならないってことはほとんどないんだよなあ、、、（経験不足なのか？）。

大体の場合、上記のように結合の方法を工夫するか、テンポラリテーブルを使って処理できる場合が多いと思う。（個人の感想です）

参考サイト）

http://nullnote.com/programs/mysql/join/

SQLでサブクエリを上手に使う6パターン

月別: 2016年10月

濁点（゛）と半濁点（゜）を含む日本語のメール文字化け対応

非効率なサブクエリを改善する