MySQL的体系结构与管理

一、MySQL的体系结构

1.MySQL的实例
一个MySQL实例的构成是：mysqld进程 + master thread进程 + worker thread进程 + 预分配的内存

• mysqld进程：MySQL服务的主进程，负责处理MySQL数据库的文件
• master thread线程：MySQL服务的主线程，负责管理其它线程
• worker thread线程：MySQL服务的工作线程
• 预分配内存：因为MySQL服务需要一直开启来处理客户端的请求，这样MySQL服务就要长期占用内存空间。所以在MySQL服务开启的那一刻，就会向系统申请一定的内存空间留有自己处理数据

简单的说，MySQL实例就是在一台服务器上开启一个服务端口，同时运行MySQL服务进程。同理，常说的MySQL多实例就是在一台服务器上开启多个不同的服务端口（如3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务

tips:MySQL是典型的C/S结构，提供了两种连接方式 TCP/IP和Socket
(1)TCP/IP连接方式： 例如-->mysql -uroot -p123456 -h 10.0.0.51 -P3306  此种方式适用于远程和本地连接，当一台服务器中有多实例时，可通过"-P"参数来指定连接哪个实例
(2)Socket连接方式： 例如-->mysql -uroot -p123456 -S /tmp/mysql.sock   此种方式只能用于本地连接，当一台服务器中有多实例时，可通过不同实例的"xxx.sock"文件来指定连接哪个实例  

1.1通过一张图来详细了解一下mysqld的程序结构

tips：
（1）mysql> show  processlist;  执行此语句后会显示所有的已经和本数据库建立的会话，每一个会话就是一个连接线程；其中yigemysql数据库实例最大允许会话数是可以通过参数设置的，默认是151个。
（2）优化MySQL功能的主要是在SQL层进行操作的

1.2 MySQL逻辑存储结构

• 字符集（charset）：字符集就是编码表，MySQL 5.7默认字符集时"latin"

mysql>show charset;  查看MySQL数据库中支持哪些字符集
常用的字符集为：utf8mb4、utf8
两者的区别为：
utf8mb4：最大支持4个字节编码一个字符，且支持emoji表情，utf8mb4字符集是包含utf8字符集的，是推荐使用的字符集
utf8：最大支持3个字节编码一个字节，这样有些编码较长的字符就无法存储了。若是想节省空间，那就选择utf8字符集

例：
修改一个数据库的字符集为utf8mb4:
mysql>alter database 数据库名 charset utf8mb4;

修改一张表的字符集为utf8mb4：
mysql>alter table 表名 charset utf8mb4

修改表中某一字段的字符集为utf8mb4,并使用utf8mb4_general_ci校对规则：
mysql>alter table 表名 change 字段名 字段名 字段约束 character set utf8mb4 collate utf8mb4_general_ci;
或
mysql>alter table 表名 modify 字段名 字段约束 character set utf8mb4 collate utf8mb4_general_ci;

tip：
1、若创建数据库时指定了字符集和存储引擎，那么在此数据库中建立的表会自动继承数据库的字符集类型和存储引擎的类型。
2、执行"alter database 数据库名 charset utf8mb4;"语句后，MySQL中的"character_set_database"参数也会被修改。
   "character_set_database"此参数决定着新建数据库的字符集类型。

• 校对规则（collation，也叫排序规则）：校对规则是在字符集内用于比较字符的一套规则，比如区分字母的大小写。不同的字符集有不同额校对规则，但是一个校对规则只能属于一个字符集。

mysql>show collaton;  查看所有的校对规则
例如：utf8mb4_general_ci  是针对utf8mb4字符集，按照普通的字母排序且不区分大小写的校对规则，如：a B c D。（以"_ci"结尾的校对规则，都是大小写不敏感的校对规则；以"_cs"结尾的校对规则，都是大小写敏感的校对规则）
     utf8mb4_bin         是针对utf8mb4字符集,按照二进制排序的校对规则，如：B D a c 

• 数据类型：用于约束表中每列数据。

数字类型：
     整数：tinyint 一个字节
          int     四个字节
     浮点数：
