前言
最近在使用MySql数据库过程中,遇到了一些问题,并抽时间总结了下来,也是结合Java语言和MyBatis的方方面面,在此分享给大家。
正文
中文姓名排序问题
比如一张有中文姓名的表,我们要对中文姓名按拼音进行排序,在没有姓名拼音字段(插入姓名时顺带插入了姓名拼音)的帮助下,可以使用如下语法。
如果MySql的排序字段采用的GBK字符集,那可以直接使用
order by ‘字段名’ asc
语法。如果MySql的排序字段采用的不是GBK字符集,那么可以使用如下方法。
1
select * from '表名称' order by convert('字段名' using gbk) asc;
使用该语法,姓名字段名无法使用索引(如果有的话),需要注意。
多个字段去重并统计数量
这个问题还是比较常见的,比如有一张用户表,有用户姓名 user_name
用户身份证号 id_card_no
等字段,现在我们要把姓名和身份证号一致的认为一个用户,其它情况均为两个用户,则可以使用以下语法。
可以使用DISTINCT,虽然DISTINCT只能去重一行,但是可以使用字段合并功能来进行处理。
1
select count(DISTINCT(c.user_name + IFNULL(c.id_card_no,''))) from user c where c.invite_id = 'xxxxx';
注意: 两列的类型最好为
varchar
类型,其他类型不推荐使用此方法,比如char
和int
相加,会得到意想不到的结果从而产生问题。使用GROUP BY语句,需要有子查询。
1
select count(*) from (select * from user u where u.invite_id = 'xxxxx' GROUP BY u.user_name,IFNULL(u.id_card_no,'')) a ;
注意:子查询得到的表必须有别名,不然SQL语句报错。
查询条件字段有多个条件的查询
这个问题是这样,比如我们有一群用户在user表,他们有个字段表示所属平台platform,比如有A、B、C、D等几种平台,现在要查询所属平台为A或B平台的用户。
我们可以明显看到用
in
即可以解决。在MyBatis里,对于这个字段,则需要传入一个List
形式的数组,并在xml文件里通过循环赋值给SQL语句进行查询,也是比较常用的方法。1
2
3
4
5
6<select id="findUser" parameterType="com.xxx.NewUser">
select * from user u where u.invite_id= #{condition.inviteID} and u.platform in
<foreach collection="list" item="condition.platform" index="index" open="(" close=")" separator=",">
#{condition.platform}
</foreach>
</select>上面的NewUser对象里的platform字段是个List
,最后组成的SQL语句如下。 1
select * from user u where u.invite_id= 'xxxxxx' and u.platform in ('A','B');
我们还可以利用
FIND_IN_SET
函数,同时在MyBatis里传入的参数直接为String
即可,但是A、B平台需要用逗号隔开。1
2
3<select id="findUser" parameterType="com.xxx.User">
select * from user u where u.invite_id= #{condition.inviteID} and FIND_IN_SET(u.platform,#{condition.platform})
</select>上面的User对象里的platform字段是个String,平台之间用逗号分割,最后SQL如下。
1
select * from user u where u.invite_id= 'xxxxxx' and FIND_IN_SET(u.platform,'A,B');
这儿需要注意的一点是,FIND_IN_SET无法使用u.platform字段的索引。
虽然无法使用索引,但如果SQL语句本身其它条件索引检索后数据量不大,或者被FIND_IN_SET的字段没有索引,也是可以使用的。
如果传入Mybatis里的实体类字段本身就是逗号分割的,我们在拆成一个一个数据最后再使用in也是非常费事的,可以使用FIND_IN_SET轻松搞定。
使用程序对MySql批量处理数据
我们知道,对于MySql批量处理数据,我们可以使用MyBatis的批量处理方法。
大致如下:
1 | <insert id="insertBatch" parameterType="java.util.List"> |
这种方法比普通的程序循环一条条插入要快很多,下面是一个例子。
1 |
|
运行结果:
1 | 2019-02-25 14:46:14.851 INFO 9360 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... |
可以看到批处理要比单条处理快了N倍,这儿不再过多介绍。
Tips:如果数据量过大,如100w,1000w等,可以将数据分成多份循环去批处理插入。
关于批量的处理的另外一点思考:
当我们需要对多张表进行批量处理时,如果想提高运行效率,可以考虑使用多线程处理,比如下面的例子。
我们对4张表插入1w数据,正常的处理逻辑。
1 |
|
运行结果:
1 | 2019-02-25 15:01:23.688 INFO 9576 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... |
我们用线程池对四张表分别进行批量操作。
1 |
|
运行结果:
1 | 2019-02-25 15:04:46.623 INFO 8284 --- [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... |
可以看到速度提高了1倍,当数据量更大时,提高效果更明显。
注意
对于上面多线程多表批量插入,如果需要保证事务,单独的每张表的事务是可以保证的(将每张表的批量插入提出来,形成一个方法,并加上事务属性,如果有错误就会回滚),但是如果要同时保证4张表的事务(这几张表要么全成功,要么全不成功),是无法满足的,由于多线程的特殊性。所以这种情况下请使用第一种同步方法,并加上事务,才能保证4张表批处理要么全成功,要么全不成功。
对于一张表的批处理,如果数据量过大时,可以使用多线程同时插入这一张表吗?
答案是否定的,对于MySQL InnoDB数据库,默认是行锁,前提条件是建立在索引之上的。如果筛选条件没有建立索引,会降级到表锁。即如果where条件中的字段都加了索引,则加的是行锁;否则加的是表锁。
当为表锁时,瓶颈在数据库,多线程是无法提高对同一张表的插入效率的;
当为行锁时,看起来可以insert A行时同时insert B行,确实可以提高效率,但有数据冲突的错误情况,一般也不会使用。
结语
我还会在工作学习过程中不断总结,此文章类型也会不断更新,今天就先到这里吧。