MySQL 是怎么存储数据的

starine
672 阅读6分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路

引言

只有正确地把数据存储起来，我们才能进行有效的处理和分析，进而对数据进行科学的挖掘，从而为决策提供数据支持。在 MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

1. 创建 MySQL 数据库

数据存储的第一步，就是创建数据库。从系统架构的层次上看，MySQL 数据库系统从大到小依次是数据库服务器、数据库、数据表、数据表的行与列。

数据库是 MySQL 里面最大的存储单元。没有数据库，数据表就没有载体，也就无法存储数据。

如何创建数据库

CREATE DATABASE demo；

如何查看数据库

SHOW DATABASES;

image.png

demo是我们通过 SQL 语句创建的数据库，是我们用来存储业务数据的。

information_schema 是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等。

performance_schema是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。

sys数据库是 MySQL 系统自带的数据库，主要作用是，以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL的技术性能。

mysql数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等。

2. 确认字段

数据存储流程的第二步是确认表的字段。

MySQL 数据表由行与列组成，一行就是一条数据记录，每一条数据记录都被分成许多列，一列就叫一个字段。每个字段都需要定义数据类型，这个数据类型叫做字段类型。

3. 创建数据表

数据存储流程的第三步，是创建数据表。

CREATE TABLE demo.test(
    barcode text,
    goodsname text,
    price int
);

注意：

  • 创建表的时候，最好指明数据库。否则，如果你没有选中数据库，可能把表创建到错误的数据库中。

  • 不要在最后一个字段的后面加逗号“,”

如何查看表的结构

DESCRIBE demo.test;

image.png

如何查看数据库中的表

先用 USE 语句选择数据库：

USE demo;

运行后，进入 demo 数据库，然后用 SHOW 语句查询这个数据库中所有的表：

SHOW TABLES;

image.png

如何设置主键

MySQL 中数据表的主键，是表中的一个字段或者几个字段的组合。它主要有 3 个特征：

  • 必须唯一，不能重复；

  • 不能是空；

  • 必须可以唯一标识数据表中的记录。

一个 MySQL 数据表中只能有一个主键。 虽然 MySQL 也允许创建没有主键的表，但是，我建议你一定要给表定义主键，并且养成习惯。因为主键可以帮助你减少错误数据，并且提高查询的速度。

可以通过一条 SQL 语句，修改表的结构，来增加一个主键字段：

ALTER TABLE demo.test 
ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE，表示修改表；

ADD COLUMN，表示增加一列；

PRIMARY KEY，表示这一列是主键；

AUTO_INCREMENT，表示每增加一条记录，这个值自动增加。

4. 插入数据

数据存储流程的第四步，也是最后一步，是把数据插入到表当中去。

INSERT INTO demo.test (barcode,goodsname,price) VALUES ('0001','签字笔',3);

  • 要插入数据的字段名也可以不写，但是我建议你不要怕麻烦，一定要每次都写。这样做的好处是可读性好，不易出错，而且容易修改。否则，如果你记不住表的字段，就只能去查表的结构，才能知道值所对应的字段了。

  • 由于字段 itemnumber 定义了 AUTO_INCREMENT，所以我们插入一条记录的时候，不给它赋值，系统也会自动给它赋值。而且，每次赋值，都会在上次的赋值基础上，自动增加 1。你也可以在插入一条记录的时候给 itemnumber 赋值，由于它是主键，新的值必须与已有记录的 itemnumber 值不同，否则系统会提示错误。

MySQL 逻辑架构

第一层是服务器层，主要提供连接处理、授权认证、安全等功能，该层的服务不是 MySQL 独有的，大多数基于网络的 C/S 服务都有类似架构。

第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。

第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

操作总结

-- 创建数据库
CREATE DATABASE demo； 
-- 删除数据库
DROP DATABASE demo； 
-- 查看数据库
SHOW DATABASES; 
-- 创建数据表：
CREATE TABLE demo.test ( 
    barcode text, 
    goodsname text, 
    price int 
);
-- 查看表结构
DESCRIBE demo.test; 
-- 查看所有表
SHOW TABLES; 
-- 添加主键
ALTER TABLE demo.test 
ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT;
-- 向表中添加数据
INSERT INTO demo.test (barcode,goodsname,price) VALUES ('0001','签字笔',3);

程序员的第一性原理应该是实际业务需求，技术服务于业务，一切从实际需求出发。

面试题 1

select count(*) from t;

t中有id(主键)，name，age,sex4个字段。假设数据10条，对sex添加索引。

用explain 查看执行计划发现用了sex索引，为什么不是主键索引呢?主键索引应该更快的

解答：

  1. MySQL Innodb的主键索引是一个B+树，数据存储在叶子节点上，10条数据，就有10个叶子节点。

  2. sex索引是辅助索引，也是一个B+树，不同之处在于，叶子节点存储的是主键值，由于sex只有2个可能的值：男和女，因此，这个B+树只有2个叶子节点，比主键索引的B+树小的多

  3. 这个表有主键，因此不存在所有字段都为空的记录，所以COUNT(*) 只要统计所有主键的值就可以了，不需要回表读取数据

  4. SELECT COUNT(*) FROM t，使用sex索引，只需要访问辅助索引的小B+树，而使用主键索引，要访问主键索引的那个大B+树，明显工作量大，这就是为什么，优化器使用辅助索引。

avatar
文章
阅读
粉丝