MySQL探险-1、数据库与MySQL基本介绍

最新推荐文章于 2024-07-25 01:00:00 发布

NarutoConanKing

最新推荐文章于 2024-07-25 01:00:00 发布

阅读量222

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/NarutoConanKing/article/details/106585223

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、概览：

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。
数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，用户可以对文件中的数据进行新增、查询、更新、删除等操作。

①关系型数据库 vs NoSQL

目前数据库的类型主要分为两种，一种是关系型数据库，另一种是非关系型数据库（NoSQL）。

关系型数据库，顾名思义，是指存储的数据之间具有关系。这种所谓的关系通常用二维表格中的行列来表示，即一个二维表的逻辑结构能够反映表中数据的存储关系。
简单来说，关系型数据库的存储就是按照表格进行的。数据的存储实际上就是对一个或者多个表格的存储。通过对这些表格进行分类、合并、连接或者选取等运算来实现对数据库的管理。常见的关系型数据库有 MySQL、Oracle、DB2 和 SqlServer 等。

非关系型数据库（NoSQL）是相对于关系型数据库的一种泛指，它的特点是去掉了关系型数据库中的关系特性，从而可获得更好的扩展性。NoSQL并没有严格的存储方式，但采用不同的存储结构都是为了获得更高的性能和更高的并发。
NoSQL根据存储方式可分为四大类，键值存储数据库、列存储数据库、文档型数据库和图形数据库。这四种数据的存储原理不尽相同，因而在应用场景上也有些许的差异。一般常用的有作为数据缓存的 Redis 和分布式系统的 HBase。
NoSQL

关系型数据库与非关系型数据库本质上的区别就在于存储的数据是否具有一定的逻辑关系，由此产生的两类数据库看的性能和优劣势上也有一定的区别。
关系型数据库 vs NoSQL

常见数据库排行

②MySQL 简介

MySQL 是关系型数据库的主流数据库之一，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。

MySQL 数据库具有以下几个方面的优势：
●体积小、速度快。
●代码开源，采用了 GPL 协议，可以修改源码来开发自己的 MySQL 系统。
●支持大型的数据库，可以处理拥有上千万条记录的大型数据库。
●使用标准的 SQL 数据语言形式，并采用优化的 SQL 查询算法，有效地提高了查询速度。
●使用 C/C++ 编写，并使用多种编译器进行测试，保证源代码的可移植性。
●可运行在多个系统上，并且支持多种语言。
●核心程序采用完全的多线程编程，可以灵活地为用户提供服务，充分利用 CPU 资源。

二、MySQL 架构：

MySQL 可以在多种不同场景中应用并发挥良好作用，与其架构特色密不可分。这个特色主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

MySQL 的逻辑架构可分为四层，包括连接层、服务层、引擎层和存储层，各层的接口交互及作用如下图所示：
MySQL 逻辑架构

其中各层功能如下：
●连接层：负责处理客户端的连接以及权限的认证。
●服务层：定义有许多不同的模块，包括权限判断，SQL接口，SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行等。MySQL的查询语句在服务层内进行解析、优化、缓存以及内置函数的实现和存储。
●引擎层：负责MySQL中数据的存储和提取。MySQL中的服务器层不管理事务，事务是由存储引擎实现的。其中使用最为广泛的存储引擎为InnoDB，其它的引擎都不支持事务。
●存储层：负责将数据存储与设备的文件系统中。

三、MySQL 存储引擎：

①存储引擎概览

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

用户可以灵活选择使用哪一种引擎，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

MySQL 服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎。

1、查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES;

-- 展示表结构（可查看具体某一个表所使用的存储引擎）
SHOW CREATE TABLE + tablename;

-- 查询表详细信息（可准确查看某个数据库中的某一表所使用的存储引擎）
SHOW TABLE STATUS LIKE + 'tablename';
SHOW TABLE STATUS FROM + databasename + WHERE name = "tablename";

2、设置存储引擎

