盘点SqlServer 分页方式和拉姆达表达式分页

目录SQLServer分页方式1.Offset and Fetch 方式分页2.row_number() 方式分页3.row_number()变种4.not in/top方式分页5.not exists方式分页6.max/top方式分页SQLServer分页方式 附带50万数据分页时间[本机访问|已重启SQL服务|无其他程序干扰][非索引排序]环境 WIN7 SQL服务12.04 SQL管理器SSMS14.01 1.Offset and Fetch 方式分页 [SqlServer2012+] OFFSET 起始值 ROWS FETCH NEXT 每页条数 ROWS ONLY 1.分页条件不能重复，建议使用 分页条件+主键 一起作为 分页参数2.分页语句中 FIRST=NEXT ROWS=ROW3.不使用排序可以用 order by (select 1) 使用 OFFSET FETCH 方式分页必须带order by key 其中key 必须是唯一值 不然会导致错误 select * from ( select * from sysrizhi ) table_temp order by riqi desc,sysrizhiid desc offset 10 rows fetch next 10 rows only 50万行执行时间 770毫秒 索引排序10毫秒 select * from tcpliushui order by riqi OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY 2.row_number() 方式分页 [SqlServer2005+] row_number()over(order by 已有排序字段)rownumber 然后根据rownumber筛选 使用方式 select * from (select *, ROW_NUMBER() Over( order by riqi desc,sysrizhiid desc ) as rowNum from ( select * from sysrizhi ) as table_1 ) as t where t.rowNum>10 and t.rowNum<=20 速度测试 50万行执行时间 774毫秒 索引排序10毫秒 select * from (select row_number()over(order by riqi)rownumber,* from tcpliushui )a where rownumber>100 and rownumber<121 速度测试 50万行执行时间 777毫秒 索引排序13毫秒 select top 20 * from (select row_number()over(order by riqi)rownumber,* from tcpliushui)a where rownumber>100 速度测试 50万行执行时间 775毫秒 索引排序15毫秒 select * from (select row_number()over(order by riqi)rownumber,* from tcpliushui)a where rownumber between 101 and 120 3.row_number()变种 不基于已有字段 [SqlServer2005+] 速度测试 50万行执行时间 779毫秒 索引排序15毫秒 select * from ( select row_number()over(order by tempColumn)rownumber,* from (select top 120 tempColumn=0,* from tcpliushui where 1=1 order by riqi)a )b where rownumber>100 4.not in/top方式分页 速度测试 50万行执行时间 858毫秒 索引排序51毫秒 select top 20 * from tcpliushui where tcpliushuiid not in (select top 100 tcpliushuiid from tcpliushui order by riqi) order by riqi 5.not exists方式分页 速度测试 50万行执行时间 852毫秒 索引排序22毫秒 select top 20 * from tcpliushui where not exists (select 1 from (select top 100 tcpliushuiid from tcpliushui order by riqi)a where a.tcpliushuiid=tcpliushui.tcpliushuiid) order by riqi 6.max/top方式分页 速度测试 50万行执行时间 819毫秒 索引排序12毫秒 select top 20 * from tcpliushui where tcpliushuiid>(select max(tcpliushuiid) from (select top 100 tcpliushuiid from tcpliushui order by riqi)a) order by riqi 到此这篇关于SqlServer 多种分页方式 (含简单速度测试)的文章就介绍到这了,更多相关SqlServer 分页方式内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！

目录方法一：三重循环 思路方法二：利用max（主键）方法三：利用row_number关键字第四种方法：offset /fetch next（2012版本及

目录1、ROW_NUMBER() OVER()方式（SQL2012以下推荐使用）2、offset fetch next方式（SQL2012及

