记录一个索引导致的慢查询

单表 4.285G，50W行数据

表结构大概这样子
CREATE TABLE [dbo].[activity_log](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [dom_id] [int] NULL,
    [user_id] [int] NULL,
    [type] [nvarchar](135) NULL,
    ...略
) ON [PRIMARY]

最初运行最简单都要10分钟，如

select count(1) from activity_log;

select count(id) from activity_log_new where id  > 0 and id <700000; -- 耗时12:00分钟
Table 'activity_log_new'. Scan count 5, logical reads 636553, physical reads 0, read-ahead reads 464081, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

发现read-ahead reads 464081，预读特别高。

read-ahead reads 为进行查询而放入缓存的页数。

估计是聚焦索引导致的。

聚集索引的存储是以B树存储，B树的叶子直接存储聚集索引的数据:

所以，即使是仅仅想count一下，它也会把读取所有的叶子节点，导致大量的IO操作。(猜的)

最后，把集束索引(id)删除，改为非集束索引就解决了。

非聚集索引，本质上来说也是聚集索引的一种.非聚集索引并不改变其所在表的物理结构，而是额外生成一个聚集索引的B树结构，但叶子节点是对于其所在表的引用,这个引用分为两种，如果其所在表上没有聚集索引，则引用行号。如果其所在表上已经有了聚集索引，则引用聚集索引的页.

非聚集索引就是需要额外的空间。

修改后的结果

select count(1) from activity_log;
cost time: 888 ms
Table 'activity_log_test'. Scan count 1, logical reads 606, physical reads 3, read-ahead reads 598, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

一些用到语句

 DBCC DROPCLEANBUFFERS   --清除缓冲区
 DBCC FREEPROCCACHE   删除计划高速缓存中的元素
 SET STATISTICS TIME ON  --执行时间
 SET STATISTICS IO ON  --IO读取

另外，在设计表的时候，对于一些重复不多的数据，如'male','femal'的性别，省份什么的，用数字类型，查询时会快很多，也省空间。如果上G的表，可以节省很多空间。就是开发的时候有点麻烦。

参考

T-SQL查询进阶--理解SQL Server中索引的概念，原理以及其他

[SQL基础]统计信息解释

SQL性能优化前期准备-清除缓存、开启IO统计

转载于:https://my.oschina.net/yespsy/blog/1570000