MySQL数据库

(Updated: )
,

再来学习一遍数据库, 这一次稍微学习深入一点.

数据库系统的简单回顾

我们这一次主要关注的还是关系型数据库, 也就是RDBMS. 对于数据库应用程序一般都是C/S架构的, 而他们使用的数据交换协议一般都是专有协议. 说道关系型数据库, 就一定要说到关系模型了, 也就是二维关系的表(行,列).

当然数据库中也不仅仅只有表, 还包括例如叫做的索引的组件, 索引其实就是把表中特定字段的结果抽取出来, 根据某种组织形式来把他们组织起来. 常用的比如平衡树等等.

除了索引, 还有视图这个概念, 视图其实就是虚拟的表, 其实就是一个存储下来的查询语句, 当下次遇到查询的时候可以直接使用视图进行子查询.

当然一个更重要的概念自然就是SQL本身了, SQL的全称是结构查询语言, 主要分成DDL, DML. SQL的开发商也提供了SQL的编程接口和一些内置的函数, 用户也可以自定义一些存储过程, 包括一些触发器的编写.

从数据的角度来看, 我们有一个三层模型:

  • 物理层: 磁盘
  • 逻辑层: 数据库内建组件
  • 视图层: 看到的表(用户所看到的, 可能不同, 可能不全)

MySQL和MariaDB

我们之前介绍过MariaDB, 他跟MySQL的发布路线不太一样, 但是每当MySQL发布新功能之后, MariaDB都会根据社区力量, 引入社区的代码来提供新功能. 尽管他们的一部分功能实现不一样, 基本功能上他们是兼容的. 在调用接口上也是兼容的.

MySQL采取的一种十分巧妙的方式, 即插件式存储引擎. 有些存储引擎是商用的, 即使用需要付费, 有些是开源的. 另外, MySQL是多线程单进程的模式, 其中一类线程是连接线程, 另外一类是守护线程. 这种模型使得MySQL对于多核心和大内存不是很能利用起来.

MySQL的配置文件是属于集中式的配置模式, 一个配置文件可以为mysql的各个应用提供配置信息.

关于一些安装MySQL之后的一些惯常动作, 我们再来说一次:

  1. 为所有的root用户设定密码, 当然也可以删除不需要的root用户, 需要注意的是别忘记flush权限表.
  2. 删除所有的匿名用户, 可以直接使用DROP命令搞定.
  3. 建议关闭主机名反解功能, 修改配置文件增加skip_name_resolve

在我们安装MySQL之后, 会在默认的数据库中看到一个加做mysql的数据库, 这个就是元数据数据库, 包含一些程序运行所需要的数据.

我们MySQL无论是服务端程序还是客户端在启动的时候都会携带大量的变量, 这些变量有的可以在运行的时候进行修改, 并且立即生效, 有的参数不支持, 只能通过修改配置文件的形式并且重启服务端程序来进行生效. 并且MySQL也引入了作用域的概念, 有的参数是全局的不可改变, 有的则是为每一个用户提供单独的设置, 这些变量叫做会话变量.

设置变量的值的方法很简单, 直接在命令行中SET就行了, 这样默认修改的变量是会话变量, 一旦用户会话中断再次进行访问的时候, 这些变量就又变成配置文件所约束的值了. 如果想要修改全局变量, 则需要在SET后面加上GLOBAL.

另外还有一种叫做状态变量的数据, 这一种变量通常都是记录的MySQL的运行信息, 一般也不可以进行修改.

MySQL的数据类型和SQL接口

我们现在使用的SQL语法都是在ANSI SQL中进行的标准定义, 就好像我们的C语言规范也是ANSI C一样.除了约束SQL的语法之外, ANSI SQL-03也同时规范了一些数据类型, 例如:

  • 字符类型
  • 数值型
  • 日期时间型
  • 内建类型

你可能会说为啥没有布尔型, 因为布尔其实说到底就还是特殊的数值类型, 而二进制类型其实就也是特殊的长字符串类型.(BLOB其实就是Binary Large OBject)

