MySQL高级（二）

尚硅谷mysql高级部分学习

一、MySQL锁机制

一、锁的分类

1、从对数据操作的类型（读/写）分

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

2、从对数据操作的粒度分为

表锁
行锁

二、二锁

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

1.2、案例分析
1.2.1建表

lock table 表名字 read(write),表名字2 read(write)，其它；#手动增加表锁
show open tables; #查看数据库中表是否加锁，In_use为1表示表加了锁
unlock tables; #释放表锁

1.2.2、为mylock表加read锁（读阻塞写例子）
1)、开启两个连接分别为session1和session2。
2)、通过session1给表加读锁,session2不给任何表加锁

3)、因为读锁是共享锁，所以此时session2去读取数据都可以获取到，但不可对表数据进行变更。

4)、通过session1尝试去修改加锁后表中数据，发现并不能修改。

5)、session1只能查询加锁表的数据，不能查询其它表，session2可以查询加锁表和其它表

6）、通过session2去修改被session1锁住的表数，发现session2进入阻塞状态，在等待session1释放锁。在session1释放锁的瞬间，处于阻塞状态的session2修改了表数据。


1.2.3、为mylock表加write锁（MyISAM存储引擎的写阻塞读例子）
1)、开启两个连接分别为session1和session2。
2)、通过session1给表加写锁,session2不给任何表加锁

3）、因为写锁是排它锁，所以此时的session1可以对锁表进行读取或修改操作，但session2去读取或修改锁表的数据时处于阻塞状态，等待session1释放锁。
4）、session1只能查询加锁表的数据，不能查询其它表

1.2.4案例结论

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改查前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式

表共享读锁
表独占写锁

结论：

1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读释放锁以后，才会执行其它进程的操作
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放以后，才会执行其他进程的读写操作
简而言之读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞

1.2.5、表锁分析

2、行锁（偏写）
2.1、特点：①偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。②InnDB与MyISAM的最大不同有两个点：一是支持事务；二是采用了行级锁
2.2、案例分析
2.2.1、建表

2.2.2、
索引失效会导致行锁变表锁

间隙锁的危害


结论：

行锁分析：


优化建议：

二、主从复制

一、复制的基本原理

1、slave会从master读取binlog来进行数据同步
2、三步骤+原理图
MySQL复制过程分成三步：

• master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events。
• slave将master的binary log events拷贝到它的中继日志（relay log）。
• slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的。
  

二、复制的基本原则

1. 每个slave只有一个master
2. 每个slave只能有一个唯一的服务器ID
3. 每个master可以有多个slave
   复制中会遇到的最大的问题就是延时。

三、一主一从常见配置

1、保证mysql版本一致且后台以服务运行
2、主从都配置在[mysqld]结点下，都是小写
3、主机修改my.ini配置文件
①、server-id=1 [必须]主服务器唯一ID

②、[必须]启动二进制日志，log-bin=自己本地的路径/mysqlbin
③、[可选]启用错误日志，log-err=自己本地的路径/mysqlerr

④、[可选]根目录，basedir=“自己的本地路径”
⑤、[可选]临时目录,tmpdir=“自己的本地路径”

⑥、[可选]数据目录,datadir=“自己本地路径/Data/”
⑦、read-only=0，主机，读写都可以
⑧、[可选]设置不要复制数据库，binlog-ignore-db=mysql
⑨、[可选]设置需要复制的数据库，binlog-do-db=需要复制的主数据库名字

4、从机修改my.cnf配置文件
①、[必须]从服务器唯一ID，my.cnf默认开启server-id=1，需要将其注释掉，然后在打开server-id=2的注释

②[可选] 启用二进制日志
3、因修改过配置文件，请主机+从机都重启后台mysql服务。
4、主机和从机都关闭防火墙

• windows手动关闭
• liunx通过指令service iptables stop

5、在Windows主机上建立账户并授权slave
①、

GRANT ALL PRIVILEGES ON . TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY “123456”;
授权了复制给从机，允许从机以zhangsan的用户名和123456的密码登录到我的主机

格式：grant 权限 on 数据库名.表名 to 用户@登录主机 identified by “用户密码”;
@ 后面是访问MySQL的客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本----地访问（那此用户就不能远程访问该mysql数据库了）

②、FLUSH PRIVILEGES;刷新MySQL的系统权限相关表，否则会出现拒绝访问

③、查询master的状态
show master status；
记录下File和Position的值
④、启动从服务器复制功能

start slave;

从机绑定主机
change master to master_host=主机ip,
master_port=端口,
master_user=用户名,
master_password=密码,
master_log_file=File名,
master_log_pos=Position的值;

⑤、通过show slave status\G；下面两个参数都是Yes，则说明配置成功

6、测试（主机新建库、表、insert数据，从机复制）
①在主机上创建mydb58数据库和dog表并插入一条数据

②、在从机上查看所有的数据库，发现有个mydb58的库，查询结果为

7、停止从机复制功能

stop slave;