Kotlin × MyBatisでPARTITIONを使ったSelectをしようとしたらハマったのでメモがわりに記事にしました。

最初に結論

Kotlin × MyBatisで@Selectアノテーションなどの中でヒアドキュメントを使用してシングルクォートの付かない${変数}を挿入する場合は${'$'}{変数}のような形にする。

@Mapper
interface UsersMapper {

    @Select(
        """
            <script>
                SELECT
                    id,
                    name
                FROM users
                PARTITION (${'$'}{targetPartition})
            </script>
        """
    )
    fun getUserList(targetPartition: String): List<User>
}

起こったこと＆過程

よくあるPARTITIONを指定してデータを引っ張ってくるSelect文を用意しました。

@Mapper
interface UsersMapper {

    @Select(
        """
            <script>
                SELECT
                    id,
                    name
                FROM users
                PARTITION (#{targetPartition})
            </script>
        """
    )
    fun getUserList(targetPartition: String): List<User>
    // targetPartition=p202001
}

しかしこれをこのまま実行すると「おいおい、ちゃんとマニュアル読めよ？」と怒られてしまいます。

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''p202001') 

実際に実行されたSQL文を確認してみると、どうやら変数targetPartitionを#{}で挿入していたため、シングルクォートがついてしまったようです。

SELECT id, name FROM users PARTITION (`p202001`);

いやぁうっかりうっかり。
MyBatisの公式ドキュメントを読むとシングルクォートを付けずに値を挿入するには${}を使用するようです。

さてこれで一件解決かと思いきや、今度は構文エラーに…

@Select(
    """
    // ~~~
        PARTITION (${targetPartition}) //構文エラー
        PARTITION (\${targetPartition}) //構文エラー
    // ~~~
    """
)
fun getUserList(targetPartition: String): List<User>

どうやらヒアドキュメント（トリプルクォート）の場合は$はそのまま使えず、エスケープも効かないらしい。

調べてみると${'$'}もしくは${doller}というように文字列として$を挿入してやればいいようです。
そんなのありか…

@Mapper
interface UsersMapper {

    @Select(
        """
            <script>
                SELECT
                    id,
                    name
                FROM users
                PARTITION (${'$'}{targetPartition})
            </script>
        """
    )
    fun getUserList(targetPartition: String): List<User>
}

これで無事SQLを実行することができました。

SELECT id, name FROM users PARTITION (p202001);

参考資料

MyBatis: Mapper XML ファイル
Kotlinでヒアドキュメント中の文字をエスケープ

Laravel DB.com

「 テストデータ登録 」 までCODEを書かずに作成可能なツールにアップデートされました。

私の開発環境

• Laravel5.5/PHP7.2.1/MySQL5.6.38 （Mac: Chromeで確認）

---

LaravelDB.com 操作解説ページはこちらです。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

---

アップデート内容

faker自動生成(テストデータ生成処理)

注意点：テーブル作成後 → テストデータ投入 の順でコマンド打ってください。

ファイルの中はこんな感じです。

自動で文字が入ってるので、精度はまあこんなもんですかね。※文字列、数値、日付は自動で判定して入れてくれてるようです。

---

コマンド打って表示して見ましょう！！！！

php artisan migrate  //既にmigrationファイルも設置済みであること

php artisan db:seed  //既にseederファイルも設置済みであること（上記写真を確認）

そして、次の画面はこんな感じ。

---

LaravelDB.com はじめての人はこちらからどうぞ ！！！ 慣れると最強ですね。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

以上

Laravel DB.com

「 テストデータ登録 」 までCODEを書かずに作成可能なツールにアップデートされました。

私の開発環境

• Laravel5.5/PHP7.2.1/MySQL5.6.38 （Mac: Chromeで確認）

---

LaravelDB.com 操作解説ページはこちらです。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

---

アップデート内容

faker自動生成(テストデータ生成処理)

注意点：テーブル作成後 → テストデータ投入 の順でコマンド打ってください。

