MySQL架构 InnoDB存储引擎

1. 什么是Mysql？

  • 我们在开发的时候，我们都需要对业务数据进行存储，这个时候，你们就会用到MySQL、Oracal等数据库。
  • MySQL它是一个关系型数据库，这种关系型数据库就有Oracal、 MySQL，以及最近很火的PgSQL等。

那什么是关系型数据库呢？

  • 就是它是基于我们的SQL语句去执行操作的。
  • 其实就是关系表数据库，由表结构来存储数据与数据之间的关系，同时用SQL（Structured query language）结构化查询语句来进行数据操作。
  • 关系型数据库，对应会有一个非关系型数据库，像我们用的比较多的 Redis 这种Key - Value结构数据存储、hbase这种列存储格式、MongoDB这种文档存储等等。

那么关系型数据库相比非关系型数据的区别： 

  1. 关系型数据库都是用表来进行维护，所以格式一致，可以统一用SQL语言来进行操作
  2. 关系型数据库都是表结构，所以灵活度不够，操作复杂的海量数据性能比较差，所以我们才会有表结构、索引以及索引优化。
  3. 虽然性能可能会比较慢，但是能做复杂的关联查询操作。 比如一对一， 一对多，多对多等等。
官网介绍： https://www.oracle.com/mysql/what-is-mysql/

MySQL 的优势：

  1. 易用性：开发者可以在几分钟内安装好MySQL，数据库易于管理。
  2. 可靠性： MySQL 是最成熟、使用最广泛的数据库之一。超过 25 年，它已经在各种场景中进行了测试，其中包括许多世界上最大的公司。由于MySQL 的可靠性，组织依赖 MySQL 来运行关键业务应用程序。
  3. 可扩展性： MySQL 可扩展以满足最常访问的应用程序的需求。MySQL 的本机复制架构使 Facebook 等组织能够扩展应用程序以支持数十亿用户。
  4. 高性能： MySQL HeatWave比其他数据库服务更快且成本更低，多项标准行业基准测试证明了这一点，包括 TPC-H、TPC-DS 和 CH-benCHmark。
  5. 高可用性： MySQL 为高可用性和灾难恢复提供了一套完整的本机、完全集成的复制技术。对于关键业务应用程序，并满足服务级别协议承诺，客户可以实现 零数据丢失以及秒级的故障转移恢复。
  6. 安全性： 数据安全需要保护和遵守行业和政府法规，包括欧盟通用数据保护条例、支付卡行业数据安全标准、健康保险可移植性和责任法案以及国防信息系统局的安全技术实施指南。MySQL 企业版提供高级安全功能，包括身份验证/授权、透明数据加密、审计、数据屏蔽和数据库防火墙。
  7. 灵活性： MySQL 文档存储为用户开发传统 SQL 和 NoSQL 无模式数据库应用程序提供了最大的灵活性。开发人员可以在同一个数据库和应用程序中混合和匹配关系数据和 JSON 文档。
Mysql服务安装
  • 见官网：https://dev.mysql.com/doc/refman/8.0/en/installing.html
Mysql连接或者断开服务器 
  • 官网：https://dev.mysql.com/doc/refman/8.0/en/connecting-disconnecting.html
Mysql里面的基本库表信息
库基本操作
  • 我们叫它数据库，数据库，所以，在表的前面还有库的概念，操作查询库信息，这些基本操作就不演示了。
  • 官网：https://dev.mysql.com/doc/refman/8.0/en/database-use.html

查询所有的库：

 

创建库： 

查询当前选择的库：

 

表基本操作
  • 官网：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement

系统库表 

  • 我们发现除了我们自己创建的库以外，还有很多系统的库、以及表来保证MySQL的系统运行。

官网：MySQL ：： MySQL 8.0 参考手册 ：： 5.3 mysql 系统架构 

三个系统库： 

  1. mysql库：这一个系统库是来保证我的数据库服务正常运行的一个系统表全部在这个库里面。 
  2. information_schema库：包括InnoDB里面的数据、日志、事务、表；还有PROCESSLIST表：它记录了当前正在运行的数据库有多少个连接和查询进程的信息；ENGINES表：存储引擎插件表。
  3. performance_schema库 - 性能库：这个库里面所有的数据是当前只存在内存里面的，这个库下面所有的事件、锁(比如data_locks表) - 我会去锁哪些数据。这些数据它只针对我当前的服务，如果重启了，所有的数据会丢失，所以这个它只存在当前内存。
