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`;

テーブルデータのコピー

次の例では、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 -t -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 -t -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のテーブルサイズを確認できます。

田中ソフトウェアラボ @TanakaSoftwareLab
田中ソフトウェアラボ公式アカウント。当アカウントでは、「芽萌丸」の運用情報や田中ソフトウェアラボからのお知らせを発信いたします。仕事のご相談は Chatwork ID: akirattii まで。