SQL优化培训.ppt

课程大纲 1、表与索引的物理存储结构 2、全面了解索引 2.1 索引的B树结构 2.2 非聚集索引 2.3 聚集索引 3、SQL SERVER查询优化策略 3.1 概述：基于成本的优化策略 3.2 表关联策略 3.3 索引覆盖策略 3.4 索引统计 3.5 高速缓存策略 表的存储结构 前言： 了解表和索引的存储结构对于SQL优化，即如扎马步对于武林神功一样，是基础，不可跨越。不了解存储结构，知道再多的查询优化策略也无用，因为始终会知其然不知其所以然。幸好，我们这个基础马步只需要半小时就可以了。 第1节是基础，第2节是进阶，第3节是精华。 表的存储结构 页的示意图 每页的开始部分是 96 字节的页首，用作SQL中的管理字节，管理字节中记录着本页的编号、上一页和下一页的编号等（还有其他信息）。 页首下面紧跟着放记录，如下图。 页链示意图 页通过页链连成表 表的存储结构 2、每个页上最大存储8192－96=8096个字节的数据。如：表的每条记录的长度是1000字节，则每个页可存放8条记录；如果记录长度是200字节，则每个页放40条记录，假设表有1万条记录，则后者会使用250个页。 3、记录不可跨页。就是说，假设一条记录长度为5000个字节的话，那么一页上只能存放一条记录，而页上的剩余的3096个字节就浪费了，且没办法回收利用。 4、每条记录的长度不可大于8060个字节。 计算表的大小(可跳过) 计算方法： 表大小=页的数量*8K 页的数量=总行数/每页可存放的行数 取整 每页可存放的行数=8096/(行长度+2) 取整 行长度=各列长度之和+2 + (总列数/ 8 进位取整 ) +4 如果存在VarChar型的可变长度列，行长度需再加上：2+2*可变长度列数 如果存在可变长度列，每行的长度可能不一样。 计算表的大小:练习 有Table，结构如下，计算当行数为100万时，该表的大小： TB001 品号 CHAR(20) TB002 品名 CHAR(60) TB003 单位 CHAR(8) TB004 备注 CHAR(100) 看一下记录是如何插入的? 索引的存储结构 索引的存储结构和表差不多。也是存储在页上的。只是表中存储的字段多，而索引中存的字段非常少而已。所以索引一般比表小很多。 (具体计算方法可略过) 计算方法（叶级索引）： 叶级索引的大小=页的数量*8K 页的数量=总行数/每页可存放的行数 取整 每页可存放的行数=8096/(行长度+2) 取整 行长度=各列长度之和+2 + (总列数/ 8 进位取整 ) +1+8 如果存在VarChar型的可变长度列，行长度需再加上：2+2*可变长度列数 如果存在可变长度列，每行的长度可能不一样。 工具使用 1、sp_spaceused 表名 功能：查看一个表共有多少行、共有多少页、共占用多少K字节，及数据和索引的分别占用。 用此存储过程检查一下，你人工计算的表大小和实际的表大小一样吗？ 2、sp_tablespace 功能： sp_spaceused的强化版，返回一个数据库所有表的空间占用情况。此存储过程不是SQL SERVER自带的，是我自己写的，目前放置在10.4.40.12\master数据库中。可以移植到任何服务器以方便使用。 课程大纲 1、表与索引的物理存储结构 2、全面了解索引 2.1 索引的B树结构 2.2 非聚集索引 2.3 聚集索引 3、SQL SERVER查询优化策略 3.1概述：基于成本的优化策略 3.2 表关联策略 3.3 索引覆盖策略 3.4 索引统计 3.5 高速缓存策略 索引的B树结构 工号 姓名 性别 380 献宇 男 347 刘晶 女 963 陆续 男 621 张伟 男 641 魏李 男 264 大鹏 男 045 龚克 男 索引的B树结构 索引层数的计算 假设每页放100条索引，一个1000万笔记录的大表，那么叶级索引约有10万页，二级索引有1000页，三级索引10页，根索引1页，每一级都是上一级的1/100。这个表的索引共有4层。 看看SQL是如何根据索引找记录的 看一下如何根据索引找记录 由上我们知道：SQL根据索引查找记录时，只需要对索引中的每一级扫描一页，最后再扫描一个数据页就可以了。 索引字段一般都很短，每个页上可能会存放几百条索引记录。假设1000万笔记录、一个2G的大表，每页放100条索引，根据索引查找记录，只需扫描5个页，4个索引页+1个数据页，5个页是多少？硬盘要做多少动作？5*8K=区区40K而已。不论记录是在表的任何一个地方：前面、后面或中间。