数据库优化的方法及步骤

最新推荐文章于 2024-08-08 19:04:59 发布

人称东哥

最新推荐文章于 2024-08-08 19:04:59 发布

阅读量1.4k

点赞数

文章标签： SQL 数据库优化活动监视器

本文链接：https://blog.csdn.net/Lewis_1990213/article/details/81669351

版权

先简单介绍下博主面临的情况，17年底，一个用了5年的售后系统处于要升级换代又因为其他原因导致暂时不能升级的情况，所以需要在不动大的架构的情况来做优化。具体有个呼叫中心，坐席会一直在接电话，60人左右，电话保存时有个很复杂的派单逻辑，要优先保证效率。外面管理员，维修工，操作员3000+。

先用代码整理下，看看各个表都是多大

--Script1:
--查看某数据库所有表的信息
DECLARE @tablespaceinfo TABLE (
    [name] SYSNAME,
    [rows] BIGINT,
    [reserved] VARCHAR(100),
    [data] VARCHAR(100),
    [index_size] VARCHAR(100),
    [unused] VARCHAR(100)
)

DECLARE @tablename VARCHAR(255);

DECLARE Info_cursor CURSOR FOR
    SELECT '['+[name]+']' FROM sys.tables WHERE TYPE='U';

OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @tablespaceinfo EXEC sp_spaceused @tablename
    
    FETCH NEXT FROM Info_cursor INTO @tablename
END

CLOSE Info_cursor
DEALLOCATE Info_cursor

SELECT * FROM @tablespaceinfo
    ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC

复制下来直接执行就好，结果是数据库大致400G,最大的表为客户档案和客户资产，分别有2000万条和1800万条数据这两个数据表，之前优化后已经对表做了分区，具体可以看表分区，下面是要先对呼叫中心反映的卡顿做对应的处理，说是电话接入后要10-20秒才会有反应，需要出画面打开慢的原因。

开始以为是两个大表的原因，后面发现不是，下面介绍下最快速发现问题的工具。

框框中标识的是活动监视器，里面会有几个非常有用的东西，我们继续暂开说

活动监视器会自动给出这些监视的内容，这里有两点非常需要关注，最近耗费大量资源的查询，以及进程。双击这些查询最近耗费大量资源的查询，会看到具体的语句。我们这时只要在其中发现我们需要优化的界面中存在的语句调用的内容，就可以做对应处理。同时插一句，如果让这里耗费大量资源语句的都得到优化就已经极大的完成了任务了。

这个是我找出来的问题的存储过程，发现这个执行起来非常慢大概需要15秒，虽然看着表很多，但总觉得不至于。图中可以发现，有大量的DATEADD(MM,-6,GETDATE())为了获取最近6个月的服务数据，那就简单了，创建个变量代替这个就行。现在这个存储过程被压缩到2秒，另外说一句，就算不用运算getdate这个函数，取一次，然后用变量代替getdate也会比现在优化很多，不需要反复去数据库取时间。

下面是进程

这是当时上线一个外部app端后的情况，可以发现目前的资源等待已经很严重了，高达8517毫秒，虽然阻塞者都是自己273，但其实是有很多可以优化的空间的。双击，可以看到详细的回话信息，里面的内容可以拷出来单独优化。语句中有个*非常的扎眼，这里说下当你想在select子句中列出所有的列时，使用动态sql列引用“*”是一个方便的方法，不幸的是，是一种非常低效的方法。sql解析过程中，还需要把“*”依次转换为所有的列名，这个工作需要查询数据字典完成！后续查看该视图，又将里面的UNION ALL拆分，将该语句效率提升了3倍左右。