前言 在学习一种事务之前，我们需要先了解事物的基本组成结构，清楚了事物的基本组成结构之后，我们才能更深入的了解相关操作，那么今天我将为大家介绍MySQL的体系架构。 MySQL数据库的服务端主要分为Server层和存储引擎层，接下来我将以这两层为着重点为大家介绍MySQL的体系架构。  MySQL的Server层 MySQL的Server层照顾要有七个组件：  MySQL 向外提供的交互接口（Connectors） 连接池组件（Connection Pool） 管理服务组件和工具组件（Management Service & Utilities） SQL 接口组件（SQL Interface） 查询分析器组件（Parser） 优化器组件（Optimizer） 查询缓存组件（Query Caches & Buffers） 1）MySQL向外提供的交互接口（Connectors） Connectors 组件是 MySQL 向外提供的交互组件，如Java，.NET，PHP等语言可以通过该组件来操作 MySQL 语句，实现与 MySQL 的交互。建立连接之后，可以通过show processlist 语句来查看已经建立的连接。  如果客户端一段时间内没有活跃行为，那么连接器在默认的8个小时后会主动断开连接。加果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。  客户端连接到MySQL数据库上时，根据连接时间的长短可以分为：短连接和长连接。短连接比较简单，指每次查询之后会断开，再次查询需要重新建立连接，因此使用短连接的成本较高；长连接指长时间连接到MySOL数据库上并执行数据库操作，因此长连接会导致出现内存溢出的问题从而使MySQL异常重启。 在使用长连接时，可以使用客户端函数mysql_reset_connection()来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。 2）连接池组件（Connection Pool） 负责监听客户端向MySQL服务器端的各种请求，接收请求、转发请求到目标模块。每个成功连接MySQL服务器端的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL服务器端的通信，接收客户端发送的命令，传递服务器端的结果信息等。 3）管理服务组件和工具组件（Management Service &Utilities） 提供对MySOL的集成管理，如备份（Backup)、恢复（Recovery）、安全管理（Security）等。  4）SQL接口组件（SQL Interface）  接收用户SQL命令，如DML、DDL和存储过程等，并将最终结果返回给用户。  5）查询分析器组件（Parser）  系统在执行输入语句之前，必须分析出语句想要干什么。例如:首先通过select关键字得知这是一条查询命令，还包括分析要查询的是哪张表以及查询条件是什么。同时，分析器必须分析输入语句的语法正确性。如果SQL中存在语法的错误，则查询分析器组件将返回提示信息“You have an error in your SQL syntax”。 6优化器组件（Optimizer） 优化器是MySQL用来对输人语句在执行之前所做的最后一步优化。优化内容包括：是否选择索引、选择哪个索引、多表查询的联合顺序等。每一种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一种方案。  7）查询缓存组件（Query Caches & Buffers） 这个查询缓存是比较容易理解的。在每一次查询时，MySQL 都先去看看是否命中缓存，命中则直接返回，提高了系统的响应速度。但是这个功能有一个相当大的弊病，那就是一旦这个表中数据发生更改，那么这张表对应的所有缓存都会失效。  对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非业务系统就只有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。所以在生产系统中，建议关闭该功能。  在MySQL8.0版本之前，可以通过将参数“query_cachetype”设置成OFF，来关闭查询缓存的功能。但是在MySQL8.0版本之后，直接删掉了这部分的功能。  show variables like '% query_cache% '; MySQL的存储引擎 MySQL 存储引擎层负责数据的存储和提取，其架构模式是插件式的，支持InnoDB、MyISAM、Memory、Archive、NDB Cluster等多个存储引擎。最常用的是InnoDB，我将为大家详细介绍InnoDb、MyISAM 和 Mymery 存储引擎。  我们可以使用 show create table 表名; 来查看创建表时使用的存储引擎。   create table test (id int);  show create table test; 1）InnoDB 存储引擎 InnoDB是MySQL的默认存储引擎，它支持ACID（原子性、一致性、隔离性和持久性）事务，并提供了行级锁定、外键约束和崩溃恢复等功能。它适用于大多数应用场景，特别是需要事务支持和高并发读写操作的应用。  它具有以下特性：  事务支持：InnoDB引擎支持事务的ACID属性，确保了数据的原子性、一致性、隔离性和持久性。这意味着可以使用BEGIN、COMMIT和ROLLBACK语句来管理事务，保证数据的完整性和一致性。 行级锁定：InnoDB使用行级锁来处理并发访问和修改数据，而不是表级锁。这意味着多个事务可以同时访问同一表的不同行，提高了并发性能和并发控制。 外键约束：InnoDB支持外键约束，可以在数据库层面实现数据的一致性和完整性。它提供了CASCADE、RESTRICT和SET NULL等选项来处理外键关系。 崩溃恢复：InnoDB具有崩溃恢复的能力，即使在系统崩溃或电源故障的情况下，也可以保证数据的完整性。它通过事务日志（redo log）来恢复未完成的事务和恢复已提交的事务。 自动增长列：InnoDB支持自动增长列，可以为表中的某一列指定自动递增的整数值，简化了数据插入操作。 回滚段：InnoDB通过回滚段（Rollback Segment）来存储未提交事务的数据，以便在需要时进行回滚操作。 可以在线热备份：InnoDB引擎支持在线热备份，可以在不停止MySQL服务器的情况下备份数据库。 支持MVCC（多版本并发控制）：InnoDB使用多版本并发控制来处理并发事务，在读操作的同时允许写操作，并通过行版本来实现数据的隔离性和一致性。 高性能：InnoDB引擎通过使用缓冲池（Buffer Pool）来缓存热门数据和索引，提高读取数据的性能。 2）MyISAM 存储引擎 MyISAM是MySQL的另一个常见的存储引擎，它不支持事务和行级锁定，但具有良好的性能。MyISAM适用于主要是读取操作的应用，如数据仓库、归档和非事务性的应用。  它具有以下特性：  快速读取速度：MyISAM存储引擎在读取数据时非常高效，对于主要是读取操作的应用性能表现较好。这是因为MyISAM表以表级锁定的方式处理并发，读操作可以并发执行，不会有行级锁定带来的争用。 支持全文索引：MyISAM存储引擎对全文索引提供了良好的支持，可以通过创建全文索引提供高效的文本搜索能力。 节省磁盘空间：相较于InnoDB存储引擎，MyISAM通常在磁盘占用方面更加节省空间，这是因为它不支持事务、行级锁定和崩溃恢复等功能，减少了存储额外的元数据和日志。 表级锁定：MyISAM存储引擎使用表级锁定，这意味着一个写操作锁定整个表，因此在写操作频繁的情况下可能会导致并发性能下降。 不支持事务和外键：MyISAM存储引擎不支持事务操作，也不支持外键约束。这意味着在使用MyISAM时，你无法使用BEGIN、COMMIT和ROLLBACK等事务操作，也无法定义外键约束来维护数据的完整性。 不支持崩溃恢复：MyISAM存储引擎没有崩溃恢复的能力，这意味着如果MySQL服务器在写操作过程中崩溃，可能会导致数据的不一致。 自动维护索引统计信息：MyISAM存储引擎会自动维护表的索引统计信息，这些统计信息用于优化查询执行计划。 多用途：MyISAM存储引擎适用于主要是读取操作的应用场景，如报表、日志分析和静态网站等。 我们可以在创建表的时候指定存储引擎。  create table 表名 (     ) engine = 存储引擎名 eate table test1 (id int) engine = myisam;  show create table test1; 正是因为 MyISAM 存储引擎的这些特性，它适合于以下场景：  不需要事务支持的场景 读多或者写多的单一业务场景，读写频繁的则不合适 读写并发访问较低的业务 数据修改相对较少的业务 以读为主的业务 对数据的一致性要求不是很高的业务 服务器硬件资源相对比较差的环境 3）Memory 存储引擎 Memeory 存储引擎将表中的数据存储在内存中，而不是磁盘上，也就是说如果重启MySQL 或者关闭，此时的数据将会丢失。  create table test2 (id int,name varchar(20)) engine = memory; show create table test2; insert into test2 values (1,'zhangsan'); select * from test2; # 重启MySQL systemctl restart mysqld select * from test2; 1 输出信息  Empty set (0.00 sec) 1 Memory 存储引擎具有以下特点：  高速读写：由于数据存储在内存中，Memory 存储引擎提供非常快速的读取和写入性能。相比于其他存储引擎，它可以更快地执行查询和写入操作。 临时数据和缓存表：由于数据存储在内存中，Memory 存储引擎对于处理临时数据和缓存表非常有效。如果你需要在查询过程中创建一些临时数据，并且它们在查询结束后不再需要，那么 Memory 引擎是一个不错的选择。 高速缓存索引：Memory 存储引擎对索引查询非常快速，因为索引数据完全存储在内存中，减少了磁盘I/O的开销。 不持久化：Memory 引擎的数据不会持久化到磁盘上，一旦 MySQL 服务器重启或关闭，存储在 Memory 引擎中的数据就会丢失。因此，Memory 存储引擎适合于处理非持久化的数据，并且可以在服务器重新启动后重新加载数据。 适用于小规模数据：由于数据存储在内存中，Memory 存储引擎的容量受限于可用的内存大小。它不适合用于处理大规模数据集，因为内存可能会成为限制因素。 不支持事务和崩溃恢复：Memory 存储引擎不支持事务，也不支持崩溃恢复。因此，在使用 Memory 存储引擎时需要注意数据的一致性和持久性。 ———————————————— 版权声明：本文为CSDN博主「不能再留遗憾了」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/m0_73888323/article/details/131523624 

 Docker是一种流行的容器化平台，可以简化应用程序的部署和管理。在本博客中，我们将探讨如何使用Docker部署两个广泛使用的数据库：MySQL。我们将提供详细的步骤和相应的命令，以帮助您轻松地在Docker容器中设置和运行这个数据库。  1. Docker部署Mysql 1.1 Mysql容器 1.1.1 创建Mysql容器 首先我们拉取mysql镜像，要在Docker中部署MySQL数据库，我们首先需要创建一个MySQL容器。可以使用以下命令创建一个MySQL 8..0.24版本的容器：  docker run -d --name mysql-container -e MYSQL_ROOT_PASSWORD=123456  -p 3307:3306 mysql:8.0.24 此命令会创建一个名为mysql-container的容器，将MySQL的root用户密码设置为123456，并将宿主机的3307端口映射到容器的3306端口。  1.1.2 进入Mysql容器并登录Mysql docker exec -it mysql-container mysql -u root -p 此命令将打开MySQL的命令行客户端，并要求您输入MySQL root用户的密码如下图：    然后我们就可以在这里进行数据库操作。  1.1.3 持久化数据 为了在容器重新启动后保留MySQL数据，可以将数据目录映射到宿主机的目录。在创建容器时，可以添加以下参数：  -v /docker/mysql/config/my.cnf:/etc/my.cnf #宿主机目录：mysql容器目录 -v /docker/mysql/data:/var/lib/mysql  这里可以进行数据卷挂载，卷就是目录或文件，存在于一个或多个容器中，由docker挂载到容器，但不属于联合文件系统，因此能够绕过Union File System提供一些用于持续存储或共享数据的特性，卷的设计目的就是数据的持久化，完全独立于容器的生存周期，因此Docker不会在容器删除时删除其挂载的数据卷。数据卷可在容器之间共享或重用数据并且卷中的更改可以直接实时生效，数据卷的生命周期一直持续到没有容器使用它为止。如下图：      1.2 远程登录Mysql 在Mysql 8.x版本当我们在云服务器上创建dockier容器后，尝试远程登录Docker容器内数据库的时候会遇见如下图问题：    这是什么原因呢？  出现1251的主要原因是由于mysql版本的问题，mysql8.0版本，与mysql8.0以下版本的加密方式不同，导致错误产生。  MySql 8.0.11 换了新的身份验证插件（caching_sha2_password）,而原来的身份验证插件为（mysql_native_password）。​ 而客户端工具Navicat Premium12 中找不到新的身份验证插件（caching_sha2_password），因此报上面的错，所以我们将mysql用户使用的 登录密码加密规则还原成 mysql_native_password，即可登陆成功。  1.2.1 修改root加密方式 运行下面的命令：  mysql -u root -p  #登陆mysql use mysql;    # 切换mysql数据库 select host, user, authentication_string, plugin from user; #查看root用户登录加密方式   如下图：    然后我们改变加密命令  alter user 'root'@'%' identified with mysql_native_password by '123456';  然后再次查看root用户登录的加密方式  select host, user, authentication_string, plugin from user; #查看root用户登录加密方式     然后我们重新使用客户端登录系统显示登录成功。  1.2.2 在容器启动时配置加密方式为mysql_native_password 代码-e identified=mysql_native_password，配置了加密方式。  docker run -d --name mysql-container -e MYSQL_ROOT_PASSWORD=123456  -p 3307:3306 -e identified=mysql_native_password mysql:8.0.24     1.3 Mysql编码 1.3.1 Mysql编码问题 当我们使用客户端连接成功我们的docker容器后，然后进行创建数据库，创建表格然后添加数据如下：  CREATE DATABASE /*!32312 IF NOT EXISTS*/`project` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;   USE `project`;   /*Table structure for table `user` */   DROP TABLE IF EXISTS `user`;   CREATE TABLE `user` (   `id` BIGINT NOT NULL AUTO_INCREMENT,   `username` VARCHAR(20) DEFAULT NULL,   `password` VARCHAR(20) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;   /*Data for the table `user` */   INSERT  INTO `user`(`id`,`username`,`password`) VALUES (1,'张三','123'),(2,'lisi','456');  然后在我们的docker容器内查询mysql数据如下图：    然后我们发现了乱码问题，乱码一般都是因为编码引起的，所以我们来查一下数据库的编码    1.3.2 Mysql编码问题解决办法 1.修改my.cnf文件  cd /etc/mysql/ #进入my.cnf文件中的目录 vim my.cnf #编辑my.cnf文件 2.出现bash: vim: command not found提示，需要安装一下vim，使用如下命令  apt-get update apt-get install vim -y 重新执行vim命令。  3. 在 my.cnf文件中[mysql] 下面添加  default-character-set=utf8mb4，然后 :wq 退出。没有 [mysql] 的话就写一个。  如下图：      然后看一下mysql的字符集，已经变成 utf8mb4 了，这样就可以解决中文乱码问题了。    查看表格数据   至此我们的问题得到了成功解决。 送书活动 Python自动化办公应用大全（ChatGPT版）：从零开始教编程小白一键搞定烦琐工作（上下册） 本书简介： 本书全面系统地介绍了Python语言在常见办公场景中的自动化解决方案。全书分为5篇21章，内容包括Python语言基础知识，Python读写数据常见方法，用Python自动操作Excel，用Python自动操作Word 与 PPT，用Python自动操作文件和文件夹、邮件、PDF 文件、图片、视频，用Python进行数据可视化分析及进行网页交互，借助ChatGPT轻松进阶Python办公自动化。  本书适合各层次的信息工作者，既可作为初学Python的入门指南，又可作为中、高级自动化办公用户的参考手册。书中大量的实例还适合读者直接在工作中借鉴。本书特色：   ★方式新颖 详细介绍了如何用 ChatGPT 来补充学习知识点，以及如何快速生成所需的代码，零基础人员学习编程的成本进一步降低。 ★内容丰富 以Excel数据处理与分析为重点，延展到 Word、PPT、邮件、图片、视频、音频、本地文件管理、网页交互等现代办公所需要处理的各种形式的数据。 ★案例实用 用大量易借鉴的案例帮助用户学会在各个场景中使用自动化技术。  ★作者权威 Excel Home团队策划，多位微软全球最有价值专家（MVP）通力打造，确保每个案例都实用，对编程小白友好。  让没有编程经验的普通办公人员也能驾驭 Python，实现多个场景的办公自动化，提升效率！ ———————————————— 版权声明：本文为CSDN博主「山河亦问安」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_43649937/article/details/131645945 

 一、数据库的索引介绍和如何使用索引加速查询 索引是用于加速数据库查询的一种数据结构，其基本原理就是在查询时避免全表扫描，在查询时采用二分查找的方式快速定位数据。MySQL 支持多种类型的索引，包括简单索引、主键索引、唯一索引和全文索引等。使用索引可以大幅度提高数据查询的效率，但索引的维护和使用也需要一定的成本。 MySQL 中常用的索引类型如下：  简单索引：指在一个列上创建的普通索引。可以加快查询速度，但不能保证列中的值是唯一的。 主键索引：基于一个或多个列进行排序的索引，用于唯一标识一条记录。 唯一索引：与主键类似，但可以包含空值。 全文索引：用于全文搜索，常用于文本数据类型的列。 B树索引：基于B树数据结构实现的索引，适用于等值查询和范围查询。 B+树索引：基于B+树数据结构实现的索引，适用于等值查询和范围查询。 哈希索引：基于哈希表实现的索引，适用于等值查询。 空间索引：适用于空间数据的索引，可以快速定位空间对象的位置。 在使用索引的同时，还需要注意以下几个问题：  索引的选择：应该根据查询的实际情况选择合适的索引类型，不要盲目添加索引。 索引的数量：过多的索引会增加空间和维护成本，应该根据实际情况谨慎添加。 索引的更新：插入、更新和删除操作会影响索引的更新，应该避免频繁的更新操作。 复合索引：将多个列的索引组合在一起，可以提高查询效率，但需要注意索引的排序方式和顺序。 索引可以提高查询效率，但是会占用磁盘空间，同时会影响数据的插入和更新操作，因为每次插入和更新数据时，索引也需要随之更新。 索引的创建需要根据具体情况进行权衡和选择，如果表中的数据量很大，或者需要频繁地进行插入和更新操作，那么创建过多的索引可能会影响性能。 在使用索引时，需要注意索引的选择和使用方法，避免出现过度索引的情况，也需要注意避免使用过多的联合索引，因为联合索引需要满足一定的条件才能生效。 二、视图的作用以及如何创建视图 视图是一种虚拟的表，可以将多张表的数据整合在一起，通过视图查询可以获得数据的一部分。视图将表数据的逻辑结构和物理结构分开，是一个非常重要的数据抽象技术。视图在多表查询、数据分离和权限控制等方面都有很大的作用。  简化复杂查询：通过视图，可以将多个表的查询结果合并成一个表，从而简化复杂的查询操作。 保护数据隐私：视图可以隐藏部分数据，只显示给用户他们需要看到的数据，保护数据隐私。 提高查询性能：视图可以缓存查询结果，避免多次执行相同的查询操作，提高查询性能。 在 MySQL 中，创建视图的语法如下：  CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; 其中，view_name 表示新视图的名称， column1、column2 等表示要查询的列名或表达式， table_name 表示要查询的表名， WHERE 条件表示数据筛选条件。  例如，以下语句将创建一个名为“employee_info”的视图，显示员工姓名、工资和部门名称：  CREATE VIEW employee_info AS   SELECT employees.name, employees.salary, departments.department_name   FROM employees   JOIN departments ON employees.department_id = departments.department_id; 创建好视图之后，可以使用视图名称，使用SELECT语句来查询视图，例如：  SELECT * FROM employee_info; 1 这将返回视图“employee_info”中的所有数据。  三、存储过程和触发器的使用及示例 3.1 存储过程 存储过程（Stored Procedure）是一组为了完成特定任务而预先编写的集合，存储在数据库中，并通过一个关键字来调用执行。存储过程可以包含一系列SQL语句，可以在数据库中创建、删除、修改或调用。  触发器（Trigger）是一种特殊的存储过程，它在数据库中某个表执行特定操作（如插入、更新或删除）时自动触发。触发器可以用于强制实施数据完整性约束，或者在数据发生变化时执行一些操作。 下面是一个创建存储过程的示例，该存储过程将两个数相加并返回结果：  CREATE PROCEDURE AddNumbers      @FirstNumber INT,      @SecondNumber INT,      @Result INT OUTPUT   AS   BEGIN      SET @Result = @FirstNumber + @SecondNumber      RETURN @Result   END 在上面的示例中，我们创建了一个名为AddNumbers的存储过程，它接受两个整数参数，并返回这两个数的和。存储过程中的@Result参数是一个输出参数，用于返回计算结果。 下面是调用存储过程的示例： EXEC AddNumbers 5, 10, @Result OUTPUT   SELECT @Result 在上面的示例中，我们通过EXECUTE语句调用了AddNumbers存储过程，并将5和10作为输入参数传递给它。存储过程返回的结果被存储在@Result变量中，并通过SELECT语句进行输出。 下面是一个创建触发器的示例，该触发器将在每次插入新记录时将插入时间戳保存在另一个表中：  CREATE TRIGGER InsertTimestampTrigger   ON InsertedTable   FOR INSERT   AS   BEGIN      INSERT INTO TimestampTable (Timestamp)      SELECT GETDATE()   END 1 2 3 4 5 6 7 8 在上面的示例中，我们创建了一个名为InsertTimestampTrigger的触发器，它将在InsertedTable表中插入新记录时触发。触发器将当前时间戳保存到另一个表TimestampTable中。 当我们在InsertedTable表中插入新记录时，触发器将自动执行：  INSERT INTO InsertedTable (Name, Value) VALUES ('Test', 123) 1 在上面的示例中，我们向InsertedTable表中插入了一条新记录，这将会触发InsertTimestampTrigger触发器，并将当前时间戳保存在TimestampTable表中。  四、学习事务的概念、ACID属性、以及如何保证数据的一致性 4.1 事务 事务（Transaction）是指一组数据库操作，这些操作要么全部执行成功，要么全部失败回滚。事务是一个原子性的操作，它要么全部执行成功，要么全部失败回滚。  4.2 ACID ACID是指数据库事务正确执行所需要满足的四个基本特性：  原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部失败回滚。 一致性（Consistency）：事务执行后，数据库的数据必须符合数据库的一致性约束条件。 隔离性（Isolation）：并发执行的事务之间互不干扰，事务执行时独立进行，不会相互影响。 持久性（Durability）：事务执行成功后，对数据的修改永久保存在数据库中，即使出现系统故障也不会丢失。 4.3 数据的一致性 数据一致性是指数据库中的数据在多个副本之间的一致性。在分布式数据库系统中，数据被分布在不同的节点上，每个节点都有自己的副本，因此需要保证这些副本之间的数据一致性。 数据一致性分为三种类型：  强一致性：当数据被更新后，所有副本都会立即看到更新后的数据。 弱一致性：当数据被更新后，所有副本最终都会看到更新后的数据，但不一定是立即看到的。 最终一致性：当数据被更新后，所有副本最终都会看到更新后的数据，但在一段时间内，可能会存在数据不一致的情况。 为了实现数据一致性，分布式数据库系统通常采用数据复制、数据同步等技术来保证多个副本之间的数据一致性。同时，还需要考虑如何处理节点故障、网络中断等问题，以保证数据库的可靠性和可用性。  为了保证数据的一致性，通常需要采取以下措施：  使用事务：将数据的操作封装在事务中，确保操作的原子性、一致性和隔离性。 合理设计事务的并发策略：根据业务需求和数据操作的特性，选择合适的并发策略，如读写锁、行锁、表锁等。 避免并发冲突：通过合理的设计和优化，减少并发冲突的可能性，如使用分区表、分库分表等技术。 实施数据备份和恢复策略：定期进行数据备份，确保数据不会因为系统故障而丢失，能够在故障发生后恢复数据到最新状态。 使用可靠的网络传输协议：在进行分布式系统数据传输时，使用可靠的网络传输协议，确保数据的完整性和一致性。 监控和日志记录：对数据库和数据操作进行监控和日志记录，及时发现和处理问题，保证数据的一致性和可靠性。 五、MySQL安全相关概念介绍 5.1 MySQL的安全设置 MySQL的安全设置包括以下几个方面：  用户权限管理：MySQL支持多用户管理，可以为不同用户分配不同的权限。可以为每个用户设置不同的访问权限，如只读、读写、完全控制等。 数据库加密：MySQL支持对数据库进行加密，可以使用AES、DES等算法对数据库进行加密处理，保护数据的安全性。 网络安全：MySQL可以通过配置网络访问控制列表（ACL）来限制数据库的访问，只允许可信源IP地址访问数据库。 数据库备份：MySQL的备份操作可以将数据备份到本地或者远程，可以定期备份数据，以保证数据不会因为攻击、故障等原因丢失。 日志记录：MySQL支持记录操作日志，可以记录用户的登录、操作等行为，以便进行安全审计和追踪。 5.2 数据库的维护操作方法，包括备份和恢复MySQL中的数据 备份MySQL数据的方法有以下几种：  使用mysqldump命令备份：使用命令行工具，输入“mysqldump -u username -p dbname tablename > filename.sql”即可备份指定数据库中指定表的SQL语句。 使用MySQL Workbench备份：MySQL Workbench是一款MySQL官方推出的图形化工具，可以通过它进行数据库备份。 使用第三方备份工具：如Xtrabackup、mysqldbcopy等。 恢复MySQL数据的方法有以下几种： 使用mysql命令恢复：使用命令行工具，输入“mysql -u username -p dbname < filename.sql”即可恢复备份的SQL语句。 使用MySQL Workbench恢复：可以通过MySQL Workbench进行数据库恢复。 使用第三方恢复工具：如Xtrabackup、mysqldbcopy等。 需要注意的是，在进行数据库备份和恢复时，应该选择合适的时间点和方式，避免影响数据库的正常运行和数据的一致性。并且，在备份和恢复过程中，应注意备份文件和恢复文件的安全性，避免数据丢失或泄露。 5.3 SQL注入 SQL注入是指攻击者利用Web应用程序的漏洞，向后台数据库服务器发送恶意SQL查询语句，以获取或篡改数据库中的敏感信息，或者实现未经授权的操作。攻击者通常通过在Web表单中插入特定的SQL代码或者在URL参数中插入恶意代码来实施SQL注入攻击。 例如，攻击者可以在Web表单中输入类似于“admin’ OR 1=1 --”这样的字符串，这将导致后台数据库执行不需要的SQL查询，从而泄露敏感信息或者执行其他恶意操作。 为了避免SQL注入攻击，开发者可以采取以下预防措施：  使用参数化查询：将用户输入的数据作为查询参数传递给数据库服务器，而不是将其拼接到SQL查询语句中。 对输入数据进行过滤和验证：对用户输入的数据进行严格的过滤和验证，确保只有预期的数据类型和格式才能通过验证。 限制数据库权限：只给予应用程序必要的数据库权限，避免因为过度授权而导致的安全问题。 使用安全的编程框架：使用安全的编程框架和工具来编写Web应用程序，例如Spring Security、OWASP ESAPI等。 日志记录：记录所有的用户操作和数据库查询，以便进行安全审计和追踪。 MySQL存在许多安全问题，需要采取多种措施来提高其安全性，及时更新和打补丁。 ———————————————— 版权声明：本文为CSDN博主「Android西红柿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/fumeidonga/article/details/131144640 

