Mybatis 与 SQL

一、mybatis

1.1配置文件中参数类型pareameterType

参考：

《MyBatis传入参数与parameterType》

《Mybatis之传入参数》

极客学院上mybatis的专题

mybatis动态SQL语句 (官网)

1.2配置文件中返回值类型resultType和resultMap

官网文档

《Java Persistence with MyBatis 3(中文版)》

MyBatis 注解(重点关注@mapkey)

代码示例  代码示例2
一对一与一对多

备注：在mapper中的SQL语句，没有满足查询条件时，如果返回值是一个list则会是一个空list，上层应用可以直接往该list添加对象；而如果返回的是一个对象时则返回null。

1.3Mapper.xml各类型的非空判断

1.判断String是否为空
<if test="stringParam != null and stringParam != ''"></if>
2.判断Integer是否大于0
<if test="idParam !=null and idParam gt 0"></if>
3.判断List是否不为空
<if test="listParam !=null and listParam.size >0"></if>
4.判断String是否以某特定字符(比如此处的"user")开头
<if test="stringParam.indexOf('user') != -1"></if>
5.判断字符串是否等于特定字符(比如此处的user)
<if test='stringParam != null and stringParam == "user"'></if>

参考：csdn博客专栏<mybatis学习>

二、SQL语句

2.1.多表联合查询

2.1.定义：
inner join(等值连接) ： 只返回两个表中联结字段相等的记录(取两表的交集)；
left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录(取左侧表的全部数据)；
right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录(取右侧表的全部数据) ；
语法：select * from 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

2.2实例

表A记录如下	表B记录如下
aID               a Num    1                  a20050111 2                  a20050112 3                  a20050113 4                  a20050114 5                  a20050115	bID               bName 1                   2006032401 2                  2006032402 3                  2006032403 4                  2006032404 8                  2006032408

实验如下:
1.left join

sql语句如下: 
select * from A left join B  on A.aID = B.bID

结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
5                   a20050115                NULL              NULL
（所影响的行数为 5 行）

结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.
备注：注意left join中on后面的条件与where后面的条件区别。

2.right join
sql语句如下: 
select * from A right join B on A.aID = B.bID
结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
NULL           NULL                          8                     2006032408
（所影响的行数为 5 行）
结果说明:
        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join
sql语句如下: 
select * from A
inner join B 
on A.aID = B.bID

结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404

结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.  还有就是inner join 可以结合where语句来使用 如：   select * from A inner join B on A.aID = B.bID where b.bname='2006032401' 这样的话 就只会返回一条数据了

4.逗号

sql语句如下: 
select * from A,B where A.aID = B.bID

结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404

效果同 inner join on ，逗号其实是隐式的内连接，只返回匹配的行。

几个常用SQL：

SELECT
	t1.id,
	t1. NAME,
	t1.domain,
	upm_flag. NAME,
	upm_fo_user.user_id,
	upm_user.username,
	upm_user.username_zh
FROM
	(
		SELECT
			concat("app_administrator_fo_", id) AS tid,
			id,
			NAME,
			domain
		FROM
			upm_app
	) t1
LEFT JOIN upm_flag ON upm_flag. NAME = t1.tid
LEFT JOIN upm_fo_user ON upm_flag.id = upm_fo_user.fo_id
LEFT JOIN upm_user ON upm_user.id = upm_fo_user.user_id
WHERE
	upm_flag.app_id = 888
AND upm_flag. NAME LIKE "app_administrator_fo_%"
AND upm_fo_user.is_delete = 0;

SELECT
	r.id AS id,
	r.`name` AS NAME,
	r.name_zh AS nameZh,
	r.description AS description,
	r.app_id AS appId,
	r.`status` AS STATUS,
	r.created_at AS createdAt,
	td.id AS dimeNodeId,
	td.dime_node_name AS dimeNodeName
FROM
	upm_role r
LEFT JOIN upm_role_dime_relation rdr ON r.id = rdr.role_id and rdr.is_delete = 0
LEFT JOIN upm_tb_dimenode td ON rdr.dime_id = td.id
WHERE
	r.is_delete = 0
