分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每行记录所占用空间) 很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间 这里共享一个脚本 CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR ( 500 ) , rowsinfo BIGINT , reserved
分享一个SQLSERVER脚本(计算数据库中各个表的数据量和每行记录所占用空间)
很多时候我们都需要计算数据库中各个表的数据量和每行记录所占用空间
这里共享一个脚本
<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo ( nameinfo </span><span>VARCHAR</span>(<span><strong>500</strong></span><span>) , rowsinfo </span><span>BIGINT</span><span> , reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ) </span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>); </span><span>DECLARE</span> Info_cursor <span>CURSOR</span> <span>FOR</span> <span>SELECT</span> <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span> <span>FROM</span><span> sys.tables </span><span>WHERE</span> type <span>=</span> <span>'</span><span>U</span><span>'</span><span>; </span><span>OPEN</span><span> Info_cursor </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span> <span>WHILE</span> <span><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></span> <span>BEGIN</span> <span>INSERT</span> <span>INTO</span><span> #tablespaceinfo </span><span>EXEC</span> sp_spaceused <span>@tablename</span> <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor </span><span>INTO</span> <span>@tablename</span> <span>END</span> <span>CLOSE</span><span> Info_cursor </span><span>DEALLOCATE</span><span> Info_cursor </span><span>--</span><span>创建临时表</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( TableName </span><span>VARCHAR</span>(<span><strong>50</strong></span><span>) , DataInfo </span><span>BIGINT</span><span> , RowsInfo </span><span>BIGINT</span><span> , Spaceperrow </span><span>AS</span> ( <span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> DataInfo <span>/</span><span> RowsInfo </span><span>END</span><span> ) PERSISTED ) </span><span>--</span><span>插入数据到临时表</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( </span><span>[</span><span>TableName</span><span>]</span><span> , </span><span>[</span><span>DataInfo</span><span>]</span><span> , </span><span>[</span><span>RowsInfo</span><span>]</span><span> ) </span><span>SELECT</span> <span>[</span><span>nameinfo</span><span>]</span><span> , </span><span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>datainfo</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) <span>AS</span> <span>'</span><span>datainfo</span><span>'</span><span> , </span><span>[</span><span>rowsinfo</span><span>]</span> <span>FROM</span><span> #tablespaceinfo </span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>--</span><span>汇总记录</span> <span>SELECT</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>*</span><span> , </span><span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>Spaceperrow</span><span>]</span> <span>AS</span> <span>'</span><span>每行记录大概占用空间(KB)</span><span>'</span> <span>FROM</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>AS</span><span> tbspinfo , </span><span>[</span><span>#tmptb</span><span>]</span> <span>AS</span><span> tmptb </span><span>WHERE</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>nameinfo</span><span>]</span> <span>=</span> <span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>TableName</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>reserved</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span>
注意:使用之前要计算哪个数据库的记录,请先USE一下要统计表记录数的那个数据库!!
工作中遇到的问题
可以说我在实际的工作中 ,在100个问题中有90个都会先用到这个脚本
这里举一个我本人工作中遇到的一些问题
问题一:
程序员反映数据库查询慢,5分钟还没有出结果
我先用这个脚本看一下这个表有多少记录,大概有1000w+条数据
然后在本地的SSMS里查询,确实也是大概4分钟的样子才出来数据,看一下执行计划,发现查询能使用到索引
看一下数据库的压力,并不是很大,我跟会不会跟数据量有关系呢?
程序员要查询的结果条数是500条数据,业务表是做了分区的,按道理应该不会慢成这样。。。
后来我再看一下共享出来的那个脚本的结果,发现查询的结果大小=每行记录的大小*记录数
要查询大概500MB的数据,再传到客户端,不慢才怪
为什麽查询出的结果这么大?
主要是有几个大字段:例如:二进制字段和NVARCHAR(MAX)
并且时间范围跨度比较大
马上叫程序员改一下查询的语句,由于是entity framework程序,怎麽改我就不太清楚了,主要是不必要的字段就不查询处理并且缩小时间范围
问题二:
还有一些问题也需要知道每行记录的大小,例如删除表的历史数据,QA说要保留2013年之前的数据,你需要查出保留的数据或者2013年之前的数据占用多少G空间
再结合当前服务器的磁盘可用空间,来评估删除的数据是否太多或者太少
那么流程是:先查出2013年之前的记录数有多少-》计算表的总记录数-》计算表的大小-》手工计算每行记录的大小-》乘以2013年之前的记录数
如果没有每行记录数这个字段,那么你手工计算,是不是效率就变慢了???
问题三:
导数据的时候,你想知道当前已经导了多少数据了,那么执行一下这个脚本就可以了,这个脚本基本不会被阻塞
很快就能查出结果
脚本的计算方法
方法一
实际上利用的就是数据行大小的信息除以记录数
<span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> DataInfo <span>/</span> RowsInfo
方法二
<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>
说一下两种方法的区别
第一种方法是效率高,当表有上亿条记录的时候,如果你使用第二种方法执行AVG(DATALENGTH(C0))是很慢的,因为SQLSERVER要统计字段大小信息
可能十几分钟都出不来结果
当然,第一种方法也有一些缺陷,就是当表的记录数少的时候,统计出来的每行记录占用空间是不准确的
因为datainfo这个值是以数据页大小为单位的,因为就算表只有一条记录,那么也会占用一个数据页(8KB)
那么当8KB/1 =8KB,一条记录肯定不会是8KB大小的,所以记录少的时候会不准确
但是当记录数很多的时候,就准确了
看一下TB106这个表统计出来的结果值
<span>SELECT</span> <span>AVG</span>(<span>DATALENGTH</span>(C0))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C1))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C2))<span>+</span><span>AVG</span>(<span>DATALENGTH</span>(C3)) <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>TB106</span><span>]</span>
可以看到是比较准确的
注意:
无论方法一还是方法二都不包括索引所占用的空间 !!
总结
大家平时一定会想:究竟DBA有什么作用?
在这里就给大家一个例子了,在工作中,程序员是不会关心他要查询的数据的大小的,他不管三七二十一只要把数据select出来就行了,然后收工
DBA这里就要解决数据查询不出来的问题,一般的程序员觉得查询500条数据是很少的,根本不会关心表设计,表的字段的数据类型
当工作越来越多,开发任务越来越重的时候更是这样
所以本人觉得DBA这个角色还是比较重要的o(∩_∩)o
如有不对的地方,欢迎大家拍砖o(∩_∩)o
2014-7-7 脚本bug修复
由于算出来每行记录的精度有问题,我又对脚本的精度进行了改进
<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo ( nameinfo </span><span>VARCHAR</span>(<span><strong>500</strong></span><span>) , rowsinfo </span><span>BIGINT</span><span> , reserved </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , datainfo </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , index_size </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) , unused </span><span>VARCHAR</span>(<span><strong>20</strong></span><span>) ) </span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span><strong>255</strong></span><span>); </span><span>DECLARE</span> Info_cursor <span>CURSOR</span> <span>FOR</span> <span>SELECT</span> <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span> <span>FROM</span><span> sys.tables </span><span>WHERE</span> type <span>=</span> <span>'</span><span>U</span><span>'</span><span>; </span><span>OPEN</span><span> Info_cursor </span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span> <span>WHILE</span> <span><strong>@@FETCH_STATUS</strong></span> <span>=</span> <span><strong>0</strong></span> <span>BEGIN</span> <span>INSERT</span> <span>INTO</span><span> #tablespaceinfo </span><span>EXEC</span> sp_spaceused <span>@tablename</span> <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor </span><span>INTO</span> <span>@tablename</span> <span>END</span> <span>CLOSE</span><span> Info_cursor </span><span>DEALLOCATE</span><span> Info_cursor </span><span>--</span><span>创建临时表</span> <span>CREATE</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( TableName </span><span>VARCHAR</span>(<span><strong>50</strong></span><span>) , DataInfo </span><span>BIGINT</span><span> , RowsInfo </span><span>BIGINT</span><span> , Spaceperrow </span><span>AS</span> ( <span>CASE</span><span> RowsInfo </span><span>WHEN</span> <span><strong>0</strong></span> <span>THEN</span> <span><strong>0</strong></span> <span>ELSE</span> <span>CAST</span>(DataInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span>))<span>/</span><span>CAST</span>(RowsInfo <span>AS</span> <span>decimal</span>(<span><strong>18</strong></span>,<span><strong>2</strong></span><span>)) </span><span>END</span><span> ) PERSISTED ) </span><span>--</span><span>插入数据到临时表</span> <span>INSERT</span> <span>INTO</span> <span>[</span><span>#tmptb</span><span>]</span><span> ( </span><span>[</span><span>TableName</span><span>]</span><span> , </span><span>[</span><span>DataInfo</span><span>]</span><span> , </span><span>[</span><span>RowsInfo</span><span>]</span><span> ) </span><span>SELECT</span> <span>[</span><span>nameinfo</span><span>]</span><span> , </span><span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>datainfo</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) <span>AS</span> <span>'</span><span>datainfo</span><span>'</span><span> , </span><span>[</span><span>rowsinfo</span><span>]</span> <span>FROM</span><span> #tablespaceinfo </span><span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>--</span><span>汇总记录</span> <span>SELECT</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>*</span><span> , </span><span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>Spaceperrow</span><span>]</span> <span>AS</span> <span>'</span><span>每行记录大概占用空间(KB)</span><span>'</span> <span>FROM</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>AS</span><span> tbspinfo , </span><span>[</span><span>#tmptb</span><span>]</span> <span>AS</span><span> tmptb </span><span>WHERE</span> <span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>nameinfo</span><span>]</span> <span>=</span> <span>[</span><span>tmptb</span><span>]</span>.<span>[</span><span>TableName</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>CAST</span>(<span>REPLACE</span>(<span>[</span><span>tbspinfo</span><span>]</span>.<span>[</span><span>reserved</span><span>]</span>, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>INT</span>) <span>DESC</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span> <span>DROP</span> <span>TABLE</span> <span>[</span><span>#tmptb</span><span>]</span>

