最近在使用MySql数据库过程中,遇到了一些问题,并抽时间总结了下来,也是结合Java语言和MyBatis的方方面面,在此分享给大家。

比如一张有中文姓名的表,我们要对中文姓名按拼音进行排序,在没有姓名拼音字段(插入姓名时顺带插入了姓名拼音)的帮助下,可以使用如下语法。

如果MySql的排序字段采用的GBK字符集,那可以直接使用 order by '字段名' asc 语法。

如果MySql的排序字段采用的不是GBK字符集,那么可以使用如下方法。

1select * from '表名称' order by convert('字段名' using gbk) asc;

使用该语法,姓名字段名无法使用索引(如果有的话),需要注意。

这个问题还是比较常见的,比如有一张用户表,有用户姓名 user_name 用户身份证号 id_card_no 等字段,现在我们要把姓名和身份证号一致的认为一个用户,其它情况均为两个用户,则可以使用以下语法。

可以使用DISTINCT,虽然DISTINCT只能去重一行,但是可以使用字段合并功能来进行处理。

1select count(DISTINCT(c.user_name + IFNULL(c.id_card_no,''))) from user c where c.invite_id = 'xxxxx';

注意: 两列的类型最好为varchar类型,其他类型不推荐使用此方法,比如char和int相加,会得到意想不到的结果从而产生问题。

使用GROUP BY语句,需要有子查询。

1select 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<String>形式的数组,并在xml文件里通过循环赋值给SQL语句进行查询,也是比较常用的方法。

1<select id="findUser" parameterType="com.xxx.NewUser">2  select * from user u where  u.invite_id= #{condition.inviteID} and u.platform in 3    <foreach collection="list" item="condition.platform" index="index" open="(" close=")" separator=",">4        #{condition.platform}5    </foreach>6</select>

上面的NewUser对象里的platform字段是个List,最后组成的SQL语句如下。

1select * from user u where  u.invite_id= 'xxxxxx' and u.platform in ('A','B');

我们还可以利用FIND_IN_SET函数,同时在MyBatis里传入的参数直接为String即可,但是A、B平台需要用逗号隔开。

1<select id="findUser" parameterType="com.xxx.User">2  select * from user u where  u.invite_id= #{condition.inviteID} and FIND_IN_SET(u.platform,#{condition.platform})3</select>

上面的User对象里的platform字段是个String,平台之间用逗号分割,最后SQL如下。

1select * 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批量处理数据,我们可以使用MyBatis的批量处理方法。

大致如下:

