20210303のMySQLに関する記事は6件です。

データベースって何?SQLって何?

データベースについて学習した事についてまとめてみました。

データベース(DB)とは

●データベースは、情報の集まり。
 →検索や蓄積が簡単にできるように整理されている
●データを取り扱う手段として、ほぼ全てのシステムが何かしらのデータベースを使用している
 (例)
  ・ECサイト(Amazon、楽天など)
  ・SNS(Twitter, Instagramなど)
  ・銀行の預金情報管理
  ・住民票管理
  ・etc.
●ECサイトであれば、ユーザー情報や商品情報のようなデータを保存して扱うために、データベースを使う。

なんでデータベースが必要なの?

●大量のデータから必要なデータを取り出すため
●たくさんの人がデータを共有して利用するため
●データを保護するため

データベースマネジメントシステム(DBMS)

●データベースを管理するシステムのこと。

リレーショナルデータベース(RDB)

 ●現在一番ポピュラーなデータベース
 ●列と行からなる表形式でデータを管理する(excelみたいな)
 ●SQLという専用の言語を使ってデータを操作する

 ●主なRDBMS(リレーショナルデータベースマネジメントシステム)製品
  ・オープンソース
   - MySQL
   - MariaDB(MySQLから派生)
   - PostgreSQL
   
  ・商用データベース
   - Oracle Database(Oracle社)
   - SQL Server(Microsoft社)
   - DB2(IBM社)

キー・バリュー型データストア(KVS)

 ●検索に使用するキー(Key)と値(Value)の組み合わせだけのシンプルな形でデータを保存する
  (例) name: John, gender: male

 ●主な製品
  - memchaed
  - Redis

その他(現在はあまり使われていないもの)

・オブジェクト指向データベース(OODB)
・XMLデータベース

SQLとは

 ・データベース、テーブル、行や列を扱うための言語のこと

標準SQLとSQLの方言

  ●標準SQL
   - ISO(国際標準化機構)で定められたSQL
  ●SQLの方言
   - 各データベース製品でしか、使えない方言
  
    ・各データベース製品で、標準SQLの対応にばらつきがあるため、初めは標準SQLを学んでおくとよい。

SQLの記述ルール

  ●大文字・小文字の区別はされない
   (例)
    ・ select * from users;
    ・ SELECT * FROM USERS;
    ・ SELEct * FroM UsErs;

  ●SQL文の最後にはセミコロン
    ・select * from users;

  ●単語の区切りは「半角スペース」 or 「改行」
    ・ select * from users;

    ・ select
      *
      from
      users;

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

mysql どんなデータのパターンが入っているか確認する時のSQL

select DISTINCT column from table;
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

なぜutf8mb4を使ったときにインデックスが足りなくなるのか?発生原因と対策について

モチベーション

令和という絵文字全盛の時代

絵文字もできなようなサービスはちょっとどうなの?とお達しがあり(いや自分で決めたんですが)…
既存のRails + MySQL構成のアプリで絵文字を使えるよう変更することになりました。

絵文字を使うために、MySQLのcharctersetをutf8mb4に変換すれば良いことは、すぐに分かりました。
その後、他に落とし穴がないか調べると、インデックス足りないよという問題が起きうることがわかりました。

ただ、

  • どうしてインデックスが足りなくなるのか?
  • 足りなくなるときの条件は自分たちのサービスにも当てはまるのか?

などがわからず、いくつもドキュメントをあたって調査したので、備忘録を残しておきます。

ゴール

  • どうしてインデックスが足りなくなるのか?
  • 足りなくなるときの条件は自分たちのサービスにも当てはまるのか?

が理解できる。

結論

なぜutf8 -> utf8mb4 にcharctersetを変更したとき、インデックス足りなくなるのか?

  • MySQLのversionや設定(※1)によっては、インデックス長が767バイトである。
  • utf8は、1文字3バイト。767 / 3 = 255文字分のインデックスが可能。
    • 慣習的にインデックスを255文字にしておくことがある。
  • utf8mb4を使うと、1文字4バイトなので、767 / 4 = 191文字分しかインデックスを効かせることができない。
    • 255文字分のインデックスが貼れない
    • 既存のデータもインデックスが効かなくなる

という理屈です。

こんなエラーが出ます。

Index column size too large. The maximum column size is 767 bytes

※1 発生条件は後述

どうしたらいいか?(概念)

インデックスの最大キー長を767バイトから3072バイトに拡張したら良いです。
どうに変更したら良いかは、5.6の公式ドキュメントのsysvar_innodb_large_prefix部分に記述があります。

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use DYNAMIC or COMPRESSED row format. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.22, “InnoDB Limits” for maximums associated with index key prefixes under various settings.

For tables that use REDUNDANT or COMPACT row format, this option does not affect the permitted index key prefix length.

