Linux Centos 下使用yum 命令安装mysql实现步骤

最新文章

• 在MySQL中实现冷热数据分离的方法及使用场景底层原理解析

  在MySQL中实现冷热数据分离主要是为了优化性能，特别是对于那些拥有大量历史数据的应用程序。通过将频繁访问的“热”数据和不常访问的“冷”数据分开存储，可以提高查询效率并降低存储成本。

  实现冷热数据分离

  1. 分表策略

  一种常见的方法是根据时间或其他业务规则将数据分到不同的表中：

  按日期分表：例如，每天或每月创建一个新的表来存储新产生的数据。

  CREATE TABLE orders_202501 LIKE orders;
  CREATE TABLE orders_202502 LIKE orders;

  然后，在应用程序层面决定应该向哪个表写入数据，并从哪个表读取数据。

  按状态分表：比如，订单状态为完成的订单可能被移动到一个专门的归档表中。

  INSERT INTO orders_archived SELECT * FROM orders WHERE status = 'completed';
  DELETE FROM orders WHERE status = 'completed';

  2. 使用分区表

  MySQL支持表分区功能，可以根据特定的列值（如日期）自动将数据划分到不同的分区中。这样做的好处是可以简化管理和查询操作。

  范围分区：基于某个连续的范围（如日期）进行分区。

  CREATE TABLE orders (
      order_id INT NOT NULL,
      customer_id INT NOT NULL,
      order_date DATE NOT NULL,
      PRIMARY KEY (order_id, order_date)
  )
  PARTITION BY RANGE (YEAR(order_date)) (
      PARTITION p0 VALUES LESS THAN (2024),
      PARTITION p1 VALUES LESS THAN (2025),
      PARTITION p2 VALUES LESS THAN MAXVALUE
  );

  列表分区：基于离散值（如地区代码）进行分区。

  CREATE TABLE customers (
      id INT NOT NULL,
      name VARCHAR(30),
      region_code CHAR(2)
  )
  PARTITION BY LIST(region_code) (
      PARTITION pEast VALUES IN ('01', '02'),
      PARTITION pWest VALUES IN ('03', '04')
  );

  3. 数据归档与迁移

  定期将不再需要频繁访问的数据迁移到低成本存储或专用的历史数据库中。

  使用脚本定期归档：编写定时任务或批处理脚本来识别并迁移旧数据。

  # 示例伪代码
  SELECT * INTO OUTFILE '/path/to/archive/orders_$(date +%Y%m%d).csv' 
  FROM orders WHERE order_date < CURDATE() - INTERVAL 1 YEAR;
  DELETE FROM orders WHERE order_date < CURDATE() - INTERVAL 1 YEAR;

  使用场景

  底层原理

  通过上述方法和技术，可以在MySQL环境中高效地实施冷热数据分离策略，满足各种业务需求的同时优化资源利用。选择合适的实现方式取决于具体的应用场景、数据量以及性能要求。

  到此这篇关于在MySQL中实现冷热数据分离的方法及使用场景底层原理解析的文章就介绍到这了,
  3周前 (01-21)

• MySQL磁盘坏块处理的全流程

  MySQL 磁盘坏块问题的处理总体流程

  下面是推荐的分阶段处理流程，适用于生产环境，强调数据保护、风险评估、逐步推进：

  第一阶段：问题确认与隔离

  1.1 检查 MySQL 日志确认症状

  查看 mysqld.err 或 MySQL 日志是否存在以下关键词：

  1.2 确认是否磁盘 I/O 层问题

  使用如下工具：

  dmesg | grep -i error
  dmesg | grep -i sda  # 根据你使用的磁盘设备
  

  重点关注如：

  Buffer I/O error on device /dev/sda3, logical block 123456
  EXT4-fs error (device sda3): ...
  

  第二阶段：应急保护与备份

  2.1 立即备份其他健康数据

  2.2 停止写入请求

  可通过 FLUSH TABLES WITH READ LOCK; 锁定全局读取；

  或直接将 MySQL 实例切换为只读：

  SET GLOBAL read_only = ON;
  

  第三阶段：诊断坏块位置与影响

  3.1 使用 badblocks 工具检测磁盘坏块

  badblocks -sv /dev/sda > badblocks.txt
  

  3.2 确认受损数据文件位置（特别是 .ibd 文件）

  ls -lh /var/lib/mysql/databasename/
  file /var/lib/mysql/databasename/table.ibd
  

  可配合 strace -f -p $(pidof mysqld) 跟踪是否某个 .ibd 文件访问时报错。

  第四阶段：修复受影响表或表空间

  4.1 若只影响单表，可尝试以下修复操作：

  方法A：导出可导出的数据后删除表

  SELECT * FROM problem_table INTO OUTFILE '/tmp/backup.csv';
  TRUNCATE TABLE problem_table;
  DROP TABLE problem_table;
  

  方法B：将表移出数据目录再尝试 DROP

  systemctl stop mysqld
  mv /var/lib/mysql/dbname/problem_table.ibd /tmp/
  systemctl start mysqld
  
  # 然后登录 MySQL 执行：
  DROP TABLE dbname.problem_table;
  

  注意这样会让 InnoDB 报告表空间文件不存在，但通常可跳过 DROP 阶段的 crash。

  方法C：使用 innodb_force_recovery 修复

  编辑 my.cnf 添加：

  [mysqld]
  innodb_force_recovery=1
  

  数值从 1 到 6 逐级递增（数值越高风险越大，建议从 1 开始测试）

  然后重启 MySQL，再尝试导出或 DROP 表。

  第五阶段：系统层修复或替换磁盘

  5.1 标记/屏蔽坏块（临时措施，不推荐长期使用）

  e2fsck -l badblocks.txt /dev/sda3
  

  5.2 若坏块不可控，推荐更换磁盘

  附：innodb_force_recovery 参数说明

  值	含义	风险级别
  1	跳过 insert buffer 的恢复	安全
  2	跳过 redo log 的应用	中
  3	跳过 undo log 恢复	中
  4	不执行 purge 操作	高
  5	不执行 insert buffer 合并	高
  6	禁止双写缓冲，跳过一切恢复流程	极高

  总结：MySQL 磁盘坏块处理建议

  步骤	行动	目的
  1	确认日志、dmesg、坏块位置	确认是否真为磁盘故障
  2	备份健康数据	防止坏块扩散影响
  3	使用 TRUNCATE 或 rename + DROP	规避触发 I/O 错误
  4	启用 innodb_force_recovery 修复	数据导出和表结构清理
  5	标记坏块或更换磁盘	根除问题源头

  如果你能提供 mysqld.err 或 dmesg 日志中具体的报错信息，我可以帮你进一步诊断。需要我协助你写具体的修复操作脚本也可以。

  到此这篇关于MySQL磁盘坏块处理的全流程的文章就介绍到这了,
  3周前 (01-21)