mysql系统库下几个重要的表：
  1. 数据字典表（Data Dictionary Tables）
  2. 授权表（Grant System Tables）
  3. 对象信息表（Object Information System Tables）：plugin 插件注册表 等待
  4. 日志系统表(Log System Tables)：
  • general_log：一般查询日志表。
  • slow_log：慢查询日志表。
日志配置：
show variables like 'general_log'; //一般查询日志，默认关闭
SELECT @@long_query_time;
show global variables like 'long_query_time';
show global variables like 'min_examined_row_limit'; //至少需要检索这么多行
show global variables like 'slow_query_log'; //是否开启慢日志查询 默认关闭
SET GLOBAL slow_query_log=1;
set global long_query_time=0.1; //超过100毫秒
log_output=table |file |none //设置是放在文件中，还是在mysql.slow_log表中

Sql语句的执行流程

  • 客户端发送一条语句给到服务器，然后服务器它能给你一个它的数据。

一. 连接器 - 跟MySQL服务器建立连接

  • 只有建立连接以后，我才能够发送SQL语句给到MySQL服务器，MySQL服务器它才能够去进行接收，这样才能进行网络IO。
  • 连接的过程需要先经过TCP三次握手，因为MySQL是基于TCP协议进行传输的，如果MySQL服务并没有启动，则连接会报错；如果MySQL服务正常运行，完成TCP连接的建立后，此时连接器就要开始验证你的用户名和密码，如果用户名或密码不对，就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
  • 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

连接管理
  • 首先，我们得有连接，那么Mysql里面就有一个连接层，来管理连接，我们看下跟连接有关的变量/参数
  • 变量：随着我的服务的运行，它会变更的，这些变量会随着我的客户端连接的变多而变多。 

MySQL的四个线程状态变量：MySQL ：： MySQL 8.0 参考手册 ：： 5.1.6 服务器状态变量参考

  1. Thread_cached：是MySQL的一个状态变量，表示MySQL线程缓存中当前缓存的线程数，是为了我的服务，不用每一次客户端建立连接的时候都去创建一个线程，以此减少线程的创建和销毁的开销，提高数据库性能，所以它有一个缓存的线程数。当一个客户端连接到MySQL数据库服务器时，服务器会为该连接创建一个线程来处理客户端的请求。线程缓存的作用就是在该连接请求结束后，将这些线程缓存在内存中，以便下次有新的连接请求时能够复用这些线程，而不需要重新创建。thread_cache_size是MySQL的一个静态配置参数，用来配置线程缓存的大小，默认是-1，需要手动调整(在MySQL配置文件当中配置)并重启MySQL服务才能生效，最大是16384。
  2. Thread_connected：我当前打开的线程数，就是我现在有多少个线程是打开的。
  3. Thread_created：总共创建的线程数，即创建的线程总数。创建的线程总数越多，我们的thread_cache_size 可以对应的更大，来提升线程的缓存命中率。
  4. Threads_running：正在运行的线程数
-- 查看MySQL的四个Thread线程状态变量
show status like 'Thread%';

-- 查询thread_cache_size系统变量
select @@thread_cache_size;

查看MySQL服务被多少个客户端连接 /  查看当前正在运行的线程：
-- 显示当前正在运行的线程
show full PROCESSLIST;
该查询将返回一个结果集，包含所有当前连接的信息。每一行代表一个连接，每个连接的信息包括

  • id：线程 ID

  • State：连接状态

  • User：操作的用户名

  • Host：主机 / IP

  • db：操作的数据库

  • command：当前连接执行的命令：Sleep-休眠、Query-查询

  • Time：这个状态持续的时间，单位是s

  • info - 信息：查询会有查询的信息，但是长度有限制，可能不全

删除阻塞线程

  • 官网：https://dev.mysql.com/doc/refman/8.0/en/kill.html

使用PROCESSLIST表可以查看当前正在运行的连接-线程，假如当前有客户端连接已经阻塞了，那么此时你可以去把它KILL掉：

demo
1. 会话一： 
SELECT * FROM product_new --表中有500W数据，查询很慢

2. 会话二：

SHOW PROCESSLIST; -- 查看当前线程

执行结果：

3. 关闭查询query：

​​​​​​​KILL QUERY 9328; -- 终止查询 
查看会话一的结果：[Err] - Query execution was interrupted 请求被中断

4. 杀死 / 关闭连接线程 - Connection Thread  

KILL 2280; -- kill 连接线程

查看会话一的结果：

  • [Err] - Lost connection to MySQL server during query - 丢失连接Connection 

其他连接相关配置参数：
show status like 'Max_used_connections%';
 
  • Max_used_connections 自服务启动以来最大的连接数
  • Max_used_connections_time 达到这个峰值的时间
