1.前言

为了适应大数据应用场景的要求，Hadoop以及NoSQL等与传统企业平台完全不同的新兴架构迅速地崛起。而下层技术基础的革命必将影响上层建筑：数据模型和算法。简单地将传统基于第四范式结构化关系型数据库的模型拷贝到新的引擎上，无异于削足适履，不仅增加了大数据应用开发的难度和复杂度，又无法发释放新框架的潜能。

该如何构建基于NoSQL的数据模型?现在能供参考的公开知识积累要么是空虚简单的一句“去规范化“或粗暴的宽表化(将query和应用需要访问的所有字段“排排坐“，放在一个有很多列的结构化表中)，要么是针对具体工具或具体场景的实现细节，(如《HBase权威指南》中对于如何设计HBase主键的探讨)。没有一个像编程的设计模式一样的，在模型架构层面可以遵循的方法论。

在比较不同的NoSQL数据库时，通常使用功能以外其他各种指标，如可扩展性、性能和一致性。由于这些指标通常是使用NoSQL的初衷，所以无论从理论的角度还是实践的角度被深入地研究了，而像CAP定理这样的分布式系统基础结论也同样适用于NoSQL系统。另一方面，在NoSQL的数据模型领域，却还没有很好地研究过，也缺乏关系数据库中那种系统性的理论。

我在这篇文章中从数据建模的角度对NoSQL家族系统做了比较简单的比较，并简要介绍几种常见建模技术。

2.NoSQL数据模型视图

要探索数据建模技术，必须先从系统性的NoSQL数据模型视图着手，这多多少少能帮助我们揭示其发展趋势以及相互之间的关系。下图描绘了主要NoSQL家族系统的虚拟“进化”过程，即键值存储，BigTable类型的数据库，文档数据库，全文搜索引擎，数据库和图形数据库：

首先，我们应该注意到，一般意义上讲，SQL和关系型模型都是在很久以前就被设计出来，目的是为最终用户交互之用。这种面向用户的性质有极深的影响：

最终用户往往对汇总报表信息感兴趣而不是单独的数据项，因而SQL这方面做了大量的工作。

不能指望作为自然人的用户能显式地控制并发性、完整性、一致性或者数据类型有效性。这就是为什么SQL竭力关注于事务保证、schema和参照完整性。

另一方面，软件应用程序往往对在数据库内部做聚合没有太大的兴趣，而且至少在许多情况下，程序能够自己控制完整性和有效性。除此之外，剔除这些功能对于性能和可扩展性存储的影响极其重要。

新数据模型的演变开始了：

键-值存储是一个非常简单，但非常强大的模型。下面所描述的许多技术都完全适用于这个模型。

键值模型最致命的缺点之一就是不适合按范围处理主键的场景。有序的键-值模型突破了这一限制，并显著提高了聚合能力。

有序的键-值模型非常强大，但它不提供任何针对值(value)的建模框架。在一般情况下，值的建模可以由应用程序完成，但BigTable风格的数据库想得更加周到，它可以将值按照映射的映射的映射(map-of-maps-of-maps)进行建模，说得明确点，分别是列簇(column family)、列(column)和时间戳化的版本。

文档数据库对BigTable模式提出两个明显的改善。第一，值可以被声明为任意复杂的schema，而不仅仅是一个映射的映射(map-of-maps)。第二，至少有一些产品实现了被数据库管理的索引。就这个意义上来讲，全文搜索引擎也可以同样被认为提供了灵活的schema和自动化的索引。他们之间主要区别在于，文档数据库是根据字段名对索引进行编组，而搜索引擎是使用字段值对索引编组。值得注意的是像Oracle Coherence这样的键-值存储系统增加了索引和内嵌入口处理器的功能，正逐步向文件数据库演进。

最后，图形数据模型可以被视为有序的键-值模型朝另外一个方向的进化。图形数据库允许对业务实体进行非常透明的建模(这个东西取决于那个东西)，而分层建模技术在这方面用的是另外的数据模型，但也可与之媲美。图形数据库和文件数据库息息相关，因为许多实现允许建模的值是映射或者文档。

3.NoSQL数据建模的一般注意事项

与关系型建模不同，NoSQL数据建模往往是从特定查询的应用开始：

关系型建模是典型地被手上可用数据的结构所驱动。设计主要围绕着的是“我有什么样的答案?”

NoSQL数据建模通常由特定应用的访问模式所驱动，比如需要支持的查询类型。设计主要围绕着的是“我有什么问题?”

NoSQL数据建模往往比关系数据库建模需要更加深入地了解数据结构和算法。在这篇文章中，我介绍了几个著名的数据结构，他们虽然非NoSQL所特有，但对于实际的NoSQL建模非常有用。

数据复制和去规范化是一等公民。

关系数据库在对分层或图形数据进行建模和处理时不是很方便。图形数据库显然是这个领域的完美解决方案，但实际上大多数的NoSQL也都非常善于解决这样的问题。这就是为什么这篇文章为分层数据建模单独写了一个章节。

虽然数据建模技术基本上和具体实现无关，但我还是列出了在写这篇文章时我能想到的产品：

键值存储：Oracle Coherence，Redis，Kyoto Cabinet

BigTable风格的数据库： Apache HBase，Apache Cassandra

文档数据库： MongoDB，CouchDB

全文搜索引擎： Apache Lucene，Apache Solr

图形数据库：Neo4j，FlockDB

4.概念技术

本节专门介绍NoSQL数据建模的基本原则。

1、 去规范化(Denormalization)

可以将去规范化定义为把相同的数据复制到多个文档或数据表中，这样可以简化/优化查询处理，或者让用户数据能匹配一个特定的数据模型。在本文的大多数技术用到了这样或那样的去规范化。

一般来说，去规范化用于以下的折衷：

查询的数据量或每次查询IO**与总数据量的折衷。去规范化可以将一个查询所需的所有数据组合起来存放到同一个地方。这通常意味着对相同数据的不同的查询会访问不同的数据组合。因此，数据需要被复制多份，也就意味着增加了总数据量。