1  <insert id="insertBatch" parameterType="java.util.List">2    insert into student (id, stu_no, stu_name, stu_sex, stu_birthday, stu_class)3    values4    <foreach collection="list" item="item" index="index" separator=",">5      (#{item.id,jdbcType=INTEGER}, #{item.stuNo,jdbcType=VARCHAR}, #{item.stuName,jdbcType=VARCHAR}, #{item.stuSex,jdbcType=CHAR}, #{item.stuBirthday,jdbcType=DATE}, #{item.stuClass,jdbcType=CHAR})6    </foreach>7  </insert>

这种方法比普通的程序循环一条条插入要快很多,下面是一个例子。

1    @Test 2    public void test3() throws Exception{ 3        Thread.sleep(2000); 4        long start1 =System.currentTimeMillis(); 5        for(int i=0;i<1000;i++){ 6            StudentModel studentModel = new StudentModel(); 7            studentModel.setId(i); 8            studentModel.setStuName(i+""); 9            studentModel.setStuClass("1");10            studentModel.setStuNo(i+"");11            studentModel.setStuBirthday(new Date());12            studentModel.setStuSex("M");13            studentMapper.insert(studentModel);14        }15        long end1 =System.currentTimeMillis();16        System.out.println(end1-start1);1718        Thread.sleep(2000);1920        long start2 =System.currentTimeMillis();21        List<StudentModel> list3 = new ArrayList<>();22        for(int i=1000;i<2000;i++){23            StudentModel studentModel = new StudentModel();24            studentModel.setId(i);25            studentModel.setStuName(i+"");26            studentModel.setStuClass("1");27            studentModel.setStuNo(i+"");28            studentModel.setStuBirthday(new Date());29            studentModel.setStuSex("M");30            list3.add(studentModel);31        }32        studentMapper.insertBatch(list3);33        long end2 =System.currentTimeMillis();34        System.out.println(end2-start2);35    }

运行结果:

12019-02-25 14:46:14.851  INFO 9360 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...22019-02-25 14:46:15.031  INFO 9360 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.26052019-02-25 14:48:17.666  INFO 9360 --- [       Thread-2] o.s.w.c.s.GenericWebApplicationContext   : Closing org.springframework.web.context.support.GenericWebApplicationContext@2ddc9a9f: startup date [Mon Feb 25 14:46:09 CST 2019]; root of context hierarchy62019-02-25 14:48:17.671  INFO 9360 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...72019-02-25 14:48:17.675  INFO 9360 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

可以看到批处理要比单条处理快了N倍,这儿不再过多介绍。

Tips:如果数据量过大,如100w,1000w等,可以将数据分成多份循环去批处理插入。

关于批量的处理的另外一点思考:

当我们需要对多张表进行批量处理时,如果想提高运行效率,可以考虑使用多线程处理,比如下面的例子。

我们对4张表插入1w数据,正常的处理逻辑。

1    @Test 2    public void test4() throws Exception { 3        Thread.sleep(2000); 4 5        long start1 = System.currentTimeMillis(); 6 7        List<ScoreModel> list1 = new ArrayList<>(); 8        for (int i = 0; i < 10000; i++) { 9            ScoreModel scoreModel = new ScoreModel();10            scoreModel.setId(i);11            scoreModel.setScore(new BigDecimal(100));12            scoreModel.setCouNo(i + "");13            scoreModel.setStuNo(i + "");14            list1.add(scoreModel);15        }16        scoreMapper.insertBatch(list1);1718        List<TeacherModel> list2 = new ArrayList<>();19        for (int i = 0; i < 10000; i++) {20            TeacherModel teacherModel = new TeacherModel();21            teacherModel.setId(i);22            teacherModel.setTeachNo(i + "");23            teacherModel.setTeachName(i + "");24            teacherModel.setTeachBirthday(new Date());25            teacherModel.setTeachDepart(i + "");26            teacherModel.setTeachSex("M");27            teacherModel.setTeachProf(i + "");28            list2.add(teacherModel);29        }30        teacherMapper.insertBatch(list2);3132        List<StudentModel> list3 = new ArrayList<>();33        for (int i = 0; i < 10000; i++) {34            StudentModel studentModel = new StudentModel();35            studentModel.setId(i);36            studentModel.setStuName(i + "");37            studentModel.setStuClass("1");38            studentModel.setStuNo(i + "");39            studentModel.setStuBirthday(new Date());40            studentModel.setStuSex("M");41            list3.add(studentModel);42        }43        studentMapper.insertBatch(list3);4445        List<CourseModel> list4 = new ArrayList<>();46        for (int i = 0; i < 10000; i++) {47            CourseModel courseModel = new CourseModel();48            courseModel.setId(i);49            courseModel.setCouName(i + "");50            courseModel.setCouNo(i + "");51            courseModel.setTeachNo(i + "");52            list4.add(courseModel);53        }54        courseMapper.insertBatch(list4);5556        long end1 = System.currentTimeMillis();57        System.out.println(end1 - start1);58    }

运行结果:

12019-02-25 15:01:23.688  INFO 9576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...22019-02-25 15:01:23.850  INFO 9576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.19-02-25 15:01:27.689  INFO 9576 --- [       Thread-2] o.s.w.c.s.GenericWebApplicationContext   : Closing org.springframework.web.context.support.GenericWebApplicationContext@298a5e20: startup date [Mon Feb 25 15:01:18 CST 2019]; root of context hierarchy52019-02-25 15:01:27.691  INFO 9576 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...62019-02-25 15:01:27.700  INFO 9576 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

我们用线程池对四张表分别进行批量操作。

1    @Test 2    public void test2() throws Exception{ 3        Thread.sleep(2000); 4        long start1 =System.currentTimeMillis(); 5 6        ExecutorService executorService = Executors.newFixedThreadPool(4); 7 8        Future<Boolean> future1= executorService.submit(()->{ 9            List<ScoreModel> list1 = new ArrayList<>();10            for(int i=0;i<10000;i++){11                ScoreModel scoreModel =new ScoreModel();12                scoreModel.setId(i);13                scoreModel.setScore(new BigDecimal(100));14                scoreModel.setCouNo(i+"");15                scoreModel.setStuNo(i+"");16                list1.add(scoreModel);17            }18            scoreMapper.insertBatch(list1);19            return true;20        });21        Future<Boolean> future2= executorService.submit(()->{22            List<TeacherModel> list2 = new ArrayList<>();23            for(int i=0;i<10000;i++){24                TeacherModel teacherModel =new TeacherModel();25                teacherModel.setId(i);26                teacherModel.setTeachNo(i+"");27                teacherModel.setTeachName(i+"");28                teacherModel.setTeachBirthday(new Date());29                teacherModel.setTeachDepart(i+"");30                teacherModel.setTeachSex("M");31                teacherModel.setTeachProf(i+"");32                list2.add(teacherModel);33            }34            teacherMapper.insertBatch(list2);35            return true;36        });37        Future<Boolean> future3= executorService.submit(()->{38            List<StudentModel> list3 = new ArrayList<>();39            for(int i=0;i<10000;i++){40                StudentModel studentModel = new StudentModel();41                studentModel.setId(i);42                studentModel.setStuName(i+"");43                studentModel.setStuClass("1");44                studentModel.setStuNo(i+"");45                studentModel.setStuBirthday(new Date());46                studentModel.setStuSex("M");47                list3.add(studentModel);48            }49            studentMapper.insertBatch(list3);50            return true;51        });52        Future<Boolean> future4= executorService.submit(()->{53            List<CourseModel> list4 = new ArrayList<>();54            for(int i=0;i<10000;i++){55                CourseModel courseModel = new CourseModel();56                courseModel.setId(i);57                courseModel.setCouName(i+"");58                courseModel.setCouNo(i+"");59                courseModel.setTeachNo(i+"");60                list4.add(courseModel);61            }62            courseMapper.insertBatch(list4);63            return true;64        });6566        future1.get();67        future2.get();68        future3.get();69        future4.get();7071        long end1 = System.currentTimeMillis();72        System.out.println(end1-start1);73    }

运行结果:

12019-02-25 15:04:46.623  INFO 8284 --- [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...22019-02-25 15:04:47.084  INFO 8284 --- [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.19-02-25 15:04:47.986  INFO 8284 --- [       Thread-2] o.s.w.c.s.GenericWebApplicationContext   : Closing org.springframework.web.context.support.GenericWebApplicationContext@298a5e20: startup date [Mon Feb 25 15:04:40 CST 2019]; root of context hierarchy52019-02-25 15:04:47.990  INFO 8284 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...62019-02-25 15:04:48.004  INFO 8284 --- [       Thread-2] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

可以看到速度提高了1倍,当数据量更大时,提高效果更明显。

对于上面多线程多表批量插入,如果需要保证事务,单独的每张表的事务是可以保证的(将每张表的批量插入提出来,形成一个方法,并加上事务属性,如果有错误就会回滚),但是如果要同时保证4张表的事务(这几张表要么全成功,要么全不成功),是无法满足的,由于多线程的特殊性。所以这种情况下请使用第一种同步方法,并加上事务,才能保证4张表批处理要么全成功,要么全不成功。

对于一张表的批处理,如果数据量过大时,可以使用多线程同时插入这一张表吗?

答案是否定的,对于MySQL InnoDB数据库,默认是行锁,前提条件是建立在索引之上的。如果筛选条件没有建立索引,会降级到表锁。即如果where条件中的字段都加了索引,则加的是行锁;否则加的是表锁。

当为表锁时,瓶颈在数据库,多线程是无法提高对同一张表的插入效率的;

当为行锁时,看起来可以insert A行时同时insert B行,确实可以提高效率,但有数据冲突的错误情况,一般也不会使用。

我还会在工作学习过程中不断总结,此文章类型也会不断更新,今天就先到这里吧。