侧边栏壁纸
  • 累计撰写 244 篇文章
  • 累计创建 16 个标签
  • 累计收到 0 条评论
隐藏侧边栏

当我们创建数据库和数据表时底层在做什么

kaixindeken
2021-04-26 / 0 评论 / 0 点赞 / 79 阅读 / 6,638 字

实际上,Server 层所做的主要是针对 SQL 语句的解析和分析工作,然后将执行计划交由存储引擎真正去执行。

创建数据库

为了方便演示,我们在创建一个名为 demo 的数据库:

mysql> CREATE DATABASE demo
    -> DEFAULT CHARACTER SET utf8mb4
    -> DEFAULT COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

创建完成后,就可以通过 show databases; 命令看到这个数据库了:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

其中 information_schema、mysql、performance_schema、sys 是 MySQL 自带的数据库,分别提供了如下功能:

  • information_schema 保存着 MySQL 服务端维护的所有其他数据库信息,比如表、视图、列、索引等元数据;
  • mysql 保存着 MySQL 服务端的用户和权限信息,以及一些存储过程、日志信息;
  • performance_schema 保存着 MySQL 服务端运行过程中的一些状态信息,用于提供性能监控和问题排查;
  • sys 通过视图(数据库视图,不是那种可视化视图)的形式把 information_schema 和 performance_schema 结合起来,方便了解 MySQL 服务器的一些性能信息。

除此之前的其他数据库是我自己创建的数据库,其中包括刚刚新建的 demo。

字符集和排序规则

关于创建数据库的基本 SQL 语法这里就不介绍了,我们重点来看后面的字符集和排序规则。

字符集表示存储到数据库的字符以何种格式编码(所谓编码指的是字符映射到二进制数据的过程,解码则是对应的逆操作)以及支持的编码字符范围,常见的字符集有 ASCII、ISO 8859-1(在 ASCII 字符集上进行扩展,对应 MySQL 中的 latin1)、GBK、UTF8 等,在 MySQL 中可以通过 show charset 命令查看系统支持的所有字符集:

1.jpeg

而排序规则(或者叫做比较规则)表示存储到数据库的字符以何种方式比较大小进行排序,如果没有指定排序规则,每个字符集有着默认的排序规则,见上图 Default collation 字段,雌此外,你也可以通过 show collation 命令查看 MySQL 支持的所有排序规则:

1.jpeg

你可以通过 show collation like 'utf8%' 筛选你想要看到的排序规则:

1.jpeg

排序规则都有固定的格式:

字符集_语言_后缀
  • 字符集表示字符对应的编码字符集,对应前面指定的 charset 设置,即某个字符集需要使用与之对应的排序规则;
  • 语言表示使用的自然语言,比如 spanish、polish 等,general 则是通用的;
  • 后缀一般是 ci,表示排序比较的时候字符不区分大小写,如果是 cs 则表示区分大小写,上图中还可以看到 bin 后缀,表示以二进制格式比较,通常我们都使用 ci。

通过上面的分析,你应该就知道为什么我们设置的字符编码和排序规则通常都是 utf8/utf8_general_ci、utf8mb4/utf8mb4_general_ci 这样的组合了吧,当然,你可以选择只设置 charset,对应的默认排序规则就是 {设置的字符集}_general_ci。

这里我们指定的字符集是 utf8mb4,它是 utf8 字符集的超集,新增对 emoji 表情等字符集的支持(换句话说,使用 utf8 字符集 emoji 表情存储到数据库会乱码),并且显示设置了排序规则,如果不指定的话,效果也是一样的:

mysql> CREATE DATABASE demo2 DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

然后我们可以像下面这样查看指定数据库的字符集和排序规则:

mysql> use demo2;
Database changed
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.01 sec)

mysql> show variables like 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_general_ci |
+--------------------+--------------------+
1 row in set (0.00 sec)

我们在数据库创建时指定的字符集和排序规则默认会应用到该数据库的所有数据表和数据列,后面建表和新增数据列时就不需要额外指定了。

注:MySQL 也支持单独设置指定表和指定列的字符集和排序规则,但是不建议这么做,因为不同字符集设置可能会导致连表查询时的字符集转换,进而导致索引失效,出现慢查询。

之所以大费周章介绍这么多关于字符集和排序规则的东西,是因为在新增记录到数据表时,如果某个数据列上设置了索引,会按照这里的排序规则对列进行排序,生成对应的 B+ 索引树。

数据库所在的数据目录

