Category Archives: Database

实战：上亿数据如何秒查？

最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。   数据背景 首先，项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表10个以上，百万级数据的表，很多… (历史问题，当初实施无人监管，无人监控数据库这块的性能问题。ps:我刚入职不久…) 不多说，直接贴西门子中国的开发人员在我司开发的SSRS报表中的SQL语句：

这个查询语句，实际上通过我的检测和调查，在B/S系统前端已无法查出结果，半小时，一小时 … 。因为我直接在SQL查询分析器查，半小时都没有结果。 (原因是里面对一张上亿级数据表和3张千万级数据表做全表扫描查询) 不由感慨，西门子中国的素质(或者说责任感)就这样？ 下面说说我的分析和走的弯路(思维误区)，希望对你也有警醒。   探索和误区 首先相关表的索引，没有建全的，把索引给建上。 索引这步完成后，发现情况还是一样，查询速度几乎没有改善。后来想起相关千万级数据以上的表，都还没有建立表分区。于是考虑建立表分区以及数据复制的方案。 这里有必要说明下：我司报表用的是一个专门的数据库服务器，数据从产线订阅而来。就是常说的“读写分离”。 如果直接在原表上建立表分区，你会发现执行表分区的事物会直接死锁。原因是：表分区操作本身会锁表，产线还在推数据过来，这样很容易“阻塞”，“死锁”。 我想好的方案是：建立一个新表(空表)，在新表上建好表分区，然后复制数据过来。 正打算这么干。等等！我好像进入了一个严重的误区！ 分析： 原SQL语句和业务需求，是对产线的数据做产品以及序列号的追溯，关键是查询条件里没有有规律的"条件"(如日期、编号)，贸然做了表分区，在这里几乎没有意义！反而会降低查询性能！ 好险！还是一步一步来，先做SQL语句分析。   一、对原SQL语句的分析 1、查询语句的where条件，有大量@var in … or (@var =") 的片段 2、where条件有like '%’+@var+’%' 3、where条件有 case … end 函数 4、多次连接同一表查询，另外使用本身已嵌套的视图表，是不是必须，是否可替代？ 5、SQL语句有*号，视图中也有*号出现   二、优化设计 首先是用存储过程改写，好处是设计灵活。 核心思想是：用一个或多个查询条件(查询条件要求至少输入一个)得到临时表，每个查询条件如果查到集合，就更新这张临时表，最后汇总的时候，只需判断这个临时表是否有值。以此类推，可以建立多个临时表，将查询条件汇总。   这样做目前来看至少两点好处： 1、省去了对变量进行 =@var or (@var=")的判断； 2、抛弃sql拼接，提高代码可读性。   再有就是在书写存储过程，这个过程中要注意： 1、尽量想办法使用临时表扫描替代全表扫描； 2、抛弃in和not in语句，使用exists和not exists替代； 3、和客户确认，模糊查询是否有必要，如没有必要，去掉like语句； 4、注意建立适当的，符合场景的索引； 5、踩死 "*" 号； 6、避免在where条件中对字段进行函数操作； 7、对实时性要求不高的报表，允许脏读(with(nolock))。   三、存储过程 如果想参考优化设计片段的详细内容，请参阅SQL代码：

虽然牺牲了代码的可读性，但创造了性能价值。本人水平有限，还请各位不吝赐教！ 最后，将SSRS报表替换成此存储过程后，SQL查询分析器是秒查的。B/S前端用时1~2秒！   四、总结 平常的你是否偶尔会因急于完成任务而书写一堆性能极低的SQL语句呢？写出可靠性能的SQL语句不难，难的是习惯。 本文的优化思想很简单，关键点是避免全表扫描 & 注重SQL语句写法 & 索引，另外，如果你查询的表有可能会在查询时段更新，而实际业务需求允许脏读，可加with(nolock)预防查询被更新事物阻塞。 作者：hangwei 出处：http://www.cnblogs.com/hangwei/ 关于作者：专注于微软平台项目的架构设计与开发、数据库调优等工作。如有问题或建议，请多多赐教！   from:http://www.oschina.net/news/74787/how-the-data-on-the-second-search

六个藉藉无名但迅速崛起的Apache大数据项目

