データ構造の把握

[giwa]

tableの構成
サイズ

[momotaro98]

About MySQL

mysql> show variables like 'version';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| version | 5.7.23-0ubuntu0.16.04.1 |
+---------------+-------------------------+

DB tables and records

mysql> select table_name, table_rows from information_schema.TABLES where table_schema = 'isubata';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| channel | 100 |
| haveread | 47 |
| image | 997 |
| message | 9446 |
| user | 1006 |
+------------+------------+
5 rows in set (0.00 sec)

DB Schema

mysql> desc channel;
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | text | NO | | NULL | |
| description | mediumtext | YES | | NULL | |
| updated_at | datetime | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
+-------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc haveread;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| user_id | bigint(20) | NO | PRI | NULL | |
| channel_id | bigint(20) | NO | PRI | NULL | |
| message_id | bigint(20) | YES | | NULL | |
| updated_at | datetime | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
+------------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> desc image;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(191) | YES | | NULL | |
| data | longblob | YES | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc message;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| channel_id | bigint(20) | YES | | NULL | |
| user_id | bigint(20) | YES | | NULL | |
| content | text | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
+------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc user;
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(191) | YES | UNI | NULL | |
| salt | varchar(20) | YES | | NULL | |
| password | varchar(40) | YES | | NULL | |
| display_name | text | YES | | NULL | |
| avatar_icon | text | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

DB Index

mysql> show index from channel;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| channel | 0 | PRIMARY | 1 | id | A | 100 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from haveread;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| haveread | 0 | PRIMARY | 1 | user_id | A | 44 | NULL | NULL | | BTREE | | |
| haveread | 0 | PRIMARY | 2 | channel_id | A | 45 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from image;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| image | 0 | PRIMARY | 1 | id | A | 994 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

mysql> show index from message;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| message | 0 | PRIMARY | 1 | id | A | 9445 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 1000 | NULL | NULL | | BTREE | | |
| user | 0 | name | 1 | name | A | 1000 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)