0 MySQL的目录文件
[root@test01 ~]# find / -name mysql
- 数据库文件存放路径：
- /var/lib/mysql/
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.04 sec)
-
相关命令目录：
- /usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）
- /usr/sbin。
-
配置文件目录：
- /usr/share/mysql-8.0（命令及配置文件）
- /etc/mysql（如my.cnf）
查看默认数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_database |
+--------------------+
5 rows in set (0.00 sec)
- mysql
- MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
- information_schema
- 保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。
- performance_schema
- 主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
- sys
- 主要是通过 视图 的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。
计算机上数据目录下的内容：
[root@xgms_VM-8-13-centos ~]# cd /var/lib/mysql/
[root@xgms_VM-8-13-centos mysql]# ll
total 188880
-rw-r----- 1 mysql mysql 56 Mar 8 14:04 auto.cnf
-rw-r----- 1 mysql mysql 498 Mar 8 14:35 binlog.000001
-rw-r----- 1 mysql mysql 1845 Mar 11 17:13 binlog.000002
-rw-r----- 1 mysql mysql 32 Mar 8 14:35 binlog.index
-rw------- 1 mysql mysql 1680 Mar 8 14:04 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 8 14:04 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 8 14:04 client-cert.pem
-rw------- 1 mysql mysql 1680 Mar 8 14:04 client-key.pem
-rw-r----- 1 mysql mysql 196608 Mar 11 17:15 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql 8585216 Mar 8 14:04 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql 3507 Mar 8 14:35 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Mar 11 17:14 ibdata1
-rw-r----- 1 mysql mysql 50331648 Mar 11 17:15 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Mar 8 14:04 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Mar 8 14:35 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Mar 8 14:35 #innodb_temp
drwxr-x--- 2 mysql mysql 4096 Mar 8 14:04 mysql
-rw-r----- 1 mysql mysql 25165824 Mar 11 17:13 mysql.ibd
srwxrwxrwx 1 mysql mysql 0 Mar 8 14:35 mysql.sock
-rw------- 1 mysql mysql 5 Mar 8 14:35 mysql.sock.lock
drwxr-x--- 2 mysql mysql 4096 Mar 8 14:04 performance_schema
-rw------- 1 mysql mysql 1680 Mar 8 14:04 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Mar 8 14:04 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Mar 8 14:04 server-cert.pem
-rw------- 1 mysql mysql 1676 Mar 8 14:04 server-key.pem
drwxr-x--- 2 mysql mysql 4096 Mar 8 14:04 sys
drwxr-x--- 2 mysql mysql 4096 Mar 11 17:13 test_database
-rw-r----- 1 mysql mysql 16777216 Mar 11 17:15 undo_001
-rw-r----- 1 mysql mysql 16777216 Mar 11 17:13 undo_002
# 进入测试数据库看看
[root@xgms_VM-8-13-centos mysql]# cd test_database/
[root@xgms_VM-8-13-centos test_database]# ll
total 80
-rw-r----- 1 mysql mysql 114688 Mar 11 17:13 test_data.ibd
1 逻辑架构剖析
1.1 SQL服务器如何处理客户端请求？
这里以查询请求为例子
具体展开看如图
如图主要过程包括
- 从客户端程序获取sql请求（查询插入等）
- 从连接池中获取并建立连接
- SQL接口接收sql指令
- 从查询缓存中查找是否之前有同样的查询操作，有则直接跳到10
- 解析器对sql指令进行语义解析，形成语法树
- 优化器对查询过程进行优化（提高操作效率）
- 经由搜索引擎与底层文件进行交互
- 从文件系统中获取需要的数据
- 将数据加载进缓存
- 调用sql接口返回数据
这里用到的缓存组件因为命中率过低，在效率提升上意义不大
从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。
中间部分的分层情况
1.2 第1层：连接层
系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
- 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
- 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
连接池(Connection Pool):TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。这一操作交给线程池去做
1.3 第2层：服务层
-
SQL Interface: SQL接口
- 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQLInterface
- MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
-
Caches & Buffers： 查询缓存组件
- MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。
- 这个查询缓存可以在 不同客户端之间共享 。
- 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。
-
Parser: 解析器
- 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
- 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树 ，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。
-
Optimizer: 查询优化器
- SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径。比如是根据全表检索，还是根据索引检索。具体可以分为逻辑查询优化阶段和物理查询优化阶段。
- 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
- 它使用“ 选取-投影-连接 ”策略进行查询。例如：
SELECT id,name FROM student WHERE gender = '女';
这个SELECT查询先根据WHERE语句进行 选取 ，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过滤，将这两个查询条件 连接 起来生成最终查询结果。
1.4 第3层：引擎层
插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
MySQL 8.0.25默认支持的存储引擎如下：
小结
简化为三层结构：
- 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
- SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
- 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
2 SQL执行流程
SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器 。
涉及的所有操作都是默认在Linux，下使用MySQL8.0服务，登录root用户后进行的
执行如下命令后输入密码 进入到mysql命令行
mysql -uroot -p
2.1 MySQL 8.0 执行流程（语句执行过程情况查询）
了解语句底层的执行过程，需要查看是否开启profiling，该工具可以让我们监控语句执行的每一步
确认profiling 是否开启
mysql> select @@profiling;
# 返回内容 profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
# 打开
mysql> set profiling=1;
# 或 采用如下命令查看
mysql> show variables like 'profiling';
# 返回
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
查看语句执行情况
mysql> show profiles; # 显示最近的几次查询
#返回
+----------+------------+----------------+
| Query_ID | Duration | Query |
+----------+------------+----------------+
| 1 | 0.00007325 | show profilies |
+----------+------------+----------------+
1 row in set, 1 warning (0.00 sec)
# 查询具体指定的QuerID
mysql> show profile for query 7;
# 查看执行步骤
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000071 |
| Executing hook on transaction | 0.000007 |
| starting | 0.000011 |
| checking permissions | 0.000009 | # 权限检查
| Opening tables | 0.000884 | # 打开表
| init | 0.000012 | # 初始化
| System lock | 0.000007 | # 锁系统
| optimizing | 0.000005 | # 优化查询
| statistics | 0.000012 |
| preparing | 0.000013 | # 准备
| executing | 0.000016 | # 执行
| end | 0.000004 |
| query end | 0.000003 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000008 |
| freeing items | 0.000020 |
| cleaning up | 0.000013 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
# 查询具体更丰富的内容
mysql> show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000078 | 0.000069 | 0.000000 | 0 | 0 |
| Executing hook on transaction | 0.000007 | 0.000005 | 0.000000 | 0 | 0 |
| starting | 0.000011 | 0.000012 | 0.000000 | 0 | 0 |
| checking permissions | 0.000008 | 0.000008 | 0.000000 | 0 | 0 |
| Opening tables | 0.000037 | 0.000037 | 0.000000 | 0 | 0 |
| init | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| System lock | 0.000011 | 0.000010 | 0.000000 | 0 | 0 |
| optimizing | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| statistics | 0.000016 | 0.000015 | 0.000000 | 0 | 0 |
| preparing | 0.000020 | 0.000020 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000034 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
| query end | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
| waiting for handler commit | 0.000010 | 0.000010 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
| freeing items | 0.000014 | 0.000014 | 0.000000 | 0 | 0 |
| cleaning up | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
2.2 MySQL 5.7 执行流程
需要 显式开启查询缓存模式
1. 配置文件中开启查询缓存
在 /etc/my.cnf 中新增一行：
query_cache_type=1
2. 重启mysql服务
systemctl restart mysqld
3. 开启查询执行计划
mysql> set profiling=1;
然后执行两次相同的查询，再使用与8.0相同的命令查询执行过程；
能够看到第二次查询比第一次查询少了很多步骤，这是因为直接从查询缓存中抓取到了数据。
SQL语句执行顺序
2.3 Oracle 中sql的执行流程（了解）
3 数据库缓冲池(buffer pool)
缓冲池是从内存分出来的一小块
InnoDB以页为单位管理存储空间，对页处理需要IO操作，IO操作很耗时间。缓冲池是为了将页提前缓冲到内存，让减少磁盘的IO操作而分出的一部分内存，会优先对使用频次高的热数据进行加载 。
缓冲池与查询缓存的区别
查询缓存：
存的是某一次查询的具体结果，以语句为key，结果为value
缓冲池：
存的是比较常用的具体数据页，以是以数据存储单位为内容的相对来说，查询缓存更局限，命中率更低
3.1 缓冲池结构
3.2 读取数据过程
如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？
答：不会，会攒够一定量的更新，再与磁盘进行交互。
3.3 查看/设置缓冲池大小
# 查看命令 单位：字节 使用的是InnoDB
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
# 修改命令 修改为256MB
set global innodb_buffer_pool_size = 268435456;
# 或者配置文修改
[server]
innodb_buffer_pool_size = 268435456
3.4 多个Buffer Pool实例
# 配置文件中修改
[server]
innodb_buffer_pool_instances = 2
# 查看缓冲池个数
show variables like 'innodb_buffer_pool_instances';
# 返回
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)
总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。
InnoDB规定，总大小小于1GB时，实例个数都默认为1
4 存储引擎
存储引擎之前又叫表处理器，其实就是表的存储类型
实际上就是数据的存储格式的不同
4.1 存储引擎相关命令
查看存储引擎
>mysql show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
show engines \G; //每一行单独显示
设置系统默认的存储引擎
- 查看默认存储引擎
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
# 修改默认存储引擎
SET DEFAULT_STORAGE_ENGINE=MyISAM;
或者修改my.cnf文件
default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service
设置与修改表的存储引擎
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称;
4.2 存储引擎介绍与对比
最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存储引擎。
InnoDB：支持外键的事务存储引擎
- MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。
- 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
文件结构
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
- 表名.ibd 存储数据和索引
InnoDB 数据存储有两种方式
- 独立表空间(file-per-table tablespace)（即上述的文件结构）
- 系统表空间（system tablespace）
- 默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。文件是 自扩展文件 ，不够时会自己增加文件大小。
想增加多个文件或修改文件名和大小，需要修改my.cng配置文件
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
自行指定使用系统表还是独立表，需要启动时这一配置
[server]
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间
# 默认情况
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
基于该结构，InnoDB最大的特点就是：索引即数据
MyISAM 引擎：主要的非事务处理存储引擎
- MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
- 5.5之前默认的存储引擎。优势在于访问速度块
文件结构
- 表名.frm 存储表结构
- MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。
- MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等
- 表名.MYD (MYData) 存储数据,存储数据信息(如果采用独立表存储模式)
- 表名.MYI 存储索引 (MYIndex)
Archive 引擎：用于数据存档
-
archive 就是归档的意思，仅仅支持插入和查询两种功能。MySQL5.5以后支持索引功能
-
有良好的压缩功能，同样数据量占存储更小
-
适合存储大量的独立的作为历史记录的数据
Blackhole 引擎：丢弃写操作，读操作会返回空内容
CSV 引擎：存储数据时，以逗号分隔各个数据项
文件结构:表名.csv
Memory 引擎：置于内存的表
- 因为存于内存，响应速度很快，但断电数据会丢失，同时要求存储的数据长度不变
- 同时支持哈希索引和B+树索引
- 大小受限，数据与索引文件分开存储
Federated 引擎：访问远程表
Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。
Merge引擎：管理多个MyISAM表构成的表集合
NDB引擎：MySQL集群专用存储引擎
也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集群。
引擎对比
| 特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 有 | 64TB | 有 | 没有 | 有 |
| 事务安全性 | 支持 | ||||
| 锁机制 | 表锁，操作一条数据也会锁整个表 | 行锁，适合高并发 | 表锁 | 表锁 | 行锁 |
| B+树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 只缓存索引，不缓存数据 | 索引与数据都要缓存 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中等 | 低 | 高 |
| 批量插入速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外键 | 支持 |
MyISAM和InnoDB对比
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 自带系统表使用 | Y | N |
| 关注点 | 性能：节省资源、消耗少、简单业务 | 事务：并发写、事务、更大资源 |
| 默认安装 | Y | Y |
| 默认使用 | N | Y |
1792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