其中每一种大类型又分成好多根据长度划分的不同大小的数据类型. 例如我们熟悉的定长数据类型和不定长数据类型. 而数值类型又可以分成精准数值和近似数值类型. 日期时间型又可以分成日期类型,时间类型,日期时间类型,时间戳等等.

在MySQL中有两种内建的类型, 分别是ENUM和SET, 其实就是单选和多选的选项数据格式, 可以使用字符串也可以使用索引来写入值. 有关上述的数据类型的修饰符就不在这里说了.

对于MySQL, 有一个SQL模式的概念. 通过定义MySQL的SQL模式, 可以定义mysqld对约束的响应行为. 修改方式其实很简单, 可以直接修改全局变量然后对于下一次的新session产生效果, 对于已经产生的会话不会产生影响. 如果对当前的会话生效可以直接修改会话变量.这个在上面就说过了.

那么问题来了, 到底什么是SQL mode呢?

1
2
3
4
5
6
7
8
9
MariaDB [(none)]> show global variables like 'sql_mode';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.02 sec)

默认的值是空的, 如果想要修改, 常用的三种模式是:

  • TRADITIONAL
  • STRICT_TRANS_TABLES
  • STRICT_ALL_TABLES

其实支持的多达数十个之多.

例如我们来做个试验:

首先我们创建一个数据表:

1
2
3
4
5
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t1 (id int unsigned auto_increment primary key not null, name char(5) not null);
Query OK, 0 rows affected (0.05 sec)

接下来我们来填充数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [test]> insert into t1 (name) values ('yes'),('2long4char(5)');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1

MariaDB [test]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 2 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | yes   |
|  2 | 2long |
+----+-------+
2 rows in set (0.00 sec)

可以看到出现了一个警告, 我们插入的值超过了char5所规定的的5个字符长度, 但是并没有MySQL并没有报错只是显示了警告, 当我们查询值的时候也会发现, 超过长度的值也被自动剪掉了.

如果说我们修改SQL模式会变成效果呢, 来试试:

1
2
3
MariaDB [test]> insert into t1 (name) values ('2long4char(5)');
ERROR 1406 (22001): Data too long for column 'name' at row 1

看, 直接就会报错了. 其他的SQL模式在MySQL的官方网站上都会有说明.

关于SQL接口, 因为是有过基础的. 所以就跳过这一部分, 但是还是简单的说一下, 我们的SQL主要分成两种, 一种叫做DDL, 即数据定义语言, 另外一种叫做DML. 即数据修改语言. 其中DDL主要负责各种数据库组件的建立删除修改, 而DML则负责进行数据的插入修改查找删除. 关于在数据库中一个频繁出现的概念 - 二维关系表, 我们在设计的时候需要遵循一定的规范, 也就是范式. 当然具体是根据业务模型来进行设计的, 范式不是绝对的.

我们来说说索引, 索引是建立在经常用于查询条件的字段上. MySQL的默认索引是B树/B+树, 由于索引的实现实际上是由存储引擎进行的, 所以不同的存储引擎实现的索引类型是不一样的.

根据索引项对应的记录是不是所有的记录项, 可以分成稠密索引和稀疏索引, 根据实现类型(数据组织结构), 我们可以分成B+索引, hash索引, R树索引, FULLTEXT索引, 根据索引和数据是否存储在一个位置, 可以分成聚集索引和非聚集索引(通过指针), 还有简单索引(单键)和组合索引.

我们来看一下刚刚创建的表的信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MariaDB [test]> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: 3
    Create_time: 2018-10-09 13:25:19
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

其中显示了当前分配的空间大小, 自增的id, 创建时间, 最后一次的更新时间, 检查时间等等.

MySQL的架构, 索引和视图

mysql_arch

