RedisPlugin for Phalcon (The correspondence of MySQL sharding.)

Composer

{
    "require": {
       "ienaga/phalcon-redis-plugin": "3.*"
    }
}

Version

PHP: 7.0.x, 7.1.x, 7.2.x
Phalcon: 3.x

phpredis and YAML

sudo yum install libyaml libyaml-devel php-pecl-yaml php-pecl-redis

app/config/config.php

@see PhalconConfig

$loader = new Phalcon\Config\Adapter\Yaml\Loader();
return $loader
    ->setIgnore(["routing"]) // ignore yml names
    ->setEnvironment("stg") // default dev
    ->setBasePath(realpath(dirname(__FILE__) . "/../.."))
    ->load();

app/config/database.yml

prd:
stg:
dev:
  database:
    dbAdminMaster:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3301
      username: root
      password: XXXXX
      dbname:   admin
      charset:  utf8
      transaction: true
    dbAdminSlave:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3311
      username: root
      password: XXXXX
      dbname:   admin
      charset:  utf8
      transaction: false
    dbCommonMaster:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3301
      username: root
      password: XXXXX
      dbname:   common
      charset:  utf8
      transaction: false
    dbCommonSlave:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3311
      username: root
      password: XXXXX
      dbname:   common
      charset:  utf8
      transaction: false
    dbUser1Master:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3306
      username: root
      password: XXXXX
      dbname:   user
      charset:  utf8
      transaction: true
    dbUser1Slave:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3316
      username: root
      password: XXXXX
      dbname:   user
      charset:  utf8
      transaction: false
    dbUser2Master:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3307
      username: root
      password: XXXXX
      dbname:   user
      charset:  utf8
      transaction: true
    dbUser2Slave:
      adapter:  Mysql
      host:     127.0.0.1
      port:     3317
      username: root
      password: XXXXX
      dbname:   user
      charset:  utf8
      transaction: false

app/config/redis.yml

prd:
stg:
dev:
  redis:
    # log出力
    logger:
      logging: true # logging ON OFF
      output:  /var/www/project/log/sql.log # output log file path
      
    enabled:   true # false => cache off
    autoIndex: true # false => auto index off

    # 対象のカラムがModelに存在したら使用。上から順に優先。存在が確認できた時点でbreak
    prefix:
      columns:  # e.g. user_id, id, social_id, [account, password]
        - user_id
        - social_id
        - [account, password]
        - id

    # 共通のマスタがあれば登録「table_」と共有部分だけの記載はtable_*と同義
    # common
    common:
      enabled: false
      service:
        name: dbCommon

      dbs: # e.g.  master_, access_log
        - mst_

    # Sharding設定
    shard:
      enabled: true # Shardingを使用しない時はfalse

    # Shardingのマスタ設定
    admin:
      service:
        name: dbAdmin
        
    # Shardingのマスタ設定
    admin:
      service:
        name: dbAdmin
      # ユーザマスタ
      # e.g.
      #    CREATE TABLE IF NOT EXISTS `admin_user` (
      #      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      #      `social_id` varchar(255) NOT NULL,
      #      `admin_db_config_id` int(10) unsigned NOT NULL,
      #      PRIMARY KEY (`id`)
      #    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
      model: AdminUser # e.g. AdminUser or namespace \Project\AdminUser
      column: admin_db_config_id # e.g. admin_db_config_id

      # ユーザマスタの登録「table_」と共有部分だけの記載はtable_*と同義
      dbs: # e.g. admin_, user_ranking
        - admin_
        
      # Shardingをコントロールするテーブルとカラム
      #
      # e.g.
      #    CREATE TABLE IF NOT EXISTS `admin_db_config` (
      #      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      #      `name` varchar(50) NOT NULL,
      #      `gravity` tinyint(3) unsigned NOT NULL DEFAULT '0',
      #      PRIMARY KEY (`id`)
      #    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
      #    INSERT INTO `admin_db_config` (`id`, `name`, `gravity`) VALUES
      #    (1, 'dbUser1', 50),
      #    (2, 'dbUser2', 50);
      # shard config master
      control:
        model: AdminDbConfig # e.g. AdminConfigDb or namespace \Project\AdminConfigDb
        column: name # e.g. name

    # schemaをキャッシュ
    metadata:
      host:   127.0.0.1
      port:   6379
      select: 0
      
    # servers
    server:
      dbMaster:
        host: 127.0.0.1
        port: 6379
        select: 0 # redis select [データベースインデックス]
      dbSlave:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbCommonMaster:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbCommonSlave:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbUser1Master:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbUser1Slave:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbUser2Master:
        host: 127.0.0.1
        port: 6379
        select: 0
      dbUser2Slave:
        host: 127.0.0.1
        port: 6379
        select: 0

