最近真是忙翻天了,该是有三个月没写博客了

博客目录:Index & Writing Plan

 

这次的需求是在Mongo的使用中碰到的,但是我觉得把这个需求放进传统的RDBMS中更易于理解。需求是这样的:假设你数据库使用的是Sqlserver,有一张表,500W条数据,你要做一个随机在表中选择一条数据的功能

假设本文所探讨的数据结构如图(聚集索引在Pk上,UserName上加了非聚集索引):

 

你的第一反应大概是:哎呀妈呀忒巧了,正好主键使用的是Int自增的,我只用生成一个随机数,然后找这个随机数对应的主键就好了

实现的步骤大概是:①返回数据库中ID的最大值IdMax   ②生成1到IdMax中间一个的随机数 int random = new Random().Next(1,IdMax);

                         ③使用UserID = random作为条件查询

                         ④如果没有查询到数据,则重新生成一个随机数,再次查找(因为某个UserID的数据可能被删除了)

这种方法简单,暴力,但是有一个致命的问题:我这里在建表的时候为了说明这种方法,所以主键使用的是Int,但是在大多数我所知道的生产环境中,其实是用Guid的。这个致命的问题会直接导致上面的那个方法不可用。

至于为什么大多数我所知道的生产环境中用Guid而不用Int,我下一篇会做出对比

 

既然Int在使用Guid作为主键的时候不能用,那么我们就用Row_Number吧。Sqlserver必然是支持Row_Number的,貌似Oracle和MySql中也有类似概念(不确定,问同事得到了肯定答复,没有深究)

实现的步骤大概是:①返回数据库中数据的总条数count   ②生成1到count中间一个的随机数 int random = new Random().Next(1,count);

                         ③查找Row_Number = random的那条数据

但是Row_Number有个极其不好的地方,就是查询越后面的数据越慢,越吃资源。但凡是将数据有序储存的数据库基本都有这个问题,比如说下面两条语句:

select * from
(SELECT  UserID,UserName,Password,Sex,City,ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP) as Number
  FROM [User_db].[dbo].[Users] ) as query
  where query.Number = 20
  
  
select * from
(SELECT  UserID,UserName,Password,Sex,City,ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP) as Number
  FROM [User_db].[dbo].[Users] ) as query
  where query.Number = 5000000

第一条查Row_Number=20的数据,logical reads 5.elapsed time = 58 ms.

第二条查Row_Number=5000000的数据,logical reads 90208.elapsed time = 900 ms.

可以明显的看出,后者的逻辑读次数多了太多,而运行速度也慢了不少。如果这个功能比较频繁使用,比如说这是向用户随机推荐好用的功能,那么这个将会成为一个性能瓶颈

 

有的网友说使用这句:

  SELECT TOP 1 * FROM Users ORDER BY NEWID() 

这个运行出来结果是正确的,但是效率却大打折扣。比如说我查到了第1336793条数据,logical reads 90208,elapsed time = 3026 ms

查看执行计划,发现Sort占用了98%:

 

 

有没有比Row_Number更好一点的方法?

答案是在表中再加一列Random列,使得数据结构变更成这样:

在添加数据的时候,就生成一个随机数插入进来。按照本篇的例子来说,一开始可以生成0到一亿之间的随机数插入。注意,要在Random上加索引

实现的步骤大概是:①插入数据的时候添加一个随机  

                         ②生成一个随机数,查询 select top(1) * from Users where Random > 随机数

                         ③这个查询的结果可能会有多条(但不会很多),再在这个多条数据中随机筛选其一(使用Linq可以很方便的实现,不赘述)

 

好了,基本说完了,请允许我在结尾卖个萌:聪明的读者,开动脑筋,您还有更好的方法么?如果有,请留言

 

PS:马上离职了,准备回家休息2个月,有人能推荐点什么好玩点的地方么,趁有空想出去走走

 