• MySQL 8.0 中 LIMIT 优化新特性使用场景及最佳实践

  在 MySQL 查询优化中，LIMIT子句的使用非常普遍，尤其在分页场景中。但当LIMIT与ORDER BY、GROUP BY结合时，优化器对索引的选择往往直接影响查询性能。MySQL 8.0.21 版本引入的prefer_ordering_index参数，为解决这类场景的性能问题提供了新的控制手段。本文将深入解析该参数的作用机制、实践效果及适用场景。

  一、背景：LIMIT 与排序的索引选择困境

  在包含LIMIT N、ORDER BY和GROUP BY的查询中，优化器的核心目标是减少排序操作—— 这通常意味着优先选择与ORDER BY字段相关的索引（“排序索引”），利用索引的有序性避免额外排序。

  但实际场景中，这种 “最优解” 可能适得其反：若排序索引与WHERE条件中的过滤字段无关，优化器可能会放弃过滤性更好的索引，转而扫描排序索引并回表过滤，最终导致全表扫描式的低效查询。

  例如，一张表同时存在主键索引（id1）和过滤字段索引（id2），当查询为SELECT c2 FROM t WHERE id2>8 ORDER BY id1 LIMIT 2时：

  在 MySQL 8.0.21 之前，这种索引选择行为无法通过参数干预，只能通过改写 SQL（如延迟关联）优化，灵活性较差。

  二、新特性：prefer_ordering_index 参数的作用

  MySQL 8.0.21 新增的prefer_ordering_index参数，通过optimizer_switch系统变量控制，用于调整优化器对 “排序索引” 的偏好：

  参数设置方式：

  -- 开启（默认）
  SET optimizer_switch = "prefer_ordering_index=on";
  -- 关闭
  SET optimizer_switch = "prefer_ordering_index=off";

  三、实践验证：参数对执行计划的影响

  1. 测试环境与数据准备

  CREATE TABLE t (
    id1 BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
    id2 BIGINT NOT NULL,
    c1 VARCHAR(50) NOT NULL,
    c2 VARCHAR(50) NOT NULL,
    INDEX i (id2, c1)  -- 联合索引（过滤字段id2）
  );
  -- 插入测试数据
  INSERT INTO t(id2, c1, c2) VALUES
  (1,'a','xfvs'), (2,'bbbb','xfvs'), (3,'cdddd','xfvs'),
  (4,'dfdf','xfvs'), (12,'bbbb','xfvs'), (23,'cdddd','xfvs'),
  (14,'dfdf','xfvs'), (11,'bbbb','xfvs'), (13,'cdddd','xfvs'),
  (44,'dfdf','xfvs'), (31,'bbbb','xfvs'), (33,'cdddd','xfvs'),
  (34,'dfdf','xfvs');

  2. 参数开启时（默认行为）

  -- 确认参数状态
  SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';  -- 返回1（开启）
  -- 查看执行计划
  EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

  执行计划关键信息：

  问题：主键索引与id2无关，需扫描大量无关记录后过滤，在大表中会导致严重性能问题。

  3. 参数关闭时（优化后）

  -- 关闭参数
  SET optimizer_switch = "prefer_ordering_index=off";
  -- 查看执行计划
  EXPLAIN SELECT c2 FROM t WHERE id2>8 ORDER BY id1 ASC LIMIT 2\G

  执行计划关键信息：

  优势：通过过滤性更好的id2索引减少扫描范围，即使增加排序步骤，整体效率仍高于全表扫描。

  四、适用场景与最佳实践

  prefer_ordering_index参数并非 “银弹”，需根据具体场景选择是否关闭：

  五、总结

  MySQL 8.0 引入的prefer_ordering_index参数，为LIMIT与排序结合的查询提供了更精细的优化控制。它的核心价值在于：允许开发者干预优化器对 “排序索引” 的偏好，在 “避免排序” 和 “减少扫描范围” 之间找到平衡。

  随着 MySQL 优化器的不断进化，这类参数的出现体现了从 “自动最优” 到 “可控优化” 的趋势。掌握这类特性，能帮助开发者在复杂业务场景中更精准地提升查询性能，避免因优化器的 “想当然” 导致的性能陷阱。

  到此这篇关于MySQL 8.0 中 LIMIT 优化新特性 的文章就介绍到这了,
  3周前 (01-21)