前言 在前一篇内容中，学习了MySQL的行列转换中的行转列，其中只讲述了在MySQL与Oracle中通欧诺个的行转列，并且进行了对应的扩展了——如果想在结果中加入学生姓名的方法，上一篇讲了其中一种方法，就是使用关联子查询。  今天这篇内容，将继续进行讲述MySQL的行列转换的后续内容，其中包括添加学生姓名的第二种方法(使用了join进行关联表)，以及本文章主攻的核心内容——行列转换中的列转行。  同样的，为了大家可以更方便的一起跟着文章进行代码的操作学习，在文章中的每一块的知识点都提供了对应的数据准备，即大家可以直接复制代码在自己的电脑上进行建表，然后根据文章中的内容一起进行实操，因为我个人认为计算机方面的知识点的学习，实操相对于光进行文字的学习会更有效果，并且更容易发现自己的短板和思路中存在的问题。  那么，快拿出你的电脑，跟着文章一起学习起来吧  一、MySQL行列转换 1.数据准备操作 👉:传送门💖数据准备操作💖  2.行转列 1.1为何进行行转列？ 👉:传送门💖1.1为何进行行转列？💖  1.2 行转列有两个意思：1.表内的行转列 2.跨表的行转列 👉:传送门💖1.2 行转列的两个意思💖  3.行转列的思路：行变少，列变多 3.1 如何进行行转列：增加字段，进行聚合(行变少) 👉:传送门💖3.1如何进行行转列💖  4.行转列的实操 4.1 通用的行转列(Mysql和Oracle都能用) 👉:传送门💖4.1通用的行转列(Mysql和Oracle都能用)💖  4.1.1想在结果中加入学生名字 👉:传送门💖4.1.1在结果中加入学生名字)💖  4.1.1.1加入名字的方法1： 4.1.1.1 加入名字的方法2： select distinct t1.user_name ,t0.*    -- 如果不加distinct，因为t1表每个ID对应多个名字，所以最终结过就是，名字重复几次，结果就有几行重复 from (     select              user_id '学生ID',             max(case when course = '语文' then score end) '语文',             max(case when course = '数学' then score end) '数学',             max(case when course = '英语' then score end) '英语'     from              table_grade     group by              user_id ) t0left join table_grade t1 on t0.学生ID  = t1.user_id;     -- 此处的t0.学生ID,因为前面设置了别名，所以此处也应该使用别名,不然就会发生错误:Unknown column 't0.user_id' in 'on clause' 4.2 私有方法的行转列(Mysql用) select user_id '学生ID',          max(if(course = '语文',score,null)) '语文',          max(if(course = '数学',score,null)) '数学',          max(if(course = '英语',score,null)) '英语' from table_grade group by user_id  4.2.1 添加名字的两种方法 select user_id '学生ID',             (select max(user_name) from table_grade where table_grade.user_id =t.user_id ) user_name,             max(if(course = '语文',score,null)) '语文',             max(if(course = '数学',score,null)) '数学',             max(if(course = '英语',score,null)) '英语' from table_grade t group by user_id  select distinct t1.user_name,t0.* from  (     select user_id '学生ID',                     max(if(course = '语文',score,null)) '语文',                     max(case when course = '数学' then score end) '数学',                     max(if(course = '英语',score,null)) '英语'      from table_grade        group by user_id ) t0 left join table_grade t1 on t0.学生ID = t1.user_id; 3.列转行     a    b    c              1                                          1   a 2                     1   b 3                     1   c                       2   a                                             2        b                                             2        c                                              3        a                                             3        b                                             3        c 列转行如上图所示，左边变成右边  右图又称为纵表，这种纵表在大数据中适合用工具hbase进行列式存储，里面存的就是键值对，右图的左列是键、右列是值 纵表适合存储，横表适合分析 底层明细数据，适合列式存储 3.1列转行思路：行变多 用union select(查询)能表达的关系是并差交笛卡尔积 集合运算是 并差交笛卡尔积 关系运算是 投影连接除 大数据一次处理一个集合(set),不是一个记录(record) 3.2 列转行实操 3.2.1 数据准备 建个横表  create table table_grade_wide as(       select user_id '学生ID',           max(if(course = '语文',score,null)) '语文',           max(if(course = '数学',score,null)) '数学',           max(if(course = '英语',score,null)) '英语'       from table_grade       group by user_id )  alter table table_grade_wide change user_id id  int; 3.2.2 实操 select * from(     select 学生ID,'语文' course,语文 score from table_grade_wide -- 只需要在第一个select字段中修改别名就好了,因为union的时候，前后的所有的select的列的类型和个数是一致的     union   --     select 学生ID,'数学',数学 from table_grade_wide     union      select 学生ID,'英语',英语 from table_grade_wide ) a where score is not null     -- 因为有的同学只考了其中几门课 order by 1;   -- 按照最后结果的第一列进行排序 小结 好了，MySQL的行列转换到这里就要告一段落了，希望大家通过上一篇文章——行列转换（一）• MySQL版以及本篇文章的学习，应该对MySQL的行列转换有了了解，学习是永无止境的，接下来，我们会按照这样的方式为大家讲述Oracle中的行列转换，如果大家对于文章的内容、排版等各个方面有什么好的想法，都可以进行沟通交流，也希望我的博客中的内容能为大家在学习的道路上提供一xxxx，我们一起学习，一起进步 ———————————————— 版权声明：本文为CSDN博主「爱书不爱输的程序猿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_40332045/article/details/131488624 

 我们从上文中了解到InnoDB默认的事务隔离级别是repeatable read（后文中用简称RR），它为了解决该隔离级别下的幻读的并发问题，提出了LBCC和MVCC两种方案。其中LBCC解决的是当前读情况下的幻读，MVCC解决的是普通读（快照读）的幻读。至于什么是当前读，什么是快照读，将在文中给出答案。  有想赚点外块|技术交流的朋友，欢迎来撩  LBCC LBCC是Lock-Based Concurrent Control的简称，意思是基于锁的并发控制，此文主要内容是MVCC，所以LBCC暂时不展开。  当前读 当前读（Locking Read）也称锁定读，读取当前数据的最新版本，而且读取到这个数据之后会对这个数据加锁，防止别的事务更改即通过next-key锁（行锁+gap锁）来解决当前读的问题。  在进行写操作的时候就需要进行“当前读”，读取数据记录的最新版本，包含以下SQL类型：select ... lock in share mode 、select ... for update、update 、delete 、insert。  因为锁的粒度过大，会导致性能的下降，因此提出了比LBCC性能更优越的方法MVCC。  MVCC MVCC是Multi-Version Concurremt Control的简称，意思是基于多版本的并发控制协议，通过版本号，避免同一数据在不同事务间的竞争，只存在于InnoDB引擎下。它主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写。  MVCC的实现依赖于：三个隐藏字段、Undo log和Read View，其核心思想就是：只能查找事务id小于等于当前事务ID的行；只能查找删除时间大于等于当前事务ID的行，或未删除的行。  接下来让我们从源码级别来分析下MVCC。  隐藏列 MySQL中会为每一行记录生成隐藏列，接下来就让我们了解一下这几个隐藏列吧。  （1）DB_TRX_ID：事务ID，是根据事务产生时间顺序自动递增的，是独一无二的。如果某个事务执行过程中对该记录执行了增、删、改操作，那么InnoDB存储引擎就会记录下该条事务的id。  （2）DB_ROLL_PTR：回滚指针，本质上就是一个指向记录对应的undo log的一个指针，大小为 7 个字节，InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在undo log中都通过链表的形式组织。  （3）DB_ROW_ID：行标识（隐藏单调自增 ID），如果表没有主键，InnoDB 会自动生成一个隐藏主键，大小为 6 字节。如果数据表没有设置主键，会以它产生聚簇索引。  （4）实际还有一个删除flag隐藏字段，既记录被更新或删除并不代表真的删除，而是删除flag变了。  undo log 每当我们要对一条记录做改动时（这里的改动可以指INSERT、DELETE、UPDATE），都需要把回滚时所需的东西记录下来, 比如:  Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。 Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。 Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。 InnoDB把这些为了回滚而记录的这些东西称之为undo log。这里需要注意的一点是，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log。 每次对记录进行改动都会记录一条undo日志，每条undo日志也都有一个DB_ROLL_PTR属性，可以将这些undo日志都连起来，串成一个链表，形成版本链。版本链的头节点就是当前记录最新的值。  例  先插入一条记录，假设该记录的事务id为80，那么此刻该条记录的示意图如下所示  实际上insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo Log Segment也会被系统回收。接着继续执行sql操作  其版本链如下  很多人以为undo log用于将数据库物理的恢复到执行语句或者事务之前的样子，其实并非如此，undo log是逻辑日志，只是将数据库逻辑的恢复到原来的样子。因为在多并发系统中，你把一个页中的数据物理的恢复到原来的样子，可能会影响其他的事务。  Read View 在可重复读隔离级别下，我们可以把每一次普通的select查询（不加for update语句）当作一次快照读，而快照便是进行select的那一刻，生成的当前数据库系统中所有未提交的事务id数组（数组里最小的id为min_id）和已经创建的最大事务id（max_id）的集合，即我们所说的一致性视图readview。在进行快照读的过程中要根据一定的规则将版本链中每个版本的事务id与readview进行匹配查询我们需要的结果。  快照读是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。MVCC只在 READ COMMITTED 和 REPEATABLE READ两个隔离级别下工作，其他两个隔离级别不和MVCC不兼容。因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE 则会对所有读取的行都加锁。事务的快照时间点（即下文中说到的Read View的生成时间）是以第一个select来确认的。所以即便事务先开始，但是select在后面的事务的update之类的语句后进行，那么它是可以获取前面的事务的对应的数据。  RC和RR隔离级别下的快照读和当前读：RC隔离级别下，快照读和当前读结果一样，都是读取已提交的最新；RR隔离级别下，当前读结果是其他事务已经提交的最新结果，快照读是读当前事务之前读到的结果。RR下创建快照读的时机决定了读到的版本。  对于使用RC和RR隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的。核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，InnoDB提出了一个Read View的概念。  Read View就是事务进行快照读（普通select查询）操作的时候生产的一致性读视图，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，它由执行查询时所有未提交的事务id数组（数组里最小的id为min_id）和已经创建的最大事务id（max_id）组成，查询的数据结果需要跟read view做对比从而得到快照结果。   版本链比对规则：  如果落在绿色部分（trx_id<min_id），表示这个版本是已经提交的事务生成的，这个数据是可见的； 如果落在红色部分（trx_id>max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的； 如果落在黄色部分（min_id<=trx_id<=max_id），那就包含两种情况： a.若row的trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；如果是自己的事务，则是可见的； b.若row的trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。 光说不练假把式，接下来就让我们用例子来演示一下：首先我们要准备两张表，一张test和一张account表，然后我们以account的undo log来画版本链，准备数据和原始记录图如下  //test表中数据 id=1,c1='11'; id=5,c1='22'; //account表数据 id=1，name=‘lilei’；  如下图，我们将按照里面的顺序执行sql  当我们执行到第7行的select的语句时，会生成readview[100,200],300,版本链如图所示：  此时我们查询到的数据为lilei300。我们首先要拿最新版本的数据trx_id=300来readview中匹配，落在黄色区间内，一看该数据已经提交了，所以是可见的。继续往下执行，当执行到第10行的select语句时，因为trx_id=100并未提交，所以版本链依然为readview[100,200],300，版本链如图所示：  此时我们查询到的数据为lilei300。我们按上边操作，从最新版本依次往下匹配，我们首先要拿最新版本的数据trx_id=100来readview中匹配，落在黄色区间内，一看该数据在未提交的数组中，且不是自己的事务，所以是不可见的；然后我们选择前一个版本的数据，结果同上；继续向上找，当找到trx_id=300的数据时，会落在黄色区间，且是提交的，所以数据可见。继续往下执行，当执行到第13行的select语句时，此时尽管trx_id=100已经提交了，因为是InnoDB的RR模式，所以readview不会更改，仍为readview[100,200],300,版本链如图所示：  此时我们查询到的数据为lilei300。原因同上边的步骤，不再赘述。  当执行update语句时，都是先读后写的，而这个读，是当前读，只能读当前的值，跟readview查找时的快照读区分开。  刚才演示的是InnoDB下的RR模式，接下来我们简单说一下RC模式，上文中提到的RC模式的数据读都是读最新的即当前读，所以readview是实时生成的，执行语句如图所示：  当我们执行到第13行的select的语句时，会生成readview[200],300，版本链还和之前一样，此时我们查询到的数据为lilei2。原因和上边讲的RR模式下的比对规则相同。  此处我们演示的是update的情况，对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位上写上true，来表示当前记录已经被删除，在查询时按照上边的规则查到对应的记录，如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。  大家应该还关心一个问题，即undo log什么时候删除呢？  系统会判断，没有比这个undo log更早的read view的时候，undo log会被删除。所以这里也就是为什么我们建议你尽量不要使用长事务的原因。长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。  总结 LBCC是基于锁的并发控制，因为锁的粒度过大，会导致性能的下降，因此提出了比 LBCC 性能更优越的方法 MVCC。 MVCC 是基于多版本的并发控制协议，通过版本号，避免同一数据在不同事务间的竞争，只存在于InnoDB引擎下。 MVCC 主要是为了提高数据库的并发读写性能，不用加锁就能让多个事务并发读写。 MVCC的实现依赖于：三个隐藏字段、Undo log和Read View。 MVCC 的核心思想就是：只能查找事务id小于等于当前事务ID的行；只能查找删除时间大于等于当前事务ID的行，或未删除的行。 ———————————————— 版权声明：本文为CSDN博主「阿Q说代码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/Qingai521/article/details/131321197 

