sql知识点小汇总
sql教程栏目介绍sql常用知识点。
推荐：sql教程
当然，有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是，它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时，必须在查询中调整数据的形态，使之适应应用程序所要求的显示方式。虽然 TABLE 数据类型会减少大量占用资源的情况，但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如，如果希望返回每个客户及其定单，使用 LEFT JOIN 则可以显示有定单和没有定单的客户。
此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多，因为它们包含与 NULL（不存在）数据匹配的数据。在某些情况下，这是不可避免的，但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多，所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN，则会得到非常可观的回报（请参阅图 1 中的图）。
sql语句中join on和where用法的区别和联系
对于要达到同一查询结果而言，join和where的用法是语句格式不一样，查询的结果是一样的。
先来看看join的语句分类：
left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。
full join:外连接，返回两个表中的行：left join + right join。
cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
转载自：：http://www.cnblogs.com/lcngu/p/6726537.html 下面这个是讲解outer join 查询 on筛选和where筛选的区别 个人认为是正确的
在连接查询语法中，另人迷惑首当其冲的就要属on筛选和where筛选的区别了， 在我们编写查询的时候， 筛选条件的放置不管是在on后面还是where后面， 查出来的结果总是一样的， 既然如此，那为什么还要多此一举的让sql查询支持两种筛选器呢？ 事实上， 这两种筛选器是存在差别的，只是如果不深挖不容易发现而已。
sql中的连接查询分为3种， cross join，inner join，和outer join ， 在 cross join和inner join中，筛选条件放在on后面还是where后面是没区别的，极端一点，在编写这两种连接查询的时候，只用on不使用where也没有什么问题。因此，on筛选和where筛选的差别只是针对outer join，也就是平时最常使用的left join和right join。
来看一个示例，有两张数据表，结构和数据如图所示
表main
表ext
可以把这两张表看作是用来存放用户信息的， main放置主要信息，ext表放置附加信息，两张表的关系是1对1的，以id字符作为对应关系键。现在我们需要将地址不为杭州的所有用户信息筛选出来，结果中需要包含main表和ext表的所有字段数据。
select * from main left JOIN exton main.id = ext.id and address <> '杭州'
闭上眼睛， 请用大脑人肉运行一下这段SQL， 想象一下是什么结果。
当把 address <> '杭州' 这个筛选条件放在on之后，查询得到的结果似乎跟我们预料中的不同，从结果中能看出，这个筛选条件好像只过滤掉了ext表中对应的记录，而main表中的记录并没有被过滤掉，也就是上图中标记为红色的那条记录。outer join相对于inner join的一个主要特性就是以一侧的表为基础，但是在这里以左表为基这一点却可以无视筛选条件，这未免也太霸道了一些。
把查询语句稍微改动一下，将地址的筛选条件从on转移至where
select * from main left JOIN ext on main.id = ext.id where address <> '杭州'
结果就如我们预期的那样了
造成这种结果上的差异要从outer join查询的逻辑查询的各个阶段说起。总的来说，outer join 的执行过程分为4步
1、先对两个表执行交叉连接(笛卡尔积)
2、应用on筛选器
3、添加外部行
4、应用where筛选器
就拿上面不使用where筛选器的sql来说，执行的整个详细过程如下
第一步，对两个表执行交叉连接，结果如下，这一步会产生36条记录（此图显示不全）
第二步，应用on筛选器。筛选器中有两个条件，main.id = ext.id and address<> '杭州'，符合要求的记录如下
这似乎正是我们期望中查询的结果，然而在接下来的步骤中这个结果会被打乱
第三步，添加外部行。outer join有一个特点就是以一侧的表为基，假如另一侧的表没有符合on筛选条件的记录，则以null替代。在这次的查询中，这一步的作用就是将那条原本应该被过滤掉的记录给添加了回来
是不是不种画蛇添足的感觉, 结果就成了这样
第四步，应用where筛选器
在这条问题sql中，因为没有where筛选器，所以上一步的结果就是最终的结果了。
而对于那条地址筛选在where条件中的sql，这一步便起到了作用，将所有地址不属于杭州的记录筛选了出来
通过上面的讲解，已经能反应出在outer join中的筛选条件在on中和where中的区别，开发人员如能详细了解之中差别，能规避很多在编写sql过程中出现的莫名其妙的错误。
转载自：：https://blog.csdn.net/wang1127248268/article/details/53413655
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。
为了获得稳定的执行性能，SQL语句越简单越好。对复杂的SQL语句，要设法对之进行简化。
常见的简化规则如下：
1）不要有超过5个以上的表连接（JOIN）
2）考虑使用临时表或表变量存放中间结果。
3）少用子查询（可以使用 join）
4）视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
连接的表越多，其编译的时间和连接的开销也越大，性能越不好控制。
最好是把连接拆开成较小的几个部分逐个顺序执行。
优先执行那些能够大量减少结果的连接。
拆分的好处不仅仅是减少SQL Server优化的时间，更使得SQL语句能够以你可以预测的方式和顺序执行。
如果一定需要连接很多表才能得到数据，那么很可能意味着设计上的缺陷。
连接是outer join，非常不好。因为outer join意味着必须对左表或右表查询所有行。
如果表很大而没有相应的where语句，那么outer join很容易导致table scan或index scan。
要尽量使用inner join避免scan整个表。
优化建议：
1）使用临时表存放t1表的结果,能大大减少logical reads（或返回行数）的操作要优先执行。（个人感觉这个建议超级实用）
仔细分析语句，你会发现where中的条件全是针对表t1的，所以直接使用上面的where子句查询表t1，然后把结果存放再临时表＃t1中：
Select t1….. into #tt1 from t1 where…(和上面的where一样)
2）再把＃tt1和其他表进行连接:
Select #t1…
Left outer join …
Left outer join…
3）修改 like 程序，去掉前置百分号。like语句却因为前置百分号而无法使用索引
4）从系统设计的角度修改语句，去掉outer join。
5）考虑组合索引或覆盖索引消除clustered index scan。
上面1和2点建议立即消除了worktable，性能提高了几倍以上，效果非常明显。
1）限制结果集
要尽量减少返回的结果行，包括行数和字段列数。
返回的结果越大，意味着相应的SQL语句的logical reads 就越大，对服务器的性能影响就越甚。
一个很不好的设计就是返回表的所有数据：
Select * from tablename
即使表很小也会导致并发问题。更坏的情况是，如果表有上百万行的话，那后果将是灾难性的。
它不但可能带来极重的磁盘IO，更有可能把数据库缓冲区中的其他缓存数据挤出，使得这些数据下次必须再从磁盘读取。
必须设计良好的SQL语句，使得其有where语句或TOP语句来限制结果集大小。
2）合理的表设计
SQL Server 2005将支持表分区技术。利用表分区技术可以实现数据表的流动窗口功能。
在流动窗口中可以轻易的把历史数据移出，把新的数据加入，从而使表的大小基本保持稳定。
另外，表的设计未必需要非常范式化。有一定的字段冗余可以增加SQL语句的效率，减少JOIN的数目，提高语句的执行速度。
3）OLAP和OLTP模块要分开
OLAP和OLTP类型的语句是截然不同的。前者往往需要扫描整个表做统计分析，索引对这样的语句几乎没有多少用处。
索引只能够加快那些如sum，group by之类的聚合运算。因为这个原因，几乎很难对OLAP类型的SQL语句进行优化。
而OLTP语句则只需要访问表的很小一部分数据，而且这些数据往往可以从内存缓存中得到。
为了避免OLAP 和OLTP语句相互影响，这两类模块需要分开运行在不同服务器上。
因为OLAP语句几乎都是读取数据，没有更新和写入操作，所以一个好的经验是配置一台standby 服务器，然后OLAP只访问standby服务器。
4）使用存储过程
可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑，这样做有几个好处。
一是存储过程的执行计划可以被缓存在内存中较长时间，减少了重新编译的时间。
二是存储过程减少了客户端和服务器的繁复交互。
三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。
索引优化
很多数据库系统性能不理想是因为系统没有经过整体优化，存在大量性能低下的SQL 语句。
这类SQL语句性能不好的首要原因是缺乏高效的索引。
没有索引除了导致语句本身运行速度慢外，更是导致大量的磁盘读写操作，使得整个系统性能都受之影响而变差。
解决这类系统的首要办法是优化这些没有索引或索引不够好的SQL语句。
创建索引的关键
优化SQL语句的关键是尽可能减少语句的logical reads。
这里说的logical reads是指语句执行时需要访问的单位为8K的数据页总数。
logical reads 越少，其需要的内存和CPU时间也就越少，语句执行速度就越快。
不言而喻，索引的最大好处是它可以极大减少SQL语句的logical reads数目，从而极大减少语句的执行时间。
创建索引的关键是索引要能够大大减少语句的logical reads。一个索引好不好，主要看它减少的logical reads多不多。
运行set statistics io命令可以得到SQL语句的logical reads信息。
set statistics io on select au_id,au_lname ,au_fname from pubs..authors where au_lname ='Green' set statistics io on
发表评论