ファイルの中はこんな感じです。

自動で文字が入ってるので、精度はまあこんなもんですかね。※文字列、数値、日付は自動で判定して入れてくれてるようです。

---

コマンド打って表示して見ましょう！！！！

php artisan migrate  //既にmigrationファイルも設置済みであること

php artisan db:seed  //既にseederファイルも設置済みであること（上記写真を確認）

そして、次の画面はこんな感じ。

---

LaravelDB.com はじめての人はこちらからどうぞ ！！！ 慣れると最強ですね。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

以上

2020/01/28最新バージョン：Laravel DB.com

「 テストデータ(faker)登録 」 までCODEを書かずに作成可能なツールにアップデートされました。

私の開発環境

• Laravel5.5/PHP7.2.1/MySQL5.6.38 （Mac: Chromeで確認）

---

LaravelDB.com 操作解説ページはこちらです。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

---

アップデート内容

faker自動生成(テストデータ生成処理)

【移行方法】
Macの場合： 「Optionキー + ドラッグ＆ドロップ」 → 「結合」でフォルダの上書きではなく、結合になります。
Winの場合： 「ドラッグ＆ドロップ」で結合できます。

---

ファイルの中はこんな感じです。

自動で文字が入ってるので、精度はまあこんなもんですかね。※文字列、数値、日付は自動で判定して入れてくれてるようです。
※ Fakerで使えるタイプ一覧、 自身で変更して使いたい場合はこちらを参考にすることをオススメします。

注意点：テーブル作成後 → テストデータ投入 の順でコマンド打ってください。

---

コマンド打って表示して見ましょう！！！！

php artisan migrate  //既にmigrationファイルも設置済みであること

php artisan db:seed  //既にseederファイルも設置済みであること（上記写真を確認）

<< 注意点 >>

「address」や「url」を使う場合はカラムサイズに気をつけましょう！

---

ブラウザで確認

そして、次の画面はこんな感じ。

---

LaravelDB.com はじめての人はこちらからどうぞ ！！！ 慣れると最強ですね。

【 Laravel 】CRUD『 表示・登録・更新・削除 』機能をCODEを書かず自動作成
https://qiita.com/daisu_yamazaki/items/3755467b39ef158b81a3

以上

docker-compose.yaml

docker-compose.yaml

version: "3"
services:
    mysql:
        container_name: [MySQLのコンテナ名]
        image: mysql:5.7
        environment:
            MYSQL_DATABASE: [データベース名]
            MYSQL_USER: [ユーザー名]
            MYSQL_PASSWORD: [パスワード]
            MYSQL_ROOT_PASSWORD: [パスワード]
        ports:
            - "4306:3306"
        volumes:
            - mysql-data:/var/lib/mysql

ポート → "4306:3306"

おそらくほとんどの人が、ローカルでMySQLを起動していると思います。特別に設定していなければ、ローカルの3306番のポートがMySQLに使われていると思います。

なので、今回は、ローカルの4306番をMySQLコンテナの3306番と繋ぐように設定しています。

ターミナルからMySQLコンテナに入って確認する場合

MySQLコンテナに入ります。

ターミナル

$ docker exec -it [MySQLのコンテナ名] bash

コンテナに入れたら、MySQLに接続します。

ターミナル

$ mysql -u root -p

パスワードが要求されるので、入力して完了。

ターミナルから直接MySQLコンテナに接続したい場合

ターミナル

$ mysql -u root -p -h 127.0.0.1 -P 4306

注意点は２つあり、
・ホストはlocalhostを使用せず、IPアドレスを直接入力する
・ポート番号を指定する際は、大文字のPを使用する

SequelProで接続

カラムの追加の後にrails db:migrate　

をした際にタイトルのエラーがMysqlにて出て苦戦したので、同じような方がいるかもなのでメモとして残します。

まず最初に結論からです。エラーの原因は、migrateした際