目录1.简介2.分页插件使用3.sql测试与分析3.1 limit现象分析3.2 解决之道4 测试时走过的坑4.1 百万数据内容都一样4.2 写sql时，把"77"写成了77；4.3 一个有趣的现象总结1.简介 之前，面阿里的时候，有个面试官问我有没有使用过分页查询，我说有，他说分页查询是有问题的，怎么解决；后来这个问题我没有回答出来；本着学习的态度，今天来解决一下这个问题； 2.分页插件使用 1.pom文件 <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>4.1.6</version> </dependency> 2.创建分页配置器 @Configuration public class PageHelperConfig { @Bean public PageHelper pageHelper() { PageHelper pageHelper = new PageHelper(); Properties p = new Properties(); p.setProperty("offsetAsPageNum", "true"); p.setProperty("rowBoundsWithCount", "true"); p.setProperty("reasonable", "true"); pageHelper.setProperties(p); return pageHelper; } } 测试代码： @Test void test() { PageHelper.startPage(400000,10,"id desc"); List<UploadData> users = userMapper.queryAll(); System.out.println(users.size()); System.out.println(users); for (UploadData uploadData: users) { System.out.println(uploadData); } } 4.重写sql分析 debug 后可以查看它是通过重写sql来实现分页功能； 重写后的sql语句为"SELECT * FROM amj_devinfo order by id desc limit ?, ?";limit a, b；// 跳过前a条数据，取b条数据；所以，其实现在问题就是回到了，执行这条sql语句所需要花费多少的问题了； 3.sql测试与分析 select * from amj_devinfo order by id limit 2000, 20; // 0.027s select * from amj_devinfo order by id limit 20000, 20; // 0.035s select * from amj_devinfo order by id limit 200000, 20; // 0.136s select * from amj_devinfo order by id limit 2000000, 20; // 1.484s select * from amj_devinfo order by devaddress limit 2000000, 20; // 7.356 全表扫描 + filesort； 结论：如果说，是小的数据量的话，使用该分页完全没问题；当数据量到达两百万的时候，执行时间就得为6.729s了，对于用户来说，这是不可接受的； 3.1 limit现象分析 使用explain对sql先来分析一波；感兴趣的同学可以看看我的另一篇文章 MySQL结合explain分析结果如下： 针对，select * from amj_devinfo order by id limit 2000, 20来说： 可以看到，使用的是基于索引树 + 回表的方法来获取数据的，顺序IO查询列数为：2000020； 首先，根据阿里Java开发手册，type为index 就已经不可接受了；最低标准为range；而且，它是order by id 能够使用上主键索引，要是order by '其他列（无索引）如devaddress' 这个时候，就是全表扫描 + filesort，效率更慢；备注： select * from amj_devinfo order by id limit 2000000, 20; 这条语句是 方案一 ：先通过id找到2000000，然后，剩下的20条再全表扫描；还是，方案二： 通过id回表直接找到2000020条，然后，放弃前2000000条；理论上剩下20条进行全表扫描肯定是快很多的；但是，有点尴尬。Mysql选择的其实是方案二； 3.2 解决之道 很显然，现在已经是发现了问题所在，我们需要对其进行解决；我们对下面的sql语句来进行升级； 测试背景：1.mysql 数据表中有5695594 (五百万)条数据，在devcho中数据相对离散。2.表的设计如下： 有需要测试的同学，可以按照我表设计来模拟测试； select * from amj_devinfo where devcho = "77" limit 20000, 10; 3.2.1 对devcho建立索引 很显然，通过sql来查询的话，对devcho建立索引的话，可以把全表扫描升级为基于索引列的扫描；能提升一个量级；索引建立结果如下： 执行sql语句： 执行时间8.415s 这个时间是不可以接收的； 3.2.2 sql执行时间长分析 经过多次测试，发现时间都是很久，那么，就不会是Mysql 刷脏页，而且，数据库空闲，没有别的sql与其竞争磁盘IO 而且，通过MVCC查找数据也不存在锁相关问题；所以，问题肯定是出现在sql语句上；那么，为什么会出现这个问题呢？ -- 答案是回表这条sql语句是怎么执行的呢？ 先基于devcho的索引列，找到devcho='77'的这一行；在通过devcho中存的主键id,然后，回表找所有的数据；找20010条数据； 这时候，问题就出现了，这个回表的过程是随机IO；这个随机IO效率是很低的；所以，undo log要把随机IO变成顺序IO。这里，就是最大的瓶颈所在；扫描条数验证： Handler_read_next: 该选项表明在进行索引扫描时，按照索引从文件数据里取数据的次数； 回表是sql瓶颈验证: 查找主键id,不需要回表，发现0.01s就可以搞定；证明了sql导致的回表就是瓶颈所在； 3.2.3 解决之道 我们刚刚发现，因为limit比较笨。select * from amj_devinfo where devcho = "77" limit 20000, 10;需要回表20010次；但是，我们只需要它回表10次啊。所以，我们可以先把符合条件的id找出来；再根据id使用inner join 去进行回表；sql语句如下： select * from amj_devinfo a INNER JOIN (select id from amj_devinfo where devcho = "77" limit 20000, 10) b on a.id = b.id; 查询时间：0.025s 这个时候，就可以达到我们的要求了；这个联结是会产生笛卡尔积的。检索出来行的数目是第一个表中的行数乘以第二个表中的行数，以前，感觉挺慢的，这也证明，如果没有文件排序或者临时表的话，效率其实还可以； 4 测试时走过的坑 在测试的时候，其实我犯了两个错，卡了自己好几个小时，证明测试都不对；特此记录一下，给想复现现象的同学提个醒； 插入百万条数据数据内容相同；在执行sql时，格式没有对应上，导致索引失效select * from amj_devinfo where devcho = 77 limit 20000, 10; 77是字符，我输入为整型； 4.1 百万数据内容都一样 select * from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.042s select id from amj_devinfo where devcho = "2212" limit 20000, 10; // 0.026s 还是上面的语句，只是数据内容是一样的；为什么两者时间是一个级别？为什么会产生这种现象呢？ 因为数据都一样的devcho 索引其实是没有用的；成为链表了；第一条语句，找出20010条语句就找到内容了，因为，都存在一起 都在一个或者几个页表中，随机IO升级为顺序IO，是有回表，但是，顺序IO的回表也是很快的。 所以，效率很高；即，第一条语句和第二条语句花的时间是差不多的； 4.2 写sql时，把"77"写成了77； 现象再现： select id from amj_devinfo where devcho = 77 limit 20000, 10; // 查询时间2.064s select * from amj_devinfo where devcho = 77 limit 20000, 10; // 查询时间3.716s 这里 第一条语句因为字段比第二条语句中少；所以，放入sort_buffer中的数据是不同的；问题回顾：我之前就在想，为什么我基于索引列查询id会这么慢？我当时没想到索引失效问题；后来，我是怎么发现这个问题的呢？因为，基于索引列查询的时候，Mysql要扫描的字段也就是20010条数据即可；而我查看Handler_read_next（此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数）时， Handler_read_next 4274160 explain分析结果： 如果，扫描这么多行，需要这么多时间是可以理解的，那么，为什么需要扫描这么多行呢？ 我那时候，重新看了一下表的设计，发现原来devcho字段的类型是varchar；这个时候，就想到了索引失效这个问题； 4.2.1 为什么会索引失效？ 既然，发现了类型不同导致索引失效，那么就分析一下，为什么会导致索引失效？这条sql又将如何执行？ 因为，他是基于索引列找的。但是，由于77 != '77'所以，这就导致了索引实现；但是，最终它还是找到了数据，这个时候，结合了扫描行数，我个人感觉应该是采用了全表扫描，然后，通过，强制类型转换,cpu进行判断，查询所得；当改成 select id from amj_devinfo where devcho = "77" limit 20000, 10;就没有这个问题了；扫描的行数为20009行； 所以，在写sql语句的过程中还是要注意啊；字段为varchar 传入 int 会索引失效，那么，字段为bigint 传入 "String" 会失效吗？经过测试：不会失效； 所以，在Mybatis中，可以放心使用#{}占位符了； 4.3 一个有趣的现象 大扫描行数 VS 随机IO select * from amj_devinfo where devcho = 77 limit 20000, 10; 查询时间 3.311s select * from amj_devinfo where devcho = "77" limit 20000, 10; 查询时间 3.188s 第一个sql扫描的行数是500多万行； 但是，由于每个行都需要读入内存中，使用的是顺序IO 第二个sql扫描的行数是20010行，但是，需要访问随机IO 20010次；其实，基本上也就把所有的页表都找了一次；小总结：随机IO，查询次数都要避免； 总结 本文，主要是模拟了分页查询中，往后数据查询较慢的现象，以及分析了速度较慢的原因；limit导致随机回表数增多。并提供了解决方法，先找到符合条件的id；然后，根据id做内联查询，减少随机IO的次数；并且，总结了一下自己出现的问题以及原因；如果，有一些个人见解不一定正确的话，希望大家多多指正； 到此这篇关于浅谈Mysql大数据分页查询解决方案的文章就介绍到这了,更多相关Mysql大数据分页查询 内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！