我们知道,MySQL 是会将数据库持久化到磁盘中的,对应的目录叫做数据目录,那么这个数据目录的路径在哪里呢?我们可以通过下面这个命令查看:

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/var/mysql/ |
+---------------+-----------------------+
1 row in set (0.00 sec)

在我的电脑本地,对应的数据库数据目录是 /usr/local/var/mysql/(Linux 系统通常是 /var/lib/mysql)。我们进入这个目录可以看到一个 demo 文件夹:

1.jpeg

这就是 demo 数据库对应的数据目录,后续所有该数据库下新建的表、表数据、索引信息都会存放在这里,由于现在还没有创建任何表,所以其中只包含了一个 db.opt 文件,这个文件中保存了该数据库的各种属性,比如该数据库的字符集和比较规则:

1.jpeg

新建数据表

接下来,我们在这个数据库中新建一张数据表,为了简化演示,先不包含任何索引信息:

mysql> use demo;
Database changed
mysql> CREATE TABLE `posts` (`id` int,`title` varchar(100),`content` text);
Query OK, 0 rows affected (0.03 sec)

创建完成后可以通过 show tables; 查看是否创建成功:

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| posts          |
+----------------+
1 row in set (0.00 sec)

我们还可以通过 show create table posts 命令查看创建 posts 表的执行计划:

可以看到,虽然没有显示指定,但是数据表沿用了数据库默认的存储引擎(InnoDB)、字符集(排序规则省略,默认是 utf8mb4 字符集对应的 utf8mb4_general_ci)。

数据表对应的磁盘文件

回到 demo 数据目录文件夹,可以看到现在新增了两个文件:

1.jpeg

其中 posts.frm 用于存放 posts 数据表的表结构,posts.ibd 用于存放 posts 表的表数据和索引(都是二进制文件,无法直接查看内容)。

需要注意的是,这是 MySQL 5.6.6+ 之后 InnoDB 引擎默认的磁盘文件格式,在此之前,InnoDB 引擎默认会将所有表的数据和索引存放到系统表空间,而不是这样通过一个磁盘文件对应一张表的数据和索引(这种存储方式叫做独立表空间)。

当然,你可以通过修改 MySQL 配置文件配置值 innodb_file_per_table 来切换使用独立表空间还是系统表空间,值为 0 表示不使用独立表空间,值为 1 表示使用:

[mysqld]
innodb_file_per_table=0

以上配置只对新建表生效,已存在的表还是保存在独立表空间中,你可以通过如下命令将它们迁移到系统表空间:

mysql> ALTER TABLE posts TABLESPACE innodb_system;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

这样一来,在 demo 数据目录下就看不到 posts.ibd 了:

1.jpeg

那系统表空间对应的磁盘文件路径又是什么呢?我们可以通过如下 show variables 命令查看:

mysql> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

重点关注 innodb_data_file_path 这个配置项,对应配置值由三部分组成,并且通过 : 分隔,第一部分 ibdata1 就是系统表空间的路径(相对于 MySQL 数据目录根目录,这里是 /usr/local/var/mysql),第二部分表示初识空间大小是 12M,最后的 autoextend 表示表空间会自动扩展,如果你想要修改这个配置,可以在 MySQL 配置文件中设置 innodb_data_file_path 配置项:

[mysqld]
innodb_data_file_path=mydata1:256M:autoextend

innodb_data_home_dir 用于配置 innodb_data_file_path 相对路径的根目录,默认是 MySQL 的数据目录,你可以通过该配置值将其设置为其他路径,这里就不具体展开了。

MyISAM 引擎对应的磁盘文件

以上都是针对 InnoDB 引擎的介绍,如果是更老的 MySQL 版本,默认使用的存储引擎是 MyISAM,MyISAM 和 InnoDB 存储数据的区别是前者数据和索引是分开存储的。我们创建一张表 posts_mi 进行演示,手动指定其存储引擎是 MyISAM:

mysql> CREATE TABLE `posts_mi` (`id` int,`title` varchar(100),`content` text) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

在数据目录 demo 下可以看到新增了如下三个文件:

1.jpeg

其中 posts_mi.frm 保存的是 posts_mi 表的表结构,posts_mi.MYI 保存的是 posts_mi 表的索引信息,posts_mi.MYD 保存的是 posts_mi 表的所有数据记录。

此外,MyISAM 引擎不存在表空间一说,所有的数据和索引都是存储在数据库对应的数据目录下的。

0

评论区