SqlServer性能优化索引（五）

SqlServer性能优化索引（五）

导入表结构：

select * into ProductCategory from AdventureWorksDW2014.dbo.DimProductCategoryselect * into Product from AdventureWorksDW2014.dbo.DimProduct

开启磁盘io：

set statistics io onselect EnglishProductName,StandardCost,Color,Size,Weight from Productwhere size>'M'--0.189 io:251set statistics io off

非聚簇索引：

创建的语句：create nonclustered index nc_product_size on product(size)

再次执行上面的查询代码（提高了三倍）：

set statistics io onselect EnglishProductName,StandardCost,Color,Size,Weight from Productwhere size>'M' --0.054 io:19set statistics io off

建立覆盖索引：

create nonclustered index nc_product_size1 on product(size) include(EnglishProductName,StandardCost,Color,Weight)

再次执行上述语句：

set statistics io onselect EnglishProductName,StandardCost,Color,Size,Weight from Productwhere size>'M' --0.003 io:2set statistics io off

数据库会自动选择索引：

没有创建索引的情况：

set statistics io onselect c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere c.ProductCategoryKey=1 --0.1928set statistics io off

创建索引：

create nonclustered index nc_productcategory_key on ProductCategory(ProductcategoryKey) include(EnglishProductCategoryName)

在次查询：

set statistics io onselect c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere c.ProductCategoryKey=1 --0.1928 io:c 2 p 251set statistics io off

IO情况：

由此可见 Product表影响比较严重 251

建立一个非聚簇索引：（做一个物理排序）

create nonclustered index nc_product_categorykey on product(productsubcategorykey) include(englishproductname,color,size)

执行语句：

set statistics io onselect c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere c.ProductCategoryKey=1 --4.29 od 1497 oh 783 c 155set statistics io off

导入三张表：

select * into Customer from AdventureWorks2014.Sales.Customerselect * into OrderHeader from AdventureWorks2014.Sales.SalesOrderHeaderselect * into OrderDetail from AdventureWorks2014.Sales.SalesOrderDetail

实现一些业务：

set statistics io on select c.CustomerID,SUM(od.LineTotal) from OrderDetail as od inner join orderheader as oh on od.SalesOrderID=oh.SalesOrderID inner join customer as c on oh.CustomerID =c.CustomerID group by(c.CustomerID) --4.29 set statistics io off

优化的第一步：

1.查看sql语句写法是否有问题（进行改造）

set statistics io onselect oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --3.77 od 1497 oh 783set statistics io off

创建索引：

create nonclustered index nc_OrderDetail_SalesOrderID on OrderDetail(SalesOrderID) include(linetotal)

创建另外一个索引：针对group by 的列

create nonclustered index nc_OrderHeader_CustomerID on OrderHeader(CustomerID)

在次执行上述语句：

set statistics io onselect oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --3.10 od 533 oh 783set statistics io off

采用索引视图的方式：

create view v_Order_Totalasselect oh.CustomerID,sum(od.LineTotal) as 总额 from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)

效果差不多：

set statistics io onselect * from v_Order_Total --3.10 od 533 oh 783set statistics io off

修改：

alter view v_Order_Totalasselect oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)

对唯一列做聚集索引：

create clustered index c_vordertotal_customerid on v_order_total(客户ID)

直接运行报错：

解决方案：

在次执行：

alter view v_Order_Totalwith schemabinding asselect oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)

报错：

解决方法：

alter view v_Order_Totalwith schemabinding asselect oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额 from dbo.OrderDetail as od inner joindbo.OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)

在次创建：

create clustered index c_vordertotal_customerid on v_order_total(客户ID)

报错：

办法：

create unique clustered index c_vordertotal_customerid on v_order_total(客户ID)

报错：

方法：

alter view v_Order_Totalwith schemabinding asselect oh.CustomerID as 客户ID, sum(od.LineTotal) as 总额,COUNT_BIG(*) as 计数 from dbo.OrderDetail as od inner joindbo.OrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID)

执行创建索引：

create unique clustered index c_vordertotal_customerid on v_order_total(客户ID)

成功

set statistics io onselect * from v_Order_Total --0.09 io:92set statistics io off

执行计划：

会自动进行值的更新，不用关心

对语句的访问会用到刚才的架构：

set statistics io onselect oh.CustomerID,sum(od.LineTotal) from OrderDetail as od inner joinOrderHeader as oh on od.SalesOrderID=oh.SalesOrderID group by(oh.CustomerID) --0.09 io:92set statistics io off

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。