分享一个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>

データベースとプログラミングにおけるMySQLの位置は非常に重要です。これは、さまざまなアプリケーションシナリオで広く使用されているオープンソースのリレーショナルデータベース管理システムです。 1)MySQLは、効率的なデータストレージ、組織、および検索機能を提供し、Web、モバイル、およびエンタープライズレベルのシステムをサポートします。 2)クライアントサーバーアーキテクチャを使用し、複数のストレージエンジンとインデックスの最適化をサポートします。 3)基本的な使用には、テーブルの作成とデータの挿入が含まれ、高度な使用法にはマルチテーブル結合と複雑なクエリが含まれます。 4)SQL構文エラーやパフォーマンスの問題などのよくある質問は、説明コマンドとスロークエリログを介してデバッグできます。 5)パフォーマンス最適化方法には、インデックスの合理的な使用、最適化されたクエリ、およびキャッシュの使用が含まれます。ベストプラクティスには、トランザクションと準備された星の使用が含まれます

MySQLは、中小企業に適しています。 1)中小企業は、顧客情報の保存など、基本的なデータ管理にMySQLを使用できます。 2)大企業はMySQLを使用して、大規模なデータと複雑なビジネスロジックを処理して、クエリのパフォーマンスとトランザクション処理を最適化できます。

INNODBは、次のキーロックメカニズムを通じてファントムの読み取りを効果的に防止します。 1)Next-KeyLockingは、Row LockとGap Lockを組み合わせてレコードとギャップをロックして、新しいレコードが挿入されないようにします。 2)実際のアプリケーションでは、クエリを最適化して分離レベルを調整することにより、ロック競争を削減し、並行性パフォーマンスを改善できます。

MySQLはプログラミング言語ではありませんが、そのクエリ言語SQLにはプログラミング言語の特性があります。1。SQLは条件付き判断、ループ、可変操作をサポートします。 2。ストアドプロシージャ、トリガー、機能を通じて、ユーザーはデータベースで複雑な論理操作を実行できます。

MySQLはオープンソースのリレーショナルデータベース管理システムであり、主にデータを迅速かつ確実に保存および取得するために使用されます。その実用的な原則には、クライアントリクエスト、クエリ解像度、クエリの実行、返品結果が含まれます。使用法の例には、テーブルの作成、データの挿入とクエリ、および参加操作などの高度な機能が含まれます。一般的なエラーには、SQL構文、データ型、およびアクセス許可、および最適化の提案には、インデックスの使用、最適化されたクエリ、およびテーブルの分割が含まれます。

MySQLは、データストレージ、管理、クエリ、セキュリティに適したオープンソースのリレーショナルデータベース管理システムです。 1.さまざまなオペレーティングシステムをサポートし、Webアプリケーションやその他のフィールドで広く使用されています。 2。クライアントサーバーアーキテクチャとさまざまなストレージエンジンを通じて、MySQLはデータを効率的に処理します。 3.基本的な使用には、データベースとテーブルの作成、挿入、クエリ、データの更新が含まれます。 4.高度な使用には、複雑なクエリとストアドプロシージャが含まれます。 5.一般的なエラーは、説明ステートメントを介してデバッグできます。 6.パフォーマンスの最適化には、インデックスの合理的な使用と最適化されたクエリステートメントが含まれます。

MySQLは、そのパフォーマンス、信頼性、使いやすさ、コミュニティサポートに選択されています。 1.MYSQLは、複数のデータ型と高度なクエリ操作をサポートし、効率的なデータストレージおよび検索機能を提供します。 2.クライアントサーバーアーキテクチャと複数のストレージエンジンを採用して、トランザクションとクエリの最適化をサポートします。 3.使いやすく、さまざまなオペレーティングシステムとプログラミング言語をサポートしています。 4.強力なコミュニティサポートを提供し、豊富なリソースとソリューションを提供します。

INNODBのロックメカニズムには、共有ロック、排他的ロック、意図ロック、レコードロック、ギャップロック、次のキーロックが含まれます。 1.共有ロックにより、トランザクションは他のトランザクションが読み取らないようにデータを読み取ることができます。 2.排他的ロックは、他のトランザクションがデータの読み取りと変更を防ぎます。 3.意図ロックは、ロック効率を最適化します。 4。ロックロックインデックスのレコードを記録します。 5。ギャップロックロックインデックス記録ギャップ。 6.次のキーロックは、データの一貫性を確保するためのレコードロックとギャップロックの組み合わせです。


ホットAIツール

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

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

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

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

人気の記事

ホットツール

SAP NetWeaver Server Adapter for Eclipse
Eclipse を SAP NetWeaver アプリケーション サーバーと統合します。

DVWA
Damn Vulnerable Web App (DVWA) は、非常に脆弱な PHP/MySQL Web アプリケーションです。その主な目的は、セキュリティ専門家が法的環境でスキルとツールをテストするのに役立ち、Web 開発者が Web アプリケーションを保護するプロセスをより深く理解できるようにし、教師/生徒が教室環境で Web アプリケーションを教え/学習できるようにすることです。安全。 DVWA の目標は、シンプルでわかりやすいインターフェイスを通じて、さまざまな難易度で最も一般的な Web 脆弱性のいくつかを実践することです。このソフトウェアは、

SublimeText3 英語版
推奨: Win バージョン、コードプロンプトをサポート!

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

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