技術向上

プログラミングの学び、気になるテクノロジーやビジネストレンドを発信

ページング関連のクエリとUI設計【MySQL】

MySQL パフォーマンス データベース UI

Limit Offsetは遅い

MySQLを用いてページネーションを作成する際、はじめに遭遇するのが「Limit Offset」構文かもしれません。 MySQLに用意されている、ページングを実現するには便利な機能です。

SELECT * FROM tableA LIMIT 10 OFFSET 250

OFFSETに指定された数字の次の行からLIMITに指定された数だけ抽出します。

しかし大量件数となると、この方法では問題があります。 ソートされていない場合はフルスキャンになります。ソートされている場合でも、OFFSETに指定された行まで捜査して廃棄するため、その件数が多くなるほどコストがかかります。 パフォーマンス以外にも、抽出データがその時々によって異なる可能性があります。再度クエリを実行するまでの間に、データが追加されたり削除された場合です。 したがって、次のようなクエリが推奨されます。

SELECT * FROM tableA WHERE id < 10 ORDER BY id DESC LIMIT 10

（ORDER BY id ASCの場合はid > 10）

この場合、WHERE句で指定された目的の行まで直行するため速くなります。また、連番のidで指定する場合、最後尾以外ならば抽出データが異なることはありません。 ただし、WHERE句に指定されるカラムにはPRIMARY_KEYが設定されているかindexが貼られていることが条件になります。また「抜け」のない連番でなくてはなりません。


ページネーションをどうするか

次のようなページネーションは、最大ページ数を算出する必要があるため、別テーブルでレコード数を保存していない場合で大量件数の場合には、大きなコストがかかるでしょう。
https://promonista.com/wp-content/uploads/2017/06/pagination3.png https://promonista.com/wp-content/uploads/2017/06/pagination3.png

最大ページ数を表示せず、現在のページを中心に前後のページ番号の表示数を抑えることで、クエリのパフォーマンス悪化を抑えることができるでしょう。
前後のページ番号を表示するためには、そのページが存在すること、つまりデータ件数が現在のページで表示されるデータから前後に、少なくとも1件以上あることが必要です。先ほどのWHERE句を使ったデータ抽出を応用することで、ページが存在する確証を得ることができます。

1ページ10件とした場合に、LIMITで11件を指定し、データが11件取得できたら、次のページが存在するとわかります。

SELECT * FROM tableA WHERE id < 10 ORDER BY id DESC LIMIT 11


前にもページがあるかを確かめる場合には、次のようにUNIONを使って、前にもあるかを確かめるクエリを合成します。

(SELECT * FROM tableA WHERE id <= 10 ORDER BY id DESC LIMIT 1) UNION (SELECT * FROM tableA WHERE id > 10 ORDER BY id DESC LIMIT 11)


１つ目のクエリが、あるページの最初のレコードの直前にレコードがあるかを確認する（つまり前にページが存在するか）クエリです。２つ目のクエリが、あるページのレコード件数 + 1件までを抽出対象とすることで、対象を全件取得できた場合には次のページが存在することを確認できるクエリになります。
+-1の部分を、1ページあたりの件数に合わせて拡大していくことで、存在を確認できるページの数を増やすことができます。

このようにしてページネーションのクエリを工夫することができますが、そもそもGoogleやECサイトのようなページネーションが必要かどうかを設計段階でよく検討してみるのも良いでしょう。


OFFSETは前の行を読み飛ばすのにはよくない方法

YappoLogs: なぜ SQL_CALC_FOUND_ROWS や LIMIT OFFSET のページングが良く無いのか

MySQLのlimitで大量データのパフォーマンスが遅い重い！


セレクトボックスの初期表示【Vue.js】

Vue.js

セレクトボックスの初期表示をする場合、hidden属性を持つoptionタグを最初に記載します。

pugとTypeScriptを使用しています。

<template lang="pug">
  select(v-model="selectedPtn")
    option(value="" hidden) Choose    // 初期状態で表示されるが、選択肢には表示されない
    option(v-for="ptn in ptns" :value="ptn.ID") {{ptn.text}}