posted @ 2013-03-19 12:00 CrazyJinn 阅读(3780) 评论(56) 推荐(4) 编辑
摘要: 自己最近的口头禅换成了:这将会很Cooooooooooooooool,非常Cooooooooooooooool。所以这篇博文的名字也就变得如此奇怪打算把这个写成一个系列,与大家分享我在工作中弄出来的一些很Cool的功能/效果/思路/等等等等一直都觉得程序员交流的最好手段是通过代码。强烈建议配合代码阅读本文源代码下载:点此下载源代码页面效果如下:点击这里下载Html(文件很小,建议大家都下载一下看看效果,第一次加载会比较慢,因为Js文件和Css文件都是从谷歌取的)我的博客Index:Index & Writing Plan首先介绍一下Dojo。Jquery想必大家已经耳熟能详,一个轻量级 阅读全文
posted @ 2012-12-13 09:33 CrazyJinn 阅读(2496) 评论(9) 推荐(2) 编辑
摘要: 其实我一直在准备另一篇博文的基础资料,但是和朋友聊天,他问我最近在做什么,我说在做系统Log模块,并和他交流了一下,于是这篇博客就应运而生我的博客目录:Index & Writing Plan所有数据都可以用如下形式表述:ID,表名,列名,Value比如说现在有这么一条数据要插入User表:ID(Guid,这里为了方便理解用Int)UsernamePasswordEmail1CrazyJinn123456CrazyJinn@W.C这一条记录可以转换为:ID表名列名Value1UserUsernameCrazyJinn1UserPassword1234561UserEmailCrazyJ 阅读全文
posted @ 2012-12-04 13:57 CrazyJinn 阅读(7029) 评论(46) 推荐(11) 编辑
摘要: 如果您是初次阅读这个系列,请先去《Index & Writing Plan》查找并阅读“架构设计系列”的前两篇文章,顺序阅读会使您有更好的阅读体验强烈推荐配合源代码阅读本文:点击此处下载(可以直接运行,会在本地自动生成数据库)正文开始上一篇我们写完了Service,剩下Controller和View,但是这两个都是没什么可说的了:Controller其实就是接收Service处理过的数据,并且呈现给页面;因为业务逻辑已经在Service中处理过了,所以Controller无非就是将数据稍微修改一下,比如说日期的显示方式,2012/11/15,还是2012-11-15而View,也没什么 阅读全文
posted @ 2012-11-15 11:22 CrazyJinn 阅读(2284) 评论(3) 推荐(2) 编辑
摘要: 首先分享一点自己最近的感悟:讨厌你的人总可以找到理由去讨厌你正文开始如果您是初次阅读这个系列,请先去《Index & Writing Plan》查找并阅读“架构设计系列”的前两篇文章,顺序阅读会使您有更好的阅读体验强烈推荐配合源代码阅读本文:点击此处下载(可以直接运行,会在本地自动生成数据库)已经写完了Factory的实现。在Factory中,我们使用了预编译指令来实现了Model的切换:#define A#if Busing Model.B;using DBaccess.B;#endif#if Ausing Model.A;using DBaccess.A;#endif切换Model 阅读全文
posted @ 2012-10-30 11:21 CrazyJinn 阅读(2824) 评论(13) 推荐(3) 编辑
摘要: 一直觉得,简单也是一种美,架构如此,做人亦如此;重剑无锋,真水无香为了便于大家理解,在此放出源代码:点击此处下载强烈建议配合代码阅读本文,毕竟代码才是程序员最好的交流方式之前的文章分析了系统,并画出了架构草图,详情请见《一步一步搭架子(分析篇)》关于ModelBase层与Model层的实现,因为很简单,就不再赘述了,直接上代码即可。关于Model继承的思路,请见:《我们该如何设计数据库(三)(续)》ModelBase代码:namespace ModelBase{ public class Identifier { [Key] public Guid ID... 阅读全文
posted @ 2012-10-23 10:26 CrazyJinn 阅读(2859) 评论(13) 推荐(2) 编辑
摘要: 写下这篇博客,主要是想和大家分享我的思路以及碰到的问题作为开篇,我打算和您分享如下内容:分析系统,技术的选择,系统初步构架图话不多少,进入正文假设现在要实现一个学校登记所有教师信息的系统。系统功能十分简单:对教师信息的增删改查。我们几乎是立即设计出了这样两张表(为了增加一点复杂度,这里将Teacher和Contact设计为一对一关系):系统完成之后,我们一个学校一个学校的去兜售。卖给A学校之后,他们说:“你这个系统不错,但是我们学校的教师信息有一些特有字段,希望你们能帮我们加上。”B学校买了之后,也表示很满意,但是B学校也有自己独有的字段需要我们添加简单的说,就是一个通用系统的二次开发。现在来 阅读全文
posted @ 2012-10-16 09:19 CrazyJinn 阅读(3544) 评论(9) 推荐(6) 编辑
摘要: 上篇博客《我们该如何设计数据库(三)》写出来之后,深感自己写得不够清晰,虎头蛇尾,描述问题用了很多篇幅,而问题的解决方案及其优缺点却是一笔带过,于是就写下了这篇博客来负荆请罪示例代码下载:点击这里下载示例代码说明见下文首先让我们来回顾一下《我们该如何设计数据库(三)》中描述的问题:现在有一个系统,我们暂时假设为学校选课系统。系统要按学校来卖。每个学校的选课逻辑都是一样的,而表中的数据有共性,但是也有差异性。比如说基本的Teacher表结构是这样的:现在把系统卖给A学校。A学校除了的Teacher表除了用户名和密码之外,还要储存老师的FirstName和LastName,那么表结构变化如下:现在 阅读全文
posted @ 2012-10-10 11:00 CrazyJinn 阅读(4120) 评论(18) 推荐(6) 编辑
摘要: 首先是一些废话:前文链接:我们该如何设计数据库(一)我们该如何设计数据库(二)在《我们该如何设计数据库(二)》中,园友Jacklondon Chen提出了一些问题,大致如下:“man/woman应该设计在同一张表中。用户表大多都设计成一个表。连分 administrator 和 user 都不应该。”我想还是因为我举例太随意,因为博文中Man和Woman只有4个差异属性:HasCar\HasHouse\HasMoney,以及IsBeauty其实对于这个问题我无力吐槽什么,简单的说说吧:假设为Man用户实现的是一个征婚系统,而Woman用户实现的是一个选美系统。这么说应该能理解Man和Woman 阅读全文
posted @ 2012-09-25 11:37 CrazyJinn 阅读(6415) 评论(54) 推荐(6) 编辑
摘要: 最近公司要开发新系统,基本决定使用ORM(高层还在犹豫,担心效率问题)。既然使用了ORM,那么自然而然的就想到了用面向对象的思想来设计数据库本篇文章旨在讨论如何抽象(以用户作为抽象的例子),并提出一些解耦的思路我也是第一次在实际项目中使用面向对象的思想来设计数据库,写下这篇博客,也是希望与大家多多交流正文开始首先来需求分析我们的系统有前台和后台,前台用户有:Man,Woman,SuperMan,SpiderMan与IronMan。后台用户为Administrator前台用户都要填写联系方式与地址,然后SuperMan,SpiderMan与IronMan都有Ability需求很简单。那么按照这个 阅读全文
posted @ 2012-08-20 10:46 CrazyJinn 阅读(7222) 评论(75) 推荐(4) 编辑
点击右上角即可分享
微信分享提示