从入门到精通 | MySQL知识整理（上）

共计 9684 个字符，预计需要花费 25 分钟才能阅读完成。

本文核心内容

第一部分：从入门到精通 | MySQL知识整理（上）

一、体系结构详解

1、安装MySQL所必需的体系结构组件：MySQL服务器，客户机程序以及MySQL非客户机程序；中央程序可以充当服务器，客户机程序连接到该服务器用以发出数据请求。

2、MySQL客户机/服务器通信可以运行在不同的操作系统中实现。

• 客户机程序可以连接到在相同主机或不同主机上运行的服务器
• 客户机/服务器通信可以发生在计算机运行不同操作系统的环境中

客户机程序：

1、mysql/mysqldump客户机程序是使用最多的一个客户段工具；

2、MySQL Workbench是一款GUI工具,可以用来：

• 为数据库建模
• 执行数据库查询
• 执行管理任务

MySQL服务器：

1、服务器和主机有什么区别：服务器：一个程序(mysqld),具有版本号和一系列功能；主机：物理计算机，其中包含：硬件配置、操作系统、网络地址

2、多个mysqld实例可同时运行在一台主机上;

服务器进程：

1、应用程序主要以mysqld进程来访问数据库服务器,由mysqld来操作不同的存储引擎，包括：磁盘、内存、网络;

2、mysqld(服务器程序)进程可以划分为以下三个层：

• 连接层：处理连接,此层存在于所有服务器软件(Web服务器)上;
• SQL层:SQL查询发送到所连接的应用程序处理;
• 存储层:处理数据存储,数据可以按不同格式和结构存储在不同物理介质上

连接层：

1、连接层可通过多种通信协议接受来自应用程序的连接，包含以下几种:

• TCP/IP;
• UNIX套接字;
• 共享内存;
• 命名管道;

2、其中TCP/IP是最常用的连接方式，适用于整个网络;客户机和服务器在同一台计算机上运行时,上面列出的其他协议仅支持本地连接;

3、此层针对每个连接维护一个线程,此线程处理查询执行;在某个连接可以开始发送SQL查询之前,将会通过验证用户名+口令+客户机主机来对该连接进行验证;

通信协议：

1、TCP/IP：该通信协议套件用于连接Internet上的主机;在Linux操作系中,TCP/IP是内置的,供 Internet使用,从而使其成为通过网络传输数据的标准;

2、UNIX套接字：一种进程间通信形式,用于在同一台计算机上的进程之间形成双向，通信链路的一端;套接字需要本地系统上的物理文件,这是适用于Linux的最佳连接类型;

3、共享内存：一种在程序之间传递数据的有效方式;一个程序创建其他进程(如果允许)可以访问的内存部分;;默认情况下,共享内存处于禁用状态,要启用共享内存连接,必须使用–shared-memory选项启动服务器;

4、命名管道：命名管道的使用偏向于客户机/服务器通信,其工作方式与套接字非常相似;命名管道支持读/写操作,以及服务器应用程序的显式“被动”模式;此协议仅适用于单台(Windows)计算机;默认情况下,命名管道处于禁用状态,要启用命名管道连接,必须使用–enable-named-pipe选项启动服务器;

SQL层：

1、解析器:验证语法是否正确;

2、授权:验证是否允许所连接的用户运行特定查询;

3、优化器:创建每个查询的执行计划,这是有关如何以最优化的方式执行查询的分步指令集,确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分;

4、查询执行:完成每个查询的执行计划;

5、查询高速缓存:(可选)可配置的查询高速缓存,可用于存储(并立即返回)执行的查询和结果;

6、查询日志记录:可以启用以跟踪执行的查询;SQL语句处理：第一次的选择主要是查看是否有配置开启[高速缓存查询]的特性;

存储层：

1、通过MySQL,可以使用称为“存储引擎”的不同类型的存储,数据可以存储在磁盘,内存和网络中;

2、数据库中的每个表可以使用任何可用的存储引擎,“磁盘”存储便宜且持久,而“内存”存储则要快得多;

3、InnoDB是默认存储引擎,它可提供事务,全文索引和外键约束,因此适用于各种混合查询;它具有多种用途,支持读密集型工作负荷,读/写工作负荷和事务工作负荷;

4、其他存储引擎包括:

• MyISAM:适用于频繁读取但很少更新的数据;
• MEMORY:在内存中存储所有数据;
• NDB:供MySQL Cluster用来为高可用性数据提供冗余的可伸缩拓扑

注:存储引擎可扩展,超越存储层,而不只包含存储,它们还包括其他结构和实现机制;

存储引擎概览：

1、客户机通过以SQL语句形式向服务器发送请求从表中检索数据或更改表中的数据;

2、服务器通过使用双层处理模型执行每条语句;

3、客户机通常不需要关心哪些引擎参与SQL语句处理,这种独立于引擎的SQL语句的一些例外情况包括:

