如何优化亿级数据表，和查询方面的优化

[toopcn]

ms sql 2014
aliyun服务器 8G 内存 60G数据盘（不可改变）

该数据库同时基本上只有一二个人同时链接

其中一个主表A有1000万条数据，另一个副表B，有1亿条数据
每天主表A中，数据不增加，但会有修改行为，大约修改100万条
每天副表B中，数据有增加和修改行为，数量不确定，大约在500~800万条之间

tableA
id phone name
1 13711112222 张三

tableB
id phone info time state
1 13711112222 在吃饭 2016-5-4 作废
2 13711112222 在拉屎 2018-11-13 正常
3 13722223333 清理屎 2018-5-1 正常


现在是在关联查询时出现超慢的情况（查一次超过10分钟以外）
比如条件，查出所有行为中 有 屎 字的，并且时间发生在2018-11-11号之后 2019-11-11号之前，已作废的除外

请大神看看这种该如何修改，才能比较快速的完成操作，大家可以留个QQ之类的，我给大家发个红包以表谢意

[toopcn]

[二月十六]

1、表关联和查询字段加索引。
2、改善硬件，升级内存和固态。

另外可以从业务查询修改修改方案。
尝试不要关联查询、分页查询等

[吉普赛的歌]

优化是一项比较细致的活， 需要得到足够的信息。 请楼主贴一下： 1. 主表和副表的表结构； 2. 慢的具体查询 sql . 如果能贴对应的执行计划截图就更好。

[吉普赛的歌]

3. 相关表的索引， 如果有的话。

[toopcn]

tableA 1000万


tableB 1亿


tableC 5000条


tableB中数据，diyname，存在大量重复情况，唯一性名称只有5000条，我已经用tableC表以diyid为关联，

每个tableA中数据，关联tableB数据在1~20行之间

SELECT   count(1)
FROM      dbo.tableA INNER JOIN
                dbo.tableB ON dbo.tableA.id = dbo.tableB.mid
				where taskid=1 and dbo.tableB.isold=1 and dbo.tableB.DIYID in(select DIYID from dbo.tableC where DIYNAME like '%吃饭%')

上面这条数据，用taskid=1为条件，查询tableA中的1万条数据，用时8秒，查询统计出2600条数据
取消taskid=1,查询全表1000万条数据，用时3分16秒，查询统计出300万数据

[toopcn]

SELECT count(1) FROM tableB
都会花费40多秒

[吉普赛的歌]

--查询 3 个表的索引情况，截图出来
EXEC sp_helpindex 'tableA' --实际表名请自己替换一下
EXEC sp_helpindex 'tableB'
EXEC sp_helpindex 'tableC'

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('tableA') IS NOT NULL DROP TABLE tableA
IF OBJECT_ID('tableB') IS NOT NULL DROP TABLE tableB
IF OBJECT_ID('tableC') IS NOT NULL DROP TABLE tableC
GO
--1000万 每个tableA中数据，关联tableB数据在1~20行之间
CREATE TABLE tableA(
	id INT PRIMARY KEY,
	number VARCHAR(11) NOT NULL,
	realname NVARCHAR(50) NOT NULL,
	diy NVARCHAR(50) NOT NULL,
	diytime DATETIME NOT NULL,
	addTime DATETIME NOT NULL,
	uptime DATETIME NOT NULL,
	taskid INT NOT NULL
)
--1亿 tableB中数据，diyname，存在大量重复情况，唯一性名称只有5000条
CREATE TABLE tableB(
	id INT PRIMARY KEY,
	diyid VARCHAR(20) NOT NULL,
	diyType VARCHAR(20) NOT NULL,
	diyname NVARCHAR(100) NOT NULL,
	effectiveDate DATETIME NOT NULL,
	[EXPIREDATE] DATETIME NOT NULL,
	number VARCHAR(11) NOT NULL,
	mid INT NOT NULL,
	addTime DATETIME NOT NULL,
	uptime DATETIME NOT NULL,
	isold INT NOT NULL
)
CREATE INDEX ix_tableB_mid_diyid_EXPIREDATE ON tableB(mid,diyid,[EXPIREDATE])
--5000
CREATE TABLE tableC(
	id INT PRIMARY KEY,
	diyid VARCHAR(13) NOT NULL,
	diyname NVARCHAR(60) NOT NULL,
	diyType VARCHAR(30) NOT NULL,
	islock INT NULL
)
GO
---- 以上为创建测试表 ----

---- 以下为修改后的查询 
SELECT DIYID 
INTO #tmp 
FROM dbo.tableC
WHERE  DIYNAME LIKE '%吃饭%'
CREATE CLUSTERED INDEX ix_#tmp ON #tmp(diyId)

SELECT COUNT(1)
FROM   dbo.tableA AS a WITH(NOLOCK)
       INNER JOIN dbo.tableB AS b WITH(NOLOCK)
            ON  a.id = b.mid AND a.taskid=1 AND b.isold=1
	   INNER JOIN #tmp AS tmp ON tmp.DIYID=b.diyid

--删除临时表
DROP TABLE #tmp;

--上面的脚本可能会比你原来快一点，但要秒出是不可能。
--优化， 本身只能是万里挑一。
--你这种 sql ，基本上没有过滤多少数据， 相当于扫全表，还要几个表连接。
--几乎没有更快的可能了，换 mysql, oracle 也一样。
--除非你加内存、换 SSD 硬盘并组 raid 。
--数据量这么大， 肯定只有一部分数据是需要的， 你要从业务上分析：只有哪些数据是必要的。
--建议从时间上来过滤， 比如：只取 1 个月内的数据……
--总之，最好是加上时间来过滤，并在时间字段上创建索引。
--注：除了主键的索引，前面不要加 id 
--另外一个办法是做成报表， 凌晨处理好数据， 并存放到结果表， 查询时只查报表相关的结果表即可。

[笨蛋girl]

第一：你试试这种写法： SELECT count(*) FROM dbo.tableA as a
INNER JOIN dbo.tableB as b ON b.id = b.mid
INNER join dbo.tableC as c on c.DIYID=b.DIYID
where taskid=1 and b.isold=1 and c.DIYNAME like '%吃饭%'

第二：你针对于上面的语句添加下索引 具体你可以用执行计划或者引擎顾问

或者你可以根据经验进行索引的添加

第三：你看下 针对于like查询 是否是完全必要的操作，毕竟like会引发全盘扫描

[leo_lesley]

频繁的增、删、改会对索引造成影响，可以试试重建索引试试看

DBCC SHOWCONTIG ('表名');	--查看表索引的碎片信息
dbcc dbreindex('('表名')	--重新索引
DBCC INDEXDEFRAG (数据库名, 表名, 索引)	--索引碎片整理

[toopcn]

谢谢各位，我试试你们的办法