Mysql高级（二）

锁机制

在数据库中，数据也是一种供许多用户共享的资源，为了保证数据的一致性】有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

对数据操作的类型：

1. 读锁：共享锁，针对同一份数据，多个读操作可以同时进行，不会互相影响
2. 写锁：排它锁，当前写操作没有完成前，它会阻断其他写锁和读锁

对数据操作的粒度：

1. 表锁
2. 行锁

---

表锁

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的更新会让查询很难获得锁，从而造成永久阻塞。

命令：

3. 手动增加表锁：lock table 表名 read/write,表名 read/write;
4. 释放锁：unlock tables

（表）读锁

1. session1给表加读锁，能对该表进行读，其他session也能读该表
2. session1不能读其他表，其他session可以读其他表
3. session1不能改自己加锁的表，其他session改加锁的表会进入阻塞
   1. 等session1释放锁，其他session的改操作才会执行

（表）写锁

1. session1给表加写锁，能对该表进行读、改，其他session读该表会进入阻塞
   1. 等session1释放锁，其他session的操作才会执行
2. session1不能读其他表，其他session可以读改其他表

总结

读锁会阻塞写，但是不会阻塞读。而写锁会把读和写都阻塞。

分析表锁

1. 查看表上加过的锁：show open tables
2. 查看锁的状态：show status like 'table%'
   1. Table_locks_immediate：产生标记锁定的次数，表示可以立即获得锁的查询次数
   2. Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获得锁的次数），值越高，证明存在严重的表级争用情况

---

行锁

特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。但也有其脆弱的一面，如果

InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。

事务的一些知识

ACID：

1. 原子性
2. 一致性
3. 隔离性
4. 持久性

并发事务带来的问题：

1. 更新覆盖
2. 脏读
3. 不可重复读
4. 幻读

事务的隔离级别：

1. 读未提交
2. 读已提交
3. 可重复度：InnoDB默认
4. 串行化

总结

1. 行锁两个事务同时操作同一行时，后面的事务会进入阻塞状态

   1. 等待事务1提交之后，事务2才会执行

2. 当操作不同行时，互不影响

3. 索引失效，会导致行锁升级为表锁

   索引：a，索引：b
   #session1
   update test set a=41 where b=4000;#b是字符串类型，没有加单引号导致索引失效
   #session2
   update test set b='9000' whrer b=9;
   #结果：由于session1中的事务索引失效，导致session2进入阻塞
   #session1
   commmit;
   #结果：session1提交之后，session2恢复执行
   

间隙锁

1. 当执行过程中通过范围查找，它就会锁定整个范围内的所有索引键值，即使这个键值不存在。
   1. 不存在的键值被无辜锁定，导致锁定之后，无法在该范围内插入任何数据

#表中数据id:1,3,4,5,6
#session1
update test set age=18 where id>1 and id<6;
#session2
insert into test(id) values(2);
#结果：因为id在1-6之间，在此范围内插入数据，会产生间隙锁，session2进入阻塞
#session1
commit;
#结果：session1提交之后，session2恢复执行。以后session2是在session1之后才执行的，故不会受到1的操作

如何锁定一行

1. begin
2. for update

#session1
begin;
select * from test where id=8 for update; #for update会为id=8的行加上锁
#session2
update test age=18 where id=8 ;#进入阻塞状态
#session1
commit;#session1提交之后，session2恢复执行

分析行锁

1. show status like 'innodb_row_lock%'

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免索引失效行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围，避免间隙锁
3. 尽量控制事务大小，减少锁定资源量和时间长度
4. 尽可能低级别事务隔离

---

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间；并发一般。

主从复制

基本原理

slave会从master读取binlog来进行数据同步

复制过程：

1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
2. salve将master的binary log events拷贝到它的中继日志（relay log）
3. salve重做中继日志中的事件，将改变应用到自己的数据库中

复制的基本原则

1. 每个slave只有一个Master
2. 每个slave只能有一个唯一的服务器id
3. 每个master可以有多个slave

一主一从常见配置

1. mysql版本一致且后台以服务运行，必须双向ping ip 能通
2. 主从都配置在[mysqld]结点下，都是小写
3. 主机修改配置my.ini
   1. 【必须】主服务器唯一ID：sever-id=1
   2. 【必须】启用二进制日志：log-bin=自己本地的路径/mysqlbin，如log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
   3. 【可选】启动错误日志：log-err=自己本地的路径/mysqlerr
   4. 【可选】根目录：basedir="自己本地路径"，如log-bin=D:/devSoft/MySQLServer5.5/
   5. 【可选】临时目录：tempdir="自己本地路径"
   6. 【可选】数据目录：datadir="自己本地路径/Data/"
   7. read-only=0，主机，读写都可以
   8. 【可选】设置不要复制的数据库：binlog-ignore-db=mysql
   9. 【可选】设置需要复制的数据库：binlog-do-db=需要复制的主数据库名字
4. 从机修改配置my.cnf
   1. 【必须】从服务器唯一ID：将server-id=1注释掉，往下拉，开启server-id=2的注释
   2. 【可选】启用二进制日志
5. 修改配置文件后，重启主/从机的服务
   1. service mysql stop
   2. service mysql start
6. 主机从机关闭防火墙
   1. windows手动关闭
   2. 关闭虚拟机Linux防护墙 service iptables stop
7. 在windows主机上建立账户并授权slave
   1. 登录mysql账号
   2. grant replication slave on *.* to 'zhangsan'@'从机数据库ip'identified by '123456'：表示从机可以通过zhangsan用户，密码123456来操作主机
   3. 刷新：flush privileges;
   4. 查询master状态
      1. show master status;
      2. 记录下file、position的值：表示告知从机从哪开始复制
8. 在Linux从机上配置需要复制的主机
   1. 登录mysql账号
   2. 配置需要复制的主机：change master to master_host='主机数据库ip',master_user='张三',master_password='123456',master_log_file='mysqlbin.上述file的数字',master_log_pos=上述position的值;
      1. 如果出现error 1198，需要stop slave，再进行配置
   3. 启动从服务器复制功能：start slave;
   4. 查看slave状态
      1. show slave status\G
         1. Slave_IO_Running:Yes
         2. Slave_SQL_Running:Yes：这两个值必须同时为Yes，否则失败
9. 配置完成，进行测试：
   1. 主机新建库、表、Insert操作。
   2. 从机进行查询，发现已经自动复制完成
10. 关闭从服务复制功能
    1. stop slave;

注意：

如果配置出现问题，如没有出现两个Yes，需要关闭从服务复制功能，重新主机授权，记录最新的file和position值，再在从机上配置需要复制的主机。