处理复杂性与总数据量的折衷。建模时的规范化和相应查询的连接(join)明显增加了查询处理器的复杂度，在分布式系统中尤为明显。去规范化允许将数据按照查询友好的方式存储，从而简化查询的处理。

适用性：键值存储，文档数据库， BigTable风格的数据库

2、 聚合(Aggregates)

所有主流NoSQL都提供了这样或那样的松散schema(soft schema)支持：

键值存储和图形数据库通常不对值进行约束，所以值可能是任意格式。另外，也可以通过使用组合键将一个业务实体表示为多条记录。例如，可以将一个用户帐户建模为UserID_name，UserID_email，UserID_messages等组合键表示的一个实体集合。如果用户没有电子邮件或消息，然后相应的实体不会被记录。

BigTable模式也支持松散schema，因为一个列簇是可变的列集合，一个单元格又能存储不定数目的数据版本。

文档数据库天生就没schema，虽然某些文档数据库允许在数据输入时使用用户定义的schema进行验证。

松散schema允许使用复杂的内部结构(嵌套实体)构造实体的类，也允许改变特定实体的结构。这个更能带来了两个重要的便利：

通过嵌套的实体，最小化了一对多的关系，也因此减少了连接(join)。

异构业务实体的模型可以使用一个文档集合或者一个数据表。松散schema掩藏了这种建模和业务实体之间“技术”上的差异。

我们用下面的图来说明这些便利。该图描绘了对电子商务领域中一个产品实体进行的建模。首先我们可以认为所有的产品都有一个ID、价格(Price)和描述(Description)。进一步来看，我们发现不同类型的产品有不同的属性，如图书包含作者信息，而牛仔裤有长度属性。这些属性中间的某些属性天生就有一对多或这多对多的特性，比如音乐唱片中的曲目。

更进一步来看，可能有些实体不可能使用固定的类型进行建模。例如，不同品牌的牛仔裤的属性是不固定的，而每个制造商出产的牛仔裤的属性也是不一致的。在规范化的关系型数据模型中虽然这些问题都可以解决，但方法很猥琐。松散schema软架构允许只使用一个聚合(Aggregation)(产品)就能对所有类型的产品及其属性进行建模：

内嵌的去规范化会在性能和一致性上对更新操作造成很大的影响，所以要特别注意更新过程。

适用性：键值存储，文档数据库， BigTable的风格数据库

3、 应用端连接(Application Side Joins)

很少有NoSQL解决方案支持连接。NoSQL“问题导向”性质的后果就是，通常在设计时处理join，而关系型模型是在执行查询时处理join。查询时处理join几乎肯定会带来性能上的损失，但在许多情况下，可使用去规范化和聚合，即嵌入嵌套实体来避免join。当然，join在许多情况下是不可避免的，而且应该由应用程序处理。主要的用例：

多对多关系往往是通过链接(link)建模的，这需要join。

聚合操作往往不适合内部实体会被频繁修改的场景。通常更好的办法是将发生的事情作为一条新的记录保留，并在查询的时候将所有记录做join，而不是去更改值。例如，对于一个信息系统而言，可以用嵌套包含了Message实体的User实体来建模。但是，如果会经常地添加消息，更好的办法可能是把Message提取出来作为独立实体，并在查询时再将其与User进行连接：

适用性：键值存储，文档数据库， BigTable风格数据库，图形数据库

5.一般建模技术

在本节中，我们将讨论适用于各种NoSQL实现的一般建模技术。

1、 原子聚合(Atomic Aggregates)

许多NoSQL解决方案提供了有限的事务支持，虽然有些NoSQL不支持。在某些情况下，人们还可以使用分布式锁或应用程序管理的MVCC机制实现事务行为，但常见的是使用聚合技术来对数据建模，以保证一些ACID特性。

强大的事务处理机制对于关系型数据库而言是不可或缺的，其中原因之一就是规范化的数据通常需要在多个地方进行更新。另一方面，聚合允许一个单个业务实体存储为一个文件，行或键值对，从而可以对其进行原子性的更新：

当然，做为一种数据建模技术，原子聚合并不是一个完善的事务型解决方案，但如果存储能提供原子性、锁或者TAS(test-and-set，测试并设置)指令上的一些担保，那原子聚合就是可行的。

(译者注：即将需要事务性操作的业务数据聚合放在一起，存储在一个NoQSQL提供或者应用能提供原子性操作的数据结构中。使用HBase时，将某个用户某个业务的所有数据，如上图，用一行存储就是这种模式的应用。)

适用性：键值存储，文档数据库， BigTable风格数据库

2、 可枚举主键(Enumerable Keys)

也许无序键-值数据模型最大的好处就是可以通过将主键哈希的办法把实体数据分别存储在多个服务器上。排序使事情变得更加复杂，但是即使存储不提供这样的功能，有时应用程序也能利用到有序主键的优势。让我们将对电子邮件建模作为一个例子：

某些NoSQL存储提供原子计数器，能生成一个顺序化的ID。在这种情况下，可以使用userID_messageID作为一个复合键来存储消息。如果最新的消息ID是已知的，那就可以遍历以前的消息。另外，对于任何一个给定的消息ID，也可以向前或向后进行遍历。

也可以将消息分桶(bucket)，例如，每天的数据放到一个桶里。这样就允许从任何指定日期或当前日期开始，向前或向后遍历一个邮箱。

适用性：键值存储

(译者注：能利用主键的一些自然或业务维度的特征，将随机读写转换为顺序读写能提高遍历性能，同时能方便应用逻辑编写。但需要注意对分布式部署时并发写的影响以及对于业务的过度耦合。对于无序主键和有序主键的讨论可以参见《HBase权威指南》中Schema设计章节。)

3、 降维(Dimensionality Reduction)

降维这种技术允许将一个多维数据模型映射到一个键-值模型或其他非多维模型。

传统的地理信息系统使用四叉树(Quadtree)或R树(R-tree)的某种变形来做索引。这些结构需要就地完成更新操作，因此在数据量很大时，维护开销相当的大。另一种方法是对这个二维结构进行遍历，并将其扁平化为一个普通的条目列表。使用这种技术的一个众所周知的例子是Geohash。 Geohash使用类似Z形状的路线来扫描整个二维空间，每次移动根据行进方向被编码为0或1。交错位的经度和纬度上的变更移动以及移动。编码过程在下图中进行了说明，其中黑色和红色位分别代表经度和纬度：