rails db:migrate:status
up 20200123145035 Create users
up 20200123150804 Create
up 20200124043414 Create
down 20200125060439 Create
down 20200125064510 Create

＊downと書かれているファイルならupされてないので問題ないと作り直す為に、削除して作り直した事が原因で
起こりました。downのままですが、追加しようとしたカラムが一部だけ追加されていた事による重複です。

初心者さんで同じような事になる方もいると思いますので、その際しっかりとロールバックまたは、データベースを削除したかのご確認をしてみてください。

直し方

そして直した方法ですが・・・
rails db:drop
をしてデータベースを再作成する事でup済みのものに追加してしまいマイグレーションファイルが削除されなくなってしまったものをなかった事にしました。

rails db:create

raild db:migrate

これで、 rails g AddxxxRefToテーブル名　カラム名: で追加し直し。
正常に戻りました。今回は、作りたてだったので問題なかったですが、デプロイ済みの場合などはユーザーデータが消えたりして大変な事になりますので、お気をつけください。

dockerでmysqlコンテナを立てる時の設定 my.cnf

mysqlのconf について学ぶ。

ざっと出すとこんな感じ

[mysqld]
skip-host-cache
skip-name-resolve
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

mysql server, clientの設定がそれぞれ書いてある。

server 設定 mysqld

skip-host-cache