我们先来看一下这个MySQL的架构图, 我们说这是一个C/S架构的应用程序, 所以最上面自然就有接受客户端连接的连接器, 这也是使用的MySQL的驱动. 当我们通过这些连接器连接进入MySQL之后, 会获得一个连接会话. 这个连接会话其实也是可以分成长连接或者短连接的. 我们说过, MySQL是单进程多线程的, 所以就需要分发连接线程给用户, 这就有一个Connection Pool也就是连接池. 用户通过这个连接会话发送SQL到MySQL的SQL接口, 于是这个接口就是一个完备的SQL解释器, 这里分析的是用户发送的SQL的语法正确与否, 如果没有问题就会进入下一个分析器, 这个分析器不负责检查语法方面的问题, 他所作的是关于查询的内容方面的分析, 例如, 查询的用户是否有查询数据的权限以及查询的翻译等等. 分析之后生成的查询路径(查询路由)被丢到下面的一层, 也就是分析那一条路是最近的, 开销最低, 通过评估进行优化.甚至于, 这个优化器会进行SQL的重写.接着就交给了存储引擎, 由存储引擎. 然后这些存储引擎寻找到目标磁盘块, 将他们加载到内存中. 这些存储引擎是没有写入权限的, 所以他需要依托于内核, 通过内存空间来进行改写.

通过上面的架构图, 我们大概可以获得MySQL的数据文件类型:

  • 数据文件, 索引文件
  • 日志: 重做日志, 撤销日志, 二进制日志, 错误日志, 查询日志, 慢查询日志

关于MySQL的索引, 我们之前稍微的整理了一下几种索引类型, 其实在真实的查找过程中, 我们的索引也是分等级的, 因为直接扫描全部的索引也是一个消耗资源的耗时操作, 所以就再添加一个稀疏的索引用来检索索引, 从而在查询结果的时候先快速的拿到对应的索引的位置, 接着通过索引所指向的硬盘块读取进内存从而拿到结果.

接下来, 我们说一个MySQL中的索引概念, 叫做 – 左前缀索引.

这是个什么玩意呢, 我们知道, 一个varchar类型的字符串数据最长可以达到65535个字符.这就导致如果将所有的字符串继续匹配的话, 会很麻烦. 因此MySQL只选取这字符串的前半段(例如: 只取前50个), 然后将他们组织成索引. 但是这样就造成了一个结果, 那就是我们进行查询的尽量不要做形如这样的查询: LIKE %abc%. 因为左前缀索引是不能找到满足这样条件的字符串的, 要查询的是一个中间数据, 但是只组织了左半数据的索引是做不到的. 所以, 在进行查询的时候, 尽量不要在左边加上%.

接下来我们读取一个SQL脚本来做个测试, 这个脚本可以在这里得到.

我们可以先用学生表来做个试验, 首先看下这张表的相关信息和他的索引:

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
MariaDB [hellodb]> show indexes from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

MariaDB [hellodb]> select * from students where StuID=3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     3 | Xie Yanke |  53 | M      |       2 |        16 |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.01 sec)

我们可以看到,当前表设置的主键是StuID, 然后我们通过这个主键查找可以找到对应的记录. 事实上, 对于MySQL而言, 有个命令EXPLAIN可以来显示这个命令的执行过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [hellodb]> explain select * from students where StuID=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

可以看到, 显示的结果是, 使用我们的主键就找到了, 仅仅使用了1行. 这就证明这个是从索引中抓取的. 接下来我们试试如果使用别的字段作为筛选条件的话会是什么样子的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [hellodb]> explain select * from students where Name='Xie Yanke'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.00 sec)

这一次就没有可以参考的索引了, 所以就只能扫描全表来获取结果了. 可以看出来扫描了25行然后使用where条件才获取到.

现在我们再来验证一下上面说的MySQL的左前缀索引的效果, 我们先给Name字段加上一个索引, 接着再通过模糊匹配来试试:

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
MariaDB [hellodb]> create index name_index on students(Name);
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> show indexes from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: name_index
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

创建完成了, 这样创建的默认的索引类型都是B树索引. 接着我们来试试查询:

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
MariaDB [hellodb]> select * from students where Name like "Xi%";
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     7 | Xi Ren    |  19 | F      |       3 |      NULL |
|    22 | Xiao Qiao |  20 | F      |       1 |      NULL |
|     3 | Xie Yanke |  53 | M      |       2 |        16 |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where Name like "Xi%"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: name_index
          key: name_index
      key_len: 152
          ref: NULL
         rows: 3
        Extra: Using index condition
