InnoDBの設計とインデックスを意識したサロゲートキーと複合プライマリキーの比較

作者: Baron Schwartz,Peter Zaitsev,Vadim Tkachenko,Jeremy D. Zawodny,Arjen Lentz,Derek J. Balling,伊藤直也(監訳),田中慎司(監訳),吉川英興(監訳),株式会社クイープ
出版社/メーカー: オライリージャパン
発売日: 2009/12/14
メディア: 大型本
購入: 17人クリック: 373回
この商品を含むブログ (46件) を見る

概要

この記事ではInnoDBを使用する上でのクラスタインデックスを意識したサロゲートキーと複合主キーの比較をパフォーマンスの観点から行います。サロゲートキーか複合キーのどちらを使うべきかは様々な議論がなされているようにケースバイケースといったところで自分は納得しています。ここでは自分の実現したいテーブル設計と利用目的を明確にしてそのケースに合うのはどちらかという検証をしたいと思います。ちなみに今までの業務ではサロゲートを使う事がほとんどでした。理由としては参加したPJのほとんどで元々のテーブルが全部サロゲートであること、また自分で設計する時も主キー管理に面倒なことを考えなくて済むからという理由でサロゲートを使用していました。(使用しているフレームワークの制限でサロゲートを使用しないといけないということはありませんでした。)今までパフォーマンスを求められる箇所のDB設計を担当しておらず勉強不足が否めないので、今回検証してみることにしました。間違い等あればどんどんご指摘ください。宜しくお願いいたします。以下初めにこの記事で利用するキーの名前の定義をしておきます。

キーの定義

名前意味

ナチュラルキー(自然キー) システムの外部から入力される値に対するキー。

サロゲートキー(代替キー) 自動生成される連番キーで主にプライマリーに利用されるが、値としては意味を持たない。

プライマリキー(主キー) テーブル内の行を一意とするための識別子。

複合プライマリキー(複合主キー) 複数のナチュラルキーにて構成されるプライマリキー

ユニークキー(一意キー) NULL以外をユニークとするキー。

複合ユニークキー複数のカラムでユニークを意味するキー。

名前	意味
ナチュラルキー(自然キー)	システムの外部から入力される値に対するキー。
サロゲートキー(代替キー)	自動生成される連番キーで主にプライマリーに利用されるが、値としては意味を持たない。
プライマリキー(主キー)	テーブル内の行を一意とするための識別子。
複合プライマリキー(複合主キー)	複数のナチュラルキーにて構成されるプライマリキー
ユニークキー(一意キー)	NULL以外をユニークとするキー。
複合ユニークキー	複数のカラムでユニークを意味するキー。

実現したい内容

商品販売を行う携帯サービスを展開する時に「どのユーザ」が「どの商品」を「どの携帯電話」で利用しているかという状況の履歴を管理するテーブルを作成します。データとしてはユーザ、商品、携帯電話の値それぞれがデータの意味としては一意となりますが履歴のテーブルであるため3つ揃って一つのユニークな履歴となります。（3つの値を持つ同じ履歴レコードは他には存在しない状況）テーブルとして必要な情報を以下にまとめます。

必要な項目説明

ユーザID ユーザを識別する値。データの重複は無い

商品ID 商品を識別する値。データの重複は無い

携帯端末ID 携帯端末を識別する値。データの重複は無い

ダウンロード日時ユーザが商品を携帯端末にダウンロードした日時

このデータの管理を今回はInnoDBで行います。InnoDBを選択している理由は重要な顧客情報であるためトランザクションを使ったcommit/rollbakを行いたい事と一部外部キー制約を利用する事によります。データの挿入は一回限りですがユーザがどの商品をどれだけの端末で利用しているかを調べるためにSELECTを多く使用します。記事で検証したいのはInnoDBのクラスタインデックスです。クラスタインデックスについては以前記事を書いたのでそちらを参照してください。実践ハイパフォーマンスMySQL(第2版)を斜め読みして前半の重要なポイントだけをまとめてみた - Yuta.Kikuchiの日記調査のポイントとしてはINSERTの段階でどれだけインデックスが近接し効率よくSELECTされるかをサロゲートと複合プライマリでの比較をします。

必要な項目	説明
ユーザID	ユーザを識別する値。データの重複は無い
商品ID	商品を識別する値。データの重複は無い
携帯端末ID	携帯端末を識別する値。データの重複は無い
ダウンロード日時	ユーザが商品を携帯端末にダウンロードした日時

テーブル/インデックスの設計

今回の検証の中心はInnoDBのクラスタインデックスで上のケースを実現したい時にはサロゲートと複合プライマリのどちらが適しているかという事です。そこでそれぞれのテーブルとインデックスの設計を行います。

サロゲート方式

