单表 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的表,可以节省很多空间。就是开发的时候有点麻烦。
参考