https://www.bizreach.co.jp/ 
 プラットフォーム基盤推進室
 DBRE Group
 MySQL Casual Talks #12 ~ schema policy check ~

Hello!!
 *********** 1. row ********** name: 粟田 啓介 nickname: あわっち title: DBRE twitter: @_awache 1 rows in set (0.00 sec) mysql > SELECT * FROM me \G

README ✅ この資料は @_awache という個人の独断と偏見により作成したものです。 ✅ 所属する組織の意見を代表するものではありません。 ✅ 合法性や安全性、情報の正確性についても保証できません。 カジュアルな会(のはず)なので内容もノリも軽めです。 カシュっとしながら 楽しんでいただければと思いますｍｍ ✅ Theme by Template Park. ＞https://template-parks.com/

Schema Policy

スキーマポリシー決めてますか？
 ○ 今回は INDEX の名前について
 ➢ 命名規則、なんだかんだないと辛くないですか。。。 
 ■ 特に EXPLAIN 見るとき 
 ● alias が適当に付けられたりするとDBA的には死ぬ
 ● ORM とかで自動的に作られたものにありがち
 ● 実は名前と実際に貼ってあるカラムが違かったり。。
 mysql> EXPLAIN SELECT * FROM film f, language l WHERE f.language_id = l.language_id; +----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+-------+ | 1 | SIMPLE | f | NULL | ALL | idx_fk_language_id | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | l | NULL | eq_ref | PRIMARY | PRIMARY | 1 | sakila.f.language_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+--------------------+---------+---------+----------------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)

これを導入するための物語(序章)
 ○ 前回の DBRE Night で。。


僕が勝手に決めた雑ルール
 ○ PK
 ➢ 基本的にはサロゲートキー推奨 
 ■ とはいえもう複合PKとかたくさんある。。
 ■ 今回は複合PKは一旦除外
 ➢ table_name + ’_’ + id 
 
 ○ UK/FK/INDEX
 ➢ [uk|fk|ix] + ’_’ + table_name + ’_’ + col_name 


本当にあった、怖い話
 ○ 名前に嘘つかれた
 ➢ 
 ➢ 
 ➢ 
 ➢ 
 
 ➢ 本当は cc_id, rc_id の順番で Unique Key ついてるのに名前が
 `uk_sample_rc_id_kc_id`
 ➢ 更にたちが悪いのは cc_id がそのテーブルに存在しちゃった
 mysql> show create table sample \G *************************** 1. row *************************** Table: sample Create Table: CREATE TABLE `sample` ( `sample_id` bigint(20) NOT NULL AUTO_INCREMENT, `cc_id` bigint(20) NOT NULL, `rc_id` bigint(20) NOT NULL, `jj_id` bigint(20) NOT NULL, `kc_id` bigint(20) NOT NULL, PRIMARY KEY (`sample_id`), UNIQUE KEY `uk_sample_ rc_id_kc_id` (`cc_id`,`rc_id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec)

mysql_schema_policy_check
 ○ ディレクトリ構成
 mysql_schema_policy_check ┣ test ┃ ┗ docker ┃ ┣ go ┃ ┃ ┗ Dockerfile ┃ ┣ mysql ┃ ┃ ┣ conf.d ┃ ┃ ┃ ┗ my.cnf ┃ ┃ ┣ initdb.d ┃ ┃ ┃ ┗ 01.sakila.sql ┃ ┃ ┗ Dockerfile ┃ ┗ docker-compose.yml ┗ mysql_schemapolicy_check.go

information_schema から
 ○ statistics / key_column_usage
 ➢ この２つのテーブルから INDEX のリストを取得して 
 命名規則に則っているかチェックするだけ 


叩いてみる
 [root@d4c10a6f95a9 go]# go run mysql_schema_policy_check.go -h mysql-schema-rule-check -u root -D sakila -P 8015 mysql-schema-rule-check / 8015 / root / / sakila { "Primary Key": [ { "primary_key": "film_id", "table_name": "film_text" } ], "INDEX": [ { "column": ["last_name"], "current_key_name": "last_name", "reccomend_key_name": "ix_actor_last_name", "table_name": "actor" }, : : ], "Foreign Key": [ { "column": ["city_id"], "current_key_name": "fk_address_city", "reccomend_key_name": "fk_address_city_id", "table_name": "address" }, : : ], "Unique Key": [ { "column": ["rental_date", "inventory_id", "customer_id"], "current_key_name": "rental_date inventory_id customer_id", "reccomend_key_name": "uk_rental_rental_date_inventory_id_customer_id", "table_name": "rental" }, : ] }

命名規則に沿った形に変更する
 ○ statistics / key_column_usage
 ➢ この２つのテーブルから INDEX のリストを取得して 
 命名規則に則っているかチェックするだけ 
 mysql> ALTER TABLE rental RENAME INDEX `rental_date` TO `uk_rental_rental_date_inventory_id_customer_id`; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0

叩いてみる
 [root@d4c10a6f95a9 go]# go run mysql_schema_policy_check.go -h mysql-schema-rule-check -u root -D sakila -P 8015 mysql-schema-rule-check / 8015 / root / / sakila { "Primary Key": [ { "primary_key": "film_id", "table_name": "film_text" } ], "INDEX": [ { "column": ["last_name"], "current_key_name": "last_name", "reccomend_key_name": "ix_actor_last_name", "table_name": "actor" }, : : ], "Foreign Key": [ { "column": ["city_id"], "current_key_name": "fk_address_city", "reccomend_key_name": "fk_address_city_id", "table_name": "address" }, : : ], "Unique Key": [ { "column": ["manager_staff_id"], "current_key_name": "uk_store_manager_staff", "reccomend_key_name": "uk_store_manager_staff_id", "table_name": "store" } ] } `rental` table の UK 名が命名規則に則ったため、 `store` のみになった


Skipしたい？
 ○ たまには命名規則に則ってないのも必要悪
 ➢ 引数で json file path 指定したらそこに書かれているもの 
 Skip したいよねー
 ➢ 一度はチェック通ってるわけだし、認識済みだからこそ 
 json に記載してあるわけだし 
 ➢ その願い叶えてみよう 
 ■ 今回は PK の部分で 
 "Primary Key": [ { "primary_key": "film_id", << ルール的には film_text_id "table_name": "film_text" } ],

Sample
 { ’pk’: { ”${table_name}”: [”${pk_col_name}”], : : }, ’uk’: { ”${table_name}”: ”${uk_name}”, : : }, ’fk’: { ”${table_name}”: ”${fk_name}”, : : }, ’ix’: { ”${table_name}”: ”${ix_name}”, : : } }

叩いてみる
 [root@d4c10a6f95a9 go]# go run mysql_schema_policy_check.go -h mysql-schema-rule-check -u root -D sakila -P 8015 -s skip.json mysql-schema-rule-check / 8015 / root / / sakila { "Primary Key": [], "INDEX": [ { "column": ["last_name"], "current_key_name": "last_name", "reccomend_key_name": "ix_actor_last_name", "table_name": "actor" }, : : ], "Foreign Key": [ { "column": ["city_id"], "current_key_name": "fk_address_city", "reccomend_key_name": "fk_address_city_id", "table_name": "address" }, : : ], "Unique Key": [ { "column": ["rental_date", "inventory_id", "customer_id"], "current_key_name": "rental_date inventory_id customer_id", "reccomend_key_name": "uk_rental_rental_date_inventory_id_customer_id", "table_name": "rental" }, : ] } PK に対する Skip が効いて check 対象から除外


thesaurus 使いたい？
 ○ そのままカラム名使うと文字数オーバー
 ➢ なんとか短縮系もいい感じに使いたい 
 ➢ どんな感じで短縮するかはその時々だし 
 ➢ 同じカラムでも複数のものがあるかもしれない 
 ➢ よし、類義語辞書を作りゃいんじゃね？ 
 ➢ それで json 作って 引数で渡せば。。。 


Sample
 { ’${column_name1}’: [ ’${thesaurus1}’, ’${thesaurus2}’ ], ’${column_name1}’: [ ’${thesaurus1}’, ’${thesaurus2}’ ], : : }

Example
 mysql> show create table film \G *************************** 1. row *************************** Table: film Create Table: CREATE TABLE `film` ( `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, `release_year` year(4) DEFAULT NULL, `language_id` tinyint(3) unsigned NOT NULL, `original_language_id` tinyint(3) unsigned DEFAULT NULL, `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3', `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99', `length` smallint(5) unsigned DEFAULT NULL, `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99', `rating` enum('G','PG','PG-13','R','NC-17') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'G', `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`film_id`), KEY `idx_title` (`title`), KEY `idx_fk_language_id` (`language_id`), KEY `idx_fk_original_language_id` (`original_language_id`), CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE, CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) mysql> ALTER TABLE film RENAME INDEX `idx_fk_original_language_id` TO `ix_film_o_l_id`; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 [root@d4c10a6f95a9 go]# cat thesaurus.json { "original_language_id": ["ol_id", "o_l_id"] }

叩いてみる
 [root@d4c10a6f95a9 go]# go run mysql_schema_policy_check.go -h mysql-schema-rule-check -u root -D sakila -P 8015 mysql-schema-rule-check / 8015 / root / / sakila { : : "INDEX": [ : : { "column": ["original_language_id"], "current_key_name": "ix_film_original_language_id", "reccomend_key_name": "ix_fk_film_ol_id", "table_name": "film" }, : : [root@ go]# go run mysql_schema_policy_check.go -h mysql-schema-rule-check -u root -D sakila -P 8015 -t thesaurus.json mysql-schema-rule-check / 8015 / root / / sakila d4c10a6f95a9 { : : "INDEX": [ : : } ちゃんとチェックの対象から外れる


僕たちのPlatform思想の根底
 ○ 前回の mysql_casual_talks でのこれ
 https:/ /speakerdeck.com/_awache/no-ops-more-code

これから
 ○ まだまだ中身は荒いスクリプト
 ➢ 考慮が漏れている部分もあるかもしれない 
 ➢ 少しづつ成長させればいい 
 
 ○ 全事業にルールの統一化を目指したい
 ➢ どうなるかは分からない 
 ➢ 静観するよりもやって失敗したほうが絶対いい 
 ➢ (公開して意味のあるものなら公開してみるのもあり) 


Thanks!! @_awache Contact me!