拡張方法としては、

  • ファイルフォーマットを、Barracudaにする
  • 行フォーマットがDYNAMICまたはCOMPRESSEDにする
  • innodb_file_per_tableパラメータがONである
  • innodb_large_prefixパラメータがONである

となります。

どうしたらいいか?(具体的な設定)

↑↑↑のように設定が変えればOKです
※ 環境によってやり方が違うので、また別の記事にまとめます。

概念説明

以上で、対応方法はわかったのですが、

  • ファイルフォーマット?
  • 行フォーマット?
  • DYNAMIC?

などなどわからないことが盛りだくさんなので、1つ1つ見ていきます。

データベースエンジン

まずは、データベースエンジンです。

InnoDBやMyISAMが有名です。代表的にはテーブルロックの仕組みが違います。

  • InnoDB(レコード(行)単位のロック)
  • MyISAM(テーブル単位のロック)

テーブルロックの場合、レコードをいじるときに、文字通りテーブル全体がロックされてしまい、他のレコードをいじることができなくなります(= パフォーマンスが悪くなる。)
今回の触るサービスが、InnoDBだったので、以下はInnoDBの話になります。

InnoDBのファイルフォーマットとは? / レコードフォーマットとは?

InnoDBでファイルをどのように保持するか、レコードをどのように保持するか、の設定です。

ファイルフォーマットが2種類、レコードフォーマットがそれぞれ2種類あります。

ファイルフォーマット 利用可能なレコードフォーマット 補足
Antelope Compact または Redundant 古いファイルフォーマット
Barracuda DYNAMIC またはCOMPRESSED 新しいファイフォーマット。インデックス問題を回避するためこっちを使うと良い。

innodb_file_formatパラメータで指定可能ですが、変更を反映させるためにはInnoDBの再起動が必要です。

デフォルト値は下記の通りです。
- MySQL5.6以前:Antelope
- MySQL5.7以降:Barracuda

レコードフォーマットの違い(DYNAMIC / COMPRESSED)

詳細わからず、これくらいの理解…

DYNAMIC = 大きいレコードを扱うとき、ポインタで先頭データを保持して、実データを別にもつ
COMPRESSED = DYNAMIC + データ圧縮している

確認方法

ファイルフォーマットとレコードフォーマット

先述の通り、ファイルフォーマットとレコードフォーマットの組み合わせが決まっているので、SQLを叩くと確認できます。

# 全テーブルを確認する方法
mysql> SHOW TABLE STATUS \G

...一部抜粋...
*************************** 33. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 910
 Avg_row_length: 162
    Data_length: 147456
Max_data_length: 0
   Index_length: 180224
      Data_free: 0
 Auto_increment: 915
    Create_time: 2021-02-02 12:25:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

...一部抜粋...


# 特定のテーブルだけ確認する方法
show table status from your_database_name where name = 'articles' \G                       

この場合、「Row_format: Compact」なので、「ファイルフォーマットはAntelope」だとわかります。

innodb_file_per_table

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_large_prefix

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.06 sec)

変更方法

Rails環境で設定を変更しました。
このあたりは別記事で。

インデックスが足りなくなるのか?に関するまとめ

  • 5.1 / 5.5 / 5.6: 注意が必要。アップデートするときにでも設定を変えよう
  • 5.7 / 8.0: デフォルトでそれなりに用意されているので大丈夫っぽい

2021年1月にAWSからアナウンスもあり、
5.6系のサポートもなくなるので、おとなしく5.7や8.0にアップデートするのが良いでしょう。

wikiによれば、5.7のリリースが2015年なので、それ以前のものがサポート対象から外れていくのは仕方ないですね。

参考文献

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLの文字コード関連設定を1つ1つ説明する

提供しているサービスで絵文字を利用できるようにした際の記録です。

「MySQL 絵文字」のようなクエリで検索をかけると、色々な記事にヒットします。
文字コードをutf8mb4にすれば良い、というのはすぐに分かるのですが

  • どうしてこの設定を変更するのか?

の解説記事が少なかったので、調べた内容をまとめておきます。

前提条件&環境

  • AWSのRDSでデータベースを管理している
  • MySQL 5.6.x
    • 変更前の文字コードはUTF-8

RDSで文字コードを確認する方法

RDSのコンソールでcharacter_ で検索かけると確認できます。
6つの項目がヒットすると思います。(画像は一部抜粋)

Kobito.KWYxc9.png

それぞれ「utf8」が設定されていました。

MySQLの文字コード関連の設定値の意味

概要

英文はRDSまま、翻訳は筆者訳です。