1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where Name like "%Xi%"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
        Extra: Using where
1 row in set (0.00 sec)

当我们没有在左边加上通配符的时候. 查询的使用还是通过索引来进行的, 然而如果加上了, 就会扫描全表.

接着我们再来看一下视图, 视图是关系型数据库中的一个概念, 但是在MySQL中对于视图的支持没那么好, 视图其实就是一个虚表, 他的数据仍然来源于基表(就是创建视图的那个表), 所以说如果想要在视图上插入数据, 是需要参照基表的约束的, 但是几乎没有人会去这么做, 创建一个视图很简单:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [hellodb]> create view test as select StuID, Name, Age from students;
Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| test              |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

然后我们就可以看到这个视图了, 那么怎么区分视图和表的呢?

我们可以通过查看表的状态来看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MariaDB [hellodb]> show table status like "test"\G
*************************** 1. row ***************************
           Name: test
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
1 row in set (0.00 sec)

一个视图不会具备表的信息, 你只会在注释里面看到有写.

可以正常的在视图中继续查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [hellodb]> select * from test where StuID=22;
+-------+-----------+-----+
| StuID | Name      | Age |
+-------+-----------+-----+
|    22 | Xiao Qiao |  20 |
+-------+-----------+-----+
1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from test where StuID=22\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

但是实际上, 我们的结果还是从students表中获取的.

MySQL的查询操作

了解MySQL的查询操作就是了解他的查询执行路径, 我们先来看一下:
MySQL查询过程

最左上就是我们的客户端了, 通过C/S协议来访问到我们的MySQL服务器端. 通过连接线程在缓冲池中寻找, 如果能够命中Cache, 直接结束查询过程, 相当于是剩下了后面的所有资源消耗, 可想而知这是多么大的收益!

不过, 查询缓存也只是在一些特定条件下才会有用, 例如一个最典型的例子:

1
2
3
4
5
6
7
8
MariaDB [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-10-16 22:29:34 |
+---------------------+
1 row in set (0.00 sec)

这样的值是没有缓存的必要的, 因为每次查询都不一样.

有关这个查询的后半部分, 我们在之前的MySQL的架构中说了, 这里就不在赘述了. 但是我们依然可以总结出查询的路径:

查询缓存 -> 解析器 -> 预处理器 -> 优化器 -> 查询执行引擎 -> 存储引擎

说到查询, 不得不提到的就是SELECT查询语句了, 一个完整的SELECT语句执行下来, 是从FROM先开始的, FROM说明选择查询的表, 接着where表明需要满足那些条件, 分析完where之后就会进行分组GROUP BY, 接着通过HAVING进行分组的聚合, 接着还可以通过ORDERBY来进行排序, 接着就根据select指明的列进行投影, 之后再根据LIMIT所指明的范围对结果进行移除.

在MySQL的交互式SHELL命令行中, 我们可以看到完整的SELECT查询语句:

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
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

我们可以添加前面一些查询选项, 来实现一些功能, 例如: DISTINCT就是在做数据去重, 重复的数据会被舍弃. 还有SQL_CACHE和SQL_NO_CACHE分别表示显示指定存储查询结果是否丢到缓存中. 在我们的MySQL的服务器变量叫做query_cache_type. 只有当这个值为ON的时候, 查询缓存才是打开的. 看一下相关的变量:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [hellodb]> show global variables like "query%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
8 rows in set (0.00 sec)

并且, SELECT的结果缓存符合条件的时候就会进行缓存. 另外, 还可以使用DEMAND来要求MySQL进行显式缓存, 也就是说, 需要用户手动的显式指定需要缓存的数据, 但是如果数据的数据类型不符合缓存的数据类型, 就依然不能进行. 当然了, 在我们进行查询的时候加上SQL_NO_CACHE的话就都不会进行缓存了.