在mysql的悲观锁是相对于乐观锁而言的，我们悲观的认为线程更新数据时经常会因为线程冲突而无法修改成功，因此需要在从读数据到更新数据结束采用加锁的形式实现。例如以下sql语句#开启事务（三选一）begin;/begin work;/start transaction;#首先利用【select ... for update】加锁select money from users where userid=1 for update;#更新数据update users set money=1500 where userid=1;#提交事务或者回滚commit;/commit work;/rollback;1、innodb引擎时, 默认行级锁, 当有明确字段时会锁一行；2、如无查询条件或条件字段不明确时, 会锁整个表；3、条件为范围时会锁整个表；4、查不到数据时, 则不会锁表。所以在实际项目中容易造成事故一般不使用数据库级别的悲观锁，而是使用分布式锁或者Synchronized、ReendtrantLock等实现。

在高并发场景中修改数据库内数据经常会遇到需要加锁修改的场景，数据库锁一般分为乐观锁和悲观锁两种。乐观锁是指我们自认为“修改数据时因为线程冲突造成无法修改”的情况很少发生，所以采用给数据加版本号的形式修改数据的时候判断版本号和读取数据时的版本号是否一致来判断数据是否被其他线程修改。举一个sql例子：#读数据,假设读取到的version是1001select money,userid,version from users where userid=1;#修改数据update users set money=1500,version=1002 where userid=1 and version=1001;以上sql在update时由于判断了version=1001，如果成功则修改version为1002；当然为了避免版本号重复，可以采用uuid或雪花算法形式。

