2023-06-28

【データベース】ロック（排他制御）

データベース

複数ユーザーが同時にDB更新しても矛盾が起きないようにする仕組み

ロックの種類

共有ロック（READロック）
- 他ユーザーのSELECTだけ認める
- ユーザーのログイン処理とか
占有ロック（WRITEロック）
- 他ユーザーの一切の処理を認めない
- ECサイト等で在庫数で矛盾が起きないようにSELECTも認めたくないとき

ロックの範囲

テーブルロック
行（=レコード）ロック

SELECT FOR UPDATE

行単位で共有ロックをかけるSQL文（他ユーザーのSELECTのみ許す）
必ずトランザクションを開始すること（開始しないとただのSELECT文になる）
他ユーザーが対象の行をSELECT FOR UPDATEしようとすると、待ち状態になる
- 一定時間が過ぎると Lock wait timeout exceeded; エラーになる
- トランザクションを開始した方を commit すると、SELECTできるようになる

注意点

トランザクションの中で行うこと
ユニークに行指定できる条件を使うこと
処理が終わったら COMMIT or ROLLBACK でトランザクションを終了させてロック解除
begin したらすぐに SELECT FOT UPDATE

どのロックが良いのか？

ケースバイケース
アドバイザリロックが便利
楽観的ロックはほぼ見かけない
SELECT FOR UPDATEは使用注意（デッドロックしないように！）

参考

データベースのロック(排他制御)とは？ロックの種類や仕組みを解説 - YouTube

2023-06-28

パーティションでDBの検索速度を速める

データベース

パーティションとは

DB検索を速くするために、テーブルを内部的に分割する技術のこと。

インデックスでも速くならない場合の検討材料

性能の向上

テーブルの分割により、アプリケーションからのSQLアクセスにおいて、検索性能の向上が見込まれます。性能向上を実現する要因には以下があります。

テーブル分割によるI/O削減絞り込み条件をSQLに指定することで、アクセスする範囲を特定のパーティションのみに絞り込むことができます。また、パーティション内でよく使用される部分がメモリー上にキャッシュされやすくなります。これにより、ディスクI/Oが減り、アクセス性能が向上します。

テーブル空間を分けることによるI/O分散分割したテーブルを物理ディスクの異なるテーブル空間に配置します。これにより、別々の物理ディスクに対して並行で読み書きが行われることでディスクI/Oが分散し、処理性能が向上します。

https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/partitioning-overview/

パーティションの種類

リスト
- キーカラムを固定値で分割する方法
- キー値の取りうる値の種類があらかじめ判明していて、その数が少ない場合に有効
レンジ
- キーカラムの値の範囲で分割する方法
- 日付や連続するIDなどで分割したいときに有効
ハッシュ
- 均等に分割する方法
- 不特定多数、一意のキー値をもとに分散させたい場合に有効

MySQLの注意点

パーティションキーカラムは、プライマリーキー or ユニークキーにするという制約がある
外部キー制約が使えない

参考

learn.microsoft.com

【DB】パーティションとは？データベースのパフォーマンスを改善するパーティションの仕組み - YouTube

xn--w8j8bac3czf5bl7e.com

2023-06-27

データベースの種類

データベース

復習

階層型

ツリー構造：1本の幹から複数の枝に分かれながら作っていく
親データ：子データ = 1 対複数
1つのデータまでのルートが必ず1つなため、ある特定のデータを検索する場合に高速

ネットワーク型

ツリー構造
親データ：子データ = 複数対複数なので柔軟に格納できる

リレーショナル型

表形式
RDBMS（MySQL、PostgreSQL、OracleDatabase、SQLserver）
複雑なデータ向き
データが正確
検索スピードが遅い

キーバリュー型（NoSQL）

単純な構造のデータに向いている
キーとバリューを1つの組みにして格納
NoSQL（Not Only SQL）（Redis、Riak、Hibari）
検索スピードが早い
少ない容量で保管できる

2023-05-31

【SQL】LIKE検索は％を3つ以上含めてパターン検索できる

SQL

リクストされたパスを保存しているログから対象の商品IDを含むレコードを取得したいとする。

例：ログテーブル

id	path
1	`/item_ids=5,10,24&created_at=2023-04-01`
2	`/item_ids=22,23,98&created_at=2023-04-12`
3	`/item_ids=25,36&created_at=2023-04-30`
4	`/item_ids=50,55,66,78&created_at=2022-10-01`
5	`/item_ids=19&created_at=2022-07-05`

