MySQL：系统信息查询/pconnect和connect/binlog三种工作模式/mysqldump/分区/死锁检测

Asthenian
44 阅读3分钟

1. MySQL的SELECT如何查询系统信息？

在MySQL中，可以通过SELECT语句结合系统变量或函数查询系统信息：

  • 查询服务器版本： 
    SELECT VERSION();
    输出示例：8.0.27，表示MySQL的版本号。
  • 查询当前数据库名称： 
    SELECT DATABASE();
    如果当前未选择数据库，返回NULL，否则返回当前数据库名，如my_db。
  • 其他常用系统信息：
    • SELECT @@hostname;：查询服务器主机名。
    • SELECT @@port;：查询服务器端口号。

2. MySQL的pconnect和mysql_connect的区别？

  • mysql_connect：
    • 普通的MySQL连接函数，建立非持久化连接。
    • 脚本执行结束后，连接自动关闭。
    • 每次请求都重新建立连接，适用于短期任务。
  • mysql_pconnect：
    • 持久化连接函数，连接不会因脚本结束而关闭。
    • 服务器维护一个连接池，下次请求可复用已有连接，减少连接开销。
    • 适用于高并发、频繁连接的场景，但可能导致连接数过多，需注意配置max_connections。
  • 注意：这俩函数都属于老的mysql扩展，已在PHP 7中移除，推荐使用mysqli或PDO。

3. MySQL的Binlog有哪些工作模式？

Binlog（二进制日志）记录数据库变更操作，有三种模式：

  • ROW（行模式）：
    • 记录每行数据的具体变更。
    • 日志详细，占用空间大，但主从复制更可靠。
    • 适合数据一致性要求高的场景。
  • STATEMENT（语句模式）：
    • 记录执行的SQL语句。
    • 日志体积小，但可能因语句非确定性（如NOW()）导致主从不一致。
    • 适合简单查询场景。
  • MIXED（混合模式）：
    • 默认模式，结合ROW和STATEMENT。
    • 普通语句用STATEMENT，非确定性操作（如函数）用ROW。
    • 平衡了性能和一致性。
  • 配置：binlog_format参数控制，默认值通常是MIXED。

4. MySQL有哪些命令进行备份和恢复？

  • 备份：
    • mysqldump： 
      mysqldump -u root -p db_name > backup.sql
      逻辑备份，生成SQL脚本，适合中小型数据库。
    • mysqlhotcopy： 物理备份，复制数据文件，仅限MyISAM引擎（已较少使用）。
    • XtraBackup： 第三方工具，支持InnoDB热备份，无锁备份大型数据库。
  • 恢复：
    • 使用mysqldump备份文件： 
      mysql -u root -p db_name < backup.sql
    • 物理备份恢复： 停止MySQL服务，替换数据目录后重启。
  • 注意：备份前建议开启--single-transaction（InnoDB）确保一致性。

5. MySQL的分区机制

  • 分区机制： 分区是将大表按规则拆分成多个小分区，逻辑上仍是一个表，但物理存储分开，提升查询和维护效率。
  • 适用业务场景：
    • 时间序列数据（如日志，按年/月分区）。
    • 大表查询性能瓶颈（如订单表，按用户ID分区）。
    • 数据归档需求（老数据分区后可快速删除）。
  • 分区类型：
    • RANGE：按范围分区，如按日期。 
      PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021)
);
    • LIST：按枚举值分区，如按地区。
    • HASH：按哈希值均匀分布，适合负载均衡。
    • KEY：类似HASH，但由MySQL内部控制。
  • 优势：提高查询效率、便于数据管理；缺点：跨分区查询可能变慢。

6. MySQL中的死锁机制

  • 死锁定义： 两个或多个事务互相持有对方需要的锁，形成循环等待，导致无法继续执行。
  • 死锁判定：
    • MySQL通过InnoDB的锁监控检测死锁。
    • 查看死锁日志： 
      SHOW ENGINE INNODB STATUS;
      输出中LATEST DETECTED DEADLOCK部分显示死锁详情。
  • 解决方法：
    • 预防：
      • 按固定顺序访问资源（如先锁表A再锁表B）。
      • 减少事务范围，尽量缩短锁持有时间。
      • 使用较低隔离级别（如READ COMMITTED）。
    • 处理：
      • MySQL会自动回滚一个事务（通常是代价较小的）。
      • 应用层捕获死锁错误（1213或1205），重试事务。
    • 调试：
      • 开启innodb_print_all_deadlocks，记录所有死锁到错误日志。
avatar
文章
阅读
粉丝