我们都知道用explain xxx分析sql语句的性能，但是具体从explain的结果怎么分析性能以及每个字段的含义你清楚吗？这里我做下总结记录，也是供自己以后参考。首先需要注意：MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETEexplain结果示例：mysql> explain select * from staff;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | staff | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set先上一个官方文档表格的中文版：Column含义id查询序号select_type查询类型table表名partitions匹配的分区typejoin类型prossible_keys可能会选择的索引key实际选择的索引key_len索引的长度ref与索引作比较的列rows要检索的行数(估算值)filtered查询条件过滤的行数的百分比Extra额外信息这是explain结果的各个字段，分别解释下含义：1. idSQL查询中的序列号。id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。2. select_type查询的类型，可以是下表的任何一种类型：select_type类型说明SIMPLE简单SELECT(不使用UNION或子查询)PRIMARY最外层的SELECTUNIONUNION中第二个或之后的SELECT语句DEPENDENT UNIONUNION中第二个或之后的SELECT语句取决于外面的查询UNION RESULTUNION的结果SUBQUERY子查询中的第一个SELECTDEPENDENT SUBQUERY子查询中的第一个SELECT, 取决于外面的查询DERIVED衍生表(FROM子句中的子查询)MATERIALIZED物化子查询UNCACHEABLE SUBQUERY结果集无法缓存的子查询，必须重新评估外部查询的每一行UNCACHEABLE UNIONUNION中第二个或之后的SELECT，属于无法缓存的子查询DEPENDENT 意味着使用了关联子查询。3. table查询的表名。不一定是实际存在的表名。可以为如下的值：<unionM,N>: 引用id为M和N UNION后的结果。<derivedN>: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。<subqueryN>: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。4. type（重要）这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。1、system表中只有一行数据或者是空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index2、const最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描3、eq_ref多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。eq_ref可用于使用'='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。eq_ref只能找到一行，而ref能找到多行。4、ref对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。ref可用于使用'='或'<=>'操作符作比较的索引列。5、 fulltext使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引6、ref_or_null跟ref类型类似，只是增加了null值的比较。实际用的不多。eg.SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;7、index_merge表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range8、unique_subquery用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的IN子查询的ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)9、index_subquery该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。10、range索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;SELECT * FROM tbl_name WHERE key_column IN (10,20,30);11、index索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。# 此表见有一个name列索引。# 因为查询的列name上建有索引，所以如果这样type走的是indexmysql> explain select name from testa;+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| 1 | SIMPLE | testa | index | NULL | idx_name | 33 | NULL | 2 | Using index |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+1 row in set# 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走ALL扫描，如下：mysql> explain select cusno from testa;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | testa | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set# 包含有未见索引的列mysql> explain select * from testa;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | testa | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set12、all全表扫描，性能最差。5. partitions版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。6. possible_keys查询可能使用到的索引都会在这里列出来7. key查询真正使用到的索引。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。8. key_len查询用到的索引长度（字节数）。如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。9. ref如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func10. rows（重要）rows 也是一个重要的字段。 这是mysql估算的需要扫描的行数（不是精确值）。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.11. filtered这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要12. extra（重要）EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:distinct：在select部分使用了distinc关键字Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.# 例如下面的例子:mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: indexpossible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 filtered: 100.00 Extra: Using index; Using filesort1 row in set, 1 warning (0.00 sec)我们的索引是KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了. 例如:mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: indexpossible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)Using index"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错Using temporary查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.除此之外还有其他值，这里就不一一一列举了。通过以上这些总结，以后我们看到explain的结果，就知道该从哪些字段值去分析sql语句的执行效率了。来源：https://www.jianshu.com/p/8fab76bbf448

 前言：         本篇我们将为大家讲解SQL性能的分析工具，而只有熟练的掌握了性能分析的工具，才可以更好的对SQL语句进行优化。虽然我们在自己练习的时候对这种优化感知并不明显，但是如果我们要处理几千几万条数据，那么这种优化带来的感知就会很强，因此我们要学好SQL语句的性能分析的工具，熟练掌握SQL的优化，才可以更加有把握解决现实生活中的实际问题  SQL执行频率：         SQL执行频率是指在一定时间范围内某个SQL语句在数据库中被执行的次数。SQL执行频率的高低可以反映出该语句在实际业务中的重要性和影响力。          对于一个复杂的应用系统，其数据库中可能存在大量的SQL语句，有些语句在实际使用中频率较高，有些则可能很少使用或者仅用于特定情况。对于频率较高的SQL语句，如果它们的执行效率低下，可能会导致数据库性能下降，影响整个系统的运行效率。          因此，在进行SQL优化时，从SQL执行频率的角度出发，通常会优先考虑优化那些执行频率较高的查询语句。这些查询语句可能涉及到的数据表和字段也是比较重要的，需要认真进行索引设计和数据库结构优化。          同时，SQL执行频率也可以作为数据库性能监控的一个指标。通过对SQL执行频率的监控、统计和分析，可以及时发现系统中执行频率较高的SQL语句，定位并解决数据库性能问题。  查询方法：         MySQL客户端连接成功之后，通过 show [session | global] status 命令可以提供服务器状态信息，通过如下指令就可以当前数据库中 INSERT , UPDATE ,  DELETE , SELECT 的访问频次：  SHOW GLOBAL STATUS LIKE 'COM_______'; 补充概念：服务器状态信息          MySQL服务器状态信息是指数据库服务器当前的状态信息，包括各种性能指标、资源使用情况、连接信息、缓存和锁状态等。通过查看这些状态信息，可以了解数据库服务器的工作状态、瓶颈问题和性能瓶颈等，帮助我们进行系统优化和调整。  我们在 DataGrip 中进行查询：   这些查询结果分别为：  Com_binlog：   执行 BINLOG 操作的次数，即写二进制日志的次数； Com_commit： 执行 COMMIT 操作的次数，即提交事务的次数； Com_delete：   执行 DELETE 操作的次数，即删除数据的次数； Com_import：   执行 LOAD DATA 导入数据的次数； Com_insert：    执行 INSERT 操作的次数，即插入数据的次数； Com_repair：    执行 REPAIR TABLE 修复表的次数； Com_revoke：  执行 REVOKE 操作的次数，即取消授权的次数； Com_select：   执行 SELECT 操作的次数，即查询的次数； Com_signal：   执行 SIGNAL 操作的次数； Com_update： 执行 UPDATE 操作的次数，即更新数据的次数； Com_xa_end：执行 XA END 操作的次数，即提交分布式事务的次数。 这些命令在 MySQL 中具有不同的作用和用途，通过统计执行这些命令的次数，可以更加全面地了解 MySQL 服务器的工作状态和性能瓶颈，从而进行数据库性能优化和故障排查。   tips:  在 MySQL 中，执行一次 SELECT 查询语句会生成多个命令请求，包括 Com_select、Handler_read_key、Handler_read_first、Handler_read_next 等。这些命令请求都会被累加到相应的命令计数器中。  如果在执行一次 SELECT 查询语句后，该命令计数器的值会增加 n，这是因为生成了 n 个命令请求。因此，如果你在 DataGrip 中执行多次 show global status 命令，每次命令执行都会使 com_select 计数器的值增加 n。  通过查询指令，我们可以快速的知道目前数据库哪些类型的语句占据数据库的绝大部分，也就确定了优先优化目标。   那我们知道了优化的主要目标是哪类语句，我们又如何找出要对这类语句中的哪些语句进行优化呢？我们可以采用慢查询日志  慢查询日志：         慢查询日志（slow query log）是 MySQL 中的一项功能，用于记录执行时间超过特定阈值的 SQL 语句的日志。慢查询日志包括每个 SQL 语句的执行时间、扫描的行数、使用的索引、执行的方式等信息。通过分析慢查询日志，可以定位数据库的性能问题，优化查询语句或数据库结构来提升系统性能。          MySQL 提供了配置慢查询日志的相关参数，包括慢查询阈值、慢查询日志文件名、日志格式等。          默认情况下，MySQL 没有开启慢查询日志。(这里是基于linux的，Linux会自动关闭)需要在 MySQL 配置文件（/ect/my.cnf）中设置 slow_query_log 参数为 ON，以开启慢查询日志的记录。另外，还需要设置 long_query_time 参数为一个时间阈值，单位为秒。当 SQL 查询的执行时间超过该阈值时，MySQL 会将该查询记录到慢查询日志中。          通过慢查询日志，可以对具体的 SQL 查询语句进行性能分析和优化。一些常见的优化手段包括：修改查询语句的语法；创建或修改索引；优化表结构等。除此之外，还可以利用慢查询日志来监控数据库系统的性能瓶颈，分析表负载、索引使用情况、数据分布等方面的信息，以进一步完善数据库的性能和可用性。  检查慢查询日志是否开启：  SHOW VARIABLES LIKE 'SLOW_QUARY_LOG'; 运行结果：   没有开启的需要在Datagrip中配置如下信息：  SET GLOBAL slow_query_log = 1;  # 开启慢查询日志   SET GLOBAL long_query_time = 1;  # 设置慢查询阈值为 1 秒钟 设置慢查询阈值的意义是：只要待查询的语句超过了设定阈值，我们就会把他记入慢查询日志之中，供后续我们进行针对优化。 开启之后我们可以看到   第一个是确定慢查询表已经开启记录，第二个则是告诉我们慢查询的表名。  那么我们可以直观的在文件资源管理器中查看慢查询表：   点击进入Data：   这个就是我们的慢查询表。  需要注意的是Data基本上都会被隐藏，需要我们先对这个文件以管理员模式进行访问，如果还是不显示的话，我们就在MySQL客户端执行这条语句：   SELECT @@datadir; 点击执行后，运行结果就是Data的地址，我们直接访问就可以了     慢查询表：    我们尝试执行这样一句：  select sleep (10); 执行结束后，我们查看慢查询表：   我们可以看到有关于这条语句的执行用户，使用的哪一个数据库，以及执行的语句都会被记录下来。  profile： MySQL的`PROFILE`是一个可以用来分析执行查询语句的工具。它可以让我们更好地了解查询执行过程中的各种时间和资源开销，包括：  1. 执行查询语句所需的时间 2. 查询语句返回的行数和大小 3. 系统在执行查询语句时所使用的资源，如CPU时间、磁盘和内存使用情况等 使用`PROFILE`可以帮助我们优化查询语句，发现慢查询问题，以及更好地了解MySQL数据库引擎执行查询的方式。  简单的来讲，使用profile可以告诉我们具体的每一条语句耗时多少，具体耗时在哪个环节，让我们可以做出更加具体的优化。  但是在使用之前，我们还要查询当前的MySQL是否支持profile  select @@have_profiling;   通过查询我们可以看到目前是支持profile的，而默认情况下profile是关闭的，因此我们再次执行语句，查询当前profiling是否开启：  select @@profiling;  于是我们调用下面的语句开启profiling  set profiling =1; 运行结果：  profile各个指令： 1.查看每一条SQL的耗时基本情况  show profiles; 2.查看指定query_id的SQL语句的各阶段耗时：  show profile for query query_id; 3.查看指定query_if的SQL语句CPU使用情况：  show profile cpu for query query_id; 我们用语句1来举一个例子：  我们执行语句：  select name from emp where id =1;   show profiles ;  这段SQL语句的作用大致如下：  1. 第一行查询上一个查询语句的执行结果中是否包含警告信息。 2. 第二行查询当前所在的数据库名称。 3. 第三行和第四行都是执行`SHOW WARNINGS`，查询语句是否产生了警告信息。 4. 第五行和第六行分别设置`net_write_timeout`和`SQL_SELECT_LIMIT`的值。 5. 第七行查询`emp`表中`id`为1的员工的姓名。 6. 第八行和第九行查询当前会话的事务隔离级别。 7. 第十一行将当前会话的事务隔离级别设置为读写模式。 8. 第十三行查询当前会话的事务隔离级别是否被设置为只读模式。 9. 第十四行和第十五行执行`SHOW WARNINGS`语句查询语句是否产生了警告信息。 10. 第十六行和第十七行都是重新设置`net_write_timeout`和`SQL_SELECT_LIMIT`的默认值。  总之，`SHOW WARNINGS`语句可以用来查看最后一次执行的查询语句是否产生了警告信息，警告信息可以包括查询语句执行过程中产生的一些异常和错误信息。  总结：         本篇我们讲解如何利用 SQL执行频率，查询慢日志，profile来确定需要对哪些句子进行优化他们分别起到了：哪一部分需要被优化，哪些语句需要被优化，如何对语句进行更具体的优化的作用，但这三个也只是从时间角度粗略的评判哪些语句需要被优化，而下一篇我们将会讲解explain，他提供了查看执行计划的功能，在实际生活中我们也经常通过它来评判语句性能，因此大家要做好准备，熟练的掌握这一篇的内容，充满信心的学习下一篇章。  如果我的内容对你有帮助，请点赞，评论，收藏。创作不易，大家的支持就是我坚持下去的动力！ ———————————————— 版权声明：本文为CSDN博主「我是一盘牛肉」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/fckbb/article/details/131260572 

 一、MySQL行列转换 1.数据准备操作 👉:传送门💖数据准备操作💖  2.行转列 1.1为何进行行转列？ 👉:传送门💖1.1为何进行行转列？💖  1.2 行转列有两个意思：1.表内的行转列 2.跨表的行转列 👉:传送门💖1.2 行转列的两个意思💖  3.行转列的思路：行变少，列变多 3.1 如何进行行转列：增加字段，进行聚合(行变少) 👉:传送门💖3.1如何进行行转列💖  4.行转列的实操 4.1 通用的行转列(Mysql和Oracle都能用) 👉:传送门💖4.1通用的行转列(Mysql和Oracle都能用)💖  4.1.1想在结果中加入学生名字 👉:传送门💖4.1.1在结果中加入学生名字)💖  4.1.1.1加入名字的方法1： 4.1.1.1 加入名字的方法2： select distinct t1.user_name ,t0.*    -- 如果不加distinct，因为t1表每个ID对应多个名字，所以最终结过就是，名字重复几次，结果就有几行重复 from (     select              user_id '学生ID',             max(case when course = '语文' then score end) '语文',             max(case when course = '数学' then score end) '数学',             max(case when course = '英语' then score end) '英语'     from              table_grade     group by              user_id ) t0left join table_grade t1 on t0.学生ID  = t1.user_id;     -- 此处的t0.学生ID,因为前面设置了别名，所以此处也应该使用别名,不然就会发生错误:Unknown column 't0.user_id' in 'on clause' 4.2 私有方法的行转列(Mysql用) select user_id '学生ID',          max(if(course = '语文',score,null)) '语文',          max(if(course = '数学',score,null)) '数学',          max(if(course = '英语',score,null)) '英语' from table_grade group by user_id  4.2.1 添加名字的两种方法 select user_id '学生ID',             (select max(user_name) from table_grade where table_grade.user_id =t.user_id ) user_name,             max(if(course = '语文',score,null)) '语文',             max(if(course = '数学',score,null)) '数学',             max(if(course = '英语',score,null)) '英语' from table_grade t group by user_id  select distinct t1.user_name,t0.* from  (     select user_id '学生ID',                     max(if(course = '语文',score,null)) '语文',                     max(case when course = '数学' then score end) '数学',                     max(if(course = '英语',score,null)) '英语'      from table_grade        group by user_id ) t0 left join table_grade t1 on t0.学生ID = t1.user_id; 3.列转行     a    b    c              1                                          1   a 2                     1   b 3                     1   c                       2   a                                             2        b                                             2        c                                              3        a                                             3        b                                             3        c 列转行如上图所示，左边变成右边  右图又称为纵表，这种纵表在大数据中适合用工具hbase进行列式存储，里面存的就是键值对，右图的左列是键、右列是值 纵表适合存储，横表适合分析 底层明细数据，适合列式存储 3.1列转行思路：行变多 用union select(查询)能表达的关系是并差交笛卡尔积 集合运算是 并差交笛卡尔积 关系运算是 投影连接除 大数据一次处理一个集合(set),不是一个记录(record) 3.2 列转行实操 3.2.1 数据准备 建个横表  create table table_grade_wide as(       select user_id '学生ID',           max(if(course = '语文',score,null)) '语文',           max(if(course = '数学',score,null)) '数学',           max(if(course = '英语',score,null)) '英语'       from table_grade       group by user_id )  alter table table_grade_wide change user_id id  int; 3.2.2 实操 select * from(     select 学生ID,'语文' course,语文 score from table_grade_wide -- 只需要在第一个select字段中修改别名就好了,因为union的时候，前后的所有的select的列的类型和个数是一致的     union   --     select 学生ID,'数学',数学 from table_grade_wide     union      select 学生ID,'英语',英语 from table_grade_wide ) a where score is not null     -- 因为有的同学只考了其中几门课 order by 1;   -- 按照最后结果的第一列进行排序 小结 好了，MySQL的行列转换到这里就要告一段落了，希望大家通过上一篇文章——行列转换（一）• MySQL版以及本篇文章的学习，应该对MySQL的行列转换有了了解，学习是永无止境的，接下来，我们会按照这样的方式为大家讲述Oracle中的行列转换，如果大家对于文章的内容、排版等各个方面有什么好的想法，都可以进行沟通交流，也希望我的博客中的内容能为大家在学习的道路上提供一点点助力，我们一起学习，一起进步 ———————————————— 版权声明：本文为CSDN博主「爱书不爱输的程序猿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_40332045/article/details/131488624 

 🍔函数 是指一段可以直接被另一段程序调用的程序或代码  ⭐字符串函数  🎈字符串拼接函数 concat('s1','s2'); 🎈把字符串全部变为小写 select lower('str'); 🎈把字符串全部变为大写 select upper('str'); 🎈字符串左填充 select lpad('str',length,'-');  -- 在str左边用-进行填充，达到长度为n 🎈字符串右填充 select rpad('str',length,'-');  -- 在str右边用-进行填充，达到长度为n 🎈去掉字符串头部和尾部的空格 select trim('str'); 1  🎈字符串截取 select substring('str',截取起始位置,截取长度); 🏀应用 由于业务需求变化，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0 （比如1好员工的工号应该是00001）  update emp set worknumber = lpad(worknumber,5,'0');  --            更新的字段（工号） ⭐数值函数  🎈向上取整 select ceil(number); 🎈向下取整 select floor(number); 🎈返回x/y的模 select mod(num1,num2); 🎈求随机数 是0~1之间的随机数  select rand(); 🎈四舍五入，并且保留n位小数 对number进行四舍五入，并且保留length位小数  select round(number,length); 🏀应用 通过数据库的函数，生成一个六位数的随机验证码  select lpad(round()*1000000,0),6,'0'); 1 ⭐日期函数  🎈返回当前日期 select curdate(); 🎈返回当前时间 select curtime(); 🎈返回当前日期+时间 select now(); 🎈获取指定date的年份 select YEAR(date); 🎈获取指定date的月 select MONTH(date); 🎈获取指定date的天 select DAY(date); 🎈返回一个时间，是date向后推迟number个DAY（或MONTH，YEAR） select date_add(now(),INTERVAL 70 MONTH); 🎈两个指定时间中相差的天数 select datediff('2021-12-01','2022-12-01'); 🏀应用 查询所有员工的入职天数，并根据入职天数倒序排序  select name datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc; 1 解释：entrydays是函数的别名，这样子就不用写一串函数了，order by 后面的是排序方式  ⭐流程控制函数  🎈进行判断 如果条件表达式的结果是true，那么返回OK，否则返回Error  select if(条件表达式,'OK','Error'); 🎈如果第一个值为null，那么返回第二个值，否则返回第一个值 select ifnull('OK','Default');   🎈case语句 select       name,      ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end )    from emp; 🍔约束 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据 目的：保证数据库中数据的正确，有效性和完整性 分类：  🎈主键约束 主键约束（Primary Key Constraint）：主键约束用于定义一个唯一标识来标识表中的每一行。它要求主键列的值唯一且非空。主键可以由一个或多个列组成。  "column"是指表中的一个字段,"datatype"是数据类型  CREATE TABLE table_name (     column1 datatype,     column2 datatype,     ...     primary key (column1, column2, ...) ); 🎈唯一约束 唯一约束（Unique Constraint）：唯一约束用于确保表中的某个列或一组列的值是唯一的。唯一约束允许空值（NULL），但对于非空值，要求其在列中是唯一的。  "column"是指表中的一个字段,"datatype"是数据类型  CREATE TABLE table_name (     column1 datatype,     column2 datatype,     ...     unique (column1, column2, ...) ); 🎈外键约束 外键约束（Foreign Key Constraint）：外键约束用于建立表与表之间的关联关系。用来让两张表之间建立连接，从而保证数据的一致性和完整性   "column"是指表中的一个字段,"datatype"是数据类型  🏀添加外键 情况1：表结构没有创建好（直接在表里面进行添加）  CREATE TABLE table_name2 (     column1 datatype primary key,     column2 datatype,     ...     foreign key (column2) references table_name1(column1) );  情况2：表结构创建好了  alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名) ; 🏀删除外键 alter table 表名 drop foreign key 外键名称; 🎈检测约束 检查约束（Check Constraint）：检查约束用于限制列中的值必须满足指定的条件。可以使用逻辑运算符、比较运算符和函数等来定义检查约束条件。  "column"是指表中的一个字段,"datatype"是数据类型  CREATE TABLE table_name (     column1 datatype,     column2 datatype check (condition),     ... ); 🎈非空约束 非空约束（Not Null Constraint）：非空约束用于确保表中的某个列不接受空值（NULL）。  "column"是指表中的一个字段,"datatype"是数据类型  CREATE TABLE table_name (     column1 datatype not null,     column2 datatype,     ... ); 🏀样例 create table user(      id int primary key auto_increment comment '主键',      name varchar(10) not null unique comment '姓名',      age int check ( age > 0 && age < 30 ) comment '年龄',      status char(1) default '1' comment '状态',      gender char(1) comment '性别'  ) comment '用户表';  插入数据  insert into user(name,age,status,gender) values ('Tom1','19','1','男'),('Tom2','25','0','男'); 1 ⭐总结  ———————————————— 版权声明：本文为CSDN博主「在下小吉.」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/m0_72853403/article/details/131222460 

