Skip to main content

msyql 主从复制读写分离

主从复制​

主从复制和读写分离都是 MySQL 的高可用性和性能优化方案。主从复制可以提高数据库的可用性，因为在 master 服务器失效时，slave 服务器可以继续提供服务。同时，slave 服务器也可以用作读操作，减轻 master 服务器的压力。读写分离将读操作分配到多个 slave 服务器上，减轻 master 服务器的压力。同时，它也可以提高数据库的可用性，因为在 master 服务器失效时，slave 服务器可以继续提供服务。

主从复制的核心算法原理是通过 binary log 和 relay log 实现的。binary log 是 master 服务器上的日志文件，记录了所有的写操作。relay log 是 slave 服务器上的日志文件，用于传输 master 服务器的 binary log 到 slave 服务器。

  1. 在 master 服务器上启动 binary log。

  2. 在 slave 服务器上启动 relay log。

  3. 在 slave 服务器上启动复制线程，将 master 服务器的 binary log 传输到 slave 服务器。

  4. 在 slave 服务器上启动应用线程，将 relay log 应用到 slave 服务器上。

函数

查询 INVOICE_IDS 字段中包含 99232 的记录​

select *
from moa_fnd_submit_material  where find_in_set('99232', INVOICE_IDS);

使用 MySQL 提供的字符串函数 locate()函数。​

MySQL 还提供一个字符串函数 locate(substr,str)函数，用于返回 str 中 substr 所在的位置索引，如果找到了，则返回一个大于 0 的数，否则返回 0。

select * from user where locate('yanggb', hobby ) > 0;

适用的场景和 find_in_set()函数差不多，两个函数的区别大概只有返回值上的不同。

锁

按封锁粒度分为行锁和表锁​

行锁​

innodb 实现了两种类型的行锁分别是共享锁(S 锁、读锁)和排他锁(X 锁、写锁)

  • 共享锁

    允许获得该锁的事务读取数据行，同时也允许其他事务获得该数据行上的共享锁，并阻止其他事务对该数据行加排他锁

  • 排他锁

    允许获得该锁 的事务更新或删除数据行，同时阻止其他事物对该数据行加共享锁和排他锁

测试

  • 共享锁

    先取消事务自动提交

    set autocommit = 0

    两个控制台都可以读取加了共享锁后的数据

    image-20221001160146155

    两个控制台可以使用同一个共享锁，但是如果下面的连接对该行数据使用排他锁，那么将会阻塞，一直等待上面的共享锁释放。

    image-20221001160410498

    将上面事务提交后，下面就能查询出该行数据

    image-20221001160429314

  • 排他锁

    上面的控制台对 id=1 的记录添加排他锁，下面控制台对该行数据添加共享锁尝试查询数据，同样也是等待排他锁释放

    image-20221001160720618

    image-20221001160754715

表锁​

表级别的锁分为意向共享锁(IS)和意向排他锁(IX) ,都称为意向锁。

意向锁的作用：如果需要对表添加表锁，需要判断当前表中的某行记录是否有行级的排他锁，如果一行行去遍历来查看是否有对应的锁的话，效率是十分低下的，所以可以所用意向锁来判断是否可以对某张表添加表锁

  • 意向共享锁(Intention Shared Lock，IS 锁):事务有意向对表中的某些记录添加共享锁，对某行记录加共享锁之前必须先在对应表加意向共享锁
  • 意向排他锁(Intention Exclusive Lock，IX 锁):事务有意向对表中的某些记录添加排他锁，对某行记录加排他锁之前必须先在对应表加意向共享锁

意向锁是有数据库引擎来维护的，用户无需干涉

表级锁的兼容性

image-20221001164959319

意向排他锁(IX)和排他锁(X),当事务 A 对表加了 IX 锁，表示事务 A 准备修改表中的某一行数据、那么事务 B 想要对零一行数据添加 X 锁的时候，就会先获取 IX 锁，此时 IX 锁已经被事务 A 取到了，事务 B 就拿不到 IX 锁，那么事务 B 将会阻塞，等待事务 A 去释放 IX 锁，之后事务 B 才可进行相关操作。

重点看 IX 和 IX 是兼容的，也就是同一张表可以加两个 IX 锁。原因是，两个事务 A、B，如果他们需要修改的数据并不是同一条记录，那么并不会产生互斥效果，所以应当允许 A、B 事务同时加 IX 锁

实战记录​

当使用 for update 时。如果 where 条件中没有索引，会导致全表扫描，此时 for update 将会锁定全表。当一个事务获得锁，并执行业务逻辑，未提交事务的情况下。此时另一个事务将不能更新这个表中的任何数据，只有当之前的事务提交或回滚后，另一个事务才可以继续操作

其他​

1、死锁问题

#查看当前活动的事务：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#终止当前trx
KILL 5661;#trx_mysql_thread_id

#查看当前等待锁的事务：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#查看当前被锁定的资源：
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;