数据科学汇集

mysql Workbench查看图形化执行计划数据示例假定有部门表(dept)、员工表(emp),它们的表结构和数据见如下脚本：-- Mysql 版本DROP TABLE IF EXISTS emp; DROP TABLE IF EXISTS dept; create table dept( deptno int unsigned auto_increment COMMENT '部门编号', dname varchar(15) COMMENT '部门名称',

workbench导出数据库报1109错的解决方法问题描述用mysqlWorkbench导出数据库时遇到Unknown table 'column_statistics' in information_schema (1109)错误，详见如下截图：问题分析该问题是由MySQL Workbench和Mysql数据库的版本不一致造成的。information_schema里的column_statistics视图是mysql 8之后才引进的。解决方法定义新命令新定义m...

Mysql 插入意向锁与自增锁备份锁日志锁插入意向锁Insert Intention Lock插入意向锁Insert intention locks是记录级别的，它通过“INSERT”关键词来向其它的事务传达插入的意向。插入意向锁针对的是将要插入的记录而不是已经存在的记录。下面我们通过例子来重现这个插入意向锁。示意案例--Step1 在会话1里对city表ID大于4079的记录进行更新操作。USE world;START TRANSACTION;SELECT * FROM c

记录锁Record Lock与间隔锁GAP Lock记录锁Record Lock记录锁Record Locks又称为行锁，它同时包含索引和间隔锁。记录锁可以是共享锁也可能是排他锁。可以通过performance_schema.data_locks表的LOCK_TYPE和LOCK_MODEL来查看。示意案例-- Step1 开始事务，更新city的Population字段，这里更新时通过辅助索引里的字段CountryCode。START TRANSACTION;UPDATE cityS

显式锁Explicit Table Lock与隐式锁Explicit Table Lock显式锁Explicit Table Lock显式表锁(Explicit Table Locks)即通过命令lock tables对表进行锁定(共享或排他锁)、FLUSH TABLES WITH READ LOCK(共享锁)，直到用显式的命令UNLOCK TABLES语句才能解锁。当FLUSH TABLES WITH READ LOCK不接表名时会触发metadata lock。示意案例-- 锁定e

Mysql数据锁MetaData Lock元数据锁MetaData Lock元数据锁MetaData Locks的主要作用是在执行查询或者发起事务时元数据结构受到保护，即不被修改。MetaData Lock是表级锁。MetaData Lock是排他锁，当该锁存在时不能有其它的连接对表的模式进行修改。关于元数据锁的最大问题是空闲事务(因网络断开或者程序Bug而导致的COMMIT/ROLLBACK语句没有传给数据库，也没有释放线程而导致线上事务锁定等待严重、连接数暴涨的情况)会阻止DDL语句

Mysql 清空缓存锁Flush Locks清空缓存锁Flush Locksflush table的功能是关闭所有打开的表，强制关闭所有正在使用的表，并刷新准备好的语句缓存。这种操作需要用户拥有FLUSH_TABLES、RELOAD权限。flush tables在已经锁定表（对应LOCK TABLES ... READ语句）的情况下不能使用，刷新和锁定表一般用FLUSH TABLEStbl_name... WITH READ LOCK语句。在我们需要做备份时为了同时防止有新数据写...

Mysql 用户自定义锁介绍锁简介当多个请求连接到数据库时，能保证同类查询执行时不相互冲突的机制即是锁。比如马路上的信号灯就是一个解决冲突的例子，在一定的时间内锁定了能通行的方向或者路线。MySQL里的锁实际上是一个处于处于授予或挂起状态锁请求。锁的访问级别锁的访问级别（Lock Access Levels）即定义锁以那种方式进入(使用)。主要有两种方式：共享的（shared）和排他的（exclusive）。共享锁是比较宽松的锁，即允许多个连接访问。而排他锁则只能指定一个连接访问的锁。有

Mysql 优化器之派生条件回移Derived Condition Pushdown详解派生条件回移优化概述Mysql 8.0.22之后的版本支持派生条件回移(Derived Condition Pushdown)优化。该优化可以减少派生表处理的行数从而提高查询执行的效率。比方说有如下查询：SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i >constant通过派生条件回移优化后类似如下形式（WHERE条件拿到派生表的里面）：.

MySQL 优化器之Index merge Multi-Range Read MRR与Batched Key Access介绍索引合并Index merge覆盖索引是在索引里即有查询时需要的字段，而在一个表上的查询往往条件会有多个组合且较为复杂，所以很难全部由覆盖索引包含到。那么此时我们就会用到索引合并（index merge）算法。该算法主要包含三类情况： 算法 应用场景 执行计划 Intersection .

Mysql 各种hash join算法讲解hash join的概述提到hash join之前自然得说Nest loop join，以两个表的关联为例，它其实是个双层循环，先遍历外层的表(n条)，再拿每次对应的值去匹配、循环遍历内部的表（M条）。这样显然会有M*n的计算复杂度。如果能将外部表先装载到内存，然后再做内部表的匹配、遍历，计算的复杂度就会大大降低，这就是hash join的思想。本文继续介绍hash join的其它几个算法On-Disk Hash Join、Grace Hash Join

Mysql hash join之classic hash join CHJ过程详解hash join的历史优化器里的hash join算法在SQL Server、Oracle、postgress等数据库早已实现，而Mysql在8.0.18之后才支持。在8.0.18之前mysql只支持嵌套循环关联(nested loop join),这其中最简单就是简易嵌套循环关联simple nestloop join，随后mysql做了改进进而支持block nestloop join, index nestlo

Mysql UTF8之utf8mb3 utf8mb4字符集概述字符集即是为了兼容各国的文字而做的编码，比如GB2312、GBK等，通用的编码我们用utf8。mysql(5.7及8.0)里utf8实际是用3个字节存储即对应utf8mb3，而真正意义上的utf8对应的是4个字节存储即对应utf8mb4。utf8mb3和utf8mb4的比较： 比较内容 utf8mb3 utf8mb4 存储最大字节数 3

需求说明基于某个起始日期值，比如‘2021-9-1’，生成其之后的30天数据，结果类似：2021-09-02、2021-09-03、2021-09-04...问题分析该需要看似简单，但想不借助交互式SQL即WHILE循环的方式，则得考虑借助自增辅助表来实现。自增辅助表的构造有几种方法，详见代码处的介绍：-- # Way1 借助系统表作为自增辅助SELECT DATE_FORMAT(DATE_SUB('2021-09-01', INTERVAL -pos DAY), '%Y-%m-%d

Mysql查询窗口函数之按序号取元素详解需求概述查询以课程(course)分区以分数(score)逆序的窗口里的第一个、最后一个和第2个元素对应的值。示例表数据见下：SELECT '数学' course, 40 score, '张三' name UNIONSELECT '数学' course, 60 score, '李四' name UNIONSELECT '数学' course, 70 score, '王二' name UNIONSELECT '数学' course, 55 s...

Mysql索引基本概念及案例总结Key与Index关键字索引是键的列表，当我们定义一个key(外键除外，一般是PRIMARY KEY或者KEY形式)时就会产生对应的索引。一般情况下KEY和INDEX关键字可以互换。唯一索引的说明唯一索引是只允许索引中每个值只能对应一行的索引。这里唯一分两层含义，在用户层对应唯一约束，即不能将重复的值插入定义为唯一的字段里，而对于数据库而言唯一性对应的是唯一索引检查字段的不重复性。特别的:对于字段允许为NULL的唯一索引，NULL可以多次插入进去，而不

Mysql数据唯一索引与唯一约束案例总结唯一约束的说明唯一约束是约束(CONSTRAINT)里的一种，常见的还有主键、外检、默认值、是否为空、检查等。唯一约束即限制某个或某些字段具有唯一性（不能重复）。唯一索引的说明唯一索引是只允许索引中的每个值对应记录的一行，这就像身份标识一样，每个人都只有一个。代码验证创建表时指定唯一约束CREATE TABLE tb_unique(id int,name varchar(20) UNIQUE,addr varchar(30),ag

数据类型概述Mysql里的数据类型主要有数值型(整形、浮点型)、时间类型、字符串类型、JSON类型、坐标类型、混合类型(整数或字符串)。本文主要介绍字符串和二进制类型的使用。字符串与二进制类型概述字符串是和二进制类型是比较灵活的可以存放任意数据的灵活类型。二进制类型和字符串的差别是字符串在存储时指定了字符集，即mysql能“读懂”这个字符串，而二级制则存的是裸的数据，需要特定或者专有的方法（程序）去解析。行容量限制因为mysql里每一个行的大小被限制在64k，所以varchar、...

Mysql数据类型之浮点与二进制型数据数据类型概述Mysql里的数据类型主要有数值型(整形、浮点型)、时间类型、字符串类型、JSON类型、坐标类型、混合类型(整数或字符串)。数值型范围表格 数据类型 存储位数 数据范围 tinyint 1 Signed: -128–127 Unsigned: 0–255 smallint 2 .

Mysql数据类型之整数型篇数据类型概述Mysql里的数据类型主要有数值型(整形、浮点型)、时间类型、字符串类型、JSON类型、坐标类型、混合类型(整数或字符串)。数值型范围表格 数据类型 存储位数 数据范围 tinyint 1 有符号: -128–127 无符号: 0–255 smallint 2 有符号: .

SQL时间错位与还原生成案例需求描述1 原表T1某条记录(记做r1，相邻下一条为r2)的下一行记录的STARTDATE小于上一行ENDDATE，针对这样的记录做转换即：r1的STARTDATE保持不变，ENDDATE为r1的STARTDATE-1r2的STARTDATE为r1的ENDDATE，ENDDATE为r1的ENDDATE2 如果原表T1不存在相邻行“时间重叠”（即为1的定义）时保持原有数据不变。...

Mysql8 NDB集群二进制方式配置图解声明与简介本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍mysql的NDB集群的配置及操作。注：1 当前的NDB SQL节点采用二进制的方式（免安装）安装在VVMware虚拟机内。 2 当前虚拟机内mysql运行在Linux的Centos 8内，版本号是 8.0.23、端口号3306。 3 host_128...

Mysql8 InnoDB集群配置图解声明与简介本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍mysql InnoDB Cluster(集群)的配置及操作。注： 1当前已经安装配置好单主MGR集群，详见Mysql 8 MGR集群单主配置图解、Mysql8 MGR集群操作图解 2 当前各节点内mysql版本是8.0.23，其中host_12...

Mysql8 MGR集群操作图解声明与简介本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍mysql的MGR集群的操作。MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性。其特点如下：高一致性：基于分布式paxos协议实现组复制，保证数据一致...

Mysql 8 MGR集群单主配置图解声明与简介本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍mysql的MGR集群的配置。MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性。其特点如下：高一致性：基于分布式paxos协议实现组复制，保证数...

Mysql 8主从复制配置图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍mysql的主从的配置。注：1 当前主服务器的操作系统是Win7、IP为192.168.0.100、数据库版本是Mysql 8.0.23、端口号3309。2 当前从服务器的操作系统是CentOS 8、IP为192.168.111.128、数据库版本是Mysql 8.0.23、端口号33...

Mysql 8解压配置图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。本文主要介绍windows下配置解压版（无安装）mysql。注：1 当前操作系统Win7、Mysql 8.0.23，win 10等其它windows系统类似。 2 当前系统内已安装了mariadb(mysql分支)其端口号为3306，当前mysql 8.0.23指定端口号为3309，服务名为...

Xtrabackup增量备份还原图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。XtraBackup是Percona提供的免费、在线、开源、完整的数据库备份解决方案，适用于所有版本的Percona Server for MySQL和MySQL。Percona XtraBackup在事务系统上执行在线非阻塞、紧致压缩、高度安全的完整备份。准备验证思路1 对当...

xtrabackup备份还原图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。XtraBackup是Percona提供的免费、在线、开源、完整的数据库备份解决方案，适用于所有版本的Percona Server for MySQL和MySQL。Percona XtraBackup在事务系统上执行在线非阻塞、紧致压缩、高度安全的完整备份。准备验证思路1 对当前数...

Mysql备份工具xtrabackup安装图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。当前操作系统CentOS 8、Mysql 8.0.23，使用的xtrabackup 版本号是8.0.23-16。下载工具Step 1：下载链接 https://www.percona.com/downloads/Percona-XtraBackup-LATEST/...

Mysql only_full_group_by及Error 1055问题分析图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。问题描述ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tri...

Mysqlbackup 8增量备份还原图解声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。增量备份还原准备增量备份，又称为差异备份是基于某个全备而作的备份。它相对于全备份而言备份量较小，但备份和还原时必须依附这个全备。首先创建个表以验证增量（差异）备份，思路见下：在全备前建立一张表，并插入一条数据；然后做全备； 插入第二条数据，然后基于之前全备做增量(差异)...

Mysqlbackup 8.0.23 备份还原图解Mysqlbackup简介mysqlbackup是mysql 企业版里的一个数据库备份还原工具。它是一个多平台，高性能的工具，提供丰富的功能，如热(在线)备份，增量和差异备份，选择性备份和恢复，支持直接的云存储备份，备份加密和压缩等注：当前验证环境CentOS 8 X86 64、mysql 8.0.23、mysqlbackup 8.0.23Mysqlbackup准备-- 创建用户...

Mysql 8 逻辑升级8.0.21升到8.0.23 升级准备核对系统环境操作系统环境：centOS 8 X86 64 数据库环境由mysql 8.0.21升级到mysql 8.0.23 当前数据库服务无复制、集群情况，仅单个数据库实例。下载新的版本当前是以rpm包的形式安装mysql，所以需要选择相应的包文件。Step 1 找到mysql的归档版本：Step 2 找到对应数据库和操作系统版本的rpm包(这里...

Mysql 8 密码策略组件方式问题描述创建用户时提示密码不满足当前的策略定义，错误代码是[Err] 1819。Your password does not satisfy the current policy requirements解决方法首先查看当前的密码策略：-- 查看当前用户的密码策略SHOW VARIABLES LIKE 'validate_password%';由如上结果可见当前的密码策略级别是...

Mysqlbackup下载安装图解1 声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。2 Mysqlbackup简介mysqlbackup是mysql 企业版里的一个数据库备份还原工具。它是一个多平台，高性能的工具，提供丰富的功能，如热(在线)备份，增量和差异备份，选择性备份和恢复，支持直接的云存储备份，备份加密和压缩等注:当前实验版本，下载Win7，安装Cenos8...

Mysql物理逻辑备份概述1 声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。2 Mysql物理逻辑备份简介物理备份物理备份由存储数据库内容的目录和文件的原始副本组成,这种类型的备份适用于出现问题时需要快速恢复的大型、重要的数据库。逻辑备份逻辑备份保存表示为逻辑数据库结构(创建数据库、表的语句)和内容(插入语句或分隔文本文件)的信息。这种类型的备份适用于数据量较小...

Mysql数据目录初识1 声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。2 数据目录简介存放Mysql管理信息的目录叫做（系统）数据目录。我们可以看到安装mysql时会有些特定的系统数据库，比如mysql、information_schema、sys等。3数据目录详解Mysql，该目录对应于mysql系统架构，包含mysql服务器运行时所需的信息。该...

Mysql数据库数据目录迁移1 声明本文的数据来自网络，部分代码也有所参照，这里做了注释和延伸，旨在技术交流，如有冒犯之处请联系博主及时处理。2 数据目录迁移简介一般我们是通过rpm包的方式安装mysql,自己源码编译的较少。但这种方式安装数据库时默认的数据库数据存储目录在/var/lib/mysql或者其它类似的系统级目录。大多数情况下我们的系统盘因为各种原因分配的不够大，这时提前安排数据库的配额或者单独给数据库分配空间就...

MariaDB架构图与执行流程概述1MariaDB执行流程以客户端访问MariaDB数据库服务器为例，它的执行流程见下：当一个客户端(应用程序、GUI工具)连接MariaDB时会产生一个基于该客户端主机名、用户名、密码的认证。该认证以插件的实用。 如果登录成功，客户端就可以给数据库发送SQL了。 解析器负责翻译传来的SQL字符串。 数据库检查客户端是否有对请求的操作有权限.(比如DML操作的DELETE某个表时)。 如果某个查询已经...