• CREATE TABLE具有ENGINE选项,可基于每个表指定要使用的引擎;
• ALTER TABLE具有ENGINE选项,允许将表转换为使用不同的存储引擎;
• 某些索引类型仅适用于特定存储引擎;例如,仅InnoDB和MyISAM引擎支持全文索引;
• COMMIT和ROLLBACK操作仅影响事务存储引擎(例如InnoDB和NDB)管理的表;

依赖于存储引擎的功能：

1、存储介质:表存储引擎可以在磁盘上,在内存中或通过网络存储数据;

2、事务功能:某些存储引擎支持全面的ACID事务功能,而其他存储引擎可能不具有事务支持;

3、锁定:存储引擎可能使用不同的锁定粒度(例如表级别锁定或行级别锁定)和机制来提供与并发事务的一致性;

4、备份和恢复:可能会受到存储引擎存储和操作数据的方式的影响;

5、优化:不同的索引实现可能会影响优化,存储引擎以不同的方式使用内部高速缓存,缓冲区和内存以优化性能;

6、特殊功能:某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力;TIPS:优化器可能需要根据存储引擎进行不同的选择,但这均是通过每种存储引擎支持的标准化接口(API)进行处理的;

MySQL如何使用磁盘空间：

1、默认情况下,程序文件随数据目录一起存储在服务器安装目录下;执行各种客户机程序,管理程序和实用程序时将创建程序可执行文件和日志文件;

2、首要使用磁盘空间的是数据目录:服务器日志文件和状态文件包含有关服务器处理的语句的信息,日志可用于进行故障排除/监视/复制和恢复;InnoDB日志文件(适用于所有数据库)驻留在数据目录级别;InnoDB系统表空间包含数据字典,撤消日志和缓冲区;每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表),数据库目录存储以下内容:

