SQL 性能调优（索引的理论）.pptVIP

SQL 性能调优（索引的理论） 浪潮，鞠强 前言 性能调优的工作在公司的各个产品组作了不少年了，始终不算太成功。 性能的问题，90%的情况和SQL有问题。 在这90%中，80%以上的情况，和索引设置、SQL写法相关。 如何判断SQL是否有效率，我们并没有掌握工具和理论，始终还处在一个瞎猜的阶段。 我们的目标 通过这2小时的培训，解决我们对于这80%和90%的问题的困惑！ 恢复Credit数据库 实验所用的表 category charge corporation member payment provider region statement status 判断SQL优劣的标准 Set statistics io on Set statistics time on 打开查询计划开关：Ctrl + K 仔细观察查询计划、I/O状况 牢记这几张图 – 无索引查询 牢记这几张图 –通用索引查询 牢记这几张图 – 聚集索引查询 牢记这几张图 – 无聚集索引的索引查询 牢记这几张图 – 有聚集索引的索引查询 试验一：检查索引的使用情况 首先了解我们要操作的表的值范围 SELECT Charge_No , MIN(Charge_No) AS Minimum Value, MAX(Charge_No) AS Maximum Value FROM charge UNION SELECT Member_No , MIN(Member_No) AS Minimum Value, MAX(Member_No) AS Maximum Value FROM charge UNION SELECT Provider_No , MIN(Provider_No) AS Minimum Value, MAX(Provider_No) AS Maximum Value FROM charge UNION SELECT Category_No , MIN(Category_No) AS Minimum Value, MAX(Category_No) AS Maximum Value FROM charge GO 试验一：检查索引的使用情况 检查数据量大小 SELECT * FROM charge WHERE charge_no BETWEEN 1 AND 100000 SELECT * FROM charge WHERE member_no BETWEEN 1 AND 10000 SELECT * FROM charge WHERE provider_no BETWEEN 1 AND 500 SELECT * FROM charge WHERE category_no between 1 AND 10 试验二：检查索引选择性 分别执行下列SQL，查看执行计划，以及每个SQL的I/O成本 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 100000 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 10000 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 1000 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 100 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 10 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 1 试验三：比较聚集索引和非聚集索引 执行下列SQL，并记录I/O成本 SELECT charge_no,member_no FROM charge WHERE charge_no BETWEEN 1 AND 100000 在charge_no上建立聚集索引，重新执行，并记录I/O。 试验三：比较聚集索引和非聚集索引 删除上述聚集索引，在member_no上建立聚集索引，重新执行，并记录I/O。（强制使用索引，下同） SELECT charge_no,member_no FROM charge(index(charge_member_link)) WHERE charge_no BETWEEN 1 AND 100000 删除上述聚集索引，在category_no上建立聚集索引，重新执行