如图所示，Geohash的一个重要特性是能够通过这种逐位编码的近似程度来估计区域之间的距离。Geohash编码允许使用简单普通的数据模型来存储地理信息，比如用有序键值保存空间上的联系。[6.1]讲述了BigTable中的降维技术。更多有关Geohash及其相关技术的信息可以在[6.2]和[6.3]中找到。

(译者注：通过交织编码方式来能将原本需要多维度标示的数据，如cube，存储到一维的键值存储系统中，这是一种非常重要的建模模式：提供了不同缩放等级下在多维空间中邻接的数据仍然顺序存储，遍历高效;同时不同主键从前向后的相似度和空间距离的远近相一致，能通过键值的简单顺序比较判断其位置“相似度”。

它的应用远远不只地理信息的表示，有多个维度属性不同粒度的数据表示都能用到这个技术，比如线下销售交易数...

SQL是数据库的查询语言，语法结构简单，相信本文会让你从入门到熟练。

掌握SQL后，不论你是产品经理、运营人员或者数据分析师，都会让你分析的能力边界无限拓展。别犹豫了，赶快上车吧！

以下的语句都在SequelPro的Query页面运行，其他操作页面不会有太大差异。标点符号必须为英文，这是新人很容易犯的错误。

SQL最小化的查询结构如下：

selectcolumnfromtable

table是我们的表名，column是我们想要查询的字段／列，column可以用*代替，指代全部字段，意为从table表查询所有数据。

where是基础查询语法，用于条件判断。

select*fromDataAnalystwherecity='上海'

上图是最简化的查询语句，将所有城市为上海的职位数据过滤出来。我们也可以用and进行多条件判断。

select*fromDataAnalystwherecity='上海'andpositionName='数据分析师'

or语句则是或的关系：

select*fromDataAnalystwherecity='上海'orpositionName='数据分析师'

查找城市为上海，或者职位名称是数据分析师的数据，它们是并集。

当我们涉及到非常复杂的与或逻辑判断，应该怎么办？比如即满足条件AB，又要满足条件C，或者是满足条件DE。此时需要用括号明确逻辑判断的优先级。

select*fromDataAnalystwhere(city='上海'andpositionName='数据分析师')or(city='北京'andpositionName='数据产品经理')

这条语句的含义是查找出上海的数据分析师或者是北京的产品经理。当有括号时，会优先进行括号内的判断，当有多个括号时，对最内层括号先进行判断，然后依次往外。

接下来的问题来了，当我们要查询多个条件，比如北京上海广州深圳南京这些城市，难道一个个用and关联起来？这太麻烦了，我们可以使用in。

select*fromDataAnalystwherecityin('北京','上海','广州','深圳','南京')

当我们遇到字段数据类型是数值时，也可以使用符号>、>=、<、<=、!=进行逻辑判断，!=指的是不等于，等价于<>。

select*fromDataAnalystwherecompanyId>=10000

上例是筛选出公司ID>=10000的职位，为数值时，不需要像字符串一样加引号。

当我们需要取区间数值时，使用betweenand：

select*fromDataAnalystwherecompanyIdbetween10000and20000

betweenand包括数值两端的边界，等同于companyId>=10000andcompanyId<=20000。

如果要模糊查找，能用like。

select*fromDataAnalystwherepositionNamelike'%数据分析%'

语句的含义是在positionName列查找包含「数据分析」字段的数据，%代表的是通配符，含义是无所谓「数据分析」前面后面是什么内容。如果是‘数据分析%’，则代表字段必须以数据分析开头，无所谓后面是什么。

除了上面所讲，还有一个常用的语法是not，代表逻辑的逆转，常见notin、notlike、notnull等。

接下来我们学习groupby，它是数据分析中常见的语法，目的是将数据按组／维度划分。类似于Excel中的数据透视表，我们以city为例。

select*fromDataAnalystgroupbycity

它将城市划分成几组，通过groupby可以快速的浏览数据有哪些城市。我们看一下它的高阶用法。

selectcity,count(1)fromDataAnalystgroupbycity

上述语句，使用count函数，统计计数了每个城市拥有的职位数量。括号里面的1代表以第一列为计数标准。这里出现新的问题，当我们遇到重复数据怎么办？在DataAnalyst这张表中，北京职位包含重复的职位ID，我们需要去重。

selectcity,count(distinctpositionId)fromDataAnalystgroupbycity

北京的数据一下子少了2000，多余的重复值被排除在外。distinct是去重函数，distinctpositionId会只计算唯一的positionId个数。日常工作中，活跃用户数、文章UV，都是用distinct计算获得，这是唯一标示符ID的重要作用。

除了count，还有max，min，sum，avg等函数，也叫做聚合函数。用法和Excel没什么区别。

当我们在groupby添加多个字段，它将以多维的形式进行数据聚合。

selectcity,workYear,count(distinctpositionId)fromDataAnalystgroupbycity,workYear

这就是数据分析师常用的多维分析法，通过groupby切分不同的维度进行对比，在不利用BI的情况下，通过SQL进行快速数据分析。

接下来学习逻辑判断，SQL也有if函数，和Excel的用法一摸一样，通过它我们能进行复杂的运算。比如我想统计各个城市中有多少数据分析职位，其中，电商领域的职位有多少，在其中的占比？

industryField是公司的行业领域，虽然我们能用wherelike计算出有几个电商的数据分析师，但是占比的计算会比较麻烦，此时可以用if。

selectif(industryFieldlike'%电子商务%',1,0)fromDataAnalyst

上面的公式利用if判断出哪些是电商行业的数据分析师，哪些不是。if函数中间的字段代表为true时返回的值，不过因为包含重复数据，我们需要将其改成positionId。之后，用它与groupby组合就能达成目的了。

selectcity,

count(distinctpositionId),

count(if(industryFieldlike'%电子商务%',positionId,null))fromDataAnalystgroupbycity

第一列数字是职位总数，第二列是电商领域的职位数，相除就是占比。记住，count是不论0还是1都会纳入计数，所以第三个参数需要写成null，代表不是电商的职位就排除在计算之外。

接下来是新的问题，如果我想找出各个城市，数据分析师岗位数量在500以上的城市有哪些，应该怎么计算？有两种方法，第一种，是使用having语句，它对聚合后的数据结果进行过滤。

selectcity,count(distinctpositionId)fromDataAnalystgroupbycityhavingcount(distinctpositionId)>=500

第二种，是利用嵌套子查询。

我们将第一次查询获得的城市职位数的结果，看作一张新的表，利用as将它命名为t1(table1的简写)，将职位数命名为一个新的字段counts。然后外面再套一层select过滤出counts>=500。

这种查询方式就叫嵌套子查询，使用场景比较广泛，where后面也能跟子查询。

很多时候，数据是凌乱的，我们希望结果能够呈现一定的顺序，这时候就用到orderby语句。

selectcity,count(distinctpositionId)ascountsfromDataAnalystgroupbycity

orderbycounts

看，数据就按照统计结果升序排列，如果需要降序，则是orderbycountsdesc，后面加一个desc就好了。如果是多个字段，按逗号分隔即可。

我们再来熟悉SQL的常用函数，首先是时间。因为我们的练习数据中没有时间，首先用now创建出一个时间字段。

selectnow()

直接执行它，就能获得当前的系统时间，精确到秒。其实select不一定后面要跟from。

selectdate(now())

它代表的是获得当前日期，week函数获得当前第几周，month函数获得当前第几个月。其余还包括，quarter，year，day，hour，minute。

时间函数也包含各种参数，比如week，因为中西方计算第几天是不一样的，西方把周日算作一周中的第一天，而我们习惯周一。

selectweek(now(),0)

除了以上的日期表达，也可以使用dayofyear、weekofyear的形式计算。它和上面的部分函数等价。

怎么对时间进行加减法呢？这时候靠date_add函数出马。

selectdate_add(date(now()),interval1day)

我们可以改变1为负数，达到减法的目的，也能更改day为week、year等，进行其他时间间隔的运算。如果是求两个时间的间隔，则是datediff(date1,date2)或者timediff(time1,time2)。

时间函数的运用比较灵活，没有特殊限定，网络上的文档和教程也不少，可以深入学习。

最后是数据清洗类的函数。

selectleft(salary,1)fromDataAnalyst

MySQL支持left、right、mid等函数，这里又和Excel一样。我们通过salary计算数据分析师的工资吧（这一步骤，在曾经的文章中已经用Excel和BI多次讲解，所以我就不多赘述了，只讲过程，不熟悉的同学可以看历史内容）。

首先利用locate函数查找第一个k所在的位置。

selectlocate("k",salary),salaryfromDataAnalyst

然后使用left函数截取薪水的下限。

selectleft(salary,locate("k",salary)-1),salaryfromDataAnalyst

为了获得薪水的上限，要用substr函数，或者mid，两者等价。

substr（字符串，从哪里开始截，截取的长度）

薪水上限的开始位置是「-」位置往后推一位。截取长度是整个字符串减去「-」所在位置，刚好是后半段我们需要的内容，不过这个内容是包含「K」的，所以最后结果还得再减去1。

这里不了解不要紧，可以将计算过程分步骤运行。基本上，了解了上面写法的含义，文本清洗这块就没有问题了（notlike用来清洗乱七八糟的薪水，我简单处理了）。再然后计算不同城市不同工作年限的平均薪资。

上面语句，我们用了文本清洗、子查询嵌套、分组聚合、排序等多种用法，属于较复杂的查询。重复数据的问题，因为我是复制了一份北京数据，数量刚好乘二，对平均数没有影响，感兴趣的朋友可以再加一步清洗掉它。

下面是三道思考题：

查询出哪家公司招聘的岗位数最多；查询出O2O、电子商务、互联网金融这三个行业，哪个行业的平均薪资最高；查询出各城市的最高薪水Top3是哪家公司哪个岗位。

做完上面的题目，你已经神功初成，数据分析的SQL意见没有大问题了。更复杂的查询，也无非是嵌套更多的内容，本质思路是一样的。

讲到这里，只剩join语法还没有教大家。因为练习数据只有一张表，而join又是SQL中比较容易混淆的难点，我会单独开一篇内容讲解，到时候使用SQLZoo和LeetCode的案例。

LeetCode是知名的算法竞赛网站，可以在上面和全世界的程序员比拼算法，当然我们只练习SQL，完成后，至少能秒杀全世界50%的程序员吧。

CREATE        VIEW `test`.`view_ll`     AS(SELECT * FROM ...);

`test`.`view_ll` 是数据库.表名。()里面是sql语句。

（本文内容由百度知道网友du瓶邪贡献）

CREATE TABLE

CREATE TABLE用来创建一个表

//创建一个表，表的名称为table_name，该表有三列，列名分别为column_1，column_2，column_3.列中的数据类型分别为整形，文本，整形

举例如下：

INSERT INTO

INSERT INTO用于向列中插入值

//分别向列id, name, age中插入 1, 'Justin Bieber', 21 insert into table_test (id,name,age) values(1,"Justin Bieber",21);

//插入多条数据

1、MySQL数据库：

2、oracle数据库：

UPDATE

用于修改表中的数据

语法 UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

举例：修改id=1这一行的age列的数据为22 SQL如下：

UPDATE table_test SET age = 22 WHERE id = 1;

ALTER TABLE

修改表，用于增加，修改，删除列

例如：ADD COLUMN添加一个列 ALTER TABLE table_test ADD COLUMN address TEXT;

例如：DROP COLUMN删除一个列 ALTER TABLE table_test DROP COLUMN address ;

DELETE

删除表中的行

例如：IS NULL表示值是NULL或者不存在，删除age列中值不存在的行 DELETE FROM table_test WHERE age IS NULL;

SQL查询 DISTINCT 过滤重复的数据 语法如下：

WHERE

通过各种条件定位到具体的数据

例如：统计age大于24的数据 SELECT * FROM table_test WHERE age >24;

LIKE

模糊匹配

举例：_通配一个字符 SELECT * FROM table_test WHERE name LIKE 'Mar_y';

举例：%通配任意多个字符 SELECT * FROM table_test WHERE name LIKE '%Bieber%';

BETWEEN 匹配一个范围

例如：查到age范围在23到30之间的数据 SELECT * FROM table_test WHERE age BETWEEN 23 and 30;

AND 逻辑与

例如：查到age范围在23到30之间的数据 SELECT * FROM table_test WHERE age >= 23 and age <= 30;

OR逻辑或

例如：查到age范围不在在23到30之间的数据 SELECT * FROM table_test WHERE age <= 23 or age >= 30;

等同于 SELECT * FROM table_test WHERE age not BETWEEN 23 and 30;

ORDER BY

例如：依据某一列进行排序，ASC是顺序排序，DESC是逆序排序。 SELECT * FROM table_test ORDER BY age ASC;

COUNT()

COUNT()是最快的方式，统计一张表总共的行数。COUNT()函数的参数是一个列的名称，统计整个表所有的行数时，采用通配符*;

例如：统计整个表的行数 SELECT COUNT(*) FROM table_test ;

例如：统计age为25的行数 SELECT COUNT(*) FROM table_test WHERE age= 25;

GROUP BY

GROUP BY <列名>将一列中相同值的列分成一组

例如：按照age进行分组，并统计每组元素的个数 SELECT age, COUNT(*) FROM table_test GROUP BY age;

SUM()

SQL通过SUM()可以很容易统计一列的和

例如：计算age一列的和 SELECT SUM(age) FROM table_test ;

MAX()

例如：MAX()函数可以找到一列中的最大值 SELECT MAX(age) FROM table_test ;

MIN()

MIN()函数可以找到一列中最小的值，用法与MAX()相同

AVG()

AVG()函数计算一列的平均值，用法与MAX()相同

ROUND() 设定数值到指定的精度

举例：设定计算的平均值精度精确到小数点后两位 SELECT ROUND(AVG(age), 2) FROM table_test;

PRIMARY KEY

在使用CREATE TABLE时为id添加PRIMARY KEY，PRIMARY KEY是一张表中每一行独一无二的标识，该表将id作为主键。通过主键将多个表联系起来。

CREATE TABLE table_test (id INTEGER PRIMARY KEY, name TEXT）；

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。

1、 首先要搞明白什么叫执行计划？

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000条记录了，那查询优化器就会改变方案，采用 “全表扫描”方式。

可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要：

(1) SQL语句是否清晰地告诉查询优化器它想干什么？

(2) 查询优化器得到的数据库统计信息是否是最新的、正确的？

2、 统一SQL语句的写法

对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。

select*from dual

select*From dual

其实就是大小写不同，查询分析器就认为是两句不同的SQL语句，必须进行两次解析。生成2个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！

3、 不要把SQL语句写得太复杂

我经常看到，从数据库中捕捉到的一条SQL语句打印出来有2张A4纸这么长。一般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者，结果他说时间太长，他一时也看不懂了。可想而知，连原作者都有可能看糊涂的SQL语句，数据库也一样会看糊涂。

一般，将一个Select语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的，但是根据经验，超过3层嵌套，查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西，终究比人的分辨力要差些，如果人都看晕了，我可以保证数据库也会晕的。

另外，执行计划是可以被重用的，越简单的SQL语句被重用的可能性越高。而复杂的SQL语句只要有一个字符发生变化就必须重新解析，然后再把这一大堆垃圾塞在内存里。可想而知，数据库的效率会何等低下。

4、 使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

5、 OLTP系统SQL语句必须采用绑定变量

select*from orderheader where changetime >'2010-10-20 00:00:01'

select*from orderheader where changetime >'2010-09-22 00:00:01'

以上两句语句，查询优化器认为是不同的SQL语句，需要解析两次。如果采用绑定变量

select*from orderheader where changetime >@chgtime

@chgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL语句的负担。一次解析，多次重用，是提高数据库效率的原则。

6、 绑定变量窥测

事物都存在两面性，绑定变量对大多数OLTP处理是适用的，但是也有例外。比如在where条件中的字段是“倾斜字段”的时候。

“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这列，90%以上都是汉族。那么如果一个SQL语句要查询30岁的汉族人口有多少，那“民族”这列必然要被放在where条件中。这个时候如果采用绑定变量@nation会存在很大问题。

试想如果@nation传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。

7、 只在必要的情况下才使用begin tran

SQL Server中一句SQL语句默认就是一个事务，在该语句执行完成后也是默认commit的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。

有些情况下，我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran 可以起到这样的作用，它可以把若干SQL语句套在一起执行，最后再一起commit。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前，所有SQL语句锁住的资源都不能释放，直到commit掉。

可见，如果Begin tran套住的SQL语句太多，那数据库的性能就糟糕了。在该大事务提交之前，必然会阻塞别的语句，造成block很多。

Begin tran使用的原则是，在保证数据一致性的前提下，begin tran 套住的SQL语句越少越好！有些情况下可以采用触发器同步数据，不一定要用begin tran。

8、 一些SQL查询语句应加上nolock

在SQL语句中加nolock是提高SQL Server并发性能的重要手段，在oracle中并不需要这样做，因为oracle的结构更为合理，有undo表空间保存“数据前影”，该数据如果在修改中还未commit，那么你读到的是它修改之前的副本，该副本放在undo表空间中。这样，oracle的读、写可以做到互不影响，这也是oracle 广受称赞的地方。SQL Server 的读、写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据。使用 nolock有3条原则。

(1) 查询的结果用于“插、删、改”的不能加nolock ！

(2) 查询的表属于频繁发生页分裂的，慎用nolock ！

(3) 使用临时表一样可以保存“数据前影”，起到类似oracle的undo表空间的功能，

能采用临时表提高并发性能的，不要用nolock 。

9、 聚集索引没有建在表的顺序字段上，该表容易发生页分裂

比如订单表，有订单编号orderid，也有客户编号contactid，那么聚集索引应该加在哪个字段上呢？对于该表，订单编号是顺序添加的，如果在orderid上加聚集索引，新增的行都是添加在末尾，这样不容易经常产生页分裂。然而，由于大多数查询都是根据客户编号来查的，因此，将聚集索引加在contactid上才有意义。而contactid对于订单表而言，并非顺序字段。

比如“张三”的“contactid”是001，那么“张三”的订单信息必须都放在这张表的第一个数据页上，如果今天“张三”新下了一个订单，那该订单信息不能放在表的最后一页，而是第一页！如果第一页放满了呢？很抱歉，该表所有数据都要往后移动为这条记录腾地方。

SQL Server的索引和Oracle的索引是不同的，SQL Server的聚集索引实际上是对表按照聚集索引字段的顺序进行了排序，相当于oracle的索引组织表。SQL Server的聚集索引就是表本身的一种组织形式，所以它的效率是非常高的。也正因为此，插入一条记录，它的位置不是随便放的，而是要按照顺序放在该放的数据页，如果那个数据页没有空间了，就引起了页分裂。所以很显然，聚集索引没有建在表的顺序字段上，该表容易发生页分裂。

曾经碰到过一个情况，一位哥们的某张表重建索引后，插入的效率大幅下降了。估计情况大概是这样的。该表的聚集索引可能没有建在表的顺序字段上，该表经常被归档，所以该表的数据是以一种稀疏状态存在的。比如张三下过20张订单，而最近3个月的订单只有5张，归档策略是保留3个月数据，那么张三过去的 15张订单已经被归档，留下15个空位，可以在insert发生时重新被利用。在这种情况下由于有空位可以利用，就不会发生页分裂。但是查询性能会比较低，因为查询时必须扫描那些没有数据的空位。

重建聚集索引后情况改变了，因为重建聚集索引就是把表中的数据重新排列一遍，原来的空位没有了，而页的填充率又很高，插入数据经常要发生页分裂，所以性能大幅下降。

对于聚集索引没有建在顺序字段上的表，是否要给与比较低的页填充率？是否要避免重建聚集索引？是一个值得考虑的问题！

10、加nolock后查询经常发生页分裂的表，容易产生跳读或重复读

加nolock后可以在“插、删、改”的同时进行查询，但是由于同时发生“插、删、改”，在某些情况下，一旦该数据页满了，那么页分裂不可避免，而此时nolock的查询正在发生，比如在第100页已经读过的记录，可能会因为页分裂而分到第101页，这有可能使得nolock查询在读101页时重复读到该条数据，产生“重复读”。同理，如果在100页上的数据还没被读到就分到99页去了，那nolock查询有可能会漏过该记录，产生“跳读”。

上面提到的哥们，在加了nolock后一些操作出现报错，估计有可能因为nolock查询产生了重复读，2条相同的记录去插入别的表，当然会发生主键冲突。

11、使用like进行模糊查询时应注意

有的时候会需要进行一些模糊查询比如

select*from contact where username like ‘%yue%’

关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%，

12、数据类型的隐式转换对查询效率的影响

sql server2000的数据库，我们的程序在提交sql语句的时候，没有使用强类型提交这个字段的值，由sql server 2000自动转换数据类型，会导致传入的参数与主键字段类型不一致，这个时候sql server 2000可能就会使用全表扫描。Sql2005上没有发现这种问题，但是还是应该注意一下。

13、SQL Server 表连接的三种方式

(1) Merge Join

(2) Nested Loop Join

(3) Hash Join

SQL Server 2000只有一种join方式——Nested Loop Join，如果A结果集较小，那就默认作为外表，A中每条记录都要去B中扫描一遍，实际扫过的行数相当于A结果集行数x B结果集行数。所以如果两个结果集都很大，那Join的结果很糟糕。

SQL Server 2005新增了Merge Join，如果A表和B表的连接字段正好是聚集索引所在字段，那么表的顺序已经排好，只要两边拼上去就行了，这种join的开销相当于A表的结果集行数加上B表的结果集行数，一个是加，一个是乘，可见merge join 的效果要比Nested Loop Join好多了。

如果连接的字段上没有索引，那SQL2000的效率是相当低的，而SQL2005提供了Hash join，相当于临时给A，B表的结果集加上索引，因此SQL2005的效率比SQL2000有很大提高，我认为，这是一个重要的原因。

总结一下，在表连接时要注意以下几点：

(1) 连接字段尽量选择聚集索引所在的字段

(2) 仔细考虑where条件，尽量减小A、B表的结果集

(3) 如果很多join的连接字段都缺少索引，而你还在用SQL Server 2000，赶紧升级吧。

在查询数据的过程中，我们经常会遇到某个字符或者某个字符串在一个长字符串中出现次数的问题，这个时候，我们就有必要根据实际情况进行分析处理了。

我们首先打开sql server management studio,我们输入登录密码，进入到sql server 企业管理器。

我们点击工具栏的新建查询按钮，打开一个查询代码编辑窗口。

我们定义一个查询函数，代码如图所示。

我们用代码调用查询函数进行查询，当查询不到的时候，次数显示为0.

当能在目标字符串中查找到字符串时，我们可以看到查找到的次数。

从上述步聚，我们可以看到大功告成了。上面的步聚适合于单个字符及多个字符的匹配。

作者介绍

黄浩：从业十年，始终专注于SQL。十年一剑，十年磨砺。3年通信行业，写就近3万条SQL；5年制造行业，遨游在ETL的浪潮；2年性能优化，厚积薄发自成一家。

在《SQL优化案例之五味杂陈》之后的若干天，开发人员来到我座位，不说话，只是端看着我，还似笑非笑。看着这诡异的一幕，从他不怀好意的神情中，隐隐感觉到一丝丝不祥之感。果真，又出现了性能问题。刹那间，我心里瘆得慌，因为当时我曾断言，在经过对数据模型进行大刀阔斧的优化后，性能撑个一年两年的是没问题的。而现在还不到一个月的时间，就在开发人员痴痴的笑声中被啪啪啪打脸了。

是福不是祸，是祸躲不过

我故作镇定地与开发做了一番交谈：

“是突然变慢了吗？”

此时，我希望是执行计划变化引发的性能问题。

“是的。”

开发人员的回答让我稍稍轻松了下，但是他接下来的描述如同一盆冷水，又浇灭了我刚刚点燃的星星火苗

“这次是增加了活动流过滤条件，就变慢了。之前的条件还是蛮快的。”

……….哎，被赤裸裸地调戏了一番呀。

找开发人员拿到了SQL，如下：

这个SQL我是相当的熟悉了，根据开发人员的说法，只是比之前的SQL多了一个过滤条件：

AND (T1.TASKLOWIDS IN (18061000))

这个非常简单的过滤条件居然会有如此大的魔力，将我千辛万苦优化的SQL，轻而易举地让性能从2秒变成了90秒，不仅打回原形，还“变本加厉”了。面对如此赤裸裸的挑衅，也激发了我的应战情绪。

沉着冷静，从容不迫

在展开分析之前，结合之前的优化过程，我梳理了下思路：

这次性能问题特征很明显：由一个过滤条件引发的性能问题；增加一个过滤条件，正常情况下对性能的影响不会太大，但是可能会对执行计划产生一系列影响，比如如果该过滤字段有索引，很可能会将之前的TABLE ACESS FULL变成INDEX RANGE SCAN，继而，其与其他表的关联方式会从之前的NESTED LOOP变成HASH JOIN。

因此，我初步判定这个条件过滤引发了执行计划的变化，为了印证我的判定，我对比了执行计划，如下：

我先来看下带有TASK_FLOW_ID条件的执行计划

简单解读如下：

驱动表是SDS_DU_TF_RELEASE_T，该表的访问方式是TABLE ACCESS BY INDEX ROWID，因为在该表上，分别在字段TASK_FLOW_ID和PROJECT_NUMBER上创建了索引，所以ORACLE优化器选择了两个索引BITMAP AND操作。需要注意的是，此时出现的索引SDS_SDS_DU_TF_RELEASE_TFID_I正是因为过滤条件AND (T1.TASKLOWIDS IN (18061000)) 引起的；SQL中的主体表RP_PLAN_LOG_T的访问方式是TABLE ACESS BY LOCAL INDEX ROWID，被访问的索引是INX_OPERATETIME_PROJECTNUMBER，即过滤条件中PROJECT_NUMBER和OPERATE_TIME的字段组合索引。由于OPERATE_TIME命中的是多个分区，所以最终是PARTITION RANGE ITERATOR；结果1和结果2两个集合通过DU_IID做了HASH JOIN。

接下来我们看看没有TASK_FLOW_ID过滤条件的执行计划：

驱动表为SQL中的主体表RP_PLAN_LOG_T，访问方式是TABLE ACESS BY LOCAL INDEX ROWID，被访问的索引是INX_OPERATETIME_PROJECTNUMBER，即过滤条件中PROJECT_NUMBER和OPERATE_TIME的字段组合索引。由于OPERATE_TIME命中的是多个分区，所以最终是PARTITION RANGE ITERATOR；SDS_DU_TF_RELEASE_T，该表的访问方式是TABLE ACCESS BY INDEX ROWID；结果集1和结果集2通过DU_IID，进行了NESTED LOOPS关联。

不比不知道，一比吓一跳

通过上述对比，我们发现：

RP_PLAN_LOG_T的访问方式是没有变化的，前后都是：驱动表发生了变化，没有TASK_FLOW_ID过滤条件时，驱动表为RP_PLAN_LOG_T表。而后变成了SDS_DU_TF_RELEASE_TRP_PLAN_LOG_T与SDS_DU_TF_RELEASE_T的关联方式也发生了变化，没有TASK_FLOW_ID过滤条件时，关联方式为NESTED LOOPS，而后变成了HASH JOIN

至此，我的心情有些失落。一开始，我是做了打一场大战硬战的准备，而这场战斗才刚开始，就似乎要结束了。这个起初“山雨欲来风满楼，剑拔弩张马齐嘶”的性能问题突然变成了一个非常常见又平常的案例：由一个查询条件引发了执行计划变化，从而导致了性能问题。而此类问题的药方也通用：干扰Oracle优化器。比如这次的方案，可以通过HINT，或者LEADING指定驱动表，或者NO_INDEX强制不使用TASK_FLOW_ID的索引，或者USE_NL指定关联方式。

水落石未出，疑云层层来

该案例的优化工作就这样在大起大落中平淡收场了。然而，有两个问题并没有随着优化结束而水落石出，其一是为何增加了一个过滤条件会引发执行计划变化？其二是为何RP_PLAN_LOG_T做驱动表的性能会高？尤其是第二个问题，要知道，RP_PLAN_LOG_T通过PROJECT_NUMBER和OPERATE_TIME综合过滤后，其数据量达到了百万级，是数据量最大的结果集，这明显有违小表驱动的基本原理。

剥开第一层疑云

我们先看看第一个问题，这个问题相对简单。为了弄清这个问题，我们首先要看看SDS_DU_TF_RELEASE_T的模型结构，在该SQL中，关于这个表的关键字段有三个字段，分别是DU_IID、TASK_FLOW_ID、PROJECT_NUMBER。三者之间的关系如下：

从PROJECT_NUMBER—>TASK_FLOW_ID—>DU_IID，数据粒度越来越细，所以当TASK_FLOW_ID作为了过滤条件，Oracle就认为可以过滤掉大量的数据，而且TASK_FLOW_ID上又存在索引，从而认定可以作为驱动表。

剥开第二层疑云

现在重点看看第二个问题：为何RP_PLAN_LOG_T做驱动表的性能会高？

带着这个疑问，为了便于说明，我们简化下这个SQL，砍掉枝枝叶叶，只保留RP_PLAN_LOG_T这个“孤家寡人”，同时我们也略作改动，即将ORDER BY的字段由OPERATE_TIME修改为CDESCRIPTOIN。如下：

其中RP_PLAN_LOG_T的表结构如下：

表的索引如下：

执行计划如下：

由于满足条件的数据量近170万，整个SQL耗时达1.5S，而执行计划与我们预期是一样的，主要包含两步骤：

先通过本地索引获取到符合条件的数据（INDEX RANGE SCAN）再根据CDESCRIPTOIN字段排序（SORT ORDER BY STOPKEY）

从成本看，很大一部分成本消耗在SORT上。现在我们将ORDER BY CDESCRIPTOIN还原成ORDER BY OPERATE_TIME。那么，在性能上会发生什么神奇的效果呢？

索引还是那个索引，表还是那个表，只是SORT ORDER BY STOPKEY不见了，成本降低了，执行效率达到了毫秒级。

辩论时刻

这里，有一个大写的疑问：明明是ORDER BY OPERATE_TIME，为何在执行计划里面没有SORT ORDER BY STOPKEY步骤了？难道是Oracle优化器的BUG？此时，你会不会因为发现了Oracle的BUG而欢呼雀跃？很遗憾的告诉你，这并非Oracle的BUG，反而是Oracle优化器的高明之处。

索引的特性之一就是有序，我们先通过OPERATE_TIME字段上的索引获取到了有序的OPERATE_TIME（及其对应的ROWID），以此为基础，通过TABLE ACCESS BY LOCAL INDEX ROWID获取其它字段信息，这样得到的结果集自然是已经按照OPERATE_TIME排好序的有序结果：

请问，这还需要“教条”般的再次排序吗？

除了大写的疑问外，还有一个小写的疑问：不考虑排序，同样的查询条件，同样的索引扫描，为何成本差异如此之大？在无SORT的情况下，INDEX RANGE SCAN的COST值为11，而如果进行了SORT，COST值为1910。

难道是SORT会影响到INDEX RANGE SCAN的成本？事实上ORACLE引擎是先执行INDEX RANGE SCAN，再执行SORT，也只能是：INDEX RANGE SCAN的结果集会影响到SORT的成本，因为INDEX RANGE SCAN的结果集越大，SORT的成本会越高。

那么，这里面到底发生了什么呢？还得要从根本说起：在正常情况下，我们如果想要获取前N条数据，就必须要按照既定字段排序，那就意味着我们首先要获取到全部的数据；但是，如果我们拿到的是已经按照既定字段排好序的数据，那么就可以直接获取前N条数据，而无需获取全部数据。这就是同样是INDEX RANGE SCAN，而COST相距甚远的玄妙所在。

这个猜想也是可以在执行计划中得到印证：就是INDEX RANGE SCAN这步操作的实际返回ROWS，如下：

看到这里，你是否会有些小激动？因为你发现：在排序字段上创建一个索引，就能将分页时排序产生的性能开销幻灭于无形。其实并非绝对。为了印证，我们继续以上述案例为例举证。

在RP_PLAN_LOG_T表中，字段PLAN_LOG_ID的值由序列号填充，并且在上面创建了UNIQUE INDEX：

现在，我们将ORDER BY的字段由OPERATE_TIME修改为PLAN_LOG_ID，我们来看看执行计划：

嘿，还真如我们所料：利用了索引数据有序的特性，COST也相当得低。

是真实的性能呢？通过SQL*MONITOR，我们发现耗时竟达66S。

其中IO等待耗时54S，为何？原来这个执行计划实际加载了45M的数据量，这个就是全表的数据量。

由此可见，理想是丰满的，而现实却一地排骨。利用索引数据有序的特性做分页排序，是要讲究缘分的，可遇而不可求。必须要满足如下两个条件：

排序字段上必须要建有（前缀）索引；在多表关联的SQL中，排序字段所在表，必须为执行计划中的驱动表

否则，反而事与愿违适得其反。

化腐朽为神奇，以四两拨千斤

至此，为何RP_PLAN_LOG_T做驱动表的性能会高？这个问题就迎刃而解了。

我们再次通过SQL*MONITOR来回顾下执行计划：

表面上，我们看到的是通过PROJECT_NUMBER和OPERATE_TIME过滤后的结果集多大170万，而事实上，Oracle优化器巧妙的利用了OPERATE_TIME索引字段的排序：

只获取了15条记录，用这15条记录来驱动，即便千万级集合，也会是弹指一挥间；省却了庞大结果集排序的开销，SORT的COST灰飞烟灭。

End.

在上篇《提高mysql千万级大数据SQL查询优化几条经验(1)》中我们从几个方面提出了15条优化或是注意的。接下来我们接着看还有那些需要注意的。

本节主要内容：

1：字段数据类型上需注意

2：临时表使用需注意

16：尽量使用数字类型字段，若只含有数值信息的字段，尽量不要设计字符类型，这会降低查询和连续查询性能，并会增加存储开销。这是因为引擎在处理查询和连接时候会逐个比较字符串中每一个字符，而对于数字类型来说只需要比较一次就够了。

另：mysql

17：尽可能的使用varchar/nvarchar 代替 char/nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高许多。

18：任何地方都不要使用select * from user ，使用具体的字段列来代替"*".不要返回用不到的任何字段。

19：尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限(只有主键索引)

20：避免频繁创建和删除临时表，以减少系统表资源消耗

21：临时表并不是不可使用，适当的使用它们可以使某些更有效。

例如，当需啊哟重复引用大型表或者常用表中的某个数据集的时候。但是，对于一次性事件，最后使用导出表。

22：在创建临时表时，如果一次性插入数据量很大，那么可以使用select into 语句来代替create table，避免造成大量log日志，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后 insert.

23：如果使用到了临时表，在存储过程的最后务必将所有临时表显示删除，先truncate table，然后 drop table.这样可以避免系统表的较长时间锁定。

24：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过一万行，那么就应该考虑改写了。

25：使用基于游标的方法或是临时表方法之前，应先找基于集的解决方案来解决问题，基于集的方法通常更有效。

26：与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

27：在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

28：尽量避免大事务操作，提高系统并发能力。尽量避免大事务操作，提高系统并发能力。

29：.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

相关文章：

提高mysql千万级大数据SQL查询优化几条经验(1)

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%c%’

若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。

可用group by……having来实现。

可做如下测试：

1、创建表插入数据：

create table test(id int,name varchar(10))insert into test values (1,'张三')insert into test values (2,'李四')insert into test values (3,'张三')insert into test values (4,'王五')insert into test values (5,'赵六')

其中name是张三的有两行，也就是重复行。

2、执行sql语句如下：

select * from test where name in (select name from test group by name having COUNT(*)>1)

结果如图：

（本文内容由百度知道网友badkano贡献）