[mysql: note] MySQL command & tips note. #mysql #sql

mysql.md

TIPS & REFERENCES

• サイボウズ版 MySQL パフォーマンスチューニングとその結果

Explain

MySQLのEXPLAINを徹底解説!!

実行計画の確認。

EXPLAIN SELECT * FROM Country WHERE ...;

Optimize / Analyze

https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html

innodb でも DELETE でデータが歯抜け状態になるので、削除が多いやつは定期的にやるとよろしいらしい。

# SHOW TABLE STATUS で状態確認してからやってね
> OPTIMIZE TABLE users;

あと統計情報は innodb の場合殆ど気にsなくていいらしいが、大きくデータのカーディナリティが変化したりすると狂うので以下で更新してやるとよい。

> ANALYZE TABLE users;

innodb_buffer_pool_size

https://dba.stackexchange.com/questions/199397/why-query-with-sql-no-cache-runs-slower-on-the-first-run

SQL_NO_CACHE を使用したクエリの実行が最初の実行で遅い

クエリキャッシュが唯一のキャッシュではありません。...InnoDBバッファープールには、頻繁にアクセスされるインデックスとデータが含まれます。最初のクエリの後、一部のインデックスとデータはキャッシュされるため、次回はそれらがメモリから読み取られます。おそらくいくつかのデータ/インデックスページはクエリごとに1回しかアクセスされません。

最初に実行するときにクエリを高速化する方法はありません。ただし、バッファプールが十分に大きい場合、クエリは常に高速になります。MySQLのパフォーマンスには、大きなバッファプールが非常に重要であることに注意してください。一般的な推奨事項は、総メモリの75〜80％を維持することです。

クエリキャッシュ

https://dev.mysql.com/doc/refman/5.6/ja/query-cache-configuration.html
https://qiita.com/mamy1326/items/d1548d8cf4528277172a
https://qiita.com/tukiyo3/items/797f9916e6494ec33991

クエリキャッシュ機能は 5.7 で非推奨 ( デフォルト OFF ) 、8 から削除されたらしいよ。スケールしづらいという理由。

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      | --キャッシュクエリを使用可能か
| query_cache_limit            | 1048576  | --クエリキャッシュ最大サイズ(1MB)
| query_cache_size             | 33554432 | --クエリキャッシュ領域。(32MB)
| query_cache_type             | ON       | --クエリをキャッシュする(SELECT SQL_NO_CACHE以外)
| query_cache_wlock_invalidate | OFF      | --書込みロック獲得時に、ロックしたテーブルに関するクエリキャッシュを無効にするか
+------------------------------+----------+

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 2108       | --空きメモリーブロックの数。起動時は1。1だと良い。それ以上だと断片化しているためFLUSH QUERY CACHEにてキャッシュのデフラグも検討。
| Qcache_free_memory      | 6830336    | --クエリーキャッシュ用の空きメモリーの量
| Qcache_hits             | 3810442057 | --クエリーキャッシュヒットの数。
| Qcache_inserts          | 397612602  | --クエリーキャッシュに追加されるクエリーの数
| Qcache_lowmem_prunes    | 307230686  | --メモリーが少ないためクエリーキャッシュから削除されたクエリーの数
| Qcache_not_cached       | 151054542  | --非キャッシュクエリーの数 (キャッシュできないか、query_cache_type 設定のためキャッシュされない)
| Qcache_queries_in_cache | 8044       | --クエリーキャッシュ内に登録されたクエリーの数
| Qcache_total_blocks     | 26009      | --クエリーキャッシュ内のブロックの合計数

メモリ変更

MySQL最低限のメモリ設定

# InnoDB の場合 ...
mysql> SHOW VARIABLES LIKE "innodb_%_size";
+---------------------------------+---------+
| Variable_name                   | Value   |
+---------------------------------+---------+
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size         | 8388608 |
| innodb_log_buffer_size          | 1048576 |
| innodb_log_file_size            | 5242880 |
+---------------------------------+---------+

# 設定ファイル変更
$ vi /etc/my.cnf
> innodb_buffer_pool_size = 256M  # 8 MB からぶちあげ
> innodb_log_file_size = 64M      # 上記の 25% 程度が適当らしい