</template>

<script lang="ts">
  selectedPtn: string = "";
  ptns: IPattern[] = [
    {
      ID: 1,
      text: "S"
    },
    {
      ID: 2,
      text: "M"
    },
    {
      ID: 1,
      text: "L"
    }
  ];
</script>


エラー解消法_Property '…' has no initializer and is not definitely assigned in the constructor【TypeScript】

TypeScript

TypeScriptで、Property '…' has no initializer and is not definitely assigned in the constructorのエラー、つまり初期化されていない、というエラーが発生する場合、tsconfig.jsonに下記を記述をするとエラーが解消されます。

"strictPropertyInitialization": false


プロパティの初期化チェックの厳密でなくする、という設定を意味します。



Docker-Compose【Docker】

Docker MySQL

Docker-Composeは、複数のコンテナを管理する仕組みです。

いくつかのファイルにコンテナにまつわる設定を記述することで、コンテナのコマンド実行時にその内容を基にして処理されます。この仕組みがないと、各コンテナの設定をコマンド実行の都度指定する必要があります。コンテナの数が増えたり、何度もコマンドを実行するほどその作業は面倒に感じられるでしょう。

アプリケーション用のコンテナとSQLデータベース用のコンテナに対する環境構築は、よくあるケースかと思います。

設定ファイル

docker-compose.yml
複数コンテナのcomposeに関する設定を記述します。

version: "3"

volumes:
  vendor:

services:
  api:
    build: ./api
    volumes:
      - ./api/src:/<ソースコードのディレクトリ>
    ports:
      - "3001:3001"
    environment:
      - ENV=develop
      - CLOUDSQL_CONNECTION_NAME=...
      - CLOUDSQL_USER=...
      - CLOUDSQL_PASSWORD=...
      - CLOUDSQL_DATABASE=..
  db:
    build: ./mysql
    volumes:
      - ./mysql/initdb.d:/docker-entrypoint-initdb.d
      - ./mysql/conf:/etc/mysql/conf.d
      - ./mysql/data:/var/lib/mysql
    ports:
      - "3306:3306"
    environment:
      TZ: "Asia/Tokyo"
      MYSQL_ROOT_PASSWORD: ...
      # MYSQL_ALLOW_EMPTY_PASSWORD: "true"
      # MYSQL_USER: ...
      # MYSQL_PASSWORD: ...
      MYSQL_DATABASE: ...

services以下にapi（アプリケーションとして）とdbのコンテナを用意し、下記の設定を記述しています。

build：Dockerfileの場所を記述
volumes：「ホスト:コンテナ」でマウント（コンテナとホストを同期）する内容を記述
ports：「ホスト:コンテナ」でコンテナの公開ポートを記述
environment：コンテナにおける環境変数を記述

volumesに記述されたディレクトリは、ローカルとコンテナで同じ１つのファイル（ローカルのファイル）を参照していますので、 コンテナを起動した状態で、コンテナ内でファイルの編集（go mod のgo mod tidyなどのコマンドを実行するなど）を行うと、ローカルのファイルが編集されます。

ただし、docker-compose.yamlでread only属性を指定することで、コンテナ内での編集ができないよう指定することもできます。


他にも、imageやdepends_onなどの設定項目が存在します。

volumesに記述した内容はそれぞれ次の意味を持っています。

  db:
    build: ./mysql
    volumes:
      - ./mysql/initdb.d:/docker-entrypoint-initdb.d
→ compose upした時に実行されるsqlファイル群のマウントを指定
      - ./mysql/conf:/etc/mysql/conf.d
→ MySQLの文字コードやログの出力先など、各種設定を記述したファイルのマウントを指定
      - ./mysql/data:/var/lib/mysql
→ MySQLが保存するデータのマウント（データの永続化）を指定

他にも「- ./log/mysql:/var/log/mysql」でログをマウントするなどがあります。

フォルダ階層

compose-up実行後のフォルダ階層の一例を示します。（dataフォルダ、confフォルダ以外のフォルダ、ファイルは用意する必要があります。）