• MySQL InnoDB undo log数据结构用法详解

  一、概念解析

  1、undo log基本

  undo log是InnoDB事务中特有的结构，它的作用有两个：

  undo能够避免脏读：在操作数据之前，将数据备份到undo页中，然后在进行数据的修改，不能看到其他回话未提交的数据，当要读取被修改页数据行时，会指向备份在undo页中的数据，避免脏读。

  在InnoDB存储引擎中有rollback segment，每个回滚段记录了1024个undo log segment（slot），每个undo log segment段中进行undo页的申请。

  从1.1版本开始InnoDB支持最大128个rollback segment，所以理论上可以支持的最大的事务上限为128*1024。

  在1.2版本开始，可以通过参数对rollback segment进行设置，参数有：

  参数名称	含义
  innodb_undo_directory	设置回滚段文件所在的路径
  innodb_undo_logs	设置回滚段的个数
  innodb_undo_tablespaces	设置构成回滚段文件的数量

  也就是说：

  回滚段可以存放在共享表空间以外的地方，即可以设置独立表空间，innodb_undo_directory的默认值为“.”，表示当前InnoDB存储引擎的目录。

  回滚段的个数默认值为128。

  设置回滚段文件的数量后，回滚段可以较为平均地分布在多个文件中，在路径下可以看到undo为前缀的文件，该文件代表回滚段文件。

  可用show variables like “innodb_undo%”;查询：

  

  重新初始化时指定innodb_undo_tablespaces=N来设置，设置好后会在data目录下生成undo独立表空间（大小10M）：

  

  2、事务系统头transactionsystemheader

  回滚段的管理，在InnoDB共享表空间中，用第6个页面（5号）来管理的，这个页面专门用来存储事务系统相关信息的，源代码位于trx0sys.h，它的格式如下：

  

  这三个参数的含义分别为：

  参数名称	含义
  TRX_SYS_TRX_ID_STORE	存储事务号
  TRX_SYS_FSEG_HEADER	存储事务段信息
  TRX_SYS_RSEGS	数组，InnoDB有128个rollback segment

  3、回滚段头rollbacksegmentheader

  对于每一个回滚段，即上面TRX_SYS_RSEGS数组中的一个元素，也有其自己的存储格式，代码中的宏定义位于trx0rseg.h，格式如下：

  

  这五个信息的含义如下：

  参数名称	含义
  TRX_RSEG_MAX_SIZE	所有undo段页面之和
  TRX_RSEG_HISTORY_SIZE	需要purge的回滚段页面数
  TRX_RSEG_HISTORY	存储history list的链表首地址
  TRX_RSEG_FSEG_HEADER	存储回滚段的inode位置信息
  TEX_RSEG_UNDO_SLOTS	数组，1024，每个元素是一个页面号

  这五个信息存储了一个回滚段的信息，最后一个位置的数组，用来真正存储回滚段的位置。

  这五个信息是从页面偏移38的位置开始存储，在TRX_SYS宏定义中可以查看：

  

  

  

  4、回滚页头undologpageheader

  定义在trx0undo.h，事务undo日志页面头偏移量：

  

  其中各个参数的含义为：

  参数名称	含义
  TRX_UNDO_PAGE_TYPE	类型，TRX_UNDO_INSERT\UPDATE
  TRX_UNDO_PAGE_START	开始存储undo日志的位置
  TRX_UNDO_PAGE_FREE	结束标志
  TRX_UNDO_PAGE_NODE	双向链表的指针

  第一个页在undo log段的回滚日志段头：

  

  其中各个参数的含义为：

  参数名称	含义
  TRX_UNDO_STATE	undo段五种状态，包括TRX_UNDO_ACTIVE\CACHED\TO_FREE\TO_PURGE\PREPARED
  TRX_UNDO_LAST_LOG	最后一个undo日志的偏移位置
  TRX_UNDO_FSEG_HEADER	undo段inode信息
  TRX_UNDO_PAGE_LIST	段链表的首地址

  其中undo log段的状态有五种：

  

  5、回滚日志头undologheader

  这是回滚日志的头，在第一页的update undo log中有几个undo log headers。同样定义在trx0undo.h中，它的宏定义如下：

  

  整理并解释其中的含义：

  参数名称	含义
  TRX_UNDO_TRX_ID	存储当前undo日志对应事务的事务id
  TRX_UNDO_TRX_NO	事务序列号
  TRX_UNDO_DEL_MARKS	删除记录标记
  TRX_UNDO_LOG_START	存储当前页面中第一个undo日志的开始位置
  TRX_UNDO_XID_EXISTS	标记的当前日 志中有没有包含xid事务
  TRX_UNDO_DICT_TRANS	标记当前事务是不是DDL，在回滚时判断如何操作
  TRX_UNDO_TABLE_ID	表id
  TRX_UNDO_NEXT_LOG	链接指向下一个undo日志
  TRX_UNDO_PREV_LOG	指向上一个undo日志，构成双向链表
  TRX_UNDO_HISTORY_NODE	存储history list中的双向链表，链表首地址

  6、两种undolog格式

  undo log格式有两种：

  

  其中，insert操作会产生insert undo log。因为隔离性的要求，insert操作只对事务本身可见，对其他事务不可见。并且undo log可以在事务提交后直接删除，不需要进行purge操作。

  update和delete操作都会产生update undo log。对于update操作很简单，就是将旧版本的值修改为新版本的值，并且将旧版本的记录保存到undo log中；对于delete操作完后并不是真正的删除该行数据，而是将该行的数据存在undo log中，并且将标记位delete置为true，以备回滚时可以根据标记位进行逆操作，这样就能够找回之前的旧数据，起到回滚的作用。

  在delete中又分为三种情况:

  

  purge线程两个主要作用是：清理undo页和清除page里面带有delete_bit标识的数据行。在InnoDB中，事务中的delete操作实际上并不是真正的删除掉数据行，而是在记录上标识delete_bit，而不是真正删除记录，真正的删除工作需要后台purge线程去完成。

  下面是两种undo log记录的具体的结构：

  

  二、数据结构

  与undo log相关的数据结构有trx_sys_t、trx_rsegs_t 、trx_t、trx_rseg_t、trx_undo_t、trx_undo_ptr_t等结构，其中比较重要的结构有两个：trx_rseg_t和trx_undo_t。trx_rseg_t保存回滚段的信息，trx_undo_t保存关于回滚日志的信息。

  

  通过两种方式找到回滚日志：

  

  

  结论：

  1、从全局变量trx_sys中的回滚段数组rseg_array中的回滚链表update-undo_list中指针（trx_sys->rseg_array[N]->update_undo_list->start）以及当前事务trx中的回滚段指针rsegs中的slot槽区m_redo中的undo log（trx->rsegs->m_redo->update_undo）都可以找到相应的回滚段中的回滚日志。

  2、事务id、回滚段id、page_no递增。

  1、trx_rseg_t

  回滚段的内存对象的类型是一个叫trx_rseg_t的结构，它定义在trx0rseg.h文件中，它的各成员有：

  成员变量	解释
  ulint id;	回滚段id
  RsegMutex mutex;	回滚段mutex，保护
  ulint space;	回滚段头空间
  ulint page_no;	回滚段页码
  page_size_t page_size;	页面大小
  ulint max_size;	允许的最大页大小
  ulint curr_size;	当前页的大小
  UT_LIST_BASE_NODE_T(trx_undo_t) update_undo_list;	update回滚日志链表
  UT_LIST_BASE_NODE_T(trx_undo_t) update_undo_cached;	为快速重用而缓存的update回滚日志链表
  UT_LIST_BASE_NODE_T(trx_undo_t) insert_undo_list;	insert回滚日志的字段
  UT_LIST_BASE_NODE_T(trx_undo_t) insert_undo_cached;	为快速重用而缓存的insert undo log链表
  ulint last_page_no;	最后一个尚未清除的日志页码
  ulint last_offset;	最后一个尚未清除的日志头的字节偏移量
  trx_id_t last_trx_no;	最后一个尚未清除的日志的事务号
  ibool last_del_marks;	判断最后一个尚未清除的日志是否要清除
  ulint trx_ref_count;	跟踪rseg分配事务的参考计数器
  bool skip_allocation;	如果为真，则跳过分配此rseg

  update\insert_undo_list 用于保存产生的回滚日志，update\insert_undo_cached用于快速重用而缓存的回滚日志链表。当确定回滚日志无用时，会将回滚日志从update\insert_undo_list中摘除，放到相应的cached链表中。

  2、trx_undo_t

  回滚日志的内存对象的类型是一个叫trx_undo_t的结构，它定义在trx0undo.h文件中，它的各成员有：

  成员变量	解释
  ulint     id;	回滚段内的回滚日志槽号（slot）
  ulint     type;	类型。TRX_UNDO_INSERT\UPDATE
  ulint     state;	相应的回滚日志段的状态
  ibool del_marks;	删除标记
  trx_id_t     trx_id;	事务id
  XID     xid;	open XA事务识别
  ibool dict_operation;	是否是dict操作
  table_id_t table_id;	设置表ID
  trx_rseg_t* rseg;	回滚日志所属的rseg回滚段
  ulint     space;	放置回滚日志的空间id
  page_size_t page_size;	回滚日志中标题页的大小
  ulint     hdr_page_no;	回滚日志中标题页的页码
  ulint      hdr_offset;	页面上回滚日志的header偏移量
  ulint     last_page_no;	登录日志中最后一页的页码
  ulint     size;	当前页的大小
  ulint     empty;	回滚日志记录堆栈当前是否为空
  ulint     top_page_no;	链接最近撤消日志记录的页码
  ulint     top_offset;	最新回滚记录的偏移量
  undo_no_t top_undo_no;	最新的回滚记录的编号
  buf_block_t* guess_block;	猜测可能位于首页的缓冲区
  ulint     withdraw_clock;	存储guess_block时缓冲池的退出时钟值
  UT_LIST_NODE_T(trx_undo_t) undo_list;	回滚段中的回滚日志对象链表

  回滚段指针包括回滚段的id、日志所在的page no、以及page内偏移量。通过回滚段的指针就能够找到这个回滚段，通过回滚日志的槽号、页码、回滚记录编号和偏移量就能定位到这条回滚日志。

  undo_list用于将此类型的undo log链接到undo log list中。

  UT_LIST_NODE_T(trx_undo_t) undo_list链表中有两个指针，分别为prev和next，用于将此undo log链接到undo log list中。

  总结

  以上为个人经验，希望能给大家一个参考，也希望大家多多支持Linux运维。
  3周前 (01-21)

