sql 性能优化 索引碎片
1、索引
简单的说，索引就像书本的目录，目录可以快速找到所在页数，数据库中索引可以帮助快速找到数据，而不用全表扫描，合适的索引可以大大提高数据库查询的效率。
(1). 优点
大大加快了数据库检索的速度，包括对单表查询、连表查询、分组查询、排序查询。经常是一到两个数量级的性能提升，且随着数据数量级增长。
(2). 缺点
索引的创建和维护存在消耗，索引会占用物理空间，且随着数据量的增加而增加。
在对数据库进行增删改时需要维护索引，所以会对增删改的性能存在影响。
a. 直接创建索引和间接创建索引
直接创建: 使用sql语句创建，Android中可以在SQLiteOpenHelper的onCreate或是onUpgrade中直接excuSql创建语句，语句如
间接创建: 定义主键约束或者唯一性键约束，可以间接创建索引，主键默认为唯一索引。
b. 普通索引和唯一性索引
普通索引：
唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用，语句为
c. 单个索引和复合索引
单个索引：索引建立语句中仅包含单个字段，如上面的普通索引和唯一性索引创建示例。
复合索引：又叫组合索引，在索引建立语句中同时包含多个字段，语句如：
CREATE INDEX name_index ON username(firstname, lastname)
其中firstname为前导列。
d. 聚簇索引和非聚簇索引(聚集索引，群集索引)
聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列，语句为：
其中WITH ALLOW_DUP_ROW表示允许有重复记录的聚簇索引
非聚簇索引：
索引默认为非聚簇索引
(4). 使用场景
在上面讲到了优缺点，那么肯定会对何时使用索引既有点明白又有点糊涂吧，那么下面总结下：
a. 当某字段数据更新频率较低，查询频率较高，经常有范围查询(>, <, =, >=, <=)或order by、group by发生时建议使用索引。并且选择度越大，建索引越有优势，这里选择度指一个字段中唯一值的数量/总的数量。
b. 经常同时存取多列，且每列都含有重复值可考虑建立复合索引
、查看数据库表中索引的情况
select i.name AS '索引名称' ,o.name AS TableName,avg_fragmentation_in_percent,*
FROM sys.dm_db_index_physical_stats(DB_ID() ,object_id('agent') ,NULL,NULL,NULL)
inner join sys.indexes i on i.object_id=dm_db_index_physical_stats.object_id
INNER JOIN sys.objects o ON o.object_id = i.object_id
查询结果重要字段解释：
| database_id | 表或视图的数据库 ID | ||
| TableName | 表名称 | ||
| index_level |
索引的当前位于B树结构中的级别。 0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。 大于 0 的值表示非叶索引级别。 index_level 在索引的根级别中属于最高级别。 仅当 mode = DETAILED 时才处理非叶级别的索引。 |
||
| avg_fragmentation_in_percent |
索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。 此值按百分比计算，并将考虑多个文件。 0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。 如果是堆表且mode模式 为 Sampled 时，为 NULL。如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。 |
||
| page_count |
索引或数据页的总数。 对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。 对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示该分配单元中的总页数。 |
||
| record_count |
总记录数。 对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树(包括非叶子数据页的数量)的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的总记录数。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。 当 mode 为 LIMITED 时，为 NULL。 |
||
| index_id |
索引的索引 ID。 0 = 堆。 |
2、关于碎片的解决方法：
1.删除索引并重建
这种方式有如下缺点：
索引不可用：在删除索引期间，索引不可用。
阻塞：卸载并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞。
对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针)。
唯一性约束：用于定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除。而且，唯一性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法。
基于以上原因，不建议在生产数据库，尤其是非空闲时间不建议采用这种技术。
2.使用DROP_EXISTING语句重建索引
为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞。
CREATE UNIQUE CLUSTERED INDEX IX_C1 ON t1(c1) WITH (DROP_EXISTING = ON)
缺点:
阻塞：与卸载重建方法类似，这种技术也导致并面临来自其他访问该表(或该表的索引)的查询的阻塞问题。
使用约束的索引：与卸载重建不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重新创建使用约束的索引。如果该约束是一个主键或与外键相关的唯一性约束，在CREATE语句中不能包含UNIQUE。
具有多个碎片化的索引的表：随着表数据产生碎片，索引常常也碎片化。如果使用这种碎片整理技术，表上所有索引都必须单独确认和重建。
3.使用ALTER INDEX REBUILD语句重建索引
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长。
阻塞：这个依然有阻塞问题。
事务回滚：ALTER INDEX REBUILD完全是一个原子操作，如果它在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以通过ONLINE关键字减少锁，但会造成重建时间加长。
4.使用ALTER INDEX REORGANIZE
这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种。
4种索引整理技术比较：
| 特性/问题 | 卸载并重建索引 | DROP_EXISTING | ALTER INDEX REBUILD | ALTER INDEX REORGANIZE |
| 在聚集索引碎片整理时，重建非聚集索引 | 两次 | 无 | 无 | 无 |
| 丢失索引 | 是 | 无 | 无 | 无 |
| 整理具有约束的索引的碎片 | 高度复杂 | 复杂性适中 | 简单 | 简单 |
| 同时进行多个索引的碎片整理 | 否 | 否 | 是 | 是 |
| 并发性 | 低 | 低 | 中等，取决于冰法用户活动 | 高 |
| 中途撤销 | 因为不使用事务，存在危险 | 进程丢失 | 进程丢失 | 进程被保留 |
| 碎片整理程度 | 高 | 高 | 高 | 中到低 |
| 应用新的填充因子 | 是 | 是 | 是 | 否 |
| 更新统计 | 是 | 是 | 是 | 否 |
