可用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贡献）

489034603

最近总是在QQ上遇到有朋友问一些SQL的语句语法，对于初学大数据的小伙伴来说，可能以前没有学过数据库，下面是整理的一些SQL的语句语法，可能在其它地方上也有，凑合看看

下列语句部分是Mssql语句，不可以在access中使用。 SQL分类：

DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)

DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)

DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)

首先,简要介绍基础语句：

1、说明：创建数据库

CREATE DATABASE database-name

2、说明：删除数据库 drop database dbname

3、说明：备份sql server --- 创建 备份数据的 device USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始 备份

BACKUP DATABASE pubs TO testBack

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2 from tab_old definition only

5、说明：删除新表drop table tabname

6、说明：增加一个列

Alter table tabname add column col type

注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col)

8、说明：创建索引：create [unique] index idxname on tabname(col.) 删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：

创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

11、说明：几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接

A、left outer join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 sql: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 其次，大家来看一些不错的sql语句

1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

7、说明：在线视图查询(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a >1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法 select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

上面这些语句语法是大数据访问数据库经常要用到的，需要熟透到心中，数据越大，进行查表的语句就应该越简单，才可以加快查询速度。。

注意：想学习大数据技术的伙伴请加第一张图片下方的学习交流群，群里有大量视频可自行下载。大家关注小编可以获取更多新消息的哦！！

    相同sql在不同数据库耗时不同分析    sql查询时间不同，有很多方面影响，上层比如索引、数据量、分区、锁等原因，下层存储、架构等原因，具体事件具体分析，以下只是我遇到的一种情况。操作步骤：1：执行sql2：执行计划对比3：数据总量对比4：表结构对比5：索引对比6：数据行数对比 7：建议

执行sqlselect * from FFXX where FFXXID in (select min(FFXXid) from FFXX where SJZT=1 and FJBJ=3 and fjr=1 and JGSJ>=to_date('2014-11-22 00:00:00','yyyy-mm-dd HH24:Mi:SS') and JGSJ

执行计划对比分析：B库该表分区，条件时间所在分区数据基数非常小，所以直接走全表扫描C库该表不是分区，索引基数非常大，但是走索引是该表最好的方式

数据总量对比select sum(bytes)/1024/1024 from dba_segments where segment_name='FFXX';分析：从中可以看出sql在B库数据库执行该表，数据总范围是32M但是在C库数据库总范围是2628M

表结构对比分析：B库该表创建了分区，C库没有创建分区

索引对比分析：B库时分区索引，C库时普通索引

数据行数对比分析：B库所在分区数据量才8W，C库是749W 

建议将C库数据库中的该表该造成分区表 注释：分区表的好处之一是减少数据查询范围总量

1.查询的模糊匹配

尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分号会导致相关列的索引无法使用，最好不要用。

解决办法:

其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下：

1.1 修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。

1.2 直接修改后台——根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联

2.索引问题

在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记录越来越多

这时缺少索引，对性能的影响便会越来越大了。

这个问题需要数据库设计人员和开发人员共同关注

法则：不要在建立的索引的数据列上进行下列操作:

避免对索引字段进行计算操作避免在索引字段上使用not，<>，!=避免在索引列上使用IS NULL和IS NOT NULL避免在索引列上出现数据类型转换避免在索引字段上使用函数避免建立索引的列中使用空值。3.复杂操作

部分UPDATE、SELECT 语句 写得很复杂（经常嵌套多级子查询）——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作

4.update

同一个表的修改在一个过程里出现好几十次，如：

update table1set col1=...where col2=...;update table1set col1=...where col2=.........

象这类脚本其实可以很简单就整合在一个UPDATE语句来完成（前些时候在协助xxx项目做性能问题分析时就发现存在这种情况）

5.在可以使用UNION ALL的语句里，使用了UNION

UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本中几个子集的记录绝对不可能重复，故可以改用UNION ALL）

6.在WHERE 语句中，尽量避免对索引字段进行计算操作

这个常识相信绝大部分开发人员都应该知道，但仍有不少人这么使用，我想其中一个最主要的原因可能是为了编写写简单而损害了性能，那就不可取了

9月份在对XX系统做性能分析时发现，有大量的后台程序存在类似用法，如：

......where trunc(create_date)=trunc(:date1)

虽然已对create_date 字段建了索引，但由于加了TRUNC，使得索引无法用上。此处正确的写法应该是

where create_date>=trunc(:date1) and create_date

或者是

where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)

注意：因between 的范围是个闭区间（greater than or equal to low value and less than or equal to high value.），

故严格意义上应该再减去一个趋于0的小数，这里暂且设置成减去1秒（1/(24*60*60)），如果不要求这么精确的话，可以略掉这步。

7.对Where 语句的法则

7.1 避免在WHERE子句中使用in，not in，or 或者having。

可以使用 exist 和not exist代替 in和not in。

可以使用表链接代替 exist。Having可以用where代替，如果无法代替可以分两步处理。

例子

SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN(SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist(SELECT CUSTOMER_NAME FROM CUSTOMER)

7.2 不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）否则会使索引无效，产生全表扫描。

例子使用：

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’

8.对Select语句的法则

在应用程序、包和过程中限制使用select * from table这种方式。看下面例子

使用SELECT empno，ename，category FROM emp WHERE empno = '7369‘

而不要使用SELECT * FROM emp WHERE empno = '7369'

9. 排序

避免使用耗费资源的操作，带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序

10.临时表

慎重使用临时表可以极大的提高系统性能

为了帮助小伙伴们更好的学习Python，技术学派整理了Python的相关学习视频及学习路线图。

领取方式

关注“技术学派”后，评论转发文章，私信回复：Python学习

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

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

最好不要给数据库留NULL，尽可能的使用NOTNULL填充数据库.

备注、描述、评论之类的可以设置为NULL，其他的，最好不要使用NULL。

不要以为NULL不需要空间，比如：char(100)型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用100个字符的空间的，如果是varchar这样的变长字段，null不占用空间。

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

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

4.应尽量避免在where子句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10orName='admin'

可以这样查询：selectidfromtwherenum=10unionallselectidfromtwhereName='admin'

5.in和notin也要慎用，否则会导致全表扫描，如：selectidfromtwherenumin(1,2,3)

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

很多时候用exists代替in是一个好的选择：selectnumfromawherenumin(selectnumfromb)

用下面的语句替换：selectnumfromawhereexists(select1frombwherenum=a.num)

6.下面的查询也将导致全表扫描：selectidfromtwherenamelike‘%abc%’

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

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

可以改为强制查询使用索引：selectidfromtwith(index(索引名))wherenum=@num

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

应改为:selectidfromtwherenum=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：selectidfromtwheresubstring(name,1,3)=’abc’-–name以abc开头的idselectidfromtwheredatediff(day,createdate,’2005-11-30′)=0-–‘2005-11-30’--生成的id

应改为:

selectidfromtwherenamelike'abc%'selectidfromtwherecreatedate>='2005-11-30'andcreatedate<'2005-12-1'

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

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

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

selectcol1,col2into#tfromtwhere1=0

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

createtable#t(…)

13.Update语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

14.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

15.selectcount(*)fromtable；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

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

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

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

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

20.任何地方都不要使用select*fromt，用具体的字段列表代替“*”，不要返回用不到的任何字段。

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

22.避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

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

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

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

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

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

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

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

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

实际案例分析：拆分大的DELETE或INSERT语句，批量提交SQL语句

如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。

所以，如果你有一个大的处理，你一定把其拆分，使用LIMIToracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例：

while(1){//每次只做1000条mysql_query(“deletefromlogswherelog_date<=’2012-11-01’limit1000”);if(mysql_affected_rows()==0){

前言：通常SQL优化的常见手段包括，收集统计信息，创建索引，重写SQL语句，而大家日常遇到的CASE中是否也有遇到过，SQL已经看似没有优化空间，系系统整体性能也非常良好，但是SQL语句就是达不到预期执行效率的呢？今天作者给大家分享的就是一个不算SQL优化的“SQL优化"，希望大家能有所启发；

批量语句太慢了

某日早上，客户应用维护的同事提出问题，有一个每个工作日都执行的批量SQL语句执行速度太慢，如果不处理，恐怕以后会影响到联机开门业务；该SQL执行还有一个特点，虽然每天处理的数据量差不多，但是执行速度却时快时慢，快的时候可能1个小时左右执行完，慢的时候需要3、4个小时；具体SQL语句如下：

该语句并不复杂，是一个merge语句，涉及的表也不多，主要目的是将某个BANK_NO的相关数据归集到XXX_REST_INVM1_C表中；

通过相关脚本查看该语句的执行历史情况：

可以看到，该SQL的执行计划一直使用的是3134845891，近期未发生过改变；

而从执行时间上看，该SQL在20180612当日执行时间从早上3点多执行到6点多，而20180610当日在一个小时内即已经执行完成；

再查看执行计划如下：

从执行计划来看，存在几个全表扫描和一个表的索引范围扫描，所有的关联方式都使用的hash join；

解决思路

1、优化执行计划，让SQL执行足够快，这样即使有差异，也不会感觉太明显；

2、如果无法优化执行计划，那么找到快慢的变化因素，让SQL以最快的速度运行；

那么，接下来该如何分析呢？经验丰富的你，看到上面的语句，是否有优化的思路呢？思考一下，精彩马上继续……

分析执行计划

通常，我们说一个执行计划的好坏，是需要结合实际访问数据来评判的；在11g以后，oracle提供了一个很好的sqlmonitor的功能，对于大多数执行时间偏长的SQL语句，它都能记录非常详细的信息来帮助我们分析；这里我们sqlmonitor进行分析：

分析执行计划：

全表扫是否合适：从执行计划中的几个全表扫描来看，XXX_INVM1_B和XXX_REST_INVM1_C表返回的记录数较多，分别达到了2M和1M条记录，这里全表扫描是正确的；对于表XXX_DEPP_B，单个分区只有292条记录，占用空间也不大，这里走全表扫描影响也不大；走索引的XXX_NAME_B表是否合适：从返回的记录数来看，大约在108K条左右，这里在返表后未再做filter，这里走索引也是合适的；关联方式使用的hash join是否合适：从最终的返回记录数看，最后返回记录数在1M条左右，数据量偏大；而且从SQL逻辑来看，实际传入的参数只有一个BANK_ID字段，说明该merge语句是针对某一分行数据的集中处理，需要处理的数据量都会比较大；所以，这里使用hash join是合适的；

看起来执行计划的选择上来说，都没有太大的问题；

再仔细看看sqlmonitor的内容，Activity(%)这一列显示，在SQL执行过程中，merge过程占了整个执行过程的88.99%，而且从等待事件上看，该过程主要是在CPU上，还有少量的数据文件单块读；哦？难道是merge过程中有什么特殊的操作吗？难道问题在这里？

小小的插曲

从SQL文本来看，我们注意到update部分我们还能看到调用了FN_GEN_POSTNAME函数，从内容上看，该函数可能是导致merge部分CPU使用率特别高的原因之一；但是该函数内容主要为将传入的各个字段做拼接整合，并不涉及SQL语句的执行；事实上，我们从前面的sqlmonitor知道最终join完成后返回的记录数达到100万左右，对每条记录都执行一次该函数，执行纯数据/字符运算，消耗较多的CPU也是可以理解的，也就是说，这块的内容，其实并不能进行优化；

总的来说，就这个SQL语句的执行计划而言，看起来并不具备较大的优化空间！

既然优化执行计划的路子不通，那么接下来就先分析分析同样执行计划，同样业务数据量的情况下，为何时快时慢了；

换个思路

根据与应用维护团队的沟通了解来看，也以对该系统的业务特点来看，应该不存在单日数据量太大变化的情况，也正是因为SQL执行时间的不稳定，他们才提出了优化需求；

那是否某些情况下，系统IO情况缓慢或者其他原因呢？不如直接看看等待事件的分析来看：

我们需要注意的是，这里核查的是dba_hist_acitve_sess_history视图，该视图记录的是10秒一次采样的活动会话数据；

其中大量的ON CPU的记录，正是如sqlmonitor中显示的merge部分，以及几次hash可以join的过程理解；

少量db file scattered read的等待发生在几次做全表扫描的过程中；

而这里 db file sequential read 被采样的次数就有点偏多了，整个执行计划中，只有一次对表访问中使用了索引扫描而后回表操作，而这里显示db file sequential read的采样次数比db filescattered read要多很多，单独核查该部分的信息如下；

看起来，这部分主要是访问UNDO表空间了，嗯，只有看起来也许是在访问过程中构造一致性读导致的？不过，这里应该并不重要，毕竟占比还是不高，读写速度也还是正常的；

另外，我们对比分析6月12日和6月10日的等待事件，各种等待事件的比例是相当的，并不是因为异常等待事件过多而导致的时间过长；

而之前6月10日的sqlmonitor肯定是看不到了，所以我们也不能确定12日需要处理的数据量就一定比10日处理的要多；只不过在与应用沟通过程中，从历史数据的分析中，我们能知道最近每天需要处理的数据量是相当的；

如此看来，来分析解决这个问题的两个维度都已经没有前路了，该如何继续呢？盯着眼前的等待事件的，突然感觉之前似乎忽略了些什么，看来有了新方向！

转机

我们再来仔细看看等待事件的统计：

再对比此前的sqlmonitor信息：

你发现了什么吗？不妨停下来仔细看看，看看之前是漏掉了什么？想清楚了之后再往下翻哦……..

前面统计等待事件时，使用的dba_hist_active_sess_history视图；该视图的记录为10秒做一次采样，这里的采样超过800次，那么执行超过8000秒；而我们刚刚sqlmonitor中记录的执行时间好像没有执行那么久啊，原来并没有特别注意到sqlmonitor中的执行时间：

注意：事实上，如果我们只是查看dbms_sqltune.report_sqlmonitor(‘&sql_id’)，数据库会默认返回最近一次执行的monitor信息（前提是sqlmonitor中包含该次执行的信息）

仔细观察sqlmonitor的信息，可以发现，该次SQL的执行的开始时间是在06:25左右，执行时间也就持续1000秒左右，理论上这个执行速度是满足批量任务的要求的；

我们再换个角度看一下SQL的执行历史情况：

原来，从03:20开始，这条SQL在同一会话上连续执行了至少5次，执行的SQL_EXEC_ID是连续的，而且执行时间上看也是连续的；那是怎么回事呢？难道是对多个不同的BANK_ID进行了操作？我们继续通过sqlmonitor指定SQL_EXEC_ID来收集相关信息进行确认：

通过对多次执行过程的信息分析，我们可以看到每次其实执行的都是对BANK_NO为138的数据的处理！难道应用的调用或者调度出现了问题？同一事情做了5次？不过这5次的速度也不尽相同？

跟应用维护同事反复沟通，实际上这条SQL是在存储过程中执行的，上下文还有许多SQL需要执行，那些SQL不会在10秒内执行完成，即使是重复调用也就不会像我们前面看到的那么密集的在一个会话中重复执行；而且在应用的调用过程中，会记录对应的执行日志，这里也并没有记录对应的日志；总而言之，这条SQL只执行了一次，不应该出现这种情况……….

到这里，不用应用维护同事过多解释，其实作者心中已经差不多有了自己的答案，接下来需要的只是一步一步的确认而已！各位看官是不是也已经有了自己的答案呢？如果还没有，不妨停下来想一想………….

经验与验证

其实这里只是日常处理故障的经验的体现，处理到这里的时候，我才想起以前遇到过类似的案例，只是在没有注意到这些细节时是无法匹配上那次的经验的；我此前处理类似的案例时，查到过MOS上有一篇文档专门讲过SQL RESTART的；该文档的重点在于：如果你看到一条DML的SQL出现连续的SQL_EXEC_ID在一个会话上重复执行时，那么有可能是在执行该SQL的过程中，对应的SQL游标已经失效或者回滚段出现了无法扩展的情况，这个时候，oracle会自动重新执行该SQL；

那么这里可能出现了什么情况呢？我们首先检查SQL游标的情况：

这里可以看到，SQL的最近的解析时间是在06:25，也就是我们上面看到的最后一次执行的开始时间点；

我们的SQL最早在3:20就开始执行，而最新的解析时间是在06:25，显然这个过程中游标发生过失效；为什么失效呢？几个方面：统计信息收集，对表的DDL操作或者被挤出shared_pool区域，从这里看，正在执行的SQL应该不会被挤出shared_pool区域，而检查并没有发现涉及该SQL的表在3:00以后有统计信息收集的操作；不过，检查SQL涉及的几个表，我们能看到其中的XXX_NAME_B表确实在SQL的执行过程中做过数次对其分区的DDL操作：

至此，真相大白了，正是对于表XXX_NAME_B的DDL操作导致了SQL的不断重新执行，进而显得整个SQL执行非常慢；

前面我们提到的几点都有了解释：

执行慢，并不是执行计划的原因，而是多次执行的原因；执行时快时慢，并不是涉及的单次SQL性能的好坏，而是执行次数的多少而已；

最后的小疑问

这里我们可能还会有几个小疑问：

Q：为什么重复执行几次，每次执行的时间长度还不一样，而且中间执行的某次已经大大超过了最后一次正常执行完成锁消耗的时间呢？

A：其实，除最后一次外，前面的数次执行都是没有完成的，而且在再次开始执行之前，需要进行回滚操作，通常来说，回滚的速度是偏慢的，而且执行持续的时间越长，回滚所需的时间也就越长，所以我们才看到上述情况；

Q：多次执行开始时间似乎与DDL时间并不完全吻合呀？

A：同理，在再次开始之前需要回滚操作；DDL只是触发cursor失效，然后开始回滚，回滚完成才能重新开始执行；

综合上面，我们又可以注意到前面提到的发生在UNDO表空间的‘db file sequential read’等待实际上是回滚过程造成的；

解决方案&总结

这里了解到是由于DDL造成的cursor失效继而导致SQL自动restart的情况，我们与应用沟通确认是由于另外的批量任务在执行完相关操作后，会对XXX_NAME_B表的各自分区进行truncate操作，truncate之后每天新的业务数据再往相关分区中灌入数据，而truncate操作实际上也是DDL操作；在了解到其实该表的各分区中记录数并不大未来也不会有大的增长后，建议应用修改对应的批量程序，将原来的truncate操作改成针对全表的delete操作，调整完成后观察到，该SQL在后续的执行中，每天只执行一次，执行时间也缩短到16分钟；

以往对于数据清理操作，特别是这种可以对无全局索引、整个单独分区的清理，我们都会建议使用truncate操作来完成，因为truncate通常更快，在批量时段对于SQL的影响也可以忽略，而delete操作相对来说执行时间会更久一些，通常还会占用一些undo空间；然而，从这个CASE来看，凡事不能一概而论，truncate对于SQL执行过程中的性能也不可忽略；

另外，关于这个CASE，主要关注的MOS文档如下：

关键点涉及几个：

分区表DML操作CURSOR的失效或者回滚段无法扩展的错误，该错误并不向用户抛出，而是直接restart这个SQL的执行；

实际上，这类CASE的触发除了上述条件外，还有其他条件，从ORA-14403的描述来看，这应该是针对以往版本的某个bug的修复后造成的现象，不过在该篇文档中未提及以往修复的bug，也不认为这里是一个bug。

转载自百家号作者：从小白到架构师

本文与之前的文章已经涵盖了了MyBatis所有的使用方法，所以这是最后一篇主们讲解MyBatis框架的文章了。我们都知道MyBatis的SQL语句是写在mapper.xml文件里面的,动态SQL指通过MyBatis提供的标签实现动态拼接SQL,在mapper的根元素下还有许多标签,请看下图,都有哪些标签

cache是MyBatis提供的二级缓存,目前几乎是已经作废的,我们可以通过第三方缓存框架ehcache等或分布式缓存进行替代。cache-ref用于引用其他标签下的namespace属性，这样我们可以建立共享的全局的缓存配置。delete,insert,select,update四个标签分别用于写入相对数据操作的增删改查,parameterMap该标签已被废弃,接下来的版本中MyBatis官方明确表示该标签会被移除,resultMap用于手动映射Java对象与数据库中的表,上一篇文章有具体使用方法,最后我们来看一看这个标签的用法

使用起来非常简单,它的属性只有一个id,这个id名字我们可以随便取,用于被其他标签引用。sql标签可以成为sql片段,我们将所有的查询语句中公共的sql片段抽取出来,能够简化sql语句,在hibernate框架中的hql也是有一定的缩减。

然后我们在其他标签中创建include标签,其属性refid=sql片段的id名即可,那么这块include标签就等同于select * from .使用起来还是比较简单的。

在select标签内部其实还有一些标签

先看foreach标签,见名知意这应该是起到循环或迭代作用的一个标签。该标签能够解决批量查询的问题,假设我们要查询id为1,2,3的用户,sql的写法是select * from user where id in #{value};那么1,2,3如果作为入参被传进来呢，我们可以通过包装类,或者List集合,Integer数组的形式将参数传入。请看下图

图中做法是在包装类的内部封装了一个List集合,这里的传入参数就必须要写QueryVo,看下图mapper中是如何配置的

可以看到入参类型为QueryVo,但是要想表示QueryVo下ids集合中的元素就要用到foreach标签了,该标签常用在构建in语句时使用,它能够遍历集合中的元素,collection属性表示引用的集合名称,上图中我们创建的集合名为ids,那么这里写ids。item为本次迭代的变量名,可以随便取，内部的ONGL表达式取值时,需要通过item的名字,可以理解为#{ item }，separator为分隔符,我们in语句后的1,2,3全部用" , "分割,所以这里写"，"。open表示 sql语句中集合元素起始部分本sql语句为 select * from user where id in (1,2,3); 这里以(开头，那么close就是结束部分,以)结束。以上都是为完成sql语句拼接所准备的,为了方便我们也可以将id in 这段写入open中,理论上where后集合前的语句都可以写在open里。

我们必须要考虑其他情况如果传入的参数类型不是包装类而是其他集合该怎么写呢？假设这次我们传入一个List集合，看看会有什么变化

这时候我们的入参只有集合,那么collection的属性不能再写List的变量名了,而是需要指定类型list,一次类推set,数组用array，map等,都需要在这里指定类型,唯独包装类在collection写的是内部集合的变量名字。当我们传入的集合为map时,需要增加一个新的属性,index,用来表示map的K值,同样其他K，V结构的对象也是一样。OGNL表示要这样写,#{index },如果遍历的集合不是map时，index用来表示当前遍历的次数。

还有一个标签bind以从 OGNL 表达式中创建一个变量并将其绑定到上下文，请看下图

原本OGNL表达式中的内容通过pattern引用，可以写到value里面了,但是要注意写法很容易出问题,不过这真的是一个非常灵活的框架,实现一个目的有多种方法,这就是通过bind标签实现模糊查询。下面我们进行关联查询

先补充一下表连接的知识,以下方的sql语句为例

select * from orders o left join user u on o.user_id = u.id;

left join查询的是orders表中所有记录与user表中的u.id=o.user_id的记录;

inner join查询到的是只有u.id=o.user_id的记录;

right join查到的是user表中所有记录与user表中的u.id=o.user_id的记录;

full join 查到的是user表与orders表中所有记录。在mysql中不支持full join全连接查询。

这是一个一对一查询在mapper中的写法首先看select标签中的内容,本次查询不需要入参,但是ResultType结果集在这种情形下已经是不适用了,因为我们会返回user的所有字段,此时结果集类型选择为Orders的话讲不能完成自动映射。只要是关联查询,结果集必然会用到resultMap手动映射,于是在上面创建resultMap标签,id与下面对应,类型就是Orders,如果左边的表为user的话,那么此处类型就是user。userId本是Orders里面的属性,但与数据库字段名不同所以需要手动映射，下面我们需要创建一个association标签,这个标签用于一对一关联。它的property属性是用在表示在Orders中用来表示User的变量名,所以为了让框架找到变量的类型,我们这里需要制定JavaType。然后通过单标签result来映射Orders下的user变量的属性。

这是它的java代码部分,很简单下面看一下查询结果

太多null了,是怎么回事呢？首先教大家怎么打印拼接后的sql语句,来检查是不是sql的问题。

SqlMapConfig中,全局参数配置加一条,value的地方如果没有log4j包的话就按照我图中的写，有就写log4j即可。看到打印出的sql语句是没有问题的,那么问题一定出来映射上。我发现只要在resultMap中删除association标签,那么左表的数据都能正常显示,有了association就都变成null了,这是为什么？我们在做单表查询时，可以有选择的指定resultMap需要映射的字段,而做关联查询时则必须映射需要显示的字段,表示可以理解,如果两张表中有相同字段框架也是不知道到底该映射哪个,如果出现相同字段名必须取别名或者只显示其中一个。那么我在下面重新将为映射字段补全

查询结果

可以看到,除了数据库中本来就是null,所有信息都能正常显示了,真是一点不能省啊,这意味着如果有1000个字段也必须一一对应,谁都跑不了！

接下来我们看看一对多的情形

如果理解1对1关联查询,那么一对多,多对多也都是小菜一碟了。唯一的一处改动,我们将关联标签association换成了collection集合,而JavaType也变成ofType用来表示集合的泛型的类型。那么到底什么一对一与一对多有什么区别呢？当我们使用left join时,需要根据实际业务需求来判断以左边的表为中心,是一对一还是一对多,还是多对多。结果如下

查询到8条数据,有条数数据与order。多对多的映射,表的左边一方不变,右边映射与一对多相同。到这里MyBatis的内容就基本结束了,MyBatis还有一个Sql类,类似hibernate的critrica对象,在java类的内部写sql语句,不过我们在mapper写sql已经足够了。再就是通过spring对MyBatis进行进一步整合,明天会讲解Spring框架的使用。持久层的框架还是相对比较简单的,但是大家一定要理解它的架构,在bug时能帮我们缩小问题范围,当然了基础足够扎实,bug几乎是不会出现的。希望各位能够在评论区积极留言进行讨论,也可以回复想要学习的知识！

高并发是指在同一个时间点，有很多用户同时的访问URL地址，比如：淘宝的双11，双12，就会产生高并发,如贴吧的爆吧，就是恶意的高并发请求，也就是DDOS攻击，再屌丝点的说法就像玩撸啊撸被ADC暴击了一样,那伤害你懂得(如果你看懂了，这个说法说明是正在奔向人生巅峰的屌丝。

高并发会来带的后果

服务端：

导致站点服务器/DB服务器资源被占满崩溃，数据的存储和更新结果和理想的设计是不一样的，比如：出现重复的数据记录，多次添加了用户积分等。

用户角度：

尼玛，这么卡，老子来参加活动的，刷新了还是这样，垃圾网站，再也不来了。

我的经历：

在做公司产品网站的过程中，经常会有这样的需求，比如什么搞个活动专题，抽奖，签到，搞个积分竞拍等等，如果没有考虑到高并发下的数据处理，那就Game Over了，很容易导致抽奖被多抽走，签到会发现一个用户有多条记录，签到一次获得了获得了多积分，等等，各种超出正常逻辑的现象，这就是做产品网站必须考虑的问题，因为这些都是面向大量用户的，而不是像做ERP管理系统，OA系统那样，只是面向员工。

下面我进行实例分析，简单粗暴，动态分析，纯属本人个人经验分享，如有说错，或者有更好的建议或者意见的请留言，大家一起成长。

并发下的数据处理：

通过表设计,如：记录表添加唯一约束，数据处理逻辑使用事物防止并发下的数据错乱问题

通过服务端锁进程防止包并发下的数据错乱问题

这里主要讲述的是在并发请求下的数据逻辑处理的接口,如何保证数据的一致性和完整性，这里的并发可能是大量用户发起的，也可能攻击者通过并发工具发起的并发请求

如例子：通过表设计防止并发导致数据错乱

需求点

【签到功能】 一天一个用户只能签到一次，

签到成功后用户获取到一个积分

已知表

用户表，包含积分字段

高并发意淫分析(属于开发前的猜测)：

在高并发的情况下，会导致，一个用户签到记录会有多条，或者用户签到后不止加一积分。

我的设计

首先根据需求我会添加一张签到记录表，重点来了，这张表需要把用户唯一标识字段(ID,Token)和签到日期字段添加为唯一约束，或者唯一索引，这样就可以防止并发的时候插入重复用户的签到记录。然后再程序代码逻辑里，先执行签到数据的添加(这里可以防止并发，添加成功后再进行积分的添加，这样就可以防止重复的添加积分了。最后我还是建议所有的数据操作都写在一个sql事务里面， 这样在添加失败，或者编辑用户积分失败的时候可以回滚数据。

如例子2（事务+通过更新锁 防止并发导致数据错乱 或者事物+Update的锁表机制）

需求点：

【抽奖功能】 抽奖一次消耗一个积分 抽奖中奖后编辑剩余奖品总数 剩余奖品总数为0，或者用户积分为0的时候无法进行抽奖

已知表:

用户表，包含积分字段 奖品表，包含奖品剩余数量字段

高并发意淫分析(属于开发前的猜测):

在高并发的情况下，会导致用户参与抽奖的时候积分被扣除，而奖品实际上已经被抽完了

我的设计:

在事物里，通过WITH (UPDLOCK) 锁住商品表，或者Update 表的奖品剩余数量和最后编辑时间字段，来把数据行锁住，然后进行用户积分的消耗，都完成后提交事物，失败就回滚。 这样就可以保证，只有可能存在一个操作在操作这件商品的数量，只有等到这个操作事物提交后，其他的操作这个商品行的事物才会继续执行。

如例子3（通过程序代码防止包并发下的数据错乱问题）

需求点：

【缓存数据到cache里】， 当缓存不存在的时候，从数据库中获取并保存在cache里，如果存在从cache里获取，每天10点必须更新一次，其他时间点缓存两个小时更新一次 到10点的时候，凡是打开页面的用户会自动刷新页面

问题点：

这里有个逻辑用户触发缓存的更新，用户刷新页面，当缓存存在的时候，会取到最后一次缓存更新时间，如果当前时间大于十点，并且最后缓存时间是10点前，则会从数据库中重新获取数据保存到cache中。 还有客户端页面会在10点时候用js发起页面的刷新，就是因为有这样的逻辑，导致10点的时候有很多并发请求同时过来，然后就会导致很多的sql查询操作，理想的逻辑是，只有一个请求会去数据库获取，其他都是从缓存中获取数据。(因为这个sql查询很耗服务器性能，所以导致在10点的时候，突然间数据库服务器压力暴增)

解决问题：

C#通过 （锁）lock，在从数据读取到缓存的那段代码前面加上锁，这样在并发的情况下只会有一个请求是从数据库里获取数据，其他都是从缓存中获取。

访问量大的数据统计接口

需求： 用户行为数据统计接口，用来记录商品展示次数，用户通过点击图片，或者链接，或者其他方式进入到商品详情的行为次数

问题点：

这接口是给前端ajax使用，访问量会很大，一页面展示的时候就会有几十件商品的展示，滚动条滚到到页面显示商品的时候就会请求接口进行展示数据的统计，每次翻页又会加载几十件

意淫分析：

设想如果同时有1W个用户同时在线访问页面，一个次拉动滚动条屏幕页面展示10件商品，这样就会有10W个请求过来，服务端需要把请求数据入库。在实际线上环境可能还会超过这个请求量，如果不经过进行高并发设计处理，服务器分分钟给跪了。

解决问题：

我们通过nodejs写了一个数据处理接口，把统计数据先存到redis的list里。(使用nodejs写接口的好处是，nodejs使用单线程异步事件机制，高并发处理能力强，不会因为数据逻辑处理问题导致服务器资源被占用而导致服务器宕机) 然后再使用nodejs写了一个脚本，脚本功能就是从redis里出列数据保存到mysql数据库中。这个脚本会一直运行，当redis没有数据需要同步到数据库中的时候，sleep，让在进行数据同步操作

高并发的下的服务器压力均衡，合理站点架设，DB部署

以下我所知道的：

服务器代理nginx，做服务器的均衡负载，把压力均衡到多台服务器

部署集群 mysql数据库， redis服务器，或者mongodb服务器，把一些常用的查询数据，并且不会经常的变化的数据保存到其他NoSQLDB服务器中，来减少数据库服务器的压力，加快数据的响应速度。

数据缓存，Cache

在高并发接口的设计中可以使用具有高并发能力的编程语言去开发，如：nodejs 做web接口

服务器部署，图片服务器分离，静态文件走CDN

DBA数据库的优化查询条件，索引优化

消息存储机制，将数据添加到信息队列中(redis list)，然后再写工具去入库

脚本合理控制请求，如，防止用户重复点击导致的ajax多余的请求，等等。

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

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

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

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

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

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

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

可用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贡献）