几个全局系统变量： 
-- 查询最大连接数 默认151
SELECT @@max_connections;
select @@GLOBAL.max_connections;-- 手动设置最大连接数
SET @@GLOBAL.max_connections = 1000;-- 查询服务器超时等待时间  默认28800s - 8h
SELECT @@wait_timeout;
select @@GLOBAL.wait_timeout;-- 手动设置服务器最大等待时间
SET @@GLOBAL.wait_timeout = 1000;
  • max_connections：最大的连接数，即我的服务最多能开启的连接数，超过该值不允许建立连接，默认151，最小1，最大 100000。如果开启太大，同时会有很多的客户端来进行连接操作，MySQL性能可能会跟不上；如果开启太小，可能在高并发场景下导致并发量上不来。超过默认值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
  • wait_timeout：空闲连接最大的等待时间 / 服务器超时等待时间 / 非交互连接等待的时间(单位s)，默认28800s，也就是8小时，用于指定一个连接在空闲状态下的最长等待时间。如果一个连接在8小时内没有进行任何操作，那么MySQL服务器会自动关闭该连接，以释放资源。wait_timeout  => 服务器什么时候会自动关闭?

跟服务器建立完连接之后，此时就代表客户端能跟服务端去进行通信了，也就是客户端能向服务端去发送SQL语句的请求了。 

MySQL Server中的第一层 - 网络连接层

-- MySQL 8.0版本以后就已经移除掉了缓存模块：查询缓存 -- 

  • 连接器的工作完成后，客户端就可以向 MySQL 服务器发送 SQL 语句了，MySQL 服务器收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。
  • 如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。 
  • 如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

  • 这么看，查询缓存还挺有用，但是其实查询缓存挺鸡肋的。

  • 对于更新比较频繁的表，查询缓存的命中率是很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

  • 所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

二. 解析器 - 解析SQL

  • 当建立连接后，客户端向服务端去发送SQL语句请求时需要解析SQL语句，会把一条SQL语句解析生成语法树；因为MySQL不是人，不会一眼看到SQL语句就知道要做什么事情。
  • 所以它会借用解析器去把SQL语句解析出来，看是否符合我们的SQL语法，最终生成一个语法树(理解为一个数据结构)。
解析器分为词法解析跟语法解析！
词法解析(器)
  • 将SQL语句打碎，转化成一个一个关键单词 => 然后交给语法解析(器)去构建语法树，判断语法是否正确
语法解析(器)  
  • 语法解析已经知道每个SQL语句的单词了，那么在语法解析的时候，会去判断 / 检查语法是否正确，比如，where是不是写出where1，from写成from1；
  • 表名、字段名是否存在、用户是否有操作权限等等。
  • 如果发现SQL语法错误，则MySQL直接抛出相应的错误信息，并拒绝执行该SQL语句 => You have an error in your SQL syntax；
  • 否则会去解析构建出SQL语法树，以便后面模块获取SQL语句类型、表名、字段名、where条件等等。
总结：
  • 先将输入的SQL语句解析为语法树，然后对语法树进行语法检查，这样可以确保在执行之前先判断SQL语句是否符合MySQL的语法规则，避免执行无效或错误的语句。 
  • 注意：表名不存在或者字段名不存在，并不是在解析器里做的，解析器只负责检查语法和构建语法树，但是不会去查表名或字段名是否存在。

三. 预处理器 / 预编译器（可做可不做）  

预处理 / 预编译的两个作用： 
  1. 提升性能
  2. 防止SQL注入，更安全 

MyBatis中的SQL注入是MyBatis去做的参数化，而这里的SQL注入是我们的MySQL服务器自己能支持的，预处理器它是我们的MySQL服务能支撑的。 

什么是预处理？
  • 以我们的工作场景为例，一个查询接口，SQL语句都是一样的，但是每次查询的参数都不一样，所以我们想只需要变更参数部分就行。
  • 那么，我们就是在拼接SQL语句的时候，将用户的输入跟语句拼接成一个SQL语句给到MySQL执行。
  • 但是会发生一个SQL注入问题！
什么是SQL注入？
  • 因为参数是客户端传过来的，所以可以传任何值，那么就有可能传入任何值就有了SQL注入问题。
-- 要执行的SQL语句
select * from emp where password = '';-- SQL注入演示-客户端传入查询的参数为: ' or '1' = '1
select * from emp where password = '' or '1' = '1';
SQL注入是因为客户端拼接用户传入的参数，然后拼接好语句给到MySQL，这样会导致会安全问题。
那么能不能把这个参数化的事情交给MySQL自己做呢？
  • 当然可以，这个就是预处理。
  • 如果你需要参数化，你只要告诉MySQL，传一个预处理语句就行，MySQL会将参数与语句编译分开。