困ってたこと

SQLのLIKE検索で、意図しない箇所で検索ヒットしてしまう。

item_idに10が含まれるレコード（id: 1）だけを取得したいのに、下記の書き方だとcreated_atが10を含む箇所（id: 1 と id: 4）もヒットしてしまう。

例

SELECT * FROM Log WHERE path LIKE '%10%';

解決策

SELECT * FROM Log WHERE path LIKE '%item\_id=%10%created_at%';

こうすれば、item_idの中に10を含むもので、その後にcreate_atが来るように順番を指定することができる。

ありがとう、ChatGPT。

2023-04-17

【PHP】cronで1億件のデータを移行

php SQL データベース

1億件のIoT計測データのデータ移行を行った話。

cronでやることになった背景

cronでデータ移行することになった背景はこんな感じ。

カラム名は仮です。

年ごとの計測テーブルBに保存されている、ある期間の数種類の製品の計測データを、四半期ごとの計測テーブルA-Xテーブルに移行したい（1〜3月の計測データならA-1、4〜6月ならA-2、7〜9月ならA-3、10〜12月ならA-4）。
移行時に気を付ける点は下記だった。
1. Bテーブルの製品番号（ product_no）と計測日時（datetime ）の組み合わせと、同じ組み合わせのレコード（つまり同じ計測データ）が既にAテーブルにも保存されている可能性があった。
2. Aテーブル内では容量の関係で同じ計測データはなるべく重複しないようにする（事情により、 product_no と datetime の組み合わせで uniqueキーは貼られていない）。
3. Cテーブルにも、Bテーブルの製品番号（ product_no）と計測日時（datetime ）の組み合わせが同じレコードが存在する場合がある。BテーブルからAテーブルへ移行する際、Cテーブルにも同じ組み合わせのレコードが存在する場合は、そのデータもAテーブルへ一緒に移行する。
上記のように、ちょっと複雑な条件だったので、dumpファイルを作って一気に移行する作戦はできなかった。
結果、PHPでプログラムを書いて少しずつ移行するように確定。
このとき、一気に全データを取得するようにするとメモリが尽きるし、タイムアウトで処理が途中で途切れてしまう懸念があったので、安全に処理が完了するように少しずつデータ移行することにした。流れは下記の通り。
1. Bテーブルから条件に該当するデータをちょうどいい量SELECT
2. aで取得したデータを1件ずつループ
  1. product_no と datetime が同じ組み合わせのレコードが、既にAテーブルに存在するか確認。
  2. あれば continue
  3. なければ、Cテーブルにも同じ組み合わせのレコードがないか確認して、あればSELECT
  4. BテーブルとCテーブルのデータをAテーブルへINSERT。
  5. 移行完了をログに吐き出す
3. 繰り返し
これを人の手で都度確認してると大変なので、cronを書いてやってしまおう！となった。

プログラムの実装

詳細は書かないが、頑張って実装。

レコード数が膨大なので、発行されるSQLが負荷のかからないSQLかどうかを、EXPLAINで確認しながら実装する。

EXPLAINの確認項目

項目	確認事項
type	indexかALLじゃないか
rows	多すぎないか
Extra	Using index condition Using where Using index あたりか

処理の流れはざっとこんな感じ。

cronの設定

phpの場所の確認

$ which php
/usr/bin/php

現在設定されているcronの確認

$ crontab -l
crontab: no crontab for name

cronタブを vi で開いて、1時間おきにPHPプログラムを実行したかったので、下記のように記載して、 :wq で保存。

$ crontab -e

* */1 * * * /usr/bin/php /var/www/cron/migration_test.php

保存したら設定完了と出てくる。

crontab: installing new crontab

cronが実行されているか確認。

$ sudo tail -f cron

Apr 16 23:01:01 xxxx CROND[]: (xxxx) CMD (php /var/www/cron/migration.php)

ちゃんと実行されてるっぽい。

でもデータベースを確認すると、なぜか更新されていない。

ログを吐くようにしてみる。

再度 cronタブを開いて、ログを吐くように追記。

$ crontab -e

* */1 * * * /usr/bin/php /var/www/cron/migration_test.php >> /var/www/cron/migration.log 2>&1

