Mysql

MySQL 中主要的日志类型 MySQL 提供了多种日志类型，每种都有其特定的目的和格式。理解这些日志对于数据库管理、性能调优、数据恢复和故障排查至关重要。 下面我将详细介绍 MySQL 中主要的日志类型及其格式 总结概览 日志类型 主要目的 格式 是否默认开启 错误日志 记录 MySQL 启动、运行、停止时的错误、警告和提示信息。 文本格式 是 二进制日志 记录所有更改数据的语句，用于主从复制和数据恢复。 STATEMENT, ROW, MIXED 否 (8.0默认开启) 通用查询日志 记录所有到达 MySQL 的客户端连接和执行的语句。 文本格式 / CSV 格式 否 慢查询日志 记录执行时间超过指定阈值的查询，用于性能优化。 文本格式 / CSV 格式 否 重做日志 InnoDB 特有的，用于保证事务的持久性和崩溃恢复。 物理格式（二进制） 是 回滚日志 InnoDB 特有的，用于保证事务的原子性和 MVCC。 逻辑格式 是 详细解析 1. 错误日志 这是 DBA 首先需要查看的日志，当数据库出现任何异常时，它通常是排查问题的起点。 内容：启动/关闭信息、错误信息、警告信息、在复制环境中从服务器线程的启动信息等。 格式：纯文本格式，易于阅读。 配置参数： log_error：指定错误日志文件的位置。 示例内容： 2023-10-27T08:00:00.000000Z 0 \[Note\] Server started. 2023-10-27T08:01:23.456789Z 5 \[Warning\] Aborted connection 5 to db: 'test' user: 'root' host: 'localhost' 2....

Mysql窗口函数 一、什么是窗口函数？ 窗口函数是一种在查询结果的"窗口"上执行计算的函数，它不会像常规聚合函数那样将多行合并为一行，而是为每一行返回一个值，同时保持原始行数不变。 基本语法 <窗口函数>(<参数>) OVER ( [PARTITION BY <分区表达式>] [ORDER BY <排序表达式> [ASC | DESC]] [ROWS/RANGE <窗口范围>] ) ‌<窗口函数>‌：可以是聚合函数（如SUM、AVG）或专用函数（如ROW_NUMBER、RANK）。‌‌ ‌OVER()‌：必需子句，定义窗口框架。‌‌ ‌PARTITION BY‌：可选，用于分组数据；若省略，窗口覆盖整个结果集。 ‌ORDER BY‌：可选，指定窗口内行的排序顺序。‌‌ ‌窗口范围‌：可选，默认ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。‌‌ 二、 准备测试数据 -- 创建销售数据表 CREATE TABLE sales ( sale_id INT PRIMARY KEY, salesperson VARCHAR(50), region VARCHAR(50), sale_date DATE, amount DECIMAL(10,2) ); INSERT INTO sales VALUES (1, '张三', '北京', '2023-01-15', 5000), (2, '李四', '上海', '2023-01-16', 8000), (3, '王五', '北京', '2023-01-17', 6000), (4, '张三', '北京', '2023-02-01', 7000), (5, '李四', '上海', '2023-02-02', 9000), (6, '赵六', '广州', '2023-02-03', 4000), (7, '王五', '北京', '2023-02-04', 5500), (8, '张三', '北京', '2023-03-01', 8500); -- 创建员工薪资表 CREATE TABLE employee_salary ( emp_id INT, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10,2), hire_date DATE ); INSERT INTO employee_salary VALUES (1, '张三', '技术部', 15000, '2020-01-15'), (2, '李四', '技术部', 12000, '2021-03-20'), (3, '王五', '销售部', 8000, '2022-06-10'), (4, '赵六', '销售部', 7500, '2022-08-05'), (5, '钱七', '技术部', 13000, '2020-11-30'), (6, '孙八', '人事部', 9000, '2021-09-15'), (7, '周九', '技术部', 16000, '2019-05-20'); 1....

Mysql分库分表和主从复制 MyCat 实现分库分表 MySQL 的分库分表解决方案通常依赖于中间件来实现水平扩展。常见的中间件有以下几种 MyCat ShardingSphere TDDL (Taobao Distributed Data Layer) Cobar Vitess MyCat 是一款开源的数据库中间件，支持 MySQL 数据库的分库分表功能。 使用 MyCat 实现分库分表的过程包括多个步骤，涉及配置 MyCat 和数据库的分片策略，路由规则等。下面通过一个实际案例来详细解释 MyCat 的实现原理、方式和步骤。 案例背景 假设你有一个电商系统，包含一个 orders 表，记录用户的订单信息。随着数据量的增长，单一数据库难以满足性能需求，因此需要进行 分库分表。 实现原理 MyCat 是一种数据库中间件，支持分库分表。它的原理是通过代理 MySQL 连接，将 SQL 请求转发到实际的数据库实例上。MyCat 会根据预定义的路由规则（如分片策略）来决定将查询请求路由到哪个数据库或表。 MyCat 的 分库分表原理如下： 分库：将数据按照某种规则分配到多个数据库实例中。例如，按用户的 ID 来分库。 分表：将一个大表拆分成多个小表，避免单表的数据过大导致查询性能下降。例如，可以按时间范围或数据量来分表。 实现方式 MyCat 的实现方式主要通过 配置分片规则 来实现分库分表。配置内容包括： 数据源配置：定义数据库实例。 分片规则配置：指定哪些字段用于分片，如何分片（按范围、按哈希等）。 路由规则配置：根据 SQL 查询的条件来路由请求到不同的数据库或表。 实现步骤 安装并启动 MyCat 下载 MyCat 安装包并解压。 配置 MyCat 启动脚本，启动 MyCat。 cd MyCat ./bin/start.sh 配置数据源 在 MyCat 的配置文件 conf/context....

