MySql数据库知识点总结(1)

前言

最近在使用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类型,其他类型不推荐使用此方法,比如charint相加,会得到意想不到的结果从而产生问题。

  • 使用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
2
3
4
5
6
7
<insert id="insertBatch" parameterType="java.util.List">
insert into student (id, stu_no, stu_name, stu_sex, stu_birthday, stu_class)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id,jdbcType=INTEGER}, #{item.stuNo,jdbcType=VARCHAR}, #{item.stuName,jdbcType=VARCHAR}, #{item.stuSex,jdbcType=CHAR}, #{item.stuBirthday,jdbcType=DATE}, #{item.stuClass,jdbcType=CHAR})
</foreach>
</insert>

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

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

Thread.sleep(2000);

long start2 =System.currentTimeMillis();
List<StudentModel> list3 = new ArrayList<>();
for(int i=1000;i<2000;i++){
StudentModel studentModel = new StudentModel();
studentModel.setId(i);
studentModel.setStuName(i+"");
studentModel.setStuClass("1");
studentModel.setStuNo(i+"");
studentModel.setStuBirthday(new Date());
studentModel.setStuSex("M");
list3.add(studentModel);
}
studentMapper.insertBatch(list3);
long end2 =System.currentTimeMillis();
System.out.println(end2-start2);
}

运行结果:

1
2
3
4
5
6
7
2019-02-25 14:46:14.851  INFO 9360 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-02-25 14:46:15.031 INFO 9360 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
120601
260
2019-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 hierarchy
2019-02-25 14:48:17.671 INFO 9360 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
@Test
public void test4() throws Exception {
Thread.sleep(2000);

long start1 = System.currentTimeMillis();

List<ScoreModel> list1 = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
ScoreModel scoreModel = new ScoreModel();
scoreModel.setId(i);
scoreModel.setScore(new BigDecimal(100));
scoreModel.setCouNo(i + "");
scoreModel.setStuNo(i + "");
list1.add(scoreModel);
}
scoreMapper.insertBatch(list1);

List<TeacherModel> list2 = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
TeacherModel teacherModel = new TeacherModel();
teacherModel.setId(i);
teacherModel.setTeachNo(i + "");
teacherModel.setTeachName(i + "");
teacherModel.setTeachBirthday(new Date());
teacherModel.setTeachDepart(i + "");
teacherModel.setTeachSex("M");
teacherModel.setTeachProf(i + "");
list2.add(teacherModel);
}
teacherMapper.insertBatch(list2);

List<StudentModel> list3 = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
StudentModel studentModel = new StudentModel();
studentModel.setId(i);
studentModel.setStuName(i + "");
studentModel.setStuClass("1");
studentModel.setStuNo(i + "");
studentModel.setStuBirthday(new Date());
studentModel.setStuSex("M");
list3.add(studentModel);
}
studentMapper.insertBatch(list3);

List<CourseModel> list4 = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
CourseModel courseModel = new CourseModel();
courseModel.setId(i);
courseModel.setCouName(i + "");
courseModel.setCouNo(i + "");
courseModel.setTeachNo(i + "");
list4.add(courseModel);
}
courseMapper.insertBatch(list4);

long end1 = System.currentTimeMillis();
System.out.println(end1 - start1);
}

运行结果:

1
2
3
4
5
6
2019-02-25 15:01:23.688  INFO 9576 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-02-25 15:01:23.850 INFO 9576 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
4348
2019-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 hierarchy
2019-02-25 15:01:27.691 INFO 9576 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-02-25 15:01:27.700 INFO 9576 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
@Test
public void test2() throws Exception{
Thread.sleep(2000);
long start1 =System.currentTimeMillis();

ExecutorService executorService = Executors.newFixedThreadPool(4);

Future<Boolean> future1= executorService.submit(()->{
List<ScoreModel> list1 = new ArrayList<>();
for(int i=0;i<10000;i++){
ScoreModel scoreModel =new ScoreModel();
scoreModel.setId(i);
scoreModel.setScore(new BigDecimal(100));
scoreModel.setCouNo(i+"");
scoreModel.setStuNo(i+"");
list1.add(scoreModel);
}
scoreMapper.insertBatch(list1);
return true;
});
Future<Boolean> future2= executorService.submit(()->{
List<TeacherModel> list2 = new ArrayList<>();
for(int i=0;i<10000;i++){
TeacherModel teacherModel =new TeacherModel();
teacherModel.setId(i);
teacherModel.setTeachNo(i+"");
teacherModel.setTeachName(i+"");
teacherModel.setTeachBirthday(new Date());
teacherModel.setTeachDepart(i+"");
teacherModel.setTeachSex("M");
teacherModel.setTeachProf(i+"");
list2.add(teacherModel);
}
teacherMapper.insertBatch(list2);
return true;
});
Future<Boolean> future3= executorService.submit(()->{
List<StudentModel> list3 = new ArrayList<>();
for(int i=0;i<10000;i++){
StudentModel studentModel = new StudentModel();
studentModel.setId(i);
studentModel.setStuName(i+"");
studentModel.setStuClass("1");
studentModel.setStuNo(i+"");
studentModel.setStuBirthday(new Date());
studentModel.setStuSex("M");
list3.add(studentModel);
}
studentMapper.insertBatch(list3);
return true;
});
Future<Boolean> future4= executorService.submit(()->{
List<CourseModel> list4 = new ArrayList<>();
for(int i=0;i<10000;i++){
CourseModel courseModel = new CourseModel();
courseModel.setId(i);
courseModel.setCouName(i+"");
courseModel.setCouNo(i+"");
courseModel.setTeachNo(i+"");
list4.add(courseModel);
}
courseMapper.insertBatch(list4);
return true;
});

future1.get();
future2.get();
future3.get();
future4.get();

long end1 = System.currentTimeMillis();
System.out.println(end1-start1);
}

运行结果:

1
2
3
4
5
6
2019-02-25 15:04:46.623  INFO 8284 --- [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-02-25 15:04:47.084 INFO 8284 --- [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
1993
2019-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 hierarchy
2019-02-25 15:04:47.990 INFO 8284 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2019-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行,确实可以提高效率,但有数据冲突的错误情况,一般也不会使用。

结语

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




-------------文章结束啦 ~\(≧▽≦)/~ 感谢您的阅读-------------

您的支持就是我创作的动力!

欢迎关注我的其它发布渠道