これでもう一度cronを実行してみて、吐き出されたログを確認してみると、 fopen() でファイルを開こうとしている箇所で怒られていた。

failed to open stream: No such file or directory in /var/www/.....

ググってみると、cronで実行するプログラムでは相対パスではなくて絶対パスで指定する必要があるらしい。

絶対パスに修正して、再度cron実行！

これでちゃんと少しずつデータの移行が行われ始めました。

1億件なので時間はかかりますが、少しずつ。

参考

qiita.com

chusotsu-program.com

nippondanji.blogspot.com

2023-04-14

【Laravel】リポジトリパターンでデータ周りの要求変更に負けない設計パターンを取り入れる

Laravel php

こちらの本で勉強中なので、学習記録として。

PHPフレームワーク Laravel Webアプリケーション開発バージョン8.x対応

作者:竹澤有貴,栗生和明,新原雅司,大村創太郎
ソシム

Amazon

リポジトリパターンとは

リポジトリパターンとは、ビジネスロジックからデータの保存や復元を別レイヤ（リポジトリ層）へ移し分離・隠蔽することで、コードのメンテナンス性やテストの容易性を高める実装パターン。

ビジネスロジックからデータストアに対して直接操作する処理を切り離し、何らかのデータ保管庫（リポジトリ）に対して、データの保存や復元を行う処理を抽象的に扱うオブジェクトを用意する。

対象サンプル

出版社テーブル（publishers）へのデータ操作を例とする。

Field	Type	Null	Key	Default	Extra
id	bigint unsigned	NO	PRI	NULL	auto_increment
name	varchar(100)	NO		NULL
address	text	NO		NULL
created_at	timestamp	YES		NULL
updated_at	timestamp	YES		NULL

出版社を新規に追加するWebAPIを作成していく。

エンドポイントは /api/publishers とする。

作成していくのは下記の3つのファイル。

データベースアクセスを受け持つEloquent（Publisher）
ビジネスロジックを受け持つサービスクラス（PublisherService）
リクエストを受けるコントローラクラス（PublisherAction）

実装

Publisherクラス

nameとaddressカラムのみ登録可能にする。

<?php
// app/DataProvider/Eloquent

namespace App\DataProvider\Eloquent;

use Illuminance\Database\Eloquent\Model;

class Publisher extends Model
{
    proteced $fillable = [
        'name',
        'address',
    ];
}

PublisherServiceクラス

existsメソッドで、引数nameで指定された名前と同じ出版社名がないかを確認し、もし同じ出版社名がすでに登録されていたら true を返す。

storeメソッドで、新たに登録して、シーケンス値（id）を返す。

<?php
// app/Services

namespace App\Services;

use App\DataProvider\Eloquent\Publisher;

class PublisherService
{
    public function exists(string $name): bool
    {
        $count = Publisher::whereName($name)->count();
        if ($count > 0) {
            return true;
        }
        return false;
    }

    public function store(string $name, string $address): int
    {
        $publisher = Publisher::create(
            [
                'name' => $name,
                'address' => $address,
            ]
        );
        return (int)$publisher->id;
    }
}

PublisherActionコントローラクラス

ユーザーからリクエストを受けて、nameで指定された名前と同じ出版社名が存在しないか確認する。同一出版社がすでに登録済みならなにも行わず、HTTPステータス200で返す。

登録されていない場合は、新規で登録してHTTPステータス201で返す。

<?php
// app/Http/Controllers

namespace App\Http\Controllers;

use App\Services\PublisherService;
use Illuminate\Http\Request;
use Symfony\Component\HttpFoundation\Response;

class PublisherAction
{
    private $publisher;

    public function __construct(PublisherService $publisher)
    {
        $this->publisher = $publisher;
    }

    public function create(Request $request)
    {
        if ($this->publisher->exists($request->name)) {
            return response('', Response::HTTP_OK);
        }

        $id = $this->publisher->store($request->name, $request->address);
        return response('', Response::HTTP_CREATED)
            ->header('Location', '/api/publishers/' . $id);
    }
}

最後に、エンドポンとを登録するために、 routes/api.php にルート追加。

Route::post('/publishers', [App\Http\Controllers\PublisherAction::class, 'create']);

リファクタリング

PublisherServiceクラスを確認すると、データの存在確認やデータ登録の処理は、EloquentであるPublisherクラスに依存してしまっている。

つまり、MySQLに接続できるEloquentを利用することが前提となっている。