Mysql 简易安装 安装Mysql8.0(centos8为例)， 可参考 MySQL官方文档 # CentOS el8下载 MySQL YUM 仓库（替换为最新版本） wget https://dev.mysql.com/get/mysql80-community-release-el8-6.noarch.rpm # el9 使用sudo yum install mysql84-community-release-el9-x86_64.noarch.rpm sudo rpm -Uvh mysql80-community-release-el*.rpm sudo yum makecache # 安装 MySQL 社区版服务器 # el9 的话sudo yum install mysql84-community-release-el9-x86_64.noarch.rpm sudo yum install -y mysql-community-server # 可以通过手动编辑/etc/yum.repos.d/mysql-community.repo文件来选择发布系列。 # **install时如果出现GPG 密钥问题** # sudo curl -o /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 https://repo.mysql.com/RPM-GPG-KEY-mysql-2023 # sudo chmod 644 /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 # sudo rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2023 # 重新执行 yum install -y mysql-community-server # 启动 MySQL 服务 sudo systemctl start mysqld sudo systemctl enable mysqld sudo systemctl status mysqld #MySQL 首次启动会生成一个临时 root 密码： sudo grep 'temporary password' /var/log/mysqld....

MySQL事务与隔离级别深度解析 一、 事务基础概念 1. 什么是事务 事务(Transaction)是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作。事务具有以下四个关键特性(ACID)： 原子性(Atomicity)：事务中的所有操作要么全部完成，要么全部不完成 一致性(Consistency)：事务执行前后，数据库从一个一致状态变到另一个一致状态 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务 持久性(Durability)：事务一旦提交，其结果就是永久性的 2. MySQL中的事务控制语句 START TRANSACTION; -- 或 BEGIN -- 执行SQL操作 COMMIT; -- 提交事务 ROLLBACK; -- 回滚事务 二、事务隔离级别详解 1. 四种标准隔离级别 MySQL支持四种事务隔离级别，控制不同事务之间的可见性： 隔离级别 脏读 不可重复读 幻读 说明 READ UNCOMMITTED（读未提交） 可能 可能 可能 最低隔离级别 READ COMMITTED（读已提交） 不可能 可能 可能 大多数数据库默认级别 REPEATABLE READ（可重复读） 不可能 不可能 可能 MySQL默认级别 SERIALIZABLE（序列化） 不可能 不可能 不可能 最高隔离级别 2. 并发问题说明 脏读(Dirty Read)：读取到其他事务未提交的数据 不可重复读(Non-repeatable Read)：同一事务内多次读取同一数据结果不同 幻读(Phantom Read)：同一事务内多次查询返回不同的行集合 3. 隔离级别设置与查看 -- 设置当前会话隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 查看当前隔离级别 SELECT @@transaction_isolation; 三、各隔离级别实现机制 1....

