MySQL統計テーブルのサイズを説明する例

MySQL統計テーブルのサイズを説明する例

各データベース内の各テーブルのサイズをカウントすることは、データ ガバナンスの最も単純な要件の 1 つです。この記事では、サンプリング統計結果と正確な統計結果という 2 つの側面から、MySQL 内の各データベースとテーブルのデータ量をカウントします。

1. データ量の統計的推定

MySQL データ ディクショナリ ライブラリ information_schema には、推定データ量 (InnoDB エンジン テーブルの場合は不正確、MyISAM エンジン テーブルの場合は正確) と、データ サイズ、インデックス サイズ、テーブル フラグメント サイズなどの情報が記録されます。

各データベースとテーブルのおおよそのデータ量を知りたい場合は、information_schema.tables を直接クエリして統計情報を取得できます。例えば:

テーブルスキーマ、テーブル名、テーブル行、データ長+インデックス長+データ空き容量、データサイズを選択します。
 information_schema.`TABLES` から table_schema に ('db1','db2') を指定します。

data_sizeの単位はBです

前述のように、統計情報のデータ項目数とサイズは、一部のデータのサンプリング統計に基づいているため、実際のサイズと異なる場合があります。テーブルが大きいほど、違いは顕著になります。各テーブルの実際の状況を知りたい場合は、次の方法を使用する必要があります。

2. 実際のデータ量を数える

各テーブルの実際のサイズを数えたい場合は、各テーブルを走査してレコード数を計算する必要があります。テーブルスペースのサイズを確認することで、各テーブルのサイズを確認できます (各テーブルには独立したテーブルスペースがあります)。正確な統計の目的は、以下の手順に従うことで達成できます。

パスの作成

スクリプトや一時ファイルなどを保存するための作業ディレクトリを作成します。

mkdir -p /usr/local/データサイズ

統計データベースとテーブルを作成する

統計が必要なデータベース インスタンスに統計データベースを作成します。

SQL> データベース bak_db を作成します。

統計用のストアドプロシージャを作成する

SQL> bak_db を使用します。
SQL>プロシージャ `p_db_size`() を作成します
始める
v_id INT を宣言します。
v_maxid INT を宣言します。
v_tbname VARCHAR(50)を宣言します。
v_dbname VARCHAR(50) を宣言します。
v_sql_upd VARCHAR(200) を宣言します。
SET v_id =(SELECT MIN(id) FROM bak_db.tb_size);
SET v_maxid =(SELECT MAX(id) FROM bak_db.tb_size);
v_id <=v_maxid の場合
する
SET v_tbname = (SELECT tbname FROM bak_db.tb_size WHERE id=v_id);
SET v_dbname = (SELECT dbname FROM bak_db.tb_size WHERE id=v_id);
SET v_sql_upd = CONCAT('update bak_db.tb_size set tb_rows=(select count(*) from ',v_dbname,".",v_tbname,"") where id=",v_id);
  SET @v_sql_upd := v_sql_upd;
  @v_sql_upd からステートメントを準備します。
  ステートメントの実行;
  DEALLOCATE PREPARE ステートメント;
  v_id = v_id +1 を設定します。
終了しながら;
  終わり;

スクリプトの作成

vimデータ.sh
/* 次のコンテンツを挿入します */

