テーブルに設定されているインデックス

MySQLにおいて表題の件。昨日の続きである。

■ 環境

  • MySQL 5.64

■ データサイズ

テーブルはやはり下記で良い気がする。

data_length + index_length

となると気になるのは、”index_length“が必要以上に大きいものか。ひとつのインデックスが大きいのか、単にいくつものインデックスが設定されているからなのか、を確認したい。

■ show index

下記でテーブルに設定されているインデックスの一覧を確認可能である。

show index from [table_name];

試しに実行してみると下記のように得られた。

mysql> show index from test1;
+-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 |          0 | PRIMARY                   |            1 | id          | A         |        2801 |     NULL | NULL   |      | BTREE      |         |               |
| test1 |          1 | index_XXXXX_on_XXXXXXXXXX |            1 | XXXXXXXXXX  | A         |         700 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

mysql>

Cardinality“の数が高い方がインデックスとしては有効なんだろうな、とイメージしている。本当にそうであろうか…。ちょっとしっかり考えよう。

以上。

■ 関連

データベースサイズの概算

データベースサイズの概算

MySQLにおいて表題を知りたい。現在どれくらいのサイズになっているのかな?と疑問に思ったので。

■ 環境

  • MySQL 5.6.34

■ information_schema

MySQLに”root“で接続し、”information_schema“データベースを確認する。”TABLES“テーブルに情報がありそうである。

mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)

mysql>

DATA_LENGTH“カラムで判明できそうである。SQLでテーブルごとに取得する。これでデータベースごとにサイズを概算する。

mysql> select table_schema, sum(data_length) from tables group by table_schema;
+-----------------------+------------------+
| table_schema          | sum(data_length) |
+-----------------------+------------------+
| example               |         66027520 |
| information_schema    |                0 |
  :
mysql>

INDEX_LENGTH“も合わせて計算した方が良いのだろうか?試してみる。

mysql> select table_schema, sum(data_length + index_length) from tables group by table_schema;
+-----------------------+---------------------------------+
| table_schema          | sum(data_length + index_length) |
+-----------------------+---------------------------------+
| example               |                        83951616 |
  :

byteでの表記なので1024で順次割っていく。

mysql&gt select table_schema, sum(data_length + index_length) / 1024 as KB from tables group by table_schema;
+-----------------------+--------------+
| table_schema          | KB           |
+-----------------------+--------------+
| example               |   81984.0000 |
  :
mysql&gt select table_schema, sum(data_length + index_length) / 1024 / 1024 as MB from tables group by table_schema;
+-----------------------+---------------+
| table_schema          | MB            |
+-----------------------+---------------+
| example               |   80.06250000 |
  :

欲しい情報としてはこんな感じで十分だ。

以上。

phpMyAdmin

表題を久しぶりに準備したのでメモ。

■ 環境

  • phpMyAdmin 4.7.8
  • php 7.0.27
  • Apache httpd 2.4.27
  • Amazon Linux

■ phpMyAdmin

phpMyAdmin
https://www.phpmyadmin.net/

上記ページより、”4.7.8“をダウンロードし展開した。

展開したディレクトリを念のためランダムな文字列に変更し、DocuemntRoot配下に配置した。ちなみにこのディレクトリへのアクセスは接続元IPを絞ることにしている。

  <Directory "/var/www/html/daieuk11rq/">
    Order Deny,Allow
    Allow from XX.XX.XX.XX1
    Allow from XX.XX.XX.XX2
    Deny from all
  </Directory>

DBへの接続情報を持ったファイルを作成する。sampleがあるのでコピーして利用する。

$ cp config.sample.inc.php config.inc.php

下記行の箇所を編集する。接続先はRDSなので書いてしまう。

$cfg['Servers'][$i]['host'] = 'XXXXXXXXXXXXXX.YYYYYYYYYYYY.ap-northeast-1.rds.amazonaws.com';

あとはブラウザから接続し、DBへのログイン情報でアクセスすれば問題ない。

不要になった際には移動するなり削除するなりしておく。

以上。

datetime型をunixtimeに変換

MySQLにおいて表題の通り。以前はunixtime(エポック秒)を人が理解しやすい表記に変換したが今回は逆である。

■ 環境

  • MySQL 5.6.35

■ unix_timestamp

datetime型のカラムが存在する。これをunixtimeで取得するには下記のように”unix_timestamp()“を使用すれば良い。

mysql> desc users;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name                   | varchar(255) | YES  |     | NULL    |                |
  :
| created_at             | datetime     | NO   |     | NULL    |                |
| updated_at             | datetime     | NO   |     | NULL    |                |
  :

mysql>

ここから”created_at“をunixtimeで取得したい。

mysql> select id, name, unix_timestamp(created_at) from users;
+----+------------+------------+
| id | name       | created_at |
+----+------------+------------+
|  1 | test       | 1512623164 |
|  2 | test       | 1512975404 |
  :
mysql>

以上。

■ 関連

unixtimeをSQLで変換

mysqldump: Got errno 32 on write

表題のエラーが出た。

■ 環境

  • MySQL 5.6.34
  • Linux

■ mysqldump

データをバックアップしようとして下記のようなコマンドで実行しようとした。

$ mysqldump -uXXXXX -pXXXXX -hXXXXXXXX database | gzip > backup/$(date '+%Y%m%d').dump.gz

このように実行しようとしたところ、表題のエラーメッセージが出力された。あれ?と思ったが単に出力先のディレクトリを作成していないだけであった…。”backup“ディレクトリに出力しようとしていたのを忘れていたようだ。

ディレクトリに出力するのであれば、事前にちゃんと作成しておこう。またそのディレクトリに書き込み権限があるかも確認しておこう。

以上。