AND r.app_id =16 
ORDER BY
	r.created_at DESC
LIMIT 0,20

SELECT
    DISTINCT
	r.id AS id,
	r.`name` AS NAME,
	r.name_zh AS nameZh,
	r.description AS description,
	r.app_id AS appId,
	r.`status` AS STATUS,
	r.created_at AS createdAt,
	td.id AS dimeNodeId,
	td.dime_node_name AS dimeNodeName
FROM
	upm_role r
LEFT JOIN upm_role_dime_relation rdr ON r.id = rdr.role_id and rdr.is_delete = 0
LEFT JOIN upm_tb_dimenode td ON rdr.dime_id = td.id
WHERE
	r.is_delete = 0
AND r.app_id =16 
ORDER BY
	r.created_at DESC
LIMIT 0,20

2.3 Where/Group by/having

group by,where,having 是数据库查询中最常用的几个关键字。在工作中，时常用到，那么，当一个查询中使用了where ,group by ,having及聚集函数时 ，执行顺序是怎么样的？为了回答这个问题，将这个三个关键字的用法整理一下。

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。如下面这个例子，从user表中查询出userDepartmentId等于2的数据
select * from dbo.user where userDepartmentId=2
备注：此处需要注意与多表联合查询时的left join table on codition(筛选条件)的区别，where 后面的codition(筛选条件)是对整个SQL语句结果集进行条件过滤筛选；而left join on后面的codition(筛选条件)是仅对右侧联合表进行条件过滤筛选，将筛选出来的行联合到左侧表上。

group by:对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值作为select的返回结果。 
在说group by的时候，我们还需要了解聚合函数，聚合函数是SQL语言中一种特殊的函数。例如：
count(*)：获取数量
sum()：求和（这里要注意求和是忽略null值的，null与其他数值相加结果为null，所以可以通过ifnull(xxx,0)将null的值赋为0）
avg()：求平均数
max()：求最大值
min()：求最小值
这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

我们需要注意的是：在使用group by的SQL语句中，select中返回的字段，必须满足以下两个条件之一：
包含在group by语句的后面，作为分组的依据；
这些字段包含在聚合函数中；
从刚才的那个例子中，我们查询出每个城市，相同年龄的员工数量：
select city, count(*),age from dbo.user where departmentID=2 group by city,age

having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。 

所以having的使用需要注意以下几点：
having只能用于group by（分组统计语句中）；
where是用于在初始表中行的筛选查询，having用于在where和group by 结果分组中查询；
having子句中的每一个元素也必须出现在select列表中；
having语句可以使用聚合函数，而where不使用。
还是刚才的例子，我们进一步整理，查询员工数量大于20的城市和年龄段
select city, count(*),age from dbo.user where departmentID=2 group by city,age having age >40

回到开头的那个问题：当一个语句中同时含有where、group by 、having及聚集函数时，执行顺序如下：
执行where子句查找符合条件的数据；
使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；
最后用having 子句去掉不符合条件的组。
当加上其他sql语句时，执行顺序如下： 
S-F-W-G-H-O 组合
select –>where –> group by–> having–>order by

顺序是不能改变的

需要注意的是，
having 子句中的每一个元素也必须出现在select列表中；
having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制， having子句限制的是组(所以having必定与group by在一起)，where限制的是行；
where子句中不能使用聚集函数，而having子句中可以。

参考练习题：《SQL经典面试题》

三、SQL优化之六脉神剑

d

四、数据库及事务隔离级别

