sqlserver调优--索引篇
[TOC]
理论知识
- SQL Server中数据存储的基本单位是页，每页大小8KB，即每MB有 128 页；
- 区是管理空间的基本单位。一个区是八个物理上连续的页（即 64 KB），即每MB有 16 个区；
通常，一个数据表的存储是由两部分组成，一部分是存放表的数据页，另一部分是存放表的索引页。当检索数据时，系统先搜索索引页面，从中找到所需数据的指针，然后直接通过指针从数据页面中读取数据，提高数据的访问速度。
索引的分类
分类
- 常用的：主键、聚集索引、非聚集索引、唯一索引、组合索引
- 不常用：全文索引、包含列的覆盖索引、筛选索引、XML索引
联系
- 聚集索引与非聚集索引：以宾馆入住举例
- 主键：是唯一索引的特殊类型、默认主键为聚集索引；
- 聚集索引、非聚集索引，可以不唯一；
索引的存储结构
SQL Server使用下列三种方法之一来组织其分区中的数据或索引页：
- 索引是按 B树 结构组织的 (MySQL使用B+Tree作为索引结构)；
- 基础表按照聚集索引的顺序物理存储和排序，而非聚集索引的数据行不按非聚集键的顺序排序和存储。
聚集索引的叶节点包含基础表的索引页，而非聚集索引的叶节点是由索引页而不是由数据页组成。
由于聚集索引按B树存储数据，所以一个100万行完全不重复的表上建立聚集索引，搜索一笔资料最多扫描20次（2^20=1,048,576）即可找出，这也是为什么强制自增id为主键的原因。
堆是没有聚集索引的表；
- 非聚集索引：
- 非聚集索引与聚集索引有一个相似的B树索引结构
- 不同的是非聚集索引不影响数据行的顺序。叶级别仅包含索引行，没有数据。
| 表类型 | 存储结构 | sys.partitions |
|---|---|---|
| 未建索引 | 堆 | index_id = 0 |
| 聚集索引 | B树 | index_id = 1 |
| 非聚集索引 | B树 | index_id > 1 |
| 只有非聚集索引 | 堆+B树 | index_id = 0 + >1 |
查看 index_id 类型
SELECT * FROM sys.partitions WHERE OBJECT_NAME(object_id) = '表名'
索引的创建
语法
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table_name (column_name) [WITH FILLFACTOR = x, ONLINE = {ON|OFF}]
- UNIQUE 表示唯一索引
- CLUSTERED、NONCLUSTERED 表示聚集索引还是非聚集索引
- FILLFACTOR 表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比
- ONLINE = { ON | OFF } 指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为OFF，表示在索引维护期间不允许修改数据，但允许读操作；设为ON表示索引维护期间，可继续对基础表和索引进行查询或更新。
例如:
CREATE NONCLUSTERED INDEX idx_city ON ip2location(city) WITH (FILLFACTOR=90, ONLINE=ON)
索引的命名规范
| 索引类型 | 命名规范 | 备注 |
|---|---|---|
| 主键 | pk_表名 | |
| 普通索引 | idx_字段名 | |
| 唯一索引 | udx_字段名 | |
| 包含列索引 | idx_字段名@包含列 |
进阶
创建索引的原则
使用索引虽然可以提高系统的性能，增强数据的检索速度，但它需要占用大量的物理存储空间，建立索引的一般原则如下：
- 在频繁搜索的列上建立索引；
- 在经常被分组、排序的列上建立索引；
- 在外键引用的列上一定要建立索引；
不适合建立索引的场景：
- 重复值比较多\查询较少的列上不要建立索引，比如性别、状态值；
- 小表(少于100行)不适合建立索引，比如配置表；
如何理解组合索引的最左匹配原则
组合索引的最左匹配原则：
使用组合索引时，从左向右(并非where条件顺序)匹配，遇到范围查询(>、<、between、like)则会停止索引匹配，无法用到后续的索引列。where条件里面字段的顺序与索引顺序无关，优化器会自动调整顺序。
比如索引 idx_a_b_c(a,b,c)，相当于创建了(a)、(a,b)、(a,b,c)三个索引：
①where a=? 用到(a) ⑦where c=? 用不到
②where b=? and a=? 用到(a,b) ⑧where b=? and c=? 用不到
③where a=? and c=? 用到(a)
④where a>? and b=? 用到(a)
⑤where a=? and b=? and c>? 用到(a,b,c)
⑥where a=? and b>? and c=? 用到(a,b)
妙用筛选索引(fiter index)
举个场景
一张100万行的用户消息表，当用户读取消息时，通过将 mes_unread 字段从1 切换为0将其标记为已读。
CREATE TABLE user_messages(
mes_id bigint IDENTITY(1,1) NOT NULL,
mes_sender_usr_id bigint NOT NULL,
mes_receiver_usr_id bigint,
mes_title nvarchar(255),
mes_body nvarchar(2000),
mes_unread bit NOT NULL DEFAULT ((1)),
PRIMARY KEY CLUSTERED (mes_id ASC))
ON [PRIMARY]
假如应用用户非常活跃，收到消息后立即阅读消息，因此大部分消息已被阅读（95％），只有5％的消息尚未打开。当用户登入系统后，将显示未读消息。这种情况执行频繁最高的查询为：
SELECT *
FROM user_messages
WHERE mes_receiver_usr_id = ?
AND mes_unread = 1
目前，此表上只有 mes_id 上的主键索引。因此，执行计划显示 Clustered Index Scan 读取所有数据，以查找与条件匹配的行，其查询成本为 10.6818。
| name | rows | reserved | data | index_size | unused |
|---|---|---|---|---|---|
| user_messages | 1000000 | 103880 KB | 103456 KB | 392 KB | 32 KB |
使用 sp_spaceused 看到该表存储空间约 103 MB + 聚集索引约 392 KB。
传统办法
创建组合索引，执行计划显示使用 Index Seek + Key Lookup 替换 Clustered Index Scan，其查询成本为 1.20738，它比没有索引的快9倍。
CREATE NONCLUSTERED INDEX idx_mes_receiver_usr_id_unread
ON user_messages(mes_receiver_usr_id,mes_unread)
WITH (ONLINE=ON)
使用 sp_spaceused 看到该表索引约 15 MB。
| name | rows | reserved | data | index_size | unused |
|---|---|---|---|---|---|
| user_messages | 1000000 | 118800 KB | 103456 KB | 15296 KB | 48 KB |
此时，该表100万行，数据103MB，索引就已经15MB。若数据达到 10 GB，索引则有 1.5 GB 之大。那还有什么方法可以减少这个索引的数据量吗？
筛选索引
用过滤索引替换当前普通索引，执行计划与普通索引一样，但其执行成本下降到 0.246134，仅有传统索引的1/5。
DROP INDEX idx_mes_receiver_usr_id_unread ON user_messages;
CREATE NONCLUSTERED INDEX idx_mes_receiver_usr_id_unread
ON user_messages(mes_receiver_usr_id,mes_unread)
WHERE mes_unread = 1
为什么？答案是索引大小。使用 sp_spaceused 看到，与传统索引的 15 MB 相比，过滤索引仅 1.1 MB 明显更小（因为它不保存过滤条件以外的数据），它直接的效果就是：更小的索引、更少的IO、更小的执行成本、更快的搜索。
| name | rows | reserved | data | index_size | unused |
|---|---|---|---|---|---|
| user_messages | 1000000 | 104720 KB | 103456 KB | 1152 KB | 112 KB |
优缺点
- 优点：更小的索引、更快的搜索
- 缺点：更改WHERE子句中的值可能会使索引无用。例如查询已读消息：
SELECT * FROM user_messages WHERE mes_receiver_usr_id = 234 AND mes_unread=0
将条件从 mes_unread=1 更改为 mes_unread=0 会导致从 Index Seek + Key Lookup 切换回 Clustered Index Scan，因为过滤索引没有关于 mes_unread=0 行的任何信息，因此无法使用。
包含列(included column)的覆盖索引
先了解几个概念：
- 覆盖查询：当索引包含查询引用的所有列时，它通常称为“覆盖查询”。
- 索引覆盖：如果返回的数据列就包含于索引的键值中，那么就不会发生 Key Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了( 这也是为什么不建议select *的原因)。这种情况，叫做索引覆盖；
- 组合索引：和组合索引相对的就是单一索引了，就是索引只包含一个字段，所以组合索引就是包含两个或者多个字段的索引；
- 非键列：键列就是在索引中所包含的列，当然非键列就是该索引之外的列了
在 SQL Server 2005 中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。
当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。
几点说明
- 包含列的覆盖索引，只针对非聚集索引，不适用聚集索引；
- 来自包含列的值，不计入每个索引最多16列、900字节的限制；
- 比起组合索引是有性能上的提升的，因为索引的大小变小了；
举个例子
SELECT username,password,mobile,age FROM Users WHERE username = xxx AND age = xxx
- 1.这是一个我们很常见的查询语句，我们如何提高查询效率呢？
- 2.首先我们来看看谓词，这条语句是通过 username = xxx AND age = xxx 作为条件的，那么我们就应该建立一个组合索引，注意索引中的键列的位置，先 username 后 age；
- 3.其实上面那个是一个非聚集索引，那我们就可以把 password, mobile 这几列作为索引包含列；
- 4.所以，最终就是建立一个以 username 和 age 做为键列，以 password, mobile 作为非键列的非聚集索引；
CREATE NONCLUSTERED INDEX idx_username_age@password_mobile
ON Users (username, age)
INCLUDE (password, mobile)
WITH (ONLINE=ON)
读懂语句执行的统计信息
请参考：《读懂语句执行的统计信息》
索引的维护
定期重建碎片过高的索引
背景
- 对数据执行INSERT、UPDATE、DELETE操作，都会造成碎片；
- 碎片过高，会导致查询同样多数据需要检索的数据页变多，IO变高，查询就会更慢；
- 碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
解决办法
- 执行索引重建可提高DML语句执行效率。
几点注意事项
- 从效率上来看，一般碎片比例大于30%使用重新生成索引，大于5%小于30%使用重新组织索引，小于5%不必处理。
点评：这个要求其实有点高，建议>70%再重建索引
- 由于非聚集索引的叶层是由索引页而不是由数据页组成，所以重建非聚集索引并不能释放数据页的空间；而聚集索引的页层是由数据页组成，所以重建聚集索引才能释放数据空间。所以在进行DELETE删除数据后必须要重建该表的聚集索引才能使删除数据所占用空间释放。
- 可使用如下指令验证是否需要重建索引，主要看这项指标：Scan Density [Best Count:Actual Count]
dbcc showcontig(fee) with all_indexes - 如何联机执行索引重建或重新组织？
参考[3]： 重新组织和重新生成
- 索引重建会影响磁盘IO，建议放在凌晨3-4点执行；
- 生产系统在非维护期间重建聚集索引，请务必指定online，否则会有阻塞；
定期更新统计信息
统计信息对SQL Server正确预估执行计划的cost非常重要。默认情况下，当SQL Server发现有接近20%的记录发生改变时，它将自动更新统计。
语句参考
--更新数据库的所有统计信息
exec sp_updatestats;
--更新表的所有统计信息
update statistics dbo.fee with all;
--更新指定索引的统计信息
update statistics dbo.fee idx_createtime;
--通过使用 50% 抽样更新统计信息(默认根据需要采用抽样)
UPDATE STATISTICS dbo.fee(createtime) WITH SAMPLE 50 PERCENT;
- 参考[4]：UPDATE STATISTICS (Transact-SQL)
- 参考[5]：统计信息
最佳实践
组合索引的字段顺序有什么讲究
几个原则
- where条件里面字段的顺序与索引顺序无关，优化器会自动调整顺序。
- 建议将高度唯一的字段放到组合索引的最左侧
猜猜哪个效率好
SELECT * FROM ip2location WHERE province = 'jiangsu' AND city = 'kunshan'
- 索引1：idx_province_city，把重复率最高的字段(province)放到最左侧
- 索引2：idx_city_province，把重复率最低的字段(city)放到最左侧
新增/重建索引务必使用在线创建模式
当该实例为线上业务所依赖时，记录大于100W条的表只能在凌晨2点-6点进行，且使用在线创建模式(ONLINE=ON)。举例：
CREATE NONCLUSTERED INDEX idx_city ON ip2location(city) WITH(ONLINE=ON)
删除索引也有讲究
建议：先禁用，再删除。举例：
ALTER INDEX idx_city ON ip2location DISABLE
DROP INDEX idx_city ON ip2location
索引使用的误区
- SQL无法走索引常见的有如下8种情况：
1.统计信息不准确
2.索引列的值允许为NULL
3.谓词使用了不等于（<>, !=）
4.LIKE前通配或全通配的查询
5.索引列使用了函数、数学运算、其它表达式等
6.使用了隐式类型转换
7.查询转换失败
8.其它语句逻辑原因
- 索引可以提高不修改数据的查询（例如 SELECT ）的性能，但并非越多约好。一个表如果建有大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整。
- 对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。
为什么数据库会走错执行计划
- 参考：《为什么数据库会走错执行计划》