[https://www.na3.jp/entry/20140205/p1:embed:cite]

[https://dev.mysql.com/doc/refman/5.6/ja/host-cache.html:title]

これはDNSのキャッシュ戦略機能をオフにする宣言。

ipアドレスから引かれたホスト名がすでにキャッシュされている時にそれを使い回す機能があるそう。

docker-composeでコンテナを立てる場合、docker networkがよしなにprivate ipを当ててくれるからわざわざdnsで名前解決しなくていい。

skip-name-resolve

[http://gihyo.jp/dev/serial/01/mysql-road-construction-news/0028:embed:cite]

[https://dev.mysql.com/doc/refman/5.6/ja/host-cache.html:title]

これも同様にDNSの名前引きを飛ばす設定。

character-set-server=utf8mb4

mb4 がポイント。

mysqlのutf8は1-3byte文字しか扱えない。

iOS, Androidの絵文字, 最近追加された難しい日本語や中国語は4byteあるためこれらの文字列を格納しようとするとエラーが起きる。

そこで utf8mb4を使う。

[https://flhouse.co.jp/article/2/utf8mb4:embed:cite]

clientも同じ

MySQL の バイナリログ

/etc/my.cnf に log-bin = log-name を設定するとバイナリログを出力できますが、
デフォルトの設定だと MySQL を再起動したタイミングやバイナリログの最大容量(デフォルトでは最大の1GB)に達した時点でローテートされログが増え続けてしまいます。

現在の設定

/etc/my.cnf

[mysqld]
log-bin = log-name

log の状況

# ls /var/lib/mysql/
-rw-rw----  1 mysql mysql 10491971 12月 19 12:37 log-name.000001
-rw-rw----  1 mysql mysql      143 12月 19 13:04 log-name.000002
-rw-rw----  1 mysql mysql  1718471  1月 21 09:07 log-name.000003
-rw-rw----  1 mysql mysql       60 12月 19 13:05 log-name.index

show master logs の状況

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| ujm-server.000001 |  10491971 |
| ujm-server.000002 |       143 |
| ujm-server.000003 |   1718471 |
+-------------------+-----------+

手動でのバイナリログを削除

SQL を実行

mysql> purge master logs to 'ujm-server.000002';
Query OK, 0 rows affected (0.01 sec)

結果を表示

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| ujm-server.000002 |       143 |
| ujm-server.000003 |   1718471 |
+-------------------+-----------+
2 rows in set (0.00 sec)

自動でバイナリログを削除

expire_logs_days を設定します。

現在の設定

mysql> show variables like 'expire_logs_days';

SQL を実行

mysql> SET GLOBAL expire_logs_days = 10;

今回は10日にしました。

変更後の設定

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 10    |
+------------------+-------+
1 row in set (0.00 sec)

はじめに

Railsアプリをローカルで起動させた際に、MySQLのConnectionErrorが発生しました。先日まで問題なく起動していたのですが……。

mysqlサーバーに接続する際、mysql.sockファイルを使って接続するらしいのですが、
なんらかの原因により、このファイルが消えることがあるそうです。
エラー文で指定されているところに、mysql.sockファイルを作成して再度接続してみました。

$ sudo touch /tmp/mysql.sock
$ sudo mysql.server restart
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/MBP-2.local.pid).

エラー画面の(2)が(38)に変わっただけです。
検索するとこの類のエラーは結構頻発するらしく、まずは権限周りから触ってみました。

$ sudo chown mysql:mysql /tmp

$ sudo chmod 777 /tmp/mysql.sock

$ mysql.server start
Starting MySQL
... ERROR! The server quit without updating PID file (/usr/local/var/mysql/MBP-2.local.pid).

$ sudo touch MBP-2.local.pid

$ sudo rm /tmp/mysql.sock

$ chown -R _mysql:_mysql mysql

$ sudo chown UserName /tmp/mysql.sock
$ mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/var/mysql/マシン名.local.pid).

以上の内容を色々試してみたのですが解決せず。Macを再起動しても何も変化なしでした。
MySQLをhomebrewでインストールしていたので/usr/local/var/mysqlをディレクトリごと消去して再インストールしました。

$ sudo rm -rf /usr/local/var/mysql

$ brew uninstall mysql@5.7
Uninstalling /usr/local/Cellar/mysql@5.7/5.7.28... (319 files, 232.2MB)

$ brew install mysql@5.7
・
・
mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.
If you need to have mysql@5.7 first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc
・
・
?  /usr/local/Cellar/mysql@5.7/5.7.29: 319 files, 232.3MB

再インストールだけだとパスが通っていないのでexportコマンドでパスを通すように設定します。

$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc
$ sudo vi ~/.zshrc
export PATH=/usr/local/bin:$PATH
export PATH="$HOME/.rbenv/bin:$PATH"
eval "$(rbenv init -)"
export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"
$ source ~/.zshrc

まずはMySQLがインストールされているか確認します。

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.29, for osx10.15 (x86_64) using  EditLine wrapper

続いてMySQL5.7を起動させます。

$ brew services start mysql@5.7
==> Successfully started `mysql@5.7` (label: homebrew.mxcl.mysql@5.7)

起動させるRailsアプリのデータベース設定ファイルは以下のようになっています。

database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: test-user
  password: password-example
  socket: /tmp/mysql.sock

指定しているユーザーでMySQLに接続してみます。

$ mysql -u test-user -ppassword-example
ERROR 1045 (28000): Access denied for user 'test-user'@'localhost' (using password: YES)

接続エラーとなりました。MySQLを再インストールしたためにtest-userが消去されているようです。
試しに現在のユーザーリストを確認すると、

$ mysql -u root -p
・
・
・
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

確かに、ユーザーリストにtest-userが存在していませんので再度ユーザーを作成します。

$ mysql -u root -p
・
・
・
mysql> CREATE USER 'test-user'@'localhost' IDENTIFIED BY 'password-example';
Query OK, 0 rows affected (0.12 sec)


mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test-user        | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

ユーザーの作成ができたので権限付与も合わせて行います。今回はテストなので全てのデータベースにアクセスできる権限を付与しました。

mysql> GRANT ALL ON *.* TO 'test-user'@'localhost';
Query OK, 0 rows affected (0.05 sec)

再度、MySQLに接続してみます。

$ mysql -u test-user -ppassword-example
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  
・
・
・
mysql>

接続できましたので、続いてデータベースを作成します。

$ bundle exec rails db:create
Created database '[アプリケーション名]_development'
Created database '[アプリケーション名]_test'

未実行のマイグレーションファイルを全て実行し、データベースにテーブルを作成します。

$ bundle exec rails db:migrare
== 20200117122858 DeviseCreateUsers: migrating ================================
-- create_table(:users)
   -> 0.0093s
-- add_index(:users, :email, {:unique=>true})
   -> 0.0302s
・
・
・

以上で設定終了です。再度Railsアプリを立ち上げてみます。

無事に立ち上がりました。急にエラーが出て結構焦りましたが、いい勉強になりました。

参考記事

https://qiita.com/tosite0345/items/3f346780e14feca48f55
https://weblabo.oscasierra.net/mysql-57-homebrew-install/
https://qiita.com/gatapon/items/92b942fa7081cfe17482
https://qiita.com/kskumgk63/items/b308c0a688c358b17162
https://qiita.com/pugiemonn/items/718b7360028286c5b626

クエリチューニングに欠かせないEXPLAINについて簡単に説明します

目次

1.EXPLAINとは

2.各項目の意味するところ

3.注目すべきところ

EXPLAINとは

インデックスがどう働いているか確認出来ます クエリチューニングを行うときまず最初に行うのがEXPLAINです

EXPLAINの使い方

クエリの頭にEXPLAINをつけて流すと「実行計画」と呼ばれる結果が出力されます
最後に¥Gをつけると縦に出力されて読みやすくなります

実行結果はデータ量によって変わるので、本番と近い環境で行いましょう

各項目の意味するところ

id・・テーブル読み込みの順番

select_type・・クエリの種類

table・・該当テーブル

type・・テーブルにどのようにアクセスしているか

possible_keys・・使用可能なインデックス

key・・オプティマイザによって選択されたインデックス

key_len・・インデックスが有効になっている対象の長さ（バイト）

ref・・keyで比較されているカラム（定数が指定されているときはconstと表示される）

rows・・フェッチされる行数の見積もり（正確なものではない）

Extra・・インデックスの効き具合やソートの仕方etc

改善すべきところ

type

ALLやINDEXの場合はチューニング必須

ALLはインデックスが効いていない状態でテーブルを全スキャンしているので大変遅い

INDEXはインデックス全体をスキャンしている状態でこちらも遅い

インデックスの最適化が必要です

Extra

Using temporary, Using filesortでrowsが多い場合はチューニング必須

order byを使ったクエリで、全てのテーブルが結合されたのち並び替えが行われている状態で遅い

結合前にインデックスによってソートをかけることで解消できます

さわりだけ書かせていただきました
補足は別途行います

複合インデックスを作成する際に注意しなければいけないSQLの評価順序との関係について説明します

目次

1.インデックス作成

2.インデックス削除

3.複合インデックス作成

4.評価順序

インデックス作成

任意のインデックス名をつけて下記のクエリで作成できます

ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名);

インデックス削除

ちなみに削除はこのように記述します

ALTER TABLE テーブル名 DROP INDEX インデックス名;

複数のカラムを指定することもでき、これを複合インデックスと言います

複合インデックス作成

ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム名1, カラム名2);

この指定するカラムの組み合わせによって、処理速度が大きく変わります

SQLの評価順序

そして作成するとき、カラム名の順番が非常に大事です

SQLの評価順序にのっとって記述しないとインデックスが有効になりません

評価順序 = SQLが実行される順番です

上から順に実行されていきます

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT

例えばこのようにインデックスを作成したとき 　

ALTER TABLE users ADD INDEX XXX(col1, col2);

下記のクエリだと評価順序を守っているのでインデックスは有効です

SELECT * FROM users WHERE col1 = Z ORDER BY col2;

ではこちらはどうでしょうか

SELECT * FROM users WHERE col2 = Z ORDER BY col1;

3.WHERE句にcol2、8.ORDER BYにcol1が記述されていて、インデックスの記述順とSQLの評価順序が逆転しています

この場合インデックスは無効になってしまいます

インデックス作成でカラムを指定するときは、評価順序を意識しましょう