MySQL笔记1

一、MySQL的特性

（1）开发与兼容性：用 C 和 C++ 编写，经多编译器测试，保障源代码跨平台可移植，适配 AIX、Linux、Windows 等众多操作系统 。
 
（2）语言与交互：为 C、Java、Python 等多种编程语言提供 API ；支持多线程，能高效利用 CPU ，优化 SQL 查询算法提升速度 。
 
（3）部署与集成：可独立作为客户端 - 服务器模式应用，也能嵌入其他软件；支持多语言编码（如 GB2312、BIG5 等）用于表名、列名 。
 
（4）连接与管理：提供 TCP/IP、ODBC、JDBC 等连接方式；配套管理工具，用于数据库的管理、检查与优化 。
 
（5）数据处理能力：可应对千万级记录的大型数据库，支撑业务数据存储与操作需求 。

二、MySQL架构

1.网络连接层

（1）架构模式：MySQL是一个单进程多线程架构的数据库，通过数据库连接池处理所有客户端接入的工作。

（2）连接流程：客户端通过 “mysql -h（主机ip） -u（用户名） -p（密码）”命令，基于 TCP/IP 协议与服务端建立连接，校验用户名、密码、权限。连接建立后，采用半双工通讯；服务端为每个客户端连接分配独立线程维护，可通过“show processlist” 查看线程状态（如用户、命令、运行时长等）。

  id：线程 id，是 MySQL 内部用于标识不同连接线程的编号，便于管理和区分，比如图里的 5  和 23  ，代表两个不同的线程。

  User：执行操作的用户， event_scheduler  是 MySQL 事件调度器的专用账户，用于自动执行定时任务； root  是常见的超级用户，拥有较高权限。

  Host ：客户端连接来源，localhost  表示本地连接， localhost:63552  说明 root  用户从本地某个端口（63552 ）发起连接。

  Command ：线程执行的命令类型，Daemon  是守护进程类命令（事件调度器线程的常规状态 ）， Query  表示执行查询操作（这里 root  用户执行“show processlist”查询线程列表 ）。

  Time ：线程已运行的时间（秒）， 139336  说明 event_scheduler  线程已运行很久， 0  表示 root  线程刚启动该查询。

  State ：线程当前状态， Waiting on empty queue  是事件调度器线程等待任务的状态； init  表示 root  线程的查询操作刚初始化。

  Info ：线程执行的具体语句， NULL  说明 event_scheduler  线程无具体执行语句（因是守护进程等待任务 ），show processlist  是 root  线程执行的查询命令。

（3）数据库连接池：因线程资源珍贵，无法无限创建，连接池复用连接，避免频繁创建/销毁线程的开销。

连接池的最大线程数可以通过参数 max-connection 来控制，如果到来的客户端连接超出该值时，新到来的连接都会被拒绝，关于最大连接数的一些命令主要有两条：

show variables like '%max_connections%' ：查询目前DB的最大连接数。

set GLOBAL max_connections = 200 ：修改数据库的最大连接数为指定值。

MySQL本身还会对客户端的连接数进行统计，对于这点可以通过命令 show status like "Threads%" 查询

  Threads_cached：目前空闲的数据库连接数。

  Threads_connected：当前数据库存活的数据库连接数。

  Threads_created：MySQL-Server运行至今，累计创建的连接数。

  Threads_running：目前正在执行的数据库连接数。

2.数据库服务层

（1）核心定位：MySQL 大多数核心功能的承载层，负责 SQL 请求解析、语义分析、查询优化、缓存处理，以及内置函数、跨引擎功能（存储过程、触发器、视图等）的实现，是连接用户请求与底层存储的“中枢”。

（2）组件与功能拆解：作为客户端与数据库交互的“出入口”， 接收客户端各类 SQL 命令（DML、DDL、DQL、DCL、TCL ），分发至其他组件执行；收集执行结果，返回给客户端。

（3）SQL 语句分类：
 
  DML：操作数据（ update / delete / insert  ）；
 
  DDL：定义数据库结构（ create / alter / drop  ）；
 
  DQL：查询数据（ select  ）；
 
  DCL：控制权限（ grant / revoke  ）；
 
  TCL：管理事务（ commit / rollback  ）

（4）解析器：验证与装换SQL语句。执行词法分析（识别 SQL 关键字、表名、字段等）、语义分析（校验语句逻辑合法性，如字段是否存在）；生成 语法树，将 SQL 转换为 MySQL 可执行的“机器码指令”。

（5）优化器：生成最优执行计划，提升查询效率。分析表、索引、数据分布，选择 最优索引、表连接顺序（如  join  方式 ）；输出一套执行成本最低的方案，交由连接线程执行 SQL。

（6）缓存和缓冲区：

读取缓存：以 select  语句缓存为核心（也含权限、引擎缓存 ），存储高频查询结果；若相同 SQL 再次执行，直接从内存命中结果，避免重复磁盘 IO；

 注意：高版本 MySQL 移除查询缓存（命中率低 + 维护开销大，一般用 Redis 替代 ）。

写入缓存区：用内存速度弥补磁盘 IO 短板，提升写操作性能；写操作时，先修改内存缓冲区的“数据页”，立即返回客户端成功；后台通过 checkpoint  机制，异步刷盘持久化；可减少磁盘 IO 次数，缓解数据库性能瓶颈。

3.存储引擎层

（1）核心定位：MySQL 架构中数据操作的“执行层”，承接服务层的逻辑指令，负责具体数据读写、磁盘交互，以及库表管理、索引管理等与存储相关的工作，是 MySQL 与底层存储深度交互的关键层级。

（2）核心特性（可插拔式设计）：

  开源驱动多样性：与 Oracle、SQL Server（闭源，仅官方单一引擎 ）不同，MySQL 因开源特性，支持多种存储引擎共存（如 InnoDB、MyISAM 等 ）。

  灵活适配业务：引擎层设计为可插拔架构，可根据业务需求（如事务支持、读写性能、数据持久化要求 ），为数据库选择不同引擎，灵活应对场景差异。

（3）常见引擎与能力：MySQL 提供多种存储引擎，最核心的是 InnoDB（支持事务、行级锁、外键，适合高并发、需数据完整性的业务，如电商订单）和 MyISAM（不支持事务，采用表级锁，查询性能好、空间占用小，适合读多写少场景，如日志系统），可通过  show variables like '%storage_engine%'  查看当前引擎。其他还包括 NDB、Archive、Federated、Memory （数据存内存，读写极快但易失，适合临时缓存，如会话数据）等，覆盖不同场景：

（4）职责范围：

数据读写：直接操作磁盘文件，实现数据的持久化存储与读取。

辅助管理：涵盖库表结构维护（如创建/删除表 ）、索引构建与优化（加速查询 ），所有与磁盘打交道的工作，最终由存储引擎承载。

4.文件系统层

（1）核心定位:作为 MySQL 架构的底层支撑，基于物理磁盘文件系统（兼容 NTFS、UFS 等），承载配置文件、库表结构、数据、索引、日志等各类文件，与存储引擎交互，完成数据最终存储与持久化 。

（2）7类核心日志：

（3）数据板块（各类数据文件）：

5.架构小结