mysqlオペレーションメモ

mysqlデータベースのオペレーションでよく使うCLIコマンドやSQLをメモしておきます。（随時更新）

前提

mysqlサーバのバージョン:

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.27-0ubuntu0.16.04.1 |
+-------------------------+

mysqlクライアントのバージョン:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

操作対象DB名: mydb
操作対象の特定テーブル名: mytable
操作対象DBのユーザ名: dbuser

mysqlの導入

mysqlのインストール

$ sudo apt update
$ sudo apt install mysql-server

NOTE: mysqlサーバとクライアントがインストールされます。

mysqlサーバの起動・停止・状態確認

# mysqlサーバの停止
$ sudo service mysql stop
# mysqlサーバの起動
$ sudo service mysql start
# mysqlサーバの状態確認
$ sudo service mysql status

データベースの作成と削除

mysqlクライアントで接続

mysqlクライアントからmysqlサーバへ接続します:

$ mysql -u dbuser -p

もし既にmydbデータベースがあれば直接データベースを選択できます:

$ mysql -u dbuser -p mydb

データベースの作成

mydbデータベースを作成:

mysql> 

CREATE DATABASE mydb;

データベースの削除

mydbデータベースを削除:

mysql> 

DROP DATABASE mydb;

テーブルの作成と削除

テーブルを作成

mytableテーブルを作成します。今回はテスト用データとして郵便番号データを使ってみたいと思います。なお、郵便番号データは KEN_ALL.CSV というCSVファイル(文字コードはSJIS)で、次のようなレイアウトになっています:

01101,"060  ","0600000","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"064  ","0640941","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｱｻﾋｶﾞｵｶ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0
01101,"060  ","0600041","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾋｶﾞｼ","北海道","札幌市中央区","大通東",0,0,1,0,0,0
...

上記の郵便番号データの構造に合わせてmytableテーブルを定義します:

mysql>