MySQL主从复制原理主服务器数据库的每次操作都会记录在其二进制文件mysql-bin.xxx（该文件可以在mysql目录下的data目录中看到）中，从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件，并将文件内容写入到自己本地的中继日志relay-log文件中，然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句MySQL主从同步的作用1、可以作为备份机制，相当于热备份2、可以用来做读写分离，均衡数据库负载项目场景1、主服务器10.10.20.111，其中已经有数据库且库中有表、函数以及存储过程2、从服务器10.10.20.116，空的啥也没有准备工作主从服务器需要有相同的初态1、将主服务器要同步的数据库枷锁，避免同步时数据发生改变mysql>use db;mysql>flush tables with read lock; 2、将主服务器数据库中数据导出mysql>mysqldump -uroot -pxxxx db > db.sql;这个命令是导出数据库中所有表结构和数据，如果要导出函数和存储过程的话使用mysql>mysqldump -R -ndt db -uroot -pxxxx > db.sql其他关于mysql导入导出命令的戳这里3、备份完成后，解锁主服务器数据库mysql>unlock tables;4、将初始数据导入从服务器数据库mysql>create database db;mysql>use db;mysql>source db.sql;好了，现在主从服务器拥有一样的初态了主服务器配置1、修改MySQL配置vi /etc/my.cnf在[mysqld]中添加#主数据库端ID号server_id = 1 #开启二进制日志 log-bin = mysql-bin #需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可 binlog-do-db = db #将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中 log-slave-updates #控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) sync_binlog = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突auto_increment_offset = 1 #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突auto_increment_increment = 1 #二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除 expire_logs_days = 7 #将函数复制到slave log_bin_trust_function_creators = 1 2、重启MySQL，创建允许从服务器同步数据的账户#创建slave账号account，密码123456mysql>grant replication slave on *.* to 'account'@'10.10.20.116' identified by '123456';#更新数据库权限mysql>flush privileges;3、查看主服务器状态mysql>show master status\G;***************** 1. row **************** File: mysql-bin.000033 #当前记录的日志 Position: 337523 #日志中记录的位置 Binlog_Do_DB: Binlog_Ignore_DB: 执行完这个步骤后不要再操作主服务器数据库了，防止其状态值发生变化从服务器配置1、修改MySQL配置vi /etc/my.cnf在[mysqld]中添加server_id = 2log-bin = mysql-binlog-slave-updatessync_binlog = 0#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作innodb_flush_log_at_trx_commit = 0 #指定slave要复制哪个库replicate-do-db = db #MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据slave-net-timeout = 60 log_bin_trust_function_creators = 12、执行同步命令#执行同步命令，设置主服务器ip，同步账号密码，同步位置mysql>change master to master_host='10.10.20.111',master_user='account',master_password='123456',master_log_file='mysql-bin.000033',master_log_pos=337523;#开启同步功能mysql>start slave;3、查看从服务器状态mysql>show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.20.111 Master_User: account Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000033 Read_Master_Log_Pos: 337523 Relay_Log_File: db2-relay-bin.000002 Relay_Log_Pos: 337686 Relay_Master_Log_File: mysql-bin.000033 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ...Slave_IO_Running及Slave_SQL_Running进程必须正常运行，即Yes状态，否则说明同步失败若失败查看mysql错误日志中具体报错详情来进行问题定位最后可以去主服务器上的数据库中创建表或者更新表数据来测试同步链接：https://www.jianshu.com/p/b0cf461451fb

 1 读写分离的概念 读写分离是指将数据库的读和写操作分不到不同的数据库节点上。主服务器负责处理写操作和实时性要求较高的读操作，从服务器负责处理读操作。  读写分离减缓了数据库锁的争用，可以大幅提高读性能，小幅提高写的性能，非常适合读请求非常多的场景。读写分离会依赖到Mysql的主从复制的功能，因此也能够顺带着解决了数据库单点故障的问题，基于主从切换可以实现数据库的高可用性。  读写分离的方案中，一主一从、一主多从、多主多从都是可以的，比较灵活。  2 读写分离的实现 项目中读写分离常见的实现方式有两种，一种是直接在客户端的实现，另一种是第三方数据库中间件代理的实现。  基于客户端的实现也就是在应用程序/代码层面的实现。我们可以自己写一个AOP拦截器，然后对配置多个读、写数据源，利用AOP拦截技术对到达的读/写请求进行解析（可以通过方法名之类的进行拦截），针对性地选择不同的数据源，将请求分发到不同的数据库中，从而实现读写分离，，不引入任何jar包，也不需要引入任何中间组件，节省了很多运维的成本，但是需要自己编程。  基于中间件的实现推荐引入的中间件jar的方式而不是手动编程的方式，也称为组件式，推荐使用sharding-jdbc的jar包，这些jar包中已经包含了读写分离的各种逻辑，只需要开发人员少量的配置即可非常方便的实现读写分离，相比于手动实现，节省了很多编程的成本，sharding-jdbc还提供了多种不同的从库负载均衡策略，以及强制路由策略。  基于外部数据库中间件实现第二种就是基于外部数据库中间件来帮助我们实现读写分离，比如MySQL Router（官方）、Atlas（基于 MySQL Proxy）、Maxscale、MyCat。这种方式类似于在应用层和数据库层之间添加了一个独立的代理层，应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。这种方式不会侵入客户端代码，但是需要额外的部署第三方组件，会增加运维成本，让系统架构变得更加复杂。  *推荐使用sharding-jdbc的jar包来实现读写分离。sharding-jdbc官网的介绍：Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。 它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它虽然也是一个数据库中间件，但是使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。  sharding-jdbc官网也提供了非常详细且简单的实现读写分离的方案：https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/usage/read-write-splitting/。  注意，读写分离依赖于Mysql的主从复制，而sharding-jdbc等数据库中间件是不提供主从复制的功能的，这是Mysql的原生实现。  3 读写分离的问题 因为读写分离依赖主从复制，因此读写分离的问题实际上就是主从复制的问题，那就是主备延迟的问题。  没有特别好的解决办法，主要思路有下面这些：  可以在数据同步一定时间之后再从备库读取。 将那些必须获取最新数据的读请求都交给主库处理。 一个主库分为多个主库，分担主库的写请求，减少单个库的binlog日志产生速度。 打开 MySQL 从库的并行复制，sql_thread从一个变成多个，这需要Mysql5.6及其以上的版本支持。 ———————————————— 原文链接：https://blog.csdn.net/weixin_43767015/article/details/120075184 