预处理操作解决SQL注入
官网地址： MySQL :: MySQL 8.0 Reference Manual :: 13.5 Prepared Statements
预处理语句的工作流程 / 预处理语句为什么能够防止SQL注入？
  1. 首先在应用程序中，应该去创建一个预处理语句，将SQL查询与占位符(通常使用问号?来表示)组合起来，形成一个带有参数占位符的SQL查询语句。
  2. 接着，应用程序将这个预处理语句发送到MySQL服务器进行编译。服务器会对SQL语句进行语法解析和执行计划生成，但不会执行实际的查询。
  3. 在执行查询之前，应用程序通过绑定参数的方式将实际的参数值与占位符关联起来，这样可以防止SQL注入攻击，因为用户输入的参数不会直接嵌入到查询语句中，而是作为参数传递给服务器，并允许在多次执行中重复使用预处理语句(因为预处理语句只需编译一次，再次执行时就无需重新编译，以此提高性能)。
  4. 一旦参数绑定完成，应用程序可以多次执行相同的预处理语句，通过更改参数值来获取不同的结果。
预处理Demo，防止SQL注入： 
-- 创建预处理语句
-- PREPARE 预处理名字 from 'SQL语句';
PREPARE select_user from 'select * from emp where password = ?'-- 绑定参数(设置参数值)
SET @passsword = '123456';-- 执行预处理语句
-- EXECUTE 预处理名字 USING @绑定参数名;
EXECUTE select_user USING @passsword;-- 清除预处理语句
-- DEALLOCATE PREPARE 预处理名字;
DEALLOCATE PREPARE select_user;
预处理主要做主要做以下2个事情：
  1. 将语句编译、优化跟参数分开处理，当执行SQL语句相同，参数不同的场景，提升性能。
  2. 因为是参数化去执行的，而不是拼接参数，从而解决了SQL注入问题。
比如我们经常被问的Mybatis里面#跟$符号的区别：
  • #符号执行SQL时，会将#{...}替换成？，生成预编译SQL / 预处理SQL，然后进行预处理，能防止SQL注入，并且必须传入参数；
  • $符号会拼接SQL，直接将${...}参数拼接在SQL语句中，存在SQL注入问题。

四. 优化器 - 决定怎么做 => 生成执行计划，确定执行方案

  • 根据上面的流程，我们知道要去执行什么语句，但是具体怎么执行会有很多的方式，比如走哪个索引，要不要回表，要不要去在内存里面排序，你的语句是不是可以优化等等。
做哪些优化，有哪些优化器，通过全局变量 optimizer_switch 来决定， 控制优化器策略的一种方法是设置 optimizer_switch 系统变量。
具体参数如下： 
-- 获取MySQL数据库中的全局优化器开关配置信息
SELECT @@GLOBAL.optimizer_switch;
  • 优化器说明：https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html，里面包含了每个优化选项说明.
  • 优化方式官网地址：https://dev.mysql.com/doc/refman/8.0/en/optimization.html；这个里面就有很多优化器的实践，比如优化sql语句等等。
  • 优化器它是基于服务，觉得自己最快的一些方式去执行这个语句，优化后会生成一个最优的执行计划，并将该执行计划传递给存储引擎层，所以这个语句到底怎么走，优化器来决定。
  • 它是基于内存与CPU或性能的消耗得到一个算法或者说得到哪一个执行计划它是最快的或查询成本最小的。
  • 当前，优化器里面有一些东西它是可以自己设置的，比如说要不要索引下推，比如说要不要回表，比如说要不要去用联合索引，要不要用hash_join，要不要用跳跃扫描skip_scan，要不要用mrr，mrr是它底层的一个算法，能够更快的跟我的磁盘去进行交互等等。 
  • 优化器负责将 SQL 查询语句的执行方案确定下来！
  • 要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引，比如下图的 key 为 PRIMARY 就是使用了主键索引。
  • 如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的。
MySQL  Server中的第二层 - 核心服务层，核心服务层不牵扯到数据的存储与查询。