project
    api
        Dockerfile
        src
    mysql
        conf
        data
            （MySQLのデータをマウント）
        Dockerfile
        initdb.d
            01-create_db.sql
            02-create_table.sql
            03-insert_master.sql
        my.cnf


initdb.dではサンプルとしてdatabaseのcreate文を記述したsqlファイル、tableのcreate文を記述したsqlファイル、初期設定として必要なinsert文を記述したsqlファイルを格納することとしました。「01-」とというようにファイル名を指定することで、ファイルを実行する順番を指定することができます。

コマンド

コンテナ起動関連のコマンド

$ docker-compose build
// ビルド

$ docker-compose up
// 起動

$ docker-compose down
// コンテナ削除

起動後 「Ctrl + C」で停止することはできますが、downにより削除することで、前のコンテナが立ち上がってしまうことを回避することができ、確実性があります。


コンテナ内に入るためのコマンド

$ docker ps
// コンテナの一覧を取得

$ docker exec -it <コンテナID> /bin/bash
// コンテナ内に入る （bashを使う）


MySQL関連

$ mysql -u <ユーザー名> -p
→ ユーザーを指定してmysqlを実行

$show databases
→ データベースの一覧を表示

$ use <データベース名>
→ 利用するデータベースを指定


docker-compose.yml内の環境変数に指定したデータベースを選択し、SQLで中身を確認すると、initdb.d内のsqlファイルに指定した内容が反映されているかと思います。

SQLのタイムゾーンを設定する【Go】

Go MySQL GCP

SQLの時刻がUTC時刻になってしまう問題を解決します。 

sql, err := sql.Open("mysql", "user:passward@/dbname?parseTime=true&loc=Asia%2FTokyo")


このようにparseTime=trueに併せてlocを指定しないと、例えソースコード側でJST時刻を指定しても、sql接続時にUTC時刻に書き換えられてしまいます。

SQL上でCURRENT_TIMESTAMP()などを指定している場合で、UTC時刻になってしまう時は別の問題です。
下記SQLでtime_zoneを確認ください。

show variables like '%time_zone%';

time_zoneを+09:00に更新することで、JST時刻と合わせることができます。

GCPのGoogle Cloud SQLを利用している場合は、下記のマニュアルを確認ください。

Configuring Database Flags  |  Cloud SQL for MySQL  |  Google Cloud


脱構造化【TypeScript】

TypeScript

構造化された内容を変数に展開します。

let person = {
    nickname: "Mike",
    like: "Cat"
};

let {nickname, like} = person;
console.log(nickname, like)    // Mike, Cat


配列を展開することもできます。

let array = [1, 2];
let [numA, numB] = array;
console.log(numA, numB);    // 1, 2


関数の引数に使うことで、実行時にオブジェクト形式で引数を指定することができるため、引数の内容が実行時にわかりやすくなります。

function hi({name, from}: {name: string, from: string}): void {
    console.log(`HI, I'm ${name}, from ${like}.`);
}

hi({name: "Mike", from: "Japan"});    // Hi, I'm Mike from Japan.    引数の内容がわかりやすく、間違いがあればすぐに気づく。


TypeScript早わかりチートシート【1.5.3対応】 - Build Insider


最後にInsertまたはUpdateしたレコードのIDを取得する【MySQL】

MySQL データベース

InsertまたはUpdateした後に、他の関連テーブルを操作するため、処理したレコードのIDを使いたい場合があるかと思います。そんな時は、LAST_INSERT_IDを利用します。
Upsert時に、UpdateしたレコードのIDを取得したい場合は、LAST_INSERT_IDを併せて更新する必要があります。

INSERT INTO categories_shops(
    category_id,
    shop_id,
    status,
    created_at,
    updated_at
)
VALUES(
    1,
    1,
    1,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
)
ON DUPLICATE KEY UPDATE
    status = 9,
    updated_at = CURRENT_TIMESTAMP,
    id = LAST_INSERT_ID(    // Updateの時にもIDを利用できるようにするには、更新が必要
        id
    )
;SELECT LAST_INSERT_ID();    // 最後に処理したレコードのIDを取り出す