sql大数据多条件查询索引优化
2023-09-11 14:19:21 时间
此优化的前提可以称之为最近流行的头条人物“许三多”,总数据多,查询条件多,返回列多
优化前分页查询内部select列为需要的全部列,优化后内部select只返回ID主键,外部查询关联原数据表,然后查出所需要的列
例子1
优化前:
- select t.* from (
- select r.* ,row_number() over(order by r.id desc) row from tab(nolock) r
- where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000
- ) as t where row between 1 and 10
select t.* from ( select r.* ,row_number() over(order by r.id desc) row from tab(nolock) r where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000 ) as t where row between 1 and 10
优化后:
- select r.* from (
- select r.ID ,row_number() over(order by r.id desc) row from tab(nolock) r
- where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000
- ) as t join tab r on r.id=t.id where row between 1 and 10
select r.* from ( select r.ID ,row_number() over(order by r.id desc) row from tab(nolock) r where 1=1 and r.IsDelete=0 and r.Status>0 and r.PlatformID=1 and r.CreateUser=100000 ) as t join tab r on r.id=t.id where row between 1 and 10
最近又有一个例子
例子2
优化前:tablA数据量1千多万,tablB数据量几百万,查询速度11秒多
- select * from (
- select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime,row_number() over(order by d.id desc) row
- from tablA(nolock) d
- join tablB(nolock) p on p.id=d.lessonplanid
- where p.createID in(109486,103295,103298,109347,130346,181382,330312)
- ) t where t.row between 1 and 20
select * from ( select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime,row_number() over(order by d.id desc) row from tablA(nolock) d join tablB(nolock) p on p.id=d.lessonplanid where p.createID in(109486,103295,103298,109347,130346,181382,330312) ) t where t.row between 1 and 20
优化后:查询速度14毫秒
- select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime from (
- select d.id,row_number() over(order by d.id desc) row
- from tablA(nolock) d
- join tablB(nolock) p on p.id=d.lessonplanid
- where p.createID in(109486,103295,103298,109347,130346,181382,330312)
- ) t join tablA(nolock) d on d.id=t.id join tablB(nolock) p on p.id=d.lessonplanid
- where t.row between 1 and 20
select d.LessonPlanID,d.ResUrl,p.createID,p.CreateTime from ( select d.id,row_number() over(order by d.id desc) row from tablA(nolock) d join tablB(nolock) p on p.id=d.lessonplanid where p.createID in(109486,103295,103298,109347,130346,181382,330312) ) t join tablA(nolock) d on d.id=t.id join tablB(nolock) p on p.id=d.lessonplanid where t.row between 1 and 20
相关文章
- 【SQL 学习】排序问题之order by与索引排序
- SQL SERVER 中is null 和 is not null 将会导致索引失效吗?
- Mysql第五天 索引
- sql 语句系列(列举非索引外键)[八百章之第九章]
- SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句
- mysql索引类型 normal, unique, full text
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- 华为OD机试 - 按索引范围翻转文章片段(Java & JS & Python)
- mysql索引_效率测试(包含测试sql脚本300万条数据),可用作教学案例。
- 【sql优化】(大表小技巧)有时候 2 小时的 SQL 操作,可能只要 1 分钟
- sql server 创建索引 超时时间已到
- 但从谈论性能点SQL Server选择聚集索引键
- 数据库索引的作用和长处缺点
- Linq 中带有迭代索引的 Select 扩展方法 **惊喜发现**,要多研究源码哦
- 高效管理 Elasticsearch 中基于时间的索引——本质是在利用滚动模式做数据的冷热分离,热索引可以用ssd
- elasticsearch indices.recovery 流程分析(索引的_open操作也会触发recovery)——主分片recovery主要是从translog里恢复之前未写完的index,副分片recovery主要是从主分片copy segment和translog来进行恢复
- 视图,索引,表的区别【以Mysql为例】