MariaDB

MariaDB

データ基地

MariaDB とはデータ基地を管理するためのApp。C言語製。SQLで基地にあるデータを高速に参照/更新/計算したりできる。アプリは基本的にデータ基地と連携しながら動く。ゆえに、アプリの本質とはデータベース。

当然ながら、MariaDB の公式の説明書、公式チュートリアル は一通り読んでおく。MySQLリファレンス も参照。

セットアップ

1. 設定ファイル etc/my.cnf のモード、所有者、システム変数を確認
2. /var/lib/mysqlディレクトリの所有者を mysql に変更
3. ローカル利用ならリモート接続を禁止
4. 日本語入力のズレを解消

$ mysqladmin -u root -p status
$ ss -tan

SHOW VARIABLES LIKE 'char%';
$ vi /etc/my.cnf

[mysqld]
skip-networking
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

• character_set_system は MariaDB の内部システム用の設定 だから、utf8mb3 のままでOK

セキュリティ

DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost');

リモートから root で MariaDB にログインできると危険なので削除。

rootのパスワードリセット

一旦MariaDBのプロセスを落としてからセーフモードで起動、パスワードを設定してMariaDBのプロセスを再度起動させてやるだけ。

# systemctl stop mariadb
# mysqld_safe --skip-grant-tables &
alter user 'root'@'localhost' identified by 'new_passwd';
# systemctl stop mariadb
# systemctl start mariadb

以上。できなかったら「flush privileges」してから。

データベース設計

Database normalization。データベースを正常な状態にしておく。

要点リスト

• そのテーブルの主人公は誰（なに）なのかを主キーで明確にする
• そのテーブルはどのような「状態」の述語なのかを明確にする
• 実世界において２つの事柄を同時に認識することはできない
• ゆえに認識の鏡であるテーブルにも２つの概念を混ぜてはいけない
• ２つの概念が混じっているように感じるならテーブルを分けるべき
• テーブルを分けた時は外部キーを利用して両テーブルの整合性を保証
• １つの変更が多数箇所の修正に結びつく場合もテーブルを分けられるかも
• カラムに定義する属性は実世界の「状態」を適切に表現しているべき
• 例えば、id と cat_no は違う。id は各要素のID、cat_no はカテゴリ番号
• でも外部キーを追加するには一意性制約があるので上記２つをまとめても良い
• 命名はすべて短くて適切で本質的な表現であるべき
• １つの「状態」を２度も記述するような冗長なカラム設計は避けるべき
• NULL を許すと世界が 3VL になってしまいアプリ設計が非常に複雑になる
• NULL を許すとそのテーブルはオプティマイザとの相性が悪くなる
• NULL を許すとそのテーブルはインデックスとも相性が悪くなる
• NULL が入る余地があるということは DB設計に問題があるかも
• NULL 発生を引き起こす「ありえないデータ」は将来、悩みのタネになるかも
• SQL にはそもそも順序という概念がないので手続き型ロジックの実装は高負荷
• ゆえに、手続き型のロジックに頼るのは最後の手段にする
• 空間や時系列を表現したいのであれば RDB はやめる

データベースの本質

データベースの本質は「実世界のパラレルワールド」をマシンの中に創造すること。ゆえに、データベースとは人間の認識を反映する鏡だと言える。

実世界（やその状態）を「パラレルワールド」としてマシンの中に複製すると、遠隔マシンにバックアップしたり、自作アプリと連携させたり、AIと抱き合わせて情報分析したりと、色々なことができるようになる。

RDB の場合、主にリレーション（テーブル相互の関係性）という表現手法に沿って実世界（やその状態）を整理しつつパラレルワールド化していくことになる。ちなみに、他の表現手法には NoSQL や DIT などがある。

実世界の「本質」を適切に表現

データベースの活用とはつまり実世界データ構造のパラレルワールド化。ゆえに、実世界を適切に認識し、その本質的な状態を適切にデータベースにアップロードしていく必要がある。

しかし、実世界の本質的な状態とは流動的で常に変化していくもの。この性質をしっかりと踏まえ、将来を見越したデータベース設計をする必要がある。

ゆえに、データ構造パラレルワールド化の際には以下３点が特に大切。

1. 実世界の状態や本質を適切に認識する能力
2. 目的から逆算してデータを整理する能力
3. その状態を適切に表現する能力

以上３点があれば、その人は適切にデータベースを設計できる。そのような人は自分の部屋と同様にデータたちのお部屋も綺麗に整理することができるはず。

マシンパワーが大前提

データを管理するだけなら別に紙やノートを使っても良いはず。わざわざマシンにデータ管理を任せるのだから「マシンにしかできない何か」を強く期待しているはず。つまり、マシンパワーの活用と目的の実現とのバランスをしっかり前提とした上でテーブル設計やチューニングすることが大切。

データベース設計においては特に、以下２点を強く意識すべき。

1. マシンパワーの活用を大前提としたデータベース設計
2. 目的から逆算した実世界データ構造のパラレルワールド化

チューニング

扱うデータ量が１００万件程度なら、そもそもマシンに負担はかからないのでチューニングする必要はあまりない。しかし、日頃からチューニングを意識したSQLに慣れておくのは良いこと。

捜索範囲は適切に限定する

• データサイズが小さいとパフォーマンス問題は表面化しない。テストはビッグデータで
• WHERE句を利用すれば総なめ捜索は回避されるのでマシンリソースを無駄にしない
• SELECT int, name など必要なデータだけを取得すればマシンリソースを無駄にしない
• 捜索データ１億件など、膨大なデータを捜索する場合は利用列にインデックスを作成
• かつ、カーディナリティ（値の多様性）が高い列にのみインデックスを作成
• インデックスは更新の負担が大きいため無闇矢鱈に作成しない
• 実際に捜索プランを立てている裏方オプティマイザの働きやすさを意識する

アプリ制作において

• SELECT * は使わない。必要なデータを明示して取得する
• データベースを変更しても大丈夫なようにデータアクセスロジックを汎用的にしておく
• Abort発生の可能性はゼロにならないので Abort発生に備えてエラー処理は必ず実装しておく
• 書き込まれるデータの整合性を担保するためにデータ変更はトランザクションを利用する

スレッドプール機能を使う

膨大な数のクライアントからの膨大な数の SQL を処理する場合、スレッドプール機能が役立つ。スレッドプール機能の設定はとても簡単で、稼働中でも動的に設定を変更できる。

# my.cnf
thread_poolmax_threads = 500
thread_pool_idle_timeout = 100000

mysql > show global status like 'threadpool%';