开发中难免会遇到宽度很窄的列表需要使用分页器的情况，这时若使用Element Plus组件的分页器会导致分页器内容超出展示的区域，而Element Plus组件中目前没有Acro Design那样小巧的分页器（Arco Design Vue）如下图所示，如果再引入一个新的UI组件库未免导致项目臃肿，所以基于Vue3.x和Element Plus封装了一个即拿即用的”简洁模式“分页器组件以便不时之需 分页器组件代码部分： <!-- （简洁模式）分页器组件 --> <template> <div class="smallpagination"> <!-- 总数统计 --> <span>{{ '共' + total + '条' }}</span> <!-- 翻页 --> <div class="smallpagination-pager"> <!-- 左翻页 --> <el-icon @click="pageTurning('down')" :class="curPage <= 1 ? 'forbid-pageturning' : ''"> <ArrowLeft /> </el-icon> <!-- 页码 --> <el-input-number @change="handlePageChange" v-model="pageNum" :min="1" :max="pageTotal" :step-strictly="true" :controls="false" /> <b>{{ '/ ' + pageTotal }}</b> <!-- 右翻页 --> <el-icon @click="pageTurning('up')" :class="curPage >= pageTotal ? 'forbid-pageturning' : ''"> <ArrowRight /> </el-icon> </div> </div> </template> <script setup> import { useAttrs, computed, ref } from 'vue'; import { ArrowLeft, ArrowRight } from '@element-plus/icons-vue'; // 接收父组件参数 const attrs = useAttrs(); // 父组件事件 const em = defineEmits(['handlePageChange']); // 当前页 const pageNum = ref(1); // 父组件传递-当前页码 const curPage = computed(() => { pageNum.value = attrs.curPage; return attrs.curPage; }); // 父组件传递-总数 const total = computed(() => { return attrs.total; }); // 总页码数 const pageTotal = computed(() => { return attrs.total > 0 ? Math.ceil(attrs.total / attrs.pageSize) : 1; }); /* 改变页码 */ const handlePageChange = (e) => { if (pageTotal.value <= 1) { return; } em('handlePageChange', e); }; /* 翻页 */ const pageTurning = (type) => { // 向前翻页 if (type === 'up') { if (curPage.value >= pageTotal.value || pageTotal.value <= 1) { return; } em('handlePageChange', pageNum.value + 1); } // 向后翻页 else { if (pageTotal.value <= 1 || curPage.value <= 1) { return; } em('handlePageChange', pageNum.value - 1); } }; </script> <style lang="less" scoped> .smallpagination { width: auto; height: 100%; display: flex; align-items: center; >span { margin-right: 11px; font-size: 14px; font-weight: 400; color: #4E5969; line-height: 21px; } .smallpagination-pager { display: flex; align-items: center; .el-icon { width: 30px; height: 30px; font-size: 14px; color: #4E5969; cursor: pointer; &:hover { background: rgb(247, 248, 250); color: #0082ff; } } .forbid-pageturning { opacity: 0.4; cursor: not-allowed; &:active { color: #4E5969; background: rgb(255, 255, 255); } } >b { margin: 0 5px; font-size: 14px; font-weight: 400; color: #4E5969; } } } </style> <style lang="less"> .smallpagination { .smallpagination-pager { .el-input-number { width: 40px; margin-left: 5px; span { display: none; } .el-input__wrapper { padding: 0; height: 30px; font-size: 14px; box-sizing: border-box; background: #f2f3f5; box-shadow: none !important; } } } } </style> 使用简洁模式分页器组件代码如下： <template> <div class="xxx-list"> ... <div class="list-bottom-common-page"> <SmallPagination :total="total" :curPage="curPage" :pageSize="pageSize" @handlePageChange="handleCurrentChange"> </SmallPagination> </div> </div> </template> <script setup> import SmallPagination from '@/components/xxx/SmallPagination.vue'; import { ref } from 'vue'; // 当前页 const curPage = ref(1); // 每页条数 const pageSize = ref(20); // 列表总数 const total = ref(0); /* 当前页改变 */ const handleCurrentChange = (val) => { curPage.value = val; ... }; </script> 最终效果如下： 到此这篇关于Vue3.x+Element Plus仿制Acro Design简洁模式实现分页器组件的文章就介绍到这了,更多相关Vue Element分页器内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！

