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

mysqldiffersfromothersqldialectsinsyntaxforlimit, 자동 점유, 문자열 comparison, 하위 쿼리 및 퍼포먼스 앤 알리 분석 .1) mysqluse Slimit, whilesqlSerVerusestOpandoracleSrownum.2) MySql'Sauto_incrementContrastSwithPostgresql'serialandoracle '

MySQL 파티셔닝은 성능을 향상시키고 유지 보수를 단순화합니다. 1) 큰 테이블을 특정 기준 (예 : 날짜 범위)으로 작은 조각으로 나누고, 2) 데이터를 독립적 인 파일로 물리적으로 나눌 수 있습니다.

MySQL에서 권한을 부여하고 취소하는 방법은 무엇입니까? 1. 보조금 명세서를 사용하여 grantallprivilegesondatabase_name.to'username'@'host '와 같은 부여 권한; 2. Revoke 문을 사용하여 Revokeallprivilegesondatabase_name.from'username'@'host '와 같은 권한을 취소하여 허가 변경의 적시에 의사 소통을 보장하십시오.

InnoDB는 거래 지원 및 높은 동시성이 필요한 응용 프로그램에 적합한 반면, MyISAM은 더 많은 읽기와 덜 쓰는 응용 프로그램에 적합합니다. 1. INNODB는 전자 상거래 및 은행 시스템에 적합한 거래 및 은행 수준의 자물쇠를 지원합니다. 2. Myisam은 블로깅 및 컨텐츠 관리 시스템에 적합한 빠른 읽기 및 색인을 제공합니다.

MySQL에는 Innerjoin, Leftjoin, RightJoin 및 FullouterJoin의 네 가지 주요 조인 유형이 있습니다. 1. 결합 조건을 충족하는 두 테이블의 모든 행을 반환합니다. 2. Leftjoin 오른쪽 테이블에 일치하는 행이 없더라도 왼쪽 테이블의 모든 행을 반환합니다. 3. RightJoin은 LeftJoin과 상반되며 오른쪽 테이블의 모든 행을 반환합니다. 4. FULLOUTERNOIN은 조건을 충족 시키거나 충족하지 않는 두 테이블의 모든 행을 반환합니다.

mysqloffersvariousStorageEngines, 각각의 everitedforentUsecases : 1) innodbisidealforapplicationsneedingAcidCoInceandHighConcurrency, 지원 트랜잭션 및 foreignKeys.2) myIsAmisbestforread-heverworkloads, memoryengineis

MySQL의 일반적인 보안 취약점에는 SQL 주입, 약한 암호, 부적절한 권한 구성 및 업데이트되지 않은 소프트웨어가 포함됩니다. 1. 전처리 명령문을 사용하여 SQL 주입을 방지 할 수 있습니다. 2. 강력한 비밀번호 전략을 사용하여 약한 암호는 피할 수 있습니다. 3. 정기적 인 검토 및 사용자 권한 조정을 통해 부적절한 권한 구성을 해결할 수 있습니다. 4. Unupdated 소프트웨어는 MySQL 버전을 정기적으로 확인하고 업데이트하여 패치 할 수 있습니다.

느린 쿼리 로그를 활성화하고 임계 값을 설정하여 MySQL에서 느린 쿼리를 식별 할 수 있습니다. 1. 느린 쿼리 로그를 활성화하고 임계 값을 설정하십시오. 2. 느린 쿼리 로그 파일을보고 분석하고 심층 분석을 위해 MySQLDumpSlow 또는 PT-Query 소수성과 같은 도구를 사용하십시오. 3. 인덱스 최적화, 쿼리 재 작성 및 select*의 사용을 피함으로써 느린 쿼리 최적화를 달성 할 수 있습니다.


핫 AI 도구

Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!

인기 기사

뜨거운 도구

VSCode Windows 64비트 다운로드
Microsoft에서 출시한 강력한 무료 IDE 편집기

SublimeText3 Linux 새 버전
SublimeText3 Linux 최신 버전

메모장++7.3.1
사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.

mPDF
mPDF는 UTF-8로 인코딩된 HTML에서 PDF 파일을 생성할 수 있는 PHP 라이브러리입니다. 원저자인 Ian Back은 자신의 웹 사이트에서 "즉시" PDF 파일을 출력하고 다양한 언어를 처리하기 위해 mPDF를 작성했습니다. HTML2FPDF와 같은 원본 스크립트보다 유니코드 글꼴을 사용할 때 속도가 느리고 더 큰 파일을 생성하지만 CSS 스타일 등을 지원하고 많은 개선 사항이 있습니다. RTL(아랍어, 히브리어), CJK(중국어, 일본어, 한국어)를 포함한 거의 모든 언어를 지원합니다. 중첩된 블록 수준 요소(예: P, DIV)를 지원합니다.
