分类 SQL Server 下的文章

多条件查询及 left join 的利用,几乎在所有项目都会用到。但只有数据量大、并发大时才会有人去注意他的性能。

先来看一个最有代表性的分页存储过程:

ALTER PROCEDURE [dbo].[P_Article_List]  
@page int=1,
@pagesize int=10,
@key VARCHAR(450)=''
AS
BEGIN
    SELECT COUNT(1)OVER(PARTITION BY '''') AS Total,b.NickName,b.Avator,a.*
    FROM dbo.t_article a with (NOLOCK)
    LEFT join t_user b with (NOLOCK) ON b.UserID=a.UserID
    WHERE a.IsValid=1
    AND (@key='' or a.Title like '%'+@key+'%')
    ORDER BY a.order DESC desc
    OFFSET @pagesize*(@page-1) ROWS FETCH NEXT @pagesize ROWS ONLY
END

这是一个很标准的存储过程,这样写代码也很漂亮,但这样的性能很差。通过查看执行计划,你会发现一个很奇怪的问题:

明明是 left join,为什么执行计划里是 inner join 呢,要知道后者的性能是低很多的。

这个问题是因为 where 条件导致的,所以我们要优化它,就必须用新的方式:

先把左边表的数据按条件查询出来放入临时表,再将临时表与右表 left join,不带where

当然,还有一个很重要的因素,分页,我们可以把分页也在临时表里先做了,比如一页10条,这样最后 left join时,左表只有10条数据,这个性能,就会很高

最后贴出优化后的存储过程:

ALTER PROCEDURE [dbo].[P_Article_List]  
@page int=1,
@pagesize int=10,
@key VARCHAR(450)=''
AS
BEGIN
    SELECT COUNT(1)OVER(PARTITION BY '''') AS Total,b.NickName,b.Avator,a.* FROM
    (SELECT * FROM dbo.t_article with (NOLOCK)
        WHERE IsValid=1
        AND (@key='' or Title like '%'+@key+'%')
        ORDER BY order DESC desc
        OFFSET @pagesize*(@page-1) ROWS FETCH NEXT @pagesize ROWS ONLY
    ) a
    LEFT join t_user b with (NOLOCK) ON b.UserID=a.UserID
END

搞定,最后测试,我们数据库的这个存储过程,查询时间从平均 2.3 秒优化到了 0.15 秒!

这是 Mysql 在查询数据进行排序时,需要用到的缓存不够导致的,默认才几百kb。可以通过以下方式增加:

查询当前大小 sort_buffer_size
select @@global.sort_buffer_size;

临时进行调整 sort_buffer_size
SET GLOBAL sort_buffer_size = 1024 1024 8;

永久性调整 (编辑 my.ini 或 my.cnf 配置文件)
[mysqld]
sort_buffer_size=8M

注一:sort_buffer_size 设定 1024 1024 8 是什么意思?
即为将 sort_buffer_size 设为 8MB

注二:my.ini 或 my.cnf 的 sort_buffer_size 不生效?
修改配置后,需要重启 MySQL 服务。

这个存储过程,可以在当前数据库中按关键字查找所有存储过程,非常实用,DBA必备工具:

CREATE PROCEDURE [dbo].[SP] 
     @keywords nvarchar(100)= 'p_Comm_Area_GetList'
AS 
BEGIN 
    SET NOCOUNT ON; 
    SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%' + @keywords+ '%' 
    AND ROUTINE_TYPE='PROCEDURE' 
END

用法:

exec sp 'keywords'

在使用正则前,需要启用系统高级选项的OLE自动化功能,启用过程如下:

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ole Automation Procedures',1
reconfigure

安全起见,使用完后记得关闭该高级功能:

exec sp_configure 'Ole Automation Procedures',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

搜索 Function:

ALTER FUNCTION [dbo].[RegexMatch]
(
    @matchstring VARCHAR(4000),
    @pattern VARCHAR(200)
)
RETURNS BIT
AS BEGIN
    DECLARE @handle int, @result bit
    exec sp_oacreate 'vbscript.regexp', @handle output
    exec sp_oasetproperty @handle, 'pattern',@pattern
    exec sp_oasetproperty @handle, 'global', 'true'
    exec sp_oasetproperty @handle, 'ignorecase', 0
    exec sp_oamethod @handle, 'test', @result output,@matchstring
    exec sp_oadestroy @handle
    return @result
END

使用:

select dbo.RegexMatch(N'abc12345',N'\d{5}')

替换Function

CREATE FUNCTION [dbo].[RegReplace]
(
    @matchstring VARCHAR(4000),
    @pattern VARCHAR(200),
    @replace VARCHAR(200)
)
RETURNS VARCHAR(4000)
AS BEGIN
    DECLARE @handle int, @result VARCHAR(4000)
    exec sp_oacreate 'vbscript.regexp', @handle output
    exec sp_oasetproperty @handle, 'pattern',@pattern
    exec sp_oasetproperty @handle, 'global', 1 --全局替换
    exec sp_oasetproperty @handle, 'ignorecase', 1 --忽略大小写
    exec sp_oamethod @handle, 'Replace', @result output,@matchstring,@replace
    exec sp_oadestroy @handle
    return @result
END

使用:

select dbo.RegReplace('hello 123','\d{2}','world')