[MySQL] テンポラリーテーブル＆「INSERT ... ON DUPLICATE KEY UPDATE」構文

集計バッチで
MySQLのテンポラリーテーブルと
「INSERT ... ON DUPLICATE KEY UPDATE」構文の合わせ技を使ったので
メモ

まず テンポラリーテーブルを使うにあたって、MySQLのメモリ関係の設定値を確認する

参考) MySQLのメモリ関係のシステム変数 - 祈れ、そして働け ～ Ora et labora http://d.hatena.ne.jp/tetsuyai/20111006/1317873012

メモリ上に作成される テンポラリファイルは max_heap_table_size内まで
超えるとISAMテーブルとしてディスクに書き出されます。

(デフォ)max_heap_table_size グローバル 16MB

それから、今現在のDBのテーブルデータサイズから

テンポラリーテーブルのおよそのデータサイズを出す

参考）データベースとテーブルのサイズを確認する方法 - ふってもハレても
http://d.hatena.ne.jp/sho-yamasaki/20120405/1333640589

DB調べたら

Avg_row_length＝151で、200としても

平均4万レコードくらいの データをテンポラリーテーブルにするから

200x4万レコード=800万byte → 7.63MB ・・・もんだいなすっぽ

で、
テンポラリーテーブルを使った 処理について

まず データの出力イメージは
商店Aテーブル、商店Bテーブル があります
それぞれの テーブルから 日付を絞って、商品番号でGroupByした集計を取得して、
それをマージします

↓こんな感じ

┌────┬───┬───┐
│商品番号│商店A │商店B │
├────┼───┼───┤
│   11111│     3│     2│
├────┼───┼───┤
│   11112│     0│     5│
├────┼───┼───┤
│   11113│     2│     0│
└────┴───┴───┘

まず 商店Aテーブルを集計して テンポラリーテーブルに データインサート

CREATE TEMPORARY TABLE `tmp_summary` (
  `商品番号` varchar(10) NOT NULL,
  `商店A購買数` int(11) NOT NULL DEFAULT 0,
  `商店B購買数` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`商品番号`)
)
  SELECT  商品番号 ,count(商品番号) as 商店A購買数, 0 as 商店B購買数
  FROM    商店Aテーブル
  WHERE   del_flg       =  0 
  AND     購買日   >= 'YYYY/MM/DD 00:00:00'
  AND     購買日   <= 'YYYY/MM/DD 23:59:59'
  GROUP BY 商品番号
  ;

SELECT文内の 「0 as 店舗B 購買数」のところは
次に集計する 店舗Bの購買数が入る部分を あらかじめ
０で埋めて先に作っとく的な感じ

次に 商店Bテーブルを集計して
先ほどのテンポラリーテーブル「tmp_summary」に マージしていきます
この時 「INSERT ... ON DUPLICATE KEY UPDATE」構文を使うと
MySQLが
既にPKが存在している時は UPDATE
なかったら INSERTしてくれます

INSERT INTO tmp_summary 
  SELECT  商品番号 , 0 as 商店A購買数, count(商品番号) as 商店B購買数
  FROM    商店Bテーブル
  WHERE   del_flg       =  0 
  AND     購買日   >= 'YYYY/MM/DD 00:00:00'
  AND     購買日   <= 'YYYY/MM/DD 23:59:59'
  GROUP BY 商品番号

  ON DUPLICATE KEY UPDATE
  商店A購買数=tmp_summary.商店A購買数+values(商店A購買数),
  商店B購買数=tmp_summary.商店B購買数+values(商店B購買数);

これで あとは テンポラリーテーブルをよしなに処理すればOK

select * from tmp_summary;

参考）
複合UNIQUEキーでも「INSERT ... ON DUPLICATE KEY UPDATE」構文は使える - 岩本隆史の日記帳
http://d.hatena.ne.jp/IwamotoTakashi/20080329/p1

テンポラリテーブルの作成MySQL｜プログラムメモ
http://logic.moo.jp/memo.php/archive/11/

他のテーブルのデータを追加(INSERT ... SELECT文) - データの追加と削除 - MySQLの使い方
http://www.dbonline.jp/mysql/insert/index6.html

SELECT結果からテーブルを作る

↓こんな感じのね

CREATE TABLE [table_name]
 SELECT  *
 FROM    TBL_A
 WHERE   del_flg       =  0 
 AND     regist_date   <= '2012/07/23 23:59:59'
 ;

