分类 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')
  1. 1
  2. 2