MYSQL中的回（Back to Table）表说明 一、 基本概念 回表是指当使用非聚簇索引（二级索引）进行查询时，首先在索引中查找到所需数据的主键值，然后再根据这个主键值回到主键索引（聚簇索引）中查找完整数据行的过程。 二、核心原理 聚簇索引 vs 非聚簇索引 -- 创建测试表 CREATE TABLE user_info ( id INT PRIMARY KEY, -- 主键，聚簇索引 name VARCHAR(50), email VARCHAR(100), age INT, created_at DATETIME, INDEX idx_email (email), -- 非聚簇索引（二级索引） INDEX idx_name_age (name, age) -- 复合非聚簇索引 ); 索引结构对比 聚簇索引（主键索引）结构： 索引节点 -> 叶子节点包含完整数据行 [id:1] -> [id:1, name:'张三', email:'zhang@xx.com', age:25, ...] [id:2] -> [id:2, name:'李四', email:'li@xx.com', age:30, ...] 非聚簇索引（二级索引）结构： 索引节点 -> 叶子节点只包含索引列 + 主键值 [email:'li@xx.com'] -> [主键id:2] [email:'zhang@xx....

MYSQL中的锁 一、 MySQL锁概述 MySQL锁机制是数据库管理系统实现并发控制的核心技术，它通过在共享资源上实施访问限制，确保数据的一致性和完整性。作为PHP高级开发者，深入理解MySQL锁机制对于构建高性能、高并发的Web应用至关重要。 二、 MySQL锁分类体系 1. 按锁的粒度划分 1. 表级锁 特点 开销小，加锁快 锁定整个表 并发度低 使用场景 -- 显式加表锁 LOCK TABLES users READ; -- 共享锁 LOCK TABLES users WRITE; -- 排他锁 -- 操作完成后释放 UNLOCK TABLES; 注意事项： MyISAM引擎默认使用表锁 在InnoDB上应谨慎使用，会严重影响并发 2. 行级锁 特点 开销大，加锁慢 只锁定需要的行 并发度高 使用场景 -- 共享锁(S锁) SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- MySQL 8.0+推荐语法 SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- 排他锁(X锁) SELECT * FROM accounts WHERE id = 1 FOR UPDATE; 注意事项：...

​MySQL优化常用方法 1. EXPLAIN EXPLAIN 查看SQL执行计划 主要字段说明： type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。 key_len列，索引长度。 rows列，扫描行数。该值是个预估值。 extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。 2. SQL语句中IN包含的值不应过多 MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。 3. SELECT语句务必指明字段名称 SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。 4. 当只需要一条数据的时候，使用limit 1 这是为了使EXPLAIN中type列达到const类型 5. 如果排序字段没有用到索引，就尽量少排序 6. 如果限制条件中其他字段没有索引，尽量少用or or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。 7. 尽量用union all代替union nion和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。 8. 不使用ORDER BY RAND() select id from dynamic order by rand() limit 1000; 上面的SQL语句，可优化为： select id from dynamic t1 join (select rand() * (select max(id) from dynamic) as nid) t2 on t1....

Mysql主从复制原理及保证数据一致性 提升数据库的并发能力 提在实际工作中，我们常常将Redis作为缓存与MySQL来配合使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出，如果不存在再访问数据库。这样就提升了读取的效率，也减少了对后端数据库的访问压力。 此外，对于一般数据库应用而言，都是读多写少的，当数据库读取数据压力较大时，我们可以从成本较小的方案开始优化，可以首先考虑优化SQL和索引，其次就是缓存策略，最后才是主从架构。 主从复制的作用 读写分离。 在读多写少的情况下，可以采用读写分离，主库当做写库，然后根据实际需要，选择使用多个读库，分散读的压力，提高并发性。 数据备份。 主从复制其实就相当于一种热备份的机制。 实现高可用。 数据备份其实就是一种冗余机制，当主服务器出现故障是时，可以切换到从服务器上，提高服务器可用性。 主从复制原理 实际上主从同步的原理就是基于binlog进行数据同步的。在主从复制过程中，会基于3个线程来操作，一个主库线程，两个从库线程。 二进制日志转储线程是一个主库线程。 当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在Binlog上加锁，读取完成之后，再将锁释放掉。 从库I/O线程会连接到主库，向主库发送请求更新Binlog。 这时从库的I/O线程就可以读取到主库的二进制日志转储线程发送的Binlog更新部分，并且拷贝到本地的中继日志。 从库SQL线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。 总结起来就是三步： 步骤1：Master将写操作记录到二进制日志（binlog），这些记录叫做二进制日志事件（binary log events）； 步骤2：Slave 将 Master 的 binary log events拷贝到它的中继日志（relay log）； 步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 搭建 TODO 此处省略，待补充 如何解决数据一致性问题 进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟，这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。 方案一、异步复制 异步模式就是客户端提交COMMIT之后不需要等从库返回任何结果，而是直接将结果返回给客户端，这样做的好处是不会影响主库写的效率。 但这样可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。 这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。 方案二、半同步复制 半同步复制的原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了Binlog，并且写入到中继日志中，再返回给客户端。 这样做的好处是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。 在MySQL5.7版本中还增加了一个参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。 方案三、组复制 异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景。 组复制技术MGR很好地弥补了这两种复制模式的不足，它是MySQL在5.7.17版本中推出的一种新的数据复制技术，是基于Paxos协议的状态机复制。 原文链接：

MySQL 分库分表 + 主从复制 一、主从复制 1. 环境说明 1个master节点，2个slave节点 mysql-master mysql-slave1 mysql-slave2 此处使用docker启动多个 MySQL 容器,为每个容器挂载配置文件 docker run -d --name mysql-master -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 mysql:8.0 docker run -d --name mysql-slave1 -e MYSQL_ROOT_PASSWORD=root -p 3307:3306 mysql:8.0 docker run -d --name mysql-slave2 -e MYSQL_ROOT_PASSWORD=root -p 3308:3306 mysql:8.0 创建配置文件目录 mkdir -p ~/mysql/master ~/mysql/slave1 ~/mysql/slave2 2. 修改配置 master的 my.cnf （~/mysql/master/my.cnf） [mysqld] server-id=1 log_bin=mysql-bin binlog_format=ROW gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave1 的 my.cnf（~/mysql/slave1/my.cnf） [mysqld] server-id=2 relay_log_recovery=ON read_only=ON super_read_only=ON gtid_mode=ON enforce_gtid_consistency=ON log_slave_updates=ON slave2 的 my....