SQL性能优化的几个思路（二）

最近又总结了一批SQL性能优化的方法，大家可以参照着做。前一次的优化思路在这里：

SQL性能优化的几个思路

1、WHERE、JOIN的字段，能用主表字段就用主表字段，比如下面这样：

SELECT *
FROM A --主表
    INNER JOIN B ON A.ID=B.ID
    INNER JOIN C ON B.ID=C.ID--注意这里
WHERE B.ID=1023--注意这里

这时的优化方法有：

A、把关联C表时用的B.ID换成A.ID；

B、把WHERE条件中的B.ID换成A.ID

这个情况我不太清楚具体原理，最近在优化一条语句的时候，发现怎么优化都没用。突然发了神经，把关联字段全部换成了主表字段，结果速度一下子就上来了

2、减少中间表数据提取

SELECT * FROM (
    SELECT * 
    FROM A 
        INNER JOIN B ON A.ID=B.ID
    ) AS AB 
WHERE AB.BMONTH=202312 AND 其他条件

SELECT * FROM (
    SELECT * 
    FROM A 
        INNER JOIN B ON A.ID=B.ID 
    WHERE A.BMONTH=202312) AS AB 
WHERE 其他条件

看看这两条语句，觉得哪条性能高？

当然是第二条。

这两个语句都分为两步，第一步是以A、B表作关联，生成一个中间表，然后再从中间表取数据。

第一条语句直接用A关联B，把所有数据都提取出来，涉及的数据量自然很大。

第二条语句把查询条件A.BMONTH加到中间表中，中间表提取的数据量就大为减少，这时对于内存的消耗会急剧减少。

3、减少涉及数据量的提取
看一个分页算法：

SELECT * 
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) ROWNUM,* 
    FROM A) A1
WHERE A.ROWNUM BETWEEN 1 AND 100


SELECT * 
INTO #TEMP
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ID) ROWNUM,ID  
    FROM A  ) AS A1
WHERE A.ROWNUM BETWEEN 1 AND 100
SELECT * 
FROM #TEMP 
    INNER JOIN A ON A.ID=#TEMP.ID

请问哪个性能更强？

在A表中字段不多的时候，其实性能差不了多少，但是当A表字段多（比如有20个）且有多张表格关联的时候，后者的优势就显示出来了。

前者是把A表中符合条件的记录全部提取到内存里面作分页，涉及的数据量是A表所有字段和所有数据

后者是只提取符合条件的100条记录的ID和行号，然后再去关联A表，涉及的数据量就那么一点，速度肯定更快
 

4、适度删除部分索引

A表有以下字段：

CREATETIME，创建时间

STATE，状态，取值0、1、2

FACTORYCODE，厂家编号，取值少于10个

DATAVALUE，读取数据值

建立的索引有：

FACTORYCODE+STATE的联合索引

CREATETIME索引

DATAVALUE索引

DATAVALUE+STATE索引

实际分析情况：

FACTORYCODE、STATE的可取值太少，建立索引的意义不大，可以把这两个字段上的索引全部删除

DATAVALUE一般用于统计汇总，而且也不会加入到GROUP和WHERE子句里面，存在意义不大，可以删除

只有CREATETIME可能是经常会被加入查询条件的，应该保留

索引影响的主要功能点有这几个：

A、插入、修改、删除数据时，可能会引起索引的重排，这时对这些功能有部分影响，所以很多同行都在强调要尽量少建索引

B、索引的原理是排序，当一个字段上的可选值很少时，即使作了排序也没多少意义，所以对于可选值少的字段一般不建立索引

5、使用SQLBULKCOPY作批量数据插入

看下面几行代码：

DataTable dt=new DataTable()
//此处可以加一些数据列与行
using (var copy = new SqlBulkCopy(con))//con即数据库连接
{
    copy.DestinationTableName = "MyTest";//MyTest是用于测试的表名
    copy.WriteToServer(dt);
}

插入数据的方法一般就是单条插入、多条语句组装插入，在1000条以下时，SQLBULKCOPY能够使速度提升20倍左右；在达到10000条以上时，一般能够提升40倍左右。所以如果有大批量数据插入的需求，可以考虑用SQLBULKCOPY来替代INSERT