dbまとめ

[soudai]

やること

• [ ] テーブル一覧
  • カラム名
  • 件数まとめ
  • INDEXの有無

ベンチマークを実行後

• 各テーブルのレコードの増減の確認

[soudai]

MySQLのことしか考えてない。 DBのサイズ、レコードを抽出する

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;

[soudai]

スロークエリを全部だす

slow_query_log                = 1
slow_query_log_file           = /var/lib/mysql/mysqld-slow.log
long_query_time               = 0
log-queries-not-using-indexes = 1

[kamipo]

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

mysql> select count(*) from categories;
+----------+
| count(*) |
+----------+
|       43 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from configs;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from items;
+----------+
| count(*) |
+----------+
|    50035 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from shippings;
+----------+
| count(*) |
+----------+
|    15037 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from transaction_evidences;
+----------+
| count(*) |
+----------+
|    15037 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|     4000 |
+----------+
1 row in set (0.00 sec)

[soudai]

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 |      43806 |           2361 |   101 |   98 |    2 |
| transaction_evidences | InnoDB |      13195 |           2349 |    29 |   29 |    0 |
| shippings             | InnoDB |      14848 |            318 |     4 |    4 |    0 |
| users                 | InnoDB |       3993 |            398 |     1 |    1 |    0 |
| configs               | InnoDB |          2 |           8192 |     0 |    0 |    0 |
| categories            | InnoDB |         43 |            381 |     0 |    0 |    0 |
+-----------------------+--------+------------+----------------+-------+------+------+
6 rows in set (0.00 sec)