sql分页优化

一.sql分页:先分页后多表连接查询

declare @row int;
select @row=COUNT(*) from BS_Subject where 1=1;
SELECT TOP 10 @row as Record, a.*,c.ClassName FROM BS_Subject a left join BS_Class c on a.ClassID=c.ClassID  WHERE 1=1 and a.ID 
not IN(SELECT TOP 1000000 ID FROM BS_Subject 
where 1=1 ORDER BY ID desc)ORDER BY a.ID desc

虽然这样可以快速查询出来，但是在程序里不好调用。

二.存储过程分页：单表，多表，多字段排序，总数

create PROCEDURE [dbo].[usp_PagingLarge]
@TableNames VARCHAR(200),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = '',    --排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int  --返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    declare @strSQL varchar(500)
    
    if @RecordCount=1
    begin
        IF @Filter = ''
        begin
            set @strSQL = 'select count(*) as Total from ' + @TableNames
        end
        else 
        begin
            set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter
        end
        exec (@strSQL);
    end
    else
    begin
        IF @Fields = ''
            SET @Fields = '*'
        IF @Filter = ''
            SET @Filter = 'WHERE 1=1'
        ELSE
            SET @Filter = 'WHERE ' +  @Filter
        IF @Group <>''
            SET @Group = 'GROUP BY ' + @Group

        IF @Order <> ''
        BEGIN
            DECLARE @pos1 INT, @pos2 INT
            SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
            IF CHARINDEX(' DESC', @Order) > 0
                IF CHARINDEX(' ASC', @Order) > 0
                BEGIN
                    IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                        SET @Operator = '<='
                    ELSE
                        SET @Operator = '>='
                END
                ELSE
                    SET @Operator = '<='
            ELSE
                SET @Operator = '>='
            SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
            SET @pos1 = CHARINDEX(',', @SortColumn)
            IF @pos1 > 0
                SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
            SET @pos2 = CHARINDEX('.', @SortColumn)
            IF @pos2 > 0
            BEGIN
                SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
                IF @pos1 > 0 
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
                ELSE
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
            END
            ELSE
            BEGIN
                SET @SortTable = @TableNames
                SET @SortName = @SortColumn
            END
        END
        ELSE
        BEGIN
            SET @SortColumn = @PrimaryKey
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
            SET @Order = @SortColumn
            SET @Operator = '>='
        END

        DECLARE @type varchar(50)
        DECLARE @prec int
        SELECT @type=t.name, @prec=c.prec
        FROM sysobjects o 
        JOIN syscolumns c on o.id=c.id
        JOIN systypes t on c.xusertype=t.xusertype
        WHERE o.name = @SortTable AND c.name = @SortName
        IF CHARINDEX('char', @type) > 0
        SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

        DECLARE @TopRows INT
        SET @TopRows = @PageSize * @CurrentPage + 1
        print @TopRows
        print @Operator
        
        if @RecordCount=2
        begin
            IF @Filter = ''
            begin
                set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';'
            end
            else 
            begin
                set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+@Filter+';'
            end

            EXEC(@strSQL+'
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
            ') 
        end
        else 
        begin
            EXEC('
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
            ') 
        end
        
    end   
END

调用

declare   @ct   datetime   
  set   @ct   =   getdate()  
  
   EXEC usp_PagingLarge 'BS_Acode left join BS_Class on BS_Acode.ClassID=BS_Class.ClassID',
  'BS_Acode.ID','BS_Acode.*,BS_Class.ClassName',10,100,'','',
  'BS_Acode.ID desc,BS_Acode.CreateDate desc',2
  
select   datediff(ms,   @ct   ,getdate())   as   '查询时间（毫秒）' 

搜索了好多分页的存储过程，这个是挺好用的。可以支持单表、多表的查询，还可以排序（多字段排序），也可以返回总数，效率也好。之前用的一个，在前面的页数的时候查询很快，当到中后的页面的时候非常慢。

证明设置索引的必要性，我之前并相信设置这玩意儿会带来什么，关键是自己真不知道怎么看。其实是自己愚笨，不懂用了之后就说他没用。另外，非常确定ordery by真的能不用就用，经过尝试加上非常影响效率。

好友的总结，我收下了：

1.表的主键、外键必须有索引

2.数据量超过300的表应该有索引

3.经常与其他表进行连接的表，在连接字段上应该建立索引

4.经常出现在where字句中的字段，特别是大表的字段，应该建立索引

5.索引应该建在选择性高的字段上

6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引

7.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替

 分页注意点：分页的时候一定要确保每一条数据都尤其自己的位置（顺序），比如按照热度排序的时候，就得order by 热度，发表时间，主键

这样就可以避免了在相同热度，相同发表时间里，再按照主键排序，就能将每个数据确定他的位置了。

-------------好多东西都是需要慢慢完善的,再自己没有发现的时候,它都是最好的-------------------

---上面的存储过程分页在分组查询的时候,不能order by分组字段。修改了一下，现在可以了

ALTER PROCEDURE [dbo].[usp_PagingLarge]
@TableNames VARCHAR(500),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(500),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(500),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(500) = '',    --条件，可以为空，不用填 where
@Group VARCHAR(500) = '',    --分组依据，可以为空，不用填 group by
@Order VARCHAR(500) = '',    --排序，可以为空，为空默认按主键升序排列，不用填 order by
@RecordCount int  --返回总页数 1只返回总数，0只返回当前页数据，2返回总数并返回当前页数据
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    declare @strSQL varchar(500)
    
    if @RecordCount=1
    begin
        IF @Filter = ''
        begin
            set @strSQL = 'select count(*) as Total from ' + @TableNames
        end
        else 
        begin
            set @strSQL = 'select count(*) as Total from ' + @TableNames + ' where '+@Filter
        end
        exec (@strSQL);
    end
    else
    begin
        IF @Fields = ''
            SET @Fields = '*'
        IF @Filter = ''
        begin
            IF @Group <>''
            begin
             SET @Filter = 'having 1=1'
            end
            else
            begin
             SET @Filter = 'WHERE 1=1'
            end
        end
        ELSE
        begin
            IF @Group <>''
            begin
             SET @Filter = 'having '+@Filter
            end
            else
            begin
             SET @Filter = 'WHERE ' +  @Filter
            end
        end
        IF @Group <>''
            SET @Group = 'GROUP BY ' + @Group

        IF @Order <> ''
        BEGIN
            DECLARE @pos1 INT, @pos2 INT
            SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
            IF CHARINDEX(' DESC', @Order) > 0
                IF CHARINDEX(' ASC', @Order) > 0
                BEGIN
                    IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                        SET @Operator = '<='
                    ELSE
                        SET @Operator = '>='
                END
                ELSE
                    SET @Operator = '<='
            ELSE
                SET @Operator = '>='
            SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
            SET @pos1 = CHARINDEX(',', @SortColumn)
            IF @pos1 > 0
                SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
            SET @pos2 = CHARINDEX('.', @SortColumn)
            IF @pos2 > 0
            BEGIN
                SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
                IF @pos1 > 0 
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
                ELSE
                    SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
            END
            ELSE
            BEGIN
                SET @SortTable = @TableNames
                SET @SortName = @SortColumn
            END
        END
        ELSE
        BEGIN
            SET @SortColumn = @PrimaryKey
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
            SET @Order = @SortColumn
            SET @Operator = '>='
        END

        DECLARE @type varchar(50)
        DECLARE @prec int
        SELECT @type=t.name, @prec=c.prec
        FROM sysobjects o 
        JOIN syscolumns c on o.id=c.id
        JOIN systypes t on c.xusertype=t.xusertype
        WHERE o.name = @SortTable AND c.name = @SortName
        
        IF CHARINDEX('char', @type) > 0
        begin
          SET @type = @type + '(' + CAST(@prec AS varchar) + ')'
        end

        if @type is null and @Group<>''
        begin
          set @type='int'
        end


        DECLARE @TopRows INT
        SET @TopRows = @PageSize * @CurrentPage + 1
        
        if @RecordCount=2
        begin
            IF @Filter = ''
            begin
                set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+';'
            end
            else 
            begin
                set @strSQL = 'declare @PageRecordNumber int;select @PageRecordNumber=count(*) from ' + @TableNames+' '+replace(@Filter,'having','where')+';'
            end

            IF @Group <>''
            begin
                --把分组和不分组的查询分开，查询条件和位置不一样
               EXEC(@strSQL+'
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames  + ' ' + @Group+' '+@Filter + ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM  ' + @TableNames + ' ' + @Group+' '+@Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + '    
            ') 
            end
            else
            begin
                EXEC(@strSQL+'
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ',@PageRecordNumber as Page_RecordNumber FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
            ') 
            end
        end
        else 
        begin 
            IF @Group <>''
            begin
                --把分组和不分组的查询分开，查询条件和位置不一样
                EXEC('
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames  + ' ' + @Group +' '+@Filter+ ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ' FROM  ' + @TableNames +' '+  @Group+' '+ @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ' ORDER BY ' + @Order + '    
            ')
            end
            else
            begin
                EXEC('
                DECLARE @SortColumnBegin ' + @type + '
                SET ROWCOUNT ' + @TopRows + '
                SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
                SET ROWCOUNT ' + @PageSize + '
                SELECT ' + @Fields + ' FROM  ' + @TableNames +' '+ @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' +  @Group + ' ORDER BY ' + @Order + '    
            ')
            end    
        end
        
    end   
END

EXEC    usp_PagingLarge
        @TableNames = N'User_ExchangeGoods left join User_ExchangeOrder on User_ExchangeGoods.GID=User_ExchangeOrder.GoodsID',
        @PrimaryKey = N'User_ExchangeGoods.GID',
        @Fields = N'User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max)) as GoodsPictures',
        @PageSize = 10,
        @CurrentPage = 0,
        @Filter = '1=1',
        @Group = N'User_ExchangeGoods.GID,User_ExchangeGoods.GoodsName,User_ExchangeGoods.GoodsPrice,User_ExchangeGoods.ExchangeIntegral,cast(User_ExchangeGoods.GoodsPictures as varchar(max))',
        @Order = 'COUNT(User_ExchangeOrder.GoodsID) desc',
        @RecordCount = 0

上面，GoodPictures类型是text，会报错“不能比较或排序 text、ntext 和 image 数据类型，除非使用 IS NULL 或 LIKE 运算符。”，转换一下即可。

 但是分页存储过程当有两个字段排序时，第二个排序无效。

 ——————————————————————————

事实上上面的存储过程并不是通用的，个别情况的时候还是得自己写一个分页sql。

SELECT TOP 页大小 *
FROM 表
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID