MySQL综合二

一、高可用架构

1. Orc对比MHA的优势

• Orc本身是高可用架构，而MHA是单点架构。
• Orc支持web管理界面和API接口，能灵活实现针对大规模集群的管理。
• Orc切换支持多种hook scripts脚本，能自定义实现各种功能。
• Orc支持动态发现并调整拓扑图架构，如加减从库，而MHA只能被动调整配置文件并重启服务。
• Orc基于Go语言开发，对二次开发友好。

2. Orc切换流程

1. Orchestrator探测集群节点，监控主库和从库复制是否正常。
2. 主库故障且从库复制关系异常时，判定主库故障并执行切换。
3. 选择新主（读取最新的binlog位点）。
4. 进行高可用切换，打开新主的读写，调用钩子函数。
5. 清理新主的复制信息。

3. MHA切换流程

1. 检测Master存活性，经过二次检测，发生故障时开始进行切换。
2. 确定新的Master，标准是最新的read_master_log_postion位点。
3. SSH尝试连接旧Master并拷贝与新Master差异binlog。
4. 新Master应用差异的binary/relay log
5. 打开新Master读写权限，绑定VIP
   6 从库并行应用差异的binary/relay log日志，并重新指向新Master。
6. 清除新Master的复制信息。

二、备份恢复

1. XtraBackup备份流程

• 开启redo拷贝线程和ibd数据拷贝线程。
• 拷贝innodb表
• 加FLUSH TABLES WITH READ LOCK获取全局读锁。
• 拷贝非InnoDB表。
• 获取并保存当前位点信息。
• 停止redo log的写入，备份完成。

三、SQL语句优化

1. 慢日志EXPLAIN需要关注点

• 是否走索引，索引是否完全、正确。
• 扫描的行数与实际预期结果行数对比。
• 是否用到临时表、文件排序、联合查询字段是否走索引等信息。

2. 慢日志查看字段

• Query_time: 语句执行时间。
• Lock_time: 获取锁的时间。
• Rows_sent: 发送给Client的行数。
• Rows_examined: 服务器层检查的行数。

3. 慢日志优化手段

• 数据库层优化：添加索引、读写分离、冷热数据归档、提升硬件水平。
• 应用层优化：增加Redis缓存层、优化业务逻辑、降低并发或改写SQL。

4. 驱动表

• 驱动表在SQL执行过程中会先被访问，而被驱动表则会在每次驱动表的一行数据被处理后访问。
• 在EXPLAIN执行计划中，通常情况下，id列值较小的表被视为驱动表。
• 优化的一个常见目标是让小表作为驱动表来驱动大表。

5.联合索引最左匹配原则

-mysql会一直向右进行匹配直到遇到(>,<,between,like等)会失效
-联合索引的构建顺序是按照列的顺序构建的

四、监控报警

• Linux级别监控：CPU、I/O性能、内存使用率、硬件健康、网络性能、进程和资源。
• MySQL级别监控：QPS、slow log、锁等待、死锁、主从复制状态等。

五、MySQL BUG

• AHI导致的latch征用，关闭AHI特性。
• 关闭loosescan特性解决查询问题。

六、分库分表

• 分布式ID问题、片键选择、分片方法、扩容缩容问题、聚合查询问题、中间层损耗问题。

七 ### 锁粒度分类

1. 全局锁：对整个数据库加锁，通常在需要对整个数据库进行特殊操作时使用，例如数据库的整体备份。这种锁粒度最大，对并发性能影响也最大。

2. 表级锁：锁定整个表，阻止其他用户并发访问。表级锁开销小，加锁快，但并发度相对较低。

3. 行级锁：

   • record lock（记录锁）：锁定单条记录。
   • gap lock（间隙锁）：锁定一个范围，但不包括记录本身，用于防止幻读。
   • next key lock：结合记录锁和间隙锁，锁定记录及记录前的间隙。

4. 插入意向锁：表示事务意图在某个间隙中插入新记录的锁。

八 ### 锁性质分类

1. 共享锁（S锁）：允许多个事务读取同一资源，但不允许写操作。

2. 排它锁（X锁）：阻止其他事务对资源进行读写操作，确保数据的一致性和完整性。

九 ### gap加锁机制

gap加锁机制主要是为了防止幻读。当需要锁定一个范围时，gap锁会锁定该范围内的间隙，但不包括具体记录。具体实现上，它会向右寻找最近一个匹配条件的记录，并将这个范围锁定。这样可以确保在这个范围内不会有新的记录被插入，从而避免幻读。

九 ### 当前读操作

当前读操作是指那些在执行时会设置锁的操作，以确保数据的一致性。包括：

• delete：删除操作，会先锁定要删除的记录。
• update：更新操作，同样需要先锁定要更新的记录。
• select for share mode：设置共享锁，允许其他事务读取但不允许修改。
• select for update：设置排它锁，阻止其他事务对被锁定的记录进行任何操作。

这些是当前读操作，因为它们在执行时会锁定相关的数据记录或范围，以确保数据在事务处理过程中的一致性。

十、Linux磁盘监控

• 使用top查看wa（iowait）百分比。
• 使用iostat查看%util、tps、Blk_read/s、Blk_wrtn/s和await等指标。

十一、负载

• 负载值表示单位时间段内CPU活动进程数。单核CPU负载小于1表示无负载压力，多核CPU负载小于核数表示无负载压力。