項目 意味(英) 意味(日)
character_set_client The character set for statements that arrive from the client クライアント側から受け取った命令文の文字コード
character_set_connection The character set used for literals that do not have a character set introducer and for number-to-string conversion イントロデューサー(※1)なしの文字列や数値から文字列への変換に利用される文字コード
character_set_database The character set used by the default database. デフォルトデータベースで使う文字コード
character_set_filesystem The file system character set. ファイルシステムで使う文字コード。csvやtsvなどを読み込むときに、ファイル名の解釈(ファイル名を参照する文字列リテラル)に使う文字コード。
character_set_results The character set used for returning query results to the client. SQLの実行結果(クエリ)をクライアントに返すときに利用する文字コード
character_set_server The server's default character set. MySQLサーバーのデフォルトの文字コード

また、下記も変更が必要です(正確には変更したほうがベター)

項目 意味(英) 意味(日)
collation_server - 文字列の照合順序

詳細

概要だけだとわかりにくいので、詳細も調べました。(概要のままのもあります。すいません。)

character_set_client

クライアント(mysql)から送られてきた文字コードをどのように解釈するか、です。

character_set_connection

イントロデューサーなしの文字列や数値から文字列への変換に利用される文字コードです。

イントロデューサーとは?

文字列(正確には文字列リテラル)の前に記載されている文字コードのことです。
例では、_latin1の部分です。
イントロデューサーが、SQLパーサーに、後続する文字列リテラルが●●文字セットですよ、と教えています。

例:

SELECT _latin1'string';

character_set_database

MySQLのドキュメントから引用します。
* charecter_set_database

デフォルトデータベースで使用される文字セット。
デフォルトのデータベースが変更されるたびに、サーバーはこの変数を設定します。
デフォルトデータベースが存在しない場合、変数は character_set_server と同じ値になります。

デフォルトデータベースとは?

use文を使って選択したデータベースのことです。

# これで指定したデータベース
use database_name

character_set_filesystem

SQLのLOAD_FILE()関数などで、ファイル名を参照するときに、どの文字コードで読み取るかのオプションです。
(あくまで、ファイル名の解釈であり中身ではない)

私の環境では、この値は空でした。
デフォルト値はbinaryです。

Amazon RDS for MySQL のパラメーターを設定するためのベストプラクティス。パート 3: セキュリティ、操作管理性、および接続タイムアウトに関連するパラメーター

character_set_results

SQLの実行結果(クエリ)をクライアントに返すときに利用する文字コード

character_set_server

MySQLサーバーのデフォルトの文字コード

collation_server

collationは照合する、という意味です。
SQLのWHERE等で、文字列比較を行う際に設定します。

詳しいことは参考文献を読んでいただくと良いのですが、このパラメータの設定次第では、
寿司の絵文字とビールの絵文字が同一になってしまう問題が発生してしまいます。
(? = ?:寿司ビール問題というそうです。天才的なネーミングですね。)

設定例

最後にクラウドフォーメーションの設定例です。

ポイントは下記の通り
* しれっとMySQLのversionを上げている
* パラメータグループ名が衝突して更新できないので、名前を変えている
* MySQL関連の文字コードを変えている
* 照合順序も変えている

template.yml
- RdsParameterGroup
+ RdsParameterGroup57:
    Type: "AWS::RDS::DBParameterGroup
    Properties:
-      Family: mysql5.6
+      Family: mysql5.7
      Description: "MySQLのパラメータグループの設定です。実環境では適切な名前を設定してください。"
      Parameters:
        slow_query_log: 1   
        query_cache_type: 1  
        query_cache_size:  10485760
        query_cache_limit: 1048576  
        innodb_buffer_pool_dump_at_shutdown: 1
-       character_set_client: utf8     
-       character_set_connection: utf8     
-       character_set_database: utf8            
-       character_set_results: utf8
-       character_set_server: utf8    
-       collation_server: utf8_general_ci
+       character_set_client: utf8mb4
+       character_set_connection: utf8mb4
+       character_set_database: utf8mb4
+       character_set_results: utf8mb4
+       character_set_server: utf8mb4
+       collation_server: utf8mb4_bin
        skip-character-set-client-handshake: 1
      Tags:
        - Key: Name

参考文献

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

AWS EC2インスタンス内からRDSのMySQLにログインする

EC2インスタンスのターミナルからRDSのMySQLにログインする

目的

  • EC2インスタンス内からRDSのMySQLにログインする方法をまとめる

実施条件

  • EC2インスタンスとRDSインスタンスのセキュリティグループ設定やIAMの設定が正常に行われていること。
  • RDSインスタンスのエンドポイント、MySQLのユーザ名、MySQLのユーザに紐づくパスワードがわかっていること。

詳細

  1. EC2のインスタンスにsshでログインする。
  2. 下記コマンドを実行してMySQLにログインする。

    $ mysql -u MySQLのユーザー名 -p -h エンドポイント
    
  3. ログインパスワードを問われたら入力する。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

mysqlをローカル環境で最新版にしようとした時にエラーになった

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む