-- 建表时指定存储引擎。（默认的就是INNODB，不需要设置）
CREATE TABLE tablename (i INT) ENGINE = INNODB;
CREATE TABLE tablename (i INT) ENGINE = CSV;
CREATE TABLE tablename (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE tablename ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine = MyISAM;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表。

3、存储引擎对比

常见的存储引擎有 InnoDB、MyISAM、MEMORY 等。

InnoDB 是目前 MySQL 默认的存储引擎，支持事务、行级锁定和外键。

ⅰ、文件存储结构

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据（meta）信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有 .frm 文件，命名方式为：数据表名.frm，如 user.frm。

查看 MySQL 数据保存在哪里可以使用命令：

SHOW VARIABLES LIKE 'datadir'

或

SHOW VARIABLES LIKE 'data%'

ⅱ、InnoDB vs MyISAM

㈠文件存储结构对比：

MyISAM 物理文件结构为：
●.frm文件：与表相关的元数据信息都存放在 frm 文件中，包括表结构的定义信息等。
●.MYD（MYData）文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据。
●.MYI（MYIndex）文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息。

InnoDB 物理文件结构为：
●.frm 文件：与表相关的元数据信息都存放在 frm 文件中，包括表结构的定义信息等。
●.ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。独享表空间存储方式使用 .ibd 文件，并且每个表一个 .ibd 文件共享表空间存储方式使用 .ibdata 文件，所有表共同使用一个（或多个，可自己配置）.ibdata 文件。

㈡常见对比项：

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。

InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MyISAM 会失败。

InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。MyISAM 的一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一。
InnoDB vs MyISAM

㈢自增主键 ID 值对比：

示例：一张表里有自增主键 ID，当插入了17条记录之后，删除第15、16、17条记录，再把Mysql重启，再插入一条记录，这条记录的 ID 是18还是15？

如果表的类型是 MyISAM，那么是18。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件中，重启 MySQL 自增主键的最大 ID 也不会丢失。

如果表的类型是 InnoDB，那么是15。因为 InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或对表进行 OPTION 操作，都会导致最大 ID 丢失。

㈣count 对比：

InnoDB 不保存表的具体行数，执行 SELECT count( * ) FROM tablename 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？

这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

四、MySQL 数据类型：

MySQL 的数据类型主要包括以下五大类：
●整数类型：BIT、BOOL、TINYINT、SMALLINT、MEDIUMINT、INT、 BIGINT。
●浮点数类型：FLOAT、DOUBLE、DECIMAL。
●字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
●日期类型：DATE、DATETIME、TIMESTAMP、TIME、YEAR。
●其他数据类型：BINARY、VARBINARY、ENUM、SET、GEOMETRY、POINT、MULTIPOINT、LINESTRING、MULTILINESTRING、POLYGON、GEOMETRYCOLLECTION等。
数值相关
日期相关
字符串相关

CHAR vs VARCHAR

CHAR 是固定长度，VARCHAR 长度可变：
CHAR(n) 和 VARCHAR(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
存储时，前者不管实际存储数据的长度，直接按 CHAR 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间。

相同点：
●CHAR(n)，VARCHAR(n)中的 n 都代表字符的个数。
●超过最大长度 n 的限制后，字符串会被截断。（根据模式不同，可能抛出异常 “Data too long for column ‘xxx’ at row x”）

不同点：
●CHAR 不论实际存储的字符数都会占用 n 个字符的空间；而 VARCHAR 只会占用实际字符应该占用的字节空间加 1（实际长度length，0 <= length < 255）或加 2（length > 255）。因为 VARCHAR 保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于 255 则使用两个字节来保存长度）。
●能存储的最大空间限制不一样（CHAR 的存储上限为 255 字节）。
●CHAR 在存储时会截断尾部的空格，而 VARCHAR 不会。
●CHAR 适合存储很短的、一般固定长度的字符串。例如，CHAR 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。

BLOB vs TEXT

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB：TINYBLOB、BLOB、MEDIUMBLO 和 LONGBLOB。

TEXT 是一个不区分大小写的 BLOB。类似的也有四种类型的 TEXT：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

浮点数类型注意事项

浮点数类型可以用类型名称后加 (M,D) 来表示，M 表示该值的总共长度，D 表示小数点后面的长度，M 和 D 又称为精度和标度。

DECIMAL 在不指定精度时，默认整数为10，小数为0。

在进行末位取舍时，DECIMAL 采用的是四舍五入的方式，而 FLOAT 和 DOUBLE 采用的是四舍六入五成双（5 以下舍弃，5 以上进位，如果需要处理数字为 5 的时候，需要看 5 后面是否还有不为 0 的任何数字。如果有，则直接进位；如果没有，需要看 5 前面的数字。若是奇数则进位，若是偶数则将 5 舍掉）的方式。

DECIMAL 在插入的数据超过精度之后会触发警告。