五. 执行器 - 去操作数据的

  • 执行器根据执行计划，去调用数据存储的地方，也就来到了我们MySQL Server中的第三层 - 存储引擎层！
  • 刚刚讲了MySQL的很多插件 ， 但是还没有真正的去跟数据交互 ，也就是没有去查询数据。
  • 数据是放在我们的MySQL Server中的存储引擎层。
  • 存储引擎层是真正的跟数据进行交互的，是真正的来保存以及怎么去查询数据的，所以存储引擎层决定了我这个数据以什么样子的方式来保存，比如说你是保存到磁盘，还是保存到内存，还是磁盘跟内存都有。 
  • 执行器去根据表设置的存储引擎，调用不同存储引擎的API接口获取数据。 
  • 至于这个数据是怎么存的，这个数据有哪些优化（比如内存去缓存）等等，就是每个存储引擎自己去做的事情，也就来到了我们MySQL Server中的第三层中的存储引擎层，并且存储引擎是跟MySQL解耦的，存储引擎跟MySQL的开发者都不是同一批人。
  • 存储引擎它是我们MySQL的一个插件，你如果有能力，MySQL都支持你自己写存储引擎。
  • 基于不同的一些场景，比如说有一些场景我要去保证性能，有一些场景我要去保证一致性，所以它会有不同的存储方案，这里就牵扯到我们不同的存储引擎。
  • MySQL支持不同的存储引擎，这些存储引擎决定了我们数据的存储方式，以及数据的可靠性、一致性、持久性、原子性。也就是我们经常讲的ACID。
那么官网提供了哪些存储引擎？
  • 官网：MySQL :: MySQL 8.0 Reference Manual :: 16 Alternative Storage Engines

可以通过语句查询当前服务器支持哪些存储引擎：  

SHOW ENGINES; -- 查询当前服务器支持的存储引擎

InnoDB
  • MySQL 8.0默认的存储引擎。InnoDB是一个事务安全(兼容ACID) 的MySQL存储引擎，具有提交、回滚和崩溃恢复功能，以保护用户数据。
  • InnoDB支持行级别的锁(没有升级到更粗粒度的锁)和Oracle风格一致的非锁读取提高了多用户并发性和性能。
  • InnoDB将用户数据存储在聚集索引中，以减少常见的基于主键的查询的I/O。
  • 为了维护数据的完整性，InnoDB还支持外键引用完整性约束。
MyISAM
  • 这些表占用空间很小。
  • 表级锁限制了读/写工作负载的性能，因此它经常用于Web和数据仓库配置中的只读或以读为主的工作负载中，所以它的性能要比InnoDB要高。
Memory
  • Memory它只是把数据存储在内存，它不会做持久化。 

不同的存储引擎会有自己不同的存储实现方式 / 存储方案，不管是什么存储引擎，它一定要做的事情是把这个数据保存起来，是用内存还是磁盘，还是都用，或者磁盘的文件格式等等都会不一样。

  • 既然要保存，那么就一定要有个数据的目录，这个目录，也就是我们的一个变量，这个变量就是代表你保存到哪里。
接下来我们看下数据到底存储在哪里，以什么样的方式存储？

六. 存储层 - 数据存储地址 

SQL语句查询：

-- 查询数据库的数据目录
select @@datadir;
show variables like '%datadir%';

该目录就是我们的数据库的数据目录，我们的数据保存在该目录下。  

不同的存储引擎，存储的文件以及格式都不一样，我们今天来重点分析下InnoDB的内存以及磁盘结构。

·································································································································

InnoDB存储引擎

InnoDB 的主要优势

  • 它的 DML 操作遵循 ACID 模型(原子性、一致性、隔离性、持久性)，事务具有提交、回滚和崩溃恢复功能以保护用户数据。请参阅MySQL ：： MySQL 8.0 参考手册 ：： 15.2 InnoDB 和 ACID 模型”。
  • 行级锁定和 Oracle 风格的一致性读取提高了多用户并发性和性能。请参阅MySQL ：： MySQL 8.0 参考手册 ：： 15.7 InnoDB 锁定和事务模型。
  • InnoDB 表将您的数据排列在磁盘上以优化基于主键的查询。每个InnoDB 表都有一个称为聚簇索引的主键索引，它组织数据以最小化主键查找的 I/O。
  • 为了保持数据完整性，InnoDB 支持 FOREIGN KEY外键约束。对于外键，检查插入、更新和删除以确保它们不会导致相关表之间的不一致。请参阅MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.5 FOREIGN KEY Constraints。

InnoDB的架构 - 数据磁盘结构 

  • 首先，我们的 InnoDB 数据是肯定会落到我们的磁盘！文件后缀我们知道肯定是ibd。
  • ibd文件是我们默认的InnoDB的存储文件。 
那么存在什么空间？我们就有一个表空间的概念。
  • 简单一点，表空间就是存储表和索引数据的位置，就是用来管理表与索引数据的。

表空间