jQueryで同じIDを持つ 複数要素を選択する

<div id="hoge">hoge</div>
<div id="hoge">hoge</div>
<div id="hoge">hoge</div>

↑だったら

$("[id=hoge]").html('fuga');


参考）
$("[id=bar]"). http://www.kinopyo.com/blog/jquery-select-multy-element-with-same-id

コンポーネントでモデルを使うとき [cakePHP2]

↓メソッドの中で 使うモデルを定義しましょう

  public function component_method()
  {

    // Model呼び出し
    $this->controller->loadModel('UserInfo');
    $user = $this->controller->UserInfo->find("first",
              array("conditions"=>array(
　　：
　　：

mysqldump いろいろ

 ・特定のテーブル(複数可)のレコードのみダンプ　テーブル作成情報は出さない

$ mysqldump -u [username] -p[password] -t [database_name] [table_name1] [table_name2]・・・> [file_path]

・データベース全体のテーブル構造のみ ダンプ 　レコード情報を一切書き込まない)

$ mysqldump [username] -p[password] -d [database_name] > [file_path]

→ createtableとinsert文も一緒に出すときは −dオプションを外す

参考）
MySQLのダンプ（エクスポート）、インポート、バックアップ - Tips and Memo 

mysqldumpで複数テーブルもしくは特定のテーブルなど条件指定でレコードを出力する方法

memcache だらだらめも

■memcacheの状態を確認する簡易ツール「memcached-tool」コマンド


↓実行例

# memcached-tool localhost:11211
  #  Item_Size  Max_age   Pages   Count   Full?  Evicted Evict_Time OOM
  9     552B      2674s       1       1      no        0        0    0
 14     1.7K      1394s       1       2      no        0        0    0
 15     2.1K      1386s       1       3      no        0        0    0
 29    48.1K      2695s       1      21     yes        5     1404    0
 30    60.2K      2745s       1       6      no        0        0    0

参考）
memcached の中身を確認するなら memcached-tool コマンド | バシャログ。 http://c-brains.jp/blog/wsg/10/07/30-105035.php
memcached-toolの使い方 - あるプログラマーの後悔日誌 http://d.hatena.ne.jp/taka512/20110830/1314698515


■memcachedに繋げて確認する

・telnetで入る
$ telnet ホスト ポート番号
$ telnet localhost 11211

・statsコマンド　↓出力例

stats
STAT pid 29344
STAT uptime 12450
STAT time 1341381935
STAT version 1.4.5
STAT pointer_size 32
STAT rusage_user 0.017997
STAT rusage_system 0.026995
STAT curr_connections 13
STAT total_connections 54
STAT connection_structures 15
STAT cmd_get 44
STAT cmd_set 38
STAT cmd_flush 0
STAT get_hits 6
STAT get_misses 38
STAT delete_misses 0
STAT delete_hits 0
STAT incr_misses 0
STAT incr_hits 0
STAT decr_misses 0
STAT decr_hits 0
STAT cas_misses 0
STAT cas_hits 0
STAT cas_badval 0
STAT auth_cmds 0
STAT auth_errors 0
STAT bytes_read 1587472
STAT bytes_written 305878
STAT limit_maxbytes 1048576
STAT accepting_conns 1
STAT listen_disabled_num 0
STAT threads 4
STAT conn_yields 0
STAT bytes 1341195
STAT curr_items 33
STAT total_items 38
STAT evictions 5
STAT reclaimed 0
END

limit_maxbytes 最大メモリサイズとか
curr_items　現在登録されているアイテムの数
total_items　今まで登録されたアイテムの数（累計）
evictions　容量がいっぱいになって 追い出されたアイテムの数

▲memcachedは 最大容量に達すると、期限切れや一番古いアイテムの領域を新しい領域に当てる


・キー一覧取得　stats cachedump [slab id] [limit]

 [slab id]＝上記のmemcached-toolコマンドで出る 「#」の列がソレ
↓出力例（#15のアイテムたちを表示する）

stats cachedump 15 100
ITEM key_1[1747 b; 1341457279 s]
ITEM key_2 [1715 b; 1341374471 s]
ITEM key_3 [2042 b; 1341374471 s]
END

参考）
memcachedを知り尽くす：第2回　memcachedのメモリストレージを理解する｜gihyo.jp … 技術評論社 http://gihyo.jp/dev/feature/01/memcached/0002?page=3
[memcached] memcachedコマンド一覧 - Life with IT
http://l-w-i.net/t/memcached/command_001.txt

その他参考）
★インストール
memcachedを知り尽くす：第1回　memcachedの基本｜gihyo.jp … 技術評論社
http://gihyo.jp/dev/feature/01/memcached/0001?page=2
★めむきゃっす すてーたすみる
http://gihyo.jp/dev/feature/01/memcached/0002?page=3

[PHP] Memcache 簡易スクリプト

◎簡単な 確認やら、セットやらできるやつ

→ コマンドラインから、php -f chk_memcached.php get_list みたいに実行

・get_list　表示例

# php -f chk_memcached.php get_list
==============================
command:get_list
==============================
* = old cache
---------------
expire = 0  | size =       10 | cache_key = foo
expire = 0  | size =       20 | cache_key = hoge
---------------
key count:2
     total cache size:        30byte
*old total cache size:         0byte

#define('MEM_HOST', 'localhost');
define('MEM_HOST', '127.0.0.1');
define('MEM_PORT', 11211);

if ($argc < 2) {
  showUsage();
  exit;
}

$output = array();
$keys   = array();
$value  = '';

//Memcache
$memcache_obj = new Memcache;
$memcache_obj->connect(MEM_HOST, MEM_PORT);

array_shift($argv);
$command = array_shift($argv);

switch ($command) {
  case 'get':
    $keys = $argv;
    
    $re = $memcache_obj->get($keys);
    break;

  case 'get_list':
    $re = getKeyList($memcache_obj);
    break;

  case 'set':
    $keys = array_shift($argv);
    $value = array_shift($argv);
    
    $re = $memcache_obj->set($keys, $value);
    break;

  case 'delete':
    $keys = array_shift($argv);
    
    $re = $memcache_obj->delete($keys);
    break;

  default:
    showUsage();
    break;
}

$output[] = '==============================';
$output[] = 'command:' . $command;
if (is_array($keys)) $keys = join(',', $keys);
if ($keys)  $output[] = 'key(s):' . $keys;
if ($value) $output[] = 'value:' . $value;
$output[] = '==============================';
$output[] = '';

echo join("\n", $output);

if (is_array($re)) var_dump($re);
else echo $re;
echo "\n";


function showUsage() {

  $usage = basename(__FILE__) . " -- Usage >>>>\n";
  $usage.= "    :get_list\n";
  $usage.= "    :get [key1] [key2] [key3] ...  [keyN]\n";
  $usage.= "    :set [key] [value]\n";
  $usage.= "    :delete [key]\n";

  echo $usage;

}


/**
 * 統計情報からすべてのキーを抜き出す
 */
function getKeyList($memcache_obj) {
  $result = array();
  $tmp_keys = array();
  $all_keys = array();
  $total = 0;
  $old_total = 0;

  $slabs = $memcache_obj->getExtendedStats('slabs');

  $stats = $memcache_obj->getStats();
  $memcached_started_time = $stats['time'] - $stats['uptime'];

  $stats_item = $memcache_obj->getStats('items');
  $items = $stats_item['items'];
  foreach ($items as $slabclass => $item) {
      $stats_cachedump = $memcache_obj->getStats('cachedump', $slabclass, $item['number']);
      $tmp_keys = array_merge($tmp_keys, $stats_cachedump);
  }

  ksort($tmp_keys);
  foreach ($tmp_keys as $cache_key => $v) {
    $size = (int)$v[0];
    $time = ($v[1] == $memcached_started_time)? 0: $v[1];
    $expire = (!$time)? $time: date('Y/m/d H:i:s', $time);
    $old_flg = ($time && $time < time())? '*': ' ';
    $total+=$size;
    if ($old_flg == '*') $old_total+=$size;
    $all_keys[] = 'expire = ' . $expire . $old_flg . ' | size = ' . sprintf("%8s", $size) . ' | cache_key = ' . $cache_key;
  }



  $result[] = '* = old cache';
  $result[] = '--------------- ';
  $result[] = join("\n", $all_keys);
  $result[] = '--------------- ';
  $result[] = 'key count:' . count($all_keys);
  $result[] = '     total cache size:' . sprintf("%10s", $total) . 'byte';
  $result[] = '*old total cache size:' . sprintf("%10s", $old_total) . 'byte';

  return join("\n", $result);
}