#!/bin/bash
cd /usr/local/データサイズ
du -s /data/mysql/mysql3306/data/db1/* |grep -v ".frm" |grep -v ".opt" >/usr/local/data_size/data_size     
du -s /data/mysql/mysql3306/data/db2/* |grep -v ".frm" |grep -v ".opt">>/usr/local/data_size/data_size

# 次の4つのステップはSQLにスプライスすることです 
awk '{print "insert into bak_db.tb_size(size,tb_route)values("""$0}' /usr/local/data_size/data_size >/usr/local/data_size/data_size1  
awk '{print $0";"}' /usr/local/data_size/data_size1 >/usr/local/data_size/data_size.sql
sed -i "s#\t#,'#g" /usr/local/data_size/data_size.sql
sed -i "s#;#');#g" /usr/local/data_size/data_size.sql

# 統計テーブルを作成します /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "drop table if exists bak_db.tb_size;CREATE TABLE IF NOT EXISTS bak_db.tb_size ( id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT ,size INT,tb_route VARCHAR(200),tbname VARCHAR(50),dbname VARCHAR(50),tb_rows INT(11));" 

# データをインポート /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use bak_db;truncate table bak_db.tb_size;source /usr/local/data_size/data_size.sql;"

# ライブラリ名とテーブル名を生成します。もちろん、この手順はデータ フィールドから取得することもできます。 /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use bak_db;UPDATE bak_db.tb_size SET tbname=REPLACE(SUBSTRING_INDEX(tb_route,'/',-1),'.ibd','');"
 /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "UPDATE bak_db.tb_size SET dbname=LEFT (SUBSTRING_INDEX(tb_route,'/',-2),INSTR(SUBSTRING_INDEX(tb_route,'/',-2),'/')-1);"
sleep 10 # 前の手順がマスターデータベースにある場合は、後で統計を実行するときにテーブルとコンテンツの取得に失敗しないように、しばらく一時停止することをお勧めします。前の手順がスレーブデータベースにある場合は、この手順は省略できます echo 'start call procedure' 
# ストアド プロシージャを呼び出して、各テーブルのレコード数をカウントします /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use bak_db;call bak_db.p_db_size();"

# テーブルとデータをエクスポートします /usr/local/mysql5.7/bin/mysqldump -uroot -p'Test#123456' -h 192.168.28.132 --single-transaction bak_db tb_size >/usr/local/data_size/tb_size.sql

# テーブルと結果をマスター データベースにインポートします (スレーブ データベースは、一度削除して再構築するのと同じです)
/usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.128 -e "use bak_db;source /usr/local/data_size/tb_size.sql;"

結果は次のとおりです。

正確な値は統計情報の値とは大きく異なっており、表が大きくなるほどその違いは明らかであることがわかります。

ヒント: この記事の正確な統計スクリプトには、最適化の余地がまだたくさんあります。急いで書いたものです。必要に応じて調整してください。私のレベルには限界があるので、遠慮なく訂正してください。ご質問がございましたら、お気軽にご連絡ください。

上記は、MySQL 統計ライブラリ テーブル サイズの詳細を説明する例です。MySQL 統計ライブラリ テーブル サイズの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQL統計の概要
  • 各Mysqlテーブルの行数を正確にカウントする小さなシェルスクリプト
  • MySQLは複数テーブル関連統計(サブクエリ統計)の例を実装します
  • PythonはMySQLデータ統計とNumPy統計関数を実装します
  • 日、時間、分ごとの連続期間データの sqlserver/mysql 統計 [推奨]
  • 一定期間の日ごと、時間ごとの統計データを取得するMySQLの詳しい説明
  • PHP+MySQL で一定期間内に毎日のデータ統計の最適化操作例を実現
  • PHP 広告クリック統計コード (php+mysql)
  • MySQL 8.0 の統計が不正確である理由

<<:  Reactの状態の理解についての簡単な分析

>>:  Dockerを使用してDjango+MySQL8開発環境をデプロイする方法の詳細な説明

推薦する

MySQL でデータ テーブルを作成し、主キーと外部キーの関係を確立する方法の詳細な説明

序文MySQL テーブルの主キーと外部キーを作成するときは、次の点に注意する必要があります。主キーと...

よく理解しましたかタグ 定義方法 使用方法

序文:今日、「<!DOCTYPE> タグを注意深く理解しましたか?」と尋ねられました。私...

ウォーターフォールフローレイアウト(無限読み込み)を実現する js

この記事の例では、ウォーターフォールフローレイアウトを実装するためのjsの具体的なコードを参考までに...

Zabbix を使用して ogg プロセスを監視する (Windows プラットフォーム)

この記事では、Windows プラットフォームで ogg プログラムを監視する方法を紹介します。 (...

コンテンツ領域の周囲を回転する CSS 動的グラデーション ボーダーの効果 (サンプル コード)

レンダリング ネットで関連情報を調べたところ、現在のダイナミックグラデーションボーダーの実装方法のほ...

count(1)、count(*)、count(列名)の実行の違いの詳細な説明

実施効果: 1. count(1) と count(*)テーブル内のデータ量が多い場合、テーブルを分...

vue2 vue3 での Echarts の詳細な使用方法

目次1. インストール2. vue2でEchartsを使用するmain.jsファイル内コンテナが与え...

JS で単一ファイルコンポーネントを実装する方法

目次概要単一ファイルコンポーネント基本概念シンプルなローダーコンポーネントコンテンツの解析コンポーネ...

MySQL の最適化: サブクエリの代わりに結合を使用する

サブクエリの代わりにJOINを使用するMySQL はバージョン 4.1 以降で SQL サブクエリを...

Linux仮想マシンをWiFiに接続する方法

生活の中で、インターネットはどこにでもあります。インターネットを通じてゲームをしたり、テレビ番組を見...

MySQL クロステーブルクエリとクロステーブル更新

SQL の基礎知識がある友人は、「クロステーブル クエリ」について聞いたことがあるはずですが、クロス...

CentOS 6.8 に MySQL 8.0.18 をインストールするチュートリアルの簡単な分析 (RPM 方式)

今日は、CentOS 6.8 サーバーに MySQL 8.0.18 をインストールする方法を記録しま...

Linux seqコマンドの使い方

1. コマンドの紹介seq (シーケンス) コマンドは、指定されたステップ サイズに従って、開始番号...

mysql8 共通テーブル式 CTE 使用例の分析

この記事では、例を使用して、MySQL 8 の共通テーブル式 (CTE) の使用方法を説明します。ご...