DownloadテーブルにDownload_idという意味を持たない一意のキーをプライマリとして指定しています。SELECTを行うのはDownloadテーブルだけですがとりあえず関係しそうな外部テーブルの参照状態も表します。

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`User` (
  `user_id` INT NOT NULL ,
  `User_name` VARCHAR(20) NULL ,
  `User_age` INT NULL ,
  PRIMARY KEY (`user_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`Production`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Production` (
  `production_id` INT NOT NULL ,
  `Production_name` VARCHAR(20) NULL ,
  `Production_price` INT NULL ,
  PRIMARY KEY (`production_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`Download`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Download` (
  `Download_id` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `production_id` INT NOT NULL ,
  `device_id` VARCHAR(45) NOT NULL ,
  `download_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  PRIMARY KEY (`Download_id`) ,
  INDEX `device` (`user_id` ASC, `production_id` ASC, `device_id` ASC) ,
  CONSTRAINT `fk_Download_User`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`User` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Download_Production1`
    FOREIGN KEY (`production_id` )
    REFERENCES `mydb`.`Production` (`production_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

複合プライマリ方式

Downloadテーブルのuser_id、production_id、device_idを複合プライマリキーとして利用しています。

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`User`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`User` (
  `user_id` INT NOT NULL ,
  `User_name` VARCHAR(20) NULL ,
  `User_age` INT NULL ,
  PRIMARY KEY (`user_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`Production`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Production` (
  `production_id` INT NOT NULL ,
  `Production_name` VARCHAR(20) NULL ,
  `Production_price` INT NULL ,
  PRIMARY KEY (`production_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `mydb`.`Download`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`Download` (
  `user_id` INT NOT NULL ,
  `production_id` INT NOT NULL ,
  `device_id` VARCHAR(45) NOT NULL ,
  `download_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  PRIMARY KEY (`user_id`, `production_id`, `device_id`) ,
  INDEX `device` (`user_id` ASC, `production_id` ASC, `device_id` ASC) ,
  CONSTRAINT `fk_Download_User`
    FOREIGN KEY (`user_id` )
    REFERENCES `mydb`.`User` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Download_Production1`
    FOREIGN KEY (`production_id` )
    REFERENCES `mydb`.`Production` (`production_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

SELECTパフォーマンス検証

実行環境

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.39-log |
+------------+
1 row in set (0.00 sec)

iMac.local 10.8.0 Darwin Kernel Version 10.8.0 xnu-1504.15.3~1/RELEASE_I386 i386

query_cacheの設定

ONにしています。また一度SELECTを実行するとqueryをキャッシュするので次の実行は高速に返却します。Qcache_queries_in_cacheに格納されているものを返す。

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16765312 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
| Qcache_total_blocks     | 7        |
+-------------------------+----------+
8 rows in set (0.00 sec)

使用するクエリ

使用するクエリは以下のものです。取得するカラムに処理的には無駄なものが入っていますが分かりやすくデータを表示するために利用します。

// Pattern1 : ユーザがどの商品をどの端末で利用しているか
SELECT user_id,production_id,device_id FROM Download WHERE user_id = XXXXX;
// Pattern2 : ユーザが指定商品をどの端末で利用しているか
SELECT user_id,production_id,device_id FROM Download WHERE user_id = XXXXX AND production_id = YYYYY;
// Pattern3 : 指定商品を指定端末で利用しているユーザがいるか
SELECT user_id,production_id,device_id FROM Download WHERE production_id = YYYYY AND device_id = ZZZZZ;
// Pattern4 : ユーザが指定端末で利用している商品はどれか
SELECT user_id,production_id,device_id FROM Download WHERE user_id = XXXXX AND device_id = ZZZZZ;
// Pattern5 : ユーザが指定商品を指定端末で利用しているかどうか
SELECT user_id,production_id,device_id FROM Download WHERE user_id = XXXXX AND production_id = YYYYY AND device_id = ZZZZZ;
// Pattern6 : Pattern2の数をCOUNTしてみる
SELECT COUNT(device_id) FROM Download WHERE user_id = XXXXX AND production_id = YYYYY;

テストデータの導入

以下のテストデータを10万件入稿します。本来ならば本番稼働と同じ形式のデータを入れたいのですが時間がかかるため乱数でINSERTします。データとして発生させるパターンを指定ユーザで網羅すると以下のようになります。

指定ユーザが異なる商品を同じ端末で利用
指定ユーザが同じ商品を異なる端末で利用

<?php
$con = @mysql_connect( 'localhost:3306:/tmp/mysql.sock', 'root', '' );
if( !$con ) { 
    die( "disconnect " .  mysql_error() );
}
for( $i=0; $i<100000; $i++ ) { 
    $user_id = rand( 1, 100000 );
    $production_id = rand( 1, 2000 );
    $device_id = rand( 1, 2000000 );
    if( $i == 30000 ) {
        $user_id = 20000;
        $production_id = 5;
        $device_id = 123456789;
    }
    if( $i == 60000 ) {
        $user_id = 20000;
        $production_id = 10;
        $device_id = 123456789;
    }
    if( $i == 90000 ) {
        $user_id = 20000;
        $production_id = 10;
        $device_id = 987654321;
    }
    mysql_query( 'INSERT INTO User( user_id ) VALUES( ' . $user_id . ')');
    mysql_query( 'INSERT INTO Production( production_id ) VALUES( ' . $production_id . ')'); 
    mysql_query( 'INSERT INTO Download( user_id,production_id,device_id,download_date) VALUES( ' . $user_id . ', ' . $production_id . ',' . $device_id . ', NOW() )');
}

サロゲート方式

explain結果で基本的には指定した複合indexが使われて検索が出来ていることが分かりますがuser_idを指定しないとdeviceという名前のindexが使われないので注意が必要です。user_idを指定しない場合のindexが必要であれば別途作成が必要になります。

Pattern1 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000;
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | Download | ref  | device        | device | 4       | const |    3 | Using index |
+----+-------------+----------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Pattern2 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10;
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                  | key    | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Download | ref  | device,fk_Download_Production1 | device | 8       | const,const |    2 | Using index |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Pattern3 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE production_id = 10 AND device_id = 123456789;
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys           | key                     | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | Download | ref  | fk_Download_Production1 | fk_Download_Production1 | 4       | const |   44 | Using where |
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Pattern4 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND device_id = 123456789;
+----+-------------+----------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys | key    | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+---------------+--------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | Download | ref  | device        | device | 4       | const |    3 | Using where; Using index |
+----+-------------+----------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Pattern5 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10 AND device_id = 123456789;
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+--------------------------+
| id | select_type | table    | type | possible_keys                  | key    | key_len | ref         | rows | Extra                    |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | Download | ref  | device,fk_Download_Production1 | device | 8       | const,const |    1 | Using where; Using index |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+--------------------------+
1 row in set (0.10 sec)

Pattern6 :
mysql> EXPLAIN SELECT COUNT( device_id ) FROM Download WHERE user_id = 20000 AND production_id = 10;
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                  | key    | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Download | ref  | device,fk_Download_Production1 | device | 8       | const,const |    2 | Using index |
+----+-------------+----------+------+--------------------------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

SELECTをそれぞれ実行してtimeを算出します。若干面倒ですがquery_cacheを毎回削除してからSELECTの実行具合を確認します。どれも高速に処理が進みます。

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
+---------+---------------+-----------+
3 rows in set (0.01 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
+---------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16766848 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 2        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql>  SELECT user_id,production_id,device_id FROM Download WHERE production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.01 sec)

mysql> RESET QUERY CACHE;Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 3        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 4        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.00 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 5        |
| Qcache_inserts          | 24       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 7        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT COUNT( device_id ) FROM Download WHERE user_id = 20000 AND production_id = 10;
+--------------------+
| COUNT( device_id ) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.01 sec)

試しにINDEXを削除して挙動を確認します。少々面倒ですが外部KEY制約を解除しないとINDEXが削除できないのでそこからやります。INDEX無しのWHERE句参照でもそこそこ早く処理が進んでいるようです。

mysql> ALTER TABLE Download DROP FOREIGN KEY fk_Download_Production1;
Query OK, 100000 rows affected (3.09 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Download DROP FOREIGN KEY fk_Download_User;
Query OK, 100000 rows affected (3.30 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> DROP INDEX device ON download;
Query OK, 100000 rows affected (1.20 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> DROP INDEX fk_Download_Production1 ON download;
Query OK, 100000 rows affected (0.83 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> RESET QUERY CACHE;Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 10       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
+---------+---------------+-----------+
3 rows in set (0.04 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 11       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
+---------+---------------+-----------+
2 rows in set (0.05 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 12       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.04 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 13       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
2 rows in set (0.05 sec)

mysql> RESET QUERY CACHE;Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 14       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.04 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 5        |
| Qcache_inserts          | 23       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT COUNT( device_id ) FROM Download WHERE user_id = 20000 AND production_id = 10;
+--------------------+
| COUNT( device_id ) |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.04 sec)

複合プライマリ方式

explain結果 PRIMARYで検索が出来ていることが分かります。上のschema定義ではindexを張ってしまったがPRIMARY検索が出来ているようであれば不要なように思います。

Pattern1 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000;
+----+-------------+----------+------+----------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys  | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+----------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | Download | ref  | PRIMARY,device | PRIMARY | 4       | const |    4 | Using index |
+----+-------------+----------+------+----------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Pattern2 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10;
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                          | key    | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Download | ref  | PRIMARY,device,fk_Download_Production1 | device | 8       | const,const |    2 | Using index |
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Pattern3 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE production_id = 10 AND device_id = 123456789;
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys           | key                     | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | Download | ref  | fk_Download_Production1 | fk_Download_Production1 | 4       | const |   59 | Using where; Using index |
+----+-------------+----------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Pattern4 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND device_id = 123456789;
+----+-------------+----------+------+----------------+---------+---------+-------+------+--------------------------+
| id | select_type | table    | type | possible_keys  | key     | key_len | ref   | rows | Extra                    |
+----+-------------+----------+------+----------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | Download | ref  | PRIMARY,device | PRIMARY | 4       | const |    4 | Using where; Using index |
+----+-------------+----------+------+----------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Pattern5 : 
mysql> EXPLAIN SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10 AND device_id = 123456789;
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+--------------------------+
| id | select_type | table    | type | possible_keys                          | key    | key_len | ref         | rows | Extra                    |
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | Download | ref  | PRIMARY,device,fk_Download_Production1 | device | 8       | const,const |    2 | Using where; Using index |
+----+-------------+----------+------+----------------------------------------+--------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT( device_id ) FROM Download WHERE user_id = 20000 AND production_id = 10;
+----+-------------+----------+------+----------------------------------------+---------+---------+-------------+------+-------------+
| id | select_type | table    | type | possible_keys                          | key     | key_len | ref         | rows | Extra       |
+----+-------------+----------+------+----------------------------------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | Download | ref  | PRIMARY,device,fk_Download_Production1 | PRIMARY | 8       | const,const |    3 | Using index |
+----+-------------+----------+------+----------------------------------------+---------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

同様にSELECTを実行します。

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 5        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
|   20000 |           494 | 1714311   |
+---------+---------------+-----------+
4 rows in set (0.01 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 6        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
|   20000 |            10 | 987654321 |
+---------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 7        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.00 sec)

mysql> RESET QUERY CACHE;Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 8        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |             5 | 123456789 |
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
2 rows in set (0.00 sec)

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 9        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT user_id,production_id,device_id FROM Download WHERE user_id = 20000 AND production_id = 10 AND device_id = 123456789;
+---------+---------------+-----------+
| user_id | production_id | device_id |
+---------+---------------+-----------+
|   20000 |            10 | 123456789 |
+---------+---------------+-----------+
1 row in set (0.00 sec)

mysql> RESET QUERY CACHE;Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16768384 |
| Qcache_hits             | 5        |
| Qcache_inserts          | 22       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql> SELECT COUNT( device_id ) FROM Download WHERE user_id = 20000 AND production_id = 10;
+--------------------+
| COUNT( device_id ) |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

検証内容

上のテーブルスキーマである場合サロゲートでも複合プライマリでもINDEXを適切に張れば高速に動作する事が分かります。この例だけで考えるとサロゲートを直接条件指定することが無いので複合プライマリーの設定の方が良いように思います。理由は無駄なデータとINDEXを持たなくて済む事と検索にPRIMARYを利用できるという点です。更にレコードをuser_id、production_id、device_idの3つの掛け合わせで一意としたい場合、誤ってアプリケーションが同じデータをINSERTしても自動的にエラーにしてくれます。サロゲートだと今のところDB設定で複数レコード挿入をOKとしているので、アプリケーション側でちゃんと制御してあげる必要が出てしまいます。これを回避するのであれば複合ユニークキーを利用する手段もありますが、それを使うのであれば今回のキー選択は複合主キーを利用すべきということも言えると思います。

INDEXと話が変わりますが複合主キーの場合はUPSERT構文が利用できます。これは何かというとINSERT構文にUPDATE構文も掛け合わせる事が可能で、コードとしても無駄なSELECTを挟まずに済むために処理がすっきりします。サロゲート方式でもこの構文を記述する事ができますが今のテーブルschemaだと同一レコードとしての更新ができないのでレコードが増えてしまいますしデータの定義が異なってしまいます。
INSERT INTO Download( user_id,production_id,device_id,download_date ) VALUES( 20000, 10, 123456789, NOW() ) ON DUPLICATE KEY UPDATE download_date = NOW();

リンク

主キー

整合性制約

Y's note

Web技術・プロダクトマネジメント・そして経営について

InnoDBの設計とインデックスを意識したサロゲートキーと複合プライマリキーの比較

概要

キーの定義

実現したい内容

テーブル/インデックスの設計

サロゲート方式

複合プライマリ方式

SELECTパフォーマンス検証

実行環境

query_cacheの設定

使用するクエリ

テストデータの導入

サロゲート方式

複合プライマリ方式

検証内容

リンク