SQL#5 データベースのサイズ管理と構造確認

はじめに
学習の概要
1. 含まれる操作
2. 課題
実践
テストデータの準備と実践
課題
最終課題
まとめ

はじめに

こんにちは、シルです。
SQLの学習にあたり、学んだことメモしておきます。
学習にお役立てください。

前回までの内容です
SQL#1 環境準備と基本操作
 SQL#2 テーブルとデータベースの管理
 SQL#3 ユーザー管理と権限の設定
 SQL#4 サーバーステータスとインデックス管理

学習の概要

このパートでは、データベースやテーブルのサイズ確認、テーブルやデータベースの作成文の表示を学びます。
これにより、データベースの容量を管理し、構造を把握することで、バックアップや再作成のための準備ができるようになります。
学んだ内容を実践しながら、システム全体の管理能力を高めましょう。

含まれる操作

SQL操作は以下の通り

1. データベースやテーブルのサイズ確認

2. SHOW CREATE DATABASE – データベース作成文を表示

3. SHOW CREATE TABLE – テーブル作成文を表示

課題

各テーブルのサイズを確認し、どのテーブルが大きな容量を使用しているかを把握する。
テーブルの作成文を確認し、バックアップや再作成用のSQL文を取得する。

実践

1. データベースやテーブルのサイズ確認

データベースやテーブルのサイズを定期的に確認することで、どのテーブルが大きな容量を使用しているかを把握できます。
これにより、最適なストレージ管理が可能になります。

以下のクエリで、各データベースのサイズを確認します。

SELECT table_schema AS 'Database', 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.tables 
GROUP BY table_schema;

このクエリは、information_schema からデータベースごとのサイズをMB単位で表示します。

テーブルごとのサイズ確認

列番号で指定する方法
ORDER BY で出力列の番号を指定します。
ROUND((data_length + index_length) / 1024 / 1024, 2) は 2 番目の列なので、以下のように書きます。

SELECT table_name AS 'Table', 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
WHERE table_schema = 'EmployeeDB' 
ORDER BY 2 DESC;

サブクエリを使用する方法
計算結果をサブクエリでラップしてから、ORDER BY を使います。

SELECT * 
FROM (
    SELECT table_name AS 'Table', 
           ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
    FROM information_schema.tables 
    WHERE table_schema = 'EmployeeDB'
) AS subquery
ORDER BY `Size (MB)` DESC;

これにより、EmployeeDB 内の各テーブルのサイズが表示されます。

2. SHOW CREATE DATABASE – データベース作成文を表示

作成文を確認することで、バックアップや再作成が必要な場合に役立ちます。

SHOW CREATE DATABASE EmployeeDB;

このコマンドは、EmployeeDB の作成文を表示します。

3. SHOW CREATE TABLE – テーブル作成文を表示

SHOW CREATE TABLE Employees;

このコマンドで、Employees テーブルの完全な作成文を取得できます。

テストデータの準備と実践

実際にテーブルのサイズを確認し、構造を把握してみましょう。

テーブルのサイズ確認
まず、既存のテーブルのサイズを確認します。
どのテーブルが最も大きな容量を使用しているかを把握しましょう。

テーブル作成文の取得
必要なテーブルの作成文を取得し、バックアップとして保存します。

課題

課題1
各テーブルのサイズを確認し、最も大きな容量を使用しているテーブルを特定してください。
ヒント：information_schema.tables を使用。

課題2
任意のテーブルの作成文を取得し、再作成のためのSQLを準備してください。
ヒント：SHOW CREATE TABLE を使用。

最終課題

ここまで学んだ内容を総復習し、完全なデータベース環境を構築してみましょう。

手順

1. 新しいデータベースを作成し、いくつかのテーブルを追加します。

2. ユーザーを作成し、特定のデータベースに対する権限を付与します。

3. インデックスを使ってパフォーマンスを最適化します。

4. 必要に応じて、テーブル名を変更したり、サイズを確認します。

課題

これまでの課題を復習し、応用問題を解いて理解を深めましょう。

まとめ

この記事では、データベースやテーブルのサイズ確認、作成文の取得方法を学びました。
これらの操作を使いこなすことで、システム全体の管理がしやすくなります。
次は、さらに高度な操作や実践的な課題に挑戦し、学んだ内容を応用していきましょう。