# 再起動
$ service mysqld restart

ポート

デフォルトでは 3306 です。

ストレージ領域を変更する

MySQL のデータディレクトリを移動する - qiita.com

ソケット sock や一時ディレクトリ／ログ系も /var/lib/mysql から移動させるかどうかは割とケースバイケースぽい ( 参考 ) 。ルートボリュームでイケるならそのままでもイイケド、別ストレージが SSD で高速 & ルートボリュームは HDD で低速なときはまるっとお引越ししたほうがいいね。

$ sodo su
$ service mysqld stop  # 一度 mysqld を立ち上げてから落とすこと
$ vim /etc/my.cnf
> datadir=/export/mysql
$ cp -pRf /var/lib/mysql/* /export/mysql
$ service mysqld start

わんらいにゃー

# Set mysql data storage.
sudo service mysqld stop
sudo mkdir -p /export
sudo chmod -R 755 /export
sudo mkdir -p /export/mysql
sudo chmod -R 755 /export/mysql
sudo chown -R mysql:mysql /export/mysql
sudo cp -pRf /var/lib/mysql/* /export/mysql
sudo sed -i -e "s|datadir=/var/lib/mysql|datadir=/export/mysql|" /etc/my.cnf
sudo service mysqld start

mysql tuning

http://sawara.me/mysql/1428/

mysqld エラーログ

less /var/log/mysqld.log

MySQL5.6 にしたらCreate文でエラーが

アプリ側でデフォルト値ないカラムの指定しないままSQL分投げてない？MySQL5.5以前と5.6以降で sql_mode が異なることが原因らしい。とりあえず処置として sql_mode の STRICT_TRANS_TABLES をはずせば警告文にとどまりエラーにならんので /etc/my.cnf の sql_mode を "" とかにして再起動。

文字コード

[ utf8mb4_general_ci ] あたりが無難

タイムゾーン

設定がUTCのときはそのまま利用し、出力時に変換(+9:00)するのがよい？ ref: http://tmtms.hatenablog.com/entry/2015/08/22/mysql-timezone

rootパスワードわからんくなった

セーフモードで入れ。https://goo.gl/qoGcNv

ダミーデータテーブルの作成

http://qiita.com/tayasu/items/c5ddfc481d6b7cd8866d

バッククォート

予約語とハイフンつなぎ単語のエスケープで利用。通常のSQLでは不要（DBスキーマ扱いになってしまう）。

設定ファイル my.cnf は？

大体 /etc/my.cnf にいるけどインストールバッチ(mysql_install_db.shとか)使うとMySQL本体ディレクトリにDB毎のが自動生成されてそっちが優先されちゃう面倒。

深い親子テーブルの正規化・非正規化

http://www.slideshare.net/takumamiura3/ss-44633934

全テーブル TRUNCATE

[MySQL] 全テーブルのデータを一括で削除
【MySQL】 データベース内のテーブルを全てTRUNCATE

$ mysql -u${DB_USER} -p${DB_PW} -D${DB_NAME} -N -e  'show tables' | xargs -IARG mysql -u${DB_USER} -p${DB_PW} -e 'truncate table ARG' -D${DB_NAME}

全テーブル DROP

未検証。

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'database_name'; -- specify DB name here.

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1; 

mysql_command.md

ログイン ~ テーブル作成までの流れ

ルートログイン

$ mysql -h localhost -u root -p
-- DBの選択は USE {DATABASE}
-- ログアウトは QUIT または EXIT

PW変更

SET PASSWORD for 'root'@'localhost' = password('password');

ユーザ追加

CREATE USER '{USER}'@'{HOST}' identified by '{PASSWORD}';

DB作成

CREATE DATABASE {DATABASE} default character set utf8mb4;

DB権限付与

GRANT {ALL/SELECT/INSERT/UPDATE/DELETE} on {DATABASE}.{TABLE} to '{USER}'@'{HOST}' identified by '{PASSWORD}';
-- 権限削除は REVOKE にて行う

テーブル作成

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    pass VARCHAR(255),
    auth VARCHAR(10),
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

---

よく使うやつ

-- ログイン／ログアウト
mysql -h localhost -u root -p
quit

-- ユーザ作成
CREATE USER 'myuser'@'myhost' IDENTIFIED BY 'xxx';
-- ユーザ削除
DROP USER 'myuser'@'myhost';
-- ユーザ一覧
SELECT User, Host from mysql.user;

-- 権限付与
GRANT ALL ON *.* TO myuser@localhost IDENTIFIED BY 'xxxxxx';
- 権限削除
REVOKE ALL ON *.* TO myuser@localhost IDENTIFIED BY 'xxxxxx';

-- データベース作成
CREATE DATABASE mydatabase;
-- データベース削除
DROP DATABASE mydatabase;
-- データベース一覧
SHOW DATABASES;
-- データベース指定
USE {DB_NAME}

-- テーブル作成
CREATE TABLE mytable(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   ColA VARCHAR(20)
);
-- テーブル削除
DROP TABLE mytable;
-- テーブル名変更
ALTER TABLE mytable RENAME TO mytable2;
-- テーブル定義表示
DESC mytable;
-- テーブル一覧表示
SHOW TABLES;


-- レコード選択
SELECT * FROM mytable;
---- WHERE
SELECT * FROM mytable WHERE ColA = 10;
---- ORDER BY
SELECT * FROM mytable ORDER BY ColA DESC;
---- LIMIT
SELECT * FROM mytable LIMIT 10;

-- レコード挿入
INSERT INTO mytable(ColA, ColB) VALUES(10, 'YY');
-- レコード更新
UPDATE mytable SET ColA = 8 WHERE ColB = 'YY';
-- レコード削除
DELETE FROM mytable WHERE ColA = 10;
 
-- トランザクション
---- 開始
BEGIN;
---- 確定
COMMIT;
---- 破棄
ROLLBACK;

-- ファンクション
---- 件数カウント
SELECT COUNT(*) FROM mytable;
---- 現在日時
NOW();
---- 日時→文字列
DATE_FORMAT(col, '%Y/%m/%d %H:%i:%s');
---- 文字列→日時
STR_TO_DATE(col, '%Y/%m/%d %H:%i:%s');

---

SELECT検索いろいろ

WHERE : 基本マッチング

SELECT title, price FROM book_list
  WHERE
  author = 'auth_A';

SELECT title, price FROM book_list
  WHERE
  author <> 'auth_A';

LIKE + ワイルドカード : あいまい検索

[ %:0文字以上の任意の文字列 ] [ _:任意の1文字 ] [ *:全て ]

SELECT title, author, comments FROM book_list
  WHERE comments LIKE '%excellen%';

BINARY で大文字小文字区別

WHERE col_name LIKE BINARY 'pattern'
-- 下記 REGEXP でも利用可能

REGEXP で正規表現検索

SELECT * FROM menulist WHERE menu REGEXP '^(Hot|Ice)';

BETWEEN : 範囲指定

SELECT title, price FROM book_list
  WHERE
  price BETWEEN 1000 AND 2000;
-- "price >= 1000 AND price <= 2000"と等価

IN : 複数条件をまとめる

SELECT title, author, price FROM book_list
  WHERE
  author IN ('auth_A', 'auth_C');
-- " author 'auth_A' OR 'auth_C' " と等価

NOT : 条件反転

SELECT title, author, price FROM book_list
  WHERE
  NOT author IN ('auth_A', 'auth_C');

IS NULL , IS NOT NULL : null検索

SELECT * FROM book_list
  WHERE title IS NULL;
SELECT * FROM book_list
  WHERE title IS NOT NULL;
-- hoge = NULL は NG

CASE , WHEN : 条件分岐(if文)

SELECT 
  (CASE
    WHEN param1=0 THEN 'なし'
    WHEN param1=1 THEN 'あり'
    ELSE  null END
  ) as param1_display
  FROM db_table;

JOIN : 複数テーブルの結合[contain]

SELECT * 
  FROM schedules
    JOIN members ON schedules.member_id=members.id;