MySQL高级(下)

性能优化

索引优化

索引单表优化案例

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
 
SELECT * FROM article;

查询category_id 为 1 且comments 大于1的情况，views最多的article_id。
分析查询语句，type是all 表示全表扫描了，这是最差的，extra还有 Using filesort文件内排序，最差。必须优化

当前索引

优化：创建查询用到的三个字段的复合索引
create index idx_atricle_ccv on article(category_id,comments,views);
查看索引表，创建成功

再次分析此次查询

因为 comment 是 > 条件，索引失效，产生了 Using filesort，只解决了一半的问题

删除索引，重新建立，不再建立comments的索引
create index idx_article_cv on article(category_id,views);

索引两表优化案例

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

explain 分析，type 有all

先尝试给 book 表 的card字段建索引alter table book add index y(card);索引名为y
左连接，加了右表的索引

删除索引，尝试只给class表加索引alter table class add index y(card);

可以到这次优化没有上次优化好，因为左连接，左边是查全部，优化应该放在相反的方向，右边是关键点。右链接反之

索引三表优化案例

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

删除掉之前的索引。
三表内链接
explain select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;

三表左连接
select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

给 book 和 phone表 的card 字段建立索引
alter table book add index y(card);
alter table class add index z(card);

再看三表左连接和内连接

后两行type都是ref且总rows优化很好，效果不错。因此索引做好设置在需要经常查询的字段中。

结论：
join语句的优化
尽可能减少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”
优先优化NestedLoop的内层循环；
保证join语句中被驱动表上的join条件字段已经被索引；
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置；

索引优化，索引失效

索引优化一：最佳左前缀法则

索引失效的各种常见原因

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

最佳左前缀法则：如果索引了多列，要遵守此法则。指的是查询从索引的最左前列开始并且不跳过索引中间列。

一个值，两个值，三个值

如下查询，违背了最佳左前缀法则，type为all ref 也为null，变成了全表扫描，所以最佳左前缀法则，从左边开始，建的索引的第一个字段不能丢失，得按照建索引的顺序来查找

索引优化二：不在索引列做操作

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

用了left函数和没用，都能正常查询返回结果

用了函数的，索引失效，全文扫描

索引优化三：范围之后全失效

存储引擎不能使用索引中范围条件右边的列，范围之后全失效

索引优化四：减少select *

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *；
Extra 会出现 Using index

索引优化五：!=或者< >

mysql在使用不等于（！=或者< >）的时候无法使用索引会导致全表扫描

索引优化六：is null，is not null

is null，is not null 也无法使用索引

索引优化七：like以通配符开头

like以通配符开头（’%abc…’）mysql 索引会失效会变成全表扫描的操作。

通配符开头很差

不以通配符开头，好很多

TODO like sql 在mybatis中该怎么写

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

没建索引，左右通配符 全表扫描

create index idx_user_nameAge on tbl_user(name,age)
建立索引后，除了 select * 其他查询都能用到索引，type 为 ALL

索引优化七：字符串不加单引号索引失效 (varchar 类型)

一个是int型2000，一个是varchar型2000，mysql会在底层做隐式类型转换

索引优化七：少用or

小总结

索引面试题分析

create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

发现打乱索引的顺序，还是能触发覆盖索引，因为mysql的逻辑架构中Optimizer查询优化器 会开启自我优化分析，以达到最佳效果 这是一个特例，在常量级别的范围是能自动调整到最优，但是建议还是不要作死，怎么建的顺序，就按照顺序写

explain select * from test03 where c4='a1' and c3='a2' and c2='a4' and c1='a3';

---

范围之后全失效

select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

---

select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

通过key_len可以看出用到了和第一条一样，用到了4个关键字，因为mysql底层自动调优顺序变成了1234，但是由于最后一个是范围查找，所以type是 range

---

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

c3的作用在排序而不是查找，理论上用到了索引，但是没有统计到里explain里面

---

explain select * from test03 where c1='a1' and c2='a2' order by c3;

只比上一条少一个c4，发现和上一条一样的结果，所以说使c3用了排序后，就跟c4没关系了

---

explain select * from test03 where c1='a1' and c2='a2' order by c4;
跳过了c3 直接排序c4，产生了 Using filesort

---

explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;

只用c1一个字段索引，但是c2、c3用于排序，无filesort

---

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

调换了c2 和 c3 产生了 filesort，没有按照我们建索引的顺序来

---

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
比较正常，没啥问题，有按照索引顺序

---

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
结果同上，于 c5无关

---

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
咋一看，order by中 c3和c2未遵循索引顺序，但是，c2在这之前已经成为常量所以没产生filesort

---

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
未尊照索引顺序

---

explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
只用到了一个索引值

对比
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;

group by 表面叫分组，但是，分组前必排序，会有临时表产生

索引优化补充和总结口诀

优化总结口诀

【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

查询截取分析

查询优化

小表驱动大表

in和exist

不会

为排序使用索引order by 优化

order by子句，尽量使用Index方式排序，避免使用FileSort方式排序

create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

select * from tblA;

mysql支持两种方式的排序，FileSort 和Index，Index效率高，它只mysql扫描索引本身完成排序。
order by 会使用Index方式排序：最佳左前缀
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort优两种算法：mysql的双路排序和单路排序

双路排序：mysql4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终取得数据，读取指针和orderby列，对他们就行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。进行了两次扫描，I\O很耗时

单路排序：从磁盘读取查询所需要的列，按照order by 列在buff儿对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了二次读取数据。并且把随机IO变成了顺序IO，大师它会使用更多的空间。因为它把每一行都保存在内存中了。

由于单路是后出的，总体而言好过双路，但是单路也有问题

group by几乎与order by 一致

