sqlserver2016 百万级数据查询优化
source link: https://www.oschina.net/question/1864890_2322791
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
sqlserver2016 百万级数据查询优化
情况说明：
表tab_testa、tab_testb、tab_testc，其中tab_testa百万级，tab_testb接近百万，tab_testc20W左右数据，三张表的主键都是uuid的，语句大概如下：
SELECT
testa.id,
testa.field1,
testa.field2,
testa.field3,
testa.field4,
testa.field5
testb.field1,
testc.field1,
testc.field2,
testc.field3
FROM
tab_testa testa
LEFT JOIN tab_testb testb ON testa.field1 = testb.field1
LEFT JOIN tab_testc testc ON testa.field1 = testc.field1
WHERE
testa.field5 IN ('1')
AND testa.status IN (0, 3)
AND CONVERT (
VARCHAR (100),
testa.CREATE_TIME,
23
) >= CONVERT (VARCHAR(100), DATEADD(DAY, -20, GETDATE()), 23)
AND CONVERT (
VARCHAR (100),
testa.CREATE_TIME,
23
) <= CONVERT (VARCHAR(100), GETDATE(), 23)
ORDER BY
CREATE_TIME DESC offset 5000 ROWS FETCH NEXT 10 ROWS ONLY ；
目前问题如下：
1、三张表的连接字段已经加上索引，testa的CREATE_TIME也加上索引
2、分页采用的是offset 5000 ROWS FETCH NEXT 10 ROWS ONLY 这种方式，越往后越慢；
3、count(*) 函数也很慢
请问这个sql如何优化好
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK