（总结篇）MySQL 概览

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 5.8k | 阅读时长 ≈ 8 分钟

什么是 MySQL？

  MySQL 是一种使用 C 和 C++ 语言编写的关系型数据库管理系统。

组织结构

  在数据库的世界内，MySQL（C/S 架构）被划分到关系数据库管理系统（Relational Database Management System,RDBMS），RDBMS可以划分为：

  • 数据库（DB）：就是一个存放信息的仓库，构造简单且遵循下面的规则：
    • 数据库里的数据集合都存放在数据表（table）中；
    • 数据表由数据行（row）和数据列（column）构成；
    • 一个数据行就是数据表里的一条记录；
    • 记录可以包含多个信息项，数据表里的每一个数据列都对应一个信息项
  • 管理系统（MS）：用来对数据进行插入、检索、修改、删除等操作的软件；
  • 关系（R）：RDBMS是DBMS中的一种，这种DBMS的专长就是把分别存储在两个数据表里的信息联系起来，这种联系是通过查找两个数据表的共同元素来实现的。RDBMS的威力在于它能方便地抽取出数据表里的数据并把它们与其他相关数据表的信息结合起来，为那些单独利用某个数据表无法找到答案的问题提供答案。

数据完整性

  关系的约束条件也称为关系的数据完整性规则，它是对关系的一些限制和规定，包括：

  • 实体完整性：关系模型对应的是现实世界的数据实体，而关键字是实体惟一性的表现，没有关键字就没有实体．因此所有关键字不能为空值，这是实体存在的最基本的前提。
  • 参照完整性：亦称作引用完整性，其是对关系外部关键字的规定，要求外部关键字的取值必须是客观存在的，即不允许在一个关系中引用另一个关系不存在的元组。
  • 用户定义完整性：由用户根据实际情况，对数据库中数据的内容所作的规定。通过这些限制让数据库接受符合完整性约束条件的数据值，不接受违反约束条件的数据，从而保证数据库的数据合理可靠。

范式

  参考知乎最高赞文章
  问：数据库为什么需要范式？
  答：范式可以尽可能地消除冗余的数据。
  对于一张数据表的结构，应该要符合的某种设计定义的标准级别。
  就像家里装修买建材，最环保的是 E0 级，其次是 E1 级，还有 E2 级等等。
  因此，数据库范式也分为 1NF，2NF，3NF，BCNF，4NF，5NF。
  一般在我们设计关系型数据库的时候，最多考虑到 BCNF 就够。符合高一级范式的设计，必定符合低一级范式，例如符合 2NF 的关系模式，必定符合 1NF。

  下面只做简单介绍：

  • 1NF ——符合 1NF 的数据表中的每个属性都不可再分，即数据表的每一列都是不可分割的原子项
  • 2NF ——在 1NF 基础上消除了非主属性对于码的部分函数依赖，即数据表有且仅有一个主键，其他列完全依赖于主键（即一个表只做一件事，如学生表不该有成绩信息列）
  • 3NF ——在 2NF 基础上消除了非主属性对于码的传递函数依赖，即数据表的每一列都和主键直接相关而非间接相关（即列之间无传递关系，如单价列 x 数量列 = 总价列 间存在传递关系）
  • BCNF ——在 3NF 基础上消除主属性对于码的部分与传递函数依赖

反（第）三范式

  反三范式是基于第三范式所调整的，没有冗余的数据库未必是最好的数据库。
  有时为了提高运行效率，就必须降低范式标准，适当地保留冗余数据。

下载安装

  不同系统 MySQL 的安装方式不同，具体步骤见本系列——（运维篇）MySQL 安装与配置一文。

支持

访问支持及 API

支持 API
应用程序 ODBC/ADO
C#、VB.NET ADO.NET
C/C++ MySQL++
PHP MySQLi、mysqlnd
javascript fibjs内置的mysql模块
Java JDBC

  当然也可以通过客户端软件和 MySQL连接，如 Navicat、mysqlyog 等等。

存储引擎

MySQL支持的引擎（部分） 功能特定 应用场景
InnoDB 提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并发控制 CRUD
MyISAM 提供较高的处理效率 主要用来插入和查询记录
Memory 临时存放数据，数据量不大，并且不需要较高的数据安全性 使用该引擎作为临时表，存放查询的中间结果
Archive 支持高并发的插入操作，但是本身不是事务安全的 只有INSERT和SELECT操作时可以考虑

  MySQL中查看系统中所有的储存引擎命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine （anything you write to it disappears） | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

  MySQL（版本 8.0 ）中默认储存引擎为：InnoDB，其支持事务处理。

数据类型

  MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。

整数类型

类型 字节长度 类型
tinyint 1 字节 小整数值
smallint 2 字节 大整数值
mediumint 3 字节 大整数值
int或integer 4 字节 大整数值
bigint 8 字节 极大整数值
float 4 字节 单精度 浮点数值
double 8 字节 双精度 浮点数值
decimal 待定 小数值

  对于整数类型的属性字段可以添加 auto_increment 自增约束条件。

日期/时间类型

类型 字节长度 格式 用途
date 3 YYYY-MM-DD 日期值
time 3 hh:mm:ss 时间值或持续时间
year 1 YYYY 年份值
datetime 8 YYYY-MM-DD hh:mm:ss 混合日期和时间值
timestamp 4 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳

字符串类型

类型 大小 用途
char 1 字节 (0~255 字符) 定长字符串
varchar 2 字节 (0~65535 字符) 变长字符串
tinytext 1 字节 (0~255 字符) 长文本数据
text 2 字节 (0~65535 字符) 长文本数据
mediumtext 3 字节 (0~16777215 字符) 长文本数据
longtext 4 字节 (0~4,294,967,295 字符) 长文本数据

二进制类型

类型 大小 用途
bit 1 字节 位类型
binary 1 字节 （固定）二进制字节字符串
varbinary 2 字节 （固定）二进制字节字符串
tinyblob 1 字节 （可变）二进制字节字符串
blob 2 字节 （可变）二进制字节字符串
mediumblob 3 字节 （可变）二进制字节字符串
longblob 4 字节 （可变）二进制字节字符串

SQL 结构化查询语言

  SQL（Structured Query Language，结构化查询语言）是 MySQL 服务器能听懂的语言，共分为四大类：

  • 数据定义语言（Data Definition Language，DDL）——CREATE、ALTER、DROP 等语句
  • 数据查询语言（Data Query Language，DQL）——SELECT、FROM、WHERE 及其组成语句
  • 数据操作语言（Data Manipulation Language，DML ）——UPDATE、INSERT、DELETE
  • 数据控制语言（Data Control Language，DCL）——COMMIT、ROLLBACK、SET TRANSACTION

  详情见——本系列[[1.（基础篇）什么是 SQL？]]一文。

SQL 约束

  SQL 的约束分为：

  • 默认约束
  • 唯一约束
  • 非空约束
  • 主键约束
  • 外键约束。

  详情见—— 本系列[[2.（基础篇）SQL 约束| SQL 约束]]一文。

SQL 连接

  SQL 的连接分为：

  • 自连接
  • 内连接
  • 左（外）连接
  • 右（外）连接
  • 全（外）连接
  • 交叉连接

  详情见—— 本系列[[3.（基础篇）SQL 连接| SQL 连接]]一文。

SQL 查询优化

  查询要占用时间，有时会长得让人心烦。为了缩短查询时间以提高效率，可以：

  • 使用索引（常用）
  • 检查MySQL的查询优化程序
  • 挑选合适的数据类型
  • 采用更有效地数据加载方法
  • 编写查询以影响服务器的调度机制
  • 分析底层硬件

  详情见—— 本系列[[10.（优化篇）SQL 查询优化]]一文。

事务处理

  事务（transaction）是作为一个不可分割的逻辑单元而被执行的一组SQL语句。

特性

  事务包含ACID四大特性：

  • 原子性（Atomic）：事务是一个不可分割的整体，其操作要么全执行，要么全不执行。
  • 一致性（Consistent）：一个事务执行前后，数据库数据的状态必须要保持一致。
  • 隔离性（Isolated）：也叫独立性,不同的事务之间不应相互影响。
  • 持久性（Durable）：也叫永久性，事务成功执行之后，它的影响将被永久的记录到数据库中。

并发一致性问题

  多个事务同时运行时可能存在下面的问题：

  • 脏读（dirty read）：指某个事务所作出的修改在它尚未被提交时就可以被其他事务看到
  • 不可重复读（nonrepeatable read）：指同一个事务使用同一条SELECT语句每次读取到的结果不一样。
  • 幻影数据行（phantom row）：也叫幻读，指某个事务突然看到了一个它以前没有见过的数据行。

隔离级别

   InnoDB 存储引擎为事务并发提供了下面 4 种从低到高的隔离级别：

  • READ UNCOMMITTED：允许某个事务看到其他事事务尚未提交的数据行改动；
  • READ COMMITTED：只允许某个事务看到其他事务已经提交的数据行改动；
  • REPEATABLE READ：如果某个事务两次执行同一个SELECT语句，其结果是可重复的。换而言之，即使有其他事务在同时插入或修改数据行，这个事务所看到的结果也是一样的；
  • SERIALIZABLE：类似REPEATABLE READ，但对事务的隔离更彻底：某个事务正在查看的数据行在该事务完成之前不允许其他事务修改。换而言之，如果某个事务正在读取某些数据行，在它完成之前，其他事务将无法对那些数据行修改。

  详情见——本系列[[4.（基础篇）事务处理]]一文。

安全管理

  MySQL 默认创建的一个名为root的用户账号对整个 MySQL 服务器具有完全的控制。非生产环境使用root用户很好。但在日常工作中，决不能使用root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用。

用户管理

  用户管理命令包括查看用户、创建用户、删除用户及用户权限设置：

  • 查看用户：首先选择mysql数据库命令USE mysql;之后查询SELECT user FROM user;
  • 创建用户：CREATE USER 用户名@主机名 IDENTIFIED BY '密码';（@主机名为可选参数）
  • 删除用户：DROP USER 用户名;

用户权限设置

  现在查询一个刚创建的git用户：

1
2
3
4
5
+---------------------------------+
| Grants for git@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `git`@`%` |
+---------------------------------+

  上面的输出代表这个刚创建的用户没有任何权限的,下面了解下权限相关命令：

  • 查看用户权限：SHOW GRANTS FOR 用户名;
  • 授予用户某个数据库查询权限：GRANT SELECT ON 数据库名.* TO 用户名;
  • 撤销用户某个数据库查询权限：REVOKE SELECT ON 数据库名.* FROM 用户名;
  • 授予用户某个数据库所有权限：GRANT ALL ON 数据库名.* TO 用户名;
  • 撤销用户某个数据库所有权限：REVOKE ALL ON 数据库名.* FROM 用户名;

注意哦：授予用户某个数据库的插入、更新、删除权限只需将上面的SELECT换成INSERT、UPDATE、DELETE即可；多个权限亦可同时授予，如GRANT SELECT,INSERT ON 数据库名.* TO 用户名;

  GRANT和REVOKE可在几个层次上控制访问权限:

  • 整个服务器，使用GRANT ALL和REVOKE ALL;
  • 整个数据库，使用ON database.*;
  • 特定的表，使用ON database.table;
  • 特定的列;
  • 特定的存储过程

用户口令修改

  不同版本 MySQL 的修改密码命令不同：

  • 8.0 之前：

    1
    2
    SET PASSWORD FOR 用户名 = Password('newPassword');
    SET PASSWORD = Password('newPassword')

  • 8.0 之后：

    1
    2
    mysqladmin -u root -p password newPassword
    # 之后输入原始密码则修改成功

备份与还原

  像所有数据一样，MySQL 的数据也必须经常备份，可以使用图形界面备份，亦可直接使用命令备份：

1
mysqldump -u用户名 -p密码 数据库名 > 保存的文件名路径

  备份后还原的步骤：

  • 登录 mysql
  • 创建数据库
  • 使用数据库
  • 执行文件还原命令:
1
source 文件名路径

高可用

  详见本系列—— [[37.（运维篇）MySQL 高可用架构 MGR]]

读写分离

  详见本系列——[[38.（运维篇）MySQL 读写分离中间件—— ProxySQL]]

参考

  • Paul DuBois. MySQL 技术内幕 [M]. 人民邮电出版社, 2011

文章信息

时间 说明
2019-06-06 初稿
2022-08-20 微调
0%