• 数据文件:特定于存储引擎的数据文件,这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎;
• 格式文件(.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中;
• 触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象;

数据目录的位置取决于配置,操作系统,安装包和分发;典型位置是/var/lib/mysql;MySQL在磁盘上存储系统数据库(mysql),mysql包含诸如用户/特权/插件/帮助列表/事件/时区实现和存储例程之类的信息;

MySQL如何使用内存：

1、内存分配可以划分为以下两种类别:

• 全局(每实例内存):服务器启动时分配一次并在服务器关闭时释放,此内存在所有会话间共享;当所有物理内存用尽时,操作系统开始交换,这会对MySQL服务器性能具有不利影响,可能会导致服务器崩溃;
• 会话(每会话内存):基于每个会话(有时称为“线程”)动态进行分配;此内存可在会话结束时或不再需要会话时释放,此内存多用于处理查询结果,所使用的缓冲区大小基于每个连接;例如,read_buffer为10MB且具有100个连接意味着可能总共有100*10MB同时用于所有读取缓冲区;

内存结构：

服务器在运行时会为许多种类的数据分配内存:

1、查询高速缓存还用于加速处理重复发出的查询;

2、线程高速缓存:在MySQL(和其他程序)中使用线程将应用程序执行划分为两个或更多个同时运行的任务,将会为连接到MySQL服务器的每个客户机创建单独的线程以处理该连接;

3、缓冲区和高速缓存:缓冲区和高速缓存提供数据管理子系统并支持快速访问项目,例如授权表缓冲区,存储引擎缓冲区(如InnoDB的日志缓冲区)和保存开放表说明符的表开放缓冲区;如果使用MEMORY存储引擎,MySQL将使用主内存作为主体数据存储,其他存储引擎也可能使用主内存进行数据存储,但MEMORY是唯一的,未设计为在磁盘上存储数据;

4、连接/会话:1）.内部临时表:在某些查询执行情况下,MySQL会创建一个临时表来解析查询;可以在内存中或在磁盘上创建临时表,具体取决于其大小或内容或者查询语法;2）.特定于客户机的缓冲区:专门设计为支持所连接的各个客户机;缓冲区示例包括:用于交换信息的通信缓冲区;排序操作:表读取缓冲区(包括支持联接的缓冲区);

MySQL插件接口：

1、当前,插件API支持:

• 可用于替换或扩充内置全文解析器的全文解析器插件;例如,某个插件可以使用不同于内置解析器所使用的规则将文本解析为字,要解析具有不同于内置解析器所预期的特征的文本,这很有用;
• 向服务器提供低级别存储,检索和数据索引的存储引擎;
• 信息模式插件;信息模式插件作为MySQL INFORMATION_SCHEMA数据库中的表出现,稍后将更详细地讨论INFORMATION_SCHEMA数据库;
• 守护进程插件启动在服务器内运行的后台进程(例如,定期执行心跳处理);

2、插件接口需要mysql数据库中的PLUGINS表,此表是在MySQL安装过程中创建的

二、系统管理

MySQL服务器分发

1.MySQL可用于多个操作系统,包括Linux/Windows/Mac OS X和等;本文仅讲述Linux;

2.MySQL可以作为二进制文件分发和源代码分发的形式提供:

• 二进制文件分发:是预编译的,可以运行的程序,可用于Enterprise和Community MySQL Server版本,这些二进制文件是正式的经过Oracle测试的版本;
• 源代码分发:不保证与商业代码更新一致,它们也不包括Oracle支持;

MySQL二进制文件分发

1.用于Linux的二进制文件:

• RPM文件可用于基于RPM的Linux分发,例如Oracle Linux;通过使用rpm程序或者通过使用yum等软件包管理器来安装这些文件,每个RPM的安装布局由RPM文件自身内包含的规范文件提供;(使用rpm -qpl 来查看安装时RPM文件内容的位置)
• TAR文件可用于多种Linux和UNIX类似系统,要安装这种类型的分发,使用.tar程序在安装目录中解压缩该分发;

2.用于Windows的二进制文件:

• 完整分发:包含MySQL安装的所有文件以及配置向导。
• 非安装分发:.zip归档文件,不使用安装或配置向导,您只需解压缩该归档文件并将其移至所需的安装位置;

TIPS:二进制文件分发还可以压缩文件形式用于多个其他操作系统(包括Oracle Solaris);

MySQL源代码分发

1.如果需要预编译分发中可能没有的功能(例如完整的调试支持),可以根据源代码编译MySQL;

2.要使服务器在运行时使用较少内存,可能需要禁用不需要的功能;例如,可能需要禁用可选存储引擎,或者仅编译实际需要的那些字符集;

3.二进制文件分发仅可用于已发行的版本,不可用于最新的开发源代码;

4.源代码分发可以安装在任何所需位置;默认Linux安装位置为/usr/local/mysql;用于Linux的MySQL RPM安装文件

1.Oracle提供两种类型的MySQL RPM:

• 与分发无关:MySQL提供给社区的RPM,它们应该可以在支持RPM软件包并使用glibc 2.3的所有Linux版本上运行;(查看方式:ldd –version)
• 特定于分发:面向目标Linux平台,Oracle为许多平台提供了RPM文件;

2.MySQL的RPM安装通常分为不同的软件包,对于标准安装,必须至少安装服务器程序 和客户机程序,标准安装不需要其他软件包

6.启动数据库:service mysql start;

• 查看后台进程:ps -ef | grep mysql;
• 本来是启动的mysqld服务,后台多了一个mysqld_safe服务,之后讲数据库启动的几种方式会提到;

8.卸载数据库:

• 查看安装了哪些软件:yum list | grep MySQL;删除软件:yum remove MySQL-*;

从MySQL 5.7.5以后,mysql_upgrade将直接和MySQL Server交互,发送所需的SQL语句来执行升级;补充:升级过程;

• 1.停止服务:service mysql stop;
• 2.备份文件:- 创建临时目录:mkdir /tmp/mbackup;- 备份配置文件:cp /etc/my.cnf /tmp/mbackup/;- 备份数据文件:cp -rf /var/lib/mysql/ /tmp/mbackup/mysql;也可以用mysqldump/xtrabackup;
• 3.删除软件:yum -y remove MySQL-*-advanced-5.6.25-1.el6.x86_64;(不推荐)
• 4.安装软件:rpm -ivh –replacefiles MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm;
• 5.拷贝回配置文件:cp /tmp/mbackup/my.cnf /etc/my.cnf;
• 6.尝试重启:service mysql restart;
• 7.执行更新:mysql_upgrade -uroot -p;
• 8.查看生成的更新文件并且尝试登录查看版本信息:less $MYSQL_DATADIR/mysql_upgrade_info;

多个服务器选项

使用mysqld或mysqld_multi以及每个服务器函数的相应选项来调用每个MySQL服务器:

1.数据目录:使用–datadir选项的唯一值启动每个服务器;

2.网络:通过使用–port,–socket和–shared-memory-basename选项的唯一值启动每个服务器,将每个服务器设置为使用其自己的网络接口;

3.组名称:使用mysqld_multi时,每个服务器组在Linux或UNIX上必须具有唯一的mysqldN名称;

4.日志文件:每个服务器必须具有其自己的日志和PID文件;

5.InnoDB表空间和日志文件:不能由多个服务器共享;

6.Windows服务名称:每个mysqld Windows服务必须使用唯一的服务名称,通过使用–install设置服务名称;服务器启动时,它们从标准选项文件中的各个相应服务组中读取选项;

练习任务

1.创建和导入world_innodb数据库;

• $ mysql -uroot -p;mysql> CREATE DATABASE world_innodb;mysql> USE world_innodbmysql> SET autocommit=0;mysql> SOURCE /labs/world_innodb.sql;mysql> SET autocommit=1; // 为了提高运行速度,批量提交;

3.检查MySQL服务器的状态;

• service mysql status;关闭/启动MySQL服务器;service mysql stop/start;

更多内容请关注微信公众号：数据与人