• MySQL参数innodb_force_recovery详解

  innodb_force_recovery 是 InnoDB 存储引擎的一个重要参数，用于在数据库崩溃恢复时控制恢复行为的级别。这个参数主要在数据库无法正常启动时使用，可以帮助我们从损坏的数据库中恢复数据。

  一 参数概述

  二 参数级别详解

  级别	名称	行为描述	适用场景
  0	正常模式	默认值，执行完整恢复	数据库正常运行时
  1	SRV_FORCE_IGNORE_CORRUPT	忽略损坏的页	表空间存在损坏页
  2	SRV_FORCE_NO_BACKGROUND	阻止主线程和清理线程运行	恢复过程中避免后台干扰
  3	SRV_FORCE_NO_TRX_UNDO	不执行事务回滚	事务系统损坏
  4	SRV_FORCE_NO_IBUF_MERGE	不执行插入缓冲合并	插入缓冲损坏
  5	SRV_FORCE_NO_UNDO_LOG_SCAN	启动时不查看undo日志	undo日志损坏
  6	SRV_FORCE_NO_LOG_REDO	不执行前滚操作	redo日志损坏

  三 使用场景与操作指南

  1 数据库无法正常启动时

  # 修改my.cnf文件
  [mysqld]
  innodb_force_recovery=1  # 从最低级别开始尝试
  
  # 重启MySQL服务
  systemctl restart mysqld
  

  2 数据恢复步骤

  3 各级别典型应用

  -- 级别1：忽略损坏页（可以读取未损坏数据）
  SET GLOBAL innodb_force_recovery=1;  -- 注意：实际上需要写入配置文件
  
  -- 级别3：跳过事务回滚（当存在大量未完成事务导致启动失败）
  [mysqld]
  innodb_force_recovery=3
  
  -- 级别6：最激进模式（redo日志损坏时最后手段）
  [mysqld]
  innodb_force_recovery=6
  

  四 重要注意事项

  五 恢复后操作

  六 与其他参数的关系

  七 监控与日志

  innodb_force_recovery 是MySQL数据库恢复的强大工具，但需要谨慎使用。建议在测试环境先验证恢复方案，并确保有完整备份后再在生产环境操作。

  更详细的内容请查看官方文档：

  https://dev.mysql.com/doc/refman/8.4/en/forcing-innodb-recovery.html

  到此这篇关于MySQL参数innodb_force_recovery详解的文章就介绍到这了,
  3周前 (01-21)