データベースの代わりにモックを利用したり、Eloquent以外のデータ操作クラスを利用しようとすると、このサービスクラスを大幅に修正する必要が出てきてしまう。

そこで、ビジネスロジックから特定のデータベース操作を取り除いていく。

手順は下記のとおり。

Repositoryを抽象化するインターフェースとEntityクラスを作成する
データベース操作を担当するRepositoryクラスを作成する
Serviceクラスはインターフェースを参照する
インターフェースと具象クラスを紐づける

データ操作をServiceクラスから見た場合、Publisherオブジェクトは、同名出版社の存在確認と登録処理ができればいいため、この2つの処理を持つクラスを「リポジトリ」として新たに定義する。

同時に処理を抽象化し、これを表現したクラスをインターフェースとして作成する。

リポジトリインターフェース

インターフェースクラスなため、出版社名をキーにデータ取得を行うfindByNameと登録処理を行うstoreメソッド定義のみを行う。

<?php
// app/DataProvider/PublisherRepositoryInterface.php

namespace App\DataProvider;

use App\Domain\Entity\Publisher;

interface PublisherRepositoryInterface
{
    public function findByName(string $name): ?Publisher;

    public function store(Publisher $publisher): int;
}

PublisherのEntityクラス

<?php
// app/Domain/Entity/Publisher.php

namespace App\Domain\Entity;

class Publisher
{
    protected $id;
    protected $name;
    protected $address;

    public function __construct(?int $id, string $name, string $address)
    {
        $this->id = $id;
        $this->name = $name;
        $this->address = $address;
    }

    public function getName(): string
    {
        return $this->name;
    }

    public function getAddress(): string
    {
        return $this->address;
    }
}

リポジトリインターフェースを実装した具象クラス

上記のインターフェースの実処理を行う具象クラス。

PublisherActionクラスで実行していたデータアクセス処理をこちらに移動。

<?php
// app/Domain/Repository/PublisherRepository.php

namespace App\Domain\Repository;

use App\DataProvider\PublisherRepositoryInterface;
use App\DataProvider\Eloquent\Publisher as EloquentPublisher;
use App\Domain\Entity\Publisher;

class PublisherRepository implements PublisherRepositoryInterface
{
    private $eloquentPublisher;

    public function __construct(EloquentPublisher $eloquentPublisher)
    {
        $this->eloquentPublisher = $eloquentPublisher;
    }

    public function findByName(string $name): ?Publisher
    {
        $record = $this->eloquentPublisher->whereName($name)->first();
        if ($record === null) {
            return null;
        }

        return new Publisher(
            $record->id,
            $record->name,
            $record->address,
        );
    }

    public function store(Publisher $publisher): int
    {
        $eloquent = $this->eloquentPublisher->newInstance();
        $eloquent->name = $publisher->getName();
        $eloquent->address = $publisher->getAddress();
        $eloquent->save();

        return (int)$eloquent->id;
    }
}

これで、データ操作の実処理はリポジトリクラスに移った。

PublisherServiceクラスでは、MySQLのデータアクセスクラスを直接利用していたが、抽象クラスであるPublisherRepositoryInterfaceをコンストラクタインジェクションで引数として渡す形式に置き換え可能なので、リファクタリングしていく。

<?php
// app/Services

namespace App\Services;

use App\DataProvider\PublisherRepositoryInterface;
use App\DataProvider\Eloquent\Publisher;

class PublisherService
{
    private $publisher;

    public function __construct(PublisherRepositoryInterface $publisher)
    {
        $this->publisher = $publisher;
    }

    public function exists(string $name): bool
    {
        if (!$this->publisher->findByName($name)) {
            return false;
        }

        return true;
    }

    public function store(string $name, string $address): int
    {
        return $this->publisher->store(new Publisher(null, $name, $address));
    }
}

こうすれば、

このサービスクラスは同じPublisherRepositoryInterfaceインターフェースを持つクラスであれば何でも動作することになる。
ユニットテストではモッククラスを利用可能。
他のデータストアを利用することになっても、サービスクラスには変更を加えることなく差し替えが可能。
コントローラもこのサービスクラスを利用するので、データストア先の変更にも影響を受けない。

最後に、インターフェースと具象クラスの関連づけ（バインド）を行う。

サービスプロバイダクラスのregisterメソッドに記述する。