官网地址： MySQL :: MySQL 8.0 Reference Manual :: 15.6.3 Tablespaces
  • 表空间又分为很多的类型，比如系统表空间、通用表空间、独立表空间 等等。
  • InnoDB的存储结构：InnoDB肯定会保存到磁盘，并且默认每个表都会有一个独立的ibd文件，用专有名词就叫做表空间，表空间就是我这个数据到底怎么去保存到我的磁盘的，这一个叫做表空间。

独立表空间：

  • 准确一点儿应该叫独立表空间{现在默认开启的}：就是我每个表都有个独立的ibd数据文件，好处在于数据相对来讲它是隔离的。
  • 独立文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统中的单个数据文件中。
  • MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables
独立表空间优点：
  • 真正做到了解耦，易于管理，同时更大的利用磁盘空间。当然还有很多其它的优势。
怎么决定我的数据与索引文件放在哪个表空间？
  • 最关键的innodb_file_per_table参数，代表是存储到系统表空间，还是独立表空间，1 代表开启了独立表空间，数据文件会保存在file_per_table独立表空间下，然后以表名.ibd保存。​​​​​​​
-- 查看是否启用了每个表的独立表空间 1或者ON代表开启,0或OFF表示关闭
select @@innodb_file_per_table; -- 1
show variables like '%innodb_file_per_table%'; -- ON-- 也可以设置为0或OFF进行关闭独立表空间,进而变成了系统表空间
set global innodb_file_per_table = 0;
set global innodb_file_per_table = OFF;

系统表空间：

  • 我想把所有的表都放在一个文件里面 => 系统表空间：所有的表的数据都在一个文件或者说在多个指定的文件里面。
  • 系统表空间可以有1个或者多个数据文件 ， 默认情况下，在data目录中创建一个名为ibdata1的系统表空间数据文件，所有的Innodb表的文件会保存在ibdata1目录下。
  • 系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义！
  • 官网：MySQL :: MySQL 8.0 Reference Manual :: 15.14 InnoDB Startup Options and System Variables
-- 查询系统表空间配置 / 查询系统表空间数据文件的文件名和文件大小
select @@innodb_data_file_path;
SHOW VARIABLES LIKE '%innodb_data_file_path%';

  • ibdata1: 文件名
  • 12M: 默认文件大小
  • autoextend 自动扩容，扩容大小由innodb_autoextend_increment决定。
-- 查看扩容的大小，默认扩容增量为64M
SHOW VARIABLES LIKE '%innodb_autoextend_increment%';

  • 如果扩容到64M还不够，我们可以采用多个数据文件来提升系统表空间， 可以设置innodb_data_file_path，但是autoextend 只对最后一个文件生效，并且，不能对已经生成的文件的大小进行更改。
  • 为了减少系统表空间的大小，我们也可以用通用表空间，这样我可以自行设置哪些表放在哪些表空间。

通用表空间

  • 与系统表空间类似，通用表空间是能够为多个表存储数据的共享表空间，但是这个空间可以自己进行维护管理。
  • 使用场景：假如说我想把订单表的放在一起，把商品表的放在一起，这时就用到了通用表空间。
  • 通用表空间说白了就是你可以自己指定哪个表到哪个文件！但前提是你要先创建文件！
  • 官方网址：​​​​​​​ MySQL ：： MySQL 8.0 参考手册 ：： 15.6.3.3 通用表空间
  • 创建通用表空间：
    -- 创建通用表空间
CREATE TABLESPACE huihui_tablespace ADD DATAFILE '/var/lib/mysql/huihui.ibd' ENGINE = InnoDB;
    创建表到通用表空间： 
    CREATE TABLE gp_student
(studentId   INT PRIMARYKEY,studentName VARCHAR(500)
) TABLESPACE huihui_tablespaceEngine = InnoDB;
    添加数据后，我们发现数据存储在huihui.ibd通用表空间中！
    删除通用表空间： 
    -- 删除通用表空间,但是必须先保证通用表下没有表
DROP TABLESPACE huihui_tablespace;
    通用表空间的限制:
    • 同时通用表空间只能创建在已知目录，已知目录是由datadir、innodb_data_home_dir和innodb_directories变量定义的目录。

    临时表空间

    • 官网：MySQL ：： MySQL 8.0 参考手册 ：： 15.6.3.5 临时表空间
    • 在我们的SQL当 中，可能会用到临时表的逻辑，就是 在会话中创建一个表以供当前会话使用，会话关闭后，表失效。

    • Demo

      会话一： 
      CREATE TEMPORARY TABLE temp_table (
id INT,
name VARCHAR(50)
); -- 创建临时表
SELECT * FROM temp_table; -- 能查询临时表
      会话二： 
      SELECT * FROM temp_table; -- 会报表不存在错误
      临时表空间在innodb_temp_tablespaces_dir进行配置： 
      -- 查询临时表空间的目录路径
