DB schema

[euglena1215]

mysqlチートシート

• ログイン いつもと同じで↓ mysql -uroot

root@isucon9q-1:~# systemctl list-units | grep mysql
mysql.service                                                               loaded active running   MySQL Community Server

• 基本的な操作

  show databases;
  use isutomo;
  desc tweets;desc users;

• インデックスの確認

  show index from tweets;show index from users;

• 容量の確認

  SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;

• スロークエリログの設定

  show variables like "%slow%"

Indexの貼り方

CREATE INDEX post_id_index ON comments(post_id)

[ykamez]

mysql> show tables;
+-----------------------+
| Tables_in_isucari     |
+-----------------------+
| categories            |
| configs               |
| items                 |
| shippings             |
| transaction_evidences |
| users                 |
+-----------------------+
6 rows in set (0.00 sec)

[ykamez]

テーブル一覧

• catogories

  mysql> desc categories;desc configs;desc items;desc shippings;desc transaction_evidences; desc users;
  +---------------+------------------+------+-----+---------+----------------+
  | Field         | Type             | Null | Key | Default | Extra          |
  +---------------+------------------+------+-----+---------+----------------+
  | id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  | parent_id     | int(10) unsigned | NO   |     | NULL    |                |
  | category_name | varchar(191)     | NO   |     | NULL    |                |
  +---------------+------------------+------+-----+---------+----------------+
  3 rows in set (0.00 sec)

• configs

  
  +-------+--------------+------+-----+---------+-------+
  | Field | Type         | Null | Key | Default | Extra |
  +-------+--------------+------+-----+---------+-------+
  | name  | varchar(191) | NO   | PRI | NULL    |       |
  | val   | varchar(255) | NO   |     | NULL    |       |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

- items

+-------------+------------------------------------------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------------------------------------------+------+-----+-------------------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | seller_id | bigint(20) | NO | | NULL | | | buyer_id | bigint(20) | NO | | 0 | | | status | enum('on_sale','trading','sold_out','stop','cancel') | NO | | NULL | | | name | varchar(191) | NO | | NULL | | | price | int(10) unsigned | NO | | NULL | | | description | text | NO | | NULL | | | image_name | varchar(191) | NO | | NULL | | | category_id | int(10) unsigned | NO | MUL | NULL | | | created_at | datetime | NO | | CURRENT_TIMESTAMP | | | updated_at | datetime | NO | | CURRENT_TIMESTAMP | | +-------------+------------------------------------------------------+------+-----+-------------------+----------------+ 11 rows in set (0.00 sec)

- shippings

+-------------------------+-------------------------------------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+-------------------------------------------------+------+-----+-------------------+-------+ | transaction_evidence_id | bigint(20) | NO | PRI | NULL | | | status | enum('initial','wait_pickup','shipping','done') | NO | | NULL | | | item_name | varchar(191) | NO | | NULL | | | item_id | bigint(20) | NO | | NULL | | | reserve_id | varchar(191) | NO | | NULL | | | reserve_time | bigint(20) | NO | | NULL | | | to_address | varchar(191) | NO | | NULL | | | to_name | varchar(191) | NO | | NULL | | | from_address | varchar(191) | NO | | NULL | | | from_name | varchar(191) | NO | | NULL | | | img_binary | mediumblob | NO | | NULL | | | created_at | datetime | NO | | CURRENT_TIMESTAMP | | | updated_at | datetime | NO | | CURRENT_TIMESTAMP | | +-------------------------+-------------------------------------------------+------+-----+-------------------+-------+ 13 rows in set (0.00 sec)

- transaction_evidences

+-----------------------+------------------------------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+------------------------------------------+------+-----+-------------------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | seller_id | bigint(20) | NO | | NULL | | | buyer_id | bigint(20) | NO | | NULL | | | status | enum('wait_shipping','wait_done','done') | NO | | NULL | | | item_id | bigint(20) | NO | UNI | NULL | | | item_name | varchar(191) | NO | | NULL | | | item_price | int(10) unsigned | NO | | NULL | | | item_description | text | NO | | NULL | | | item_category_id | int(10) unsigned | NO | | NULL | | | item_root_category_id | int(10) unsigned | NO | | NULL | | | created_at | datetime | NO | | CURRENT_TIMESTAMP | | | updated_at | datetime | NO | | CURRENT_TIMESTAMP | | +-----------------------+------------------------------------------+------+-----+-------------------+----------------+ 12 rows in set (0.00 sec)

- users

+-----------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------------------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | account_name | varchar(128) | NO | UNI | NULL | | | hashed_password | varbinary(191) | NO | | NULL | | | address | varchar(191) | NO | | NULL | | | num_sell_items | int(10) unsigned | NO | | 0 | | | last_bump | datetime | NO | | 2000-01-01 00:00:00 | | | created_at | datetime | NO | | CURRENT_TIMESTAMP | | +-----------------+------------------+------+-----+---------------------+----------------+ 7 rows in set (0.00 sec)

[ykamez]

index

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

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| configs |          0 | PRIMARY  |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from items;show index from shippings;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| items |          0 | PRIMARY         |            1 | id          | A         |       43764 |     NULL | NULL   |      | BTREE      |         |               |
| items |          1 | idx_category_id |            1 | category_id | A         |          36 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

+-----------+------------+----------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name             | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shippings |          0 | PRIMARY  |            1 | transaction_evidence_id | A         |       14862 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

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

+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY      |            1 | id           | A         |        3786 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | account_name |            1 | account_name | A         |        3786 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

[ykamez]

容量

mysql> SELECT table_name, engine, table_rows, avg_row_length, floor((data_length+index_length)/1024/1024) as allMB, floor((data_length)/1024/1024) as dMB, floor((index_length)/1024/1024) as iMB FROM information_schema.tables WHERE table_schema=database() ORDER BY (data_length+index_length) DESC;
+-----------------------+--------+------------+----------------+-------+------+------+
| table_name            | engine | table_rows | avg_row_length | allMB | dMB  | iMB  |
+-----------------------+--------+------------+----------------+-------+------+------+
| items                 | InnoDB |      43764 |           2363 |   101 |   98 |    2 |
| transaction_evidences | InnoDB |      13139 |           2359 |    29 |   29 |    0 |
| shippings             | InnoDB |      14862 |            318 |     4 |    4 |    0 |
| users                 | InnoDB |       3786 |            419 |     1 |    1 |    0 |
| configs               | InnoDB |          0 |              0 |     0 |    0 |    0 |
| categories            | InnoDB |         43 |            381 |     0 |    0 |    0 |
+-----------------------+--------+------------+----------------+-------+------+------+
6 rows in set (0.00 sec)