例ではデフォルトで用意されている App\Providers\AppServiceProvide クラスに登録するが、新たにサービスプロバイダを作成しても良い。

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function register()
    {
        $this->app->bind(
            \App\DataProvider\PublisherRepositoryInterface::class,
            \App\DataProvider\PublisherRepository::class,
        );
    }
}

もし、データストア先を変更する場合は、PublisherRepositoryInterfaceを持ったデータ操作クラスを新たに作成してバインド定義し直せば、ビジネスロジックを変更することなく、データ操作処理のみを差し替えることができる。

リポジトリパターンは各クラスを疎結合にできる反面、クラス数が増えるため、短期限定でしようするプログラムには不要かもしれない。

が、システムの要件や規模の拡張が見込まれるサービスでは、いいデザインパターン。

2023-04-11

【Laravel】レイヤードアーキテクチャで仕様変更に強くする

Laravel php

この本でLaravel勉強中なので、自分用にアウトプットしておく。

PHPフレームワーク Laravel Webアプリケーション開発バージョン8.x対応

作者:竹澤有貴,栗生和明,新原雅司,大村創太郎
ソシム

Amazon

レイヤードアーキテクチャ

ビジネスロジックを表現するサービスレイヤから非機能要件などを可能な限り取り除き、影響範囲を小さくすることが最大の目的。

レイヤ化のための概念

いくつかの概念をもとに分割して設計して、MVCパターンでのモデルやコントローラのクラスの肥大を防ぐ
上位レイヤから下位レイヤを呼び出すことを徹底する。その逆は禁止。
ビジネスロジックの複雑化を防いで、ビジネスロジックを担当する層から、さまざまな依存を排除して抽象化する

-> 仕様変更への対応、テストの容易さなどに役立つ

下記のコントローラ上に記述されたDB操作を、レイヤードアーキテクチャに沿って分割していく。

<?php

namespace App\Http\Controllers;

use App\User;
use App\Purchase;

class UserController
{
    public function index(string $id)
    {
        $user = User::find(intval($id));
        $purchase = Purchase::findAllBy($user->id);
        // DBから取得した値を使った処理など
        return view('user.index', ['user' => $user]);
    }
}

全体の流れ

モデルとコントローラの分離

DB処理がモデルとして役割を担っている場合、ビジネスロジック・Eloquentモデル・コントローラが強く結合する状態になる。これを解消していく。

まずは、コントローラからDB処理を排除するために、DB処理をサービスクラスに分離。

<?php

namespace App\Service;

use App\User;
use App\Purchase;

class UserPurchaseService
{
    public function retrievePurchase(int $identifier): User
    {
        $user = User::find($identifier);
        $user->purchase = Pucrchase::findAllBy($user->id);
        // DBから取得した値を使った処理など

        return $user;
    }
}

コントローラ側からDB処理を排除。

<?php

namespace App\Http\Controllers;

use App\User;
use App\Purchase;

class UserController
{
    protected $service;

    public function __construct(UserPurchaseService $service)
    {
        $this->service = $service;
    }

    public function index(string $id)
    {
        $result = $this->service->retrievePurchase(intval($id));
        return view('user.index', ['user' => $result]);
    }
}

サービスレイヤとDBの分離

ビジネスロジックを解決するロジックは、まだDBに依存した状態。

DBへの依存を解決するために、DB操作を抽象化して直接的な操作から分離するリポジトリ層を作る。

リポジトリ層でDBを操作して、サービスレイヤからDB操作を切り離していく。

リポジトリのインターフェースを定義。

<?php

namespace App\Repository;

interface UserRepositoryInterface
{
    public function find(int $id): array;
}

リポジトリを実装。

<?php

namespace App\Repository;

use App\User;

class UserRepository implements UserRepositoryInterface
{
    public function find(int $id): array
    {
        $user = User::find($id)->toArray;
    // DBから取得した値を使った処理など
        return $user;
    }
}

サービスクラスからDB操作を排除。

<?php

namespace App\Service;

use App\Repository\UserRepositoryInterface;
use App\User;

class UserPurchaseService
{
    protected $userRepository;

    public function __construct(UserRepositoryInterface $userRepository)
    {
        $this->userRepository = $userRepository;
    }

    public function retrievePurchase(int $identifier): User
    {
        // レポジトリを介したデータの取得
        $user = $this->userRepository->find($identifier);
    // DBから取得した値を使った処理など
        return $user;
    }
}