慢查询日志

说明：

查看是否开启及如何开启
查看：SHOW VARIABLES LIKE '%slow_query_log%';
开启：set global slow_query_log=1

设置 超过多少时间为慢sql


查询当前mysql优多少条慢查询记录 show global status like '%Slow_queries%';

可以作为系统健康检查度
若想永久统计生效。my.cnf文件

日志分析工具mysqldumpslow

批量数据脚本

create table dept(
id int unsigned primary key auto_increment,
deptno mediumint unsigned not null default 0,
dname varchar(20) not null default "",
loc varchar(13) not null default ""
)engine=innodb default charset=GBK;

CREATE TABLE emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
ename varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=GBK;

由于在做大数据量插入的时候mysql会报错，需要设置二进制日志功能开启
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

创建函数，保证每条数据都不同

随机产生字符串函数

//随机产生字符串函数
delimiter $$
create function ran_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$

//随机产生部门编号函数
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$

创建存储过程

因为要往 emp表中一次性插入1000w条数据，但是怕一次性数据量太大压坏mysql，分成50w一次做一次批处理

创建往emp表中插入数据的存储过程

delimiter $$ 
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

创建往dept表中插入数据的存储过程

//存储过程
delimiter $$ 
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into dept(deptno,dname,loc) values((start+i),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$

调用存储过程

之前定义 DELIMITER $$ 结束，恢复默认分号结束 DELIMITER ；
dept表。
CALL insert_dept(100,10); 从 100开始，插10个部门编号

emp表 添加50万条数据

Show Profile SQL 分析

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤
1、是否支持，看看当前mysql版本是否支持
show variables like 'profiling';
2、开启功能，默认是关闭，使用前需要开启
set profiling=on;
3、运行SQL
select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId=d.id;
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

4、查看结果，show profiles

5、诊断SQL，show profile cpu,block io for query xxx Query_ID；
类目太多，主要就看cpu和block io就行

6、日常开发需要注意的结论

分析中，主要注意以下4个Status 的，主要是这几个 Status 拖慢 Sql 查询

全局查询日志

只运行在测试环境用，永远不要在生产环境开启这个功能

配置启用

编码启动

MySQL锁机制

定义，防止争抢

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

从对数据操作的粒度来分：表锁\行锁

三锁

表锁（偏读）

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

create table mylock (
id int not null primary key auto_increment,
name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

手动增加表锁
lock table 表名 read(write), 表名2 read(write), ...
查看表上加过的锁
show open tables;
释放表
unlock tables;

读锁案例讲解1

会话1加读锁后 lock table mylock read; 会话2也能查询到数据，共享锁
会话1无法修改数据

mysql> update mylock set name='a2' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

会话1无法读取其他表

mysql> select * from book;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

会话2无法修改 mylock 表，被阻塞，等待锁释放

会话1解锁后unlock tables;，会话2执行改操作，等待了 2分多钟

读锁案列讲解2

加写锁lock table mylock write;

查询更新都被阻塞

结论

简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞

如何分析表锁定

分析这个命令就够了·show status like 'table%';

此外，MyISAM的读写锁调度是写有限，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

行锁（偏写）

偏向InnoDB存储引擎，开销大，加锁慢了会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同优两点：
一是支持事物(TRANSACTION)；
二是采用了行级锁；

行锁支持事务 关于事务的复习

事务及其ACID属性

更新丢失

脏读

不可重复读

幻读

mysql默认隔离级别 可重复读

行锁案列讲解

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
select * from test_innodb_lock;

innodb 写操作分号后自动提交，关闭自动提交
SET autocommit=0; 只限制当前会话需要手动提交

会话1修改 表数据后，查询到是 4002，而会话2查询到则是4000 ，由于未提交，所以出现脏读。会话1 commit 后 会话2读取到正确数据
update test_innodb_lock set b='4002' where a=4;

会话1修改数据后，会话2尝试修改同一行数据，发现会话2阻塞，会话1 commit后 会话2执行修改成功。

若两会话修改不同行数据，则互相不影响

索引失效，行锁变表锁

因为 字段a 是int型，字段b是varchar型，故意将b输入条件为int型时，mysql自动类型转换索引失效，产生了表锁，此时，其他会话无法操作该表任何数据(只能查操作)

间隙锁的危害

当使用范围条件操作数据时，InnoDB会给范围条件内的所有记录加锁，即使这个值不存在，范围外的不受影响

会话1，范围1到7

会话2，可以看到插入 范围外的11成功，范围内a为2到其实并不存在，但是还是被锁住

如何锁定一行

不必打开SET autocommit=0;

行锁总结

行锁的分析，分析行锁的争夺情况
show status like 'innodb_row_lock%';

优化建议

页锁 （没啥用）

主从复制

复制的基本原理

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

复制的基本原则

每个salve只有一个master
每个salve只能优一个唯一的服务器ID
每个master可以有多个slave

复制的最大问题

延时

一主一从常见配置

mysql版本一致且后台以服务运行，主从配置在[mysqld]节点下，都是小写
主机修改配置文件 ，

server-id=1 主服务器唯一ID
启用二进制日志

从机配置
注释
# server-id = 1
server-id = 2
主从都关闭防火墙
linux 关闭 service iptables stop
centos7 systemctl stop firewalld

在主机授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhuzhu'@'192.168.22.10' IDENTIFIED BY '123456';
刷新一下 flush privileges;

查询master 主机的状态. show master status;

从机设置

CHANGE MASTER TO MASTER_HOST='192.168.1.123',
MASTER_USER='zhuzhu',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.000004',MASTER_LOG_POS=263;

start slave;

show slave status\G;

配置完成

如何停止从服务复制功能
stop slave

end