如今全球各地的无数企业组织在处理数据集，这些数据集是如此地庞大而复杂，以至于传统的数据处理应用软件再也无法支持经过优化的数据分析和洞察力获取。这是新一批大数据应用软件旨在解决的问题，而Apache软件基金会（ASF）最近将一批值得关注的开源大数据项目升级为Apache顶级项目。这意味着，这些项目将获得积极的开发和强有力的社区支持。 （图片来源：Creative Commons Zero） 大多数人已听说过Apache Spark，这种大数据处理框架拥有内置模块，可用于数据流、SQL、机器学习和图形处理。IBM及其他公司正在往Spark项目投入数十亿美元的开发资金，美国宇航局和SETI研究所在开展合作，利用Spark的机器学习能力，分析数TB的复杂的外太空无线信号，搜寻可能表明存在智能外星生命的模式。 然而，另外几个最近被提升为顶级项目的Apache大数据项目同样值得关注。实际上，其中一些打造的生态系统在活动和开发上可与Spark的生态系统相媲美。本文介绍了你应该知道的几个Apache大数据项目。 下面是六个迅速崛起的项目： Kylin Apache最近宣布，Kylin项目这个脱胎于eBay的开源大数据项目已被提升为顶级项目。Kylin是一个开源分布式分析引擎，旨在提供一种基于Apache Hadoop的SQL接口和多维分析（OLAP），支持极其庞大的数据集。它仍广泛用于eBay和另外几家组织。 Apache Kylin副总裁Luke Han说：“Apache Kylin的孵化之旅已证明了开源治理在Apache软件基金会（ASF）具有的价值，并证明了围绕该项目打造一个开源社区和生态系统的力量。我们的社区在与世界上最庞大的本地开发者社区积极互动，完全依照Apache之道。” 作为一种基于Hadoop的OLAP解决方案，Apache Kylin旨在填补大数据探索与人类使用之间的空白，“让分析员、最终用户、开发人员和数据爱好者能够对庞大数据集执行交互式分析，延迟低于1秒，”据开发人员声称。他们补充道：“Apache Kylin将商业智能（BI）带回給Apache Hadoop，发掘大数据的价值。” Lens Apache最近还宣布，Apache Lens这个开源大数据和分析工具由Apache孵化器提升为顶级项目（TLP）。据宣布声称：“Apache Lens是一种统一分析平台。它为统一视图的分析查询提供了一种最佳执行环境。Apache Lens旨在通过针对多个分层数据存储系统，提供单一的数据视图，从而消除数据分析孤岛。” “通过在数据基础上提供一种联机分析处理（OLAP）模型，Lens将Apach Hadoop和传统数据仓库无缝集成起来，好比是一个整体。它还为在系统中运行的查询提供了查询历史记录和分析统计功能，另外提供了查询生命周期管理。” Apache Lens的副总裁Amareshwari Sriramadasu 说：“在ASF孵化Apache Lens是个神奇的经历。Apache Lens着眼于最终用户，解决了大数据分析领域的一个非常关键的问题。它让业务用户、分析员、数据科学家、开发人员及其他用户能够轻松处理复杂的分析，不需要了解底层的数据布局。” Ignite Apache软件基金会还宣布Apache Ingite成为了一个顶级项目。这个开源项目旨在构建一种内存中数据架构（in-memory data fabric）。 据Apache社区的成员声称：“Apache Ignite是一种高性能、集成、分布式的内存中数据架构，针对大规模数据集可实现实时计算和处理，速度比基于磁盘或闪存的传统技术要快几个数量级。它旨在可以轻松支持成本合理、基于行业标准的硬件上的分布式大规模并行架构中的新旧应用程序。” Brooklyn Apache软件基金会宣布，Apache Brooklyn现在是个顶级项目（TLP），“这标志着该项目的社区和产品已在该基金会的精英管理流程和原则下得到了妥善治理。”Brooklyn是一种应用程序蓝图和管理平台，用于跨多个数据中心集成服务，并集成云端的众多软件。 据Brooklyn宣布声称：“由于现代应用程序由许多组件构成，微服务架构日前受到关注，部署应用程序和已部署应用程序的日常改进成了一个越来越难的问题。Apache Brooklyn的蓝图提供了一种清晰简洁的方式，可以在部署到公共云或私有基础设施之前，明确应用程序、组件、配置以及组件之间的关系。基于策略的管理建立在自主计算理论这个基础上，不断评估运行中的应用程序，并对它进行改动，让应用程序保持顺畅运行，并且针对成本和响应能力等度量指标进行优化。” Brooklyn现用于一些知名企业组织。云服务提供商Canopy和Virtustream已开发了基于Brooklyn的产品。IBM也广泛使用Apache Brooklyn，以便将大量的工作负载从AWS迁移到IBM Softlayer。 Apex 今年4月份，Apache软件基金会将Apex项目提升为顶级项目。它号称是“面向Apache Hadoop生态系统的一种大规模、高吞吐量、低延时、容错、统一的大数据数据流和批量处理平台。”Apex可与Apache Hadoop YARN协同运行，后者是一种适用于Hadoop集群的资源管理平台。 Tajo 最后，Apache Tajo是需要了解的另一个新的大数据项目，这是Apache Hadoop中一个先进的开源数据仓库系统。Apache声称，Tajo为Hadoop部署系统、第三方数据库和商用商业智能工具提供了快速获取更多信息的功能。 很显然，虽然Apache Spark吸引了大量眼球，但它不是Apache提供的唯一引人注目的大数据工具。今年，Apache可能会将更引人注目的大数据项目提升为顶级项目，这些项目将得益于经过优化的开发资源及更多优势。 原文标题：On the Rise: Six Unsung Apache Big Data Projects   from:http://developer.51cto.com/art/201606/513276.htm