• MySQL实现双机双向热备份的详细教程

  在现代企业级应用中，数据的高可用性和灾难恢复能力是至关重要的。MySQL作为最流行的开源关系型数据库管理系统之一，提供了多种备份和恢复机制来确保数据的安全性。本文将详细介绍如何实现MySQL的双机双向热备份，以提高系统的可用性和数据的安全性。

  1. 环境准备

  1.1 硬件环境

  两台服务器（Server A 和 Server B），建议配置相同或相似。

  每台服务器至少有两个网络接口，一个用于内部通信，一个用于外部访问。

  1.2 软件环境

  2. 配置MySQL主从复制

  2.1 安装MySQL

  在两台服务器上安装MySQL。假设已经安装完成，版本为5.7。

  2.2 配置Server A为主服务器

  编辑MySQL配置文件 ​​/etc/my.cnf​​，添加以下内容：

  [mysqld]
  server-id=1
  log-bin=mysql-bin
  binlog-format=mixed

  重启MySQL服务：

  sudo systemctl restart mysqld

  2.3 配置Server B为从服务器

  编辑MySQL配置文件 ​​/etc/my.cnf​​，添加以下内容：

  [mysqld]
  server-id=2
  log-bin=mysql-bin
  binlog-format=mixed
  relay-log=mysql-relay-bin
  log-slave-updates=1
  read-only=1

  重启MySQL服务：

  sudo systemctl restart mysqld

  2.4 创建复制用户

  在Server A上创建一个用于复制的用户：

  CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  FLUSH PRIVILEGES;

  2.5 获取主服务器的二进制日志位置

  在Server A上执行以下命令获取二进制日志文件名和位置：

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  记录下 ​​File​​ 和 ​​Position​​ 的值。

  2.6 备份主服务器的数据

  在Server A上备份数据：

  mysqldump --all-databases --master-data=2 --single-transaction --routines --events --triggers > all_databases.sql

  2.7 传输备份文件到从服务器

  将备份文件传输到Server B：

  scp all_databases.sql user@server_b:/path/to/backup/

  2.8 在从服务器上恢复数据

  在Server B上恢复数据：

  mysql < /path/to/backup/all_databases.sql

  2.9 配置从服务器

  在Server B上配置从服务器：

  CHANGE MASTER TO 
  MASTER_HOST='server_a_ip', 
  MASTER_USER='repl', 
  MASTER_PASSWORD='your_password', 
  MASTER_LOG_FILE='mysql-bin.000001', 
  MASTER_LOG_POS=12345;

  启动从服务器：

  START SLAVE;

  2.10 检查复制状态

  在Server B上检查复制状态：

  SHOW SLAVE STATUS\G

  确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​。

  3. 配置双向复制

  3.1 在Server B上创建复制用户

  在Server B上创建一个用于复制的用户：

  CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  FLUSH PRIVILEGES;

  3.2 获取Server B的二进制日志位置

  在Server B上执行以下命令获取二进制日志文件名和位置：

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  记录下 ​​File​​ 和 ​​Position​​ 的值。

  3.3 配置Server A

  在Server A上配置从服务器：

  CHANGE MASTER TO 
  MASTER_HOST='server_b_ip', 
  MASTER_USER='repl', 
  MASTER_PASSWORD='your_password', 
  MASTER_LOG_FILE='mysql-bin.000001', 
  MASTER_LOG_POS=12345;

  启动从服务器：

  START SLAVE;

  3.4 检查复制状态

  在Server A上检查复制状态：

  SHOW SLAVE STATUS\G

  确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​。

  4. 测试双向复制

  4.1 在Server A上插入数据

  在Server A上插入一条测试数据：

  USE test;
  CREATE TABLE test_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
  INSERT INTO test_table (name) VALUES ('Test Data');

  4.2 在Server B上验证数据

  在Server B上查询数据：

  USE test;
  SELECT * FROM test_table;

  4.3 在Server B上插入数据

  在Server B上插入一条测试数据：

  INSERT INTO test_table (name) VALUES ('Test Data from B');

  4.4 在Server A上验证数据

  在Server A上查询数据：

  SELECT * FROM test_table;

  5. 注意事项

  通过以上步骤，您可以成功实现MySQL的双机双向热备份，从而提高系统的高可用性和数据的安全性。

  6.方法补充

  下面是一个简单的示例，展示如何设置两台MySQL服务器之间的双向热备份。假设我们有两台服务器：Server A (192.168.1.10) 和 Server B (192.168.1.11)，每台服务器都运行着MySQL 5.7或更高版本。

  步骤 1: 配置MySQL服务器

  Server A (192.168.1.10)

  编辑 MySQL 配置文件 ​​my.cnf​​ 或 ​​my.ini​​，添加以下内容：

  [mysqld]
  server-id=1
  log_bin=mysql-bin
  binlog_do_db=your_database_name
  auto-increment-offset=1
  auto-increment-increment=2

  Server B (192.168.1.11)

  编辑 MySQL 配置文件 ​​my.cnf​​ 或 ​​my.ini​​，添加以下内容：

  [mysqld]
  server-id=2
  log_bin=mysql-bin
  binlog_do_db=your_database_name
  auto-increment-offset=2
  auto-increment-increment=2

  步骤 2: 创建复制用户

  在两台服务器上分别创建一个用于复制的用户，并赋予相应的权限。

  在 Server A 上执行：

  CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
  FLUSH PRIVILEGES;

  在 Server B 上执行：

  CREATE USER 'repl'@'192.168.1.10' IDENTIFIED BY 'password';
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.10';
  FLUSH PRIVILEGES;

  步骤 3: 获取二进制日志文件和位置

  在两台服务器上分别获取当前的二进制日志文件名和位置。

  在 Server A 上执行：

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  记下 ​​File​​ 和 ​​Position​​ 的值，例如 ​​mysql-bin.000001​​ 和 ​​12345​​。

  在 Server B 上执行：

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  同样记下 ​​File​​ 和 ​​Position​​ 的值。

  步骤 4: 设置从属关系

  在 Server A 上执行：

  CHANGE MASTER TO 
  MASTER_HOST='192.168.1.11',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
  START SLAVE;

  在 Server B 上执行：

  CHANGE MASTER TO 
  MASTER_HOST='192.168.1.10',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
  START SLAVE;

  步骤 5: 检查复制状态

  在两台服务器上检查复制状态，确保一切正常。

  在 Server A 和 Server B 上执行：

  SHOW SLAVE STATUS \G

  确保 ​​Slave_IO_Running​​ 和 ​​Slave_SQL_Running​​ 均为 ​​Yes​​，并且没有错误信息。

  以上步骤提供了一个基本的双向热备份配置示例。在生产环境中，还需要考虑更多的安全性和性能优化措施。

  方法二：

  MySQL的双机双向热备份通常指的是MySQL的主从复制（Master-Slave Replication）和主主复制（Master-Master Replication）。这种配置可以确保数据在两台服务器之间同步，提高系统的可用性和数据的安全性。下面我将详细介绍如何设置MySQL的主主复制，并提供相应的SQL命令。

  环境准备

  假设你有两台MySQL服务器，分别命名为​​Server A​​和​​Server B​​，它们的IP地址分别为​​192.168.1.10​​和​​192.168.1.11​​。

  配置文件修改

  首先，需要修改两台服务器上的MySQL配置文件​​my.cnf​​或​​my.ini​​，添加或修改以下内容：

  Server A (​​/etc/mysql/my.cnf​​)

  [mysqld]
  server-id=1
  log-bin=mysql-bin
  binlog-do-db=your_database_name
  relay-log=mysql-relay-bin
  auto-increment-offset=1
  auto-increment-increment=2

  Server B (​​/etc/mysql/my.cnf​​)

  [mysqld]
  server-id=2
  log-bin=mysql-bin
  binlog-do-db=your_database_name
  relay-log=mysql-relay-bin
  auto-increment-offset=2
  auto-increment-increment=2

  重启MySQL服务

  修改配置文件后，需要重启MySQL服务以使配置生效：

  sudo systemctl restart mysql

  创建复制用户

  在两台服务器上创建一个用于复制的MySQL用户，并授予必要的权限。

  在Server A上执行

  CREATE USER 'replication'@'192.168.1.11' IDENTIFIED BY 'your_password';
  GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.11';
  FLUSH PRIVILEGES;

  在Server B上执行

  CREATE USER 'replication'@'192.168.1.10' IDENTIFIED BY 'your_password';
  GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.10';
  FLUSH PRIVILEGES;

  获取二进制日志位置

  在开始复制之前，需要获取当前的二进制日志文件名和位置。

  在Server A上执行

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  记录下​​File​​和​​Position​​的值，例如：

  +------------------+----------+--------------+------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  | mysql-bin.000001 | 12345    | your_database_name |              |
  +------------------+----------+--------------+------------------+

  在Server B上执行

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

  同样记录下​​File​​和​​Position​​的值。

  配置复制

  使用​​CHANGE MASTER TO​​命令配置复制。

  在Server A上执行

  CHANGE MASTER TO
  MASTER_HOST='192.168.1.11',
  MASTER_USER='replication',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
  
  START SLAVE;

  在Server B上执行

  CHANGE MASTER TO
  MASTER_HOST='192.168.1.10',
  MASTER_USER='replication',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
  
  START SLAVE;

  检查复制状态

  最后，检查复制状态以确保一切正常。

  在Server A上执行

  SHOW SLAVE STATUS \G

  确保​​Slave_IO_Running​​和​​Slave_SQL_Running​​都为​​Yes​​。

  在Server B上执行

  SHOW SLAVE STATUS \G

  同样确保​​Slave_IO_Running​​和​​Slave_SQL_Running​​都为​​Yes​​。

  测试复制

  可以在任意一台服务器上创建一个测试表并插入一些数据，然后检查另一台服务器上是否也同步了这些数据。

  在Server A上执行

  USE your_database_name;
  CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
  INSERT INTO test (name) VALUES ('Test1');

  在Server B上执行

  USE your_database_name;
  SELECT * FROM test;

  如果能看到​​Test1​​这条记录，说明复制配置成功。

  通过以上步骤，你就可以成功配置MySQL的主主复制，实现双机双向热备份。

  到此这篇关于MySQL实现双机双向热备份的详细教程的文章就介绍到这了,
  3周前 (01-21)