SELECT @@innodb_temp_tablespaces_dir;

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/145587.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

JSP学习笔记【三】——JQuery

JSP学习笔记【三】——JQuery

前言 在写项目的时候需要动态对某组件的属性进行调整，我看网上的教程都是使用document.getElementById等，但我在eclipse编写.jsp文件的时候，却提示document cannot be resolved。由于我对jsp没有系统的了解以及无人可咨询，网上也…
阅读更多...
Linux开发工具之文本编译器vim

Linux开发工具之文本编译器vim

●IDE例子 Linux编辑器-vim使用 vi/vim的区别简单点来说，它们都是多模式编辑器，不同的是vim是vi的升级版本，它不仅兼容vi的所有指令，而且还有一些新的特性在里面。例如语法加亮，可视化操作不仅可以在终端运行&#xff…
阅读更多...
金融生产存储亚健康治理：升级亚健康 3.0 ，应对万盘规模的挑战

金融生产存储亚健康治理：升级亚健康 3.0 ，应对万盘规模的挑战

随着集群规模的不断扩大，硬盘数量指数级上升，信创 CPU 和操作系统、硬盘多年老化、物理搬迁等多种复杂因素叠加，为企业的存储亚健康管理增加了新的挑战。 在亚健康 2.0 的基础上，星辰天合在 XSKY SDS V6.2 实现了亚健康 3.0&#…
阅读更多...
渗透测试之打点

渗透测试之打点

请遵守中华人民共和国网络安全法 打点的目的是获取一个服务器的控制权限 1. 企业架构收集 （1）官网 （2）网站或下属的子网站，依次往下 天眼查 企查查 2. ICP 备案查询 ICP/IP地址/域名信息备案管理系统 使用网站…
阅读更多...
ElasticSearch 10000条查询数量限制

ElasticSearch 10000条查询数量限制

一、前言 我们将库存快照数据导入ES后发现要分页查询10000条以后的记录会报错，这是因为ES通过index.max_result_window这个参数控制能够获取数据总数fromsize最大值，默认限制是10000条，因为ES考虑到数据要从其它节点上报到协调节点如果搜索请…
阅读更多...
APACHE NIFI学习之—UpdateAttribute

APACHE NIFI学习之—UpdateAttribute

UpdateAttribute 描述: 通过设置属性表达式来更新属性，也可以基于属性正则匹配来删除属性 标签: attributes, modification, update, delete, Attribute Expression Language, state, 属性, 修改, 更新, 删除, 表达式 参数: 如下列表中，必填参数则…
阅读更多...
Leetcode 剑指 Offer II 046. 二叉树的右视图

Leetcode 剑指 Offer II 046. 二叉树的右视图

题目难度: 中等 原题链接 今天继续更新 Leetcode 的剑指 Offer（专项突击版）系列, 大家在公众号 算法精选 里回复 剑指offer2 就能看到该系列当前连载的所有文章了, 记得关注哦~ 题目描述 给定一个二叉树的 根节点 root，请找出该二叉树的 最底…
阅读更多...
react create-react-app v5 从零搭建项目

react create-react-app v5 从零搭建项目

前言： 好久没用 create-react-app做项目了，这次为了个h5项目，就几个页面，决定自己搭建一个（ps:mmp 好久没用，搭建的时候遇到一堆问题）。 我之前都是使用 umi 。后台管理系统的项目 使用 antd-…
阅读更多...
【C++】C++11------线程库

【C++】C++11------线程库

目录 线程库接口线程接口使用lock_guard与unique_lockmutex(互斥锁)lock_guardunique_lock 原子性操作库条件变量(condition_variable) 线程库接口 在C11之前，涉及到多线程问题，都是和平台相关的，比如windows和linux下各有自己的接口&#x…
阅读更多...
使用Python进行App用户细分

使用Python进行App用户细分

App用户细分是根据用户与App的互动方式对用户进行分组的任务。它有助于找到保留用户，找到营销活动的用户群，并解决许多其他需要基于相似特征搜索用户的业务问题。这篇文章中，将带你完成使用Python进行机器学习的App用户细分任务。 App用户细…
阅读更多...
图片分割处理（以玉米颗粒的图片分割为例）

图片分割处理（以玉米颗粒的图片分割为例）

