问题:

碎片产生的原因

我知道SQL
Server有很多视图和函数让我来了解SQL Server的运行状态.我还想知道SQL
Server上关于来自用户或者应用的活动请求信息.怎么查询这些信息呢?

图片 1

(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;

SQL Server的动态管理视图DMV
sys.dm_exec_requests可以实现.但是它不仅仅显示了来自连接用户或应用的请求.比如,它还显示了SQL
Server有非常多的后台任务.比如下面的简单查询:

解决方案:

(2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

   

 

(3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;

select session_id,start_time,command
from sys.dm_exec_requests
where status='background';

图片 2

例如:

 

 在PHP 代码中 输入 ;

一个表有1万行,每行10字节,会占用10万字节存储空间,执行删除操作,只留一行,实际内容只剩下10字节,但MySQL在读取时,仍看做是10万字节的表进行处理,所以,碎片越多,就会越来越影响查询性能。

   

1 //$connection  是链接数据库返回的变量名;
2 
3 mysqli_set_charset($connection,'utf8');

查看表碎片大小

这是一个很简单的例子,在我的测试机上返回了20多个不同的会话.

完美解决:

(1)查看某个表的碎片大小

图片 3

图片 4

mysql> SHOW TABLE STATUS LIKE ‘表名’;

   

 

结果中’Data_free’列的值就是碎片大小

不过,通常我们是使用DMV来对活动会话进行故障排除.最先我们需要做的就是看哪些会话在经理等待.

(2)列出所有已经产生碎片的表

   

mysql> select table_schema db, table_name, data_free, engine from
information_schema.tables where table_schema not in
(‘information_schema’, ‘mysql’) and data_free > 0;

select session_id,blocking_session_id,start_time,wait_type
from sys.dm_exec_requests
where blocking_session_id >0;  

清除表碎片

 

(1)MyISAM表

我们可以使用下面的2中方法确定查询是什么,以及是什么导致了阻塞:

mysql> optimize table 表名

1.如果有活动请求,我们可以使用sys.dm_exec_requests
和sys_dm_exec_sql_text(),然后把sql_handle作为参数传进去.

(2)InnoDB表

2.如果没有活动的请求,我们可以连接sys.dm_exec_commections
然后传递most_recent_sql_handle到sys.dm_exec_sql_text().

mysql> alter table 表名 engine=InnoDB

   

 

在这个例子中,我知道spid=53会话没有活动的请求,因为我查了sys.dm_exec_requests.我们再回过头来看看第二种方法.

建议

   

清除碎片操作会暂时锁表,数据量越大,耗费的时间越长,可以做个脚本,定定期检查mysql中 information_schema.TABLES字段,查看
DATA_FREE
字段,大于0话,就表示有碎片,例如每周三凌晨,检查DATA_FREE字段,大于自己认为的警戒值的话,就清理一次。

select distinct des.session_id,dst.text as 'SQL'
from sys.dm_exec_requests as DER
join sys.dm_exec_connections as DEC
on DER.blocking_session_id=DEC.session_id
cross apply sys.dm_exec_sql_text(DEC.most_recent_sql_handle) as DST;

 

 然后我们就发现下面的请求返回了

   

图片 5

   

这看起来是一个没有问题的查询,只是简单的插入,所有我们还应该更深入的看看.这时我们应该看看是否有开启的事务,如果它有活动的请求,我们可以在sys.dm_exec_requests的open_transaction_count列看到.我们这里没有看到活动请求,我们可以看看sys.dm_exec_sessions:

   

select session_id,open_transaction_scount
from sys_dm_exec_sessions
where open_transaction_count >0;

 

   

我们看到了下面打开的事务,可能是随忘了提交事务.

发表评论

电子邮件地址不会被公开。 必填项已用*标注