目录SQL Server临时表的正确删除方式1、错误的删除操作：2、正确的删除方式：补充：SQL临时表的使用示例删除临时表SQL Server临时表的正确删除方式 临时表与一般的表不同，它是保存到tempDb表中。临时表的表名与你所建的表名也不一样，因为他要为不同人的相同操作创建不同的临时表。下文将为您分别示例正确和错误的删除操作，供您参考。 删除SQL Server临时表和一般表并不相同，下面将为您为别示例错误和正确的删除操作，供您参考，希望对您能够有所帮助。 临时表与一般的表不同，它是保存到tempDb表中。临时表的表名与你所建的表名也不一样，因为他要为不同人的相同操作创建不同的临时表。 1、错误的删除操作： --错误的临时表删除操作，因为所在数据库不同 IF EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'[dbo].[#tempTable]') AND type in (N'U')) Begin DROP TABLE [dbo].[tempTable] End --错误的临时表删除操作,因为临时表名已变 if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'[#temptable]')) Begin drop table #temptable End 2、正确的删除方式： --正确的临时表删除操作 if object_id('tempdb..#tempTable') is not null Begin drop table #tempTable End 补充：SQL临时表的使用 关系型数据库支持临时表，这是一项很棒的功能。临时表的概念由 SQL Server 引入，用来存储和处理中间结果。 临时表在会话期间创建，会话结束后自动被删除。临时表可以和普通表一样执行各种操作，比如 SELECT、UPDATE、INSERT、JOIN 等。 MySQL 3.23 及其更高版本才支持临时表，如果您使用的 MySQL 版本低于 3.23，则不能使用临时表，但可以使用堆表（Heap Table）。 如前所述，临时表仅存在于会话期间。如果您使用 PHP 脚本连接数据库，当 PHP 执行完成后，临时表将被销毁。如果您使用 MySQL 客户端连接数据库，当客户端关闭后，临时表将被销毁。 创建临时表的基本语法如下： CREATE TEMPORARY TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); 您看，创建临时表的语法和普通表极其相似。临时表创建完成以后，可以使用 INSERT、DELETE、UPDATE、SELECT 等命令进行增删改查操作。 示例 本例向您展示临时表的用法： mysql> CREATE TEMPORARY TABLE SALESSUMMARY ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SALESSUMMARY -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) 当您使用 SHOW TABLES 命令查看数据库中的表时，临时表将不会被显示。 现在，如果您退出 MySQL 会话，然后使用 SELECT 命令查找数据，您将在数据库中找不到任何有效数据，甚至连临时表也不存在。 删除临时表 默认情况下，当数据库连接终止时，MySQL 将删除所有的临时表。但是，如果您希望在会话期间删除它们，则可以使用 DROP TABLE 命令。 以下是删除临时表的示例： mysql> CREATE TEMPORARY TABLE SALESSUMMARY ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SALESSUMMARY -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber | 100.25 | 90.00 | 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SALESSUMMARY; mysql> SELECT * FROM SALESSUMMARY; ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist 到此这篇关于SQL Server临时表的正确删除方式（sql server 删除临时表）的文章就介绍到这了,更多相关sql删除临时表内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！