Innodbbufferpoolは、データをキャッシュしてページをインデックス作成することにより、ディスクI/Oを削減し、データベースのパフォーマンスを改善します。その作業原則には次のものが含まれます。1。データ読み取り:Bufferpoolのデータを読む。 2。データの書き込み:データを変更した後、bufferpoolに書き込み、定期的にディスクに更新します。 3.キャッシュ管理:LRUアルゴリズムを使用して、キャッシュページを管理します。 4.読みメカニズム:隣接するデータページを事前にロードします。 BufferPoolのサイジングと複数のインスタンスを使用することにより、データベースのパフォーマンスを最適化できます。

他のプログラミング言語と比較して、MySQLは主にデータの保存と管理に使用されますが、Python、Java、Cなどの他の言語は論理処理とアプリケーション開発に使用されます。 MySQLは、データ管理のニーズに適した高性能、スケーラビリティ、およびクロスプラットフォームサポートで知られていますが、他の言語は、データ分析、エンタープライズアプリケーション、システムプログラミングなどのそれぞれの分野で利点があります。

MySQLは、データストレージ、管理、分析に適した強力なオープンソースデータベース管理システムであるため、学習する価値があります。 1)MySQLは、SQLを使用してデータを操作するリレーショナルデータベースであり、構造化されたデータ管理に適しています。 2)SQL言語はMySQLと対話するための鍵であり、CRUD操作をサポートします。 3)MySQLの作業原則には、クライアント/サーバーアーキテクチャ、ストレージエンジン、クエリオプティマイザーが含まれます。 4)基本的な使用には、データベースとテーブルの作成が含まれ、高度な使用にはJoinを使用してテーブルの参加が含まれます。 5)一般的なエラーには、構文エラーと許可の問題が含まれ、デバッグスキルには、構文のチェックと説明コマンドの使用が含まれます。 6)パフォーマンスの最適化には、インデックスの使用、SQLステートメントの最適化、およびデータベースの定期的なメンテナンスが含まれます。

MySQLは、初心者がデータベーススキルを学ぶのに適しています。 1.MySQLサーバーとクライアントツールをインストールします。 2。selectなどの基本的なSQLクエリを理解します。 3。マスターデータ操作:テーブルを作成し、データを挿入、更新、削除します。 4.高度なスキルを学ぶ:サブクエリとウィンドウの関数。 5。デバッグと最適化:構文を確認し、インデックスを使用し、選択*を避け、制限を使用します。

MySQLは、テーブル構造とSQLクエリを介して構造化されたデータを効率的に管理し、外部キーを介してテーブル間関係を実装します。 1.テーブルを作成するときにデータ形式と入力を定義します。 2。外部キーを使用して、テーブル間の関係を確立します。 3。インデックス作成とクエリの最適化により、パフォーマンスを改善します。 4.データベースを定期的にバックアップおよび監視して、データのセキュリティとパフォーマンスの最適化を確保します。

MySQLは、Web開発で広く使用されているオープンソースリレーショナルデータベース管理システムです。その重要な機能には、次のものが含まれます。1。さまざまなシナリオに適したInnodbやMyisamなどの複数のストレージエンジンをサポートします。 2。ロードバランスとデータバックアップを容易にするために、マスタースレーブレプリケーション機能を提供します。 3.クエリの最適化とインデックスの使用により、クエリ効率を改善します。

SQLは、MySQLデータベースと対話して、データの追加、削除、変更、検査、データベース設計を実現するために使用されます。 1)SQLは、ステートメントの選択、挿入、更新、削除を介してデータ操作を実行します。 2)データベースの設計と管理に作成、変更、ドロップステートメントを使用します。 3)複雑なクエリとデータ分析は、ビジネス上の意思決定効率を改善するためにSQLを通じて実装されます。

MySQLの基本操作には、データベース、テーブルの作成、およびSQLを使用してデータのCRUD操作を実行することが含まれます。 1.データベースの作成:createdatabasemy_first_db; 2。テーブルの作成:createTableBooks(idintauto_incrementprimarykey、titlevarchary(100)notnull、authorvarchar(100)notnull、published_yearint); 3.データの挿入:InsertIntoBooks(タイトル、著者、公開_year)VA


ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

メモ帳++7.3.1
使いやすく無料のコードエディター

SecLists
SecLists は、セキュリティ テスターの究極の相棒です。これは、セキュリティ評価中に頻繁に使用されるさまざまな種類のリストを 1 か所にまとめたものです。 SecLists は、セキュリティ テスターが必要とする可能性のあるすべてのリストを便利に提供することで、セキュリティ テストをより効率的かつ生産的にするのに役立ちます。リストの種類には、ユーザー名、パスワード、URL、ファジング ペイロード、機密データ パターン、Web シェルなどが含まれます。テスターはこのリポジトリを新しいテスト マシンにプルするだけで、必要なあらゆる種類のリストにアクセスできるようになります。

PhpStorm Mac バージョン
最新(2018.2.1)のプロフェッショナル向けPHP統合開発ツール

AtomエディタMac版ダウンロード
最も人気のあるオープンソースエディター

ZendStudio 13.5.1 Mac
強力な PHP 統合開発環境
