MySQL程序结构

在MySQL中一条语句的执行过程一般会经过三层，连接层、SQL层、存储引擎层，最终才能实现查询或者操作数据。

1. 连接层

1. 连接层用于提供连接协议 (TCP/IP、Socket)。

   • TCP/IP连接方式，可进行本地或远程连接，对应连接命令是：mysql -h [远程数据库主机] -P[数据库端口] -u[用户名] -p

   • socket连接方式，仅支持本地连接，对应连接命令是：mysql -S /tmp/mysql.sock -u[用户名] -p

2. 连接时会验证连接数据库的用户名/密码/IP等的合法性。

3. 验证合法后，会开启与客户端的专用连接线程，实现接收语句、返回结果。

   • 可以通过show processlist;命令查看当前的线程。

   • 一个会话就是一个线程，无论当前是否执行操作。

   • 连接线程默认最多能同时打开151个，如果8小时不操作则会断开，一般需要调整。

4. 最后将SQL语句交给下一层等待返回结果，获取结果后通过连接线程返回给客户端。

2. SQL层

1. SQL层用于处理连接层发来的SQL语句。
2. 收到SQL语句后会先根据SQL_MODE的配置来进行语法检查。
3. 然后再进行权限检查看，检查用户是否有权限执行该语句。
4. 然后进行预处理操作。
   1. 预处理操作会解析语句来生成多种执行计划树。
   2. 然后还会进行评估成本，通过优化器算法计算多种计划的执行代价，执行代价包括CPU、IO、内存资源的消耗。
   3. 最后选择优化语句，优化器会选择执行代价最小的计划，作为最终要使用的执行计划。
5. 预处理后会将最终选择的执行计划交给语句执行器，去真正的运行SQL语句。
6. 执行SQL时如果有配置，则还会进行缓存查询(默认不开启)、日志记录(默认不开启)等操作。

3. 存储引擎层

• 存储引擎层类似于文件系统，也是以特定格式存储或者读取磁盘数据，不同的存储引擎会有不同的存储格式。

• 存储引擎会根据SQL语句的执行计划，去磁盘上寻找对应的数据。然后以结构化的二维表的方式，返回给SQL层。SQL层再返回给连接层线程，最终展示给查询者。

MySQL存储结构

逻辑存储结构

• MySQL中以库和表两层作为逻辑存储结构，库是用来存放表的，而表是用来存放实际数据的。

• 可以把库比作Linux中的目录，表比作目录中的文件，只不过库只能在根创建，表只能在目录创建。

  库1
   - 表1
   - 表2
  库2
   - 表1
   - 表2
  

物理存储结构

• 物理存储结构即是MySQL数据在磁盘上的存储方式。

• 库

  • 在文件系统中以目录来表示：./[数据目录]/[数据库名]/
  • 例如：/data/apps/mysql/data/wordpress/

• 表

  • 表的物理存储结构，相关文件存放在数据库目录中，根据存储引擎不同，存储方式也不同。
  • InnoDB - 默认的存储引擎
    • .frm - 存储表的表结构(列、列属性)
    • .ibd - 存储表的数据记录和索引
      • MySQL存储表时，会在存储引擎层对ibd文件进行格式化，使其以16KB为1个页存储数据。
      • 索引的存储：默认每次分配1个页 (16KB)。
      • 数据行存储：默认每次分配64个连续的页 (1M)。
    • ibdata1 - 数据字典信息(元数据)，数据库所有的信息会被收集到该文件。
  • MyISAM - MySQL老版本使用的默认存储引擎，基本上被淘汰。
    • .frm - 存储表的表结构(列、列属性)。
    • .MYD - 存储表的数据记录。
    • .MYI - 存储表的索引。

启动配置说明

启动配置可以影响数据库的启动和客户端的功能。

配置文件标签

• 服务端相关标签
  • [mysqld] - 会读取该配置的启动命令：mysqld、mysqld_safe
  • [mysqld_safe] - 会读取该配置的启动命令：mysqld_safe
  • [server] - 会影响到所有服务端不建议使用
• 客户端相关标签
  • [mysql] - 会读取该配置的启动命令：mysqld
  • [mysqladmin] - 会读取该配置的启动命令：mysqladmin
  • [mysqldump] - 会读取该配置的启动命令：mysqldump
  • [client] - 会影响到所有客户端不建议使用

配置文件读取顺序

• MySQL中默认从多个配置文件中读取，它的读取顺序如下：
• /etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/my.cnf -> ~/.my.cnf
  • 也可以通过该命令查看读取顺序：mysqld --help --verbose | grep my.conf
  • 但如果启动命令行中指定了自定义配置文件，则只会使用到指定的配置文件。
• 后面的配置如果有和前面的配置相同参数，则会覆盖前面的配置。但一般来说不建议配置多个配置文件，管理起来会很乱。

使用自定义配置文件

• mysqld --defaults-file=[配置文件路径]
• mysqld-safe --defaults-file=[配置文件路径]
• mysqld启动命令行中使用该参数即可。

MySQL多实例配置

• MySQL的多实例配置也很简单，区分好不同的数据目录、配置文件，然后再分别初始化，分别指定不同的配置文件启动即可。

  # 准备多个数据库目录
  mkdir -P /data/apps/mysql/data/{mysql3306,mysql3307,mysql3308}/
  
  # 准备多个配置文件，每个配置文件的datadir、socket、server_id、port、log等参数的配置不能一样
  cp /data/apps/mysql/conf/my.cnf /data/apps/mysql/conf/my3306.cnf && \
  cp /data/apps/mysql/conf/my.cnf /data/apps/mysql/conf/my3307.cnf && \
  cp /data/apps/mysql/conf/my.cnf /data/apps/mysql/conf/my3308.cnf
  # 例如：
  [mysqld]
  user=mysql
  port=3306
  server_id=6
  basedir=/data/apps/mysql/
  datadir=/data/apps/mysql/data/mysql3306/
  socket=/tmp/mysql3306.sock
  log_error=/data/apps/mysql/logs/err3306.log
  
  
  # 初始化每个数据库实例
  mysqld --initialize-insecure --user=mysql --basedir=/data/apps/mysql/ --datadir=/data/apps/mysql/data/mysql3306 && \
  mysqld --initialize-insecure --user=mysql --basedir=/data/apps/mysql/ --datadir=/data/apps/mysql/data/mysql3307 && \
  mysqld --initialize-insecure --user=mysql --basedir=/data/apps/mysql/ --datadir=/data/apps/mysql/data/mysql3308
  
  # 复制systemd启动文件，更名并修改其中配置文件参数路径
  # --defaults-file=/data/apps/mysql/conf/my3306.cnf
  cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3306.service && \
  cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3307.service && \
  cp /etc/systemd/system/mysqld.service /etc/systemd/system/mysqld3308.service
  
  # systemd重载配置
  systemctl daemon-reload
  
  # 授权目录文件
  chown -R mysql.mysql /data/apps/mysql/
  
  # 最后启动即可
  systemctl start mysqld-3306 && \
  systemctl start mysqld-3307 && \
  systemctl start mysqld-3308
  
  # 验证多实例
  mysql -S /tmp/mysql3306.sock -e "select @@server_id"
  mysql -S /tmp/mysql3307.sock -e "select @@server_id"
  mysql -S /tmp/mysql3308.sock -e "select @@server_id"