Fork me on GitHub

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

发表于 | 分类于 | | 阅读次数:
字数统计: 2,626 | 阅读时长 ≈ 13

前言

最近在使用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
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行，确实可以提高效率，但有数据冲突的错误情况，一般也不会使用。

结语

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




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

SakuraTears wechat
扫一扫关注我的公众号
您的支持就是我创作的动力！
0%