4.1 数据类型和基本操作
4.2 事务的隔离级别
事务隔离级别：顾名思义就是隔离事务的，那为啥要去隔离事务呢？这是因为数据库中的数据存在被多个事务同时操作的可能性，因此，事务隔离级别就是为了解决多个事务并发对某个数据进行操作时而可能发生的导致数据不一致的现象，可类比于多线程对某个共享量进行操作一样，为了保证一致性需要加锁以使多线程能串行化去操作共享量。
备注：MySQL数据库对事务隔离级别的支持取决于存储引擎的类型，MyISAM存储引擎为了提高性能是不支持事务的而InnoDB是支持事务的。
接下来就分别讲一下数据库事务的四种隔离级别及其各级别可能会出现的问题，数据库事务的隔离级别有4种，由低到高分别为Read uncommitted(读未提交) 、Read committed(读已提交) 、Repeatable read(可重复读) 、Serializable(串行化)：
1.Read uncommitted(读未提交)
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
举个例子说明，假设有两个并发事务A和B，同时去操作数据库里面某个数据(假设是张三的银行卡余额4万元)，
首先事务B开启，李四往张三账号里面转账5000，之后事务A开启发现张三的余额是4.5W，在之后李四发现多转了2000，于是又扣掉2000后提交了事务B，对事务B而言最终看到的余额是4.3W，而对事务A而言看到的余额却是4.5W。
分析：事务A和B最终看到的余额不一样的原因就是在于事务A读取了未提交事务B的中间数据，这就是脏读。如果事务B在操作余额的过程中(即未提交之前)不允许其他事务去操作余额则可避免此种脏读。
解决Read uncommitted(读未提交)隔离级别出现的脏读问题，需要更细的隔离级别Read committed(读已提交)。
2.Read committed(读已提交) 
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。
还是以上面例子来说明，假设有两个并发事务A和B，同时去操作数据库里面某个数据(假设是张三的银行卡余额4万元)，
首先事务A开启读取了余额4万，之后事务B开启，李四往张三账号里面转账5000，之后事务A试图去读取张三的余额(由于事务B未提交，此时不允许读，只能等待直至事务B提交)，在之后李四发现多转了2000，于是又扣掉2000后提交了事务B(对事务B而言最终看到的余额是4.3W)，此时事务A又试图去读取张三的余额，此时由于事务B已提交完成，因此，此时事务A能读取到余额为4.3W。
这种情况下，对应首先开启的事务A而言，一开始读到的余额是4W，而第二次成功读取的余额却又是4.3W，这就是不可重复读。
分析：这就是读提交，一个读事务(即事务A)首先开启后，之后若有另一个事务(即事务B)对数据进行更新（UPDATE）操作时，读操作事务A要等待这个更新操作事务B提交后才能读取数据，这样虽然可以解决脏读问题，但在这个事例中，出现了同一个事务(即首先开启的读事务A)范围内两个相同的查询却返回了不同数据，这就是不可重复读。
解决Read committed(读已提交)隔离级别出现的同一个事务不可重复读的问题，需要更细的隔离级别Repeatable read(可重复读)。
3.Repeatable read(可重复读)
重复读，顾名思义，就是在开始读取数据（事务开启）时，不再允许修改操作。
还是以上面例子来说明，假设有两个并发事务A和B，同时去操作数据库里面某个数据(假设是张三的银行卡余额4万元)，首先事务A开启读取了余额4万，之后事务B试图开启，李四往张三账号里面转账5000，此时事务B会开启失败，只能等待事务A读取完成后才能开启。
分析：可重复读可以解决读提交的不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。
但是可能还会有幻读问题，因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
4.3 Spring 事务传播行为

五、在一个千万级的数据库查寻中，如何提高查询效率？

1）数据库设计方面： 
        a. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 
        b. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

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

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

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

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

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

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

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

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

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

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

2)SQL语句方面：

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

        b. 应尽量避免在 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

        c. 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

        d. 下面的查询也将导致全表扫描： 

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

        e. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。

     然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： 

select id from t where num=@num 

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

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

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

select id from t where num/2=100 

应改为: 

select id from t where num=100*2

        g. 应尽量避免在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′

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

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

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

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

create table #t(…)

        j. 很多时候用 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)

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

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

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

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

3)java方面：重点内容

        a.尽可能的少造对象。

        b.合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的。，

        c.使用jDBC链接数据库操作数据

        d.控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；

        e.合理利用内存，有的数据要缓存

参考资料：

1.MySQL索引原理及慢查询优化