• MySQL索引中的页及索引的分类及使用

  一、什么是页？

  1.定义

  页是InnoDB存储引擎在磁盘上存储数据的一种逻辑结构，是管理数据和索引的基本单位，相当于一个容器，存放表中的记录、索引信息等。

  mysql> show variables like 'innodb_page_size';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | innodb_page_size | 16384 | 16KB
  +------------------+-------+
  1 row in set, 1 warning (0.02 sec)

  2.页的结构

  以数据页的结构为例：

  

   页目录通过分组+槽的方法进行高效的查找：

  快速定位记录组：先通过二分查找找到对应的组；

  组内线性查找：每个组内的数据不超过8个，超过8个重新分裂成一个组。

  3.B+树在索引中的应用

  

  查找4为例：

  二、索引的分类及使用

  按功能特性分类：

  普通索引

  1.定义

  最基本的索引，没有唯一性的限制，只是为了提高查询效率而创建的索引。可能为多列创建组合索引，称为复合索引或者组合索引。创建之后都会生成一颗索引树，创建多少索引生成多少棵索引树。

  2.使用

  -- 方式一：创建表时创建普通索引
  mysql> create table class4(
      -> id bigint,
      -> name varchar(20),
      -> index(name));
  Query OK, 0 rows affected (0.09 sec)
  mysql> desc class4;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  |     | NULL    |       |
  | name  | varchar(20) | YES  | MUL | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  -- 方式二：修改为普通索引
  mysql> create table class5(
      -> id bigint,
      -> name varchar(20));
  Query OK, 0 rows affected (0.15 sec)
  mysql> alter table class5 add index(name);
  Query OK, 0 rows affected (0.11 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql> desc class5;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  |     | NULL    |       |
  | name  | varchar(20) | YES  | MUL | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  -- 方式三：创建索引并指定索引名：一定要指定索引名，否则创建失
  mysql> create index idx_class5_id on class5(id);
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql> desc class5;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  | MUL | NULL    |       |
  | name  | varchar(20) | YES  | MUL | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  索引的查询

  语法

  show keys from 表名；

  示例：

  

   他有点长，后面我直接查看表结构来查看

  唯一索引

  1.定义

  当在一个表中定义一个唯一键unique，索引值必须是唯一的，不可以存在重复值。 

  2.使用

  方式一：创建时创建唯一键
  mysql> create table class1(
      -> id bigint unique,
      -> name varchar(20));
  Query OK, 0 rows affected (0.13 sec)
  mysql> desc class1;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  | UNI | NULL    |       |
  | name  | varchar(20) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  方式二：创建时指定唯一列
  mysql> create table class2(
      ->  id bigint,
      -> name varchar(20),
      -> unique (id,name));
  Query OK, 0 rows affected (0.06 sec)
  -- 方式一和方式二的区别：
  -- 方式一只能给单列加主键，而方式二支持复合主键 
  mysql>  desc class2;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  | MUL | NULL    |       |
  | name  | varchar(20) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  方式三：修改为唯一索引
  mysql>  create table class(
      ->  id bigint,
      -> name varchar(20));
  Query OK, 0 rows affected (0.10 sec)
  -- 修改方式1：
  mysql> alter table class modify id bigint unique;
  Query OK, 0 rows affected (0.04 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql> desc class;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  | UNI | NULL    |       |
  | name  | varchar(20) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  mysql> create table class3(
      -> id bigint,
      -> name varchar(20));
  Query OK, 0 rows affected (0.09 sec)
  -- 修改方式2：
  mysql> alter table class3 add unique(id,name);
  Query OK, 0 rows affected (0.09 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql> desc class3;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | YES  | MUL | NULL    |       |
  | name  | varchar(20) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  主键索引

  1.定义

  当在一个表上定义一个主键primary key时，自动创建索引，索引的值是主键列的值，主键的索引的列值不能为空且必须唯一，InnoDB使用它作为聚簇索引。

  2.使用

  -- 方式一：创建表时直接创建主键
  mysql> create table class1(
      -> id bigint primary key auto_increment,
      ->  name varchar(20));
  Query OK, 0 rows affected (0.02 sec)
  -- 方式二：创建表示单独创建主键列
  mysql> create table class3(
      -> id bigint auto_increment,
      -> name varchar(20),
      -> primary key(id,name));
  Query OK, 0 rows affected (0.11 sec)
  -- 方式三：修改表中的列为主键值
  mysql> create table class5(
      -> id bigint,
      -> name varchar(20));
  Query OK, 0 rows affected (0.09 sec)
  -- 修改方式1:
  mysql> alter table class5 modify id bigint primary key auto_increment;
  Query OK, 0 rows affected (0.11 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  -- 查看修改后的表结构
  mysql> desc class5;
  +-------+-------------+------+-----+---------+----------------+
  | Field | Type        | Null | Key | Default | Extra          |
  +-------+-------------+------+-----+---------+----------------+
  | id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
  | name  | varchar(20) | YES  |     | NULL    |                |
  +-------+-------------+------+-----+---------+----------------+
  2 rows in set (0.02 sec)
  mysql> create table class6(
      -> id bigint,
      ->  name varchar(20));
  Query OK, 0 rows affected (0.10 sec)
  -- 修改方式1：
  mysql> alter table class6 add primary key(id,name);
  Query OK, 0 rows affected (0.14 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql> alter table class6 modify id bigint auto_increment;
  Query OK, 0 rows affected (0.09 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  -- 查看修改后的表结构
  mysql> desc class6;
  +-------+-------------+------+-----+---------+----------------+
  | Field | Type        | Null | Key | Default | Extra          |
  +-------+-------------+------+-----+---------+----------------+
  | id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
  | name  | varchar(20) | NO   | PRI | NULL    |                |
  +-------+-------------+------+-----+---------+----------------+
  2 rows in set (0.00 sec)

  三、索引的删除

   语法

  alter table class6 drop 索引名;

  示例：

  -- 删除主键索引
  -- 删除主键前，如果有自增属性，要先删除自增属性，否则删除主键失败
  mysql> alter table class6 drop primary key;
  ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
  -- 删除自增属性：将自增属性修改为非自增属性
  mysql> alter table class6 modify id bigint;
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  -- 删除自增属性
  mysql> alter table class6 drop primary key;
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  -- 查看删除后的表结构
  mysql> desc class6;
  +-------+-------------+------+-----+---------+-------+
  | Field | Type        | Null | Key | Default | Extra |
  +-------+-------------+------+-----+---------+-------+
  | id    | bigint(20)  | NO   |     | NULL    |       |
  | name  | varchar(20) | NO   |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  四、其他索引

  全文索引：基于文本列（char、varchar、text）上创建，加快对些列中包含的数据查询和DML操作。
  聚簇索引：

  非聚簇索引：

  索引覆盖：当一个select语句使用了普通索引且查询列表中的列刚好是创建索引时的所有或部分列，这是就可以直接返回，而不用回表查询。 

  到此这篇关于MySQL索引中的页及索引的分类及使用的文章就介绍到这了,
  3周前 (01-21)

• Mysql索引合并的实现示例

  MySQL 中的索引合并是一种查询优化技术，当单个表查询的 WHERE 子句中包含多个条件，并且这些条件分别可以用到不同的索引时，MySQL 优化器可能会尝试将这些索引扫描的结果合并起来，以更高效地获取最终满足所有条件的行。它本质上是优化器在无法找到最优的单个复合索引时的一种“折衷”策略。

  核心思想： 利用多个索引分别筛选数据，然后将结果集合并（交集、并集或排序后并集）以得到最终结果，避免全表扫描。

  一、索引合并的类型

  MySQL 主要支持三种索引合并算法：

  1.1 Index Merge Intersection Access (Using intersect(...)):

  适用场景： WHERE 子句中的多个条件通过 AND 连接，并且每个条件都可以有效地使用一个单独的索引（这些索引通常是单列索引）。

  工作原理：优化器对每个可用的索引执行范围扫描或等值查询扫描。

  示例：

  CREATE TABLE `t` (
    `id` INT PRIMARY KEY,
    `a` INT,
    `b` INT,
    `c` VARCHAR(100),
    INDEX `idx_a` (`a`),
    INDEX `idx_b` (`b`)
  );
  -- 假设 idx_a 和 idx_b 都是 B-Tree 索引
  SELECT * FROM t WHERE a = 10 AND b = 20;

  1.2 Index Merge Union Access (Using union(...)):

  适用场景： WHERE 子句中的多个条件通过 OR 连接，并且每个条件都可以有效地使用一个单独的索引（这些索引通常是单列索引），并且查询是 SELECT（非 UPDATE/DELETE），并且没有使用 FOR UPDATE 或 LOCK IN SHARE MODE。

  工作原理：

  示例：

  SELECT * FROM t WHERE a = 10 OR b = 20;

  1.3 Index Merge Sort-Union Access (Using sort_union(...)):

  适用场景： WHERE 子句中的多个条件通过 OR 连接，但是这些条件无法直接使用 Index Merge Union（通常是因为索引扫描返回的是范围结果，而不仅仅是点查询的等值结果）。它是 Union 的一种变体，用于处理范围扫描。

  工作原理：

  示例：

  SELECT * FROM t WHERE a < 10 OR b < 20;
  -- 或者
  SELECT * FROM t WHERE a < 10 OR b = 20; -- 一个范围，一个等值

  二、索引合并的优点

  三、索引合并的缺点与注意事项

  通常不如复合索引高效：

  不是所有条件组合都适用：

  -- 查看当前设置
  SELECT @@optimizer_switch;
  -- 关闭所有索引合并优化
  SET optimizer_switch = 'index_merge=off';
  -- 关闭特定类型的索引合并 (e.g., intersection)
  SET optimizer_switch = 'index_merge_intersection=off';

  需要确认相关标志（index_merge, index_merge_intersection, index_merge_union, index_merge_sort_union）是开启的 (on)。

  统计信息准确性： 优化器是否选择索引合并以及选择哪种合并算法，高度依赖于表的统计信息（如索引的基数 cardinality）。过时的统计信息可能导致优化器做出错误的选择。

  替代方案 - 优先考虑复合索引：

  四、如何识别索引合并

  使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 查看查询的执行计划：

  五、总结

  MySQL 的索引合并（Index Merge）是一种在特定查询条件下（涉及多个索引列且条件由 AND 或 OR 连接），优化器利用多个独立索引分别扫描数据，然后对结果集进行交集、并集或排序后并集操作，最终定位目标行的优化策略。

  虽然索引合并提供了一种避免全表扫描的途径，但它通常伴随着额外的扫描、合并和回表开销。创建合适的复合索引（Composite Index）通常是解决这类查询性能问题的首选和更优方案，因为它能更直接、高效地定位数据。

  到此这篇关于Mysql索引合并的实现示例的文章就介绍到这了,
  3周前 (01-21)

• MySQL 排序规则Collation实例详解

  在 MySQL 中，排序规则（Collation）是控制字符比较方式的核心机制，直接影响查询结果的排序、匹配逻辑（如大小写区分）等。本文将从基础概念出发，详解排序规则的作用、与字符集的关系、查看与配置方法，并通过实际案例说明其对查询结果的影响，帮助开发者精准控制数据匹配行为。

  一、排序规则的核心概念

  排序规则（Collation）是字符集（Character Set）的 "配套规则"，定义了字符之间的比较、排序逻辑。其核心作用体现在两个维度：

  例如，UTF-8 字符集的utf8mb4_0900_ai_ci排序规则中：

  二、字符集与排序规则的关联

  字符集与排序规则是 "一对多" 的关系：一个字符集可以对应多个排序规则，但每个排序规则仅属于一个字符集。两者的默认关联规则如下：

  三、排序规则的查看与配置

  1. 查看字符集与排序规则信息

  查看所有字符集及其默认排序规则：

  -- 方法1：SHOW命令
  SHOW CHARACTER SET;
  -- 方法2：查询系统表
  SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

  查看指定字符集的所有排序规则：

  -- 查看utf8mb4字符集的所有排序规则
  SHOW COLLATION WHERE Charset = 'utf8mb4';

  查看数据库的默认字符集与排序规则：

  -- 方法1：使用系统变量
  USE test_db; -- 切换到目标数据库
  SELECT @@character_set_database, @@collation_database;
  -- 方法2：查询系统表
  SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA 
  WHERE SCHEMA_NAME = 'test_db'; -- 替换为数据库名

  2. 不同对象的排序规则设置

  MySQL 中，排序规则可在服务器、数据库、表、列、字符串五个层级设置，下层默认继承上层配置（可单独覆盖）。

  对象	字符集设置方式	排序规则设置方式	说明
  服务器	character_set_server 系统变量	collation_server 系统变量	未指定时，数据库默认继承服务器配置
  数据库	建库时 CHARACTER SET 子句	建库时 COLLATE 子句	未指定时，表默认继承数据库配置
  表	建表时 CHARACTER SET 子句	建表时 COLLATE 子句	未指定时，列默认继承表配置
  列	字段定义中 CHARACTER SET 子句	字段定义中 COLLATE 子句	最细粒度控制，直接影响字段查询逻辑
  字符串常量	SELECT _utf8mb4'abc'	SELECT 'abc' COLLATE utf8mb4_general_ci	临时指定，覆盖连接级别的默认规则

  四、排序规则对查询结果的影响

  排序规则不影响数据存储（存入的'a'和'A'会原样保存），但直接决定查询时的匹配逻辑。以下通过测试案例验证：

  测试环境

  案例 1：忽略大小写的排序规则（utf8mb4_0900_ai_ci）

  -- 设置字段排序规则为忽略大小写
  ALTER TABLE test_db.a CHANGE i i CHAR(1) COLLATE utf8mb4_0900_ai_ci;
  -- 插入数据
  INSERT INTO test_db.a VALUES ('a'), ('A');
  -- 查询：条件为'i = 'a''
  SELECT * FROM test_db.a WHERE i = 'a'; 
  -- 结果：返回'a'和'A'（两者被视为相同）

  案例 2：区分大小写的排序规则（utf8mb4_0900_as_cs）

  -- 设置字段排序规则为区分大小写
  ALTER TABLE test_db.a CHANGE i i CHAR(1) COLLATE utf8mb4_0900_as_cs;
  -- 插入数据（同上）
  INSERT INTO test_db.a VALUES ('a'), ('A');
  -- 查询：条件为'i = 'a''
  SELECT * FROM test_db.a WHERE i = 'a'; 
  -- 结果：仅返回'a'（严格区分大小写）

  结论

  五、实践建议

  查询时临时覆盖：如需临时改变匹配逻辑，可在查询中指定排序规则：

  -- 临时按区分大小写查询（即使字段默认不区分）
  SELECT * FROM test_db.a WHERE i COLLATE utf8mb4_0900_as_cs = 'a';

  总结

  MySQL 排序规则是控制字符比较逻辑的关键机制，其核心作用体现在查询阶段的匹配与排序行为。通过理解字符集与排序规则的关联、掌握多层级配置方法，并结合业务场景选择合适的规则（如区分 / 忽略大小写），可确保查询结果符合预期，避免因字符匹配问题导致的业务异常。

  到此这篇关于MySQL 排序规则Collation实例详解的文章就介绍到这了,
  3周前 (01-21)

• MySQL中隔离级别的4种小结

  在数据库管理系统中，事务隔离级别（Transaction Isolation Levels）决定了事务之间如何相互隔离，以防止数据不一致和其他并发问题。MySQL 提供了四种标准的事务隔离级别，每种级别在并发性能和数据一致性之间有不同的权衡。本文将详细介绍这四种隔离级别，包括它们的定义、特点、优缺点以及适用场景。

  1. 事务隔离级别的概念

  事务隔离级别定义了事务在执行过程中如何与其他事务隔离，以确保数据的完整性和一致性。不同的隔离级别通过不同的锁机制和并发控制策略来实现，从而在并发性能和数据一致性之间取得平衡。

  2. MySQL 的四种事务隔离级别

  MySQL 支持 ANSI SQL 标准定义的四种事务隔离级别，按照隔离程度从低到高依次为：

  

  脏读就是一个事务读取到了另一个事务还没有提交的数据

  不可重复度就是一个事务两次相同的sql,查询结果返回的结果却不一样,由于另外一个事务修改并提交导致的

  幻读就是插入的时候提示已经有了,但是查询的时候是空的

  2.1 READ UNCOMMITTED（读未提交）

  定义

  在此隔离级别下，事务可以读取其他事务尚未提交的修改（脏读）。这意味着一个事务可以看到另一个事务尚未提交的中间状态数据。

  特点

  优点

  缺点

  适用场景

  示例

  假设有两个事务 T1 和 T2：

  2.2 READ COMMITTED（读已提交）

  定义

  在此隔离级别下，事务只能读取已经提交的其他事务所做的修改。每个查询只会看到在该查询开始之前已经提交的数据。

  特点

  优点

  缺点

  适用场景

  示例

  2.3 REPEATABLE READ（可重复读）

  定义

  在此隔离级别下，事务在执行期间多次读取同一数据时，会看到一致的结果，即使其他事务已经修改了这些数据。MySQL 的 InnoDB 存储引擎通过多版本并发控制（MVCC）实现这一点。

  特点

  优点

  缺点

  适用场景

  示例

  2.4 SERIALIZABLE（串行化）

  定义

  这是最高的隔离级别，要求事务必须顺序执行，以避免并发问题。在此级别下，事务会对读取的数据加锁，防止其他事务对其进行修改或插入。

  特点

  优点

  缺点

  适用场景

  示例

  3. MySQL 中的隔离级别设置

  在 MySQL 中，可以通过以下方式设置事务的隔离级别：

  3.1 查看当前的隔离级别

  SHOW VARIABLES LIKE 'transaction_isolation';
  

  或者

  SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;
  

  3.2 设置全局隔离级别

  设置全局隔离级别会影响所有新连接，当前已经存在的会话不会受到影响。

  SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  

  3.3 设置当前会话的隔离级别

  设置当前会话的隔离级别，只影响当前会话及之后新建的子会话。

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

  3.4 设置下一个事务的隔离级别

  仅对下一个事务生效，事务结束后恢复到之前的隔离级别。

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  START TRANSACTION;
  -- 事务内容
  COMMIT;
  

  4. 不同隔离级别的并发问题对比

  隔离级别	脏读（Dirty Read）	不可重复读（Non-Repeatable Read）	幻读（Phantom Read）
  READ UNCOMMITTED	可能发生	可能发生	可能发生
  READ COMMITTED	不会发生	可能发生	可能发生
  REPEATABLE READ	不会发生	不会发生	可能发生（InnoDB 部分防止）
  SERIALIZABLE	不会发生	不会发生	不会发生

  5. 实际案例分析

  案例 1：读未提交导致脏读

  场景：电商系统中，订单支付和库存扣减。

  结果：库存数据不一致，可能导致超卖。

  案例 2：读已提交避免脏读，但可能出现不可重复读

  场景：银行转账系统，账户余额查询和更新。

  结果：虽然避免了脏读，但同一事务内多次读取同一数据得到不同结果，可能导致逻辑错误。

  案例 3：可重复读保证一致性

  场景：在线订票系统，查询和预订座位。

  结果：确保了 T1 在事务内看到的一致性，避免了不可重复读和幻读。

  案例 4：串行化确保最高一致性

  场景：股票交易系统，订单匹配和执行。

  结果：确保了数据的高度一致性，避免了所有并发问题，但牺牲了并发性能。

  到此这篇关于MySQL中隔离级别的4种小结的文章就介绍到这了,
  3周前 (01-21)