app/config/services.php

/**
 * Database connection is created based in the parameters defined in the configuration file
 */
$dbService = new \Phalcon\Mvc\Model\Adapter\Redis\Service();
$dbService->registration();

/**
 * If the configuration specify the use of metadata adapter use it or use memory otherwise
 */
$di->setShared('modelsMetadata', function () {
    return new \Phalcon\Mvc\Model\Adapter\Redis\Metadata\Redis(
        $this->getConfig()
            ->get("redis")
            ->get("metadata")
            ->toArray()
    );
});

findFirst

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

# findFirst
$robot = Robot::criteria()
    ->add('id', $id)
    ->add('type', $type, Criteria::NOT_EQUAL)
    ->group('type')
    ->findFirst();

find

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$robot = Robot::criteria()
    ->add('id', array($id), Criteria::IN)
    ->add('type', array($start, $end), Criteria::BETWEEN)
    ->limit(10, 5) // limit, offset
    ->order('type DESC')
    ->find();

cache Control

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$robot = Robot::criteria()
    ->add('id', array($id), Criteria::IN)
    ->add('type', array($start, $end), Criteria::BETWEEN)
    ->limit(10, 30)
    ->order('type DESC')
    ->cache(false)
    ->find();

autoIndex Control

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$robot = Robot::criteria()
    ->add('id', array($id), Criteria::IN)
    ->add('type', array($start, $end), Criteria::BETWEEN)
    ->limit(10, 30)
    ->order('type DESC')
    ->autoIndex(false)
    ->find();

save

class UserItem extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$userItem = new UserItem();
$userItem->setId($id);
$userItem->setType($type);
$userItem->save();

update

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

Robot::criteria()
    ->add("user_status", 1)
    ->add("power", 100)
    ->set("status", 2)
    ->set("name", "robot")
    ->update();

UPDATE `robot` SET `status` = 2, `name` = "robot" WHERE `user_status` = 1 AND `power` = 100;

delete

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

Robot::criteria()
    ->add("user_status", 1)
    ->add("power", 100, Robot::GREATER_EQUAL)
    ->delete();

DELETE FROM `robot` WHERE `user_status` = 1 AND `power` >= 100;

count

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$count = Robot::criteria()
    ->add("user_status", 1)
    ->add("power", 100)
    ->add("status", 2)
    ->count();

sum

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$sum = Robot::criteria()
    ->add("user_status", 1)
    ->sum("price");

autoIndex

e.g. PRIMARY = type, INDEX = id, status ※autoIndexをtrueにする事で、PRIMARYもしくはINDEXに一番マッチするクエリに並び替えて発行。

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$robot = Robot::criteria()
    ->limit(10)
    ->add("type", $type)
    ->addGroup("type")
    ->addOrder("id", "DESC")
    ->add("status", $status)
    ->add("id", $id)
    ->find();

SELECT * FROM `robot` 
WHERE `id` = :id: 
AND `type` = :type:
AND `status` = :status: 
GROUP BY `type`
ORDER BY `id` DESC
LIMIT 10

Index Test Mode

テストモードをtrueにしてSQLを発行。どのIndexにもマッチしない時はErrorを出力。

単体

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

$robot = Robot::criteria()
    ->limit(10)
    ->add("type", $type)
    ->addGroup("type")
    ->addOrder("id", "DESC")
    ->add("status", $status)
    ->add("id", $id)
    ->test(true)
    ->find();

全体

class Robot extends \Phalcon\Mvc\Model\Adapter\Redis\Model {}

\RedisPlugin\Mvc\Model::test(true);

$robot = Robot::criteria()
    ->limit(10)
    ->add("type", $type)
    ->addGroup("type")
    ->addOrder("id", "DESC")
    ->add("status", $status)
    ->add("id", $id)
    ->find();

ienaga/RedisPlugin

RedisPlugin for Phalcon (The correspondence of MySQL sharding.)

Composer

Version

phpredis and YAML

app/config/config.php

@see PhalconConfig

app/config/database.yml

app/config/redis.yml

app/config/services.php

findFirst

find

cache Control

autoIndex Control

save

update

delete

count

sum

autoIndex

Index Test Mode