-- テーブルの作成
CREATE TABLE `mytable` (
 `jis` CHAR(5) NOT NULL,
 `zipOld` CHAR(5) NOT NULL,
 `zip` CHAR(7) NOT NULL,
 `prefKana` VARCHAR(255) NOT NULL,
 `cityKana` VARCHAR(255) NOT NULL,
 `streetKana` VARCHAR(255) NOT NULL,
 `pref` VARCHAR(255) NOT NULL,
 `city` VARCHAR(255) NOT NULL,
 `street` VARCHAR(255) NOT NULL,
 `flg1` TINYINT,
 `flg2` TINYINT,
 `flg3` TINYINT,
 `flg4` TINYINT,
 `flg5` TINYINT,
 `flg6` TINYINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CSVファイルからデータインポート

先ほどの郵便番号データCSVファイルをmytableテーブルにインポートしてみましょう:

mysql>

-- KEN_ALL.CSVの文字コードはSJISにセット:
set character_set_database=sjis;

-- CSVからインポート:
LOAD DATA LOCAL INFILE '~/ダウンロード/KEN_ALL.CSV' INTO TABLE `mytable` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

-- 2.62秒で120180レコードを挿入完了!
Query OK, 120180 rows affected, 4149 warnings (2.62 sec)
Records: 124329  Deleted: 0  Skipped: 4149  Warnings: 4149

-- 挿入されたレコード数を確認
SELECT COUNT(0) FROM mytable;
+----------+
| COUNT(0) |
+----------+
|   120180 |
+----------+
1 row in set (0.02 sec)

インデックスの作成

mytableテーブルにインデックスを張り、検索を効率化します:

mysql>

-- インデックスの作成
ALTER TABLE `mytable` ADD UNIQUE `uk_zip` (`zip`); -- 郵便番号はユニークインデックス
ALTER TABLE `mytable` ADD INDEX `ik_prefKana` (`prefKana`); -- 都道府県ｶﾅにインデックス
ALTER TABLE `mytable` ADD INDEX `ik_pref` (`pref`); -- 都道府県にインデックス
ALTER TABLE `mytable` ADD INDEX `ik_prefKana_cityKana` (`prefKana`,`cityKana`); -- 都道府県ｶﾅと市区町村ｶﾅにインデックス
ALTER TABLE `mytable` ADD INDEX `ik_pref_city` (`pref`,`city`); -- 都道府県と市区町村にインデックス

インデックスの有効性調査

explain をSQLの先頭に付ければ、インデックスが利用されているかを確認できます。explain結果のpossible_keysに利用インデックス候補がいくつか挙がっていればインデックスが使える状態を意味します。実際にインデックスを張る前と張った後でどのように検索効率が変化するか見てみましょう。

インデックスを張る前:

mysql>

explain SELECT * FROM mytable WHERE prefKana='ﾎｯｶｲﾄﾞｳ' AND cityKana='ｻｯﾎﾟﾛｼｷﾀｸ';

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 126027 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

インデックスを張った後:

mysql>

explain SELECT * FROM mytable WHERE prefKana='ﾎｯｶｲﾄﾞｳ' AND cityKana='ｻｯﾎﾟﾛｼｷﾀｸ';

+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                    | key                  | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | mytable | NULL       | ref  | ik_prefKana,ik_prefKana_cityKana | ik_prefKana_cityKana | 2046    | const,const |  143 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

インデックスを張る前は126027件に検索を掛けており、とても非効率でした。しかしインデックスを張った後ではik_prefKana_cityKanaインデックスを利用し、143件の中から効率的に検索出来ています。

テーブルの中身を削除

mytableテーブルの中身を削除します。(構造はそのまま、データだけを削除します。もしAUTO_INCREMENTなカラムが設定されている場合は、その値もリセットされます):

mysql> 

TRUNCATE `mytable`;

テーブル自体を削除

mytableテーブル自体を削除します:

mysql> 

DROP TABLE `mytable`;

検索

検索結果のページング

大量の検索結果をページングしたい時は \P less を打った後、SELECTコマンド等を叩きます: (この場合はlessコマンドを使ってページングします)

mysql> \P less 
PAGER set to 'less'
mysql> SELECT * FROM hugetable;

検索結果をJSONで取得

mytableテーブルにaaaとbbbという2つのカラムが存在するとする:

mysql> SELECT JSON_ARRAYAGG(JSON_OBJECT('aaa', `aaa`, 'bbb', `bbb`)) from `mytable`;

テーブルデータのコピー

次の例では、mytableテーブルのクローンテーブル mytable_tmp を作り、特定レコード (条件: prefKana='ﾎｯｶｲﾄﾞｳ' AND cityKana='ｻｯﾎﾟﾛｼｷﾀｸ') をクローンテーブルへコピーします:

mysql>

-- クローンテーブルを作成:
CREATE TABLE IF NOT EXISTS mytable_tmp LIKE mytable;
-- prefKana='ﾎｯｶｲﾄﾞｳ' AND cityKana='ｻｯﾎﾟﾛｼｷﾀｸ' なデータをクローンテーブルへコピーします:
INSERT INTO mytable_tmp SELECT * FROM mytable WHERE prefKana='ﾎｯｶｲﾄﾞｳ' AND cityKana='ｻｯﾎﾟﾛｼｷﾀｸ';

テーブルのリネーム

mysql>

-- mytable_tmpテーブルをmytableにリネーム:
RENAME TABLE mytable_tmp TO mytable;

バックアップとリストア

バックアップ

通常版

# 全テーブルバックアップ:
$ mysqldump -u dbuser -p mydb > mydb.dump.sql
# 特定テーブルのみバックアップ:
$ mysqldump -u dbuser -p mydb -c --skip-extended-insert mytable > mydb.mytable.dump.sql

gzip圧縮版

# 全テーブルバックアップ:
$ mysqldump -u dbuser -p mydb | gzip > mydb.dump.sql.gz
# 特定テーブルのみバックアップ:
$ mysqldump -u dbuser -p mydb -c --skip-extended-insert mytable | gzip > mydb.mytable.dump.sql.gz

リストア

通常版

# 全テーブルリストア:
$ mysql -u dbuser -p mydb < mydb.dump.sql
# 特定テーブルのみリストア:
$ mysql -u dbuser -p mydb < mydb.mytable.dump.sql

gzip圧縮版

# 全テーブルリストア:
$ zcat mydb.dump.sql.gz | mysql -u dbuser -p mydb 
# 特定テーブルのみリストア:
$ zcat mydb.mytable.dump.sql.gz | mysql -u dbuser -p mydb

使用ディスク容量

各テーブルの使用ディスク容量を確認

mydbデータベースの各テーブルのディスク使用量を確認します:

mysql>

SELECT
  table_schema as `Database`,
  table_name AS `Table`,
  round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM 
  information_schema.TABLES 
WHERE
  table_schema = 'mydb' -- DB名を指定
ORDER BY
  (data_length + index_length) DESC;

+----------+---------+------------+
| Database | Table   | Size in MB |
+----------+---------+------------+
| mydb     | mytable |       0.06 | -- mytableテーブルが 0.06MB 使用しています。
+----------+---------+------------+

TIP: WHERE句の箇所を消せば全DBのテーブルサイズを確認できます。

テーブルをコンパクト化

テーブルを最適化することで使用ディスク容量を削減できる場合があります。

mytableを最適化:

mysql>

OPTIMIZE TABLE mytable;

注意: 最適化はあくまでもテーブルの最適化が目的のコマンドなので、テーブルのディスク使用容量が逆に増えてしまうこともあります。

MySQL

mysqlオペレーションメモ

前提

mysqlの導入

mysqlのインストール

mysqlサーバの起動・停止・状態確認

データベースの作成と削除

mysqlクライアントで接続

データベースの作成

データベースの削除

テーブルの作成と削除

テーブルを作成

CSVファイルからデータインポート

インデックスの作成

インデックスの有効性調査

テーブルの中身を削除

テーブル自体を削除

検索

検索結果のページング

検索結果をJSONで取得

テーブルデータのコピー

テーブルのリネーム

バックアップとリストア

バックアップ

通常版

gzip圧縮版

リストア

通常版

gzip圧縮版

使用ディスク容量

各テーブルの使用ディスク容量を確認

テーブルをコンパクト化