字符串类型：
     char(最大允许255个字符) 定长字符串类型，一旦设置成功，系统就会立即分配指定字符长度的存储空间。若存储的字符串长度小于设定的长度，为占满的空间使用"空格"填充
          例如：char(11)设置成功后，系统就会立马分配11个字符长度的存储空间
     varchar(最大允许65535个字符) 变长字符串类型，每次存储数据前，系统都要计算一下此字符串的长度，按需分配磁盘空间
            例如：varchar(11)设置成功后，系统并不会立马分配磁盘空间，而是根据存储的字符串的长度来分配空间，但是最大字符串长度不能超过11个字符
     此外，varchar（）类型系统会根据所存储字符串的长度来单独申请相应的空间来存储"字符长度"。比如字符串长度小于255，那么只需要分配1个字节的空间；大于255，就需要分配两个字节及以上的空间了
enum枚举类型
     如：city enum('sz','sh','bj')  建表时可用于枚举一些同类的事务
时间类型
     datetime  范围：1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
     timestamp 范围：1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999
     
二进制类型

建表举例：
   create table study.test(
      'id'  int not null,
      'city' enum('sz','bj','sh'),
      'time' datatime null,
      primary key('id');
   )
插入数据：
   insert into study.test(id,city,time) values(1,'sz','2020-03-05 08:08:08');

1.3MySQL物理存储结构

tips:
(1)innoDB存储引擎下，数据存储的基本单位称为page(页)，大小为16k,可通过设置"innodb_page_size"参数改变
(2)当MySQL数据库中要申请空间存储数据时，就会一次性直接分配连续的64页存储空间，即1M的连续空间。这64页连续的空间称之为"区"
(3)page的大小可以通过参数改变，但是区的大小时固定的

二、MySQL的基础管理

1.MySQL的启动和关闭
1.1日常的启停

• CentOS 6版本：CentOS 6系统中启动MySQL服务，是执行"mysql.server start"命令来启动mysql.server脚本，然后通过mysql.server脚本再调用mysqld_safe脚本，最后mysqld_safe脚本再启动mysqld脚本，从而完成MySQL服务的开启
• CentOS 7版本：CentOS 7系统启动MySQL服务是通过systemd服务在"/usr/lib/systemd/system"目录下的msyql.service文件中，直接执行mysqld脚本从而启动MySQL服务，MySQL服务启动所需要的参数依赖my.cnf配置文件提供。相对CentOS 6省去了中间过程
  

tip:
   以上的两种方式启动MySQL服务都无法在命令行指定参数，来查看MySQL服务相应的运行情况，从而也就无法进行维护。CentOS 6启动MySQL服务是命令行只允许"mysql.server start|stop|restart";
CentOS 7启动MySQL服务虽然可以在配置文件my.cnf指定参数，但是每次要查看MySQL服务不同的状态就得修改配置文件，比较麻烦
    所以若想在命令行中指定具体的参数来查看MySQL服务的相应运行状态，需要使用维护模式，就是我们自己调用MySQL服务启动中所需的脚本：mysqld_safe、mysqld

1.2维护性任务

[root@db01 ~]#mysqld_safe &            启动维护模式
[root@db01 ~]#mysqladmin -uroot -p123456 shutdown       关闭维护模式

我们忘记MySQL用户密码时使用的"mysqld_safe --skip-grant-tables --skip-networking &"命令就是进入维护模式了，
其中"--skip-grant-tables"、"--skip-networking"就是我们指定的参数，让我们登录MySQL服务是跳过授权表。

tip:
MySQL服务允许有多个配置文件：
[root@db01 ~]# mysqld --help --verbose |grep my.cnf    查看MySQL服务有哪些配置文件
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
[root@db01 ~]#mysqld_safe --default-file=/tmp/xxx.txt &     强制使用自定义的配置文件

MySQL服务启动时，配置文件的读取顺序为依次从左到右。后面配置文件的配置信息会覆盖前面配置文件的配置信息。（不建议使用多个配置文件容易出错）