当我们业务数据库表中的数据越来越多，如果你也和我遇到了以下类似场景，那让我们一起来解决这个问题数据的插入,查询时长较长后续业务需求的扩展 在表中新增字段 影响较大表中的数据并不是所有的都为有效数据  需求只查询时间区间内的评估表数据体量我们可以从表容量/磁盘空间/实例容量三方面评估数据体量，接下来让我们分别展开来看看表容量表容量主要从表的记录数、平均长度、增长量、读写量、总大小量进行评估。一般对于OLTP的表，建议单表不要超过2000W行数据量，总大小15G以内。访问量：单表读写量在1600/s以内查询行数据的方式：我们一般查询表数据有多少数据时用到的经典sql语句如下：select count(*) from table select count(1) from table但是当数据量过大的时候，这样的查询就可能会超时，所以我们要换一种查询方式use 库名 show table status like '表名' ;  或：show table status like '表名'\G ;上述方法不仅可以查询表的数据，还可以输出表的详细信息 , 加 \G 可以格式化输出。包括表名 存储引擎 版本 行数 每行的字节数等等，大家可以自行试一下哈磁盘空间查看指定数据库容量大小select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;查询单个库中所有表磁盘占用大小select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='mysql' order by data_length desc, index_length desc;查询出的结果如下：建议数据量占磁盘使用率的70%以内。同时，对于一些数据增长较快，可以考虑使用大的慢盘进行数据归档（归档可以参考方案三）实例容量MySQL是基于线程的服务模型，因此在一些并发较高的场景下，单实例并不能充分利用服务器的CPU资源，吞吐量反而会卡在mysql层，可以根据业务考虑自己的实例模式出现问题的原因上面我们已经查到我们数据表的体量了 那么为什么单表数据量越大 业务的执行效率就越慢 根本原因是什么呢？一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。❝大家是否还记得，一个B+树大概可以存放多少数据量呢？❞InnoDB存储引擎最小储存单元是页，一页大小就是16k。B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。如何解决单表数据量太大，查询变慢的问题知道了根本原因之后，我们就需要考虑如何优化数据库来解决问题了这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案方案一：数据表分区为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率 分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。推荐：Java面试题我们首先看一下分区有什么优缺点：表分区有什么好处？与单个磁盘或文件系统分区相比，可以存储更多的数据。对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。表分区的限制因素一个表最多只能有1024个分区。MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。分区表中无法使用外键约束。MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。在进行分区之前可以用如下方法 看下数据库表是否支持分区哈mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | have_partitioning | YES   | +-------------------+-------+ 1 row in set (0.00 sec)方案二：数据库分表为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率 mysql 分表分为两种 水平分表和垂直分表分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。推荐：Java面试题水平分表定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据垂直分表定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作知道了两个知识后，我们来看一下分库分表的方案1.取模方案：拆分之前，先预估一下数据量。比如用户表有4000w数据，现在要把这些数据分到4个表user1 user2  uesr3 user4。比如id = 17，17对4取模为1，加上 ，所以这条数据存到user2表。❝注意：进行水平拆分后的表要去掉auto_increment自增长。这时候的id可以用一个id 自增长临时表获得，或者使用  redis incr的方法。❞优点：数据均匀的分到各个表中，出现热点问题的概率很低。缺点：以后的数据扩容迁移比较困难难，当数据量变大之后，以前分到4个表现在要分到8个表，取模的值就变了，需要重新进行数据迁移。2.range 范围方案以范围进行拆分数据，就是在某个范围内的订单，存放到某个表中。比如id=12存放到user1表，id=1300万的存放到user2 表。优点：有利于将来对数据的扩容缺点：如果热点数据都存在一个表中，则压力都在一个表中，其他表没有压力。❝我们看到以上两种方案 都存在缺点 但是却又是互补的，那么我们将这两个方案结合会怎样呢？❞3.hash取模和range方案结合如下图 我们可以看到 group 组存放id 为0~4000万的数据，然后有三个数据库 DB0 DB1 DB2，DB0里面有四个数据库，DB1 和DB2 有三个数据库假如id为15000 然后对10取模（为啥对10 取模 因为有10个表），取0 然后 落在DB_0,然后在根据range 范围，落在Table_0 里面。总结：采用hash取模和range方案结合 既可以避免热点数据的问题，也有利于将来对数据的扩容我们已经了解了 mysql分区和分表的知识 那我们看一下这两个技术有何不同以及适用场景分区分表的区别1、实现方式上mysql的分表是真正的分表，一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件，一个.MYD数据文件，.MYI索引文件，.frm表结构分区不一样，一张大表进行分区后，他还是一张表，不会变成二张表，但是他存放数据的区块变多了。2、提高性能上分表重点是存取数据时，如何提高mysql并发能力上；而分区呢，如何突破磁盘的读写能力，从而达到提高mysql性能的目的。3、实现的难易度上1、分表的方法有很多，用merge来分表，是最简单的一种方式。这种方式根分区难易度差不多，并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。2、分区实现是比较简单的，建立分区表，根建平常的表没什么区别，并且对开代码端来说是透明的分区分表的联系1、都能提高mysql的性高，在高并发状态下都有一个良好的表现。2、分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等。推荐：Java面试题分库分表存在的问题1、事务问题在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。2、跨库跨表的join问题在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。3、额外的数据管理负担和数据运算压力额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。方案三：冷热归档为什么要冷热归档：其实原因和方案二类似，都是降低单表数据量，树的高度变低，查询经历的磁盘io变少，则可以提高效率 如果大家的业务数据，有明显的冷热区分，比如：只需要展示近一周或一个月的数据。那么这种情况这一周喝一个月的数据我们称之为热数据，其余数据为冷数据。那么我们可以将冷数据归档在其他的库表中，提高我们热数据的操作效率。接下来讲一下归档的过程创建归档表  创建的归档表 原则上要与原表保持一致归档表数据的初始化务增量数据处理过程据的获取过程转自：https://mp.weixin.qq.com/s/vCBYq1VoRRukIN3eTTGZww