Cat Paw Software

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== MySQL ======
  * [[http://dev.mysql.com/downloads/|バイナリダウンロード]]
  * [[http://dev.mysql.com/doc/refman/5.1/ja/index.html|MySQL 5.1リファレンスマニュアル]]

ネットの情報は間違いも多い。鵜呑みにせず公式のマニュアルで確認すること。もちろんこのページの情報も鵜呑みにしないこと。きちんとマニュアルで調べる。すべての情報はマニュアルに掲載されている。公式サイト以外でもオライリーの書籍（実践ハイパフォーマンス等）はほぼ無条件で信用してもいいだろう。

===== DDLクエリが終わらない =====

alter tableがなかなか終わらない場合、プロセスリストを見てみよう

<code>
show processlist

or 

show full processlist
</code>

''Waiting for table metadata lock''と出ている場合は、他のプロセスが該当のテーブルにトランザクションを開いているためDDLがロックされている状態。

ありがちなのはGUIのデータベースクライアントツールがトランザクションを張っているケース（例えばDBeaverはテーブルを開くだけでトランザクションを作成する）。アプリを閉じましょう

===== readonlyが効かない =====

<code>
set global read_only = 1; 
</code>

を実行しても書き込みできてしまう。

super権限を持っているユーザにはread_onlyは効かない。セキュリティ上の観点からもアプリからはsuper権限を持たないユーザで接続するのをオススメする。
===== rootログインできない！ =====

Ubuntu 18.08 + MySQL 5.7で発生。

認証方法がauth_socketとなっており、OSのrootユーザのみMySQLのrootユーザでログイン可能な設定にされている。なので sudo すればOK

    sudo mysql -u root

認証方法を以前と同じ（mysql_native_password）に変更するには

<code>
$ sudo mysql -u root 
mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード;    
</code>

なお、認証方法がauth_socketの状態では ''sudo mysql_secure_installation'' を実行してrootパスワード設定しても反映されなかったので、上記手順で認証方法を変更する必要があった。

==== パスワード要件 ====

5.7以降、デフォルトのパスワード要件がmedium policyとなっており、

  * パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む
  * 長さ8文字以上

でないと設定できない。


===== データ無いのに非常に遅い =====
DBサーバが別のとき、接続するとものすごい時間がかかる場合がある。

DBサーバが名前解決できない状態になっている（セキュリティ上の理由でそういう設定になっているサーバもある）とコネクションを張るのに時間がかかってしまう。my.cnfのmysqldの項目に

  [mysqld]
  skip-name-resolve

名前解決が出来ないので、ユーザアカウントのホストにはlocalhostまたはIPアドレスしか使えない。% はOKっぽい。

[[http://dev.mysql.com/doc/refman/5.1/ja/privileges-options.html|MySQL :: MySQL 5.1 リファレンスマニュアル :: 4.6.3 セキュリティ関連の mysqld オプション]]


===== パスワードが間違っていると言われる =====
例えば、環境移行の時インストール直後などのタイミングで、今まで動作していた hoge@% というユーザを登録してローカルホストからアクセスしようとすると拒否される場合は、ユーザテーブルを確認する。

  use mysql
  select host, user from user;

^host^user^
|%|hoge|
|localhost| |

名無しのlocalhostユーザが登録されていると、ユーザ名にhogeを指定してlocalhostから接続すると名無し@localhostとして接続される。対処としては hoge@localhost というユーザを登録するか、名無しのユーザを削除する。

名無しのユーザやパスワード無しユーザはインストール直後に削除してしまうと良いかもしれない。

===== 外から接続できない =====
DebianやUbuntuでは、aptでインストールするとデフォルトで外部からの接続を許可しない設定になっている。

my.cnfの
  bind-address = 127.0.0.1
をホストのIPアドレスに置き換える

bind addressはMySQL Serverがlistenしているネットワークソケットをバインドするアドレスを指定する。127.0.0.1は一般にループバックデバイスにマップされるため、ホスト内からのアクセスのみ受け付けるようになる。

ホストが複数のネットワークインターフェースを持っており、それぞれが社内と社外に接続している場合がある。社内LANに172.29.10.5で公開され、社外には192.168.1.4で公開していたとすると、
  bind-address = 172.29.10.5 
と指定することで社内からのアクセスのみ受け付けるようになる。


全てのIPv4インターフェースからのアクセスを許可
  bind-address = 0.0.0.0

全てのIPv4/v6インターフェースからのアクセスを許可
  bind-address = ::

IPv6が有効なら全てのv6、そうでなければ全てのv4インターフェースからのアクセスを許可（MySQL 5.6.6〜）
  bind-address = *

v6v4両方でアクセスするなら両方指定する
  bind-address = ::
  bind-address = 0.0.0.0
  
===== mysql_upgrade =====

例えば5.6→5.7へアップグレードするとshow variablesでエラー。

<code>
mysql> show variables;
ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist
</code>

他にも5.7→8.0へアップグレードするとshow databasesでエラーなど

<code>
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
</code>

これはバージョン間の互換性がなくなっているため

<code>
$ mysql_upgrade -u root
</code>

で、mysqldを再起動する（不要かも）。バージョンアップした時は場合はmysql_upgradeを実行！


===== 踏み台 =====
ローカルホストから直接mysql接続できない場合、踏み台サーバ経由の方法

  * 踏み台サーバ、ホスト名 proxy.example.com、sshを22222でサーブ
  * MySQLサーバ、ホスト名 remote-mysql.example.com、mysqlを3306でサーブ

sshトンネルの設定

  ssh -f -N -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com

別のターミナルでリモートのMySQLに接続

  mysql db_name -u mysql_user_name -p -P ３３３06

sshをバックグラウンドで動かす（終了後SSHをkillし忘れないように）

  ssh -f -N -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com
  mysql db_name -u mysql_user_name -p -P ３３３06

自動でkillする方法（sleep 10の場合、10秒以内に接続しないとsshが終了するので注意）

  ssh -f -L 33306:remote-mysql.example.com:3306 -p 22222 nullpon@proxy.example.com sleep 10
  mysql db_name -u mysql_user_name -p -P ３３３06    
===== テーブルの種類（ストレージエンジン） =====
  * MyISAM - テーブルロック、フルテキストインデックス、Rツリーインデックス、超爆速count、超爆速insert（ただし同時にselectされないこと）
  * InnoDB - 行ロック、トランザクション、外部キー、並列処理
  * memory - メモリ上にデータを保存。高速だがclustered indexやforeign keyが使えない、テーブルロックのみといった制限あり
  * Blackhole - すべてをdev/nullに記録(？)。つまりinsertしても何も残らない。用途不明…

MyISAMストレージはselectされなければinsertが速いので、めったにselectされないログ保存などに向いている。Memoryストレージは速いが実装が極力衝突しないように改良されたハッシュテーブルらしく、BTreeインデックスではなくHashインデックスがデフォルトになっている。一般的な用途ならInnoDB一択。


===== インデックスの使い方 =====
インデックス（B-treeインデックス）を使った検索では、絞られる件数が少ないほど高速になる。ユニークキーのように1レコードを特定できるインデックスならテーブルに1億レコードあっても待ち時間はほぼゼロである。

しかしインデックスの効果は、条件に一致するレコードが増えるほど減少する。例えば性別のようにデータの種類が2パターンしかないようなものは全く効果が期待できない。このようなカラムを「カーディナリティ度が低い」という。B-treeインデックスはカーディナリティ度が高いカラムに対して使うことで効果を発揮する。

MySQLはテーブルの30％以上のレコードが取得されそうだと予想するとインデックスを使わない。カーディナリティ度の低いカラムに対するwhere条件の検索では多くのレコードが取得される可能性があり、MySQLのオプティマイザはインデックスを使用しないことを選ぶ可能性が高い。

低カーディナリティ度のカラムにはbitmapインデックスが有効だが、残念ながらMySQLには搭載されていない、なおカーディナリティ度とは全レコード数に対する値の種類の数であるため、レコードが3しかなければ性別のような項目でもカーディナリティ度は低いとは言えない。（単に「カーディナリティ」といった場合は値の種類の数そのものを指す、性別の場合カーディナリティは２）。


===== 複合インデックス =====
以下のようなwhere句の場合
<code>
where a = ? and b = ?
</code>
aカラムとbカラムにバラバラにインデックスを付けても、一方のインデックスしか使用されないためインデックスの効果が低下する((インデックスマージによって両方のインデックスが使用されることもある))。このような場合は、aカラムとbカラムで複合キーを作成する。

検索条件が以下の3種類であることが多い場合は、
<code>
where a = ? and b = ?
where a = ?
where b = ?
</code>
aとbの複合インデックスに加えて、bカラムのみのインデックスを作成すると良い。aカラムのインデックスは不要。なぜならばaとbの複合インデックスはaカラムのみの検索でも使用可能だから。（bとaの複合インデックスの場合は逆にaカラムにインデックスを付けて、bカラムには付けない。複合インデックスの順番に意味がある）

マルチカラムでのIN条件検索で
<code>
where (a, b) in (('x', 1),('y', 2))
</code>
aとbの複合インデックスがあれば検索は効果的だが、aカラムにしかインデックスが無い場合は、
<code>
where (a, b) in (('x', 1),('y', 2)) AND a in ('x', 'y')
</code>
とやると高速化が見込める。ただ、O/RマッパはマルチカラムでのIN条件でのクエリ自動生成をサポートしていないものもあるので、あまり使わないかもしれない。いずれにせよ、素直に複合インデックスを作成する方が効果的だろう。

複合インデックスを例えば3カラム（a, b, c）で作った場合
<code sql>
where a = ? and b = ? and c = ?
where a = ? and b = ?
where a = ?
</code> 
という検索条件のとき複合インデックスが使用される。以下では使われない
<code sql>
where b = ? and c = ?
where b = ?
where c = ?
</code> 
複合インデックスの作り方をよく考えるとインデックスの数を減らせることがある。

===== インデックスマージ =====

MySQL5以降の機能で、1つのselect文で複数のインデックスを利用できる。

以下の3種類のマージメソッドが存在する

  * インデックスマージ共通集合アクセスアルゴリズム
  * インデックスマージ和集合アクセスアルゴリズム
  * インデックスマージソート和集合アクセスアルゴリズム 

ただしwhere条件によっては一方のインデックスだけが使われるケースも多い。

例

<code>
mysql> show columns from hoge;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| a     | int(11) | NO   | MUL | NULL    |       |
| b     | int(11) | NO   | MUL | NULL    |       |
| c     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show index from hoge;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| hoge  |          0 | PRIMARY  |            1 | id          | A         |      100320 |     NULL | NULL   |      | BTREE      |         |               |
| hoge  |          1 | a        |            1 | a           | A         |         202 |     NULL | NULL   |      | BTREE      |         |               |
| hoge  |          1 | b        |            1 | b           | A         |         202 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
</code>

==== インデックスマージ共通集合アクセス ====

<code>
mysql> explain select * from hoge where a = 1 and b = 2;
+----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                             |
+----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL |    9 | Using intersect(a,b); Using where |
+----+-------------+-------+-------------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)
</code>

and条件で別のキーを指定した場合に使われる（ただしこの例ではaとbの複合インデックスの方が圧倒的に高速）

<code>
mysql> explain select * from hoge where id < 5 and a = 1;
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
| id | select_type | table | type        | possible_keys | key       | key_len | ref  | rows | Extra                                   |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
|  1 | SIMPLE      | hoge  | index_merge | PRIMARY,a     | a,PRIMARY | 8,4     | NULL |    1 | Using intersect(a,PRIMARY); Using where |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-----------------------------------------+
1 row in set (0.00 sec)
</code>

primary keyの範囲検索と他のキー指定でも発生

==== インデックスマージ和集合アクセス ====

<code>
mysql> explain select * from hoge where a = 1 or b = 2;
+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                         |
+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL | 1957 | Using union(a,b); Using where |
+----+-------------+-------+-------------+---------------+------+---------+------+------+-------------------------------+
1 row in set (0.00 sec)
</code>

or条件で別々のカラムを指定した場合に発生する


==== インデックスマージソート和集合アクセス ====

<code>
mysql> explain select * from hoge where a < 5 or b < 5;
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type        | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | hoge  | index_merge | a,b           | a,b  | 4,4     | NULL | 9794 | Using sort_union(a,b); Using where |
+----+-------------+-------+-------------+---------------+------+---------+------+------+------------------------------------+
1 row in set (0.00 sec)
</code>

or条件で別々のカラムを範囲検索した場合に発生する。


===== like条件とインデックス =====
Like条件では前方一致に限りB-treeインデックスが使用される可能性がある。

例を挙げると、郵便番号と住所を格納したテーブルpostalを想定する。このテーブルの郵便番号カラム（postal_code）にインデックスがある場合、

  select * from postal where postal_code like '179%';

のようなクエリに対してオプティマイザはpostal_codeのインデックスの使用を考慮する。B-treeインデックスの仕組みを見ると何故前方一致が有効か理解できるだろう。





===== JDBCドライバのキャラクタセット設定 =====
[[http://dev.mysql.com/doc/refman/5.1/ja/connector-j-reference-charsets.html|文字セットと Unicode の使用]]

4.1以降 useUnicode は無視されようになった。characterEncodingもテーブルのエンコーディングが自動的に設定されるので基本的には不要のようだ。

===== 何が何でもUTF8で =====
どう設定しようが無理やりUTF-8でつなぐ方法。my.cnfに
  [mysqld]
  default-character-set=utf8
  skip-character-set-client-handshake
DB毎に文字コードを変えられないので、あまりお勧めできない。


===== nowとsysdate =====
[[http://dev.mysql.com/doc/refman/5.1/ja/date-and-time-functions.html|日付時刻関数]]
4.1系と5.0系で仕様が異なっている。

4.1ではsysdateはnowのシノニムであり同一の動作をする。いずれもクエリが処理開始された時刻を返す。

一方5.0ではnowはクエリが処理開始された時刻を返し、sysdateは関数が呼ばれた時刻を返す。よってsysdateは同じクエリ内部でも結果が異なる。sysdateは何度も呼び出すとパフォーマンスが劣化するかもしれない？　基本的にはnowで事足りると思われる。

TIMESTAMPカラムはデフォルト値でnowの値が入る。

===== DATETIMEカラムの初期値 =====
初期値にnowの値を指定したいという要望は多いが、残念ながら初期値に関数を指定することはできない。

MySQL 5以降ならばトリガーで代用できる。
<code sql>
CREATE TRIGGER trigger_hoge_before_insert BEFORE INSERT ON hoge
FOR EACH ROW
  SET NEW.one_datetime_column = now(); 
</code>


===== 匿名ユーザをドロップ =====
drop userでは出来ないっぽいので…
  shell> mysql -u root
  mysql> DELETE FROM mysql.user WHERE User = '';
  mysql> FLUSH PRIVILEGES;

[[http://dev.mysql.com/doc/refman/5.1/ja/default-privileges.html|最初の MySQL アカウントの確保]]

===== 最適化 =====
[[http://dev.mysql.com/doc/refman/5.1/ja/explain.html|EXPLAINを使用して、クエリを最適化する]]

==== InnoDBの最適化 ====
長い主キーを避け、AUTO INCREMENTカラムを主キーにする

ダイレクトIOを使う。MySQL専用サーバでバッファブールサイズを巨大にできるときに有効
  innodb_flush_method=O_DIRECT

バッファプールのサイズ、ログファイルのサイズを調整する

ポイントはいかにIOを抑えるかにある。ランダムアクセスが多いデータはSSDに置くのも有効

ディスクのIOスケジューラを変更する。DBデータ専用のHDDを使用しているならばInnoDBのスケジューリングを生かすためnoopまたはdeadlineを選ぶ方法もある。SSDはスケジューリングのコストの方が上回るのでnoopまたはdeadlineにする。ただし、MySQL 5.1以降と最近のLinuxならばIOスケジューラの影響は無くなってきている。
===== レプリケーション =====
あとで



===== テーブル名のケース依存 =====
hogeテーブルがあったとき、
  SELECT * FROM hoge;
  SELECT * FROM HOGE;
この２つのSQLは、WindowsやMacOSX((MacOSXのHFS+は大文字小文字を区別するようにフォーマットすることも可能だが、デフォルトは区別しない))のMySQLサーバでは両方動作するが、Linux上のMySQLサーバは上のSQLしか動かない。

MySQLはhogeというテーブルを作ると、ファイルシステムにhogeという名前でデータディレクトリを作るため、ファイルシステムの大文字小文字の取り扱いの影響を受ける。

テーブル名は小文字統一などのルールを定めて開発すべき。

===== mysqldump =====
データベースhogeをダンプ
  $ mysqldump hoge -u fuga -p -q > db_hoge_dump.sql
テーブル構造のみ
  $ mysqldump hoge -u fuga -p -q --no-data > db_hoge_creata_table.sql

文字化けする場合は、データベースの初期文字コードと同じ値を指定する。例えばデフォルトがlatin1ならば…
  $ mysqlnump hoge -u fuga -p -q --default-character-set=latin1 > db_hoge_dump.sql

ただし、--default-character-setを設定するとダンプデータでset names latin1が実行されているため、ダンプデータを実際の文字コードに置き換える必要がある。よりベターなのは、データベースにデフォルト文字コードを変更する事。

===== ランダムに一行取得 =====
ORDER BY rand()を使う
  SELECT * FROM t WHERE ... ORDER BY rand() LIMIT 1
ただし、テーブルフルスキャン、using temporary、using filesortでとても効率が悪い

===== カラムの照合順序 =====
[[http://dev.mysql.com/doc/refman/5.1/ja/charset-column.html|MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.3.4 カラムのキャラクタセットおよび照合順序]]を参照。

カラムのキャラクタセットをutf-8、照合順序を utf8_unicode_ci とすると、where や order by で表記のゆれを吸収するようになる。例えば半角Aと全角Ａを同一文字として扱うようになる。半角カナと全角カナも同一文字とみなされる。また「か」と「が」を同じ文字として扱われる。

  * uft8_bin  - aやAを別に扱う
  * uft8_general_ci  - a、Aの照合順序を同一にする
  * uft8_unicode_ci  - a、A、ａ、Ａの照合順序を同一にする

<code sql>
CREATE TABLE (
 ...
 ...
) DEFAULT CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`
</code>

キャラクタセット utf8 のカラムを作成するとデフォルト値 utf8_general_ci が設定される。後から utf8_unicode_ci に変更する場合は**カラム毎に変更**する。
<code sql>
ALTER TABLE hoge
  MODIFY COLUMN `fuga` varchar(255) CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`;
</code>

テーブルへの文字セット設定はカラムへの文字セット未指定時のデフォルト値の設定であって、テーブルレベルの照合順序を変更しても既存のカラムの設定は変更されないので注意すること。

<code sql>
-- テーブルのデフォルトキャラクタセットと照合順序を変更、既存のカラムの設定は変更されない
ALTER TABLE 
  CHARACTER SET `utf8` COLLATE `utf8_unicode_ci`;
</code>

===== 空間情報 =====
MySQL 5.1では空間インデックス（Rツリー）はMyISAMのみサポート

緯度と経度を入力できるテーブルを作成
<code sql>
create table places ( 
  id INTEGER NOT NULL AUTO_INCREMENT, 
  name VARCHAR(255) NOT NULL, 
  location POINT NOT NULL, 
  primary key(id), 
  spatial key(location) 
) default character set=utf8, engine=MyISAM;
</code>
データをインサート
<code sql>
insert into places (name, location) values ('東京駅', GeomFromText('POINT(139.766092 35.686721 )'));
</code>
データを取り出し
<code sql>
select name, X(location) as '経度', Y(location) as '緯度' from places 
</code>

===== Fulltextインデックス =====
全文検索インデックス。スペースを区切りにして単語を認識するので日本語では使えない。検索用カラムを作成し、Ngramか形態素解析で分割した文字列を検索カラムに入れる。

ただしデフォルトでは4文字未満の単語は検索対象にならない。
my.cnfを書き換える
  [mysqld]
  ft_min_word_len=2

mysqldを再起動する
フルテキストインデックスの再構築
  REPAIR TABLE tbl_name QUICK

Fulltextインデックスは再構築にも時間がかかるしメンテナンスが死ぬほどめんどくさいのでオススメしない。他の全文検索ライブラリを併用するのがいいだろう。


===== JSON型カラム =====

JSONフィールドの値で検索する

<code sql>
where colName->'$.id' = 1 

where JSON_EXTRACT(colName, '$.id') = 1
</code>