高级T_SQL语法（2005）

排名函数： RANK() 返回结果集的分区内每行的排名，FIELD为排名依据。行的排名是相关行之前的排名数加1.例如有两个并列第一，那么后一个将排名第三，而不是第二。 语法：RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例： select *, rank() over (partition by ClassID order by Mark DESC )as rank from dbo.Student 结果： DENSE_RANK()：返回结果集分区中行的排名。在排名中没有任何间断。行的排名等于所讨论的行之前的所有排名数加1.例如有两个并列第一，那么下一个将排名第二，而不是第三。 语法：DENSE_RANK() OVER([PARTITION BY clause]<ORDER BY clause>) 示例： select *, dense_rank() over (partition by ClassID order by MarkDESC ) as rank from dbo.Student 结果： NTILE()：将有序分区内的行分发到指定数目的组中。各个组由编号。，编号从1开始，对于每个行，NITLE()将返回此行所属组的编号。 语法：NTILE (int_expressions) OVER([PARTITION BY clause]<ORDER BY clause>) int_expressions：正整数常量表达式。用于指定每个分区必须被划分成的组数。 示例： select *,NTILE(4) over ( order by Mark DESC ) as NewCLASS fromdbo.Student   ROW_NUMBER()一般用于数据库分页。返回结果集分区内行的序列号。每个分区的第一行从1开始。 语法：ROW_NUMBER () OVER([PARTITION BY clause]<ORDER BY clause>) 示例： select *, ROW_NUMBER() over (partition by […]

SQL中Group By的使用

1、概述 2、原始表 3、简单Group By 4、Group By 和 Order By 5、Group By中Select指定的字段限制 6、Group By All 7、Group By与聚合函数 8、Having与Where的区别 9、Compute 和 Compute By 1、概述 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。 2、原始表 3、简单Group By 示例1

返回结果如下表，实际上就是分类汇总。 4、Group By 和 Order By 示例2

返回结果如下表 在Access中不可以使用“order by 数量之和 desc”，但在SQL Server中则可以。 5、Group By中Select指定的字段限制 示例3

示例3执行后会提示下错误，如下图。这就是需要注意的一点，在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。 6、Group By All 示例4

示例4中则可以指定“摘要”字段，其原因在于“多列分组”中包含了“摘要字段”，其执行结果如下表 “多列分组”实际上就是就是按照多列（类别+摘要）合并后的值进行分组，示例4中可以看到“a, a2001, 13”为“a, a2001, 11”和“a, a2001, 2”两条记录的合并。 SQL Server中虽然支持“group by all”，但Microsoft SQL Server 的未来版本中将删除 GROUP BY ALL，避免在新的开发工作中使用 GROUP BY ALL。Access中是不支持“Group By All”的，但Access中同样支持多列分组，上述SQL Server中的SQL在Access可以写成

7、Group By与聚合函数 在示例3中提到group by语句中select指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中，常见的聚合函数如下表： 函数 作用 支持性 sum(列名) 求和 […]

SQL Server游标的使用【转】

游标是邪恶的！        在关系数据库中，我们对于查询的思考是面向集合的。而游标打破了这一规则，游标使得我们思考方式变为逐行进行.对于类C的开发人员来着，这样的思考方式会更加舒服。        正常面向集合的思维方式是:                而对于游标来说:               这也是为什么游标是邪恶的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能.       同样的，在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源，当然还有更多的代码量……       从游标对数据库的读取方式来说，不难看出游标为什么占用更多的资源，打个比方:                  当你从ATM取钱的时候，是一次取1000效率更高呢，还是取10次100？     既然游标这么“邪恶”，为什么还要学习游标       我个人认为存在既是合理.归结来说，学习游标原因我归纳为以下2点     1.现存系统有一些游标，我们查询必须通过游标来实现     2.作为一个备用方式，当我们穷尽了while循环,子查询，临时表，表变量,自建函数或其他方式扔来无法实现某些查询的时候，使用游标实现.   T-SQL中游标的生命周期以及实现     在T-SQL中，游标的生命周期由5部分组成 1.定义一个游标      在T-SQL中，定义一个游标可以是非常简单，也可以相对复杂，取决于游标的参数.而游标的参数设置取决于你对游标原理的了解程度.      游标其实可以理解成一个定义在特定数据集上的指针，我们可以控制这个指针遍历数据集，或者仅仅是指向特定的行，所以游标是定义在以Select开始的数据集上的:                T-SQL中的游标定义在MSDN中如下:  

            看起来很让人头痛是吧.下面仔细讲一下如何定义游标:    游标分为游标类型和游标变量，对于游标变量来说，遵循T-SQL变量的定义方法（啥，不知道T-SQL变量定义的规则？参考我前面的博文）.游标变量支持两种方式赋值，定义时赋值和先定义后赋值，定义游标变量像定义其他局部变量一样，在游标前加”@”,注意，如果定义全局的游标，只支持定义时直接赋值，并且不能在游标名称前面加“@”，两种定义方式如下:          下面我们来看游标定义的参数:      LOCAL和GLOBAL二选一      LOCAL意味着游标的生存周期只在批处理或函数或存储过程中可见，而GLOBAL意味着游标对于特定连接作为上下文，全局内有效,例如:            如果不指定游标作用域，默认作用域为GLOBAL        FORWARD_ONLY 和 SCROLL 二选一      FORWARD_ONLY意味着游标只能从数据集开始向数据集结束的方向读取，FETCH NEXT是唯一的选项，而SCROLL支持游标在定义的数据集中向任何方向，或任何位置移动，如下图:                STATIC  KEYSET  DYNAMIC  和 FAST_FORWARD 四选一     这四个关键字是游标所在数据集所反应的表内数据和游标读取出的数据的关系     STATIC意味着，当游标被建立时，将会创建FOR后面的SELECT语句所包含数据集的副本存入tempdb数据库中，任何对于底层表内数据的更改不会影响到游标的内容.     DYNAMIC是和STATIC完全相反的选项,当底层数据库更改时，游标的内容也随之得到反映，在下一次fetch中，数据内容会随之改变     KEYSET可以理解为介于STATIC和DYNAMIC的折中方案。将游标所在结果集的唯一能确定每一行的主键存入tempdb,当结果集中任何行改变或者删除时，@@FETCH_STATUS会为-2,KEYSET无法探测新加入的数据     FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.       READ_ONLY  SCROLL_LOCKS  OPTIMISTIC 三选一      READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作 […]

SQLSERVER游标及循环语句

整理一下，关于游标，MSDN有： 过 Transact-SQL 服务器游标检索特定行。   参数 NEXT 紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。 PRIOR 返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。 FIRST 返回游标中的第一行并将其作为当前行。 LAST 返回游标中的最后一行并将其作为当前行。 ABSOLUTE { n | @nvar} 如果 n 或 @nvar 为正，则返回从游标头开始向后的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar为负，则返回从游标末尾开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则不返回行。n 必须是整数常量，并且 @nvar 的数据类型必须为 smallint、tinyint 或 int。 RELATIVE { n | @nvar} 如果 n 或 @nvar 为正，则返回从当前行开始向后的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar为负，则返回从当前行开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则返回当前行。在对游标进行第一次提取时，如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。n 必须是整数常量，@nvar 的数据类型必须为 smallint、tinyint 或 int。 GLOBAL 指定 cursor_name 是指全局游标。 cursor_name 要从中进行提取的打开的游标的名称。如果全局游标和局部游标都使用 cursor_name 作为它们的名称，那么指定 GLOBAL 时，cursor_name 指的是全局游标；未指定 GLOBAL 时，cursor_name 指的是局部游标。 @ cursor_variable_name 游标变量名，引用要从中进行提取操作的打开的游标。 INTO @variable_name[ ,…n] 允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。   注释 如果 SCROLL 选项未在 ISO 样式的 DECLARE CURSOR 语句中指定，则 NEXT 是唯一支持的 FETCH 选项。如果在 ISO 样式的 DECLARE CURSOR 语句中指定了 SCROLL 选项，则支持所有 FETCH 选项。 如果使用 Transact-SQL DECLARE 游标扩展插件，则应用下列规则： 如果指定了 FORWARD_ONLY 或 FAST_FORWARD，则 NEXT 是唯一受支持的 FETCH 选项。 如果未指定 DYNAMIC、FORWARD_ONLY 或 FAST_FORWARD 选项，并且指定了 KEYSET、STATIC 或 SCROLL 中的某一个，则支持所有 FETCH […]

SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

Insert是T-sql中常用语句，Insert INTO table(field1,field2,…) values(value1,value2,…)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制的情况，如将一个table1的数据的部分字段复制到table2中，或者将整个table1复制到table2中，这时候我们就要使用SELECT INTO 和 INSERT INTO SELECT 表复制语句了。       1.INSERT INTO SELECT语句       语句形式为：Insert into Table2(field1,field2,…) select value1,value2,… from Table1       要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量。示例如下：    —1.创建测试表     create TABLE Table1     (         a varchar(10),         b varchar(10),         c varchar(10),         CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED         (             a ASC         )     ) ON [PRIMARY]     create TABLE Table2     (         a varchar(10),         c varchar(10),         d int,         CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED         (             a ASC         )     ) ON [PRIMARY]     GO     —2.创建测试数据     Insert into Table1 values('赵','asds','90')     Insert into Table1 values('钱','asds','100')     Insert into Table1 values('孙','asds','80')     Insert into Table1 values('李','asds',null)     GO     select * from Table2     —3.INSERT INTO SELECT语句复制表数据     Insert into Table2(a, c, d) select a,c,5 from Table1     GO     —4.显示更新后的结果     select * from Table2     GO     —5.删除测试表     drop TABLE Table1     drop TABLE Table2       2.SELECT INTO FROM语句       语句形式为：SELECT vale1, value2 into Table2 from Table1       要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：    —1.创建测试表     create TABLE Table1     (         a varchar(10),         b varchar(10),         c varchar(10),         CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED         (             a ASC         )     ) ON [PRIMARY]     GO     —2.创建测试数据     Insert into Table1 values('赵','asds','90')     Insert into Table1 values('钱','asds','100')     Insert into Table1 values('孙','asds','80') […]

sqlserver 自增ID插入指定数据

注意： 1.set identity_insert只对当前会话生效。 2.set identity_insert 表名 ON 设置后，必须显示指定Id，否则插入错误。如insert into table_name values('111')将报错。    向自增ID插入指定值。 报错：“当 IDENTITY_INSERT 设置为 OFF 时，不能为表 ' ' 中的标识列插入显式值”。    插入语句未显示指定ID。 报错：“仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时,才能为表' '中的标识列指定显式值”。 from:http://www.cnblogs.com/wanghonghu/p/4093411.html

mysql修改字段默认值

mysql修改字段类型