问题： 为完成玉米颗粒分类任务，现需要处理训练图片，将以下图片中的玉米颗粒进行分割： 目标： 操作步骤（完整代码附在最后，该部分为解释说明） 一、提取通道并进行二值化 # 提取蓝…
阅读更多...
解决Nacos配置刷新问题： 如何启用配置刷新功能以及与`@RefreshScope`注解的关联问题

解决Nacos配置刷新问题： 如何启用配置刷新功能以及与`@RefreshScope`注解的关联问题

🌷🍁 博主猫头虎 带您 Go to New World.✨🍁 🦄 博客首页——猫头虎的博客🎐 🐳《面试题大全专栏》 文章图文并茂🦕生动形象🦖简单易学！欢迎大家来踩踩~🌺 &a…
阅读更多...
三、2023.9.29.C++面向对象.3

三、2023.9.29.C++面向对象.3

文章目录 33、简述一下什么是面向对象？34、简述一下面向对象的三大特征？35、简述一下 C 的重载和重写，以及它们的区别？36、说说 C 的重载和重写是如何实现的？37、说说构造函数有几种，分别什么作用?38、只定…
阅读更多...
SpringBoot+MinIO8.0开箱即用的启动器

SpringBoot+MinIO8.0开箱即用的启动器

一、代码拉取及安装 1.码云地址 https://gitee.com/qiangesoft/rdp-starter/tree/master/rdp-starter-minio 2.本地安装 二、代码接入 存储路径规则可配置桶访问权限可配置可配置初始生成多个桶 1.引入依赖 <dependency><groupId>com.qiangesoft.rdp</gro…
阅读更多...
会议AISTATS(Artificial Intelligence and Statistics) Latex模板参考文献引用问题

会议AISTATS(Artificial Intelligence and Statistics) Latex模板参考文献引用问题

前言 在看AISTATS2024模板的时候&#xff0c;发现模板里面根本没有教怎么引用&#xff0c;要被气死了。 如下&#xff0c;引用(Cheesman, 1985)的时候&#xff0c;模板是自己手打上去的&#xff1f;而且模板提供的那三个引用&#xff0c;根本也没有Cheesman这个人&#xff0c…
阅读更多...
Mybatis 二级缓存（使用Ehcache作为二级缓存）

Mybatis 二级缓存（使用Ehcache作为二级缓存）

上一篇我们介绍了mybatis中二级缓存的使用&#xff0c;本篇我们在此基础上介绍Mybatis中如何使用Ehcache作为二级缓存。 如果您对mybatis中二级缓存的使用不太了解&#xff0c;建议您先进行了解后再阅读本篇&#xff0c;可以参考&#xff1a; Mybatis 二级缓存https://blog.c…
阅读更多...
Fake Maxpooling 二维滑动窗口

Fake Maxpooling 二维滑动窗口

先对每一行求一遍滑动窗口&#xff0c;列数变为(列数-k1) 再对每一列求一遍滑动窗口&#xff0c;行数变为(行数-k1) 剩下的就是每一个窗口里的最大值啦 #include<bits/stdc.h> #define IOS ios::sync_with_stdio(0);cin.tie(0);cout.tie(0); #define endl \nusing nam…
阅读更多...
AIGC 绘画Stable Diffusion工具的安装与使用

AIGC 绘画Stable Diffusion工具的安装与使用

我们先让ChatGPT来帮我们回答一下，什么是Stable Diffusion Stable Diffusion 是一种基于概率模型的图像生成技术。它通过对图像空间中每个像素的颜色值进行推断，从而生成具有高度真实感和细节的图像。 Stable Diffusion 使用一种称为扩散过程的方法来生成图像。在生成过程中…
阅读更多...
测试用例的编写（面试常问）

测试用例的编写（面试常问）

作者&#xff1a;爱塔居 专栏&#xff1a;软件测试 作者简介&#xff1a;不断总结&#xff0c;才能变得更好~踩过的坑&#xff0c;不能再踩~ 文章简介&#xff1a;常见的几个测试用例。 一、淘宝购物车 二、登录页面 三、三角形测试用例 abc结果346普通三角形333等边三角形334…
阅读更多...
【计算机网络笔记五】应用层（二）HTTP报文

【计算机网络笔记五】应用层（二）HTTP报文

HTTP 报文格式 HTTP 协议的请求报文和响应报文的结构基本相同&#xff0c;由四部分组成&#xff1a; ① 起始行&#xff08;start line&#xff09;&#xff1a;描述请求或响应的基本信息&#xff1b;② 头部字段集合&#xff08;header&#xff09;&#xff1a;使用 key-valu…
阅读更多...
最新文章

Copyright @ 2022~2023